One Star

[resolved] Parse merged cell in Excel

Hello,
I am new to Talend. I would like to feed a tMap with an Excel Sheet which has vertical merged cells. I wonder how to give the same value to all the veritcal merged cells so that the rows can have the information ? Is it possible to do so in the schema ?
Thank you for your help and this great solution.
System : windows, java, talend 4.2.2
7 REPLIES
One Star

Re: [resolved] Parse merged cell in Excel

Through the reading of tFileInputExcel_begin.javajet, I understand the row are read through getRow (same for the cells with getCell).
1/ Where can I see the code of these methods (getRow and getCell) ?
2/ Do you think it would be possible to add somewhere the Worksheet's method get_merged_areas() (of ParseExcel Worksheet) to split the merged cells into cells of same value.

Example :
Vertical Merged cells : => Splitted cells with the same value :
------------ ------------
| value1 | | value1 |
| | ------------
| | => | value1 |
------------ ------------
| value1 |
------------
Thanks for your help.

PS : I don't know why the links don't show up. So here they are :
link 1 : http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel/lib/Spreadsheet/ParseExcel/Worksheet.pm#get...
link 2 : http://search.cpan.org/~jmcnamara/Spreadsheet-ParseExcel/lib/Spreadsheet/ParseExcel.pm#Worksheet
Community Manager

Re: [resolved] Parse merged cell in Excel

Hi
What does the data look like? Can you show us an example of file? what are you desired result?
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Parse merged cell in Excel

Hello Shong,
Thanks for you help.
Here is an example :
One Star

Re: [resolved] Parse merged cell in Excel

I just want to transform an Excel sheet with merged cells into an Excel Sheet with no merged cells so that all cells have the information. See uploaded images. Do you have an idea to do this ?
Thanks for your answer.
One Star mpa
One Star

Re: [resolved] Parse merged cell in Excel

Remiroc,
Could you mail me the excel file? (i'm too lazy to make it hehe)
Then I will have a look an hopefully find a solution for your problem.
*edit*
Nvm I made one. I'll have a look
*edit*
Regards
One Star mpa
One Star

Re: [resolved] Parse merged cell in Excel

Remiroc,
I found a "solution" that is working. It might not be the easiest one. Maybe Shong has a better solution for you.
Steps:
1. Add a context variable. Call it "Merged Cells" or something.
2. In the tMap create a new variable with following expression (where columnA is your vertical merged column): row1.ColumnA != null ? (context.MergedColumn = row1.ColumnA) : ""
3. For all columns make a 1:1 to the tMap output. So just drag all column to the output.
4. Add a tFlowtoiterate.
5. add a tFixedflowinput. (iterate connection) Here you assign the columns with a value.
Refer to my screenshots. ( I don't know how to add the screens so just click the links I guess)
http://imageshack.us/photo/my-images/837/excel1.png
http://imageshack.us/photo/my-images/26/excel2x.png
http://imageshack.us/photo/my-images/535/excel3.png
http://imageshack.us/photo/my-images/143/excel4.png

Regards,
Mario De Pauw
One Star

Re: [resolved] Parse merged cell in Excel

Hello mpa,
It is working. Thank you very much !!
R
PS : I have spent the day in the code of tInputFileExcel with jxl MergedCells. Finally, I can keep on => you rock !!