使用SpreadsheetGear保存工作簿时遇‘无法访问已关闭文件’错误求助

Let's break down what's causing this error and how to fix it:

The Root Cause #

First off, the web.config settings you added are for handling request size/timeouts—they don't have anything to do with the "closed file" error you're seeing, so that's why they didn't help.

The real issue boils down to two key problems in your code:

1. You're wrapping the incoming stream in a using block, which automatically closes/disposes the stream when the block exits. If this stream is managed by an external caller (like an ASP.NET response stream), closing it early will trigger errors when the caller tries to use it later.
2. Many web-based streams (like Response.OutputStream) don't support seeking, and SpreadsheetGear's SaveToStream requires a seekable stream in some cases.

Fixed Code Implementation #

Here's how to adjust your ExcelExporter class to resolve both issues:

public class ExcelExporter 
{ 
    public void Export(System.IO.Stream stream, IEnumerable<LabDataEntity> readings) 
    { 
        // Don't wrap the incoming stream in using—let the caller manage its lifecycle
        var workset = SpreadsheetGear.Factory.GetWorkbookSet(); 
        // Use using for the workbook we create to ensure it's properly disposed
        using (var workBook = workset.Workbooks.Add()) 
        { 
            var errorWorkSheet = workBook.Worksheets.Add(); 
            errorWorkSheet.Name = "File Errors"; 
            SpreadsheetGear.IRange errorCells = errorWorkSheet.Cells; 
            errorWorkSheet.Cells.ShrinkToFit = true; 
            
            // Fixed off-by-one header indexing (your original code left column 0 empty)
            errorCells[0, 0].Value = "DCP ID"; 
            errorCells[0, 1].Value = "DCP Name"; 
            errorCells[0, 2].Value = "Site ID"; 
            errorCells[0, 3].Value = "Site Name"; 
            
            int rowIndex = 1; 
            foreach (var rdg in readings) 
            { 
                errorCells[rowIndex, 0].Value = rdg.DCPCode; 
                errorCells[rowIndex, 1].Value = rdg.DCPName; 
                errorCells[rowIndex, 2].Value = rdg.SiteID; 
                errorCells[rowIndex, 3].Value = rdg.SiteName; 
                rowIndex++; 
            } 
            
            // Handle non-seekable streams (common in web scenarios) with a MemoryStream buffer
            if (!stream.CanSeek)
            {
                using (var tempStream = new MemoryStream())
                {
                    workBook.SaveToStream(tempStream, SpreadsheetGear.FileFormat.OpenXMLWorkbook);
                    tempStream.Position = 0; // Reset to start of buffered data
                    tempStream.CopyTo(stream); // Copy to target stream
                }
            }
            else
            {
                workBook.SaveToStream(stream, SpreadsheetGear.FileFormat.OpenXMLWorkbook);
            }
        }
    } 
}

Additional Notes #

• I fixed an off-by-one index issue in your headers: your original code started headers at column 1 but wrote data to column 0, leaving an empty first column in your output.
• If you're using this in an ASP.NET application, here's how to call the exporter correctly (making sure you don't close the response stream early):

public ActionResult DownloadErrors()
{
    // Replace with your actual data retrieval logic
    IEnumerable<LabDataEntity> readings = GetYourLabData();
    
    Response.Clear();
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("Content-Disposition", "attachment; filename=FileErrors.xlsx");
    
    var exporter = new ExcelExporter();
    exporter.Export(Response.OutputStream, readings);
    
    Response.End();
    return new EmptyResult();
}

Double-check that wherever you're calling Export, the stream you pass in is open, writable, and not being closed/disposed before the SaveToStream operation completes.

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