.NET MVC中基于OpenXML实现Excel模板内存编辑下载的技术问询

Got it, let's walk through exactly how to build this feature in your .NET MVC project using the OpenXML SDK—no Interop required, and full concurrency safety since we're working entirely in memory. Here's a step-by-step breakdown with practical code examples:

First, make sure you have the OpenXML SDK installed via NuGet. You can run this command in the Package Manager Console:

Install-Package DocumentFormat.OpenXml

Or search for DocumentFormat.OpenXml in the NuGet Package Manager and install it directly.

We'll never modify the original template file—instead, we'll copy it to a MemoryStream for in-memory editing. This ensures multiple users can request the template simultaneously without conflicts.

Here's the base action method to start with:

public ActionResult DownloadCustomizedExcel()
{
    // Path to your Excel template (store this in App_Data or a secure folder)
    string templatePath = Server.MapPath("~/App_Data/YourTemplate.xlsx");

    using (var templateFileStream = new FileStream(templatePath, FileMode.Open, FileAccess.Read))
    using (var memoryStream = new MemoryStream())
    {
        // Copy the template to memory
        templateFileStream.CopyTo(memoryStream);
        memoryStream.Position = 0;

        // We'll edit the Excel in memory here (see next steps)
        EditExcelInMemory(memoryStream, User.Identity.Name);

        // Reset stream position before downloading
        memoryStream.Position = 0;

        // Return the customized file as a download
        return File(
            memoryStream,
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            $"CustomReport_{User.Identity.Name}.xlsx"
        );
    }
}

Next, we'll create a helper method to modify the second worksheet's data using the logged-in user's information. We'll clear existing data (keeping headers) and insert fresh user-specific rows.

private void EditExcelInMemory(MemoryStream excelStream, string userName)
{
    using (var spreadsheetDoc = SpreadsheetDocument.Open(excelStream, true))
    {
        WorkbookPart workbookPart = spreadsheetDoc.WorkbookPart;

        // Locate the second worksheet (use its name for reliability)
        Sheet sheet2 = workbookPart.Workbook.Descendants<Sheet>()
            .FirstOrDefault(s => s.Name.Equals("Sheet2", StringComparison.OrdinalIgnoreCase));
        if (sheet2 == null)
            throw new InvalidOperationException("Sheet2 not found in the template.");

        WorksheetPart worksheetPart2 = (WorksheetPart)workbookPart.GetPartById(sheet2.Id);
        Worksheet worksheet2 = worksheetPart2.Worksheet;

        // Clear existing data rows (keep the header row at index 1)
        var existingDataRows = worksheet2.Descendants<Row>()
            .Where(r => r.RowIndex > 1)
            .ToList();
        foreach (var row in existingDataRows)
            row.Remove();

        // Fetch user-specific data (replace this with your actual data logic)
        var userDataSource = GetUserSpecificData(userName);

        // Insert new data rows starting at index 2
        uint currentRowIndex = 2;
        foreach (var dataItem in userDataSource)
        {
            Row newRow = new Row { RowIndex = currentRowIndex };

            // Add cells to the row (adjust based on your data structure)
            newRow.Append(
                CreateCell(dataItem.Column1Value, CellValues.String),
                CreateCell(dataItem.Column2Value, CellValues.Number),
                CreateCell(dataItem.Column3Value, CellValues.Date)
            );

            worksheet2.Append(newRow);
            currentRowIndex++;
        }

        // Save changes to the worksheet
        worksheet2.Save();

        // Optional: Update Sheet1's range picker if needed (see Step 4)
        UpdateSheet1RangePicker(workbookPart, userDataSource.Count);
    }
}

// Helper to create formatted cells
private Cell CreateCell(string value, CellValues dataType)
{
    Cell cell = new Cell();
    cell.CellValue = new CellValue(value);
    cell.DataType = new EnumValue<CellValues>(dataType);
    return cell;
}

// Replace this with your actual data retrieval logic
private List<UserDataItem> GetUserSpecificData(string userName)
{
    // Example: Fetch data from DB based on logged-in user
    return new List<UserDataItem>
    {
        new UserDataItem { Column1Value = "Item 1", Column2Value = "100", Column3Value = DateTime.Now.ToString("yyyy-MM-dd") },
        new UserDataItem { Column1Value = "Item 2", Column2Value = "200", Column3Value = DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") }
    };
}

// DTO for your data (adjust properties as needed)
private class UserDataItem
{
    public string Column1Value { get; set; }
    public string Column2Value { get; set; }
    public string Column3Value { get; set; }
}

If your first worksheet uses a range picker (like a data validation dropdown) tied to Sheet2's data, you need to ensure the range updates to match the new data length.

Add this helper method to handle the update:

private void UpdateSheet1RangePicker(WorkbookPart workbookPart, int dataRowCount)
{
    // Locate the first worksheet
    Sheet sheet1 = workbookPart.Workbook.Descendants<Sheet>()
        .FirstOrDefault(s => s.Name.Equals("Sheet1", StringComparison.OrdinalIgnoreCase));
    if (sheet1 == null) return;

    WorksheetPart worksheetPart1 = (WorksheetPart)workbookPart.GetPartById(sheet1.Id);
    Worksheet worksheet1 = worksheetPart1.Worksheet;

    // Find all data validation rules in Sheet1
    var dataValidations = worksheet1.Descendants<DataValidations>().FirstOrDefault();
    if (dataValidations == null) return;

    foreach (var validation in dataValidations.Descendants<DataValidation>())
    {
        // Assume the original formula references Sheet2!$A$2:$A$100 (adjust to your template's actual range)
        string originalFormula = validation.Formula1.Text;
        // Calculate the new end row (header row + data rows)
        int endRow = dataRowCount + 1;
        // Update the formula to use the new range
        string updatedFormula = originalFormula.Replace("$100", $"${endRow}");
        validation.Formula1.Text = updatedFormula;
    }

    worksheet1.Save();
}

Note: If your template uses a dynamic named range (e.g., OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A)-1,1)), you might not need this step—Excel will automatically update the range when data changes.

• Memory Stream Management: Always reset the MemoryStream.Position to 0 before reading/writing to avoid corrupted files.
• Concurrency Safety: Since each request works with its own copy of the template in memory, there's no risk of race conditions or modifying the original file.
• Data Type Matching: Ensure the cell data types match your template's formatting (e.g., dates should use CellValues.Date to avoid Excel displaying them as strings).
• Template Compatibility: Only use .xlsx files (OpenXML format)—.xls files use the older binary format and aren't supported by the OpenXML SDK.

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