Apache POI - Different active cell is selected fro

2019-08-12 03:22发布

问题:

I'm trying to set the active cell of my multiple sheet workbook. However when I programmatically set it it still selects a different cell from the one I set. Example, I set the active cell to be "C1", but in the output the selected cell is "A500".

Here is my code to create the sheets:

public FullQuoteDTO CreateQuote(FullQuoteDTO dto){

    try {

        QuoteUserModel tempUser = GlobalMethods.ExtractCustomerDetails(dto.getUser().getCustomer());

        ClassLoader classLoader = getClass().getClassLoader();
        File source = new File(classLoader.getResource("excel/Original.xls").getFile());
        String fileName = tempUser.getFullName()+"_"+dto.getUser().getJob()+"_"+dto.getUser().getDate();
        File dest = File.createTempFile(fileName, ".xls");
        File destClient = File.createTempFile(fileName+"_client_copy", ".xls");

        //Copies the file contents from template to working file
        GlobalMethods.CopyFile(source, dest);
        GlobalMethods.CopyFile(source, destClient);

        //Prints the path
        System.out.println(dest);
        System.out.println(destClient);

        //Gets the newly created file
        FileInputStream excelFile = new FileInputStream(dest);
        FileInputStream excelFileClient = new FileInputStream(destClient);

        //Get the workbook
        Workbook workbook = new HSSFWorkbook(excelFile);
        Workbook workbookClient = new HSSFWorkbook(excelFileClient);

        //Generates editable workbook for consultants
        workbook = GenerateExcel(workbook,false, dto);

        //Saves the workbook for consultants
        FileOutputStream outputStream = new FileOutputStream(dest);
        workbook.write(outputStream);
        workbook.close();

        //Generates editable workbook for clients
        workbookClient = GenerateExcel(workbookClient,true, dto);

        //Set active cell
        HSSFSheet s = (HSSFSheet)workbookClient.getSheetAt(0);//Gets the first sheet in the workbook
        s.setActiveCell(new CellAddress(0,2));//Sets the active cell to be C1 #attempt 1
        //s.setActiveCell(new CellAddress("C1")); //Also tried this
        System.out.println(s.getActiveCell());//Prints out C1 but in the output show A500 as active cell

        //Saves the workbook for clients
        FileOutputStream outputStreamClient = new FileOutputStream(destClient);
        workbookClient.write(outputStreamClient);
        workbookClient.close();

        //Clean Up
        dest.delete();
        destClient.delete();

  } catch (Exception e) {
        e.printStackTrace();
        e.getMessage();
  }

}

Here is my method to Generate and set the cells:

