• 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="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;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="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;100px;}.secondTR td{color:blue;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(); 
    } 
    }

    这种方法比较灵活，而且可以通过选择器来添加样式，相当不错。缺点就是需要将数据转换成字符串。
  • 相关阅读:
    dalvik浅析二：jni、so
    android apk壳
    prism4 StockTrader RI 项目分析一些体会
    PRISM ‘VS100COMNTOOLS’ not set. Cannot set the build environment
    安卓移动应用代码安全加固系统设计及实现
    手机游戏外挂技术研究与运用
    移动终端安全威胁与应对措施
    游戏软件反外挂技术方案的设计和安全解决方案
    网络安全等级保护 2.0 下的安全体系建设
    APP加密,ios代码混淆工具,虚拟化技术 适用于移动应用程序的虚拟化加密软件
  • 原文地址：https://www.cnblogs.com/net-study/p/4335525.html
Copyright © 2020-2023  润新知