jsuresh has asked for the wisdom of the Perl Monks concerning the following question:

Hi Team, I'm trying to read an XLSX file and write it as XLSX file. I can read it but unable to print it as a new XLSX file. I have also used the required module XLSX::writer but still it is now allowing me to create a new xlsx file. I also need to to check for the values starting from 0.00 and replace it as '0.00'(just wanted to add a single quotes to it). please provide us a solution. It is not allowing me to proceed further.

Thanks,

use Spreadsheet::XLSX;
my $excel = Spreadsheet::XLSX -> new ('/tmp/temp.xlsx');
print $excel;
my @array;
my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' );
#my $FILENAME="/tmp/Newfile.xls";
my $workbook = Spreadsheet::WriteExcel->new("$FILENAME");
my $worksheet1=$workbook->addworksheet("Worksheet1");
$worksheet1->write( "A1", "Hi Excel!" );
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 -> {MaxC
+ol}) {

                       my $cell = $sheet -> {Cells} [$row] [$col];

                       if ($cell) {
                           printf("( %s , %s ) => %s\n", $row, $col, $
+cell -> {Val});
                           my $temp=$cell->{Val};
                           $worksheet1->write($row,$col,$cell -> {Val}
+);
#                          push(@array,$temp);
                          # $sheet->write($row,$col,"CHECK");
                       }

               }

       }
last

[download]

2019-03-30 Athanasius removed paragraph tags from code and added code tags

Replies are listed 'Best First'.
Re: Reading and printing an XLSX file
by Athanasius (Archbishop) on Mar 21, 2019 at 07:59 UTC

    Hello jsuresh,

    Please reformat your post by removing paragraph tags from code and enclosing code in <code> ... </code> tags.

    You should always have

    use strict;
use warnings;

    [download]

    at the head of your script. In this case, warnings would have told you that the lexical variable $workbook is declared twice. The second declaration:

    my $workbook = Spreadsheet::WriteExcel->new("$FILENAME");

    [download]

    overrides the first, so that module Excel::Writer::XLSX is never actually used.

    Hope that helps,

    Athanasius <°(((>< contra mundum Iustus alius egestas vitae, eros Piratica,

[reply]
[d/l]
[select]
Re: Reading and printing an XLSX file
by Tux (Canon) on Mar 21, 2019 at 09:02 UTC

    it being perl? Your boss?

    If you make errors like these it is likely not do do what you expect it to do.

    • I used code tags
    • I restyled the code for readability
    • I fixed all obvious errors
    • I commented on the changes with # <-
    • I replaced the unmaintained parser with the right one: this is the main reason I reply.

    HTH

    use strict;                          # <- always use strict
use warnings;                        # <- and warnings

use Spreadsheet::WriteExcel;         # <- missing declaration
#use Spreadsheet::XLSX;              # <- this module is *STRONGLY* di
+scouraged!
use Spreadsheet::ParseXLSX;          # <- use this one instead

#my $excel = Spreadsheet::XLSX->new ("/tmp/temp.xlsx"); # <- do not us
+e Spreadsheet::XLSX
my $excel = Spreadsheet::ParseXLSX->new->parse ("/tmp/temp.xlsx");

# print $excel; # <- you cannot simply print an object and hope it wri
+tes a file
# if you want to see what this is use Data::Peek or Data::Dumper

#my @array;                          # <- unused
#my $workbook = Excel::Writer::XLSX->new ("perl.xlsx");
# ^--- Your code uses ->addworksheet, which is Spreadsheet::WriteExcel
+ syntax and
#      not supported by Excel::Writer::XLSX, so you obviously do not w
+ant this line

my $FILENAME   = "/tmp/Newfile.xls"; # <- used but commented out
my $workbook   = Spreadsheet::WriteExcel->new ($FILENAME); # <-- no qu
+otes needed
#  ^--- variable used twice
my $worksheet1 = $workbook->addworksheet ("Worksheet1");
$worksheet1->write ("A1", "Hi Excel!");
foreach my $sheet (@{$excel->{Worksheet}}) {
    print "Sheet: $sheet->{Name}\n"; # <- not really a good example fo
+r printf
    $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] or next;
            printf "(%3d, %3d) => %s\n", $row, $col, $cell->{Val};
            my $temp = $cell->{Val};
            $worksheet1->write ($row, $col, $cell->{Val});
            }
        }
    last;                            # <- missing ;
    }                                # <- missing }

    [download]

    That code correctly created /tmp/Newfile.xls with one sheet named Worksheet1.


    Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]
      Hi,

      Thank you so much for the support.

      I downloaded "Spreadsheet::ParseXLSX" module and added to my library.

      When I use "Spreadsheet::ParseXLSX" module. It gives me error

      Can't locate Spreadsheet/ParseXLSX.pm in @INC (@INC contains: /opt/highdeal/mmahalingam/Spreadsheet/lib /opt/highdeal/lib/perl5 /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.8/i386-linux-thread-multi /usr/lib/perl5/5.8.8 .) at bma_descimal_xlsx.pl line 7. BEGIN failed--compilation aborted at bma_descimal_xlsx.pl line 7.

      Please provide support.

      -Jp

[reply]

        You are obviously using a rather old version of (system)perl: 5.8.8. You now have the following options (maybe even more, but to start with):

        1. Install the system package for Spreadsheet::ParseXLSX, on which I think the odds are very low that the system ever packaged that for your OS.
        2. Install using the toolset:
          • Using cpan
            $ sudo cpan Spreadsheet::ParseXLSX
          • Using cpanm:
            $ curl -L https://cpanmin.us | perl - --sudo App::cpanminus
$ cpanm -S https://cpan.metacpan.org/authors/id/D/DO/DOY/Spreadsheet-P
+arseXLSX-0.27.tar.gz

            [download]

        It is up to you to find if you want it installed outside of the default perl tree and what options the tools have to do so. The monestary is full of tips on how to use perl modules without the use of sudo.


        Enjoy, Have FUN! H.Merijn
[reply]
[d/l]
[select]

        "...added to my library."

        How did you install this?

[reply]

Back to Seekers of Perl Wisdom