2
#!/usr/bin/env perl
use warnings;
use strict;

use Excel::Writer::XLSX;

my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' );
my $worksheet = $workbook->add_worksheet();

$worksheet->write_formula( 'A1', '=SUM(1, 2, 3, 4)' );

$workbook->close();

Until now this worked fine with LibreOffice. But today this doesn't work any more ( the value in A1 is 0 ) - maybe due to some LibreOffice-updates (LibreOffice 3.5 Build-ID: 350m1(Build:402)).

Does the occasionally in the following part of the Excel::Writer::XLSX documentation refer to such kind of situations?

If required, it is also possible to specify the calculated value of the formula. This is occasionally necessary when working with non-Excel applications that don't calculate the value of the formula. The calculated $value is added at the end of the argument list

sid_com
  • 24,137
  • 26
  • 96
  • 187

2 Answers2

1

Excel::Writer::XLSX doesn't calculate the value of a formula that it writes. Instead it inserts 0 and allows the user to specify the value if required.

This isn't a great solution but it is the best that can be done since calculating arbitrary complex formulae is beyond the scope of the module.

And, in general Excel and other third party applications will recalculate formula values for display.

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
1

It's a setting in LibreOffice. Here is a solution (quote from here):

LibreOffice intentionally does not recalculate older spreadsheets, because as formulas are updated from version to version or between different spreadsheet programs, the results can be different. Go to Tools – Options – LibreOffice Calc, under 'Recalculation on file load', change the two drop-downs, 'Excel 2007 and newer' and 'ODF Spreadsheet (not saved by LibreOffice)', to 'Always recalculate'. Click Ok, close the spreadsheet and LibreOffice. Now open the file in LibreOffice and you should see that the formulas have recalculated.

Also go to Tools – Cell Contents and be sure that AutoCalculate is selected.

Gergely Máté
  • 107
  • 2
  • 11