Modifying an .xlsx file (Excel 2007 and newer)

Question

I successfully parsed an xls file using Spreadsheet::ParseExcel::SaveParser and modified it with 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

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. therefore I'm trying to use Spreadsheet::XLSX and Excel::Writer::XLSX. Instead of

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?

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

Thanks you for your help :)

Lyophilic

What do you mean: "how to make it work"? There is an example program here: search.cpan.org/~dmow/… What exactly doesn't work? We like concrete questions here with code samples. – Kanter

I've edited and added code example. I hope that my question is clearer now and somebody can help... Thanks :) – Lyophilic

Please note that Spreadsheet::WriteExcel is in "maintenance-only mode" as per the description section on CPAN, replacement is Excel:Writer::XLSX. – Gael

Answer 1

The Spreadsheet::XLSX module is a Parser, i.e. it is not supposed to write files, only to read and understand them.

To write the file back to disk, I suggest you the Excellent (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 multilevel hash reference structured like this:

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

Modifying the example on the 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 pour it back to an 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

Pul

Answer 2

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});
        }
    }
}

Eduard

Answer 3

There is now a module that helps you convert data read with Spreadsheet::ParseXLSX into an object that Excel::Writer::XLSX can write. It would be nice if there was one module that did all this, but there isn't as best as I can tell. So for now you must use three modules to get this done. See Excel::CloneXLSX::Format

Ichthyolite

Answer 4

Try this:-

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

Stupor