• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

Perl program reading Excel document and creating html

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
spoma
Asked:
spoma
  • 7
  • 5
1 Solution
 
spomaAuthor Commented:
Just an update:
The output looks more like this now.

( $row, $column) =>$value
0
 
TintinCommented:
How do you want the output to look?
0
 
spomaAuthor Commented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
TintinCommented:
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
 
spomaAuthor Commented:
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
 
spomaAuthor Commented:
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
 
TintinCommented:
Unfortunately I've never used Spreadsheet::ParseExcel, so I don't know about the attributes.
0
 
spomaAuthor Commented:
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
 
TintinCommented:
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
 
spomaAuthor Commented:
There is no way that I can access an excel file over the LAN and then do a save as through this.
0
 
TintinCommented:
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
 
spomaAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now