search

tfussell / xlnt

:bar_chart: Cross-platform user-friendly xlsx library for C++11+
Other
1.5k stars 423 forks source link

Writing formula to cell gets #VALUE in the file. #434

Open GVI02 opened 4 years ago

GVI02 commented 4 years ago

When I write a formula to a excel document it says #VALUE even though it is correct. Manually going in the file and clicking on the function field for the cell fixes it but it's annoying if you have about 100 of these cells.

tfussell commented 4 years ago

Could you provide the formula here?

GVI02 commented 4 years ago

It's just a generic sum operation =A1+B1. I started working with xlnt a couple of days ago. I tried copying a formula from a cell: ws.cell("C2").formula("=" + ws.cell("C1").formula());

The formula in C1 returns as A1+B1 so i add the = in the beggining. When I open the xlsx file it shows #VALUE in cell C1 but theres no actual error, because when I click on the formula field at the top in fixes it.

tfussell commented 4 years ago

That's pretty straightforward. I'll check it out in the next few days.

tfussell commented 4 years ago

I just tried to reproduce this with the following code:

xlnt::workbook wb1;
xlnt::worksheet ws1 = wb1.active_sheet();
ws1.cell("A1").value(1);
ws1.cell("A2").value(2);
ws1.cell("C1").formula("=A1+A2");
ws1.cell("C2").formula("=" + ws1.cell("C1").formula());
wb1.save("wb1.xlsx");
xlnt::workbook wb2;
wb2.load("wb1.xlsx");
xlnt::worksheet ws2 = wb2.active_sheet();
ws2.cell("C2").formula("=" + ws2.cell("C1").formula());
wb2.save("wb2.xlsx");

Both files loaded fine in Excel 2016 for Mac including the formulae. Could you give me some more information?