I am writing a formula to an xlsx file using Excel::Writer::XLSX
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( "test.xlsx" );
my $worksheet = $workbook->add_worksheet();
$worksheet->write( 'A1', "4");
$worksheet->write( 'A2', "=ERF.PRECISE(A1/SQRT(2))");
But when I open my Excel sheet, I can see in the cell
A2:
=@ERF.PRECISE(A1/SQRT(2))
Where does the @ come from?
Instead of -
$worksheet->write( 'A2', "=ERF.PRECISE(A1/SQRT(2))");
Use below method:
$worksheet->write( 'A2', "=_xlfn.ERF.PRECISE(A1/SQRT(2))");
Look into the documentation for formulas for Excel 2010 and later.
Complete Script:
#!/usr/bin/perl
use strict;
use warnings;
use Excel::Writer::XLSX;
my $workbook = Excel::Writer::XLSX->new( "test.xlsx" );
my $worksheet = $workbook->add_worksheet();
$worksheet->write( 'A1', "4");
$worksheet->write( 'A2', "=_xlfn.ERF.PRECISE(A1/SQRT(2))");
$workbook->close();
Output:
