Welcome to the tFileExcel components Discussions!

[jlolling]

👋 Welcome!

We’re using Discussions as a place to connect with other members of our community. We hope that you:

• Ask questions you’re wondering about.
• Share ideas.
• Engage with other community members.
• Welcome others and are open-minded. Remember that this is a community we
  build together 💪.

To get started, comment below with an introduction of yourself and tell us about what you do with this community.

[gertvh]

Hi Jan,
Thanks for this add-on, it is extremely useful.
I'm facing an issue with tFileExcelSheetOutput. If I enable the "Create sheet as copy", I get following error message:
java.lang.Exception: createCopy from source failed:The workbook already contains a sheet named 'SheetAaFirstCompany'
at de.jlo.talendcomp.excel.SpreadsheetOutput.createCopy(SpreadsheetOutput.java:518)
at de.jlo.talendcomp.excel.SpreadsheetOutput.createCopy(SpreadsheetOutput.java:502)

If I don't enable the template copy, the excel gets created fine. As a reference, here is my sample map:

Thanks in advance for help!

[jlolling]

Strange my tests does not find such an issue. I will check that asap gertvh ***@***.***> schrieb am Fr., 2. Juli 2021, 10:01:

…

Hi Jan, Thanks for this add-on, it is extremely useful. I'm facing an issue with tFileExcelSheetOutput. If I enable the "Create sheet as copy", I get following error message: java.lang.Exception: createCopy from source failed:The workbook already contains a sheet named 'SheetAaFirstCompany' at de.jlo.talendcomp.excel.SpreadsheetOutput.createCopy(SpreadsheetOutput.java:518) at de.jlo.talendcomp.excel.SpreadsheetOutput.createCopy(SpreadsheetOutput.java:502) If I don't enable the template copy, the excel gets created fine. As a reference, here is my sample map: [image: image] <https://user-images.githubusercontent.com/8401447/124241821-67aeac80-db1c-11eb-9cf2-17bdcbc44ded.png> Thanks in advance for help! — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#27 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AB5KS7JOUGXKSXXW2PV7GDLTVVW6XANCNFSM4UUDKZYA> .

[jlolling]

If you do not enable copy sheet the component creates a sheet if it does not exist and use the sheet if it exist. If you choose copy sheet the sheet must not exist before. Please check if you do not try to create the copy more than once.
I think it could be a useful option to allow copy-sheet and check if the sheet already exist and use in this case the existing sheet. This could simplify jobs.

[gertvh]

thanks for the quick reply.
My excel file only contains one tab "template", so the new sheet definitely doesn't exist when it is trying to copy the sheet. Any other things I can try?

[sramadour]

Hi Jan !

Thanks for your wonderful components, they are really lifer savers for some requests we got !
I want to write data to a specific cell in my Excel. I have tried to use tFileExcelNamedCellOutput and tFileExcelReferencedCellOutput but to no avail.
The first componend (NamedCell) is failing with the following error

Exception in component tFileExcelNamedCellOutput_1 (test_sra)
java.lang.Exception: Cell with name:A1 is missing!

(I have tried with "$A$1" and even naming the cell but no luck either)

and the second component (ReferencedCell) don't fail but don't do anything either:

I guess I must do something wrong with the name of the cell but I don't find any documentation on these 2 components. Hope you could help !

Have a good day :)

[jlolling]

Sorry for the very late answer. A1 is not a name for a cell, this is address. A cell gets a name when you type something in in the top left input in excel (where usually the address appears).

[jlolling]

The second approach is correct.
Actually you have done everything correct. A little bit too much perhaps.
It is enough to provide the cell reference+sheet name/index or sheet name/index + row + column as target cell address.
your first attempt with tFileExcelNamedCellOutput cannot work because there is no named cell. Naming means you give a cell a unique (for the whole file) name.
I have just recreated your test and it works well. Could you please export (as Item Export!) your job and send it to me please. I am keen to find the problem. Please send it to me email address jan.lolling@gmail.com.
Thanks!

[jlolling]

I can confirm the issue with your provided test job. I am going to find the root clause and will fix it as soon as possible.

[jlolling]

Dear Severin, I have found the issue and solved it. There was more than one design flaw in the code and I have changed 2 things:

1. The calculation of the cell address was wrong
2. the error handling was fairly poor. If the values was not valid, the component simply does nothing
   I have just updated the components to version 13.4. Please give them a try and send me feedback if it works also for you.

Please use the Issue section next time if you find a bug.

Best regards
Jan Lolling

[lennelei]

Hi,
first of all, thank you for those components which are extremely useful when dealing with Excel files.
I'm unfortunately currently facing an issue while trying to open "large" Excel files : 20MB in size only but with 3 sheets of 65000 rows and 35 columns each (compression ration is approximately 10x) .
I only have a tFileExcelWorkbookOpen component in my job but it's crashing with a java.lang.OutOfMemoryError: GC overhead limit exceeded error with default memory settings.
Increasing Xmx to 2G doesn't really help : CPU reaches 100% and I tried to wait for 5 minutes but nothing happened (no error and the job never succeed).
I did try the memory saving mode without success.
For your information, standard component tFileExcelInput did manage to read the file by using the 'Event mode' (less memory consumption) in advanced settings ; however, I'd like to skip rows at the beginning of each sheets which is why I tried to use your components...
Any idea?

[jlolling]

100% CPU is quite normal while loading the file.
2G RAM could also not enough. I have examples of files which expects 6G RAM to open. Currently I do not see a better way, please increase the memory until it works. The Apache POI API has for sure more potential to be improved.
The memory saving mode works only if you write a large excel file, it does not have any impact on reading excel files - unfortunately.
My components cannot use the same way as the build-in components because of the architecture. My component must read the whole file. The build-in component can get rid of the data after reading the row because this component does not anything else.

[DavidUnderdown]

Hi Jan

Just switched to using your custom components due to the added flexibility. I'm also pretty new to Talend in general.

When I was using the built-in Talend Excel components I was building a job with two tFileInputExcel components, one going into a tMap as the main row and the other being used as a lookup. With your tFileExcelSheetInput replacing those two components (each with an associated tFileExcelWorkbookOpen as well of course), the first links to the tMap fine as the main row, but the second won't create a link as a lookup. What am I missing, or how can I work around this?

Thanks
David

[jlolling]

Hi David, it always a matter of how the trigger are made. I suggest we have private conversation because it is not a matter of this component. Please contact me directly: jan.lolling@gmail.com
And send me a screenshot of your current job design.

[tasfiahm]

I was using the tFileExcelSheetOutput(tFileExcelComponents-12.5.zip) component in Talend 7.2 recently I am trying to use the component in Talend Studio 8.0.1 using the tFileExcelComponents-13.9 component. So I imported the job in Talend 8. The job is running fine and writing the data into a .xlsm format. But when I open the output.xlsm file the int/numeric data field is coming with a comma Seperator. Is there any way to avoid the comma Seperator of the Integer/numeric field from the component setup.

[jlolling]

Yes of course. In the Column Config section (please refer to the manual) you can setup per column (especially useful for numbers and dates) the format.

[jlolling]

I also encourage you to use the latest release 13.9 - please take a look in the Release section of the repository Overview page - you will fin it in the middle at the right side.

[tasfiahm]

Thanks a lot for your lightning fast response. I have added the "####" pattern in the "Date or Number format.." column and it solve the issue. The version I have used in Talend 7.2 doesn't need the pattern but the Talend 8.0 may need the pattern for correct formating.