Re: Parsing multiple excel files in perl
by marto (Cardinal) on Oct 15, 2012 at 10:19 UTC
|
Since there are more than one way to read Excel files you should make it easy for us to help you and show us the code you're working with.
| [reply] |
|
Thanks for the reply
This part of the application is a small portion of the entire software package i'm working on. Haven't written the code yet for this . Was looking for some kind of a headstart
I have developed a parser for a single workbook for another section of the package but couldn't understand the process for multiple file handles.
| [reply] |
|
"I have developed a parser for a single workbook"
So are not using a module such as Spreadsheet::ParseExcel or Win32::OLE and have hand rolled a solution? Again, if you don't bother to show us what you're doing and what you're having problems with you make it harder to help, and drawing information piecemeal gets boring after a while.
Read and understand How do I post a question effectively?.
| [reply] |
|
|
|
|
|
|
Re: Parsing multiple excel files in perl
by nemesdani (Friar) on Oct 15, 2012 at 10:24 UTC
|
| [reply] [d/l] [select] |
|
I'm using Spreadsheet::ParseExcel
| [reply] |
Re: Parsing multiple excel files in perl
by grizzley (Chaplain) on Oct 15, 2012 at 12:12 UTC
|
Seeing the codes you posted I guess your problem is really with design rather then with Perl itself. Codes posted are not complete, so I assume you face following:
You have GUI application, in which after pressing some button 'Open file' dialog is displayed, where you choose file to be opened, then subroutine (which you pasted) is run in order to do stuff with this file. Am I correct that your problem is that you don't know how to allow user choosing more than one file from dialog box? And another thing is how to pass result of this dialog box to subroutine in order to open more files?
This kind of information is what we need. If you answer yes to those questions, then you should consider following two approaches (I don't know API of GUI you use and don't know if it allows such operations): 'open file' dialog window should allow user to choose more than one file (standard way on Windows is with Ctrl key) or you should open 'open file' dialog window in the loop as many times as there are files to be processed.
So if you were able to present following code of your GUI:
$window->create("my main window");
$window->addbutton(somex, somey, somewidth, someheight, name, \&mysub)
+;
sub mysub
{
my $result = $window->openFileDialog(...);
# error check
open FH "$result" or die $!;
# parsing file
close FH;
}
you could get proper help from us. Of course in case my assumptions are correct. | [reply] [d/l] |
|
Hi.. Thank you for replying
Well you pretty much right about the GUI part. However, my problem is slightly different from the one u mentioned here.
I'm actually trying to choose multiple files using multiple dialog boxes( jus a part of the user interface) and then pass these filenames as arguments to the parser which then sequentially checks the cell values and compares it to a set threshold and gives out the results. I'm not able to parse multiple files simultaneously after passing the filenames as arguments
Thanks again
| [reply] |
|
Then you have problem with algorithm parsing files? Because opening multiple files is simple, you just use different file handles:
open FH, "file1" or die $!;
open FH2, "file2" or die $!;
$readlinefromfile1 = <FH>;
$readlinefromfile2 = <FH2>;
$readfromfile1again = <FH;
close FH2;
close FH;
| [reply] [d/l] |
|
Re: Parsing multiple excel files in perl
by t_rex_joe (Sexton) on Oct 15, 2012 at 16:31 UTC
|
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
Joe
| [reply] [d/l] |
|
| [reply] |