C#自定义导出数据到Excel的类实例
本文实例讲述了C#自定义导出数据到Excel的类。分享给大家供大家参考。具体如下:
C#自定义Excel操作类,可以用于将DataTable导出到Excel文件,从Excel文件读取数据。
usingSystem; usingSystem.IO; usingSystem.Data; usingSystem.Collections; usingSystem.Data.OleDb; usingSystem.Web; usingSystem.Web.UI; usingSystem.Web.UI.WebControls; namespaceDotNet.Utilities { ///<summary> ///Excel操作类 ///</summary> ///MicrosoftExcel11.0ObjectLibrary publicclassExcelHelper { #region数据导出至Excel文件 ///</summary> ///导出Excel文件,自动返回可下载的文件流 ///</summary> publicstaticvoidDataTable1Excel(System.Data.DataTabledtData) { GridViewgvExport=null; HttpContextcurContext=HttpContext.Current; StringWriterstrWriter=null; HtmlTextWriterhtmlWriter=null; if(dtData!=null) { curContext.Response.ContentType="application/vnd.ms-excel"; curContext.Response.ContentEncoding=System.Text.Encoding.GetEncoding("gb2312"); curContext.Response.Charset="utf-8"; strWriter=newStringWriter(); htmlWriter=newHtmlTextWriter(strWriter); gvExport=newGridView(); gvExport.DataSource=dtData.DefaultView; gvExport.AllowPaging=false; gvExport.DataBind(); gvExport.RenderControl(htmlWriter); curContext.Response.Write("<metahttp-equiv=\"Content-Type\"content=\"text/html;charset=gb2312\"/>"+strWriter.ToString()); curContext.Response.End(); } } ///<summary> ///导出Excel文件,转换为可读模式 ///</summary> publicstaticvoidDataTable2Excel(System.Data.DataTabledtData) { DataGriddgExport=null; HttpContextcurContext=HttpContext.Current; StringWriterstrWriter=null; HtmlTextWriterhtmlWriter=null; if(dtData!=null) { curContext.Response.ContentType="application/vnd.ms-excel"; curContext.Response.ContentEncoding=System.Text.Encoding.UTF8; curContext.Response.Charset=""; strWriter=newStringWriter(); htmlWriter=newHtmlTextWriter(strWriter); dgExport=newDataGrid(); dgExport.DataSource=dtData.DefaultView; dgExport.AllowPaging=false; dgExport.DataBind(); dgExport.RenderControl(htmlWriter); curContext.Response.Write(strWriter.ToString()); curContext.Response.End(); } } ///<summary> ///导出Excel文件,并自定义文件名 ///</summary> publicstaticvoidDataTable3Excel(System.Data.DataTabledtData,StringFileName) { GridViewdgExport=null; HttpContextcurContext=HttpContext.Current; StringWriterstrWriter=null; HtmlTextWriterhtmlWriter=null; if(dtData!=null) { HttpUtility.UrlEncode(FileName,System.Text.Encoding.UTF8); curContext.Response.AddHeader("content-disposition","attachment;filename="+HttpUtility.UrlEncode(FileName,System.Text.Encoding.UTF8)+".xls"); curContext.Response.ContentType="applicationnd.ms-excel"; curContext.Response.ContentEncoding=System.Text.Encoding.UTF8; curContext.Response.Charset="GB2312"; strWriter=newStringWriter(); htmlWriter=newHtmlTextWriter(strWriter); dgExport=newGridView(); dgExport.DataSource=dtData.DefaultView; dgExport.AllowPaging=false; dgExport.DataBind(); dgExport.RenderControl(htmlWriter); curContext.Response.Write(strWriter.ToString()); curContext.Response.End(); } } ///<summary> ///将数据导出至Excel文件 ///</summary> ///<paramname="Table">DataTable对象</param> ///<paramname="ExcelFilePath">Excel文件路径</param> publicstaticboolOutputToExcel(DataTableTable,stringExcelFilePath) { if(File.Exists(ExcelFilePath)) { thrownewException("该文件已经存在!"); } if((Table.TableName.Trim().Length==0)||(Table.TableName.ToLower()=="table")) { Table.TableName="Sheet1"; } //数据表的列数 intColCount=Table.Columns.Count; //用于记数,实例化参数时的序号 inti=0; //创建参数 OleDbParameter[]para=newOleDbParameter[ColCount]; //创建表结构的SQL语句 stringTableStructStr=@"CreateTable"+Table.TableName+"("; //连接字符串 stringconnString=@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+ExcelFilePath+";ExtendedProperties=Excel8.0;"; OleDbConnectionobjConn=newOleDbConnection(connString); //创建表结构 OleDbCommandobjCmd=newOleDbCommand(); //数据类型集合 ArrayListDataTypeList=newArrayList(); DataTypeList.Add("System.Decimal"); DataTypeList.Add("System.Double"); DataTypeList.Add("System.Int16"); DataTypeList.Add("System.Int32"); DataTypeList.Add("System.Int64"); DataTypeList.Add("System.Single"); //遍历数据表的所有列,用于创建表结构 foreach(DataColumncolinTable.Columns) { //如果列属于数字列,则设置该列的数据类型为double if(DataTypeList.IndexOf(col.DataType.ToString())>=0) { para[i]=newOleDbParameter("@"+col.ColumnName,OleDbType.Double); objCmd.Parameters.Add(para[i]); //如果是最后一列 if(i+1==ColCount) { TableStructStr+=col.ColumnName+"double)"; } else { TableStructStr+=col.ColumnName+"double,"; } } else { para[i]=newOleDbParameter("@"+col.ColumnName,OleDbType.VarChar); objCmd.Parameters.Add(para[i]); //如果是最后一列 if(i+1==ColCount) { TableStructStr+=col.ColumnName+"varchar)"; } else { TableStructStr+=col.ColumnName+"varchar,"; } } i++; } //创建Excel文件及文件结构 try { objCmd.Connection=objConn; objCmd.CommandText=TableStructStr; if(objConn.State==ConnectionState.Closed) { objConn.Open(); } objCmd.ExecuteNonQuery(); } catch(Exceptionexp) { throwexp; } //插入记录的SQL语句 stringInsertSql_1="Insertinto"+Table.TableName+"("; stringInsertSql_2="Values("; stringInsertSql=""; //遍历所有列,用于插入记录,在此创建插入记录的SQL语句 for(intcolID=0;colID<ColCount;colID++) { if(colID+1==ColCount)//最后一列 { InsertSql_1+=Table.Columns[colID].ColumnName+")"; InsertSql_2+="@"+Table.Columns[colID].ColumnName+")"; } else { InsertSql_1+=Table.Columns[colID].ColumnName+","; InsertSql_2+="@"+Table.Columns[colID].ColumnName+","; } } InsertSql=InsertSql_1+InsertSql_2; //遍历数据表的所有数据行 for(introwID=0;rowID<Table.Rows.Count;rowID++) { for(intcolID=0;colID<ColCount;colID++) { if(para[colID].DbType==DbType.Double&&Table.Rows[rowID][colID].ToString().Trim()=="") { para[colID].Value=0; } else { para[colID].Value=Table.Rows[rowID][colID].ToString().Trim(); } } try { objCmd.CommandText=InsertSql; objCmd.ExecuteNonQuery(); } catch(Exceptionexp) { stringstr=exp.Message; } } try { if(objConn.State==ConnectionState.Open) { objConn.Close(); } } catch(Exceptionexp) { throwexp; } returntrue; } ///<summary> ///将数据导出至Excel文件 ///</summary> ///<paramname="Table">DataTable对象</param> ///<paramname="Columns">要导出的数据列集合</param> ///<paramname="ExcelFilePath">Excel文件路径</param> publicstaticboolOutputToExcel(DataTableTable,ArrayListColumns,stringExcelFilePath) { if(File.Exists(ExcelFilePath)) { thrownewException("该文件已经存在!"); } //如果数据列数大于表的列数,取数据表的所有列 if(Columns.Count>Table.Columns.Count) { for(ints=Table.Columns.Count+1;s<=Columns.Count;s++) { Columns.RemoveAt(s);//移除数据表列数后的所有列 } } //遍历所有的数据列,如果有数据列的数据类型不是DataColumn,则将它移除 DataColumncolumn=newDataColumn(); for(intj=0;j<Columns.Count;j++) { try { column=(DataColumn)Columns[j]; } catch(Exception) { Columns.RemoveAt(j); } } if((Table.TableName.Trim().Length==0)||(Table.TableName.ToLower()=="table")) { Table.TableName="Sheet1"; } //数据表的列数 intColCount=Columns.Count; //创建参数 OleDbParameter[]para=newOleDbParameter[ColCount]; //创建表结构的SQL语句 stringTableStructStr=@"CreateTable"+Table.TableName+"("; //连接字符串 stringconnString=@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+ExcelFilePath+";ExtendedProperties=Excel8.0;"; OleDbConnectionobjConn=newOleDbConnection(connString); //创建表结构 OleDbCommandobjCmd=newOleDbCommand(); //数据类型集合 ArrayListDataTypeList=newArrayList(); DataTypeList.Add("System.Decimal"); DataTypeList.Add("System.Double"); DataTypeList.Add("System.Int16"); DataTypeList.Add("System.Int32"); DataTypeList.Add("System.Int64"); DataTypeList.Add("System.Single"); DataColumncol=newDataColumn(); //遍历数据表的所有列,用于创建表结构 for(intk=0;k<ColCount;k++) { col=(DataColumn)Columns[k]; //列的数据类型是数字型 if(DataTypeList.IndexOf(col.DataType.ToString().Trim())>=0) { para[k]=newOleDbParameter("@"+col.Caption.Trim(),OleDbType.Double); objCmd.Parameters.Add(para[k]); //如果是最后一列 if(k+1==ColCount) { TableStructStr+=col.Caption.Trim()+"Double)"; } else { TableStructStr+=col.Caption.Trim()+"Double,"; } } else { para[k]=newOleDbParameter("@"+col.Caption.Trim(),OleDbType.VarChar); objCmd.Parameters.Add(para[k]); //如果是最后一列 if(k+1==ColCount) { TableStructStr+=col.Caption.Trim()+"VarChar)"; } else { TableStructStr+=col.Caption.Trim()+"VarChar,"; } } } //创建Excel文件及文件结构 try { objCmd.Connection=objConn; objCmd.CommandText=TableStructStr; if(objConn.State==ConnectionState.Closed) { objConn.Open(); } objCmd.ExecuteNonQuery(); } catch(Exceptionexp) { throwexp; } //插入记录的SQL语句 stringInsertSql_1="Insertinto"+Table.TableName+"("; stringInsertSql_2="Values("; stringInsertSql=""; //遍历所有列,用于插入记录,在此创建插入记录的SQL语句 for(intcolID=0;colID<ColCount;colID++) { if(colID+1==ColCount)//最后一列 { InsertSql_1+=Columns[colID].ToString().Trim()+")"; InsertSql_2+="@"+Columns[colID].ToString().Trim()+")"; } else { InsertSql_1+=Columns[colID].ToString().Trim()+","; InsertSql_2+="@"+Columns[colID].ToString().Trim()+","; } } InsertSql=InsertSql_1+InsertSql_2; //遍历数据表的所有数据行 DataColumnDataCol=newDataColumn(); for(introwID=0;rowID<Table.Rows.Count;rowID++) { for(intcolID=0;colID<ColCount;colID++) { //因为列不连续,所以在取得单元格时不能用行列编号,列需得用列的名称 DataCol=(DataColumn)Columns[colID]; if(para[colID].DbType==DbType.Double&&Table.Rows[rowID][DataCol.Caption].ToString().Trim()=="") { para[colID].Value=0; } else { para[colID].Value=Table.Rows[rowID][DataCol.Caption].ToString().Trim(); } } try { objCmd.CommandText=InsertSql; objCmd.ExecuteNonQuery(); } catch(Exceptionexp) { stringstr=exp.Message; } } try { if(objConn.State==ConnectionState.Open) { objConn.Close(); } } catch(Exceptionexp) { throwexp; } returntrue; } #endregion ///<summary> ///获取Excel文件数据表列表 ///</summary> publicstaticArrayListGetExcelTables(stringExcelFileName) { DataTabledt=newDataTable(); ArrayListTablesList=newArrayList(); if(File.Exists(ExcelFileName)) { using(OleDbConnectionconn=newOleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties=Excel8.0;DataSource="+ExcelFileName)) { try { conn.Open(); dt=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,newobject[]{null,null,null,"TABLE"}); } catch(Exceptionexp) { throwexp; } //获取数据表个数 inttablecount=dt.Rows.Count; for(inti=0;i<tablecount;i++) { stringtablename=dt.Rows[i][2].ToString().Trim().TrimEnd('$'); if(TablesList.IndexOf(tablename)<0) { TablesList.Add(tablename); } } } } returnTablesList; } ///<summary> ///将Excel文件导出至DataTable(第一行作为表头) ///</summary> ///<paramname="ExcelFilePath">Excel文件路径</param> ///<paramname="TableName">数据表名,如果数据表名错误,默认为第一个数据表名</param> publicstaticDataTableInputFromExcel(stringExcelFilePath,stringTableName) { if(!File.Exists(ExcelFilePath)) { thrownewException("Excel文件不存在!"); } //如果数据表名不存在,则数据表名为Excel文件的第一个数据表 ArrayListTableList=newArrayList(); TableList=GetExcelTables(ExcelFilePath); if(TableName.IndexOf(TableName)<0) { TableName=TableList[0].ToString().Trim(); } DataTabletable=newDataTable(); OleDbConnectiondbcon=newOleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+ExcelFilePath+";ExtendedProperties=Excel8.0"); OleDbCommandcmd=newOleDbCommand("select*from["+TableName+"$]",dbcon); OleDbDataAdapteradapter=newOleDbDataAdapter(cmd); try { if(dbcon.State==ConnectionState.Closed) { dbcon.Open(); } adapter.Fill(table); } catch(Exceptionexp) { throwexp; } finally { if(dbcon.State==ConnectionState.Open) { dbcon.Close(); } } returntable; } ///<summary> ///获取Excel文件指定数据表的数据列表 ///</summary> ///<paramname="ExcelFileName">Excel文件名</param> ///<paramname="TableName">数据表名</param> publicstaticArrayListGetExcelTableColumns(stringExcelFileName,stringTableName) { DataTabledt=newDataTable(); ArrayListColsList=newArrayList(); if(File.Exists(ExcelFileName)) { using(OleDbConnectionconn=newOleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;ExtendedProperties=Excel8.0;DataSource="+ExcelFileName)) { conn.Open(); dt=conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,newobject[]{null,null,TableName,null}); //获取列个数 intcolcount=dt.Rows.Count; for(inti=0;i<colcount;i++) { stringcolname=dt.Rows[i]["Column_Name"].ToString().Trim(); ColsList.Add(colname); } } } returnColsList; } } }
希望本文所述对大家的C#程序设计有所帮助。