亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

asp.net生成Excel并導(dǎo)出下載五種實(shí)現(xiàn)方法

 更新時(shí)間：2013年02月02日 16:23:55   作者：  
有關(guān)Excel下載的文章網(wǎng)上想必有很多，利用閑暇時(shí)間整理了一些有Excel下載方法的文章，接下來(lái)介紹五種實(shí)現(xiàn)Excel下載的方法，感興趣的朋友可以了解下，或許對(duì)你學(xué)習(xí)Excel下載有所幫助

方法一 通過(guò)GridView(簡(jiǎn)評(píng)：方法比較簡(jiǎn)單，但是只適合生成格式簡(jiǎn)單的Excel，且無(wú)法保留VBA代碼),頁(yè)面無(wú)刷新

aspx.cs部分

復(fù)制代碼 代碼如下:

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的數(shù)據(jù)

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出力(解決亂碼問(wèn)題)
/// </summary>
/// <param name="page">調(diào)用頁(yè)面</param>
/// <param name="excel">Excel數(shù)據(jù)</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部分
復(fù)制代碼 代碼如下:

<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代碼中
復(fù)制代碼 代碼如下:

foreach (GridViewRow row in excel.Rows)
{
for (int i = 0; i < row.Cells.Count; i++)
{
excel.HeaderRow.Cells[i].BackColor = System.Drawing.Color.Yellow;
}
}

這部分是給表頭添加樣式。

有時(shí)候?yàn)榱吮阌跒g覽，需要給交叉行添加樣式，簡(jiǎn)單點(diǎn)的可以用下面這種：

復(fù)制代碼 代碼如下:

excel.AlternatingRowStyle.BackColor = System.Drawing.Color.LightCyan;

但是細(xì)看一下會(huì)發(fā)現(xiàn)它把一整行的樣式都改變了，包括后面那些沒(méi)有用到的列。

解決辦法是有，不過(guò)比較繁瑣，就是修改每個(gè)單元格的樣式。

復(fù)制代碼 代碼如下:

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;
}
}

方法二 通過(guò)DataGrid(與方法一基本相同),頁(yè)面無(wú)刷新

aspx.cs部分

復(fù)制代碼 代碼如下:

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部分
復(fù)制代碼 代碼如下:

<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方式(可以設(shè)置豐富的樣式，并可以有多個(gè)sheet，但需要模版。速度很快，但生成的文件較大，且無(wú)法保留VBA代碼)

具體方法：把模版以 XML Document形式另存為A.xml。然后參照A.xml中的內(nèi)容即可

復(fù)制代碼 代碼如下:
 
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需要的數(shù)據(jù)
ds = [------獲得數(shù)據(jù)-------];
if (ds.Tables.Count == 0)
{
Response.Write("<script type='text/javascript'>alert('無(wú)數(shù)據(jù)');</script>");
return;
}
int sheetNum = ds.Tables.Count / 2;
StreamWriter writer = new StreamWriter(ExcelFileName, false);

//Styles標(biāo)簽前面的信息相當(dāng)于'頭信息'，不需要改變
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>");

//通過(guò) ss:ID 和 ss:Name相當(dāng)于html中style的類(lèi)
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>");

// 詳細(xì)數(shù)據(jù)
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;

//詳細(xì)數(shù)據(jù)
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代碼，它相當(dāng)于直接打開(kāi)一個(gè)EXCEL進(jìn)程。

當(dāng)初這個(gè)COM組件式給WinForm準(zhǔn)備的，但在Web端有很多東西的支持不盡人意，例如無(wú)法用組件中的方法關(guān)閉Excel對(duì)象，

只能通過(guò)強(qiáng)制關(guān)閉Excel進(jìn)程的方式(這其中還有很多問(wèn)題，例如只能關(guān)閉當(dāng)前打開(kāi)的這個(gè)Excel的進(jìn)程，否則會(huì)把其他用戶的Excel進(jìn)程關(guān)閉)。在這里不推薦

方法五 用流的方式，把內(nèi)容以Table的格式向Excel中放數(shù)據(jù) 好處是 可以生成格式豐富復(fù)雜的Excel，頁(yè)面無(wú)刷新

aspx部分

復(fù)制代碼 代碼如下:

<asp:Button ID="hidExport" onClick="hidExport_Click()" Runat="server"></asp:Button>

aspx.cs部分
復(fù)制代碼 代碼如下:

//內(nèi)容很好理解，只需當(dāng)成Table來(lái)拼字符串即可
private string getExcelContent()
{
StringBuilder sb = new StringBuilder();

sb.Append("<table borderColor='black' border='1' >");
sb.Append("<thead><tr><th colSpan='2' bgColor='#ccfefe'>標(biāo)題</th></tr>");
sb.Append("<tr><th bgColor='#ccfefe'>號(hào)碼</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);
}

工具類(lèi)CommonTool
復(fù)制代碼 代碼如下:

public class CommonTool
{
/// <summary>
/// 以流的形式，可以設(shè)置很豐富復(fù)雜的樣式
/// </summary>
/// <param name="content">Excel中內(nèi)容(Table格式)</param>
/// <param name="filename">文件名</param>
/// <param name="cssText">樣式內(nèi)容</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();
}
}

