RE: Reading ExcelSheet and Writing XML

> To: perl-xml@xxxxxxxxxxxxxxxxxxxxxxxx
> Subject: Reading ExcelSheet and Writing XML
> From: Dominik.Grupp@xxxxxx
> Date: Mon, 1 Dec 2003 16:37:22 +0100
> 
> This is a multipart message in MIME format.
> --=_alternative 0055D1AFC1256DEF_=
> Content-Type: text/plain; charset="us-ascii"
> 
> Hello,
> 
> unfortunately, I can't access the archive of mailings on 
> http://mailarchive.activestate.com/browse/perl-xml/ to see whether my 
> question was already answered somewhere else. 
> 
> I do have an Excel-file with several worksheets inside and 
> would like to 
> create a XML file out of it. I found a base solution to read 
> out Excel 
> files into a stream: 

<cut a bunch of stuff>


Here is my example:

use strict;
use warnings;
use Spreadsheet::ParseExcel;
use XML::SAX::Writer;
use XML::SAX;


my $w = XML::SAX::Writer->new(
        Output => 'd:\\excel.xml',
);

$w->start_document();
$w->xml_decl({Version => "1.0", encoding=>"utf-8"} );

my $oExcel = new Spreadsheet::ParseExcel;
my $oBook = $oExcel->Parse("c:\\test.xls");
$w->characters({Data =>"\n"});


my $oWkS = $oBook->Worksheet("Sheet1");
my @fields;

foreach my $headercol($oWkS->{MinCol}..$oWkS->{MaxCol})
{
        push @fields, $oWkS->{Cells}[0][$headercol]->Value;
}
my $endrow = $oWkS->{MaxRow};
$w->start_element({Name => "ExcelData" });

for(my $row = 1; $row <= $oWkS->{MaxRow} ; $row++)
{
        $w->start_element({Name => "ROW"});
   for (my $col = 0; $col <= $oWkS->{MaxCol}; $col++)
        {
                $w->start_element({Name => $fields[$col]});
                if (defined(my $oWkC = $oWkS->{Cells}[$row][$col]))
                {
                        $w->characters({Data => $oWkC->Value});
                }
                $w->end_element({Name => $fields[$col]});
        }
        $w->end_element({Name => "ROW"});
}
$w->end_element({Name => "ExcelData" });
$w->end_document;

In this case, I have a workbook which has "header" rows and data such as:

Username        Password        Group           Email
test1           test1pass       Group1  test1@xxxxxxxxxxx
test2           test2pass       Group2  test2@xxxxxxxxxxx

The first thing I do is push the header values into an array.  I then use these 
as subscripts later to generate my tag names.  Of course, if you want (more) 
pretty printed XML, you would have to add some calls to $w->characters({Data 
=>"\n"}); and $w->characters({Data =>"\t"});  to the appropriate places. 

Joe

_______________________________________________
Perl-XML mailing list
Perl-XML@xxxxxxxxxxxxxxxxxxxxxxxx
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs