Reading the value of a cell with formula

[fabianL51]

Hi,

I'm using xlnt for personal projects and I find the library very cool. Currently, I'm faced with the issue of reading the cell's value with a formula.

For example the J3 cell has a formula =H3 / 100 * N2. The cell displays the value of 718.75 in Spreadsheet. My current problem is how to read the value of the cell instead of its formula. I tried to read it as a double or as a string, but it failed to recognize the value of the cell. Does anybody know any elegant solution to this problem? My rather inelegant solution would be utilizing the calculation in the C++ scripts instead of using formulas in Spreadsheet and storing all the calculated values in Spreadsheet cells, so that these values can be easily read for other purposes.

[musshorn]

Are you able to share a sample sheet?

#include <iostream>
#include <xlnt/xlnt.hpp>

int main()
{
    xlnt::workbook wb;
    wb.load("test.xlsx");
    auto ws = wb.active_sheet();
    for (const auto& row : ws.rows())
        for (const auto& cell : row)
        {
            if (cell.has_formula())
                std::cout << "Formula: " << cell.formula() << " ";
            std::cout << "Value: " << cell.to_string() << std::endl;
        }
    return 0;
}

With a spreadsheet containing A1 = 1, A2 = 2, A3 = A1 + A2 this returns

Value: 1
Value: 2
Formula: A1+A2 Value: 3

[fabianL51]

I had decided against xlnt and deleted my old codes. Thanks for your answer though!

[izabala123]

Hello @musshorn

I have tried your example, first creating and saving the xlsx, and later reading the xlsx, but to_string() returns an empty string.

However, if I create the file with Xlint, open it in Excel and save it by hand, and open it with Xlint, then the values in the formulas can be read.

This is a small drawback, but I think it is unsolvable by any library... unless it is based on Excel or Open/LibreOffice themselves, because the only way to get the values is to implement a formula interpreter.

Best regards Iñaki

PD. Post edited.