Solved

Perl program reading Excel document and creating html

Posted on 2004-04-25
14
149 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Automate PC's, Laptop to correct OU 4 54
powershell switch statement 7 47
Find unused columns in a table 12 96
form isn't working is it missing a document 21 64
Introduction This tutorial will give you a fast look what you can do with WhizBase. I expect you already know how to work with HTML at least, and that you understand the basics of the internet and how the internet works. WhizBase is a server-s…
This article will show, step by step, how to integrate R code into a R Sweave document
The viewer will learn how to count occurrences of each item in an array.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

739 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