Parsing Excel Files while maintaining format

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hello to all the Perl Monks out there!

I wish to parse information from an excel file and print it out in a HTML table to be displayed for my users.

As of now I'm using Spreadsheet::ParseExcel

I'm not sure if its just my skill level or the module. I'm also not sure if there any other modules better capable of doing what I need to do then this.

Basically, the script I have now parses the info. pretty well. However, I would like the printed results to contain the same exact formatting Excel has. For example, if you save the excel file as a .html file, the HTML file it generates replicates the excel format pretty darn well (same font, same margin widths, same table widths, same text color, same font weight , etc.).

My script now does not replicate it well. Althought it parses the right text and data, the formatting is way off. The alignment of certain columns dont fit, the font-weight and text are different which leaves me with a ugly looking table in HTML.

Does anyone know a better Excel module for what I need? Or should I stick with this module and see if I can get the format right? I tried for the last 2-3 hours and I have given up hope. So now I seek wisdom from this board. Thanks for taking your time to read this post.

Cheers,
Larry

2006-10-07 Retitled by g0n, as per Monastery guidelines
Original title: 'Parsine Excel Files while maintaining format'

Comment on Parsing Excel Files while maintaining format

Replies are listed 'Best First'.
Re: Parsing Excel Files while maintaining format by duckyd (Hermit) on Oct 06, 2006 at 23:41 UTC
Are you parsing out the formatting information in addition to the text & data? Each Cell has a Format, and each Format a Font. See Spreadsheet::ParseExcel::Cell, Spreadsheet::ParseExcel::Format, and Spreadsheet::ParseExcel::Font	[reply]
Re: Parsing Excel Files while maintaining format by Anonymous Monk on Oct 07, 2006 at 09:33 UTC
Hello, Well, I have this website that lets us download excel files. To me, this is the only data source I have to publish. Now, I can't just offer the excel files for my users to dl nor can I make a script that saves each excel file as html and then have a little perl script handle the output of those .html files generated. The thing is, I can only publish certain parts of the excel file. For example, publish rows 2a to 5a. This is why I need Spreadsheet::ParseExcel. It handles the receiving of certain parts of the excel file pretty well. Now as to the first reply, I do know that Spreadsheet::ParseExcel has a format and font class. But it can't retreive it all. For example a cell with the format of "general" can either be align left or right. Also cells with a border style outline can not be intepreted since theres no format for it. Also the width, height, spacing of cells can not be retreived either. I think I can only parse an excel file and format it into a html table to a certain limit. Perhaps I can take a excel file and use Spreadsheet::ParseExcel to parse the certain rows and columns, then make a new spreadsheet, and save as .html file? If I find a solution to display "exact detail format from an excel file", I'll post back. Cheers, Larry	[reply]
Re^2: Parsing Excel Files while maintaining format by Hue-Bond (Priest) on Oct 07, 2006 at 10:58 UTC
The thing is, I can only publish certain parts of the excel file. For example, publish rows 2a to 5a. Then, another approach would be copy the file to another .xls and then remove from it all information that shouldn't be published. That way, you wouldn't have to deal with HTML, formatting and such, and your users would get an xls file identical to the original, just "censored". -- David Serrano	[reply]
Re^3: Parsing Excel Files while maintaining format by Anonymous Monk on Oct 07, 2006 at 20:51 UTC
Thats actually a good idea. I'll have to ask for permission first, not sure if they will let users dl .xls files i created from their source vs. viewing content from web where theres in exchange we display a small ad of theirs. But I do see this way as much more convenient and probably the best solution. Cheers, Larry	[reply]
Re: Parsing Excel Files while maintaining format by lyklev (Pilgrim) on Oct 07, 2006 at 09:03 UTC
I don't understand why you would go through all the trouble first breaking the information apart (data vs. formatting) and then trying to format the data exactly like the way it was. Why are you even trying to read the data? If all you want is 'exporting to html' from perl, you might be better off (on a Win32 platform) controlling perl from excel, using the Win32::OLE module; it allows you to run excel commands on spreadsheets (like editing spreadsheets, saving, creating new worksheets) from perl. The ActiveState documentation that comes with perl has some excellent examples. If you want to do interpretation of the data, formatting of the table can be simplified using CSS.</p?	[reply]