C# 用 EPPlus 编写 Excel 文件

Writing an Excel file in EPPlus

提问by Rahlord

I have been stuck on this for days and despite all of the help out there, none of these solutions have been working for me. What I want to do is create an excel file using the EPPlus library with some basic data in it that I am pulling from a stored procedure. This is the code that I have in my ExportDocument.cs file:

我已经被困在这个问题上好几天了，尽管那里提供了所有帮助，但这些解决方案都没有对我有用。我想要做的是使用 EPPlus 库创建一个 excel 文件，其中包含我从存储过程中提取的一些基本数据。这是我在 ExportDocument.cs 文件中的代码：

public static ExcelPackage CreateExcelDocument(int [] arr)
{
    String path = @"D:\temp\testsheet3.xlsx";
    //FileInfo newFile = null;
    /*if (!File.Exists(path + "\testsheet2.xlsx"))
    newFile = new FileInfo(path + "\testsheet2.xlsx");
    else
        return newFile;*/
    using (ExcelPackage package = new ExcelPackage())
    {
        ExcelWorksheet ws = package.Workbook.Worksheets.Add("testsheet");
        ws.Cells["B1"].Value = "Number of Used Agencies";
        ws.Cells["C1"].Value = "Active Agencies";
        ws.Cells["D1"].Value = "Inactive Agencies";
        ws.Cells["E1"].Value = "Total Hours Volunteered";
        ws.Cells["B1:E1"].Style.Font.Bold = true;

        int x = 2;
        char pos = 'B';
        foreach (object o in arr)
        {
            String str = pos + x.ToString();
            ws.Cells[str].Value = o.ToString();
            if (pos > 'E')
            {
                pos = 'B';
                x++;
            }
            pos++;
        }
        package.Save();
        return package;
    }
}

All the commented code is different things that I have found on the internet to try. Note that this a school organization and we are not using MVC. I am then using the code behind file to pull this method like this:

所有注释的代码都是我在互联网上找到的不同的东西。请注意，这是一个学校组织，我们没有使用 MVC。然后我使用隐藏文件的代码来拉出这样的方法：

protected void GenerateReport(Object o, EventArgs e)
{
    Session["reportSession"] = ddReport.SelectedItem.Value.ToString();
    int [] arr = new int [ReportRepository.GetAgencyCounts().Count];
    ReportRepository.GetAgencyCounts().CopyTo(arr, 0);

    ExcelPackage pck = ExportDocument.CreateExcelDocument(arr);
    /*try
    {
        byte [] data = ExportDocument.CreateExcelDocument(arr).GetAsByteArray();
        Response.Clear();
        Response.Buffer = true;
        Response.BinaryWrite(data);
        Response.AddHeader("content-length", data.Length.ToString());
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.Flush();
        Response.Close();
        Response.End();
    }
    catch (Exception ex) { }*/

    /*var stream = new MemoryStream();
    pck.SaveAs(stream);

    String filename = "myfile.xlsx";
    String contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    var cd = new System.Net.Mime.ContentDisposition
    {
        Inline = false,
        FileName = filename
    };
    Response.AppendHeader("Content-Disposition", cd.ToString());
    stream.Position = 0;

    return File(stream, contentType, filename);*/

    /*Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AppendHeader("Content-Disposition", "attachment; filename=" + file.Name);
    Response.TransmitFile(Path.GetFullPath(file.Name));
    Response.Flush();
    Response.Close();*/

    /*Response.ClearHeaders();
    Response.BinaryWrite(pck.GetAsByteArray());
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;  filename=Sample2.xlsx");
    Response.Flush();
    Response.Close();*/
}

Again note that all the commented code is things that I have found from various sources that have not worked.

再次注意，所有注释的代码都是我从各种来源中发现的，但没有用。

So I am not getting any errors but when I click the button on my application to execute the code behind method, nothing is happening. It is loading and it runs through but there are no files created, nothing is opening up. This is the first time I have ever used EPPlus and I am not wholly familiar with exporting things to excel programmatically so I feel lost here.

所以我没有收到任何错误，但是当我单击应用程序上的按钮来执行隐藏方法的代码时，什么也没有发生。它正在加载并运行，但没有创建文件，也没有打开任何文件。这是我第一次使用 EPPlus，我并不完全熟悉以编程方式将东西导出到 excel，所以我在这里感到迷茫。

Are there any suggestions that you guys have? I would be happy to clarify any points that I have not hit upon fully as well.

大家有什么建议吗？我很乐意澄清我还没有完全理解的任何观点。

回答by Dreaddan

Have you looked at the samples provided with EPPlus?

您是否查看过随 EPPlus 提供的示例？

This one shows you how to create a file http://epplus.codeplex.com/wikipage?title=ContentSheetExample

这个向你展示了如何创建一个文件 http://epplus.codeplex.com/wikipage?title=ContentSheetExample

This one shows you how to use it to stream back a file http://epplus.codeplex.com/wikipage?title=WebapplicationExample

这个向您展示了如何使用它来回传文件 http://epplus.codeplex.com/wikipage?title=WebapplicationExample

This is how we use the package to generate a file.

这就是我们使用包生成文件的方式。

var newFile = new FileInfo(ExportFileName);
using (ExcelPackage xlPackage = new ExcelPackage(newFile))
{                       
    // do work here                            
    xlPackage.Save();
}

回答by banging

It's best if you worked with DataSets and/or DataTables. Once you have that, ideally straight from your stored procedure with proper column names for headers, you can use the following method:

最好使用DataSets 和/或DataTables。一旦你有了它，最好直接从你的存储过程中使用正确的标题列名，你可以使用以下方法：

ws.Cells.LoadFromDataTable(<DATATABLE HERE>, true, OfficeOpenXml.Table.TableStyles.Light8);

ws.Cells.LoadFromDataTable(<DATATABLE HERE>, true, OfficeOpenXml.Table.TableStyles.Light8);

.. which will produce a beautiful excelsheet with a nice table!

.. 这将产生一个漂亮的 excelsheet 和一张漂亮的桌子！

Now to serve your file, assuming you have an ExcelPackageobject as in your code above called pck..

现在为您的文件提供服务，假设您ExcelPackage在上面的代码中有一个名为pck..

Response.Clear();

Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", "attachment;filename=" + sFilename);

Response.BinaryWrite(pck.GetAsByteArray());
Response.End();

回答by Adarsh Shah

If you have a collection of objects that you load using stored procedure you can also use LoadFromCollection.

如果您有使用存储过程加载的对象集合，您也可以使用LoadFromCollection.

using (ExcelPackage package = new ExcelPackage(file))
{
    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("test");

    worksheet.Cells["A1"].LoadFromCollection(myColl, true, OfficeOpenXml.Table.TableStyles.Medium1);

    package.Save();
}