将DataSet导出到excel 2007 EPPlus [英] Export DataSet to excel 2007 EPPlus

问题描述

我试图将一个DataSet导出到excel 2007中，我不能使用用于在内容类型中使用mime类型导出的常规代码，如Response.ContentType =application / ms-excel;
如果我使用mime类型为xls我得到一个警告，当ai尝试导出，我不能有这个错误，因为客户端，所以我开始使用EPPlus，但现在我有期望erros，如 ArgumentNullException被用户代码未处理。当我被debbuging时，我注意到btnExportClick方法中的变量ds为null，我认为erros是什么，但我不明白在这里是完整的代码：

 命名空间PortalFornecedores 
 {
 public partial class _Default：System.Web.UI.Page 
 {
 protected void Page_Load（object sender，EventArgs e）
 {
 if（！this.IsPostBack）
 {
 this.BindGrid（）; 
} 
} 
 
 
 public void BindGrid（）
 {
 using（DataSet ds = new DataSet（））
 {
 ds.ReadXml（Server.MapPath（〜/ Customers.xml））; 
 GridFornecedor.DataSource = ds; 
 GridFornecedor.DataBind（）; 
} 
} 
 
 
 
 
 public void btnExportClick（object sender，EventArgs e）
 {
 DataTable ds = GridFornecedor.DataSource作为DataTable; 
 ExportExcel（ds）; 
 
 
} 
 
 
 public void ExportExcel（DataTable ds）
 {
 
 using（ExcelPackage pck =新的ExcelPackage（））
 {
 //创建工作表
 ExcelWorksheet ws = pck.Workbook.Worksheets.Add（SearchReport）; 
 
 //从表格A1开始，将数据表加载到工作表中。在第1行打印列名
 ws.Cells [A1]。LoadFromDataTable（ds，true）; 
 
 //准备列标题的范围
 string cellRange =A1：+ Convert.ToChar（'A'+ ds.Columns.Count  -  1）+ 1; 
 
 //使用（ExcelRange rng = ws.Cells [cellRange]）格式列
的标题
 {
 rng.Style.WrapText = false; 
 rng.Style.Horizo​​ntalAlignment = ExcelHorizo​​ntalAlignment.Left; 
 rng.Style.Font.Bold = true; 
 rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //将背景设置为Solid 
 rng.Style.Fill.BackgroundColor.SetColor（Color.Gray）; 
 rng.Style.Font.Color.SetColor（Color.White）; 
} 
 
 //准备行的范围
 string rowsCellRange =A2：+ Convert.ToChar（'A'+ ds.Columns.Count  -  1）+ ds.Rows.Count * ds.Columns.Count; 
 
 //使用（ExcelRange rng = ws.Cells [rowsCellRange]）格式化行
 
 {
 rng.Style.WrapText = false; 
 rng.Style.Horizo​​ntalAlignment = ExcelHorizo​​ntalAlignment.Left; 
} 
 
 //读取字节数组中的Excel文件
 Byte [] fileBytes = pck.GetAsByteArray（）; 
 
 //清除响应
 Response.Clear（）; 
 Response.ClearContent（）; 
 Response.ClearHeaders（）; 
 Response.Cookies.Clear（）; 
 
 //添加标题&其他信息
 Response.Cache.SetCacheability（HttpCacheability.Private）; 
 Response.CacheControl =private; 
 Response.Charset = System.Text.UTF8Encoding.UTF8.WebName; 
 Response.ContentEncoding = System.Text.UTF8Encoding.UTF8; 
 Response.AppendHeader（Content-Length，fileBytes.Length.ToString（））; 
 Response.AppendHeader（Pragma，cache）; 
 Response.AppendHeader（Expires，60）; 
 Response.AppendHeader（Content-Disposition，
attachment;+ 
filename = \ExcelReport.xlsx\;+ 
size = + fileBytes.Length.ToString（）+;+ 
creation-date =+ DateTime.Now.ToString（R）+;+ 
modification-date = DateTime.Now.ToString（R）+;+ 
read-date =+ DateTime.Now.ToString（R））; 
 Response.ContentType =application / vnd.openxmlformats-officedocument.spreadsheetml.sheet; 
 
 //将其写回客户端
 Response.BinaryWrite（fileBytes）; 
 Response.End（）; 
} 
} 
 
 public override void VerifyRenderingInServerForm（控件控件）
 {
 / *确认为指定的ASP.NET呈现HtmlForm控件
运行时的服务器控制。 * /`在这里输入代码
} 
} 
  

解决方案

情侣事物。这不是一个真正的epplus问题，更一般的网页。

首先，您将网格数据源设置为dataSET：

$ b $使用（DataSet ds = new DataSet（））
{
ds.ReadXml（Server.MapPath（〜/ Customers.xml））; b

  
 GridFornecedor.DataSource = ds; 
  

，但稍后在这里投放到dataTABLE：

DataTable ds = GridFornecedor.DataSource作为DataTable;

当您首次投放到数据集，然后获取其表集合的第一个表。 >

但是，仍然无法解决问题，因为您有一个类级别的对象，不会在回发之前。您需要使用一个会话或viewstate变量，如下所示：

  public void BindGrid（）
 {
使用（DataSet ds = new DataSet（））
 {
 ds.ReadXml（Server.MapPath（〜/ Customers.xml））; 
 GridFornecedor.DataSource = ds; 
 GridFornecedor.DataBind（）; 
 ViewState [GridDataSource] = ds; 
} 
} 
 
 
 public void btnExportClick（object sender，EventArgs e）
 {
 // DataTable ds = GridFornecedor.DataSource as数据表; 
 var ds = ViewState [GridDataSource]作为DataSet; 
 var dt = ds.Tables [0]; 
 ExportExcel（dt）; 
} 
  

I'm trying to export a DataSet to excel 2007,i cant use the normal code that is used to export using mime types in the contenttype like this "Response.ContentType = "application/ms-excel";" If i use mime type for xls i get a warning when a i try to export,i can't have this error because of the clients,so i started using EPPlus,but now i'm having expections erros,like "ArgumentNullException was unhandled by user code".When i'm debbuging i noticed that the variable ds in the btnExportClick method is null,i think that where the erros is,but i cant understand where,here is the full code:

namespace PortalFornecedores
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                this.BindGrid();
            }
        }


        public void BindGrid()
        {
            using (DataSet ds = new DataSet())
            {
                ds.ReadXml(Server.MapPath("~/Customers.xml"));
                GridFornecedor.DataSource = ds;
                GridFornecedor.DataBind();
            }
        }




        public void btnExportClick(object sender, EventArgs e)
        {
            DataTable ds = GridFornecedor.DataSource as DataTable;
            ExportExcel(ds);


        }


        public void ExportExcel(DataTable ds)
        {

            using (ExcelPackage pck = new ExcelPackage())
            {
                //Create the worksheet
                ExcelWorksheet ws = pck.Workbook.Worksheets.Add("SearchReport");

                //Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
                ws.Cells["A1"].LoadFromDataTable(ds, true);

                //prepare the range for the column headers
                string cellRange = "A1:" + Convert.ToChar('A' + ds.Columns.Count - 1) + 1;

                //Format the header for columns
                using (ExcelRange rng = ws.Cells[cellRange])
                {
                    rng.Style.WrapText = false;
                    rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                    rng.Style.Font.Bold = true;
                    rng.Style.Fill.PatternType = ExcelFillStyle.Solid; //Set Pattern for the background to Solid
                    rng.Style.Fill.BackgroundColor.SetColor(Color.Gray);
                    rng.Style.Font.Color.SetColor(Color.White);
                }

                //prepare the range for the rows
                string rowsCellRange = "A2:" + Convert.ToChar('A' + ds.Columns.Count - 1) + ds.Rows.Count * ds.Columns.Count;

                //Format the rows
                using (ExcelRange rng = ws.Cells[rowsCellRange])
                {
                    rng.Style.WrapText = false;
                    rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                }

                //Read the Excel file in a byte array
                Byte[] fileBytes = pck.GetAsByteArray();

                //Clear the response
                Response.Clear();
                Response.ClearContent();
                Response.ClearHeaders();
                Response.Cookies.Clear();

                //Add the header & other information
                Response.Cache.SetCacheability(HttpCacheability.Private);
                Response.CacheControl = "private";
                Response.Charset = System.Text.UTF8Encoding.UTF8.WebName;
                Response.ContentEncoding = System.Text.UTF8Encoding.UTF8;
                Response.AppendHeader("Content-Length", fileBytes.Length.ToString());
                Response.AppendHeader("Pragma", "cache");
                Response.AppendHeader("Expires", "60");
                Response.AppendHeader("Content-Disposition",
                "attachment; " +
                "filename=\"ExcelReport.xlsx\"; " +
                "size=" + fileBytes.Length.ToString() + "; " +
                "creation-date=" + DateTime.Now.ToString("R") + "; " +
                "modification-date=" + DateTime.Now.ToString("R") + "; " +
                "read-date=" + DateTime.Now.ToString("R"));
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                //Write it back to the client
                Response.BinaryWrite(fileBytes);
                Response.End();
            }
        }

        public override void VerifyRenderingInServerForm(Control control)
        {
            /* Confirms that an HtmlForm control is rendered for the specified ASP.NET
               server control at run time. */`enter code here`
        }
    }

解决方案

Couple things. This isnt really an epplus problem, more general web.

First, you are setting the grids DataSource to a dataSET here:

using (DataSet ds = new DataSet())
{
    ds.ReadXml(Server.MapPath("~/Customers.xml"));
    GridFornecedor.DataSource = ds;

but are later casting to a dataTABLE here:

DataTable ds = GridFornecedor.DataSource as DataTable;

when you should first cast to a dataset then get the first table of its Table collection.

But that still will not fix the problem because you have a class-level object which will not presist across postbacks. You need to use a session or viewstate variable like this:

public void BindGrid()
{
    using (DataSet ds = new DataSet())
    {
        ds.ReadXml(Server.MapPath("~/Customers.xml"));
        GridFornecedor.DataSource = ds;
        GridFornecedor.DataBind();
        ViewState["GridDataSource"] = ds;
    }
}


public void btnExportClick(object sender, EventArgs e)
{
    //DataTable ds = GridFornecedor.DataSource as DataTable;
    var ds = ViewState["GridDataSource"] as DataSet;
    var dt = ds.Tables[0];
    ExportExcel(dt);
}

这篇关于将DataSet导出到excel 2007 EPPlus的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！