无为清净楼资源网

asp.net生成Excel并导出下载五种实现方法
网络编程 2024/11/16 佚名
3 2
无为清净楼资源网 Design By www.qnjia.com

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

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

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

解决办法是有，不过比较繁琐，就是修改每个单元格的样式。
复制代码 代码如下:
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();
}
}

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

标签：
生成Excel,导出Excel
无为清净楼资源网 Design By www.qnjia.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
无为清净楼资源网 Design By www.qnjia.com
www.qnjia.com 无为清净楼资源网
139,976互联网资源
144,792高清电影
21,817无损音乐
631,128技术资源
一句话新闻
苹果官宣WWDC 2024！预计会有大批AI功能 - 2024/11/16

3月27日消息，苹果宣布2024年全球开发者大会（WWDC）将于6月10日至6月14日举行，巧合的是，这次大会与端午假期重合。

苹果官方表示：

在线参加 Apple 每年规模最大的开发者盛会。亲眼见证 Apple 最新平台、技术和工具的发布。了解如何创建和改进你的 App 和游戏。与 Apple 设计师和工程师互动交流，与全球开发者社区建立联系。以上活动均免费在线举行。

探索各种新的工具、框架和功能，助力你打造出理想的 App 和游戏。通过视频讲座学习新技能，与 Apple 专家进行一对一会面，以推进你的项目，完善你的构思。

Swift Student Challenge 旨在支持和鼓舞下一代开发者、创作者和企业家。太平洋时间 3 月 28 日，我们将公布今年的获奖者名单。获奖者将有资格参加在 Apple Park 举办的特别活动。我们还会选出 50 名杰出获胜者，他们将受邀前往库比提诺，获得为期三天的非凡体验，包括参加 Apple Park 的特别活动。

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

三星在GTC上展示了专为下一代游戏GPU设计的GDDR7内存。

首次推出的GDDR7内存模块密度为16GB，每个模块容量为2GB。其速度预设为32 Gbps（PAM3），但也可以降至28 Gbps，以提高产量和初始阶段的整体性能和成本效益。

据三星表示，GDDR7内存的能效将提高20%，同时工作电压仅为1.1V，低于标准的1.2V。通过采用更新的封装材料和优化的电路设计，使得在高速运行时的发热量降低，GDDR7的热阻比GDDR6降低了70%。

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接