Re: Excel BIFF conversions

[jmcnamara]

On Tuesday, October 23, 2012 3:02:19 PM UTC+1, Frosty wrote:

 

Lately we have been informed that our policies will no longer accept excel files written in BIFF 4 or earlier formats.

 

 

Hi,

 

The following example using Spreadsheet::ParseExcel::SaveParser may work to convert BIFF4 files to BIFF8.

 

 

    #!/usr/bin/perl

    use strict;
    use warnings;
    use Spreadsheet::ParseExcel::SaveParser;

 

    my $parser   = Spreadsheet::ParseExcel::SaveParser->new();
    my $workbook = $parser->Parse('excel4.xls');

 

    $workbook->SaveAs('excel7.xls');

 

    __END__

 

You will have to test it to see how successful the conversion is. 

 

John

--

[Frosty]

 

Thx, I'll test that out today.  But it also brings up another interesting point ... How do I know what BIFF format the output file is saved in?  And, is there a way to set the BIFF output format or does it just save at the latest BIFF format recognized on the server?

[Frosty]

 

The sample code does not work in windows.  I'm getting a long series of errors from the module.  Here's a small extract:

 

Use of uninitialized value in numeric ge (>=) at C:/Perl64/site/lib/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 128.
Use of uninitialized value in numeric ge (>=) at C:/Perl64/site/lib/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 131.
Use of uninitialized value in numeric eq (==) at C:/Perl64/site/lib/Spreadsheet/ParseExcel/SaveParser/Workbook.pm line 159.

 

I really hate to go into a module and have to start mucking around in there.

 

Spreadsheet-WriteExcel at version 2.37

Spreadsheet-ParseExcel at version 0.59

 

On Tuesday, October 23, 2012 9:02:19 AM UTC-5, Frosty wrote:

I've got an interesting situation over here and I'm not sure if this group can provide a solution, but I'm giving it a shot.

 

In our situation we have a report server which handles a large volume of requests to create output, sometimes with excel output which is provided to the users.

 

Lately we have been informed that our policies will no longer accept excel files written in BIFF 4 or earlier formats.

 

Due to time constraints we're trying to see if there was a programmatic way to use Perl to convert an Excel file from one BIFF format to another.

 

 

[jmcnamara]

On Tuesday, October 23, 2012 5:07:50 PM UTC+1, Frosty wrote:

 

The sample code does not work in windows.  I'm getting a long series of errors from the module.  Here's a small extract:

 

 

Hi,

 

The warnings may be mostly harmless. Try turning off strict and warnings and see if the conversion actually works.

 

 

Otherwise, the Gnumeric spreadsheet application comes with a commandline utility called ssconvert that can convert between a variety of spreadsheet formats on the commandline.

 

  http://projects.gnome.org/gnumeric/
  http://projects.gnome.org/gnumeric/doc/sect-files-ssconvert.shtml

 

John.

--

 

 

[Frosty]

 

Follow-up: 

 

The errors appear to get created with the BIFF2, BIFF3 and BIFF4 formatted Excel files.  The BIFF1 files just crash.

BIFF5 seems to work, but I can't tell what BIFF level the output actually is at.

[Frosty]

 

Already ahead of you.  I turned strict/warnings off first. 

[Matt Holdsworth]

If all else fails, just use the Win32::OLE module to 'remote control' an instance of Excel proper with Perl. Assuming you have Excel available on your Windows machine and Excel itself doesn't object to your old format files, that is. 

You will need to import the constants for Excel and specify your target xlFileFormat value from this list:

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.xlfileformat.aspx

Just get the list of in scope files and iterate over the list, opening each and doing a SaveAs on each using the parameters you want. 

It may feel clunky, but it should work OK especially if this is a one-off activity to bring historic files up to date. 

If you have new files being created in the old format which isn't compatible with the rest of your solution, this technique probably isn't robust enough IMHO. I would look to update whatever is outputting the older format files perhaps?

Cheers,

Matt

Sent from my iPhone

--
You received this message because you are subscribed to the Google Groups "Spreadsheet::WriteExcel" group.
To post to this group, send email to spreadsheet...@googlegroups.com.
To unsubscribe from this group, send email to spreadsheet-write...@googlegroups.com.
Visit this group at http://groups.google.com/group/spreadsheet-writeexcel?hl=en.
 
 

[Frosty]

 

jmcnamara: 

  The example program did not work for BIFF1-4 formatted files.
  The Gnumeric program looks interesting, but we are not allowed to use that type of open-source.

 

GainfulShrimp:
  A nice idea, but this is operating on a Windows server for current report generation.

 

All:
  The hope was for a quick and simple command-line function to convert the reports as they are created now (BIFF4) into a more modern format until a permanent solution was put into place.  After multiple searches I still haven't found a solution to determine the BIFF format for an Excel file either.

 

[John McNamara]

On 24 October 2012 19:08, Frosty <financi...@gmail.com> wrote:

 

jmcnamara: 

  The example program did not work for BIFF1-4 formatted files.
  The Gnumeric program looks interesting, but we are not allowed to use that type of open-source.

Hi Robert,

You don't need to actually install Gnumeric or even use it. The ssconvert utility is separate. If you are using Linux you may even find a package for it in your package manager.

You should at least evaluate it to see if it does what you need (I'm fairly sure that it does).

Regards,

John.

-- 

[Frosty]

 

We are not authorized to download or install GNUmerics or SSConvert.

 

Our platform is Windows.