One Star

Inserting rows into Excel table with tFileExcelSheetOutput

Hello,
I have been using Talend for some time now, but this is an issue I could not figure a solution for.
I need to produce Excel reports on a regular basis where data is presented in pivot tables and charts.
For some time I used Hugo's Excel component -- Thank you, Hugo. In fact, I discovered and corrected and error and sent the correction to Hugo a couple of years ago, but it is not sufficient anymore and I lack the expertise to rewrite the whole component.
Recently I discovered Jan Lolling's set of components and I love them -- a big thank you, Jan.
With the improved behavior of tables within an Excel sheet in recent versions (pretty much since the new xlsx format appeared it makes the most sense to dump data into an existing table in a template file and make the related pivots and charts refresh. This is because of the dynamic re
I have not been able to do this without manual intervention, i.e. actually opening the files and copy/pasting the data into the target table. Of course, this is not preferred.
The solution might be in front of my eyes, but I cannot see it. Does anyone have any ideas?
P.S. Jan: when I am in Germany next time I should swing near your hometown and invite you to have some bier, or whatever your preferred beverage may be... Smiley Happy
3 REPLIES
Seventeen Stars

Re: Inserting rows into Excel table with tFileExcelSheetOutput

Hello, I am working to find a solution to update a pivot table with newly inserted data. Currently it is not possible - as you unfortunately must experiences.
Stay tuned, it could not be impossible - the Apache POI API is very powerful and the Apache guys constantly improving its API. So most of the capabilities of my excel related components are the result of the great work of these guys!
One Star

Re: Inserting rows into Excel table with tFileExcelSheetOutput

Thank you for the reply, Jan.
How about the issue of updating an Excel table. In the attached screenshot the highlighted row is the (empty) data of the table Table1.
That is what I would like to update (or create if that is easier), however, if I just insert the rows there with the module the table remains the current size, i.e. 1 row, the rest of the rows will be outside the table, and therefore the pivot table won't be able to reference those rows without redefining the table.
I do not exactly know what these folks are talking about, :lol:, but it seems that this discussion is relevant here. Based on my vague understanding the POI is able to handle tables in an Excel sheet.
http://apache-poi.1045710.n5.nabble.com/Create-a-new-Table-into-an-XSSF-sheet-excel-2007-td4288371.h...
(Edit: Trying to add screenshot)
(Edit 2: It seems I cannot add the image...)
Seventeen Stars

Re: Inserting rows into Excel table with tFileExcelSheetOutput

Hi Gabor,
many thanks for your ideas. In fact dealing with tables should be a nice feature and could potentially solve out Pivot-problem.
I will dive into and find out how to adopt this in the component tFileExcelSheetOutput.
Best regards
Jan