Re: RPG and Excel

[Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

Thomas

I just gave a presentation at COMMON about creating XLSX files using RPG 
and CGIDEV2.

This is very fast, and lets you do anything Excel can do. Of course, 
some things are harder than others - no way I'm going to do pivot tables 
anytime soon!

But regular tables might be very doable. I get an Excel workbook, then 
trim it down so that there is basically 1 instance of anything I want to 
generate - 1 detail row in a sheet, 1 of each summary row, one sheet 
instance for workbooks with multiple instances of the same kind of 
information - monthly reporting, for example.

I've not done tables yet - but all it takes is to create the workbook 
and open the XLSX with 7-Zip or the like - see what it takes to do 
tables - if it's not a matter of 3 or 4 relationships, it is probably 
very simple.

If interested, contact me off-list, and I'll send you the presentation, 
as well as the lab that goes along with it.

Scott's API can be used in conjunction with this technique for some 
things, like inserting image files of different sizes and locations - 
that kind of thing requires calculations that I'm not prepared to do 
right now. POI does those calcs just fine.

Regards
Vern

On 5/1/2015 12:46 PM, Thomas Garvey wrote:
> Hmmm. That might work.
> First create the spreadsheet in Excel and define the table.
> Then use the Klement stuff to find that spreadsheet and write the data 
> into the table of the spreadsheet, knowing it's row and column 
> definitions already.
>
> I'll look into it.  Thanks for the brainstorm.
>
>
> Best Regards,
>
> Thomas Garvey
>
>
>
> On 5/1/2015 12:37 PM, darren@xxxxxxxxx wrote:
> > Now that I think about it, I actually looked into creating tables in 
> > Excel
> > a couple months ago.  I decided that it looked a bit too complicated 
> > at the
> > time.  That project also required a really complicated chart system, and
> > for that, I created a spreadsheet in Excel, and pointed the charts 
> > toward a
> > blank section of data.  With that done, I wrote an RPG program using 
> > Scott
> > Klement's tool to populate data on an existing template spreadsheet.
> > Something like that might work for your table project, where you could
> > either populate the table cells themselves, or if that doesn't work, put
> > formulas in the table that refer to a more traditional section of the
> > spreadsheet, where you'll populate the data.
> >
> >
> >
> >
> >
> >
> >
> > From:    Thomas Garvey <tgarvey@xxxxxxxxxx>
> > To:    "RPG programming on the IBM i (AS/400 and iSeries)"
> >              <rpg400-l@xxxxxxxxxxxx>
> > Date:    05/01/2015 12:30 PM
> > Subject:    RPG and Excel
> > Sent by:    "RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx>
> >
> >
> >
> > Anybody know if Scott Klement's Excel/RPG/Java stuff allow creating of
> > tables in an Excel spreadsheet?
> > --
> >
> >
> > Best Regards,
> >
> > Thomas Garvey
> >
> >
> > --
> > This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
> > mailing list
> > To post a message email: RPG400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: RPG400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/rpg400-l.