Solved

Perl program reading Excel document and creating html

Posted on 2004-04-25
14
142 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
  • 7
  • 5
14 Comments
 

Author Comment

by:spoma
Comment Utility
Just an update:
The output looks more like this now.

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

Expert Comment

by:Tintin
Comment Utility
How do you want the output to look?
0
 

Author Comment

by:spoma
Comment Utility
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
 
LVL 48

Expert Comment

by:Tintin
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 48

Expert Comment

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

Author Comment

by:spoma
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The following is a collection of cases for strange behaviour when using advanced techniques in DOS batch files. You should have some basic experience in batch "programming", as I'm assuming some knowledge and not further explain the basics. For some…
Recently I have been answering a lot of questions like this in IT forums that I frequent. The question posed is usually something along the lines of "We have software X installed and need to uninstall it for reason Y" or some other variant of the sa…
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…
Learn the basics of if, else, and elif statements in Python 2.7. Use "if" statements to test a specified condition.: The structure of an if statement is as follows: (CODE) Use "else" statements to allow the execution of an alternative, if the …

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now