OpenXML - Use SpreadsheetDocument.Open in a static class to make a kinf of Excel library

33 Views Asked by At

I would like to make a helper to use OpenXML. I wrote a static class to enclose all the plumbing of accessing & updating my Excel file. But it do not work fine because (I guess) of a problem with the stream containing the file. The stream is read in program.cs and passed as argument to OpenXMLHelper. OpenXMLHelper is supposed to update the stream. But the stream is not updated !

NOTE: I remove all the tests, ... to clarify the code.

I would like to use my static class like this :

[Program.cs]
// Read the data into a stream
using (stream parameterFile = DocuAPIs.GetAttachment()
{
    // Pass the stream to the helper class & Change the file
    OpenXMLHelper.OpenWorkbook(parameterFile);
    OpenXMLHelper.SetCellValue(1, 1, "TESTRUS V2", "DONNEES");
    OpenXMLHelper.Save();

    // Update the stream to the server
    DocuAPIs.UpdateAttachment(parameterFile);
}

My static class is defined like this :

[OpenXMLHelper.cs]
using DocumentFormat.OpenXml;
using ...;

public static class OpenXMLHelper
{
    private static SpreadsheetDocument? document;
    ...

    public static WorkbookPart? OpenWorkbook(Stream workbookData)
    {
        document = SpreadsheetDocument.Open(workbookData, true, new OpenSettings { AutoSave = true });
        [...]
    }

    ...
    public static void SetCellValue(...);
    {
        // Do some stuff with OpenXML to update the Excel document
        // It should also update the stream linked by SpreadsheetDocument.Open
    }
}

But it's not working. I mean in the class OpenXMLHelper, everythink seems to go right but the stream parameterFile in program.cs which is passed as argument to OpenXMLHelper remains not updated !

If I change the use of my OpenXMLHelper class like this, it works very well:

// Read the data into a stream
using (stream parameterFile = DocuAPIs.GetAttachment()
{
    // Open the Excelfile with OpenXML directly in program.cs
    using (SpreadsheetDocument document = SpreadsheetDocument.Open(parameterFile, true, new OpenSettings { AutoSave = true }))
    {
        // Pass the document to the helper
        OpenXMLHelper.SetWorkbook(document);
        // Let the helper update the document
        OpenXMLHelper.SetCellValue(1,1,"TESTRUS V2", "DONNEES");

        document.Save();
    }

    DocuAPIs.UpdateAttachment(parameterFile);
}

where my static class just store locally the document like this :

   public static class Excel
   {
       private static SpreadsheetDocument? document;
       ...

       public static WorkbookPart? SetWorkbook(SpreadsheetDocument doc)
       {
            document = doc;
            [...]
        }

Probably due to the enclosing using document in program.cs. Can it be that the stream passed as argument do not remains accessible ? Can somebody find a solution to fix this ? Is there a better design to achieve my goal ?

Thankx a lot !

0

There are 0 best solutions below