Solved

Perl program reading Excel document and creating html

Posted on 2004-04-25
14
150 Views
Last Modified: 2013-12-25
I have an excel document and I need to be able to read this document into a program and generate an html website out of it.  Perl was the first language that popped into my head ot use and have everything done server side.  I will be running this website on a Solaris machine running Apache.

I have been looking into the Spreadsheet::ParseExcel module.  After running this I seem to get output that looks something like this:

At ($row, $column) the value is $value

My question is then how do I use this data and do anything with it?  Has anyone done something similar to what I am trying to do.  Any help would be extremely appreciated.
0
Comment
Question by:spoma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
14 Comments
 

Author Comment

by:spoma
ID: 10914658
Just an update:
The output looks more like this now.

( $row, $column) =>$value
0
 
LVL 48

Expert Comment

by:Tintin
ID: 10914802
How do you want the output to look?
0
 

Author Comment

by:spoma
ID: 10914826
I have a $iR which is the row number then I have a $iC which is the column number and a  $oWkC->Value which is the value.  I am trying to just make a table out of this.  If I can get the basic html coming out then I can change it so that I get it to look as I would like.

I am currently in the process of trying to write so that everytime a new column comes through that I print out a <td> and that when the row comes along I print a <tr>.  I am having trouble closing the tags at the write place.  

This is what I have so far.  The basics of this program I took from CSPAN in their definition of the module.

#!/usr/bin/perl -w

use strict;
use Spreadsheet::ParseExcel;

my $oExcel = new Spreadsheet::ParseExcel;

die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV;

my $oBook = $oExcel->Parse($ARGV[0]);
my($iR, $iC, $oWkS, $oWkC);
my $iC1 = 0;
my $iR1 = 0;

#######################################
# Help figuring this out
# $iR is the row number
# $iC is the column number
# $oWkC->Value is the Value at the specific row, column combination
#######################################

print "FILE  :", $oBook->{File} , "\n";
print "COUNT :", $oBook->{SheetCount} , "\n";

print "AUTHOR:", $oBook->{Author} , "\n"
 if defined $oBook->{Author};

for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
{
 $oWkS = $oBook->{Worksheet}[$iSheet];
 print "--------- SHEET:", $oWkS->{Name}, "\n";
 if ($iSheet == 0) {print "<tr><td>" }
 for(my $iR = $oWkS->{MinRow} ;
     defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;
     $iR++)
 {
  for(my $iC = $oWkS->{MinCol} ;
      defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ;
      $iC++)
  {
   $oWkC = $oWkS->{Cells}[$iR][$iC];
   #print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);  
   
  #######################################
  # this is where i am trying to print out correct html
  #######################################

   if ($iC1 != $iC ) { print "</td></tr>\n<tr>" }
   if ($iR1 != $iR ) { print "\n<td>" }
   print $oWkC->Value, if($oWkC);
 
 
  }
 }
}
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
LVL 48

Expert Comment

by:Tintin
ID: 10914883
Try changing:

 if ($iC1 != $iC ) { print "</td></tr>\n<tr>" }
  if ($iR1 != $iR ) { print "\n<td>" }
  print $oWkC->Value, if($oWkC);

to

print "<tr><td>" . $oWkC->Value . "</td></tr>\n" if ($oWkC);

Also add a print "<table>\n"; at the begining of the loops and a closing </table> tag at the end of the loops.
0
 

Author Comment

by:spoma
ID: 10917321
This is what I have so far.  The problem I am having is if cells are merged together I am getting the same value for all cells of that merge.  I need to some how know that they are merged and then be able to do a <td colspan or something like that.  Well this is what I have so far.

#!/usr/bin/perl -w

use strict;
use Spreadsheet::ParseExcel;

my $oExcel = new Spreadsheet::ParseExcel;

die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV;

#######################################
# Help figuring this out
# $iR is the row number
# $iC is the column number
# $oWkC->Value is the Value at the specific row, column combination
#######################################

my $oBook = $oExcel->Parse($ARGV[0]);
my($iR, $iC, $oWkS, $oWkC);
my $iC1 = 0;
my $iR1 = 0;

#######################################
# Create HTML header
#######################################

print <<END;
Content-Type: text/html\n\n

<html>
<head>
<title>QA Dashboard</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">

END


#print "FILE  :", $oBook->{File} , "\n";
#print "COUNT :", $oBook->{SheetCount} , "\n";

#print "AUTHOR:", $oBook->{Author} , "\n"
# if defined $oBook->{Author};

for(my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++)
{
 $oWkS = $oBook->{Worksheet}[$iSheet];
 print "<table width=100%><tr><td><div align=center>WORKSHEET:", $oWkS->{Name}, "</div></td></tr></table>\n";
 if ($iSheet == 0) {print "<table width=100%><tr><td>" }
 for(my $iR = $oWkS->{MinRow} ;
     defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;
     $iR++)
 {
  for(my $iC = $oWkS->{MinCol} ;
      defined $oWkS->{MaxCol} && $iC <= $oWkS->{MaxCol} ;
      $iC++)
  {
   $oWkC = $oWkS->{Cells}[$iR][$iC];
   #print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC);  
   
   print "\n( $iR , $iC ) while ( $iR1 , $iC1 )";  
   if ($iR1 != $iR ) { print "</td></tr><tr><td>" }
   elsif ($iC1 != $iC ) { print "</td><td>" }
   print $oWkC->Value, if($oWkC);  
   print "\n";
   $iR1 = $iR;
   $iC1 = $iC;
  }
 }
}
print "</td></tr></table>";
print "</body></html>";
0
 

Author Comment

by:spoma
ID: 10918303
MY script seems to be working now to deal with merged cells in that it will just print out the value the first time and then leaves the others blank.  
I need to be able to get some information for the format of the cell.  There seems to be a piece that has attributes but I can not get them to work.  
Have you ever dealt with getting AlignH, MergedArea, Fill, and Hidden attributes from an excel cell.  Any help is appreciated and I apreciate the help so far.
0
 
LVL 48

Expert Comment

by:Tintin
ID: 10922586
Unfortunately I've never used Spreadsheet::ParseExcel, so I don't know about the attributes.
0
 

Author Comment

by:spoma
ID: 10923284
I understand.  Thanks for the help TinTin.  The only other way I can think of doing what I need to do is to write a Perl script which actually does a "Save As HTML" to an Excel spreadsheet.  Is this possible?  I would be running this on a Solaris machine again so it would have to work from a unix base machine.  
0
 
LVL 48

Expert Comment

by:Tintin
ID: 10923325
Your last suggestion is only going to work if you have a Solaris version of Excel (I'm not aware of a version existing)
0
 

Author Comment

by:spoma
ID: 10923893
There is no way that I can access an excel file over the LAN and then do a save as through this.
0
 
LVL 48

Accepted Solution

by:
Tintin earned 500 total points
ID: 10924547
If you want to use Excel to save as HTML, then you are going to have to run the Perl script on a windows box (where Excel is installed) and use the Win32:OLE module.
0
 

Author Comment

by:spoma
ID: 10928827
Thats to bad that you can't do that running from Solaris.  I am going to have to weigh my options and keep chugging away at this.  I have a website with what seems to be all the attributes and it is a matter of me getting the time to play around with them.  Thanks.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you get a (Blue Screen of Death), your system writes a small file called a minidump. Your first step is to make certain your computer is setup to record memory dumps. Right click My Computer, choose properties. Click on the advanced tab, an…
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question