I successfully parsed an xls
file using Spreadsheet::ParseExcel::SaveParser
and modified it with Spreadsheet::WriteExcel
.
我使用Spreadsheet :: ParseExcel :: SaveParser成功解析了一个xls文件,并使用Spreadsheet :: WriteExcel对其进行了修改。
However working with xlsx
file is a whole different thing. I am trying to figure out how to work with Spreadsheet::XLSX
for parsing and how to make it work with Excel::Writer::XLSX
. Spreadsheet::ParseExcel::SaveParser
has a SaveAs()
method that makes it possible to apply Spreadsheet::WriteExcel
methods on the parsed xml
file, but I don't understand how to make it work with xlsx file
但是使用xlsx文件是完全不同的事情。我试图弄清楚如何使用Spreadsheet :: XLSX进行解析以及如何使其与Excel :: Writer :: XLSX一起使用。 Spreadsheet :: ParseExcel :: SaveParser有一个SaveAs()方法,可以在解析的xml文件上应用Spreadsheet :: WriteExcel方法,但我不明白如何使它与xlsx文件一起使用
edit: when using Spreadsheet::ParseExcel::SaveParser
and Spreadsheet::WriteExcel
I can write:
编辑:当使用Spreadsheet :: ParseExcel :: SaveParser和Spreadsheet :: WriteExcel时,我可以写:
#!/usr/bin/perl -w
use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::ParseExcel::SaveParser;
# Open the template with SaveParser
my $parser = new Spreadsheet::ParseExcel::SaveParser;
my $template = $parser->Parse('template.xls');
# Rewrite the file or save as a new file
$workbook = $template->SaveAs('new.xls');
# Use Spreadsheet::WriteExcel methods
my $worksheet = $workbook->sheets(0);
$worksheet->write($row+2, $col, "World2");
$workbook->close();
I would like to do the same with xlsx
files. therefore I'm trying to use Spreadsheet::XLSX
and Excel::Writer::XLSX
. Instead of
我想对xlsx文件做同样的事情。因此我正在尝试使用Spreadsheet :: XLSX和Excel :: Writer :: XLSX。代替
my $parser = new Spreadsheet::ParseExcel::SaveParser;
my $template = $parser->Parse('template.xls');
I use
我用
my $excel = Spreadsheet::XLSX -> new ('test.xlsx');
Now, after parsing the xlsx
file I would like to add some data to it and I don't know how to do it. As you can see above when using Spreadsheet::ParseExcel::SaveParser
I used SaveAs()
function, but Spreadsheet::XLSX
dosn't have a SaveAs()
method. So how do I add data to parsed xlsx
file?
现在,在解析xlsx文件后,我想向它添加一些数据,我不知道该怎么做。正如您在使用Spreadsheet :: ParseExcel :: SaveParser时所看到的,我使用了SaveAs()函数,但Spreadsheet :: XLSX没有SaveAs()方法。那么如何将数据添加到解析的xlsx文件中?
I could not find an answer to my question in this link.
我在这个链接中找不到我的问题的答案。
Thanks you for your help :)
谢谢你的帮助:)
3 个解决方案
#1
2
The Spreadsheet::XLSX module is a Parser, i.e. it is not supposed to write files, only to read and understand them.
Spreadsheet :: XLSX模块是一个Parser,即它不应该写文件,只是为了阅读和理解它们。
To write the file back to disk, I suggest you the Excellent (no pun intended :) ) Excel::Writer::XLSX by John McNamara.
要将文件写回磁盘,我建议您使用John McNamara的优秀(没有双关语:) :) Excel :: Writer :: XLSX。
http://search.cpan.org/~jmcnamara/Excel-Writer-XLSX/lib/Excel/Writer/XLSX.pm
http://search.cpan.org/~jmcnamara/Excel-Writer-XLSX/lib/Excel/Writer/XLSX.pm
Basically you can slurp (via Spreadsheet::XLSX) the XLSX file into a multilevel hash reference structured like this:
基本上你可以将XLSX文件(通过Spreadsheet :: XLSX)粘贴到如下结构的多级哈希引用中:
$xlsx_as_read->{worksheet_name}->{column}->{row}=value;
Modifying the example on the CPAN link you posted:
修改您发布的CPAN链接上的示例:
use Text::Iconv;
my $converter = Text::Iconv -> new ("utf-8", "windows-1251");
# Text::Iconv is not really required.
# This can be any object with the convert method. Or nothing.
use Spreadsheet::XLSX;
my $xlsx_as_read;
my $excel = Spreadsheet::XLSX -> new ('test.xlsx', $converter);
foreach my $sheet (@{$excel -> {Worksheet}}) {
printf("Sheet: %s\n", $sheet->{Name});
$sheet -> {MaxRow} ||= $sheet -> {MinRow};
foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {
$sheet -> {MaxCol} ||= $sheet -> {MinCol};
foreach my $col ($sheet -> {MinCol} .. $sheet -> {MaxCol}) {
my $cell = $sheet -> {Cells} [$row] [$col];
if ($cell) {
$xlsx_as_read->{$sheet->{Name}}->{$col}->{$row}=$cell -> {Val};
}
}
}
}
and then pour it back to an Excel::Writer::XLSX workbook, which can be written to disk.
然后将其倒回到Excel :: Writer :: XLSX工作簿,该工作簿可以写入磁盘。
my $new_workbook = Excel::Writer::XLSX->new( 'output_file_name.xlsx' );
#populate cells with a loop similar to the one before,
#iterating on $xlsx_as_read
#2
0
Try this:-
尝试这个:-
use Spreadsheet::XLSX;
my $excel = Spreadsheet::XLSX -> new ('test.xlsx', $converter);
使用Spreadsheet :: XLSX;我的$ excel = Spreadsheet :: XLSX - > new('test.xlsx',$ converter);
#3
0
my $excel_xlsx = Spreadsheet::XLSX -> new ('input_x.xlsx');
my ($sheet_xlsx, $row, $col, $cell);
Please write the content in the xls file immediately:
my $excel_xls = Spreadsheet::WriteExcel->new('input.xls');
my $sheet_xls = $excel_xls->add_worksheet();
Fetch the content:
for $sheet_xlsx ( @{ $excel_xlsx->{Worksheet} } )
{
for $row ( $sheet_xlsx->{MinRow} .. $sheet_xlsx->{MaxRow} )
{
for $col ( $sheet_xlsx->{MinCol} .. $sheet_xlsx->{MaxCol} )
{
my $cell = $sheet_xlsx->{Cells}[$row][$col];
if($cell->{Val})
{
}
Write here in the xls:
$sheet_xls->write($row, $col, $cell->{Val});
}
}
}
#1
2
The Spreadsheet::XLSX module is a Parser, i.e. it is not supposed to write files, only to read and understand them.
Spreadsheet :: XLSX模块是一个Parser,即它不应该写文件,只是为了阅读和理解它们。
To write the file back to disk, I suggest you the Excellent (no pun intended :) ) Excel::Writer::XLSX by John McNamara.
要将文件写回磁盘,我建议您使用John McNamara的优秀(没有双关语:) :) Excel :: Writer :: XLSX。
http://search.cpan.org/~jmcnamara/Excel-Writer-XLSX/lib/Excel/Writer/XLSX.pm
http://search.cpan.org/~jmcnamara/Excel-Writer-XLSX/lib/Excel/Writer/XLSX.pm
Basically you can slurp (via Spreadsheet::XLSX) the XLSX file into a multilevel hash reference structured like this:
基本上你可以将XLSX文件(通过Spreadsheet :: XLSX)粘贴到如下结构的多级哈希引用中:
$xlsx_as_read->{worksheet_name}->{column}->{row}=value;
Modifying the example on the CPAN link you posted:
修改您发布的CPAN链接上的示例:
use Text::Iconv;
my $converter = Text::Iconv -> new ("utf-8", "windows-1251");
# Text::Iconv is not really required.
# This can be any object with the convert method. Or nothing.
use Spreadsheet::XLSX;
my $xlsx_as_read;
my $excel = Spreadsheet::XLSX -> new ('test.xlsx', $converter);
foreach my $sheet (@{$excel -> {Worksheet}}) {
printf("Sheet: %s\n", $sheet->{Name});
$sheet -> {MaxRow} ||= $sheet -> {MinRow};
foreach my $row ($sheet -> {MinRow} .. $sheet -> {MaxRow}) {
$sheet -> {MaxCol} ||= $sheet -> {MinCol};
foreach my $col ($sheet -> {MinCol} .. $sheet -> {MaxCol}) {
my $cell = $sheet -> {Cells} [$row] [$col];
if ($cell) {
$xlsx_as_read->{$sheet->{Name}}->{$col}->{$row}=$cell -> {Val};
}
}
}
}
and then pour it back to an Excel::Writer::XLSX workbook, which can be written to disk.
然后将其倒回到Excel :: Writer :: XLSX工作簿,该工作簿可以写入磁盘。
my $new_workbook = Excel::Writer::XLSX->new( 'output_file_name.xlsx' );
#populate cells with a loop similar to the one before,
#iterating on $xlsx_as_read
#2
0
Try this:-
尝试这个:-
use Spreadsheet::XLSX;
my $excel = Spreadsheet::XLSX -> new ('test.xlsx', $converter);
使用Spreadsheet :: XLSX;我的$ excel = Spreadsheet :: XLSX - > new('test.xlsx',$ converter);
#3
0
my $excel_xlsx = Spreadsheet::XLSX -> new ('input_x.xlsx');
my ($sheet_xlsx, $row, $col, $cell);
Please write the content in the xls file immediately:
my $excel_xls = Spreadsheet::WriteExcel->new('input.xls');
my $sheet_xls = $excel_xls->add_worksheet();
Fetch the content:
for $sheet_xlsx ( @{ $excel_xlsx->{Worksheet} } )
{
for $row ( $sheet_xlsx->{MinRow} .. $sheet_xlsx->{MaxRow} )
{
for $col ( $sheet_xlsx->{MinCol} .. $sheet_xlsx->{MaxCol} )
{
my $cell = $sheet_xlsx->{Cells}[$row][$col];
if($cell->{Val})
{
}
Write here in the xls:
$sheet_xls->write($row, $col, $cell->{Val});
}
}
}