DataTable导出成Excel文件:
1public static void DataSetToExcel(DataSet p_dsExport,string p_strFileName,bool p_blnHaveHeaderText)
2 {
3 if (p_dsExport == null)
4 {
5 return ;
6 }
7 string strContext=GenerateWorkSheet(p_dsExport);
8
9 DownloadExcelFile(strContext,p_strFileName);
10
11 }
12
13public static string GenerateWorkSheet(DataSet p_dsExport)
14 {
15 System.Text.StringBuilder strExcelXml=new System.Text.StringBuilder ();
16 strExcelXml.Append(ExcelHeader());
17 strExcelXml.Append(ExcelWorkSheetOptions());
18
19
20 foreach(DataTable dt in p_dsExport.Tables)
21 {
22 // Create First Worksheet tag
23 strExcelXml.Append("<Worksheet ss:Name=\""+ dt.TableName +"\">");
24 // Then Table Tag
25 strExcelXml.Append("<Table>");
26 strExcelXml.Append(GetHeaderText(dt));
27 int intColCount=dt.Columns.Count;
28 foreach(DataRow dr in dt.Rows)
29 {
30 // Row Tag
31 strExcelXml.Append("<Row>\r\n");
32 for(int j=0;j<intColCount;j++)
33 {
34 // Cell Tags
35 strExcelXml.Append("<Cell ss:Index=\""+(j+1).ToString()+"\"><Data ss:Type=\"String\">");
36 strExcelXml.Append(System.Web.HttpUtility.HtmlEncode( ObjectToNullStr(dr[j])));
37 strExcelXml.Append("</Data></Cell>\r\n");
38 }
39 strExcelXml.Append("</Row>\r\n");
40
41 }
42 strExcelXml.Append("</Table>");
43 strExcelXml.Append("</Worksheet>");
44 }
45 strExcelXml.Append("</Workbook>\r\n");
46 return strExcelXml.ToString();
47 }
48
49private static void DownloadExcelFile(string p_strFileContext,string p_strFileName)
50 {
51 // Appending Headers
52 if (IsNullString(p_strFileName))
53 {
54 p_strFileName="Excel.xls";
55 }
56
57 if (!p_strFileName.Trim().ToLower().EndsWith(".xls"))
58 {
59 p_strFileName += ".xls";
60 }
61
62 try
63 {
64 HttpContext.Current.Response.Clear();
65 HttpContext.Current.Response.Buffer= true;
66 p_strFileName = UrlEncode(p_strFileName);
67 HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
68 HttpContext.Current.Response.AppendHeader("content-disposition", "attachment; filename=" + p_strFileName);
69 }
70 catch
71 {
72 }
73
74
75 //Writeout the Content
76 HttpContext.Current.Response.Write(p_strFileContext);
77 try
78 {
79 HttpContext.Current.Response.End();
80 }
81 catch
82 {
83 }
84
85 }
2 {
3 if (p_dsExport == null)
4 {
5 return ;
6 }
7 string strContext=GenerateWorkSheet(p_dsExport);
8
9 DownloadExcelFile(strContext,p_strFileName);
10
11 }
12
13public static string GenerateWorkSheet(DataSet p_dsExport)
14 {
15 System.Text.StringBuilder strExcelXml=new System.Text.StringBuilder ();
16 strExcelXml.Append(ExcelHeader());
17 strExcelXml.Append(ExcelWorkSheetOptions());
18
19
20 foreach(DataTable dt in p_dsExport.Tables)
21 {
22 // Create First Worksheet tag
23 strExcelXml.Append("<Worksheet ss:Name=\""+ dt.TableName +"\">");
24 // Then Table Tag
25 strExcelXml.Append("<Table>");
26 strExcelXml.Append(GetHeaderText(dt));
27 int intColCount=dt.Columns.Count;
28 foreach(DataRow dr in dt.Rows)
29 {
30 // Row Tag
31 strExcelXml.Append("<Row>\r\n");
32 for(int j=0;j<intColCount;j++)
33 {
34 // Cell Tags
35 strExcelXml.Append("<Cell ss:Index=\""+(j+1).ToString()+"\"><Data ss:Type=\"String\">");
36 strExcelXml.Append(System.Web.HttpUtility.HtmlEncode( ObjectToNullStr(dr[j])));
37 strExcelXml.Append("</Data></Cell>\r\n");
38 }
39 strExcelXml.Append("</Row>\r\n");
40
41 }
42 strExcelXml.Append("</Table>");
43 strExcelXml.Append("</Worksheet>");
44 }
45 strExcelXml.Append("</Workbook>\r\n");
46 return strExcelXml.ToString();
47 }
48
49private static void DownloadExcelFile(string p_strFileContext,string p_strFileName)
50 {
51 // Appending Headers
52 if (IsNullString(p_strFileName))
53 {
54 p_strFileName="Excel.xls";
55 }
56
57 if (!p_strFileName.Trim().ToLower().EndsWith(".xls"))
58 {
59 p_strFileName += ".xls";
60 }
61
62 try
63 {
64 HttpContext.Current.Response.Clear();
65 HttpContext.Current.Response.Buffer= true;
66 p_strFileName = UrlEncode(p_strFileName);
67 HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
68 HttpContext.Current.Response.AppendHeader("content-disposition", "attachment; filename=" + p_strFileName);
69 }
70 catch
71 {
72 }
73
74
75 //Writeout the Content
76 HttpContext.Current.Response.Write(p_strFileContext);
77 try
78 {
79 HttpContext.Current.Response.End();
80 }
81 catch
82 {
83 }
84
85 }
将Excel文件中的数据导入到DatSet
1private static string GetExcelConnectString(string p_strFileName,bool p_blnHaveHeaderText)
2 {
3 string strHDR=p_blnHaveHeaderText?"Yes":"No";
4 string strRtn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
5 + p_strFileName
6 +"; Extended Properties=\"Excel 8.0; HDR="+strHDR+"\";";
7 return strRtn;
8 }
2 {
3 string strHDR=p_blnHaveHeaderText?"Yes":"No";
4 string strRtn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
5 + p_strFileName
6 +"; Extended Properties=\"Excel 8.0; HDR="+strHDR+"\";";
7 return strRtn;
8 }
1private void Button2_Click(object sender, System.EventArgs e)
2 {
3
4 //也可以使用右侧的路径 string filename = @"e:\Book1.xls";
5 string filename = Request.PhysicalApplicationPath + @"excel\Book1.xls";
6
7 System.Data.DataTable dt = new DataTable();
8
9 //第二个参数若为false,则第一行数据做为datatable的数据存在。反之,则不。
10 dt = GYRTExcel.ExcelToDataTable(filename,false);
11 this.DataGrid1.DataSource = dt;
12 this.DataGrid1.DataBind();
13 }
2 {
3
4 //也可以使用右侧的路径 string filename = @"e:\Book1.xls";
5 string filename = Request.PhysicalApplicationPath + @"excel\Book1.xls";
6
7 System.Data.DataTable dt = new DataTable();
8
9 //第二个参数若为false,则第一行数据做为datatable的数据存在。反之,则不。
10 dt = GYRTExcel.ExcelToDataTable(filename,false);
11 this.DataGrid1.DataSource = dt;
12 this.DataGrid1.DataBind();
13 }
1/// <summary>
2 /// 把 Excel 文件的数据导入到 DataTable 中
3 /// </summary>
4 /// <param name="p_strFileName">服务器上 Excel 文件的全路径</param>
5 /// <param name="p_blnHaveHeaderText">true 标示第一行是否是列名,默认值为 true </param>
6 /// <returns></returns>
7 public static DataTable ExcelToDataTable(string p_strFileName,bool p_blnHaveHeaderText)
8 {
9 return ExcelToDataTable(p_strFileName,p_blnHaveHeaderText,null);
10 }
11
12/// <summary>
13 /// 把 Excel 文件的数据导入到 DataSet 中
14 /// </summary>
15 /// <param name="p_strFileName">服务器上 Excel 文件的全路径</param>
16 /// <param name="p_blnHaveHeaderText">true 标示第一行是否是列名,默认值为 true</param>
17 /// <returns></returns>
18 public static DataSet ExcelToDataSet(string p_strFileName,bool p_blnHaveHeaderText)
19 {
20 using(OleDbConnection conn=
21 new OleDbConnection(GetExcelConnectString(p_strFileName,p_blnHaveHeaderText)))
22 {
23 DataSet dsRtn=new DataSet();
24 try
25 {
26 //如果不是标准的 Excel 文件则当作是 Xml 文件读取
//如果Excel文件的路径不对的话,通常会跳到catch中,并返回对文件无读写权限或文件已经被占用。
27 conn.Open();
28 }
29 catch(Exception ex)
30 {
31 return ReadXmlFile(p_strFileName,p_blnHaveHeaderText);
32 }
33
34 DataTable dtExcelTable=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
35 if (dtExcelTable == null)
36 {
37 return null;
38 }
39 foreach (DataRow dr in dtExcelTable.Rows)
40 {
41 string strTableName=ObjectToNullStr(dr["TABLE_NAME"]);
42 if (IsNullString(strTableName))
43 {
44 continue ;
45 }
46 string strSheetName = strTableName.Substring(0,strTableName.Length-1);
47 string strCommandText="select * from " + "[" + strTableName + "]";
48
49
50 OleDbDataAdapter daAdapter=new OleDbDataAdapter(strCommandText,conn);
51 DataTable dt=new DataTable(strSheetName);
52 daAdapter.FillSchema(dt,SchemaType.Source);
53 daAdapter.Fill(dt);
54 dsRtn.Tables.Add(dt);
55 }
56 conn.Close();
57 return dsRtn;
58 }
59 }
2 /// 把 Excel 文件的数据导入到 DataTable 中
3 /// </summary>
4 /// <param name="p_strFileName">服务器上 Excel 文件的全路径</param>
5 /// <param name="p_blnHaveHeaderText">true 标示第一行是否是列名,默认值为 true </param>
6 /// <returns></returns>
7 public static DataTable ExcelToDataTable(string p_strFileName,bool p_blnHaveHeaderText)
8 {
9 return ExcelToDataTable(p_strFileName,p_blnHaveHeaderText,null);
10 }
11
12/// <summary>
13 /// 把 Excel 文件的数据导入到 DataSet 中
14 /// </summary>
15 /// <param name="p_strFileName">服务器上 Excel 文件的全路径</param>
16 /// <param name="p_blnHaveHeaderText">true 标示第一行是否是列名,默认值为 true</param>
17 /// <returns></returns>
18 public static DataSet ExcelToDataSet(string p_strFileName,bool p_blnHaveHeaderText)
19 {
20 using(OleDbConnection conn=
21 new OleDbConnection(GetExcelConnectString(p_strFileName,p_blnHaveHeaderText)))
22 {
23 DataSet dsRtn=new DataSet();
24 try
25 {
26 //如果不是标准的 Excel 文件则当作是 Xml 文件读取
//如果Excel文件的路径不对的话,通常会跳到catch中,并返回对文件无读写权限或文件已经被占用。
27 conn.Open();
28 }
29 catch(Exception ex)
30 {
31 return ReadXmlFile(p_strFileName,p_blnHaveHeaderText);
32 }
33
34 DataTable dtExcelTable=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,null);
35 if (dtExcelTable == null)
36 {
37 return null;
38 }
39 foreach (DataRow dr in dtExcelTable.Rows)
40 {
41 string strTableName=ObjectToNullStr(dr["TABLE_NAME"]);
42 if (IsNullString(strTableName))
43 {
44 continue ;
45 }
46 string strSheetName = strTableName.Substring(0,strTableName.Length-1);
47 string strCommandText="select * from " + "[" + strTableName + "]";
48
49
50 OleDbDataAdapter daAdapter=new OleDbDataAdapter(strCommandText,conn);
51 DataTable dt=new DataTable(strSheetName);
52 daAdapter.FillSchema(dt,SchemaType.Source);
53 daAdapter.Fill(dt);
54 dsRtn.Tables.Add(dt);
55 }
56 conn.Close();
57 return dsRtn;
58 }
59 }