java Excel xlsx文件在使用Apache POI 3.17第二次读取和删除行后损坏
我试图从xlsx文件中读取一行，然后删除该行。 当我运行我的代码一次时，它是完美的。POI可以读取该行并删除该行。我可以用Excel打开文件。到目前为止还不错
当我再次（第二次）重新运行代码时，POI可以读取该行并删除该行，但当我使用excel打开文件时，我看到
我遵循了here、here和here中的所有建议，但我的问题仍然没有得到解决
这是我的密码
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()方法根本没有任何文件I/O。
所有文件I/O过程都是在readExcel()方法中完成的
xlsx文件只有在我第二次运行代码时才会损坏。第一次运行不会使我的文件损坏
我想知道问题出在哪里
谢谢
[更新]：
我做了更多的调查，发现了一件奇怪的事。
第一轮就可以了。
第二次运行将把我的第二列从公式改为字符串。这是快照
由于该更改，第三次运行将引发异常
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)
例外情况指向方法readExcelSheet()，特别是这一行
case 1:
System.out.println(cell.getRichStringCellValue());
user.setIDNumberShort(formatter.formatCellValue(cell)); //This will throw exception in the 3rd run
cell.setCellValue("used");
break;
不确定哪个部分没有正确完成
共 (0) 个答案