0

I try to copy cell values from a sheet to a sheet of another file with Apache POI. Both XLSM files have the same columns, but in different order. I initialized first file as follows:

File file = new File(GlobalVariables.XLSM);
FileInputStream inputStream = new FileInputStream(file);
XSSFWorkbook workbook = XSSFWorkbookFactory.createWorkbook(inputStream);
XSSFSheet sheet = workbook.getSheetAt(0);
XSSFRow row = sheet.getRow(recordcount+4);
Cell cell;

After that I set some cells in this file:

cell = row.getCell(0);
cell.setCellValue(recordcount+1);
cell = row.getCell(2);
originalDir = originalDir.substring(originalDir.indexOf('\\', 2));
cell.setCellValue(originalDir);

Second file, from which I need to copy values to first one:

File prevfile = new File(PrevResXLS);
FileInputStream prevInputStream = new FileInputStream(prevfile);
XSSFWorkbook prevworkbook = XSSFWorkbookFactory.createWorkbook(prevInputStream);
XSSFSheet prevsheet = prevworkbook.getSheetAt(0);
XSSFRow prevrow = prevsheet.getRow(0);
Cell prevCell;

There is a flag, which is set to 1 in this case, so condition is met. I am looking for previous values, which belongs to the same file, so needs to be copied to the first one.

if(GlobalVariables.PrevRES != 0){
    File prevfile = new File(PrevResXLS);
    FileInputStream prevInputStream = new FileInputStream(prevfile);
    XSSFWorkbook prevworkbook = XSSFWorkbookFactory.createWorkbook(prevInputStream);
    XSSFSheet prevsheet = prevworkbook.getSheetAt(0);
    XSSFRow prevrow = prevsheet.getRow(0);
    Cell prevCell;

    try {
        for (Row r : prevsheet) {
            if (r.getRowNum() == 0) continue;
            if (r.getCell(1).toString().equals(trans_filename)) {
                cell = prevrow.getCell(21);
                cell.setCellValue(r.getCell(21 - 13).toString());
                cell = prevrow.getCell(22);
                cell.setCellValue(r.getCell(22 - 13).toString());
                cell = prevrow.getCell(23);
                cell.setCellValue(r.getCell(23 - 13).toString());
                cell = prevrow.getCell(24);
                cell.setCellValue(r.getCell(24 - 13).toString());
                cell = prevrow.getCell(25);
                cell.setCellValue(r.getCell(25 - 13).toString());
                cell = prevrow.getCell(26);
                cell.setCellValue(r.getCell(26 - 13).toString());
                cell = prevrow.getCell(27);
                cell.setCellValue(r.getCell(27 - 13).toString());
                cell = prevrow.getCell(28);
                cell.setCellValue(r.getCell(28 - 13).toString());
                cell = prevrow.getCell(29);
                cell.setCellValue(r.getCell(29 - 13).toString());
                cell = prevrow.getCell(30);
                cell.setCellValue(r.getCell(30 - 13).toString());
                cell = prevrow.getCell(31);
                cell.setCellValue(r.getCell(31 - 13).toString());
                cell = prevrow.getCell(32);
                cell.setCellValue(r.getCell(32 - 13).toString());
                cell = prevrow.getCell(33);
                cell.setCellValue(r.getCell(33 - 13).toString());
            }
        }
        prevInputStream.close();
    } catch (NullPointerException e) {
        System.out.print("NPE");
    }
} 
    FileOutputStream fileOut=new FileOutputStream(GlobalVariables.XLSM);
    workbook.write(fileOut);
    fileOut.close();

Execution is successful, file is modified with new values, but there isn't copied anything to first file.

plaidshirt
  • 5,189
  • 19
  • 91
  • 181
  • 1
    Show the code as whole. Currently I'd guess, that you are either iterating the wrong sheet `for (Row r : prevsheet)` or you access the wrong row inside the loop `cell = prevrow.getCell(32);` – XtremeBaumer Sep 05 '19 at 14:20
  • @XtremeBaumer : That is why I use variables with prefixes. `cell` variable belongs to the first file. – plaidshirt Sep 05 '19 at 14:29
  • Well, `cell = prevrow.getCell(21);` assignes a cell from `prevworkbook` and not from `workbook`. Currently you update `prevworkbook` only. And you only ever update the first row with the same values all the time – XtremeBaumer Sep 05 '19 at 14:48
  • @XtremeBaumer : Thanks, I fixed it, but first 2 and last 4 columns remains still empty after execution. – plaidshirt Sep 06 '19 at 12:10

0 Answers0