private Workbook GenerateExcel(Workbook workbook, boolean readOnly, FullQuoteDTO dto) {

    //Run through all sheets
    int sheetNum = 0;
    int rowNum = 0;
    Sheet sheet = workbook.getSheetAt(0);
    for (FullSheetDTO row : dto.getSheets()) {
        if(row.getNo() == 1){
            sheet = workbook.getSheetAt(sheetNum);
            sheetNum++;

            //Header
            //Customer
            rowNum = 0;
            Row firstRow = sheet.getRow(rowNum++);
            Cell C1 = firstRow.getCell(2);
            if(dto.getUser().getCustomer()!= null) {
                C1.setCellValue(dto.getUser().getCustomer());
                if(readOnly) {
                    CellStyle cell = C1.getCellStyle();
                    cell.setLocked(true);
                    C1.setCellStyle(cell);
                }
            }

            //Job Name
            Row secondRow = sheet.getRow(rowNum++);
            Cell C2 = secondRow.getCell(2);
            if(dto.getUser().getJob()!= null) {
                C2.setCellValue(dto.getUser().getJob());
                if(readOnly) {
                    CellStyle cell = C2.getCellStyle();
                    cell.setLocked(true);
                    C2.setCellStyle(cell);
                }
            }

            //Board Header
            Row thirdRow = sheet.getRow(rowNum++);
            Cell C3 = thirdRow.getCell(2);
            if(row.getBoardType()!= null) {
                C3.setCellValue(row.getBoardType());
                if(readOnly) {
                    CellStyle cell = C3.getCellStyle();
                    cell.setLocked(true);
                    C3.setCellStyle(cell);
                }
            }
            Cell E3 = thirdRow.getCell(4);
            if(row.getBoardName()!= null) {
                E3.setCellValue(row.getBoardName());
                if(readOnly) {
                    CellStyle cell = E3.getCellStyle();
                    cell.setLocked(true);
                    E3.setCellStyle(cell);
                }
            }
            Cell J3 = thirdRow.getCell(9);
            if(row.getBoardThickness()!= null) {
                J3.setCellValue(row.getBoardThickness());
                if(readOnly) {
                    CellStyle cell = J3.getCellStyle();
                    cell.setLocked(true);
                    J3.setCellStyle(cell);
                }
            }

            //Edging Header
            Row forthRow = sheet.getRow(rowNum++);
            Cell C4 = forthRow.getCell(2);
            if(row.getEdgingCategory()!= null) {
                C4.setCellValue(row.getEdgingCategory());
                if(readOnly) {
                    CellStyle cell = C4.getCellStyle();
                    cell.setLocked(true);
                    C4.setCellStyle(cell);
                }
            }
            Cell E4 = forthRow.getCell(4);
            if(row.getEdgingName()!= null) {
                E4.setCellValue(row.getEdgingName());
                if(readOnly) {
                    CellStyle cell = E4.getCellStyle();
                    cell.setLocked(true);
                    E4.setCellStyle(cell);
                }
            }

            //Start Adding the rows
            rowNum = 6;
        }

        //Get the row to work with
        Row newRow = sheet.getRow(rowNum++);

        //Row Number
        if(row.getNo()!= null) {
            Cell No = newRow.getCell(0);
            No.setCellValue(row.getNo());
            if(readOnly) {
                CellStyle cell = No.getCellStyle();
                cell.setLocked(true);
                No.setCellStyle(cell);
            }
        }

        //Length
        if(row.getLength()!= null) {
            Cell Length = newRow.getCell(1);
            Length.setCellValue(row.getLength());
            if(readOnly) {
                CellStyle cell = Length.getCellStyle();
                cell.setLocked(true);
                Length.setCellStyle(cell);
            }
        }

        //Width
        if(row.getWidth()!= null) {
            Cell Width = newRow.getCell(2);
            Width.setCellValue(row.getWidth());
            if(readOnly) {
                CellStyle cell = Width.getCellStyle();
                cell.setLocked(true);
                Width.setCellStyle(cell);
            }
        }

        //Quantity
        if(row.getQuantity()!= null) {
            Cell Quantity = newRow.getCell(3);
            Quantity.setCellValue(row.getQuantity());
            if(readOnly) {
                CellStyle cell = Quantity.getCellStyle();
                cell.setLocked(true);
                Quantity.setCellStyle(cell);
            }
        }

        //EdgeL
        if(row.getEdgeL()!= null) {
            Cell EdgeL = newRow.getCell(4);
            EdgeL.setCellValue(row.getEdgeL());
            if(readOnly) {
                CellStyle cell = EdgeL.getCellStyle();
                cell.setLocked(true);
                EdgeL.setCellStyle(cell);
            }
        }

        //EdgeW
        if(row.getEdgeW()!= null) {
            Cell EdgeW = newRow.getCell(5);
            EdgeW.setCellValue(row.getEdgeW());
            if(readOnly) {
                CellStyle cell = EdgeW.getCellStyle();
                cell.setLocked(true);
                EdgeW.setCellStyle(cell);
            }
        }

        //HolesL
        if(row.getHoleL()!= null) {
            Cell HolesL = newRow.getCell(6);
            HolesL.setCellValue(row.getHoleL());
            if(readOnly) {
                CellStyle cell = HolesL.getCellStyle();
                cell.setLocked(true);
                HolesL.setCellStyle(cell);
            }
        }

        //HoleW
        if(row.getHoleR()!= null) {
            Cell HolesW = newRow.getCell(7);
            HolesW.setCellValue(row.getHoleR());
            if(readOnly) {
                CellStyle cell = HolesW.getCellStyle();
                cell.setLocked(true);
                HolesW.setCellStyle(cell);
            }
        }

        //Edging Category
        if(row.getEdgingCategory()!= null) {
            Cell EdgeCat = newRow.getCell(8);
            EdgeCat.setCellValue(row.getEdgingCategory());
            if(readOnly) {
                CellStyle cell = EdgeCat.getCellStyle();
                cell.setLocked(true);
                EdgeCat.setCellStyle(cell);
            }
        }

        //Edging Name
        if(row.getEdgingName()!= null) {
            Cell EdgeName = newRow.getCell(9);
            EdgeName.setCellValue(row.getEdgingName());
            if(readOnly) {
                CellStyle cell = EdgeName.getCellStyle();
                cell.setLocked(true);
                EdgeName.setCellStyle(cell);
            }
        }

        //Other
        if(row.getOther()!= null) {
            Cell Other = newRow.getCell(10);
            Other.setCellValue(row.getOther());
            if(readOnly) {
                CellStyle cell = Other.getCellStyle();
                cell.setLocked(true);
                Other.setCellStyle(cell);
            }
        }

    }

    return workbook;
}

Here is the output of the client file once it is written and saved:

Note the selected cell is A500 and not C1 as specified.

This only happens on my Client sheet. The difference between the two sheets is that the client cells are read only and the consultant sheet is editable.

Any help will be appreciated.

Thanks in advance.

Update 1:

This is the Console output indicating that the active cell is indeed C1:

I also tried not making C1 read-only/locked, but that also failed.