[resolved] How to iterate over the sheets of an Excel file

[Anonymous]

I have many excel files, each containing a varying number of sheets ; all sheets have the same schema. I want to iterate over the sheets of the various files. I can iterate over the files thanks to tFileList.
I tried to iterate over the sheets with a tJavaFlex component, but apparently the tExcelInput component reads all the data during the begin phase. So I cannot compute the sheet name during the main section of the tJavaFlex...
Is there some way to do that ?
Thanks in advance

[Anonymous · Accepted solution]

I'm going to give a solution for Perl project users as this topic interest me 🙂
In tPerlFlex, the begin part is:

use Spreadsheet::ParseExcel;
my $book = Spreadsheet::ParseExcel::Workbook->Parse(
    $_globals{tFileList_1}{CURRENT_FILEPATH}
);
my @worksheets = map { $_->{Name} } @{$book->{Worksheet}};
foreach (@worksheets) {
    $_globals{CURRENT_WORKSHEET} = $_;

no main, and the end is:

}

My output is:

Starting job topic747 at 18:06 23/05/2007.
file:  file1.xls   sheet:     sheet1   firstname:   Pierrick   lastname:    Le Gall
file:  file1.xls   sheet:     sheet1   firstname:   Philippe   lastname:      Lobet
file:  file1.xls   sheet:     sheet2   firstname:    Richard   lastname:   Billerey
file:  file1.xls   sheet:     sheet2   firstname:    Olivier   lastname:    Carbone
file:  file1.xls   sheet:     sheet3   firstname:       Axel   lastname:    Maumont
file:  file1.xls   sheet:     sheet3   firstname:   Stephane   lastname:     Mallet
file:  file2.xls   sheet:     sheet1   firstname:    Michael   lastname:       Hirt
file:  file2.xls   sheet:     sheet1   firstname:    Nicolas   lastname:   Rousseau
Job topic747 ended at 18:06 23/05/2007. 

[Anonymous]

Thanks a lot; it worked fine.
In Java, the Start section is

	
   final jxl.WorkbookSettings workbookSettings_tSheetList_1 = new jxl.WorkbookSettings();
   workbookSettings_tSheetList_1.setEncoding("ISO-8859-15");
   final jxl.Workbook workbook_tSheetList_1 = jxl.Workbook.getWorkbook(
      new java.io.BufferedInputStream(new java.io.FileInputStream(
         ((String) globalMap.get("tFileList_1_CURRENT_FILEPATH")))),
         workbookSettings_tSheetList_1);
   java.lang.String[] sheetNames = workbook_tSheetList_1.getSheetNames();
   for (int ixx=0; ixx<sheetNames.length; ixx++) {
      String theSheet = sheetNames;
      // I don't want these sheets
      if ("Resultats_2006".equals(theSheet)) continue;
      
      globalMap.put("CURRENT_SHEET", theSheet);

There is no Main section. The End section contains only "}".

[_AnonymousUser]

This Java code works very well, thank you very much!

[Anonymous]

Just what I was after ta.
I had to tLibraryLoad jxl.jar. (I think this is because I'm not using a tFileInputExcel yet and therefore jxl was not loaded).
"Exception in thread "main" java.lang.NoClassDefFoundError: jxl/WorkbookSettings at *******.summary_0_1.Summary.tFileList_1Process(Summary.java:671)"
I also added "WBS_1.setSuppressWarnings(true);" to the code as I was getting, loads of warnings;
"Warning: Usage of a local non-builtin name
Warning: Property storage name for 5 is empty - setting to Root Entry"
Cheers

[Anonymous]

Hi moinerus,
On componentOk link would not help many times, if the further tasks are dependent on the status of completion of previous task. If the library is responsible for further task execution, then convert link to OnSubjobOk and then check once again..
Thanks
Vaibhav

[Anonymous]

Vaibhav as the jxl Lib is build in and loaded with other components, I'm happy its going to load.
Good point though, I do usually use OnSubjobOK must have miss clicked.
Cheers Andy

[Anonymous]

hi all,
difference between onComponentOk and OnSubjobOk is due to how Talend process the 3 different part of component (start,main,end).
for the start part Talend begin to check each component starting by the last one (usually a write - output component).
Then launch the main part (the flow)
And finish by closing each end part of component until the end.
a component could be finished before whole subjob.
you can easily view those 3 part in tJavaFlex component that is a pattern (squeletor component ( without ui).
for a subjob with a single component both (component or subjob ok) are the same
regards
laurent

[Anonymous]

Hi Laurent,
For single components like tFTPConnection, tFTPget, tFTPut, tDBconnection,tLibraryLoad components, whether both (onSubJobOk and onComponentOk) are same?
If it is the same, then it defies the sole purpose of component usage and their link creation... We must raise a change request to Talend...
tPreJob and tPostJob components does not have onSubJobOk, If and Main links, it only has onComponentOk...
Can you pl explain to me about this?
Vaibhav

[Anonymous]

yes it's the same if a subjob has only one component.
see prejob & postJOb has a graphical starting&ending point telling you : do something before anything else (init part) for the tPreJob and close properly job for tPostJob (close connection, clear buffer,etc).

regards
laurent