本文详细介绍如何使用C#将DataTable数据导出为Excel文件，包括生成XML格式的Excel工作表及下载文件的过程。同时，提供了从Excel文件读取数据并转换为DataTable的方法，涵盖了连接字符串设置、异常处理及XML文件读取等关键步骤。
DataTable导出成Excel文件：
1
public 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 }
public 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
13
public 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 tag23
strExcelXml.Append("<Worksheet ss:Name=\""+ dt.TableName +"\">");24
// Then Table Tag25
strExcelXml.Append("<Table>");26
strExcelXml.Append(GetHeaderText(dt));27
int intColCount=dt.Columns.Count;28
foreach(DataRow dr in dt.Rows)29
{30
// Row Tag31
strExcelXml.Append("<Row>\r\n");32
for(int j=0;j<intColCount;j++)33
{34
// Cell Tags35
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
49
private 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
try63
{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
catch71
{72
}73
74
75
//Writeout the Content 76
HttpContext.Current.Response.Write(p_strFileContext);77
try78
{79
HttpContext.Current.Response.End();80
}81
catch82
{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 }
private 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
} 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 }
private 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
} 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 }
/**//// <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
try25
{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
}
扫一扫
7291
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
