reaper, Here's a sample on howto take a workbook split into multiple sheets -> csv. 


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

my $ver = "1.0.0";
my $date = "2005-08-25";
my $stime = time();
use warnings;
use strict;
use Getopt::Long;
use joes_pm;
use Spreadsheet::ParseExcel;
$|++;


#workbook = $wb = whole file
#worksheet = $ws = each tab     


$wb = Spreadsheet::ParseExcel::Workbook->Parse($if);

@wss = ();
@wss = @{$wb->{Worksheet}};
print "WSS: \"@wss\"\n";

my $file = $wb->{File};
print "FILE: \"$file\"\n";

my $wsct = $wb->{SheetCount};
print "WSCT: \"$wsct\"\n";



print "\n\n";

print "DUMP EACH SHEET\n";
$ws = undef; $wsname = undef;
foreach $ws (@wss)
  {
  $wsname = undef; $minrow = 0; $maxrow = 0; $mincol = 0; $maxcol = 0;

  print "WS: \"$ws\"\n";

  $wsname = $ws->{Name};
  print "WSNAME: \"$wsname\"\n";

  if($wsname =~ /sum/i) { print "FOUND SUM SHEET NEXT..\n\n"; next; }

#  $minrow = $ws->{MinRow};
#  $maxrow = $ws->{MaxRow};
#  $mincol = $ws->{MinCol};
#  $maxcol = $ws->{MaxCol};

#  ($minrow, $maxrow) = $ws->RowRange();
#  ($mincol, $maxcol) = $ws->ColRange();

  $minrow = 0;
  $maxrow = 65535;            
  $maxrow = 1000;            
  $mincol = 0;                 
  $maxcol = 255;
  $maxcol = 100;

  print "MINROW: \"$minrow\"\n";
  print "MAXROW: \"$maxrow\"\n";
  print "MINCOL: \"$mincol\"\n";
  print "MAXCOL: \"$maxcol\"\n";


  print OUT "---------------------------------------------------------
+- WSNAME: \"$wsname\".. START\n";

  for($minrow = 0; $minrow <= $maxrow; $minrow++)     
    {
    $rowval = undef;
    if($debug == 0) 
      { 
      if(!($minrow % 1000)) { print "."; } 
      if(!($minrow % 5000)) { print "$minrow"; } 
      if(!($minrow % 25000)) { print "\n"; } 
      }

    if($debug == 1) { print "ROW: \"$minrow\"\n"; }
    for($mincol = 0; $mincol <= $maxcol; $mincol++)     
      {
      if($debug == 1) { print "ROW: \"$minrow\"\n"; }
      if($debug == 1) { print "COL: \"$mincol\"\n"; }
      $cell = undef; $val = undef;

      $cell = $ws->{Cells}[$minrow][$mincol];
      if($cell)
        {
#        $val = $cell->Value; #Formatted Value    
#        if((!$val) || ($val eq "")) { $val = ""; }
#        if($debug == 1) { print "FORMAT VAL: \"$val\"\n"; }

        $val = $cell->{Val}; #Original Value    
        if((!$val) || ($val eq "")) { $val = ""; }
        if($debug == 1) { print "ORIG VAL: \"$val\"\n"; }
    $val = clean($val);
    $val = lc($val);

    $val =~ s/\,//g;
    $rowval .= $val . ",";

        if($debug == 1) { sleep 1; }
        } #EO CELL
      else
        {
    if($debug == 1) { print "CELL IS NO DEFINED\n"; }

    $val = "";
    $rowval .= $val . ",";
        } #EO !CELL

      if($debug == 1) { print "\n"; }
      if($debug == 1) { sleep 1; }
      } #EO COL

    #### CHECK TO SEE IF ROW IS BLANK
    if($debug == 1) { print "ROWVAL: \"$rowval\"\n"; }
    $val = undef;
    $val = $rowval;
    $val =~ s/\,//g;
    if($debug == 1) { print "VAL: \"$val\"\n"; } 
    if($val eq "")
      {
      if($debug == 1) { print "ROWVAL IS BLANK... NEXT\n"; }
      next;
      }
    #### EO CHECK TO SEE IF ROW IS BLANK

    print OUT "$rowval\n";
    if($debug == 1) { sleep 1; }
    } #EO ROW


  print OUT "---------------------------------------------------------
+- WSNAME: \"$wsname\".. DONE\n";
  print "\n\n";
  } #EO EACH WS


print "\n\n";

########################################## EO INPUT FILE

[download]

Joe

In reply to Re: Parsing multiple excel files in perl by t_rex_joe
in thread Parsing multiple excel files in perl by reaper9187

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.