Perl script to convert xlsx to xls file

Shell Programming and Scripting
1

Hi

I am trying one perl script to convert xlsx to xls file but could not able to get all the rows and columns in the xls file . This scriptFILE is basically to convert XLSX to CSV .. I am tweaking the script to convert XLSX to XLS file also

#######################FILE #########################

#!/usr/bin/perl

use strict;

use warnings;
use Spreadsheet::XLSX;
#use Spreadsheet::ParseExcel::Format
use Spreadsheet::WriteExcel;

    # Create a new Excel workbook
my $workbook = Spreadsheet::WriteExcel->new('perl.xls');


#use Spreadsheet::ParseExcel;

#my $sourcename = shift @ARGV or die "invocation: $0 <source file>\n";
#my $source_excel = new Spreadsheet::XLSX;
my $source_book = Spreadsheet::XLSX -> new ("area_Status.xlsx");
my $worksheet = $workbook->add_worksheet();
#my $source_book = $source_excel->Parse($sourcename) or die "Could not open source Excel file $sourcename: $!";
#my $storage_book;

foreach my $source_sheet_number (0 .. $source_book->{SheetCount}-1)
{
 my $source_sheet = $source_book->{Worksheet}[$source_sheet_number];

 print "--------- SHEET:", $source_sheet->{Name}, "\n";

 next unless defined $source_sheet->{MaxRow};
 next unless $source_sheet->{MinRow} <= $source_sheet->{MaxRow};
 next unless defined $source_sheet->{MaxCol};
 next unless $source_sheet->{MinCol} <= $source_sheet->{MaxCol};

 foreach my $row_index ($source_sheet->{MinRow} .. $source_sheet->{MaxRow})
 {
  foreach my $col_index ($source_sheet->{MinCol} .. $source_sheet->{MaxCol})
  {
    #$worksheet->write(
 
    
    #my $col = 0;
    #   foreach my $token (@Fld) {
     
    #$worksheet->write($row_index, $col_index, $token);
    #       $col_index++;
    #   }
    #   $row++;
   my $source_cell = $source_sheet->{Cells}[$row_index][$col_index];
     $worksheet->write($row_index, $col_index);

   # $worksheet->write($source_cell);

   if ($source_cell)
   {

     #$worksheet->write($source_cell);
    #print "( $row_index , $col_index ) =>", $source_cell->Value, "\t";
    print  $source_cell->Value, ",";
    

   }
  } 
  print "\n";
 } 
}
print "done!\n";

I am adding

$workbook = Spreadsheet::WriteExcel->new('perl.xls');
 $worksheet->write($row_index, $col_index);

to the original script to convert XLSX to XLS file but not getting it

Please help me out to tackle this

Thanks
Kshitij

###########################################

2

Hello kshitij, I didn't read through your entire code. But here's an example.. This is what I do in here.. Read from xlsx file using Spreadsheet::XLSX module and write to xls using Spreadsheet::WriteExcel module.

#! /usr/bin/perl

use warnings;
use strict;

use Spreadsheet::XLSX;
use Spreadsheet::WriteExcel;

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

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

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];
            print "$cell->{Val} ";
            $sheet_xls->write($row, $col, $cell->{Val});
        }
        print "\n";
    }
}

Attached file has a sample.xlsx that will be converted to sample.xls

[user@host ~]$ ./test.pl
id name
1 tom
2 dick
3 harry
[user@host ~]$ ls -l sample.xls*
-rwxr-xr-x 1 user None 5632 May 31 11:49 sample.xls
-rwxr-xr-x 1 user None 7402 May 31 11:16 sample.xlsx
3

ok Thanks a lot !
will try it and let you know

4

This perl script is working fine if the XLSX which I need to convert into XLS is not having any formulas in the columns of the XLSX but wherever are the columns converted XLS file is putting 0 only on all those columns of XLSX wherever forumals are being used...

Please suggest some other solution to tackle this

---------- Post updated at 11:05 AM ---------- Previous update was at 10:41 AM ----------

I tried to use below code but still dont know how the values coming from Formulas in XLSX will come in XLS file

Not sure how to access formula values 

#! /usr/local/bin/perl

use warnings;
use strict;

#use Spreadsheet::XLSX;
use Spreadsheet::ParseXLSX;
use Spreadsheet::WriteExcel;
my $parser = Spreadsheet::ParseXLSX->new;
my $excel_xlsx = $parser->parse("area_Status.xlsx");
#my $excel_xlsx = Spreadsheet::ParseXLSX -> new ('area_Status.xlsx');
#my $excel_xlsx = Spreadsheet::XLSX -> new ('area_Status.xlsx');
my ($sheet_xlsx, $row, $col, $cell);

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

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];
            print "$cell->{Val} ";
            $sheet_xls->write($row, $col, $cell->{Val}, $cell->{Formula});
        }
        print "\n";
    }
}