当前位置： 移动技术网 > IT编程>开发语言>.net > asp.net生成Excel并导出下载五种实现方法

asp.net生成Excel并导出下载五种实现方法

2017年12月12日  | 移动技术网IT编程  | 我要评论

亵渎小说,果脯,唐兴和

方法一 通过gridview(简评：方法比较简单，但是只适合生成格式简单的excel，且无法保留vba代码),页面无刷新

aspx.cs部分

复制代码 代码如下:

using system;
using system.collections;
using system.configuration;
using system.data;
using system.web;
using system.web.security;
using system.web.ui;
using system.web.ui.htmlcontrols;
using system.web.ui.webcontrols;
using system.web.ui.webcontrols.webparts;
using system.text;

public partial class datapage_nationdatashow : system.web.ui.page
{
private data_link link = new data_link();
private string sql;

protected void page_load(object sender, eventargs e)
{
ajax.utility.registertypeforajax(typeof(datapage_nationdatashow));
}

protected void btnexcel_click(object sender, eventargs e)
{
string strexcelname = "myexcel";
strexcelname = strexcelname.replace(@"/", "");

data_link link = new data_link();
string strsql = this.hidparam.value;
dataset ds = new dataset();
ds = link.d_dataset_return(strsql);//获得想要放入excel的数据

gvexcel.visible = true;
gvexcel.datasource = null;
gvexcel.datamember = ds.tables[0].tablename;
gvexcel.datasource = ds.tables[0];
gvexcel.databind();

exporttoexcel(this.page, gvexcel, strexcelname);
}

protected void gvexcel_rowdatabound(object sender, gridviewroweventargs e)
{ }
public override void verifyrenderinginserverform(control control)
{ }

/// <summary>
/// 工具方法，excel出力(解决乱码问题)
/// </summary>
/// <param name="page">调用页面</param>
/// <param name="excel">excel数据</param>
/// <param name="filename">文件名</param>
public void exporttoexcel(system.web.ui.page page, gridview excel, string filename)
{
try
{
foreach (gridviewrow row in excel.rows)
{
for (int i = 0; i < row.cells.count; i++)
{
excel.headerrow.cells[i].backcolor = system.drawing.color.yellow;
}
}
excel.font.size = 10;
excel.alternatingrowstyle.backcolor = system.drawing.color.lightcyan;
excel.rowstyle.height = 25;

page.response.appendheader("content-disposition", "attachment;filename=" + filename);
page.response.charset = "utf-8";
page.response.contenttype = "application/vnd.ms-excel";
page.response.write("<meta http-equiv=content-type content=text/html;charset=utf-8>");
excel.page.enableviewstate = false;
excel.visible = true;
excel.headerstyle.reset();
excel.alternatingrowstyle.reset();

system.io.stringwriter ostringwriter = new system.io.stringwriter();
system.web.ui.htmltextwriter ohtmltextwriter = new system.web.ui.htmltextwriter(ostringwriter);
excel.rendercontrol(ohtmltextwriter);
page.response.write(ostringwriter.tostring());
page.response.end();

excel.datasource = null;
excel.visible = false;
}
catch (exception e)
{

}
}
}

aspx部分
复制代码 代码如下:

<head runat="server">
<script type="text/javascript">
//excel download
function excelexport(){
var hidtext = document.getelementbyid("hidparam");
hidtext.value = "some params";
document.getelementbyid("exceloutput").click();
}
</script>
</head>
<body onload="pageinit()">
<form id="form1" runat="server">
<input type="button" value="excel下载" style="width:100px;" onclick="excelexport()" id="excelbut" />
<input id="hidparam" type="text" runat="server" style="display:none;"/>
<asp:button runat="server" id="exceloutput" style="display:none" text= "excel出力" width="0px" onclick="btnexcel_click" usesubmitbehavior="false"/>
<asp:gridview id="gvexcel" runat="server" height="95px" onrowdatabound="gvexcel_rowdatabound" visible="false"></asp:gridview>
</form>
</body>

