ExcelSoftArtisans - Bug on Save method with open Stream - Cannot accessed closed file

Question

I have the following issue:

I’m doing an export of an ASP.Net GridView directly to an excel file.

I’m setting an image as a header in this method:

private static void insertPageHeaderFooter(ExcelInterfaceSoftArtisans excel,DateTime generatedDateTime)
    {
        StringBuilder builderFooterLeft = new StringBuilder();
        builderFooterLeft.Append("&08Comfone AG                  Tel: +41 31 341 10 10");
        builderFooterLeft.Append("\r");
        builderFooterLeft.Append("&08Nussbaumstrasse 25     Fax: +41 31 341 10 11");
        builderFooterLeft.Append("\r");
        builderFooterLeft.Append("&08CH-3000 Bern 22           www.comfone.com");

        StringBuilder builderFooterRight = new StringBuilder();
        String sDateTime = generatedDateTime.ToString(CultureInfoHandler.ShortDateShortTimeFormat);
        builderFooterRight.Append("&08&F");
        builderFooterRight.Append("\r");
        builderFooterRight.Append(string.Format("&08 {0}", sDateTime));
        builderFooterRight.Append("\r"); //new line
        builderFooterRight.Append("&08Page &P of &N");

        excel.SetHeader("&G", 0.6, HeaderFooterSection.Section.Left, false);
        excel.SetFooter(builderFooterLeft.ToString(), HeaderFooterSection.Section.Left, false);
        excel.SetFooter(builderFooterRight.ToString(), HeaderFooterSection.Section.Right, false);
    }

protected void SetHeader(string sText, double dImageSizeFactor, HeaderFooterSection.Section section, Worksheet sheet)
    {
       string headerAbsolutePath = HttpContext.Current.Server.MapPath("~/Resources/Mandates/CHECF.png");                                    
        Stream imageStream = new FileStream(headerAbsolutePath, FileMode.Open, FileAccess.Read);
        Size imageSize = Image.FromStream(imageStream).Size;
        imageStream = new FileStream(headerAbsolutePath, FileMode.Open, FileAccess.Read);
        HeaderFooterSection header = sheet.PageSetup.GetHeader(section);
        header.SetContent(sText, imageStream);
        header.SetContent(sText);
        header.GetPicture().Height = (int)(imageSize.Height * dImageSizeFactor);
        header.GetPicture().Width = (int)(imageSize.Width * dImageSizeFactor);

        imageStream.Close();
    }

As you can see on the last line, I close the Stream.

Now, I want to save my excel file this way:

HttpContext.Current.Response.ClearContent();
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.ClearHeaders();
                excel.SaveWorkbook(sFileName, HttpContext.Current.Response, false);
/// <summary>
    /// Saves the current Workbook under the given filename
    /// </summary>
    /// <param name="filename"></param>
    /// <param name="response"></param>
    /// <param name="openInBrowser"></param>
    public void SaveWorkbook(string filename, HttpResponse response, bool openInBrowser)
    {
        if (book != null)
        {
            application.Save(book, response, filename, openInBrowser);
        }
    }

but when I close the stream in the SetHeader method, I get the following error:

    Error Message: Cannot access a closed file.


 Stack Trace:   at System.IO.__Error.FileNotOpen() 
at System.IO.FileStream.Seek(Int64 offset, SeekOrigin origin)

and when I don’t close the stream in the SetHeader method, the file is correctly saved.

Are you aware of this bug? How is it possible that I need to have an open stream in order to save an excel file? What can I do to fix that?

I’m attaching you the whole classes I’m using, so you can identify the problem better.

Thanks for your quick answer and solution on this problem.

What version of ExcelWriter are you running? Is this a .xls or .xlsx file? — Aviva M., May 01 '13 at 14:46
As a workaround could you use header.SetContent(stest, headerAbsolutePath); There is an overload that takes the file path http://wiki.softartisans.com/display/EW8/HeaderFooterSection.SetContent%28String%2C+String%29 and then you do not even need to stream in the image. — Sam Plus Plus, May 01 '13 at 14:52

AlisonB · Answer 1 · 2013-05-02T14:01:26.810

2

[Disclaimer: I am the product owner of OfficeWriter]

This issue has been confirmed as a bug and has been submitted to the OfficeWriter development team.

In the meantime, I would recommend Sam's suggested workaround of using the overload that takes the image's file path instead of the image stream.

Here is a generic code snippet for how to insert an image into the header of an Excel file using the file path overload of HeaderFooterSection.SetContent():

//Open the workbook and get a handle on the worksheet that will contain the
//image in the header
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Open(Page.MapPath("\\template.xlsx"));
Worksheet ws = wb.Worksheets["Sheet1"];

//Set the header
HeaderFooterSection header = ws.PageSetup.GetHeader(HeaderFooterSection.Section.Left);
header.SetContent("&G", Page.MapPath("\\images\\image1.png"));

Please see our documentation for additional reference on the SetContent() overloads and using ExcelWriter to format headers and footers in workbooks.

edited May 02 '13 at 14:01

answered May 02 '13 at 11:24

AlisonB

395
1
6

Hello, thanks for the workaround but it doesn't change anything to use the overload SetContent(string,string). The issue is still there, because apparently in the method Save of ExcelApplication, you manipulate the stream and it's impossible to access to the header picture during this time. For instance, if I want to export files to excel, it will fail as the 2nd file will try to access the header resource which is currently open. The real question is why do you need the stream open while saving???? – user2338530 May 03 '13 at 10:07
In the ExcelApplication object, more things happen in the Save() method than just saving the file to disk or streaming it to the browser. Some of the final construction of the workbook is done at that point. However, in this case we could release the handle to the stream (or file) for the image earlier. This issue has been submitted to Development. In the meantime we'll look for another workaround. – Aviva M. May 06 '13 at 14:29

score 1 · Answer 2 · answered Jul 08 '13 at 16:03

1

This issue has been addressed in the recent 8.5.1 release of OfficeWriter. See the change log.

answered Jul 08 '13 at 16:03

Aviva M.

381
1
9

ExcelSoftArtisans - Bug on Save method with open Stream - Cannot accessed closed file

2 Answers2