Pentaho : How to split single Excel file to multiple excel sheet output

Question

I have a list of employee details. I want to split each employee detail in separate Excel sheet. I tried a lot but I get only separate Excel files alone, not all the sheets in a single file by using Pentaho kettle.

Eg:

Raja    22 developer  25000
ravi    23 tester      2000
karthik 24 designer    4000 
Mani    28 developer  45000

In that each employee details will need separate sheets in a single excel file. Already, I worked with "MS excel writer" but it did not work.

EDIT

Thanks for your valuable reply,its really clear and more useful. :-) But i need one more detail from you,if i added age,skill ,salary columns into the get variables, after run the job ,i didn't get the values of these three fields only their column names shown in every sheet. I need to include their column values in each sheet

example : sheet 1 : (raja)

Name age skill salary Raja 22 developer 25000

sheet 2 :(ravi) Name age skill salary ravi 23 tester 2000

Like that i need to generate each sheets,I hope you get my point. Can you please help me how to i generate that.

Answer 1

You need to copy the rows (employee names) into memory and then loop it across the excel file to generate multiple sheets with employee names. I have uploaded the codes in this location. You can view it.

First of all, i took an excel input and used "copy rows to result" step to load the data to the memory.

In the Second Step, loop all the data in the memory and write it across the excel file. You can loop the file by enabling the 'copy previous results to parameter' and 'execute for every row' will run for every single row. Check the image below:

Finally when writing the files in an excel file (using Excel Writer Step) make sure that every time a row is coming from prev. step is getting inserted into the same file. Check the below image for this.

I have uploaded a sample code in drive. Hope it helps :)