Solved

Perl program reading Excel document and creating html

Posted on 2004-04-25
14
146 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
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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

In this tutorial I will show you how to provide a dynamic RTF document on your website generated with data from your database. For this tutorial you will need Microsoft Word or WordPad, WhizBase and Microsoft Access. In this tutorial I will show …
Active Directory replication delay is the cause to many problems.  Here is a super easy script to force Active Directory replication to all sites with by using an elevated PowerShell command prompt, and a tool to verify your changes.
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

820 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