ASP.NET中 Execl导出的六种方法实例
/// <summary>
/// 导出excel
/// </summary>
/// <param name="page"></param>
/// <param name="dt"></param>
//方法一：
public void importexcel(page page, datatable dt)
{
try
{
string filename = guid.newguid().tostring() + ".xls";
string webfilepath = page.server.mappath("/" + filename);
createexcelfile(webfilepath, dt);
using (filestream fs = new filestream(webfilepath, filemode.openorcreate))
{
//让用户输入下载的本地地址
page.response.clear();
page.response.buffer = true;
page.response.charset = "gb2312";
//page.response.appendheader("content-disposition", "attachment;filename=monitorresult.xls");
page.response.appendheader("content-disposition", "attachment;filename=" + filename);
page.response.contentencoding = system.text.encoding.getencoding("gb2312");
page.response.contenttype = "application/ms-excel";
// 读取excel数据到内存
byte[] buffer = new byte[fs.length - 1];
fs.read(buffer, 0, (int)fs.length - 1);
// 写到aspx页面
page.response.binarywrite(buffer);
page.response.flush();
//this.applicationinstance.completerequest(); //停止页的执行
fs.close();
fs.dispose();
//删除临时文件
file.delete(webfilepath);
}
}
catch (exception ex)
{
throw ex;
}
}
方法二：
public void importexcel(page page, dataset ds)
{
try
{
string filename = guid.newguid().tostring() + ".xls";
string webfilepath = page.server.mappath("/" + filename);
createexcelfile(webfilepath, ds);
using (filestream fs = new filestream(webfilepath, filemode.openorcreate))
{
//让用户输入下载的本地地址
page.response.clear();
page.response.buffer = true;
page.response.charset = "gb2312";
//page.response.appendheader("content-disposition", "attachment;filename=monitorresult.xls");
page.response.appendheader("content-disposition", "attachment;filename=" + filename);
page.response.contentencoding = system.text.encoding.getencoding("gb2312");
page.response.contenttype = "application/ms-excel";
// 读取excel数据到内存
byte[] buffer = new byte[fs.length - 1];
fs.read(buffer, 0, (int)fs.length - 1);
// 写到aspx页面
page.response.binarywrite(buffer);
page.response.flush();
//this.applicationinstance.completerequest(); //停止页的执行
fs.close();
fs.dispose();
//删除临时文件
file.delete(webfilepath);
}
}
catch (exception ex)
{
throw ex;
}
}
方法三：
public void importexcel(page page, datatable dt1, datatable dt2, string conditions)
{
try
{
string filename = guid.newguid().tostring() + ".xls";
string webfilepath = page.server.mappath("/" + filename);
createexcelfile(webfilepath, dt1, dt2, conditions);
using (filestream fs = new filestream(webfilepath, filemode.openorcreate))
{
//让用户输入下载的本地地址
page.response.clear();
page.response.buffer = true;
page.response.charset = "gb2312";
//page.response.appendheader("content-disposition", "attachment;filename=monitorresult.xls");
page.response.appendheader("content-disposition", "attachment;filename=" + filename);
page.response.contentencoding = system.text.encoding.getencoding("gb2312");
page.response.contenttype = "application/ms-excel";
// 读取excel数据到内存
byte[] buffer = new byte[fs.length - 1];
fs.read(buffer, 0, (int)fs.length - 1);
// 写到aspx页面
page.response.binarywrite(buffer);
page.response.flush();
//this.applicationinstance.completerequest(); //停止页的执行
fs.close();
fs.dispose();
//删除临时文件
file.delete(webfilepath);
}
}
catch (exception ex)
{
throw ex;
}
}
方法四：
private void createexcelfile(string filepath, datatable dt)
{
if (file.exists(filepath))
{
file.delete(filepath);
}
oledbconnection oledbconn = new oledbconnection();
oledbcommand oledbcmd = new oledbcommand();
try
{
string ssql = "";
oledbconn.connectionstring = "provider=microsoft.ace.oledb.12.0;data source=" + filepath + @";extended properties=""excel 8.0;hdr=yes;""";
oledbconn.open();
oledbcmd.commandtype = commandtype.text;
oledbcmd.connection = oledbconn;
//写列名
ssql = "create table sheet1(";
for (int i = 0; i < dt.columns.count; i++)
{
if (i < dt.columns.count - 1)
{
if (dt.columns[i].datatype.name == "string")
{
ssql += "[" + dt.columns[i].columnname + "] text,";
}
else if (dt.columns[i].datatype.name == "datetime")
{
ssql += "[" + dt.columns[i].columnname + "] datetime,";
}
else
{
ssql += "[" + dt.columns[i].columnname + "] decimal,";
}
}
else
{
if (dt.columns[i].datatype.name == "string")
{
ssql += "[" + dt.columns[i].columnname + "] text)";
}
else if (dt.columns[i].datatype.name == "datetime")
{
ssql += "[" + dt.columns[i].columnname + "] datetime)";
}
else
{
ssql += "[" + dt.columns[i].columnname + "] decimal)";
}
}
}
oledbcmd.commandtext = ssql;
oledbcmd.executenonquery();
for (int j = 0; j < dt.rows.count; j++)
{
ssql = "insert into sheet1 values(";
for (int i = 0; i < dt.columns.count; i++)
{
if (i < dt.columns.count - 1)
{
if (dbnull.value.equals(dt.rows[j][i]))
{
ssql += "null,";
}
else
{
if (dt.columns[i].datatype.name == "decimal")
{
ssql += dt.rows[j][i].tostring() + ",";
}
else
{
ssql += "'" + dt.rows[j][i].tostring() + "',";
}
}
}
else
if (dbnull.value.equals(dt.rows[j][i]))
{
ssql += "null)";
}
else
{
if (dt.columns[i].datatype.name == "decimal")
{
ssql += dt.rows[j][i].tostring() + ")";
}
else
{
ssql += "'" + dt.rows[j][i].tostring() + "')";
}
}
}
oledbcmd.commandtext = ssql;
oledbcmd.executenonquery();
}
}
catch (system.exception ex)
{
throw ex;
}
finally
{
//断开连接
oledbcmd.dispose();
oledbconn.close();
oledbconn.dispose();
}
}
方法五：
private void createexcelfile(string filepath, dataset ds)
{
if (file.exists(filepath))
{
file.delete(filepath);
}
oledbconnection oledbconn = new oledbconnection();
oledbcommand oledbcmd = new oledbcommand();
try
{
string ssql = "";
oledbconn.connectionstring = "provider=microsoft.ace.oledb.12.0;data source=" + filepath + @";extended properties=""excel 8.0;hdr=yes;""";
oledbconn.open();
oledbcmd.commandtype = commandtype.text;
oledbcmd.connection = oledbconn;
//写列名
for(int k=0;k<ds.tables.count;k++)
{
datatable dt = ds.tables[k];
ssql = "create table sheet" + (k + 1).tostring() + "(";
for (int i = 0; i < dt.columns.count; i++)
{
if (i < dt.columns.count - 1)
{
if (dt.columns[i].datatype.name == "string" || dt.columns[i].datatype.name=="guid")
{
ssql += "["+dt.columns[i].columnname + "] text,";
}
else if (dt.columns[i].datatype.name == "datetime")
{
ssql += "[" + dt.columns[i].columnname + "] datetime,";
}
else
{
ssql += "[" + dt.columns[i].columnname + "] decimal,";
}
}
else
{
if (dt.columns[i].datatype.name == "string")
{
ssql += "[" + dt.columns[i].columnname + "] text)";
}
else if (dt.columns[i].datatype.name == "datetime")
{
ssql += "[" + dt.columns[i].columnname + "] datetime)";
}
else
{
ssql += "[" + dt.columns[i].columnname + "] decimal)";
}
}
}
oledbcmd.commandtext = ssql;
oledbcmd.executenonquery(); for (int j = 0; j < dt.rows.count; j++)
{
ssql = "insert into sheet" + (k + 1).tostring() + " values(";
for (int i = 0; i < dt.columns.count; i++)
{
if (i < dt.columns.count - 1)
{
if (dbnull.value.equals(dt.rows[j][i]))
{
ssql += "null,";
}
else
{
if (dt.columns[i].datatype.name == "decimal")
{
ssql += dt.rows[j][i].tostring() + ",";
}
else
{
ssql += "'" + dt.rows[j][i].tostring().replace("'", "''") + "',";
}
}
}
else
if (dbnull.value.equals(dt.rows[j][i]))
{
ssql += "null)";
}
else
{
if (dt.columns[i].datatype.name == "decimal")
{
ssql += dt.rows[j][i].tostring() + ")";
}
else
{
ssql += "'" + dt.rows[j][i].tostring().replace("'","''") + "')";
}
}
}
oledbcmd.commandtext = ssql;
oledbcmd.executenonquery();
}
}
}
catch (system.exception ex)
{
throw ex;
}
finally
{
//断开连接
oledbcmd.dispose();
oledbconn.close();
oledbconn.dispose();
}
}
方法六：
private void createexcelfile(string filepath, datatable dt1,datatable dt2,string conditions)
{
if (file.exists(filepath))
{
file.delete(filepath);
}
oledbconnection oledbconn = new oledbconnection();
oledbcommand oledbcmd = new oledbcommand();
try
{
string ssql = "";
oledbconn.connectionstring = "provider=microsoft.ace.oledb.12.0;data source=" + filepath + @";extended properties=""excel 8.0;hdr=yes;""";
oledbconn.open();
oledbcmd.commandtype = commandtype.text;
oledbcmd.connection = oledbconn;
//写列名
ssql = "create table sheet1(";
datatable dt = dt1.copy();
dt.columns.remove("mguid");
for (int i = 0; i < dt.columns.count; i++)
{
if (i < dt.columns.count - 1)
{
if (dt.columns[i].datatype.name == "string")
{
ssql += "[" + dt.columns[i].columnname + "] text,";
}
else if (dt.columns[i].datatype.name == "datetime")
{
ssql += "[" + dt.columns[i].columnname + "] datetime,";
}
else
{
ssql += "[" + dt.columns[i].columnname + "] decimal,";
}
}
else
{
if (dt.columns[i].datatype.name == "string")
{
ssql += "[" + dt.columns[i].columnname + "] text)";
}
else if (dt.columns[i].datatype.name == "datetime")
{
ssql += "[" + dt.columns[i].columnname + "] datetime)";
}
else
{
ssql += "[" + dt.columns[i].columnname + "] decimal)";
}
}
}
oledbcmd.commandtext = ssql;
oledbcmd.executenonquery();
dataview dv = new dataview();
dv.table = dt;
dataview dv1 = new dataview();
dv1.table = dt1;
if (conditions != "")
{
dv.rowfilter = conditions;
dv1.rowfilter = conditions;
}
dt = dv.totable();
dt1 = dv1.totable();
string mguids = "";
for (int j = 0; j < dt.rows.count; j++)
{
mguids += ",'" + dt1.rows[j]["mguid"].tostring() + "'";
ssql = "insert into sheet1 values(";
for (int i = 0; i < dt.columns.count; i++)
{
if (i < dt.columns.count - 1)
{
if (dbnull.value.equals(dt.rows[j][i]))
{
ssql += "null,";
}
else
{
if (dt.columns[i].datatype.name == "decimal")
{
ssql += dt.rows[j][i].tostring() + ",";
}
else
{
ssql += "'" + dt.rows[j][i].tostring() + "',";
}
}
}
else
if (dbnull.value.equals(dt.rows[j][i]))
{
ssql += "null)";
}
else
{
if (dt.columns[i].datatype.name == "decimal")
{
ssql += dt.rows[j][i].tostring() + ")";
}
else
{
ssql += "'" + dt.rows[j][i].tostring() + "')";
}
}
}
oledbcmd.commandtext = ssql;
oledbcmd.executenonquery();
}
if (dt2 != null)
{
ssql = "create table sheet21(";
dt = dt2.copy();
dt.columns.remove("mguid");
dt.columns.remove("dguid");
for (int i = 0; i < dt.columns.count; i++)
{
if (i < dt.columns.count - 1)
{
if (dt.columns[i].datatype.name == "string")
{
ssql += "[" + dt.columns[i].columnname + "] text,";
}
else if (dt.columns[i].datatype.name == "datetime")
{
ssql += "[" + dt.columns[i].columnname + "] datetime,";
}
else
{
ssql += "[" + dt.columns[i].columnname + "] decimal,";
}
}
else
{
if (dt.columns[i].datatype.name == "string")
{
ssql += "[" + dt.columns[i].columnname + "] text)";
}
else if (dt.columns[i].datatype.name == "datetime")
{
ssql += "[" + dt.columns[i].columnname + "] datetime)";
}
else
{
ssql += "[" + dt.columns[i].columnname + "] decimal)";
}
}
}
oledbcmd.commandtext = ssql;
oledbcmd.executenonquery();
dv = new dataview();
dv.table = dt2;
if (mguids != "")
{
dv.rowfilter = "mguid in(" + mguids.substring(1) + ")";
}
dt = dv.totable();
for (int j = 0; j < dt.rows.count; j++)
{
ssql = "insert into sheet1 values(";
for (int i = 0; i < dt.columns.count; i++)
{
if (i < dt.columns.count - 1)
{
if (dbnull.value.equals(dt.rows[j][i]))
{
ssql += "null,";
}
else
{
if (dt.columns[i].datatype.name == "decimal")
{
ssql += dt.rows[j][i].tostring() + ",";
}
else
{
ssql += "'" + dt.rows[j][i].tostring() + "',";
}
}
}
else
if (dbnull.value.equals(dt.rows[j][i]))
{
ssql += "null)";
}
else
{
if (dt.columns[i].datatype.name == "decimal")
{
ssql += dt.rows[j][i].tostring() + ")";
}
else
{
ssql += "'" + dt.rows[j][i].tostring() + "')";
}
}
}
oledbcmd.commandtext = ssql;
oledbcmd.executenonquery();
}
}
}
catch (system.exception ex)
{
throw ex;
}
finally
{
//断开连接
oledbcmd.dispose();
oledbconn.close();
oledbconn.dispose();
}
}