CODEWITHSUNDEEP
xmlperlxslt
user2538554
user2538554

Reputation: 11

perl code for converting the contents in .xls to .xml

any one help me out to write a perl script for creating the .xml from the .xls/.csv file provided....

I am trying the below code...and it is not working atall...

#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::ParseExcel;
use XML::Writer ();
use XML::Writer::String ();
use IO::File;
use File::Find;

print"Enter1";

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('C:\Users\uidw3321\Desktop\one.xls');

if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}
my $xml_doc = XML::Writer::String->new();
my $xml_file = new IO::File(">D:\\tmp\\bmw.xml");
my $xml_doc_writer = new XML::Writer(OUTPUT =>  $xml_file);             

for my $worksheet ( $workbook->worksheets() ) {

    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();

    my %headers = ();
    for my $row ( $row_min .. $row_max ) {
        for my $col ( $col_min .. $col_max ) {
    my $cell = $worksheet->get_cell( $row, $col );
            if ($row == 0) {
                $headers{$col} = $cell->unformatted();
            } else {
                $xml_doc_writer->dataElement($headers{$col},$cell->unformatted());
            }
        }
    }

}
$xml_doc_writer->end(); 
$xml_file->close();

Please help

Upvotes: 1

Views: 1372

Answers (1)

robert.r
robert.r

Reputation: 31

Your code throws an error: "Attempt to insert start tag after close of document element". To fix this, just start XML content with some tag:

$xml_doc_writer->startTag("someTag");

and finish it with

$xml_doc_writer->endTag();

In summary Your working code should be:

#!/usr/bin/perl

use strict;
use warnings;
use Spreadsheet::ParseExcel;
use XML::Writer ();
use XML::Writer::String ();
use IO::File;
use File::Find;

print"Enter1";

my $parser   = Spreadsheet::ParseExcel->new();
my $workbook = $parser->parse('C:\Users\uidw3321\Desktop\one.xls');

if ( !defined $workbook ) {
    die $parser->error(), ".\n";
}
my $xml_doc = XML::Writer::String->new();
my $xml_file = new IO::File(">D:\\tmp\\bmw.xml");
my $xml_doc_writer = new XML::Writer(OUTPUT =>  $xml_file);             

$xml_doc_writer->startTag("someTag");
for my $worksheet ( $workbook->worksheets() ) {

    my ( $row_min, $row_max ) = $worksheet->row_range();
    my ( $col_min, $col_max ) = $worksheet->col_range();

    my %headers = ();
    for my $row ( $row_min .. $row_max ) {
        for my $col ( $col_min .. $col_max ) {
    my $cell = $worksheet->get_cell( $row, $col );
            if ($row == 0) {
                $headers{$col} = $cell->unformatted();
            } else {
                $xml_doc_writer->dataElement($headers{$col},$cell->unformatted());
            }
        }
    }

}

$xml_doc_writer->endTag();
$xml_doc_writer->end(); 
$xml_file->close();

I hope it will help, cheers!

Upvotes: 1

Related Questions