Why only the last value is set during copy of excel content?

Question

I'm trying to copy the content of an Excel file to another one. But only the first row.

So, I wrote this code:

// Step 1 : Locate path and file of input excel
File inputFile = new File("path.xlsx")
FileInputStream fis = new FileInputStream(inputFile)
XSSFWorkbook inputWorkbook = new XSSFWorkbook(fis)
int inputSheetCount = inputWorkbook.getNumberOfSheets()

// Step 2 : Locate path and file of output excel
File outputFile = new File("path2.xlsx")
FileOutputStream fos = new FileOutputStream(outputFile)

// Step 3 : Creating workbook for output excel file
XSSFWorkbook outputWorkbook = new XSSFWorkbook()

// Step 4 : Creating sheets with the same name as appearing in input file
for(int i = 0; i < inputSheetCount; i++) {
    XSSFSheet inputSheet = inputWorkbook.getSheetAt(i)
    String inputSheetName = inputWorkbook.getSheetName(i)
    XSSFSheet outputSheet = outputWorkbook.createSheet(inputSheetName)

    int numberOfColumns = inputSheet.getRow(0).getPhysicalNumberOfCells()

    int columnIndex = 0
    while (columnIndex < numberOfColumns && inputSheet.getRow(0).getCell(columnIndex).stringCellValue != '') {
        // Step 5 : Creating new Row, Cell and Input value in the newly created sheet
        outputSheet.createRow(0).createCell(columnIndex).setCellValue(inputSheet.getRow(0).getCell(columnIndex).stringCellValue)
        columnIndex++
    }
}

// Step 6 : Write all the sheets in the new Workbook using FileOutStream Object
outputWorkbook.write(fos)
// Step 7 : At the end of the Program close the FileOutputStream object
fos.close()

However, I faced to a strange problem. When I check the output file, only the last value is set, not others...

Input file

     A  |  B   |    C
1 | Key  Value   Comment 
2 | 
3 |

Output file expected result

     A  |  B   |    C
1 | Key  Value   Comment 
2 | 
3 |

Output file actual result

     A  |  B   |    C
1 |              Comment 
2 | 
3 |

Someone has any idea? Thanks a lot!

`outputSheet.createRow(0)` in the while loop is the issue. It deletes the row and creates a new one every iteration and thus deleting already created cells in that row — XtremeBaumer, Jun 13 '19 at 08:49

score 0 · Answer 1 · answered Jun 13 '19 at 08:54

The while goes in loop. I suggest to put another for something like this:

    for(int j=0;j<numberOfColumns ;j++){
    if(inputSheet.getRow(0).getCell(j).stringCellValue != ''){
            // Step 5 : Creating new Row, Cell and Input value in the newly created sheet
            outputSheet.createRow(0).createCell(j).setCellValue(inputSheet.getRow(0).getCell(j).stringCellValue)

        }
}

Why only the last value is set during copy of excel content?

1 Answers1