這種方法比較靈活，而且可以通過(guò)選擇器來(lái)添加樣式，相當(dāng)不錯(cuò)。缺點(diǎn)就是需要將數(shù)據(jù)轉(zhuǎn)換成字符串。

相關(guān)文章

  • .NET6+Quartz實(shí)現(xiàn)定時(shí)任務(wù)的示例詳解

    .NET6+Quartz實(shí)現(xiàn)定時(shí)任務(wù)的示例詳解

    在實(shí)際工作中，經(jīng)常會(huì)有一些需要定時(shí)操作的業(yè)務(wù)，如：定時(shí)發(fā)郵件，定時(shí)統(tǒng)計(jì)信息等，那么如何實(shí)現(xiàn)才能使得我們的項(xiàng)目整齊劃一呢？本文通過(guò)一些簡(jiǎn)單的小例子，簡(jiǎn)述在.Net6+Quartz實(shí)現(xiàn)定時(shí)任務(wù)的一些基本操作，如有不足之處，還請(qǐng)指正
    2023-03-03
  • .NetCore獲取Json和Xml格式的配置信息

    .NetCore獲取Json和Xml格式的配置信息

    本篇將和大家分享的是如何獲取Json和Xml格式的配置信息，主要介紹的是Configuration擴(kuò)展方法的使用，對(duì).netcore 獲取json和xml格式的配置信息的相關(guān)知識(shí)，感興趣的朋友一起看看吧
    2017-09-09
  • mvc下,3種窗口彈出設(shè)置的方法

    mvc下,3種窗口彈出設(shè)置的方法

    想做頁(yè)面美化，特別是在一個(gè)頁(yè)面中，只占了很小一塊的頁(yè)面，想做成彈出窗口樣式，稍微總結(jié)了下：
    2013-07-07
  • ASP.NET Core3.1 Ocelot負(fù)載均衡的實(shí)現(xiàn)

    ASP.NET Core3.1 Ocelot負(fù)載均衡的實(shí)現(xiàn)

    這篇文章主要介紹了ASP.NET Core3.1 Ocelot負(fù)載均衡的實(shí)現(xiàn)，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
    2020-11-11
  • ASP.NET簡(jiǎn)單獲取服務(wù)端和客戶端計(jì)算機(jī)名稱(chēng)的方法

    ASP.NET簡(jiǎn)單獲取服務(wù)端和客戶端計(jì)算機(jī)名稱(chēng)的方法

    這篇文章主要介紹了ASP.NET簡(jiǎn)單獲取服務(wù)端和客戶端計(jì)算機(jī)名稱(chēng)的方法,涉及asp.net獲取服務(wù)器端計(jì)算機(jī)名以及根據(jù)IP獲取客戶端主機(jī)名的相關(guān)技巧,需要的朋友可以參考下
    2016-08-08
  • Visual Studio 2017正式版發(fā)布 Mac版新功能特性有哪些

    Visual Studio 2017正式版發(fā)布 Mac版新功能特性有哪些

    Visual Studio 2017正式版推出時(shí)間什么時(shí)候？Mac版新功能特性又有哪些？這篇文章就為大家詳細(xì)介紹Visual Studio 2017正式版的最新消息，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下
    2017-03-03
  • 支持Ajax跨域訪問(wèn)ASP.NET Web Api 2(Cors)的示例教程

    支持Ajax跨域訪問(wèn)ASP.NET Web Api 2(Cors)的示例教程

    這篇文章主要介紹了支持Ajax跨域訪問(wèn)ASP.NET Web Api 2(Cors)的示例教程,需要的朋友可以參考下
    2016-04-04
  • .Net 項(xiàng)目代碼風(fēng)格要求小結(jié)

    .Net 項(xiàng)目代碼風(fēng)格要求小結(jié)

    代碼風(fēng)格沒(méi)有正確與否，重要的是整齊劃一，這是我擬的一份《.Net 項(xiàng)目代碼風(fēng)格要求》，供大家參考
    2015-12-12
  • asp.net 使用Silverlight操作ASPNETDB數(shù)據(jù)庫(kù)

    asp.net 使用Silverlight操作ASPNETDB數(shù)據(jù)庫(kù)

    asp.net下使用Silverlight操作ASPNETDB數(shù)據(jù)庫(kù)的實(shí)現(xiàn)代碼
    2010-01-01
  • DataGridView右鍵菜單自定義顯示及隱藏列功能

    DataGridView右鍵菜單自定義顯示及隱藏列功能

    WinForm程序中表單的列可自定義顯示及隱藏，是一種常見(jiàn)的功能，對(duì)于用戶體驗(yàn)來(lái)說(shuō)是非常好的。這篇文章主要介紹了DataGridView右鍵菜單自定義顯示及隱藏列功能,需要的朋友可以參考下
    2019-11-11

最新評(píng)論