使用 EPPlus，我试图将 gridview 导出到 Excel 工作表

本文介绍了使用 EPPlus，我试图将 gridview 导出到 Excel 工作表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

限时送ChatGPT账号..

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屋！