在刚才的aspx.cs代码中
复制代码 代码如下:

foreach (gridviewrow row in excel.rows)
{
for (int i = 0; i < row.cells.count; i++)
{
excel.headerrow.cells[i].backcolor = system.drawing.color.yellow;
}
}

这部分是给表头添加样式。

有时候为了便于浏览，需要给交叉行添加样式，简单点的可以用下面这种：

复制代码 代码如下:

excel.alternatingrowstyle.backcolor = system.drawing.color.lightcyan;

但是细看一下会发现它把一整行的样式都改变了，包括后面那些没有用到的列。

解决办法是有，不过比较繁琐，就是修改每个单元格的样式。

复制代码 代码如下:

int rowcount = excel.rows.count;
int colcount = excel.headerrow.cells.count;

for (int i = 0; i < rowcount; i++)
{
for(int j=0;j<colcount; j++)
{
excel.rows[i].cells[j].backcolor = system.drawing.color.lightcyan;
}
}

方法二 通过datagrid(与方法一基本相同),页面无刷新

aspx.cs部分

复制代码 代码如下:

public override void verifyrenderinginserverform(control control)
{}

/// <summary>
/// エクセル出力イベント
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void excelbut_click(object sender, system.eventargs e)
{
datagrid dgexcel = new datagrid();

try
{
dataset ds = getexceldata(this.pageparams.value);//出力データを取得する

if(ds.tables[0].rows.count>0)
{
//エクセルへデータを投入する
string execlname= "myexcel";
encoding encodingtype=system.text.encoding.utf8;
dgexcel.datamember=ds.tables[0].tablename;
dgexcel.datasource=ds.tables[0];

response.buffer = true;
response.charset = "utf-8";
response.appendheader("content-disposition", "attachment;filename=" + execlname+ ".xls");
response.contentencoding = encodingtype;
response.contenttype = "application/ms-excel";
stringwriter ostringwriter = new stringwriter();
htmltextwriter ohtmltextwriter = new htmltextwriter(ostringwriter);
dgexcel.databind();
dgexcel.visible = true;
dgexcel.rendercontrol(ohtmltextwriter);
response.write(ostringwriter.tostring());
response.flush();
response.close();
dgexcel.datasource = null;
dgexcel.visible = false;
}
else
{
response.write("<script>alert('xxxxxx')</script>");
}
}
catch(exception ex)
{
response.write("<script>alert('oooooo')</script>");
}
}

aspx部分
复制代码 代码如下:

<head runat="server">
<script type="text/javascript">
//excel download
function excelexport(){
var hidtext = document.getelementbyid("hidparam");
hidtext.value = "some params";
document.getelementbyid("exceloutput").click();
}
</script>
</head>
<body onload="pageinit()">
<form id="form1" runat="server">
<input type="button" value="excel下载" style="width:100px;" onclick="excelexport()" id="excelbut" name="excelbut"/>
<input id="hidparam" type="text" runat="server" style="display:none;" name="hidparam"/>
<asp:button runat="server" id="exceloutput" style="display:none" text= "excel出力" width="0px" onclick="btnexcel_click" usesubmitbehavior="false"/>
<asp:datagrid id="gvexcel" visible="false" runat="server" style="z-index: 107; position: absolute; top: 72px; left: 520px"
width="80px" height="40px"></asp:datagrid>
</form>
</body>

方法三 以xml形式的excel方式(可以设置丰富的样式，并可以有多个sheet，但需要模版。速度很快，但生成的文件较大，且无法保留vba代码)

具体方法：把模版以 xml document形式另存为a.xml。然后参照a.xml中的内容即可

复制代码 代码如下:
 
