使用 EPPlus，我试图将 gridview 导出到 Excel 工作表 [英] Using EPPlus, I am trying to export a gridview to an Excel Sheet

问题描述

Excel 告诉我有不可读的数据，所以在我说尝试恢复信息后，它显示了正确的数据.但是，当我打开 xlsx 的文本文件时，我获得了整个页面的所有 html，而不仅仅是 gridview(这可能是 Excel 正在谈论的不可读内容).

Excel is telling me that there is unreadable data, and so after I say to try to recover the information, it shows the correct data. When I open the text file of the xlsx, though, I get all of the html for the entire page, instead of just the gridview (which is probably the unreadable content Excel is talking about).

这是我的代码:

public void ExcelDownload(object sender, EventArgs e)
    {
        DataSet _MailingListUsers = db.GetMailingList();
        DataTable mailTable = _MailingListUsers.Tables[0];

        DumpExcel(mailTable);

    }

    private void DumpExcel(DataTable tbl)
    {
        using (ExcelPackage pck = new ExcelPackage())
        {
            //Create the worksheet
            ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Mailing List");

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

            //Header Titles
            ws.Cells["A1"].Value = "Employee Name";
            ws.Cells["B1"].Value = "Email Address";
            ws.Cells["C1"].Value = "Phone";
            ws.Cells["D1"].Value = "Business Unit";
            ws.Cells["E1"].Value = "Site";

            ws.Cells["A1"].AutoFitColumns();

            //Format the header for column 1-3
            using (ExcelRange rng = ws.Cells["A1:E1"])
            {
                rng.Style.Font.Bold = true;
                //Set Pattern for the background to Solid
                rng.Style.Fill.PatternType = ExcelFillStyle.Solid;    
                //Set color to dark blue
                rng.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));  
                rng.Style.Font.Color.SetColor(System.Drawing.Color.White);
            }


            //Write it back to the client
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;  filename=MailingList.xlsx");
            Response.BinaryWrite(pck.GetAsByteArray());
        }
    }

对可能发生的事情有任何想法吗?在我尝试将 HTML 数据发送到 Excel 时遇到同样的问题后，有人建议使用 EPPlus，它发送的是整个页面，而不仅仅是 gridView.

Any ideas about what might be happening? Someone suggested using EPPlus after I had the same problems trying to send HTML data to Excel, and it was sending the entire page, instead of just the gridView.

谢谢

推荐答案

我缺少 Response.Clear() 和 Response.End():

try {
    var pck = new OfficeOpenXml.ExcelPackage();
    var ws = pck.Workbook.Worksheets.Add("Mailing List");
    ws.Cells["A2"].LoadFromDataTable(tbl, false);
    Response.Clear();
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;  filename=MailingList.xlsx");
    Response.BinaryWrite(pck.GetAsByteArray());
} catch (Exception ex) {
    //log error
}
Response.End();

这篇关于使用 EPPlus，我试图将 gridview 导出到 Excel 工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！