CODEWITHSUNDEEP
xmlperlexcelxlsx
Man
Man

Reputation: 131

.xlsx to xml conversion in Perl

I am trying to convert a .xlsx file into .xml file. The first row(heading) of .xlsx file would become tags of xml file.

I have written below code which is working fine-

 open(XML, ">temp.csv") or die "not able to open $!";

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

 foreach my $sheet (@{$excel -> {Worksheet}}) {
         $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) {
                           print XML $cell -> {Val};
                        }
                        unless($col == $sheet -> {MaxCol}) {print XML ",";} 
                }
                unless( $row == $sheet -> {MaxRow}){print XML "\n";}
         }
  }
 close(XML);
 use XML::CSV; 
 my $csv_obj = XML::CSV->new(); 
    $csv_obj->parse_doc("temp.csv", {headings => 1});
    $csv_obj->print_xml("out.xml");

Can anyone suggest a better code(Module) as I have to handle the large .xlsx files.

Thanks in advance.

Man

Upvotes: 1

Views: 2911

Answers (3)

runrig
runrig

Reputation: 6524

There is a project on githup to develop a lightweight XLSX reader. It's currently in a sort of alpha state, but it does work. You can try it if you like.

Upvotes: 0

Joel Berger
Joel Berger

Reputation: 20280

If your code is working then why do you need something better? If its only for speed, you need to find a way to avoid writing the temporary .csv file. File IO is slow and you are writing and then rereading and reparsing. Can you not read the data into a hash then dump it to the XML file using something like XML::Simple or any of the other XML modules on CPAN? Again writing a CSV, rereading, reparsing and writing XML will be slow!

Upvotes: 2

Allen Holman
Allen Holman

Reputation: 125

Do you have access to a Windows box with excel 2007 or higher on it? Then you could do something like:

my $excel = Win32::OLE->new( 'Excel.Application' )
  or die "Could Not Start Excel.\n";
$excel->{ 'Visible' }           = 0;
$excel->{ DisplayAlerts }       = 0;

my $workbook = $excel->Workbooks->Open( 'test.xlsx' );

#save as Spreadsheet XML (46)
# 51 = xlsx, more mappings here: http://www.datapigtechnologies.com/downloads/Excel_Enumerations.txt
$workbook->SaveAs( $output_file, 46 );
$workbook->Close();
$excel->Quit();

Upvotes: 1

Related Questions