使用EPPLUS C#创建Excel XLSX文件的ZipArchive

Question

使用EPPLUS C#创建Excel XLSX文件的ZipArchive

4

我有以下函数，用于返回一个FileStreamResult给我的ASP.NET MVC应用程序。

/// <summary>
/// Generates a FileStreamResult containing a zip file with the EXCEL file in it
/// </summary>
/// <typeparam name="T">Type of object in the object list parameter</typeparam>
/// <param name="objectList">The object list enumerable. This contains the data for the EXCEL file</param>
/// <param name="fileName">The file name of the EXCEL</param>
/// <returns>FileStreamResult</returns>
public FileStreamResult CreateZipFileFileStreamResult<T>(IEnumerable<T> objectList, string fileName)
{   
    var ms = new MemoryStream();

    var contentType = System.Net.Mime.MediaTypeNames.Application.Zip;

    ExcelPackage excelPackage = null;

    ZipArchive archive = null;

    try
    {
        excelPackage = new ExcelPackage(ms);

        var workSheet1 = excelPackage.Workbook.Worksheets.Add("Sheet1");

        workSheet1.Cells["A1"].LoadFromCollection<T>(objectList, true);

        excelPackage.SaveAs(ms);



        ms.Seek(0, SeekOrigin.Begin);

        archive = new ZipArchive(excelPackage.Stream, ZipArchiveMode.Create, true);

        var newEntry = archive.CreateEntry(fileName, System.IO.Compression.CompressionLevel.Fastest);

        var newEntryStream = newEntry.Open();

        var fsr = new FileStreamResult(excelPackage.Stream, contentType);
        fsr.FileDownloadName = fileName + ".zip";

        return fsr;
    }
    catch (Exception ex)
    {
        if (archive != null)
            archive.Dispose();

        if (excelPackage != null)
            excelPackage.Dispose();

        if (ms != null)
            ms.Dispose();

        throw;
    }
}

该函数返回内容，但是它以分割的XML格式而不是一个XLSX文件的形式返回。

我希望它能够返回一个压缩的单一文件。

这是当前结果的样子。

在使用@kuujinbo提供的帮助后，我创建了这个函数。请注意，由于某种原因，FileContentResult有效而FileStreamResult无效。

        /// <summary>
        /// Generates a FileStreamResult containing a zip file with the EXCEL file in it
        /// </summary>
        /// <typeparam name="T">Type of object in the object list parameter</typeparam>
        /// <param name="objectList">The object list enumerable. This contains the data for the EXCEL file</param>
        /// <param name="fileName">The file name of the EXCEL</param>
        /// <returns>FileStreamResult</returns>        
        public FileContentResult CreateZipFileFileContentResult<T>(IEnumerable<T> objectList, string fileName)
        {
            var contentType = System.Net.Mime.MediaTypeNames.Application.Zip;

            using (var memoryStream = new System.IO.MemoryStream())
            {
                using (Ionic.Zip.ZipFile zip = new Ionic.Zip.ZipFile())
                {
                    using (var package = new OfficeOpenXml.ExcelPackage())
                    {
                        var workSheet1 = package.Workbook.Worksheets.Add("Sheet1");
                        workSheet1.Cells["A1"].LoadFromCollection<T>(objectList, true);

                        var firstRow = workSheet1.Row(1);
                        if (firstRow != null)
                            firstRow.Style.Font.Bold = true;

                        zip.AddEntry(fileName, package.GetAsByteArray());
                        zip.Save(memoryStream);
                        var fcr = new FileContentResult(memoryStream.ToArray(), contentType); //NOTE: Using a File Stream Result will not work.
                        fcr.FileDownloadName = fileName + ".zip";
                        return fcr;
                    }
                }
            }
        }

- Dragos Durlut

看起来那就是它应该是的样子。xlsx文件是被压缩的OpenDocument结构。我认为您的文件浏览器只是在目录层次结构中透明地显示ZIP内容（考虑到左上角的图标）。当您将.zip重命名为.xlsx时，您将能够用Excel打开它。 - Cee McSharpface

1

你从未向newEntryStream写入任何内容？这个函数似乎只是返回EPPlus输出流（它已经在内部是一个ZIP并且应该有xlsx扩展名）带有.zip扩展名的流。 - Cee McSharpface

看起来你是正确的。我会去看看。 - Dragos Durlut

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- kuujinbo · Accepted Answer

EPPlus内部使用了一个版本的DotNetZip，（查看源代码）可以尝试做同样的事情。在我看来，他们的设计决策说明了为什么一些人选择不使用ZipArchive。

class TestObject
{
    public int Id { get; set; }
    public string Name { get; set; }
}

IEnumerable<TestObject> objectList = new List<TestObject>()
{
    { new TestObject() {Id = 0, Name = "zero" } },
    { new TestObject() {Id = 1, Name = "one" } }
};
string ExcelName = "test.xlsx";
string ZipName = "test.zip"; 

public ActionResult DotnetZip()
{
    using (var stream = new MemoryStream())
    {
        using (ZipFile zip = new ZipFile())
        {
            using (var package = new ExcelPackage())
            {
                var sheet = package.Workbook.Worksheets.Add("Sheet1");
                sheet.Cells["A1"].LoadFromCollection<TestObject>(objectList, true);
                zip.AddEntry(ExcelName, package.GetAsByteArray());
                zip.Save(stream);
                return File(
                    stream.ToArray(),
                    System.Net.Mime.MediaTypeNames.Application.Zip,
                    ZipName
                );
            }
        }
    }
}

已测试并可用: