将数据表导出到网页中的Excel文件

导出 Excel 的链接

看看这个例子的ExporttoExcel函数。它会帮助你。

你可以调用这个方法，只需传入你的数据表：

public static void DumpExcel(DataTable dataTable)
    {
        using (ExcelPackage package = new ExcelPackage())
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("DataTable");
            worksheet.Cells["A1"].LoadFromDataTable(dataTable, true);
            for (int i = 1; i <= dataTable.Columns.Count; i++)
            {
                worksheet.Column(i).AutoFit();
                if (dataTable.Columns[i - 1].DataType == System.Type.GetType("System.DateTime"))
                {
                    worksheet.Column(i).Style.Numberformat.Format = CultureInfo.CurrentCulture.DateTimeFormat.ShortDatePattern;
                }
            }
            HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            HttpContext.Current.Response.AddHeader("content-disposition", "attachment;  filename=table.xlsx");
            HttpContext.Current.Response.BinaryWrite(package.GetAsByteArray());
            HttpContext.Current.Response.End();
        }
    }

    public void ExportToExcel(string strFileName, DataTable dt)
    {
        try
        {
            if (dt != null && dt.Rows.Count > 0)
            {
                GridView gv = new GridView();
                gv.DataSource = dt;
                gv.DataBind();
                ExportToExcel(strFileName, gv);
            }
            else
            {
                BindScript(this.Page, "excelError", "alert('No Data to Generate Excel !')");
            }
        }
        catch (Exception ex)
        {
            BindScript(this.Page, "excelError", "alert('Some error occured " + ex.Message + "')");
        }
    }
    private void ExportToExcel(string strFileName, GridView gv)
    {
        try
        {
            if (gv.Rows.Count > 0)
            {
                //Page page = new Page();
                //HtmlForm form = new HtmlForm();
                //Response.ClearContent();
                //Response.Buffer = true;
                //Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
                //Response.ContentType = "application/excel";
                ////System.Text.StringBuilder sb = new System.Text.StringBuilder();
                //StringWriter sw = new StringWriter();
                //HtmlTextWriter htw = new HtmlTextWriter(sw);
                //form.Controls.Add(gv);
                //page.Controls.Add(form);
                ////form.RenderControl(htw);
                //HttpContext.Current.Server.Execute(page, sw, true);
                //Response.Write(sw.ToString());
                //Response.Flush();
                //Response.End();
                StringWriter tw = new StringWriter();
                HtmlTextWriter hw = new HtmlTextWriter(tw);
                //Get the HTML for the control.
                gv.RenderControl(hw);
                //Write the HTML back to the browser.
                //Response.ContentType = application/vnd.ms-excel;
                Response.ContentType = "application/vnd.ms-excel";
                Response.AppendHeader("Content-Disposition", "attachment; filename=" + strFileName);
                EnableViewState = false;
                Response.Write(tw.ToString());
                Response.End();
            }
            else
            {
                BindScript(this.Page, "excelError", "alert('No Data to Generate Excel !')");
            }
        }
        catch (Exception ex)
        {
            BindScript(this.Page, "excelError", "alert('Some error occured " + ex.Message + "')");
        }
    }