将Dataset中的多个DataTable导入到一个Excel文件的多个Sheet中

最新推荐文章于 2024-02-07 16:15:08 发布

转载最新推荐文章于 2024-02-07 16:15:08 发布 · 1k 阅读

.net(C#) 专栏收录该内容

279 篇文章

订阅专栏

本文介绍了一种使用ASP.NET和C#来批量生成包含多个Sheet的Excel表格的方法，其中包括了如何设置表格样式、如何处理不同浏览器的文件名编码、以及如何分页显示大量数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

http://bbs.youkuaiyun.com/topics/380041223

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;

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 + ".xls");

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>孟宪会</Author><LastAuthor>孟子E章</LastAuthor>

<Created>2010-09-08T14:07:11Z</Created><Company>mxh</Company><Version>1990</Version>");

Response.Write("</DocumentProperties>");

Response.Write(@"<Styles><Style 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'><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>

<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>");

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

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

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

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

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

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

System.Data.DataRow dr2;

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

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

{

dr2 = dt2.NewRow();

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

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

dr2[2] = System.Math.Round(rd2.NextDouble() * 102, 0);

dr2[3] = System.Math.Round(rd2.NextDouble() * 102, 0);

dr2[4] = System.Math.Round(rd2.NextDouble() * 102, 0);

dt2.Rows.Add(dr2);

}

DataSet ds = new DataSet();

ds.Tables.Clear();

ds.Tables.Add(dt);

ds.Tables.Add(dt2);

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

for (int k = 0; k < ds.Tables.Count; k++)

{

int SheetCount = Convert.ToInt32(Math.Ceiling((double)ds.Tables[k].Rows.Count / ItenCountPerSheet));

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

{

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

int start = ItenCountPerSheet * i;

int end = ItenCountPerSheet * (i + 1);

if (end > ds.Tables[k].Rows.Count) end = ds.Tables[k].Rows.Count;

Response.Write("<Worksheet ss:Name='Sheet" + (k*SheetCount + i+1) + "'>");

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

//输出标题

Response.Write("\r\n<Row ss:AutoFitHeight='1'>");

for (int j = 0; j < 5; j++)

{

Response.Write("<Cell ss:StyleID='Header'><Data ss:Type='String'>" + ds.Tables[k].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 < ds.Tables[k].Columns.Count; c++)

{

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

if (c > 1)

{

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

}

else

{

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

}

Response.Write("</Row>");

}

Response.Write("</Table>");

Response.Write("</Worksheet>");

Response.Flush();

}

Response.Write("</Workbook>");

Response.End();