viewstate 导出html,asp.net中输出Excel并将结果集分为多张工作表来导出

闲话少说直接上代码看了就知道怎么回事：

%@ Page Language="C#" EnableViewState="true" %

!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"

script runat="server"

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

System.Data.DataTable dt = new System.Data.DataTable();

System.Data.DataRow dr;

dt.Columns.Add(new System.Data.DataColumn("学生班级", typeof(System.String)));

dt.Columns.Add(new System.Data.DataColumn("学生姓名", typeof(System.String)));

dt.Columns.Add(new System.Data.DataColumn("语文", typeof(System.Decimal)));

dt.Columns.Add(new System.Data.DataColumn("数学", typeof(System.Decimal)));

dt.Columns.Add(new System.Data.DataColumn("英语", typeof(System.Decimal)));

dt.Columns.Add(new System.Data.DataColumn("计算机", typeof(System.Decimal)));

System.Random rd = new System.Random();

for (int i = 0; i 88; i++)

{

dr = dt.NewRow();

dr[0] = "班级" + i.ToString();

dr[1] = "【孟子E章】" + i.ToString();

dr[2] = System.Math.Round(rd.NextDouble() * 100, 0);

dr[3] = System.Math.Round(rd.NextDouble() * 100, 0);

dr[4] = System.Math.Round(rd.NextDouble() * 100, 0);

dr[5] = System.Math.Round(rd.NextDouble() * 100, 0);

dt.Rows.Add(dr);

}

GridView1.DataSource = dt;

GridView1.DataBind();

}

}

protected void Button1_Click(object sender, EventArgs e)

