Ask Ben: Updating An Excel Document More Than Once With The POI Utility
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:
<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:
<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?
Want to use code from this post? Check out the license.
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.
Ben, was anything ever done with the "InitialRowCount". This would be perfect for a project that I'm working on. That or the ability to append to an excel file! :D How would I go about this?
@Steve,
I haven't done much with this project in a while. At some point, I keep meaning to make some time really give it a solid upgrade. Just never enough time!