I am trying to read a row from xlsx file and then remove the row. When I run my code once , it is perfectly fine. POI can read the row and remove the row. I can open the file using Excel. So far so good.
When I re-run my code once again (second time), POI can read the row and remove the row, but when I open the file using excel I see
I followed all suggestions in here , here , and here , but my problem still doesn't get solved.
This is my codes
public static void main(String[] args) throws InvalidFormatException, IOException {
new ExcelReader().readExcel(PATH_TO_EXCEL, Constants.sheetName);
}
private void readExcel(String pathToExcel , String sheetName) throws InvalidFormatException {
Sheet sheet = null;
try {
FileInputStream fileInput = new FileInputStream(new File(pathToExcel));
Workbook workbook = WorkbookFactory.create(fileInput);
Iterator<Sheet> sheetItr = workbook.sheetIterator();
while (sheetItr.hasNext()) {
sheet = sheetItr.next();
// For Users sheet create List of objects
if (sheet.getSheetName().equals(sheetName)) {
readExcelSheet(sheet);
} else {
// For other sheet just print the cell values
System.out.println("Sheet not available");
}
}
//Now it's time to delete the row that we have just read
removeAndShiftRow(PATH_TO_EXCEL,1,true, sheet);
fileInput.close();
FileOutputStream outFile = new FileOutputStream(new File(pathToExcel));
workbook.write(outFile);
outFile.flush();
outFile.close();
workbook.close();
} catch (EncryptedDocumentException | IOException | ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void readExcelSheet(Sheet sheet) throws ParseException, IOException {
System.out.println("Starting to read sheet- " + sheet.getSheetName());
Iterator<Row> rowItr = sheet.iterator();
List<User> userList = new ArrayList<>();
DataFormatter formatter = new DataFormatter();
// Iterate each row in the sheet
while (rowItr.hasNext()) {
User user = new User();
Row row = rowItr.next();
// First row is header so skip it
if (row.getRowNum() == 0) {
continue;
} else if (row.getCell(1) == null || row.getCell(1).getCellTypeEnum().equals(CellType.BLANK)) {
continue;
} else if (formatter.formatCellValue(row.getCell(0)).equals("USED")) {
continue;
} else {
Iterator<Cell> cellItr = row.cellIterator();
// Iterate each cell in a row
while (cellItr.hasNext()) {
Cell cell = cellItr.next();
int index = cell.getColumnIndex();
switch (index) {
case 0:
System.out.println(formatter.formatCellValue(cell));
user.setIDNumber(formatter.formatCellValue(cell));
//sheet.createRow(row.getRowNum()).createCell(0).setCellValue("USED");
break;
case 1:
System.out.println(cell.getRichStringCellValue());
user.setIDNumberShort(formatter.formatCellValue(cell));
cell.setCellValue("used");
break;
case 2:
System.out.println(formatter.formatCellValue(cell));
user.setID(formatter.formatCellValue(cell));
cell.setCellValue("used");
break;
case 3:
System.out.println(formatter.formatCellValue(cell));
user.setEmail(formatter.formatCellValue(cell));
cell.setCellValue("used");
break;
case 4:
System.out.println(formatter.formatCellValue(cell));
user.setFirstName(formatter.formatCellValue(cell));
cell.setCellValue("used");
break;
case 5:
System.out.println(formatter.formatCellValue(cell));
user.setLastName(formatter.formatCellValue(cell));
cell.setCellValue("used");
break;
case 6:
System.out.println(formatter.formatCellValue(cell));
user.setGender(formatter.formatCellValue(cell));
cell.setCellValue("used");
break;
case 7:
System.out.println(formatter.formatCellValue(cell));
user.setHomePhone(formatter.formatCellValue(cell));
cell.setCellValue("used");
break;
case 8:
System.out.println(formatter.formatCellValue(cell));
user.setMobile(formatter.formatCellValue(cell));
cell.setCellValue("used");
break;
case 9:
System.out.println(formatter.formatCellValue(cell));
user.setDOB(formatter.formatCellValue(cell));
cell.setCellValue("used");
break;
case 10:
System.out.println(formatter.formatCellValue(cell));
user.setAddress(formatter.formatCellValue(cell));
cell.setCellValue("used");
break;
}
}
userList.add(user);
System.out.println("xxxxxxx");
System.out.println(row.getRowNum());
//removeRow(sheet, row.getRowNum());
//this.deleteRow(row, sheet , WorkbookFactory.create(new FileInputStream(PATH_TO_EXCEL)));
break;
}
}
for (User user : userList) {
System.out.println(user.getFirstName() + " " + user.getLastName() + " " + user.getEmail() + " " + user.getDOB());
}
}
private static void removeAndShiftRow(String pathToExcel, int rowToBeRemoved, boolean removeIndexOneOnly, Sheet sheet) throws IOException, InvalidFormatException {
if (removeIndexOneOnly == true) {
rowToBeRemoved = 1;
}
int lastRowNum = sheet.getLastRowNum();
Row row = sheet.getRow(rowToBeRemoved);
if (row != null && rowToBeRemoved != lastRowNum) {
//System.out.println(row.getCell(3).getRichStringCellValue()); //For Testing
//System.out.println(row.getCell(4).getRichStringCellValue()); //For Testing
System.out.println("row to be removed " + rowToBeRemoved);
System.out.println("last row " + lastRowNum);
sheet.removeRow(row);
sheet.shiftRows(rowToBeRemoved + 1, lastRowNum, -1);
//sheet.getWorkbook().setActiveSheet(sheet.getWorkbook().getSheetIndex(sheet));
}
if (rowToBeRemoved == lastRowNum) {
System.out.println("Very Last Row");
Row removingRow = sheet.getRow(rowToBeRemoved);
if (removingRow != null) {
sheet.removeRow(removingRow);
}
}
}
removeAndShiftRow() method does not have any file I/O at all.
All file I/O process is done in readExcel() method.
The xlsx file gets corrupted only when I run my codes for the 2nd time. The 1st run will not make my file corrupted.
I am wondering where could be the issue.
Thanks.
[Update]:
I do some more investigation and I found a weird thing.
The 1st run will just do perfectly fine.
The 2nd run will change my second column from formula to string. Here is the snapshot.
Because of that change, the 3rd run will throw exception
Exception in thread "main" java.lang.IllegalStateException: Master cell of a shared formula with sid=0 was not found
at org.apache.poi.xssf.usermodel.XSSFCell.convertSharedFormula(XSSFCell.java:507)
at org.apache.poi.xssf.usermodel.XSSFCell.getCellFormula(XSSFCell.java:491)
at org.apache.poi.xssf.usermodel.XSSFCell.getCellFormula(XSSFCell.java:469)
at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:984)
at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:944)
at org.apache.poi.ss.usermodel.DataFormatter.formatCellValue(DataFormatter.java:923)
at Sandbox.ExcelReader.readExcelSheet(ExcelReader.java:110)
The exception points to method readExcelSheet() specifically this line
case 1:
System.out.println(cell.getRichStringCellValue());
user.setIDNumberShort(formatter.formatCellValue(cell)); //This will throw exception in the 3rd run
cell.setCellValue("used");
break;
Not sure which part is not done correctly.
