使用EPPlus.Core开发Excel导出服务：如何向控制器返回文件？

Instead of trying to return an IActionResult directly from your service (which ties it to ASP.NET Core's PageModel/Controller infrastructure), you should have your service return the raw Excel content along with metadata like filename and content type. Then your controller can use that data to create the appropriate FileResult using the built-in File() method.

Here's a step-by-step implementation:

1. Define a Result Class for Excel Exports #

First, create a simple class to hold the Excel data and related details. This keeps your service focused on generating content, not HTTP concerns.

public class ExcelExportResult
{
    public byte[] FileBytes { get; set; }
    public string FileName { get; set; }
    public string ContentType => "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
}

2. Create Your Export Service Interface and Implementation #

Your service will handle executing the stored procedure, generating the Excel file with EPPlus, and returning the ExcelExportResult.

Interface #

public interface IExportService
{
    Task<ExcelExportResult> GenerateExcelFromStoredProcedureAsync();
}

Implementation #

using EPPlus.Core;
using System.Data;
using System.Data.SqlClient;
using OfficeOpenXml;

public class ExportService : IExportService
{
    private readonly string _connectionString;

    // Inject your connection string via configuration
    public ExportService(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("YourDbConnection");
    }

    public async Task<ExcelExportResult> GenerateExcelFromStoredProcedureAsync()
    {
        // 1. Execute the stored procedure to get your data
        List<YourDataModel> data;
        using (var conn = new SqlConnection(_connectionString))
        {
            await conn.OpenAsync();
            using (var cmd = new SqlCommand("YourStoredProcedureName", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                // Add parameters if needed: cmd.Parameters.AddWithValue("@ParamName", value);
                
                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    data = reader.MapToList<YourDataModel>(); // Use a mapping library or manual mapping
                }
            }
        }

        // 2. Generate Excel with EPPlus
        using (var package = new ExcelPackage())
        {
            var worksheet = package.Workbook.Worksheets.Add("Exported Data");
            
            // Load data into the worksheet (EPPlus's helper method)
            worksheet.Cells.LoadFromCollection(data, true);
            
            // Optional: Format the worksheet (adjust columns, styles, etc.)
            worksheet.Cells.AutoFitColumns();

            // 3. Convert the package to a byte array and return the result
            return new ExcelExportResult
            {
                FileBytes = package.GetAsByteArray(),
                FileName = $"Export_{DateTime.UtcNow:yyyyMMddHHmmss}.xlsx"
            };
        }
    }
}

3. Use the Service in Your Controller #

Now your controller can call the service and convert the ExcelExportResult into a FileResult using the File() method (available in ControllerBase or Controller).

public class ExportController : ControllerBase
{
    private readonly IExportService _exportService;

    public ExportController(IExportService exportService)
    {
        _exportService = exportService;
    }

    [HttpPost]
    public async Task<IActionResult> OnPostExport()
    {
        var exportResult = await _exportService.GenerateExcelFromStoredProcedureAsync();
        
        // Use the File method to return the Excel file to the client
        return File(exportResult.FileBytes, exportResult.ContentType, exportResult.FileName);
    }
}

Key Notes #

• Separation of Concerns: Your service only handles data retrieval and Excel generation, making it easier to test and reuse across different parts of your application.
• Alternatives: Instead of returning a byte array, you could return a MemoryStream (just remember to reset the stream's position to 0 before passing it to File()). However, byte arrays are simpler to work with as they don't require managing stream disposal.
• EPPlus Configuration: Make sure you have EPPlus.Core installed via NuGet, and if using .NET Core 3.0+, you may need to configure EPPlus's license context in your Startup.cs or Program.cs:

  ExcelPackage.LicenseContext = LicenseContext.NonCommercial; // Or Commercial if applicable
  

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