C#自定义导出数据到Excel的类实例解析

1、123456789101112131415161718192021222324252627282930313233343536373839C#自定义导出数据到Excel的类实例本文实例讲述了 C#自定义导出数据到 Excel的类。分享给大家供大家参考。具体如下：C#自定义Excel操作类，可以用于将DataTable导出到Excel文件，从ExceI文件读取数据。using System;using Syste m.10;using System.Data;using System.Collect ions;using System.Data.OleDb;using System.Web;us

2、i ng System.Web.UI;using System.Web.UI.WebC on trols;n amespace DotNet.Utilities/ / Excel操作类/ / Microsoft Excel 11.0 Object Librarypublic class ExcelHelper#region数据导出至 Excel文件/ /导出ExceI文件，自动返回可下载的文件流/ public static void DataTable1Excel(System.Data.DataTable dtData)GridView gvExport = n ull;HttpC on

3、text curCon text = HttpCo ntext.Curre nt;Strin gWriter strWriter = null;HtmlTextWriter htmlWriter = null;if (dtData != null)curC on text.Resp on se.C onten tType = applicati on/vn d.ms-excel;curC on text.Resp on se.C on te ntEn codi ng = System.Text.E ncodi ng.GetE ncodi ng(gb2312); curC on text.Res

4、p on se.Charset = utf-8;strWriter = new Strin gWriter();htmlWriter = new HtmlTextWriter(strWriter);gvExport = new GridView();gvExport.DataSource = dtData.DefaultView;gvExport.AllowPagi ng = false;gvExport.DataBi nd();gvExport.Re nderC on trol(htmlWriter);curC on text.Resp on se.Write(metahttp-equiv=

5、C onten t-TypecurC on text.Resp on se.E nd();41424344454647484950515253545556575859606162636465666768697071727374757677787980818283/ /导出ExceI文件，转换为可读模式/ public static void DataTable2Excel(System.Data.DataTable dtData)DataGrid dgExport = n ull;HttpC on text curCon text = HttpCo ntext.Curre nt;Strin g

6、Writer strWriter = null;HtmlTextWriter htmlWriter = null;if (dtData != null)curC on text.Resp on se.C onten tType = applicati on/vn d.ms-excel;curC on text.Resp on se.C ontentEn codi ng = System.Text.E ncodi ng.UTF8;curC on text.Resp on se.Charset =;strWriter = new Strin gWriter();htmlWriter = new H

7、tmlTextWriter(strWriter);dgExport = new DataGrid();dgExport.DataSource = dtData.DefaultView;dgExport.AllowPagi ng = false;dgExport.DataB in d();dgExport.Re nderCo ntrol(htmlWriter);curC on text.Resp on se.Write(strWriter.ToStri ng();curC on text.Resp on se.E nd();/ /导出ExceI文件，并自定义文件名/ public static

8、void DataTable3Excel(System.Data.DataTable dtData, String FileName)GridView dgExport = n ull;HttpC on text curCon text = HttpCo ntext.Curre nt;Strin gWriter strWriter = null;HtmlTextWriter htmlWriter = null;if (dtData != null)HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);curC on text.Re

9、sp on se.AddHeader(c onten t-dispositi on,attachme nt;file name= +HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + .xls);curC on text.Resp on se.C onten tType = applicati on n d.ms-excel;85868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212

10、312412512612784curC on text.Resp on se.C ontentEn codi ng = System.Text.E ncodi ng.UTF8;curCo ntext.Respo nse.Charset = GB2312;strWriter = new Strin gWriter();htmlWriter = new HtmlTextWriter(strWriter); dgExport = new GridView();dgExport.DataSource = dtData.DefaultView;dgExport.AllowPagi ng = false;

11、dgExport.DataB in d();dgExport.Re nderCo ntrol(htmlWriter);curC on text.Resp on se.Write(strWriter.ToStri ng();curC on text.Resp on se.E nd();/ /将数据导出至Excel文件/ / DataTable 对象 / Excel 文件路径 public static bool OutputToExcel(DataTable Table, string ExcelFilePath)if (File.Exists(ExcelFilePath)throw new E

12、xception(” 该文件已经存在！”)；if (Table.TableName.Trim ().Len gth = 0) | (Table.TableName.ToLower() = table)Table.TableName = Sheet1;/数据表的列数int ColCo unt = Table.Colu mn s.Co unt;/用于记数，实例化参数时的序号int i = 0;/创建参数OleDbParameter para = new OleDbParameterColCou nt;/创建表结构的SQL语句string TableStructStr = Create Table

13、+ Table.TableName + (;/连接字符串string connString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + ExcelFilePath +;Exte nded Properties=Excel 8.0;OleDbC onnection objConn = new OleDbC onn ectio n(conn Stri ng);/创建表结构OleDbComma nd objCmd = new OleDbComma nd();/数据类型集合ArrayList DataTypeList = new ArrayLis

14、t();129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171DataTypeList.Add(System.Double);DataTypeList.Add(System.I nt16);DataTypeList.Add(System.I nt32);DataTypeList.Add(System.I nt64);DataTypeList.Add(System.Si ngle);/遍历数据表

15、的所有列，用于创建表结构foreach (DataColu mn col in Table.Colu mns)/如果列属于数字列，则设置该列的数据类型为doubleif (DataTypeList.I ndexOf(col.DataType.ToStri ng() = 0)parai = new OleDbParameter(” + col.Colu mnN ame, OleDbType.Double); objCmd.Parameters.Add(parai);/如果是最后一列if (i + 1 = ColCo unt)TableStructStr += col.Colu mnN ame +

16、 double);elseTableStructStr += col.Colu mnN ame + double,;elseparai = new OleDbParameter( + col.Colu mnN ame, OleDbType.VarChar); objCmd.Parameters.Add(parai);/如果是最后一列if (i + 1 = ColCo unt)TableStructStr += col.Colu mnN ame + varchar);elseTableStructStr += col.Colu mnN ame + varchar,;i+;/创建ExceI文件及文

17、件结构tryobjCmd.C onnection = objC onn;173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215if (objC onn. State = Connection State.Closed)objConn. Ope n();objCmd.ExecuteN on Query();catch (Excepti on exp)throw exp;/插入记录的SQL语句st

18、ring In sertSql_1 = In sert into + Table.TableName + (;string In sertSql_2 = Values (;stri ng In sertSql =;/遍历所有列，用于插入记录，在此创建插入记录的SQL语句for (i nt colID = 0; colID ColCou nt; collD+)if (colID + 1 = ColCount) / 最后一列In sertSql_1 += Table.Colu mn scollD.Colu mnN ame + );In sertSql_2 += + Table.Colu mn sc

19、ollD.Colu mnN ame + );elseIn sertSql_1 += Table.Colu mn scollD.Colu mnN ame + ,;In sertSql_2 += + Table.Colu mn scollD.Colu mnN ame + ,;In sertSql = In sertSql_1 + In sertSql_2;/遍历数据表的所有数据行for (i nt rowID = 0; rowID Table.Rows.Cou nt; rowID+)for (i nt colID = 0; colID ColCou nt; colID+)if (paracollD

20、.DbType = DbType.Double & Table.RowsrowlDcollD.ToStri ng().Trim()=)paracolID.Value = 0;elseparacolID.Value = Table.RowsrowlDcollD.ToStri ng().Trim();217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259tryobjCmd.Comma ndText

21、 = In sertSql;objCmd.ExecuteN on Query();catch (Excepti on exp)stri ng str = exp.Message;tryif (objC onn. State = Connection State.Ope n)objC onn. Close();catch (Excepti on exp)throw exp;return true;/ /将数据导出至Excel文件/ / DataTable 对象 / 要导出的数据列集合 / Excel 文件路径 public static bool OutputToExcel(DataTable

22、Table, ArrayList Columns, string ExcelFilePath) if (File.Exists(ExcelFilePath)throw new Exception(” 该文件已经存在！”)；/如果数据列数大于表的列数，取数据表的所有列if (Colu mn s.Co unt Table.Colu mn s.Co unt)for (i nt s = Table.Colu mn s.Co unt + 1; s = Column s.Co unt; s+)Columns.RemoveAt(s); /移除数据表列数后的所有列26126226326426526626726

23、8269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303DataColu mn colu mn = new DataColu mn();for (i nt j = 0; j Column s.Co un t; j+)trycolu mn = (DataColum n)Colu mn sj;catch (Excepti on)Colu mn s.RemoveAt(j);if (Table.TableName.Trim ().Len gth =

24、 0) | (Table.TableName.ToLower() = table)Table.TableName = Sheet1;/数据表的列数int ColCo unt = Column s.Co unt;/创建参数OleDbParameter para = new OleDbParameterColCou nt;/创建表结构的SQL语句string TableStructStr = Create Table + Table.TableName + (;/连接字符串string connString = Provider=Microsoft.Jet.OLEDB.4.0;Data Sourc

25、e= + ExcelFilePath +;Exte nded Properties=Excel 8.0;OleDbC onnection objConn = new OleDbC onn ectio n(conn Stri ng);/创建表结构OleDbComma nd objCmd = new OleDbComma nd();/数据类型集合ArrayList DataTypeList = new ArrayList();DataTypeList.Add(System.Decimal);DataTypeList.Add(System.Double);DataTypeList.Add(Syste

26、m.I nt16);DataTypeList.Add(System.I nt32);DataTypeList.Add(System.I nt64);DataTypeList.Add(System.Si ngle);DataColu mn col = new DataColu mn();/遍历数据表的所有列，用于创建表结构for (int k = 0; k = 0)305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343

27、344345346347parak = new OleDbParameter( + col.Captio n.Trim(), OleDbType.Double); objCmd. Parameters.Add(parak);/如果是最后一列if (k + 1 = ColCo unt)TableStructStr += col.Capti on .Trim() + Double);elseTableStructStr += col.Captio n. Trim() + Double,;elseparak = new OleDbParameter( + col.Captio n. Trim(),

28、OleDbType.VarChar); objCmd. Parameters.Add(parak);/如果是最后一列if (k + 1 = ColCo unt)TableStructStr += col.Capti on .Trim() + VarChar);elseTableStructStr += col.Capti on .Trim() + VarChar,;/创建ExceI文件及文件结构tryobjCmd.C onnection = objC onn;objCmd.Comma ndText = TableStructStr;if (objC onn. State = Connectio

29、n State.Closed)objConn. Ope n();objCmd.ExecuteN on Query();catch (Excepti on exp)throw exp;/插入记录的SQL语句349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391string In sertSql_2 = Values (;stri ng In sertSql =;/遍历所有列，用于插入记录，在此创

30、建插入记录的SQL语句for (i nt colID = 0; colID ColCou nt; collD+)if (colID + 1 = ColCount) / 最后一列In sertSql_1 += Colu mn scollD.ToStri ng().Trim() + );In sertSql_2 += + Colu mn scollD.ToStri ng().Trim() + );elseIn sertSql_1 += Colu mn scollD.ToStri ng().Trim() + ,;In sertSql_2 += + Colu mn scollD.ToStri ng()

31、.Trim() + ”，”；In sertSql = In sertSql_1 + In sertSql_2;/遍历数据表的所有数据行DataColu mn DataCol = new DataColum n();for (i nt rowID = 0; rowID Table.Rows.Cou nt; rowID+)for (i nt colID = 0; colID ColCou nt; colID+)/因为列不连续，所以在取得单元格时不能用行列编号，列需得用列的名称DataCol = (DataColu mn )Colu mn scolID;&if(paracollD.DbType=Db

32、Type.DoubleTable.RowsrowlDDataCol.Captio n.ToStri ng().Trim()=)paracolID.Value = 0;elseparacolID.Value = Table.RowsrowlDDataCol.Captio n.ToStri ng().Trim();tryobjCmd.Comma ndText = In sertSql;objCmd.ExecuteN on Query();catch (Excepti on exp)stri ng str = exp.Message;393394395396397398399400401402403

33、404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435tryif (objC onn. State = Connection State.Ope n)objC onn. Close();catch (Excepti on exp)throw exp;return true;#en dregi on/ III获取ExceI文件数据表列表/ public static ArrayList GetExcelTables(string ExcelFileName)D

34、ataTable dt = new DataTable();ArrayList TablesList = new ArrayList();if (File.Exists(ExcelFileName)using(OleDbC onnectionconn=newOleDbCo nn ectio n( Provider=MJet.OLEDB.4.0;Exte ndedProperties=Excel 8.0;Data Source= + ExcelFileName)tryconn. Ope n();dt = conn.GetOleDbSchemaTable

35、(OleDbSchemaGuid.Tables, new object null, null, null, TABLE );catch (Excepti on exp)throw exp;/获取数据表个数int tableco unt = dt.Rows.Co unt;for (i nt i = 0; i tableco unt; i+)string table name = dt.Rowsi2.ToStri ng().Trim().TrimE nd($);437438439440441442443444445446447448449450451452453454455456457458459

36、460461462463464465466467468469470471472473474475476477478479TablesList.Add(table name);retur n TablesList;/ III将Excel文件导出至 DataTable(第一行作为表头)/ / vparam name=ExcelFilePathExcel 文件路径 / vparam name=TableName数据表名，如果数据表名错误，默认为第一个数据表名public static DataTable In putFromExcel(stri ng ExcelFilePath, stri ng TableName)if (!File.Exists(ExcelFilePath)throw new Exception(Excel 文件不存在！);/如果数据表名不存在，则数据表名为ExceI文件的第一个数据表ArrayList TableList = new ArrayList();TableList = GetExcelTables(ExcelFilePath);if (TableName .In dexOf(TableName) 0)TableName = TableList0.ToStri ng().Trim();DataTa