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.