asp.net生成Excel并导出下载五种实现方法-巨人网络通讯

主页 > 知识库 > asp.net生成Excel并导出下载五种实现方法

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;jcolCount; 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(); 
} 
}