{

//假如每10条数据放在一个 Sheet 里面，先计算需要多少个 Sheet

int ItenCountPerSheet = 10;

int SheetCount = Convert.ToInt32(Math.Ceiling((double)GridView1.Rows.Count / ItenCountPerSheet));

Response.ClearContent();

Response.BufferOutput = true;

Response.Charset = "utf-8";

Response.ContentType = "text/xml";

Response.ContentEncoding = System.Text.Encoding.UTF8;

Response.AppendHeader("Content-Disposition", "attachment;filename="+Server.UrlEncode("孟宪会Excel表格测试")+".xls");

// 采用下面的格式，将兼容 Excel 2003,Excel 2007, Excel 2010。

// Response.AppendHeader("Content-Disposition", "attachment;filename="+Server.UrlEncode("孟宪会Excel表格测试")+".xml");

Response.Write("?xml version='1.0'??mso-application progid='Excel.Sheet'?");

Response.Write(@"\r\nWorkbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'

xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'

xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'");

Response.Write(@"\r\nDocumentProperties xmlns='urn:schemas-microsoft-com:office:office'");

Response.Write(@"\r\nAuthor孟宪会/AuthorLastAuthor孟子E章/LastAuthor

Created2010-09-08T14:07:11Z/CreatedCompanymxh/CompanyVersion1990/Version");

Response.Write("\r\n/DocumentProperties");

Response.Write(@"\r\nStylesStyle ss:ID='Default' ss:Name='Normal'Alignment ss:Vertical='Center'/

Borders/Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/Interior/NumberFormat/Protection//Style");

//定义标题样式

Response.Write(@"Style ss:ID='Header'BordersBorder ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/

Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/

Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/

Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'//Borders

Font ss:FontName='宋体' x:CharSet='134' ss:Size='18' ss:Color='#FF0000' ss:Bold='1'//Style");

//定义边框

Response.Write(@"Style ss:ID='border'NumberFormat ss:Format='@'/Borders

Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/

Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/

Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/

Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'//Borders/Style");

Response.Write("/Styles");

for (int i = 0; i SheetCount; i++)

{

//计算该 Sheet 中的数据起始行和结束行。

int start = ItenCountPerSheet * i;

int end = ItenCountPerSheet * (i + 1);

if (end GridView1.Rows.Count) end = GridView1.Rows.Count;

Response.Write("\r\nWorksheet ss:Name='Sheet" + (i+1) + "'");

Response.Write("\r\nTable x:FullColumns='1' x:FullRows='1'");

//输出标题

Response.Write("\r\nRow ss:AutoFitHeight='1'");

for (int j = 0; j GridView1.HeaderRow.Cells.Count; j++)

{

Response.Write("Cell ss:StyleID='Header'Data ss:Type='String'" + GridView1.HeaderRow.Cells[j].Text + "/Data/Cell");

}

Response.Write("\r\n/Row");

for (int j = start; j end; j++)

{

Response.Write("\r\nRow");

for (int c = 0; c GridView1.HeaderRow.Cells.Count; c++)

{

//对于数字，采用Number数字类型

if (c 1)

{

Response.Write("Cell ss:StyleID='border'Data ss:Type='Number'" + GridView1.Rows[j].Cells[c].Text + "/Data/Cell");

}

else

{

Response.Write("Cell ss:StyleID='border'Data ss:Type='String'" + GridView1.Rows[j].Cells[c].Text + "/Data/Cell");

}

}

Response.Write("\r\n/Row");

}

Response.Write("\r\n/Table");

Response.Write("\r\n/Worksheet");

Response.Flush();

}

Response.Write("\r\n/Workbook");

Response.End();

}

/script

html xmlns="http://www.w3.org/1999/xhtml"

head runat="server"

title/title

/head

body

form id="form1" runat="server"

asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="导出测试" /

asp:GridView ID="GridView1" runat="server"

/asp:GridView

/form

/body

/html

如果是DataTable，DataSet，可以直接导出成文件。下面是完整的源代码：

%@ Page Language="C#" %

script runat="server"

protected void Page_Load(object sender, EventArgs e)

{

// 下面采用的是DataTable，也可以采用DataSet，其中每个DataTable可以保存成一个 Sheet

// 迅雷下载时可以在下载完毕后会自动把文件名更新成 xls 或者 xml 的。

System.Data.DataTable dt = new System.Data.DataTable();

if (!Page.IsPostBack)

{

System.Data.DataRow dr;

dt.Columns.Add(new System.Data.DataColumn("学生班级", typeof(System.String)));

dt.Columns.Add(new System.Data.DataColumn("学生姓名", typeof(System.String)));

dt.Columns.Add(new System.Data.DataColumn("语文", typeof(System.Decimal)));

dt.Columns.Add(new System.Data.DataColumn("数学", typeof(System.Decimal)));

dt.Columns.Add(new System.Data.DataColumn("英语", typeof(System.Decimal)));

dt.Columns.Add(new System.Data.DataColumn("计算机", typeof(System.Decimal)));

System.Random rd = new System.Random();

for (int i = 0; i 88; i++)

{

dr = dt.NewRow();

dr[0] = "班级" + i.ToString();

dr[1] = "【孟子E章】" + i.ToString();

dr[2] = System.Math.Round(rd.NextDouble() * 100, 0);

dr[3] = System.Math.Round(rd.NextDouble() * 100, 0);

dr[4] = System.Math.Round(rd.NextDouble() * 100, 0);

dr[5] = System.Math.Round(rd.NextDouble() * 100, 0);

dt.Rows.Add(dr);

}

}

//假如每10条数据放在一个 Sheet 里面，先计算需要多少个 Sheet

int ItenCountPerSheet = 10;

int SheetCount = Convert.ToInt32(Math.Ceiling((double)dt.Rows.Count / ItenCountPerSheet));

Response.ClearContent();

Response.BufferOutput = true;

Response.Charset = "utf-8";

Response.ContentType = "application/ms-excel";

Response.AddHeader("Content-Transfer-Encoding", "binary");

Response.ContentEncoding = System.Text.Encoding.UTF8;

//Response.AppendHeader("Content-Disposition", "attachment;filename="+Server.UrlEncode("孟宪会Excel表格测试")+".xls");

// 采用下面的格式，将兼容 Excel 2003,Excel 2007, Excel 2010。

String FileName = "孟宪会Excel表格测试";

if (!String.IsNullOrEmpty(Request.UserAgent))

{

// firefox 里面文件名无需编码。

if (!(Request.UserAgent.IndexOf("Firefox") -1 Request.UserAgent.IndexOf("Gecko") -1))

{

FileName = Server.UrlEncode(FileName);

}

}

Response.AppendHeader("Content-Disposition", "attachment;filename=" + FileName + ".xml");

Response.Write("?xml version='1.0'??mso-application progid='Excel.Sheet'?");

Response.Write(@"Workbook xmlns='urn:schemas-microsoft-com:office:spreadsheet'

xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel'

xmlns:ss='urn:schemas-microsoft-com:office:spreadsheet' xmlns:html='http://www.w3.org/TR/REC-html40'");

Response.Write(@"DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'");

Response.Write(@"Author孟宪会/AuthorLastAuthor孟子E章/LastAuthor

Created2010-09-08T14:07:11Z/CreatedCompanymxh/CompanyVersion1990/Version");

Response.Write("/DocumentProperties");

Response.Write(@"StylesStyle ss:ID='Default' ss:Name='Normal'Alignment ss:Vertical='Center'/

Borders/Font ss:FontName='宋体' x:CharSet='134' ss:Size='12'/Interior/NumberFormat/Protection//Style");

//定义标题样式

Response.Write(@"Style ss:ID='Header'BordersBorder ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/

Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/

Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/

Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'//Borders

Font ss:FontName='宋体' x:CharSet='134' ss:Size='18' ss:Color='#FF0000' ss:Bold='1'//Style");

//定义边框

Response.Write(@"Style ss:ID='border'NumberFormat ss:Format='@'/Borders

Border ss:Position='Bottom' ss:LineStyle='Continuous' ss:Weight='1'/

Border ss:Position='Left' ss:LineStyle='Continuous' ss:Weight='1'/

Border ss:Position='Right' ss:LineStyle='Continuous' ss:Weight='1'/

Border ss:Position='Top' ss:LineStyle='Continuous' ss:Weight='1'//Borders/Style");

Response.Write("/Styles");

//SheetCount代表生成的 Sheet 数目。

for (int i = 0; i SheetCount; i++)

{

//计算该 Sheet 中的数据起始行和结束行。

int start = ItenCountPerSheet * i;

int end = ItenCountPerSheet * (i + 1);

if (end dt.Rows.Count) end = dt.Rows.Count;

Response.Write("Worksheet ss:Name='Sheet" + (i + 1) + "'");

Response.Write("Table x:FullColumns='1' x:FullRows='1'");

//输出标题

Response.Write("\r\nRow ss:AutoFitHeight='1'");

for (int j = 0; j dt.Columns.Count; j++)

{

Response.Write("Cell ss:StyleID='Header'Data ss:Type='String'" + dt.Columns[j].ColumnName + "/Data/Cell");

}

Response.Write("\r\n/Row");

for (int j = start; j end; j++)

{

Response.Write("Row");

for (int c = 0; c 6; c++)

{

//对于数字，采用Number数字类型

if (c 1)

{

Response.Write("Cell ss:StyleID='border'Data ss:Type='Number'" + dt.Rows[j][c].ToString() + "/Data/Cell");

}

else

{

Response.Write("Cell ss:StyleID='border'Data ss:Type='String'" + dt.Rows[j][c].ToString() + "/Data/Cell");

}

}

Response.Write("/Row");

}

Response.Write("/Table");

Response.Write("/Worksheet");

Response.Flush();

}

Response.Write("/Workbook");

Response.End();

}

/script

以上内容都是转载 网络上一位叫

孟宪会的朋友 文章写的不错 特此转载一下 为更多有这中需求的朋友一个帮助。