On Tue, Jan 29, 2013 at 1:02 PM, Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx> wrote:
So how ARE you creating the file for Excel?
We have a hodgepodge here. Some third-party, some home-grown. Some
CSV, in various flavors; some XLS, in various flavors. We aren't
delivering XLSX yet, though I've experimented with it a little bit.
There may still be some SYLK (Excel-compatible, format-capable,
text-based, typically .slk extension) lurking around.
Before I arrived, the main way to deliver a report in electronic form
was a commercial product that essentially scraped spool files into a
now-obsolete version of XLS and sent them as e-mail attachments. The
results are very crude. We still have a bunch of reports using this.
There were a number of home-grown CSV approaches, some that use
CPYTOIMPF or CPYTOSTMF, some that are more customized. We still have
some reports that use these.
My preferred method is to use iSeries Python and the xlwt package
(both free) to generate XLS files. It's similar in capability to the
POI-based stuff (like Scott Klement's HSSF), but arguably easier to
use.
The SYLK and xlwt-generated XLS files do use quite extensive
formatting. I created a utility to convert physical files to XLS
(which I posted on code.midrange.com a while ago); it gives you a
certain amount of control over the formatting in the file itself (via
the column headings, edit codes/words, and comment text defined in the
file). For fancier stuff (merged cells, headers and footers,
formulas, frozen panes, colors), I have custom Python programs for
specific reports.
Are you bursting these "reports" such that each sales rep (for example)
gets his or her own pages only? Or do you create separate reports for each?
I am not sure what bursting would mean for CSVs or Excel files. I
guess with Excel you could have different sheets for different people.
I don't know how I would go about "bursting" this; it's certainly no
harder to create multiple Excel files than it is to create multiple
sheets in a single Excel file.
John
midrange.com
