Problem description & analysis

There are multiple Excel files (worksheets) of same structure in an xlsx directory. Each worksheet has multiple sheets, as shown below:

book1.xlsx—sheet1:

book1.xlsx—sheet2:

book2.xlsx—sheet1:

book2.xlsx—sheet2:

We are trying to combine these worksheets into one, as shown below:

result.xlsx—sheet1

result.xlsx—sheet2

Solution

Write the following script p1.dfx in esProc:

Explanation:

A1  Read names of all files in xlsx path.

A2 Loop through A1.

B2  Return the three attributes of sheet name (stname), number of rows (nrows), and number of columns (ncols) of the worksheet corresponding to the current A2’s file name.

B3  Loop through B2.

C3  Retrieve the content of sheet in B3.stname corresponding to A2’s file name.

C4, D4  If A2’s loop number is 1, output C3’s content to the sheet of B3.stname in result.xlsx. @t option enables outputting column headers, and @a option enables an append type output.

C5, D5  If A2’s loop number isn’t 1, output C3’s content to the sheet of B3.stname in result.xlsx. @a option enables an append type output.

Read How to Call an SPL Script in Java to learn about the method of integrating a SPL script with a Java program.

Q & A Collection

https://stackoverflow.com/questions/61788329/skipping-the-first-row-in-subsequent-worksheets-using-java-poi-that-are-being-co