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#程序设计有所帮助。