Modifying a .xlsx file (Excel 2007 and later)

Question

I successfully parsed the xls file using Spreadsheet::ParseExcel::SaveParser and modified it using Spreadsheet::WriteExcel .

However, working with the xlsx file is a completely different matter. 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 allows you to apply the Spreadsheet::WriteExcel in the analyzed xml file, but I don’t understand how to make it work with the xlsx file

edit: when using Spreadsheet::ParseExcel::SaveParser and Spreadsheet::WriteExcel I can write:

 #!/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. so I am trying to use Spreadsheet::XLSX and Excel::Writer::XLSX . Instead

 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 the SaveAs() function but Spreadsheet::XLSX does not have a SaveAs() method. So, how can I add data to the analyzed xlsx file?

I could not find the answer to my question in this link .

Thank you for your help :)

Answer 1

Spreadsheet module: XLSX is an analyzer, i.e. it should not write files for reading and understanding only.

To write the file back to disk, I offer you a great (no pun intended) :) Excel :: Writer :: XLSX by John McNamara.

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 multi-level hash link, structured as follows:

 $xlsx_as_read->{worksheet_name}->{column}->{row}=value; 

Changing the example CPAN link you posted:

 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 return it to the Excel :: Writer :: XLSX workbook, which can be written to disk.

 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 

Answer 2

Try the following: -

use table: xlsx;
my $ excel = Spreadsheet :: XLSX → new ('test.xlsx', $ converter);

Answer 3

 my $excel_xlsx = Spreadsheet::XLSX -> new ('input_x.xlsx'); my ($sheet_xlsx, $row, $col, $cell); 

Write the contents in the xls file immediately:

 my $excel_xls = Spreadsheet::WriteExcel->new('input.xls'); my $sheet_xls = $excel_xls->add_worksheet(); 

Get contents:

 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 xls:

  $sheet_xls->write($row, $col, $cell->{Val}); } } }