One Star

[resolved] Writing to two sheets in the same excel file from one job

Hi all,
I have a problem writing to two different sheets in the same excel file from the same job, below is the scenario :
tFileInputExcel - > tMap -> tReplicate .. replicate1 .. -> tFileOuputExcel(sheet1) .. replicate2 .. -> tMap -> tFileOuputExcel(sheet2)
tFileOuputExcel is the same file.
If I change one of the tFileOutputExcel file name to a different file name, then both files are created with the respective sheet names(sheet1, sheet2), however they are in different files which does not suite my purpose.
Is this a bug in Talend ?
1 ACCEPTED SOLUTION

Accepted Solutions
Seventeen Stars

Re: [resolved] Writing to two sheets in the same excel file from one job

This is one of the reason I build my own Excel components to:
write multiple sheets in one job
read templates and write into
reuse formattings
write formulas
and a lot more...
Take a look at the tFileExcelSheetOutput component (in conjunction with tFileExcelWorkbookOpen and tFileExcelWorkbookSave).
These components are available at Talend Exchange (with documentation)
http://www.talendforge.org/exchange/index.php and search for tFileExcel
 
22 REPLIES
Seven Stars

Re: [resolved] Writing to two sheets in the same excel file from one job

Hello,
You can write to multiple sheets in the same spreadsheet, but they need to be done in different subjobs. From what you describe, you could take the tMap output and push it into a tHashOutput (instead of the tFileOutputExcel), then in another subjob have a tHashInput go to the second sheet in the spreadsheet.
Hope that helps.
Seventeen Stars

Re: [resolved] Writing to two sheets in the same excel file from one job

This is one of the reason I build my own Excel components to:
write multiple sheets in one job
read templates and write into
reuse formattings
write formulas
and a lot more...
Take a look at the tFileExcelSheetOutput component (in conjunction with tFileExcelWorkbookOpen and tFileExcelWorkbookSave).
These components are available at Talend Exchange (with documentation)
http://www.talendforge.org/exchange/index.php and search for tFileExcel
 
One Star

Re: [resolved] Writing to two sheets in the same excel file from one job

Thank you for the responses. I would prefer to do it in the same job as it is already a subjob. I will try the tFileExcelSheetOutput combo first ! Let you folks know how it goes shortly.
Perfect !! Thanks folks, used the exchange fileexcel components and they work much better.
One Star

Re: [resolved] Writing to two sheets in the same excel file from one job

Hi Jan,
I am currently using your excel components to write multiple sheets to the same spreadsheet. I have subjobs each writing to one sheet but I am usig a tparallelize to run all the subjobs. At then end of the job, I dont see all the sheets. Its not consistent. Even though I'm saving the workbook and opening in every subjob it doesnt seem to save some sheets. do you have any suggestions?
Thanks
Shuba
Seventeen Stars

Re: [resolved] Writing to two sheets in the same excel file from one job

Hi, you cannot write into one excel workbook with multiple threads. My component does not recognise such situation yet but it impossible because you write actually into one huge XML file and the Apache POI API also does not allow multi threading - it is NOT thread save!
I cannot predict what will happen if you try this, but to get an inkonsistent document is for sure a worst case scenario.
One Star

Re: [resolved] Writing to two sheets in the same excel file from one job

Thanks for your reply. That's correct. I get inconsistent document every time. I guess have to end up adding the sheets sequentially and think of other ways to speedup.
Seventeen Stars

Re: [resolved] Writing to two sheets in the same excel file from one job

Sorry for not having a better solution. If you have to create multiple documents, you can run in parallel these processing but not within one document.
One Star

Re: [resolved] Writing to two sheets in the same excel file from one job

Hi Jan,
I took your suggestion and I am using your custom components. tFileExcelWorkbookOpen, Save and tfileExcelSheetOutput. It was working great as long as I was creating .xls files. Yesterday we had a requirement to switch to .xlsx instead. and when I changed it in the jobs, it started giving me several errors.
1.
# A fatal error has been detected by the Java Runtime Environment:
#  SIGBUS (0x7) at pc=0x00007f542f9b3732, pid=34686, tid=139998362167040
# JRE version: Java(TM) SE Runtime Environment (7.0_97-b02) (build 1.7.0_97-b02)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (24.95-b01 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C    newEntry+0x62
# Failed to write core dump. Core dumps have been disabled. To enable core dumping, try "ulimit -c unlimited" before starting Java again
2. It started throwing error in my second job that the file written and saved in the first job was not found and I could not see the file in the server either.
I have attached some screenshots of my jobs.  I have a set of jobs running one after another appending sheets to the same workbook and this works great with .xls. Not sure why .xlsx would have such issues.
Appreciate your help a lot!
Thanks
Shuba
screenshots.zip.zip
Seventeen Stars

