Re: XLSX to CSV conversion

[Generoso]

Maybe this example will help you.

#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::XLSX;
 
my $excel = Spreadsheet::XLSX -> new ('d:\\book1.xlsx');
my $line;
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) {
                $line .= "\"".$cell -> {Val}."\",";
            }
        }
        chomp($line);
        print "$line\n";
        $line = '';
    }
}

[Anonymous Monk]

Thank you for this simple yet superbly helpful example.
Having finally identified and installed apparently all of the packages dependencies and then combined with this excellent example of yours, I think that it alone has saved me a significant amount of tinkering. This probably has me 50% of the way to the basic conversion task which I had pending.

If I may make an assumption from the nickname above :

1. Muito, muito obrigado. Voce nem pode imaginar a enorme ajuda que me acaba de dar aqui.
   or alternatively
2. "Muchas gracias" ?
   failing either of the above being applicable
3. Thanks a million :)

[Anonymous Monk]

I tried running the above script but it prints only the first line.

[Anonymous Monk]

Hi I just spotted something on your code: you are appending an extra comma after processing the last column of each line in the worksheet. This can lead to problems specially if you're using the generated csv file to load data to a database for example. I did the change below to make sure that did not happen. I hope it helps

#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::XLSX;
 
my $excel = Spreadsheet::XLSX -> new ('/tmp/myexcel.xlsx');
my $line;
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) {
                $line .= "\"".$cell -> {Val}."\",";
                $line .= $cell -> {Val};
                if ($col != $sheet -> {MaxCol}) #appends the comma onl
+y if the column being processed is not the last
                {
                    $line .= ",";
                }
            }
        }
        chomp($line);
        print "$line\n";
        $line = '';
    }
}

[Anonymous Monk]

Dear,
You correction is good, but is a bit incorrect.
Line below is redundant: $line .= "\"".$cell -> {Val}."\",";
The value is printed in next line.