How to read same cell values from every sheet in a workbook

[Anonymous]

Hi there,
I would like to read particular cell values (same location) from every single worksheet in a particular workbook. So far I can only read particular cell values in just one sheet using the technique described here:  Extracting data from specific Excel cells. For some reason (I still have an issue pending on this forum about it) I cannot cycle through all the sheets having selected 'All Sheets' in the tFileInputExcel component.
Now I'm trying a second technique setting up my process as shown in the diagram attached. The question I have are:
1. How can I get the cell value (using Excel cell reference such as A1) from the sheet in the workbook that I am referencing so I can actually output that value in tLogRow
2. Is it possible that I can use this technique to get Excel cell values from all the sheets in the workbook
Thanks,
Facoda

[Anonymous]

For sure you can: your design was nearly ok 😉
Open the schema of the tFileExcelReferencedCellInput and copy all IN_ columns to the input schema.

Iterate through all sheets (or filter them) and use the return variable CURREN_SHEET_NAME the setup the sheet.

[Anonymous]

Hi Jlolling,
Thanks for your assistance. I have setup my job as recommended but I'm having problems:

Exception in component tFileExcelReferencedCellInput_1
java.lang.Exception: Cell does not exists: ref=null sheet=1 row=2 col=B
	at media_spend_harmonisation.excelcell_0_1.ExcelCell.tFileExcelSheetList_1Process(ExcelCell.java:1790)
	at media_spend_harmonisation.excelcell_0_1.ExcelCell.tFileExcelWorkbookOpen_1Process(ExcelCell.java:427)
	at media_spend_harmonisation.excelcell_0_1.ExcelCell.runJobInTOS(ExcelCell.java:2480)
	at media_spend_harmonisation.excelcell_0_1.ExcelCell.main(ExcelCell.java:2339)

I cannot understand this. Even when I put the index of the sheet as one still same problem i.e. the cell does not exist clearly the return variable for sheet index is correct.
This is confusing.
Thanks,
Facoda

[Anonymous]

I have made only an example. You have to address the cell you need (for sure another cell than my example row 2 and column B).
Perhaps you post a screenshot of your excel file to show me where is your content?

[Anonymous]

There is definitely a value in Sheet1 as can be seen in the diagram below:
The tLogRow output is strange though for sheet index. It's 0 as can be seen. Shouldn't it be returning 1 from the return variable?

[Anonymous]

The sheet index is 0-based (perhaps a bit confusing because of the default name) but technically the first sheet has the index 0.

[Anonymous]

Hi Jlolling,
I've tried again referencing a new workbook and the same setup that wasn't working for me before now works like magic.
I really don't know what to say.
But thanks for your help.
Facoda
-PS Also using this job could I get multiple cell values per sheet or a range per sheet? I want to get more than just one cell value

[Anonymous]

Of course you can. It up to you to address cells in the same or another sheet. In my example one row from the tFixFlowInput addresses one cell in a sheet. 

[Anonymous]

Sorry to be pestering here. I'm not sure it's possible to reference more than one cell per sheet. The outputs are single cells only. For example if I want to reference the following ranges in a single sheet in Excel:
1. D7:E8
2. H8:H10
I couldn't do it unless I was using multiple tFileExcelReferenceCellInput component for each cell value?
Thanks,
Facoda

[Anonymous]

I guess you see it more complex as it is actually.
Every input row of the component addresses one cell and it does not matter in which sheet it is.
If you have 3 sheets with 4 cells you have to define 4 rows in the tFixedFlowInput and because of the iteration through the tFileExcelSheetList you get in the end 12 rows.
The component unfortunately does not support a cell range. To digger the values of cell ranges you should use the tFileExcelSheetInput.