Re: [resolved] Writing to two sheets in the same excel file from one job

Whow ... this error should not happen. The problem here is not your job or my component, the problem here is an error in the JVM.
It is a error in the JVM using the zip library. I can tell you for sure these components works in a huge number of projects and it is not a problems caused by the component, instead I would say it happens because of the typical memory expensive kind of jobs working with excel files.
Can you reproduce this error? 
I suggest install another java runtime with a different version. Do you use the Oracle JDK or OpenJDK?
One Star

Re: [resolved] Writing to two sheets in the same excel file from one job

Thanks so much for the quick response again. They just upgraded the java version from jdk1.8.0_74 to jdk1.7.0_97. We use OpenJDK.  But I am having this problem in both the versions in 2 different servers.
Thanks
Shuba
Seventeen Stars

Re: [resolved] Writing to two sheets in the same excel file from one job

Can you just try to install Oracle JDK? - simply unpack and set it as JAVA_HOME for your job.
One Star

Re: [resolved] Writing to two sheets in the same excel file from one job

Sorry my bad, just figured we're using Oracle JDK not OpenJDK.
One Star

Re: [resolved] Writing to two sheets in the same excel file from one job

Jan, Do you think that the second issue is also related to the Java settings?
2. I have sequence of jobs. The first job creates the workbook, create a sheet and then saves it. The second job should open the same workbook and append another sheet. But with .xlsx, I am getting the error that the file is not found. 
Thanks
shuba
Seventeen Stars

Re: [resolved] Writing to two sheets in the same excel file from one job

File not found means file not found.... It is sometimes a timing problem but my API checks the existence of the file before it tries to open it. This helps to find problems much better. Do you use network attached storage? Sometimes NAS file systems have a synchronizing delay.
One Star

Re: [resolved] Writing to two sheets in the same excel file from one job

This is the actual error I am getting
2016-02-11 23:33:13|lQqABm|20160211233302_3NxWK|yPGLrN|GRP_ETL|jo_GRP_RPT_0003_generateServiceSummaryXls_J1|Default|6|Java Exception|tFileExcelWorkbookOpen_1|java.lang.IllegalStateException:Zip File is closed|1
I used an open and save in my previous job that writes this file.
One Star

Re: [resolved] Writing to two sheets in the same excel file from one job

Hi Jan,
Any idea why I'm getting this error? Appreciate your help on this..
Thanks
Shuba
Seventeen Stars

Re: [resolved] Writing to two sheets in the same excel file from one job

Without knowing your job design, I have no idea. Could you please post a screenshot of your job design?
One Star

Re: [resolved] Writing to two sheets in the same excel file from one job

screenshot… .zip
Please see my screenshots in the attached zip file.
Thanks!
One Star

Re: [resolved] Writing to two sheets in the same excel file from one job

Screesnshots attached.
One Star

Re: [resolved] Writing to two sheets in the same excel file from one job

All that I am doing is creating an empty .xlsx workbook in the first subjob, writing a sheet in it and save the workbook using the custom component and in the second subjob if I try to open the same workbook, it thros an error that the workbook is closed. cannot open file.
One Star

Re: [resolved] Writing to two sheets in the same excel file from one job

This is the java exception of the error:
2016-02-16 08:27:12|8ANK5U|20160216082700_VwPOp|CafRzf|GRP_ETL|Copy_of_jo_GRP_RPT_0003_generateInvoicesByEntityTypeXls_J_Good|Default|6|Java Exception|tFileExcelWorkbookOpen_1|java.lang.IllegalStateException:Zip File is closed|1
One Star

Re: [resolved] Writing to two sheets in the same excel file from one job

Hi Jan,
Can you please comment on this? Thanks!
I have also created a new post for this issue.
https://www.talendforge.org/forum/viewtopic.php?pid=178110#p178110