Ask Ben: Updating An Excel Document More Than Once With The POI Utility
Posted August 6, 2008 at 9:04 AM
Does this utility [POI] include a way to continue to add data to the spread sheet after the first addition? Thanks again for all of your help.
Yes it does. Well, sort of. The POI utility ColdFusion custom tags allows you to dictate a "template" to be used. This template is an existing Excel file whose contents are read in without alteration. Then, you can make changes to the existing Excel data as you see fit. So, if you treat each file as a template, you can continue to make as many updates as you want.
What the POI ColdFusion custom tags do NOT do is pick up where they left off. So for example, if you wrote 100 rows then closed the file. Then opened it again and wrote 100 rows - you'd end up with a file that has 100 rows in it, not 200. If your intent is to have 200 rows, then perhaps we can update the functionality of the tags. Because the "Index" attribute of the rows and cells allows you to manually output data in position you like, I want to avoid the idea of an "append" action:
Launch code in new window » Download code as text file »
- <poi:sheet action="append">
Besides, if you really are using the POI custom tags to populate a formatted template, you aren't really appending.
What about something like a CALLER-scoped variable that has information about the read-in Excel file? Something like this:
Launch code in new window » Download code as text file »
- <poi:sheet>
- <poi:row index="#(POI.InitialRowCount + 1)#">
- <poi:cell ... />
- </poi:row>
- </poi:sheet>
Here, in the context of the current Sheet, POI.InitialRowCount stores the number of rows that were already in the Excel file at the time of instantiation. This way, you could tell the "Index" tracking to pick up after the last row of the read-in Excel file.
Do you think something like that might be useful?
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Print Page
Newer Post
OOPhoto - Refactoring "With Transaction" Methods To BaseService.cfc
Older Post
Ask Ben: Moving Decimal Places And Formatting Numbers
Reader Comments
In most cases this solution would be perfect. In my case the row numbers are part of the template design (excel row numbering is turned off for aesthetic purposes), therefore, when the excel file is read there will be data in Column 1 row 1 thru ...
@Ana,
I am not sure that I am following you exactly. Are you saying that your using row numbers in a way that this would not work?
Yes. From my understanding of what you wrote is that the IntitialRowCount() would check the for the last row that has data. Is that correct? If so the author of the excel file as placed row numbers in column1:row1 with a style. That's why I think what you are saying wouldn't work for me.
