Perl 5.18.2 on Ubuntu 14.04. See attached spreadsheet. Cells in column G have formulas. Spreadsheet::ParseXLSX reads the formulas in G1-G5 but not in G6. The formula in cell G6 is E6*F6 so I don't think it is a shared formula.
All I'd like is to copy all data, formulas and formatting from one spreadsheet file to another tab on another file. If there was a method to copy get all data, like $all=$cellin->{All}, that would be great.
This is the routine I use to get the value of a cell. If a formula exists in the cell, the formula is returned near the bottom of the subroutine.
###########################################################################
# In: Workbook object;
# Sheet object;
# Row number to read (0-based);
# col number (0-based);
# XLSX filename;
# $tab=tab name;
# $zpos=row being read in spreadsheet, 1-based, displayed for errors.
# Out: String of cell contents.
# For Spreadsheet::ParseXLSX only.
# 5/31/2018 If formula exists return it prepended with '='.
sub getcellxlsx
{my($wkbkin,$sheetin,$row,$col,$xlsfnin,$tab,$zpos)=@_;
my($s,$t);
my $procname=(caller(0))[3];
my $dbg='';
my $prefixsp=' ';
my $new='';
my $cellin;
# if ( ($xlsfnin=~m/ismproject/i)) # DEBUG
# {
# $t="$procname: $col,$zpos; Reading cell ".excelntol($col+1).$zpos;
# $t.=" from $xlsfnin ";
# debugprg($t,' ');
# }
eval {
$cellin=$sheetin->get_cell($row,$col);
};
if ($@)
{
$s="$procname ERROR cell ".excelntol($col+1).$zpos.
" from XLSX sheetin->get_cell. ";
# if (defined($wkbkin->error))
# {
# $s.="wkbkin error: ".$wkbkin->error;
# }
if ( defined($@) and (length($@)>0) )
{
$s.="\n\ $@ error: ".$@;
}
writeerr($s);
return '';
}
if ($cellin)
{
if ($cellin eq 'EOR')
{
$new='';
#last;
}
else {
eval {
$new=$cellin->value(); # Returns some type of string or number.
};
if ($@)
{
$s="$procname ERROR from XLSX cellin->value() ".
excelntol($col+1).$zpos.'.';
}
if (not defined($new))
{
$s=$prefixsp."$procname ERROR row $zpos $xlsfnin: ".
"cell not defined, ".excelntol($col+1).$zpos;
#chr(65+$col).$row;
writeerr($s);
}
elsif (len($new)==0)
{
$new=' '; # Put in space so it takes up a position in array.
}
} # else
}
else {
$s=$prefixsp."$procname ERROR ".excelntol($col+1).$zpos.", $xlsfnin: ".
", cell->get_cell returned blank. ";
}
$dbg="$procname dbg1: ".excelntol($col+1).$zpos.", val='$new'";
print "$dbg\n" if ($^P>0);
if ((defined $cellin->{Formula}) and (length($cellin->{Formula})>0))
{
$t=$cellin->{Formula};
$new='\formula:='.$t; # Prepend = sign to show it's a formula.
#print "$procname: Formula $row,$col is: $t\n";
#sleep 2;
}
$dbg="$procname dbg2: ".excelntol($col+1).$zpos.", val='$new'";
print "$dbg\n\n" if ($^P>0);
return trim($new); # getcellxlsx
}
Perl 5.18.2 on Ubuntu 14.04. See attached spreadsheet. Cells in column G have formulas. Spreadsheet::ParseXLSX reads the formulas in G1-G5 but not in G6. The formula in cell G6 is
E6*F6
so I don't think it is a shared formula.All I'd like is to copy all data, formulas and formatting from one spreadsheet file to another tab on another file. If there was a method to copy get all data, like $all=$cellin->{All}, that would be great.
This is the routine I use to get the value of a cell. If a formula exists in the cell, the formula is returned near the bottom of the subroutine.
BugReportG6.xlsx