[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 ?

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 jobread templates and write intoreuse formattingswrite formulasand 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

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.

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 jobread templates and write intoreuse formattingswrite formulasand 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

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.

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? ThanksShuba

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.

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 again2. 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!ThanksShubascreenshots.zip.zip

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?

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.ThanksShuba

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. Thanksshuba

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.

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

This is the actual error I am getting2016-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|1I used an open and save in my previous job that writes this file.

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

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.

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