使用ClosedXML在同一函数中生成多个Excel文件失败，寻求技术建议

Got it, let's work through this problem. The core thing to note here is that a single FileResult can only return one file—so to deliver two Excel files from your Export function, we need to package both into a ZIP archive first. Let's break this down step by step, fixing your code along the way.

First, Fix the Original Code's Small Issues #

Looking at your current code, there's an undefined variable wb (you probably meant ts from your XLWorkbook instance), and you're only generating one workbook. Let's build on that to add the second file.

Step-by-Step Solution #

1. Generate both Excel workbooks (each with their own data and customizations)
2. Save each workbook to a separate MemoryStream
3. Create a ZIP archive containing both Excel files
4. Return the ZIP as a single FileResult

Full Modified Code #

using System.IO;
using System.IO.Compression;
using ClosedXML.Excel;
using Microsoft.AspNetCore.Mvc;
using System.Data;

public FileResult Export()
{
    // --------------------------
    // Generate First Excel File
    // --------------------------
    DataTable testTable1 = new DataTable("test1");
    testTable1.Columns.AddRange(new DataColumn[] { new DataColumn("Name") });
    foreach (var record in recordtest) // Assuming recordtest is your existing data source
    {
        testTable1.Rows.Add(record.name);
    }

    MemoryStream stream1 = new MemoryStream();
    using (XLWorkbook wb1 = new XLWorkbook())
    {
        var testSheet1 = wb1.Worksheets.Add(testTable1);
        testSheet1.Cell("D4").Value = "First Name";
        testSheet1.Range("A4:A5").Merge();
        testSheet1.Range("A4:Q5").Columns().Style.Fill.BackgroundColor = XLColor.Almond;
        wb1.SaveAs(stream1);
        stream1.Position = 0; // Reset stream cursor to start for later reading
    }

    // --------------------------
    // Generate Second Excel File
    // --------------------------
    DataTable testTable2 = new DataTable("test2");
    testTable2.Columns.AddRange(new DataColumn[] { new DataColumn("Age") });
    // Replace with your second data source
    foreach (var record in recordtest2) 
    {
        testTable2.Rows.Add(record.age);
    }

    MemoryStream stream2 = new MemoryStream();
    using (XLWorkbook wb2 = new XLWorkbook())
    {
        var testSheet2 = wb2.Worksheets.Add(testTable2);
        // Add custom styling for the second sheet as needed
        testSheet2.Cell("D4").Value = "Age";
        wb2.SaveAs(stream2);
        stream2.Position = 0; // Reset stream cursor
    }

    // --------------------------
    // Package into ZIP Archive
    // --------------------------
    using (MemoryStream zipStream = new MemoryStream())
    {
        using (ZipArchive archive = new ZipArchive(zipStream, ZipArchiveMode.Create, true))
        {
            // Add first Excel file to ZIP
            var entry1 = archive.CreateEntry($"test-{DateTime.Now:yyyyMMdd}-1.xlsx");
            using (var entryStream = entry1.Open())
            {
                stream1.CopyTo(entryStream);
            }

            // Add second Excel file to ZIP
            var entry2 = archive.CreateEntry($"test-{DateTime.Now:yyyyMMdd}-2.xlsx");
            using (var entryStream = entry2.Open())
            {
                stream2.CopyTo(entryStream);
            }
        }

        zipStream.Position = 0;
        return File(zipStream.ToArray(), "application/zip", $"ExcelFiles-{DateTime.Now:yyyyMMdd}.zip");
    }
}

Key Notes: #

• Stream Position Reset: Every time you save a workbook to a MemoryStream, you need to set stream.Position = 0—this moves the "cursor" back to the start of the stream so it can be read when adding to the ZIP.
• ZIP Archive: We use ZipArchive from System.IO.Compression to package both files. Make sure you have the necessary using directives for this namespace.
• Customization: Adjust the data sources, sheet styling, and file names to match your actual requirements.

If you didn't need two separate files and just wanted multiple sheets in one Excel, you could add both worksheets to a single XLWorkbook—but since you asked for two files, the ZIP approach is the standard, user-friendly way to deliver them.

内容的提问来源于stack exchange，提问作者ericso