locked
Excel doesn't save the same value of the formula in its xml definition RRS feed

  • Question

  • Hi

    I have the formula:=+N136 in a cell which result is: 3567.64. The sheet is: FC_FINANCIERA

    sss

    The problem is that when I use dlls as npoi and openxml to read in c#, they read another value 3528.2189980539647 which is saved in the sheet6 : 3528.218998053964

    The file is: https://drive.google.com/open?id=1JHdEnNengIx-TCie9ZCjuu6DFmXanKjm

    I don't understand why the view and the definition of the excel are differents.

     

    Thursday, June 14, 2018 3:20 AM

Answers

  • Hi

    I tried to open, add a sheet and close the file with xlsm extension using NPOI and saw that the file was

    corrupted.

    http://forums.devshed.com/net-development-87/update-excel-via-npoi-corrupts-file-751698.html.

    This link show my same error.

    The definition of the file wasn't changed with openxml.

    Finally, I opened, add a sheet and close the file with interop.

    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
             Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\neoag_000\Desktop\ColMobileFASE3PARTE3\(22.05.18)-PER-001723377-03560602 - Buena Ventura Disco NLSAS.xlsm");
             var xlSheets = xlWorkbook.Sheets as Microsoft.Office.Interop.Excel.Sheets;
             var xlNewSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
             xlNewSheet.Name = "newsheet";
     
     
             xlWorkbook.Save();
             xlWorkbook.Close();

    The formulas were saved fine after that.

    • Marked as answer by neonash Wednesday, August 15, 2018 4:29 AM
    Wednesday, August 15, 2018 4:29 AM

All replies

  • Hi neonash,

    Thanks for visiting our forum. Then here we mainly focus on general issues about Excel desktop client and my knowledge about NPOI and openxml is limited, I am afraid little we can help with this issue. Thanks for your understanding. 

    Regards,

    Yuki Sun


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.

    Thursday, June 14, 2018 8:12 AM
  • Hi

    I can see that the problem is the excel (https://drive.google.com/open?id=1JHdEnNengIx-TCie9ZCjuu6DFmXanKjm) since the real value which is in the zip definition is different that the value that excel show.

    The excel shows: 3567.24 and the definition shows: 3528.22. I only open and save the file and the value and definition are the same.

    I am going to explain in other words:

    If I open the file with excel, I see: 3567.24 which is a value of the formula.

    If I see the xml definition of the sheet I see: 3528.22

    After that I open the excel, save the file, close it and see 3567.24 with excel and in its xml file.

    The file definition is:

    <c r="N136" s="911">
    <f ca="1">NPV(N131,M127:DD127)-L127</f>
    <v>3528.2189980539647</v>
    </c>

    • Edited by neonash Saturday, June 16, 2018 4:27 AM
    Thursday, June 14, 2018 3:19 PM
  • Hi

    I tried to open, add a sheet and close the file with xlsm extension using NPOI and saw that the file was

    corrupted.

    http://forums.devshed.com/net-development-87/update-excel-via-npoi-corrupts-file-751698.html.

    This link show my same error.

    The definition of the file wasn't changed with openxml.

    Finally, I opened, add a sheet and close the file with interop.

    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
             Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(@"C:\Users\neoag_000\Desktop\ColMobileFASE3PARTE3\(22.05.18)-PER-001723377-03560602 - Buena Ventura Disco NLSAS.xlsm");
             var xlSheets = xlWorkbook.Sheets as Microsoft.Office.Interop.Excel.Sheets;
             var xlNewSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
             xlNewSheet.Name = "newsheet";
     
     
             xlWorkbook.Save();
             xlWorkbook.Close();

    The formulas were saved fine after that.

    • Marked as answer by neonash Wednesday, August 15, 2018 4:29 AM
    Wednesday, August 15, 2018 4:29 AM