在EPPlus中编写Excel文件 [英] Writing an Excel file in EPPlus

问题描述

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

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:

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;
    }
}

所有注释的代码都是我发现的不同东西在互联网上尝试。请注意，这是一个学校组织，我们不使用MVC。然后，我使用文件后面的代码来像这样提取此方法：

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:

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.

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

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.

你们有什么建议吗？有？我很乐意澄清我尚未完全解决的所有问题。

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.

推荐答案

您是否看过提供的示例

这是向您展示如何创建文件
http://epplus.codeplex.com/wikipage?title=ContentSheetExample

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

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

This one shows you how to use it to stream back a file 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();
}

这篇关于在EPPlus中编写Excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！