1

According to their docs: GemBox.Spreadsheet can read and write formulas, but can not calculate formula results. When you open a XLS file in MS Excel, formula results will be calculated automaticaly.

So if i create a spreadsheet with some injected values and save it to disk if i open the file in excel i can see the calculated values..

if i open that file in gembox and look up the calculated cell's values i get a result of 0

the same functionality exists in flexcel as well.. can't retrieve the calculated value from a given cell.. it returns zero..

So what I'm doing is:

  1. Open an existing excel file
  2. Putting some numbers in to some cells
  3. Saving the file to disk under a different name (tried with the same file too)
  4. Opening the file again and looking in the total column which is a calculated cell

The value is always 0.. I open the file in excel.. the value is calculated as expected.

Mario Z
  • 4,328
  • 2
  • 24
  • 38
Tom
  • 73
  • 1
  • 8

2 Answers2

5

EDIT 28-09-2016:
We have released a new version of GemBox.Spreadsheet (version 4.1) in which we implemented a support for cell formula calculation, see the version history page.
Also, you can find the calculation example here.

ORIGINAL ANSWER
GemBox.Spreadsheet currently supports only values that are calculated by the Excel (so if file is saved by the MS Excel, we can read formula values). GemBox.Spreadsheet does not have its own calculation engine.

To read formula values in XLS format use ExcelFile.LoadXls overload that accepts XlsOptions enumeration and use XlsOptions.PreserveWorksheetRecords or XlsOptions.PreserveAll.

To read formula values in XLSX format use ExcelFile.LoadXlsx method with XlsxOptions enumeration values XlsxOptions.PreserveKeepOpen or XlsxOptions.PreserveMakeCopy.

GemBox Dev Team
  • 669
  • 5
  • 18
  • Hi JWiley, I will post an answer to your original question that you posted, but in short your problem is not with reading (the values are read), your problem occurs because the values that are read need to be recalculated and current version of GemBox.Spreadsheet (version 3.9) does not have a calculation engine so it's unable to recalculate the formulas. – GemBox Dev Team Mar 27 '15 at 11:23
1

What is your actual question?

Cells have two properties: values and formulas. What you're seeing is value beeing recalculated by Excel on the fly. Gembox, as you said, doesn't recalculate the values.

If you need formula evaluation, look for another library, for example Apache POI.

Rikard Pavelic
  • 476
  • 3
  • 5