private void excelbut_click(object sender, system.eventargs e)
{
dataset ds = new dataset();
string excelfilename = "";
datarow dr=[------列名信息-------];
try
{
excelfilename = path.combine(request.physicalapplicationpath + "/excelfile", "myexcel.xls");

//获取excel需要的数据
ds = [------获得数据-------];
if (ds.tables.count == 0)
{
response.write("<script type='text/javascript'>alert('无数据');</script>");
return;
}
int sheetnum = ds.tables.count / 2;
streamwriter writer = new streamwriter(excelfilename, false);

//styles标签前面的信息相当于'头信息'，不需要改变
writer.writeline("<?xml version=\"1.0\"?>");
writer.writeline("<?mso-application progid=\"excel.sheet\"?>");
writer.writeline("<workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
writer.writeline("xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
writer.writeline("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
writer.writeline("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
writer.writeline("xmlns:html=\"http://www.w3.org/tr/rec-html40\">");
writer.writeline("<documentproperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
writer.writeline("<lastauthor>automated report generator example</lastauthor>");
writer.writeline(string.format("<created>{0}t{1}z</created>", datetime.now.tostring("yyyy-mm-dd"), datetime.now.tostring("hh:mm:ss")));
writer.writeline(" <company>51aspx.com</company>");
writer.writeline(" <version>11.6408</version>");
writer.writeline("</documentproperties>");
writer.writeline("<excelworkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer.writeline("<windowheight>6195</windowheight>");
writer.writeline(" <windowwidth>18495</windowwidth>");
writer.writeline(" <windowtopx>525</windowtopx>");
writer.writeline(" <windowtopy>4260</windowtopy>");
writer.writeline(" <acceptlabelsinformulas/>");
writer.writeline(" <protectstructure>true</protectstructure>");
writer.writeline(" <protectwindows>false</protectwindows>");
writer.writeline("</excelworkbook>");

//通过 ss:id 和 ss:name相当于html中style的类
writer.writeline(" <styles> ");
writer.writeline(" <style ss:id='default' ss:name='normal'> ");
writer.writeline(" <alignment ss:vertical='bottom'/> ");
writer.writeline(" <borders/> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11'/> ");
writer.writeline(" <interior/> ");
writer.writeline(" <numberformat/> ");
writer.writeline(" <protection/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s23'> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11' ");
writer.writeline(" ss:color='#ff0000'/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s24'> ");
writer.writeline(" <interior/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s25'> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11'/> ");
writer.writeline(" <interior/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s26'> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11' ");
writer.writeline(" ss:color='#ff0000'/> ");
writer.writeline(" <interior/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s27'> ");
writer.writeline(" <borders> ");
writer.writeline(" <border ss:position='bottom' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='left' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='right' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='top' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" </borders> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11'/> ");
writer.writeline(" <interior ss:color='#ccffff' ss:pattern='solid'/> ");
writer.writeline(" <protection/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s28'> ");
writer.writeline(" <borders> ");
writer.writeline(" <border ss:position='bottom' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='left' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='right' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='top' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" </borders> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11'/> ");
writer.writeline(" <interior ss:color='#ccffff' ss:pattern='solid'/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s29'> ");
writer.writeline(" <borders> ");
writer.writeline(" <border ss:position='bottom' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='left' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='top' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" </borders> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11'/> ");
writer.writeline(" <interior ss:color='#ccffff' ss:pattern='solid'/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s30'> ");
writer.writeline(" <borders> ");
writer.writeline(" <border ss:position='bottom' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='top' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" </borders> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11'/> ");
writer.writeline(" <interior ss:color='#ccffff' ss:pattern='solid'/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s31'> ");
writer.writeline(" <borders> ");
writer.writeline(" <border ss:position='bottom' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='right' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='top' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" </borders> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11'/> ");
writer.writeline(" <interior ss:color='#ccffff' ss:pattern='solid'/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s32'> ");
writer.writeline(" <alignment ss:vertical='bottom' ss:wraptext='1'/> ");
writer.writeline(" <borders> ");
writer.writeline(" <border ss:position='bottom' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='left' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='right' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='top' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" </borders> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11'/> ");
writer.writeline(" <interior ss:color='#ccffff' ss:pattern='solid'/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s33'> ");
writer.writeline(" <alignment ss:vertical='bottom' ss:wraptext='1'/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s34'> ");
writer.writeline(" <alignment ss:vertical='bottom' ss:wraptext='1'/> ");
writer.writeline(" <borders> ");
writer.writeline(" <border ss:position='bottom' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='left' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='right' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='top' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" </borders> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11'/> ");
writer.writeline(" <interior/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s35'> ");
writer.writeline(" <borders> ");
writer.writeline(" <border ss:position='bottom' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='left' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='right' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='top' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" </borders> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11'/> ");
writer.writeline(" <interior/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s36'> ");
writer.writeline(" <borders> ");
writer.writeline(" <border ss:position='bottom' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='left' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='right' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='top' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" </borders> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11'/> ");
writer.writeline(" <interior ss:color='#ffff99' ss:pattern='solid'/> ");
writer.writeline(" <protection ss:protected='0'/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s37'> ");
writer.writeline(" <borders> ");
writer.writeline(" <border ss:position='bottom' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='left' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='right' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='top' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" </borders> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11'/> ");
writer.writeline(" <interior/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s38'> ");
writer.writeline(" <borders> ");
writer.writeline(" <border ss:position='bottom' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='left' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='right' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='top' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" </borders> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11'/> ");
writer.writeline(" <interior/> ");
writer.writeline(" </style> ");
writer.writeline(" <style ss:id='s39'> ");
writer.writeline(" <borders> ");
writer.writeline(" <border ss:position='bottom' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='left' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='right' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" <border ss:position='top' ss:linestyle='continuous' ss:weight='1'/> ");
writer.writeline(" </borders> ");
writer.writeline(" <font ss:fontname='ｍｓ ｐゴシック' x:charset='128' x:family='modern' ss:size='11'/> ");
writer.writeline(" <interior/> ");
writer.writeline(" </style> ");
writer.writeline(" </styles> ");

//sheet操作
writer.writeline("<worksheet ss:name='" + dr["msg030"] + "'>");
writer.writeline(" <table ss:expandedcolumncount='9' ss:expandedrowcount='3000' x:fullcolumns='1' x:fullrows='1' ss:styleid='s33' ss:defaultcolumnwidth='54' ss:defaultrowheight='13.5'>");
writer.writeline("<column ss:styleid='s34' ss:autofitwidth='0' ss:width='110.25'/>");
writer.writeline("<column ss:styleid='s34' ss:autofitwidth='0' ss:width='33.75'/>");
writer.writeline("<column ss:styleid='s34' ss:autofitwidth='0' ss:width='118.5'/>");
writer.writeline("<column ss:styleid='s34' ss:autofitwidth='0' ss:width='159.75'/>");
writer.writeline("<column ss:styleid='s34' ss:autofitwidth='0' ss:width='147.75' ss:span='4'/>");

for (int num = sheetnum - 1; num >= 0; num = num - 1)
{
// 考課表間有3行的空行
if (num != sheetnum - 1)
{
writer.writeline("<row>");
writer.writeline("</row>");
writer.writeline("<row>");
writer.writeline("</row>");
writer.writeline("<row>");
writer.writeline("</row>");
}

// 列名
writer.writeline("<row ss:height='40.5'>");
writer.writeline("<cell ss:styleid='s32'><data ss:type='string'>" + dr["msg002"] + "</data></cell>");
writer.writeline("<cell ss:styleid='s32'><data ss:type='string'>" + dr["msg003"] + "</data></cell>");
writer.writeline("<cell ss:styleid='s32'><data ss:type='string'>" + dr["msg004"] + "</data></cell>");
writer.writeline("</row>");

// 详细数据
for (int i = 0; i < ds.tables[2 * num].rows.count; i++)
{
writer.writeline("<row ss:height='27'>");
writer.writeline("<cell><data ss:type='string'>" + ds.tables[2 * num].rows[i]["evallevel1name"].tostring() + "</data></cell>");
writer.writeline("<cell><data ss:type='number'>" + ds.tables[2 * num].rows[i]["evallevel2code"].tostring() + "</data></cell>");
writer.writeline("<cell><data ss:type='string'>" + ds.tables[2 * num].rows[i]["evallevel2name"].tostring() + "</data></cell>");
writer.writeline("</row>");
}
}
writer.writeline(" </table>");
writer.writeline(" </worksheet>");

// 其他sheet操作
for (int num = sheetnum - 1; num >= 0; num--)
{
writer.writeline("<worksheet ss:name='" + ds.tables[2 * num].rows[0][1].tostring() + "' ss:protected='1'>");
writer.writeline(" <table ss:expandedcolumncount='31' ss:expandedrowcount='30000' x:fullcolumns='1' x:fullrows='1' ss:styleid='s25' ss:defaultcolumnwidth='54' ss:defaultrowheight='13.5'>");
writer.writeline(" <row>");
writer.writeline("<cell ss:styleid='s26'><phonetictext xmlns='urn:schemas-microsoft-com:office:excel'>カキキイロテンスウツ</phonetictext><data ss:type='string'>" + dr["msg031"] + "</data></cell>");
writer.writeline("<cell ss:index='14' ss:styleid='default'/>");
writer.writeline("<cell ss:styleid='default'/>");
writer.writeline("<cell ss:styleid='default'/>");
writer.writeline("<cell ss:styleid='default'/>");
writer.writeline("<cell ss:styleid='default'/>");
writer.writeline("<cell ss:styleid='default'/>");
writer.writeline("<cell ss:styleid='default'/>");
writer.writeline("<cell ss:styleid='default'/>");
writer.writeline("<cell ss:styleid='default'/>");
writer.writeline("</row>");
writer.writeline(" <row>");
writer.writeline("<cell ss:styleid='s26'><phonetictext xmlns='urn:schemas-microsoft-com:office:excel'>カキキイロテンスウツ</phonetictext><data ss:type='string'>" + dr["msg032"] + "</data></cell>");
writer.writeline("<cell ss:index='14' ss:styleid='default'/>");
writer.writeline("<cell ss:styleid='default'/>");
writer.writeline("<cell ss:styleid='default'/>");

writer.writeline("</row>");

// 列名
writer.writeline("<row>");
writer.writeline("<cell ss:styleid='s27'><data ss:type='string'>" + dr["msg001"] + "</data></cell>");
writer.writeline("<cell ss:styleid='s28'><data ss:type='string'>" + dr["msg002"] + "</data></cell>");
writer.writeline("<cell ss:styleid='s28'><data ss:type='string'>" + dr["msg003"] + "</data></cell>");
writer.writeline("</row>");
int sheetn = ds.tables[2 * num].rows.count;

//详细数据
for (int i = 0; i < sheetn; i++)
{
writer.writeline("<row>");
writer.writeline("<cell ss:styleid='s35'><data ss:type='number'>" + ds.tables[2 * num].rows[i]["evallevel1code"].tostring() + "</data></cell>");
writer.writeline("<cell ss:styleid='s35'><data ss:type='string'>" + ds.tables[2 * num].rows[i]["evallevel1name"].tostring() + "</data></cell>");
writer.writeline("<cell ss:styleid='s35'><data ss:type='number'>" + ds.tables[2 * num].rows[i]["evallevel2code"].tostring() + "</data></cell>");
writer.writeline("<cell><data ss:type='string'> </data></cell>");
writer.writeline("</row>");
}
writer.writeline("<row>");
writer.writeline("</row>");
writer.writeline("<row>");
writer.writeline("</row>");

//列名
writer.writeline("<row>");
writer.writeline("<cell ss:styleid='s29'><data ss:type='string'>" + dr["msg011"] + "</data></cell>");
writer.writeline("<cell ss:styleid='s30'/>");
writer.writeline("<cell ss:styleid='s31'/>");
writer.writeline("<cell ss:styleid='s29'><data ss:type='string'>" + dr["msg012"] + "</data></cell>");
writer.writeline("<cell ss:styleid='s31'/>");
writer.writeline("<cell ss:styleid='s29'><data ss:type='string'>" + dr["msg013"] + "</data></cell>");
writer.writeline("<cell ss:styleid='s30'/>");
writer.writeline("<cell ss:styleid='s30'/>");
writer.writeline("<cell ss:styleid='s30'/>");
writer.writeline("<cell ss:styleid='s31'/>");
writer.writeline("<cell ss:styleid='s29'><data ss:type='string'>" + dr["msg014"] + "</data></cell>");
writer.writeline("<cell ss:styleid='s31'/>");
writer.writeline("<cell ss:styleid='s29'><data ss:type='string'>" + dr["msg015"] + "</data></cell>");
writer.writeline("<cell ss:styleid='s31'/>");
writer.writeline("</row>");
writer.writeline("<row>");
writer.writeline("<cell ss:styleid='s28'><data ss:type='string'>" + dr["msg016"] + "</data></cell>");
writer.writeline("<cell ss:styleid='s28'><data ss:type='string'>" + dr["msg017"] + "</data></cell>");
writer.writeline("<cell ss:styleid='s28'><data ss:type='string'>" + dr["msg018"] + "</data></cell>");
writer.writeline("<cell ss:styleid='s28'><data ss:type='string'>" + dr["msg019"] + "</data></cell>");

writer.writeline("</row>");
int datelen = ds.tables[2 * num + 1].rows.count;
string employeemanagement = "";

for (int i = 0; i < datelen; i++)
{
writer.writeline("<row>");
writer.writeline("<cell ss:styleid='s37'><data ss:type='number'>" + ds.tables[2 * num + 1].rows[i]["employeemanagementid"].tostring() + "</data></cell>");
if (!employeemanagement.equals(ds.tables[2 * num + 1].rows[i]["employeemanagementid"].tostring()))
{
writer.writeline("<cell ss:styleid='s35'><data ss:type='number'>" + ds.tables[2 * num + 1].rows[i]["employeecode"].tostring() + "</data></cell>");
writer.writeline("<cell ss:styleid='s35'><data ss:type='string'>" + ds.tables[2 * num + 1].rows[i]["employeename"].tostring() + "</data></cell>");
writer.writeline("<cell ss:styleid='s35'><data ss:type='string'>" + ds.tables[2 * num + 1].rows[i]["befemployeename2"].tostring() + "</data></cell>");
writer.writeline("<cell ss:styleid='s35'><data ss:type='string'>" + ds.tables[2 * num + 1].rows[i]["employeename2"].tostring() + "</data></cell>");
writer.writeline("<cell ss:styleid='s38'><data ss:type='number'>" + ds.tables[2 * num + 1].rows[i]["evallevel1code"].tostring() + "</data></cell>");
writer.writeline("<cell ss:styleid='s35'><data ss:type='string'>" + ds.tables[2 * num + 1].rows[i]["evallevel1name"].tostring() + "</data></cell>");
employeemanagement = ds.tables[2 * num + 1].rows[i]["employeemanagementid"].tostring();
}
else
{
writer.writeline("<cell ss:styleid='s35'><data ss:type='string'></data></cell>");
writer.writeline("<cell ss:styleid='s35'><data ss:type='string'></data></cell>");
writer.writeline("<cell ss:styleid='s35'><data ss:type='string'></data></cell>");
writer.writeline("<cell ss:styleid='s35'><data ss:type='string'></data></cell>");
writer.writeline("<cell ss:styleid='s38'><data ss:type='string'></data></cell>");
writer.writeline("<cell ss:styleid='s35'><data ss:type='string'></data></cell>");
}
writer.writeline("<cell ss:styleid='s39'><data ss:type='number'>" + ds.tables[2 * num + 1].rows[i]["evallevel2code"].tostring() + "</data></cell>");
writer.writeline("<cell ss:styleid='s35'><data ss:type='string'>" + ds.tables[2 * num + 1].rows[i]["evallevel2name"].tostring() + "</data></cell>");
writer.writeline("</row>");
}
writer.writeline(" </table>");
writer.writeline(" </worksheet>");
}

writer.writeline("</workbook>");
writer.close();

filedownload(excelfilename);
}
catch (system.exception ex)
{

}
finally
{
response.end();
}
}
public void filedownload(fullfilename)
{
fileinfo downloadfile = new fileinfo(fullfilename);
response.clear();
response.clearheaders();
response.buffer = true;
response.appendheader("content-disposition", "attachment;filename=" + system.web.httputility.urlencode(system.text.encoding.utf8.getbytes(myfile".xls")));
response.contenttype = "application/ms-excel;charset=utf-8";//downloadfile.fullnameoctet-stream
response.contentencoding = system.text.encoding.getencoding("shift-jis");
response.appendheader("content-length", downloadfile.length.tostring());
response.writefile(fullfilename);
response.flush();

if (file.exists(fullfilename))
{
file.delete(fullfilename);
}
}

方法四 用微软的com组件操作excel。虽然可以很方便的操作单元格，并且能保留vba代码，它相当于直接打开一个excel进程。

当初这个com组件式给winform准备的，但在web端有很多东西的支持不尽人意，例如无法用组件中的方法关闭excel对象，

只能通过强制关闭excel进程的方式(这其中还有很多问题，例如只能关闭当前打开的这个excel的进程，否则会把其他用户的excel进程关闭)。在这里不推荐

方法五 用流的方式，把内容以table的格式向excel中放数据 好处是 可以生成格式丰富复杂的excel，页面无刷新

aspx部分

复制代码 代码如下:

<asp:button id="hidexport" onclick="hidexport_click()" runat="server"></asp:button>

aspx.cs部分
复制代码 代码如下:

//内容很好理解，只需当成table来拼字符串即可
private string getexcelcontent()
{
stringbuilder sb = new stringbuilder();

sb.append("<table bordercolor='black' border='1' >");
sb.append("<thead><tr><th colspan='2' bgcolor='#ccfefe'>标题</th></tr>");
sb.append("<tr><th bgcolor='#ccfefe'>号码</th><th bgcolor='#ccfefe'>名字</th></tr></thead>");
sb.append("<tbody>");
sb.append("<tr class='firsttr'><td bgcolor='#ff99cc'></td><td></td></tr>");
sb.append("<tr class='secondtr'><td></td><td bgcolor='lightskyblue'></td></tr>");
sb.append("</tbody></table>");
return sb.tostring();
}

private void hidexport_click(object sender, system.eventargs e)
{
string content = getexcelcontent();
string css = ".firsttr td{color:blue;width:100px;}.secondtr td{color:blue;width:100px;}";
string filename = "test.xls";

commontool.exporttoexcel(filename, content ,css);
}

工具类commontool
复制代码 代码如下:

public class commontool
{
/// <summary>
/// 以流的形式，可以设置很丰富复杂的样式
/// </summary>
/// <param name="content">excel中内容(table格式)</param>
/// <param name="filename">文件名</param>
/// <param name="csstext">样式内容</param>
public static void exporttoexcel(string filename, string content,string csstext)
{
var res = httpcontext.current.response;
content = string.format("<style type='text/css'>{0}</style>{1}",csstext,content);

res.clear();
res.buffer = true;
res.charset = "utf-8";
res.addheader("content-disposition", "attachment; filename=" + filename);
res.contentencoding = system.text.encoding.getencoding("utf-8");
res.contenttype = "application/ms-excel;charset=utf-8";
res.write(content);
res.flush();
res.end();
}
}

这种方法比较灵活，而且可以通过选择器来添加样式，相当不错。缺点就是需要将数据转换成字符串。

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网