none
recalculate cells in a worksheet created with openxml2 RRS feed

  • Question

  • Hi,

    i'm relatively new to openxml2 technology, but i'm able to write data into cells. actually i have a template xlsx file, with headers, formulas in the cells, conditional formatting, etc; so i just need to enter my data into the relevant cells, and that's it.

    i don't have any issue with writing data into the cells, but the formulas are not recalculating. i'm able to recalculate the cells, if i go there press F2 to edit the cell, and press Enter to acknowledge. then all other referenced cells are re-formatted, updated.

    how can i perform the same with openxml?

    i've read the Problems with Open XML 2.0 SDK and Formulas thread, there excel services, and sharepoint is suggested, but this is not an option for me.

    any help is appreciated!

    thanks in advance!


    Ivan
    Friday, July 1, 2011 11:09 AM

Answers

  • Let me clarify, if you create a file with OpenXML and you set the force recalculate flag, when you open the file in Excel the program will (as expected) recalculate all formulas. In other words Excel is updating/modifying the file loaded in memory. Since it has a workbook in memory that isn't equal to what's on disk (as far as Excel is concerned), it asks the users if they want to save the changes.


    ClosedXML - Create Excel files in .Net
    • Marked as answer by Ivan Horvath Monday, July 11, 2011 10:53 AM
    Tuesday, July 5, 2011 5:08 PM

All replies

  • The calculation mode is probably set to Manual...

     

    Workbook workbook1 = workbookPart1.Workbook;
    
          CalculationProperties calculationProperties1 = workbook1.GetFirstChild<CalculationProperties>();
          calculationProperties1.CalculationMode = CalculateModeValues.Auto;
    



    ClosedXML - Create Excel files in .Net
    Sunday, July 3, 2011 5:53 AM
  • thanks for your reply

    actually it seems your code works, but i found another solution

        _SpreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
        _SpreadSheet.WorkbookPart.Workbook.CalculationProperties.CalculationOnSave = true;
    
    


    however the issue is that with both codes when i want to close the workbook it asks if i want to save it. of course i didn't modify anything just watch the data on every sheet.

    any idea?

     


    Ivan
    Monday, July 4, 2011 4:07 PM
  • The problem is that when you force a recalculate, Excel is effectively updating/changing the file so it only makes sense for it to ask the use if they want to save the changes.

    As far as I know you can't get around this one.


    ClosedXML - Create Excel files in .Net
    Monday, July 4, 2011 4:51 PM
  • my problem is that i save the file during the generation process. so, why excel doesn't do all the updates/changes then.

    anyway, it is really strange. every time when i open the file, and just close (without any modification) excel wants to save it. the size of the file is changing always.

    and i cannot open the file with Open XML SDK 2.0 Productivity Tool, it gives me some error message about vmldrawing7.vml...

     

     


    Ivan
    Tuesday, July 5, 2011 9:52 AM
  • Let me clarify, if you create a file with OpenXML and you set the force recalculate flag, when you open the file in Excel the program will (as expected) recalculate all formulas. In other words Excel is updating/modifying the file loaded in memory. Since it has a workbook in memory that isn't equal to what's on disk (as far as Excel is concerned), it asks the users if they want to save the changes.


    ClosedXML - Create Excel files in .Net
    • Marked as answer by Ivan Horvath Monday, July 11, 2011 10:53 AM
    Tuesday, July 5, 2011 5:08 PM
  • Hi Ivan,

    What is the status of the problem on your side? Do the suggestions help you? If you still show any concern on the post, just feel free to follow up.

    Best Regards,


    Bruce Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, July 11, 2011 10:46 AM
  • I'm not concerned about the need to respond to the Save Changes dialog.

    I am concerned that my VLOOKUP function won't work at all, unless I manually F2 Edit the cell and press Enter (no other manual change to the cell).

    I've added:

                c.CellFormula.CalculateCell = true;

                CalculationProperties prop1 = workbookPart1.Workbook.GetFirstChild<CalculationProperties>();
                prop1.ForceFullCalculation = true;
                prop1.FullCalculationOnLoad = true;
                prop1.CalculationMode = CalculateModeValues.Auto;
                prop1.CalculationCompleted = false;

    None of these changes make the cell formula work when the user first opens the file in Excel.

    For the sake of completeness, here is the formula I am attempting to use:

    =IFERROR( IF(VLOOKUP(CustomerNameRange, CustomerTable, 8, FALSE) = 0, \"\", VLOOKUP(CustomerNameRange, CustomerTable, 8, FALSE)),\"\"

    Can anybody help me with this?


    James Hippolite MCPD (Web), MCITP (DB)


    • Edited by JamesHip Friday, September 27, 2013 3:40 AM
    Friday, September 27, 2013 3:39 AM
  • Hi

    I am into same issues, do you have a resolution for this ?

    Thanks.

    Manoj.


    Thanks, Manoj

    Friday, January 5, 2018 10:21 AM