Wrapper to make creating spreadsheets easier / more maintainable?

[John Arrowwood]

I'm using Excel::Writer::XLSX to create spreadsheets as invoices based on data extracted from our point-of-sale system.  

Creating the spreadsheets using standard script-like code is nearly unmaintainable.  And maybe "nearly" is being generous.

So I created a module to make it a little better.  It keeps state and lets me do things like automatically jump from column to column, auto-register styles/formats, and stuff like that.  It's better, but I still don't much care for it.

I'm wondering:  Surely other people have faced this kind of thing before.  Surely, other bright people have solved this problem before.

How have people made their spreadsheet writing scripts more readable and maintainable?  What are some of the patterns you have used in your coding?

Thanks!

[Rick Marshall]

John

Our report generator writes the scripts for us. The program is a simple and uses a template.

But for invoicing we have an xml (also template from same report generator) that is much more suitable and this is passed to an xslt processor which produces postscript for gs and then a pdf.

Much easier as it handles pagination and page backgrounds etc automatically.

Regards
Rick

PS We also use our own db system, but it can operate on data streams too.

--
You received this message because you are subscribed to the Google Groups "Spreadsheet::WriteExcel" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spreadsheet-write...@googlegroups.com.
To post to this group, send email to spreadsheet...@googlegroups.com.
Visit this group at http://groups.google.com/group/spreadsheet-writeexcel.
For more options, visit https://groups.google.com/d/optout.

--
Rick Marshall
Director
Zenucom Pty Ltd
0411 287530
http://www.zenucom.com

[mchappell67]

 

My group supports the finance/accounting function.  As such, they eat, sleep and breathe Excel.  No matter how great a report looked, they always wanted the data in Excel.  Prior to Excel::Writer, we were only able to provide CSV files - ugly, not very functional, and limited to a single worksheet.  There were hundreds of CSV files that were created, so writing new, dedicated Perl programs to create Excel files was out of the question.

 

We wrote a Perl program that uses Excel::Writer and Text::CSV_XS to read the CSV files and create the Excel files.  This works about 95% of the time without any modification to the CSV file.  This was only the first step, though, and took care of all of the existing, legacy files.

 

The Perl program also analyzes the CSV file for, what we call, directives.  These directives invoke Excel::Writer functionality.  For example, '<<xl-col_format width 15 fixed 1 num_format 0%>>::Discount' will create a fixed width column that is 15 wide that displays percentages without any decimal points.  'Discount' is the data in the cell.  We have implemented column/row/cell formatting directives, display directives, worksheet/workbook control directives - it's pretty fully featured.

 

This approach means that our staff doesn't have to learn Perl - they simply use our existing tools to create the CSV files; they only have to learn about the directives.  This eases the maintenance a bit, as the staff is using tools that they are familiar with.  Maintenance of these files will always be difficult, regardless of approach.  We are creating Excel workbooks/worksheets cell-by-cell - this will be a labor-intensive process.

 

Since we've implemented this tool, we have created increasingly complex, highly formatted output - you know, the things management loves to see...

 

The only downside is that we need to modify the Perl program every time we want to take advantage of another Excel::Writer feature, but that's not really too big of a deal.

 

Mark

[jmcnamara]

On Sunday, 15 March 2015 16:11:33 UTC, mchappell67 wrote:

 

My group supports the finance/accounting function.  As such, they eat, sleep and breathe Excel.  No matter how great a report looked, they always wanted the data in Excel.  Prior to Excel::Writer, we were only able to provide CSV files - ugly, not very functional, and limited to a single worksheet.  There were hundreds of CSV files that were created, so writing new, dedicated Perl programs to create Excel files was out of the question.

 

We wrote a Perl program that uses Excel::Writer and Text::CSV_XS to read the CSV files and create the Excel files.  This works about 95% of the time without any modification to the CSV file.  This was only the first step, though, and took care of all of the existing, legacy files.

 

The Perl program also analyzes the CSV file for, what we call, directives.  These directives invoke Excel::Writer functionality.  For example, '<<xl-col_format width 15 fixed 1 num_format 0%>>::Discount' will create a fixed width column that is 15 wide that displays percentages without any decimal points.  'Discount' is the data in the cell.  We have implemented column/row/cell formatting directives, display directives, worksheet/workbook control directives - it's pretty fully featured.

 

Hi Mark,

Thanks for that. It is always nice to hear real world uses of Excel::Writer::XLSX.

John