c1Excel ... Calculate with a Value from a Formula cell

c1Excel ... Calculate with a Value from a Formula cell

Posted by: andreas.kren on 5 June 2023, 11:19 am EST

• 

  andreas.kren

  • Post Options:
  • Link
    Copy

  Posted 5 June 2023, 11:19 am EST

  Hi,

  I am not able to open a xlsx with C1Excel and to get the actual “Value” of a cell which has a Formula…

  I have to calculate with

     mySheet(2, 2).???
  

  Please helpexceltest.zip

• 

  andreas.kren

  • Post Options:
  • Link
    Copy

  Posted 5 June 2023, 12:57 pm EST

  When i remote control Excel

  open the workbook and save it (via the excel.application object) it works and after a reopen with c1excel the cell values are filled…

• 

  aastha.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 6 June 2023, 2:38 pm EST

  Hi Andreas,

  > I am not able to open a xlsx with C1Excel

  You can load a .xlsx file in C1Excel by using C1XLBook’s Load method. You can refer to the following code for the same:

  c1XLBook1.Load("../../exceltest.xlsx");

  > to get the actual “Value” of a cell which has a Formula

  You cannot obtain the value of the cell via XLCell object until a value is assigned to it. If the cell has a formula assigned, the calculated value is not stored in the Value property of the XLCell object. Only the Formula property gets set as per the formula assigned to the cell. When the sheet gets exported to .xlsx file the value of the cell gets calculated according to the assigned formula.

  Kindly refer to the attached sample. (ExcelFormulaValue.zip)

  >When i remote control Excel

  open the workbook and save it (via the excel.application object) it works and after a reopen with c1excel the cell values are filled…

  We are sorry but we request you explain your requirement in a bit more detail so that we can provide you with a more specific solution as per your use case. By having some information regarding the implementation steps we could assist you better in this issue.

  Thanks & Regards,
  
  Aastha

• 

  andreas.kren

  • Post Options:
  • Link
    Copy

  Posted 7 June 2023, 10:43 am EST - Updated 7 June 2023, 10:48 am EST

  and this (see screenshot) is the result of your attached sln running on my machine

  the sheet[2,2].text is just empty

  

  1381×918 188 KB

• 

  aastha.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 8 June 2023, 10:50 am EST

  Hi Andreas,

  The sheet[2,2].text and sheet[2,2].Value both will be empty as there is no Value assigned to this XLCell. The value is calculated in the Excel sheet by fetching the formula via the ‘Formula’ property of this XLCell.

  Here I am quoting it again. Please check.

  " >to get the actual “Value” of a cell which has a Formula

  You cannot obtain the value of the cell via XLCell object until a value is assigned to it. If the cell has a formula assigned, the calculated value is not stored in the Value property of the XLCell object. Only the Formula property gets set as per the formula assigned to the cell."

  ‘Text’ property returns simple text representation of the current cell value. However, since the cell with a formula doesn’t have a value assigned to it directly, the text of that cell would also be empty.

  Please let me know if you need any further help.

  Thanks & Regards,
  
  Aastha

• 

  andreas.kren

  • Post Options:
  • Link
    Copy

  Posted 8 June 2023, 12:38 pm EST

  ok

  so the following questions

  1. why did you set
     
     ‘keepformula’ false? what for?

  2. when I open the xls with excel ( manually or via an interop excel.appliction object) and save the xls , excel fills the values. when i then ‘reopen’ the xls via c1excel i see a value AND the formula …

  you wrote

  “When the sheet gets exported to .xlsx file the value of the cell gets calculated according to the assigned formula.”

  how do I “export” it? please show this in an example

  because

  3. One programm creates the xls(x) , another programm reads this xls and needs the values of the cells with a formula for further calculation

  Thank you.

• 

  aastha.gupta

  

  • Post Options:
  • Link
    Copy

  Posted 9 June 2023, 9:22 am EST

  Hi Andreas,

  1. We were conducting tests on our side to explore potential solutions for your requirement, which led us to include that particular line of code. However, we inadvertently neglected to remove it from the code before sharing it with you.

  JFYI, setting C1XLBook’s “KeepFormula” property to “false”, the formulas are removed from the excel book gets removed when it is loaded.

  In the provided sample file (C1ExcelFormulaExport.zip), we deliberately set the “KeepFormula” property of C1XLBook to “false” prior to loading the file. This was done to demonstrate the functionality of this property. As a result, the formulas from the “exceltest.xlsx” file are not imported. If you examine the exported file (ExportedFile.xlsx), you will observe that the cells containing formulas are empty.

  2. You can export the C1XLBook using its Save method. We have implemented the requirement in the attached sample for your reference. Please check C1ExcelFormulaExport.zip

  3. We are sorry for the inconvenience caused to you but you cannot fetch the value of the formula cells in XLSheet. This behavior is a limitation of C1Excel.

  We would suggest you using our different product line GCExcel if you want to achieve the required behavior.

  Here are the links for your reference,

  Documentation: https://www.grapecity.com/documents-api-excel/docs/online/overview.html

  Demos: https://www.grapecity.com/documents-api-excel/demos/

  Kindly refer to the attached sample for implementation of desired behavior in GCExcel. Please check GcExcel_Formula_Cell_Value_Demo.zip

  Kindly let us know if you need any further help regarding this.

  Thanks & Regards,
  
  Aastha