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

方法一 通过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（）;
}
}

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