using Talend to get data from Excel and then put that Data to Excel output file

[Anonymous]

Hi,

I am trying to using an Excel sheet as input, and then i used the tAggregate component to get some summary values like : SUM, AVERAGE of columns of the data in Excel.

But I am having difficulty trying to place the summary data in specific sheets / cells in the output Excel file.

SO its about using Talend to create a summary Report.

Hope someone can help me out with this, it seems that this should be simple.

(I am using Talend Open Studio for BigData version 7.1)

P.

[Anonymous]

No of your workflow and tFileOutputExcel parameters.

[Anonymous]

Hi,
Is there an easy way to pass the value from tAggregate component? I don't want to look at the auto generated Java code and try to figure out what is where. Using talend the point is to try not to become a Java Developer.

[Anonymous]

Hi Sescofet,

I have not setup the tFileOutputExcel parameters, because i did not know what i should set.
That is why i came here on this forum, in order to ask on how and what to set.

[Anonymous]

Hi,

   Could you please try the below properties?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

[Anonymous]

Hi nikhilthampi
This seems to be using an (x,y) coordinate system for only one cell which is fine for one cell.
But the other notation of "B5", can be used for a Range of Cells. Lets say i want to extract a rectangular range of cells in Excel using the range notation : "B6 9" this represent a 3 by 3 array/matrix in Excel and i want to place this input range of cells to the output.
SO i am thinking about something more generalized.

[Anonymous]

X:Y is the top left range used.

Other solution : data in a hidden sheet and formula to "resume sheet"

2019-05-22 18_47_42-out.xls [Compatibility Mode] - Excel.png
2019-05-22 18_48_06-Talend Open Studio for Data Integration (7.1.1.20181026_1147) _ GettingStarted (...

[Anonymous]

SO, it seems i need to show you the excel files, the input and output.

See the screenshots of example Input data and the output Excel i want to get.

ExcelInputData.PNG

[Anonymous]

[Anonymous]

Hi sescofet,
That looks good the image you provided.
So it seems one can use X:Y, seems to be the top left point, but the cell is F6, which would be (6,6), but your coordinate values are (5,5).
I am guessing the convention being used is the reference global point is (0,0) at the top most left, which is different from the (1,1) starting point that Native Excel uses.

[Anonymous]

That's right (consider using offset function from A1 for values)

Please note resolved if it is ok for you.