adding an excel worksheet to a generetad excel file via perl

F

FlorianKraft

Hi,

I'm currently working an a solution for exporting data from an oracle
database into an excel file, which is produced daily und therefore
kept up-to-date.

The solution works nice, the file is exported evey day with correct
data - but there's a small flaw. For the usage of this data a
disclaimer is provided as an extras excel worksheet within the
workbook - the problem is, that currently every small change (e.g.
management, contact, etc.) mut be implemented as a series of functions
bulding up the extra sheet and adding it to the gernerated file - it
thereby feels as if one has to do it manually all the time, which
requires some unterstanding of the code as well as time, as the
disclaimer data often changes in its measures (cell widths, etc.)

I now had the idea to change this system, allowing normal users to
easily access the disclaimer and thereby stop punishing me with
senseless extra work for every small change. Therefore i made an extra
Excel file, containing just the disclaimer so it could be easy for
anyone to change it.

The problem now is - how do i add such a file as an extra worksheet? i
tried some things up to now - which involves completely parsing it via
Spreadsheet::parseExcel, spllitting up the data in an array and a hash
(cell contens and cell formats) an then trying to rewrite it via
Spreadsheet::WriteExcel. It seems somehow incompatble when writing the
data read (as it is somehow complicated to find out backgroundcolours,
internal graphics, cell-merging, and so on)

I do not have the option of another module (Win32::OLE will not work
due to the fact that i just have a Linux System) nor do i have the
time for a complete rewrite of the original code of the export script.

So does anyone have an idea how to read a single worksheet of an
existing excel file (e.g. disclaimer.xls) and adding it into a
generated one?

thanks,

florian
 
M

Mirco Wahab

[a] disclaimer is provided as an extras excel worksheet within the
workbook - the problem is, that currently every small change (e.g.
management, contact, etc.) mut be implemented as a series of functions
bulding up the extra sheet and adding it to the gernerated file - it
thereby feels as if one has to do it manually all the time, which
requires some unterstanding of the code as well as time, as the
disclaimer data often changes in its measures (cell widths, etc.)

I now had the idea to change this system, allowing normal users to
easily access the disclaimer and thereby stop punishing me with
senseless extra work for every small change. Therefore i made an extra
Excel file, containing just the disclaimer so it could be easy for
anyone to change it.

The problem now is - how do i add such a file as an extra worksheet? i
tried some things up to now - which involves completely parsing it via
Spreadsheet::parseExcel, spllitting up the data in an array and a hash
(cell contens and cell formats) an then trying to rewrite it via
Spreadsheet::WriteExcel. It seems somehow incompatble when writing the
data read (as it is somehow complicated to find out backgroundcolours,
internal graphics, cell-merging, and so on)

Of course I don't know all of the complexities
involved with your xls report generation, but
here's how I'd do that ...

You said the problem is some "small other excel file"
with necessary extra data - that gets updated occasionally?

An excel table is in most cases 'a table'. Therefore, I'd
write a small Perl script that reads the 'extra file' at
times and puts it into a new table of the main db (oracle
or whatever) if something changed.

On generation of the "real report" xls, I'd just 'select'
the data together with the real data and put each in its
place in the generated file.

Don't know if that would work in your case,

Regard

M.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,731
Messages
2,569,432
Members
44,832
Latest member
GlennSmall

Latest Threads

Top