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]

Can you be more precise? Where is exactly the problem?

[Anonymous]

Hi,

    If my understanding about your query is right, you are looking to format the output results in the excel sheet to move to specific cells (like move data to cell C5 etc.).

    It is not possible with standard components. If you want to load data to an excel sheet in a predefined format, you will have to use custom java codes using routines. You can pass the values to this routine and the routine will take care of the rest of formatting.

    Having said that, you may have to do some quick surfing in java forums to get codes for excel formatting and from there, you can move forward in right direction.

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]

For example,

Lets say i sum up a column in Excel, say the value is 4250, i get from the tAggregate component.

I want to pass this value somehow into another Excel file, and put it in a particular sheet, that has name Report_Summary.

Then put it in the cell on that sheet at location B5.

That's it. Its that simple.

[Anonymous]

Using tFileOutputExcel and "Is absolute Y pos." in "Basic Setting" should work.

2019-05-22 18_24_26-Talend Open Studio for Data Integration (7.1.1.20181026_1147) _ GettingStarted (...

[Anonymous]

I am not looking to format the Excel. Not sure if we are both using the same terminology of Format here.
OK, then if that is the case, how can i pass the results of the TAggregate component, which is one value, like sum,avg, min, max to a Variable to give it to a Java variable. Or I would prefer then to send this data to a Python script instead then. Hope you can let me know.

[Anonymous]

Can you post screenshots?

[Anonymous]

Hi sescofet,

SO can i enter the position location in Excel using this component, in the alpha-numeric form of "B5" and "$A$7".

[Anonymous]

Hi sescofet,
I am not sure why you are asking for screenshots. You want a screen shot of an excel file ?

[Anonymous]

No, but you can translate with java code directly in component definition (but why?).