本文实例讲述了c#自定义导出数据到excel的类。分享给大家供大家参考。具体如下：
c#自定义excel操作类，可以用于将datatable导出到excel文件，从excel文件读取数据。
我要评论
using system;
using system.io;
using system.data;
using system.collections;
using system.data.oledb;
using system.web;
using system.web.ui;
using system.web.ui.webcontrols;
namespace dotnet.utilities
{
/// <summary>
/// excel操作类
/// </summary>
/// microsoft excel 11.0 object library
public class excelhelper
{
#region 数据导出至excel文件
/// </summary>
/// 导出excel文件，自动返回可下载的文件流
/// </summary>
public static void datatable1excel(system.data.datatable dtdata)
{
gridview gvexport = null;
httpcontext curcontext = httpcontext.current;
stringwriter strwriter = null;
htmltextwriter htmlwriter = 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 = new stringwriter();
htmlwriter = new htmltextwriter(strwriter);
gvexport = new gridview();
gvexport.datasource = dtdata.defaultview;
gvexport.allowpaging = false;
gvexport.databind();
gvexport.rendercontrol(htmlwriter);
curcontext.response.write("<meta http-equiv=\"content-type\" content=\"text/html;charset=gb2312\"/>" + strwriter.tostring());
curcontext.response.end();
}
}
/// <summary>
/// 导出excel文件，转换为可读模式
/// </summary>
public static void datatable2excel(system.data.datatable dtdata)
{
datagrid dgexport = null;
httpcontext curcontext = httpcontext.current;
stringwriter strwriter = null;
htmltextwriter htmlwriter = null;
if (dtdata != null)
{
curcontext.response.contenttype = "application/vnd.ms-excel";
curcontext.response.contentencoding = system.text.encoding.utf8;
curcontext.response.charset = "";
strwriter = new stringwriter();
htmlwriter = new htmltextwriter(strwriter);
dgexport = new datagrid();
dgexport.datasource = dtdata.defaultview;
dgexport.allowpaging = false;
dgexport.databind();
dgexport.rendercontrol(htmlwriter);
curcontext.response.write(strwriter.tostring());
curcontext.response.end();
}
}
/// <summary>
/// 导出excel文件，并自定义文件名
/// </summary>
public static void datatable3excel(system.data.datatable dtdata, string filename)
{
gridview dgexport = null;
httpcontext curcontext = httpcontext.current;
stringwriter strwriter = null;
htmltextwriter htmlwriter = 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 = "application nd.ms-excel";
curcontext.response.contentencoding = system.text.encoding.utf8;
curcontext.response.charset = "gb2312";
strwriter = new stringwriter();
htmlwriter = new htmltextwriter(strwriter);
dgexport = new gridview();
dgexport.datasource = dtdata.defaultview;
dgexport.allowpaging = false;
dgexport.databind();
dgexport.rendercontrol(htmlwriter);
curcontext.response.write(strwriter.tostring());
curcontext.response.end();
}
}
/// <summary>
/// 将数据导出至excel文件
/// </summary>
/// <param name="table">datatable对象</param>
/// <param name="excelfilepath">excel文件路径</param>
public static bool outputtoexcel(datatable table, string excelfilepath)
{
if (file.exists(excelfilepath))
{
throw new exception("该文件已经存在！");
}
if ((table.tablename.trim().length == 0) || (table.tablename.tolower() == "table"))
{
table.tablename = "sheet1";
}
//数据表的列数
int colcount = table.columns.count;
//用于记数，实例化参数时的序号
int i = 0;
//创建参数
oledbparameter[] para = new oledbparameter[colcount];
//创建表结构的sql语句
string tablestructstr = @"create table " + table.tablename + "(";
//连接字符串
string connstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath + ";extended properties=excel 8.0;";
oledbconnection objconn = new oledbconnection(connstring);
//创建表结构
oledbcommand objcmd = new oledbcommand();
//数据类型集合
arraylist datatypelist = new arraylist();
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 (datacolumn col in table.columns)
{
//如果列属于数字列，则设置该列的数据类型为double
if (datatypelist.indexof(col.datatype.tostring()) >= 0)
{
para[i] = new oledbparameter("@" + 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] = new oledbparameter("@" + 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 (exception exp)
{
throw exp;
}
//插入记录的sql语句
string insertsql_1 = "insert into " + table.tablename + " (";
string insertsql_2 = " values (";
string insertsql = "";
//遍历所有列，用于插入记录，在此创建插入记录的sql语句
for (int colid = 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 (int rowid = 0; rowid < table.rows.count; rowid++)
{
for (int colid = 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 (exception exp)
{
string str = exp.message;
}
}
try
{
if (objconn.state == connectionstate.open)
{
objconn.close();
}
}
catch (exception exp)
{
throw exp;
}
return true;
}
/// <summary>
/// 将数据导出至excel文件
/// </summary>
/// <param name="table">datatable对象</param>
/// <param name="columns">要导出的数据列集合</param>
/// <param name="excelfilepath">excel文件路径</param>
public static bool outputtoexcel(datatable table, arraylist columns, string excelfilepath)
{
if (file.exists(excelfilepath))
{
throw new exception("该文件已经存在！");
}
//如果数据列数大于表的列数，取数据表的所有列
if (columns.count > table.columns.count)
{
for (int s = table.columns.count + 1; s <= columns.count; s++)
{
columns.removeat(s); //移除数据表列数后的所有列
}
}
//遍历所有的数据列，如果有数据列的数据类型不是 datacolumn，则将它移除
datacolumn column = new datacolumn();
for (int j = 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";
}
//数据表的列数
int colcount = columns.count;
//创建参数
oledbparameter[] para = new oledbparameter[colcount];
//创建表结构的sql语句
string tablestructstr = @"create table " + table.tablename + "(";
//连接字符串
string connstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath + ";extended properties=excel 8.0;";
oledbconnection objconn = new oledbconnection(connstring);
//创建表结构
oledbcommand objcmd = new oledbcommand();
//数据类型集合
arraylist datatypelist = new arraylist();
datatypelist.add("system.decimal");
datatypelist.add("system.double");
datatypelist.add("system.int16");
datatypelist.add("system.int32");
datatypelist.add("system.int64");
datatypelist.add("system.single");
datacolumn col = new datacolumn();
//遍历数据表的所有列，用于创建表结构
for (int k = 0; k < colcount; k++)
{
col = (datacolumn)columns[k];
//列的数据类型是数字型
if (datatypelist.indexof(col.datatype.tostring().trim()) >= 0)
{
para[k] = new oledbparameter("@" + 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] = new oledbparameter("@" + 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 (exception exp)
{
throw exp;
}
//插入记录的sql语句
string insertsql_1 = "insert into " + table.tablename + " (";
string insertsql_2 = " values (";
string insertsql = "";
//遍历所有列，用于插入记录，在此创建插入记录的sql语句
for (int colid = 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;
//遍历数据表的所有数据行
datacolumn datacol = new datacolumn();
for (int rowid = 0; rowid < table.rows.count; rowid++)
{
for (int colid = 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 (exception exp)
{
string str = exp.message;
}
}
try
{
if (objconn.state == connectionstate.open)
{
objconn.close();
}
}
catch (exception exp)
{
throw exp;
}
return true;
}
#endregion
/// <summary>
/// 获取excel文件数据表列表
/// </summary>
public static arraylist getexceltables(string excelfilename)
{
datatable dt = new datatable();
arraylist tableslist = new arraylist();
if (file.exists(excelfilename))
{
using (oledbconnection conn = new oledbconnection("provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" + excelfilename))
{
try
{
conn.open();
dt = conn.getoledbschematable(oledbschemaguid.tables, new object[] { null, null, null, "table" });
}
catch (exception exp)
{
throw exp;
}
//获取数据表个数
int tablecount = dt.rows.count;
for (int i = 0; i < tablecount; i++)
{
string tablename = dt.rows[i][2].tostring().trim().trimend('$');
if (tableslist.indexof(tablename) < 0)
{
tableslist.add(tablename);
}
}
}
}
return tableslist;
}
/// <summary>
/// 将excel文件导出至datatable(第一行作为表头)
/// </summary>
/// <param name="excelfilepath">excel文件路径</param>
/// <param name="tablename">数据表名，如果数据表名错误，默认为第一个数据表名</param>
public static datatable inputfromexcel(string excelfilepath, string tablename)
{
if (!file.exists(excelfilepath))
{
throw new exception("excel文件不存在！");
}
//如果数据表名不存在，则数据表名为excel文件的第一个数据表
arraylist tablelist = new arraylist();
tablelist = getexceltables(excelfilepath);
if (tablename.indexof(tablename) < 0)
{
tablename = tablelist[0].tostring().trim();
}
datatable table = new datatable();
oledbconnection dbcon = new oledbconnection(@"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath + ";extended properties=excel 8.0");
oledbcommand cmd = new oledbcommand("select * from [" + tablename + "$]", dbcon);
oledbdataadapter adapter = new oledbdataadapter(cmd);
try
{
if (dbcon.state == connectionstate.closed)
{
dbcon.open();
}
adapter.fill(table);
}
catch (exception exp)
{
throw exp;
}
finally
{
if (dbcon.state == connectionstate.open)
{
dbcon.close();
}
}
return table;
}
/// <summary>
/// 获取excel文件指定数据表的数据列表
/// </summary>
/// <param name="excelfilename">excel文件名</param>
/// <param name="tablename">数据表名</param>
public static arraylist getexceltablecolumns(string excelfilename, string tablename)
{
datatable dt = new datatable();
arraylist colslist = new arraylist();
if (file.exists(excelfilename))
{
using (oledbconnection conn = new oledbconnection("provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" + excelfilename))
{
conn.open();
dt = conn.getoledbschematable(oledbschemaguid.columns, new object[] { null, null, tablename, null });
//获取列个数
int colcount = dt.rows.count;
for (int i = 0; i < colcount; i++)
{
string colname = dt.rows[i]["column_name"].tostring().trim();
colslist.add(colname);
}
}
}
return colslist;
}
}
}
希望本文所述对大家的c#程序设计有所帮助。
如对本文有疑问， 点击进行留言回复！！
使用Visual Studio2019创建C#项目(窗体应用程序、控制台应用程序、Web应用程序)
C#实现获取本地内网(局域网)和外网(公网)IP地址的方法分析
网友评论