| Summary: | Open xml4j Exception is coming on the attached excel. | ||
|---|---|---|---|
| Product: | POI | Reporter: | Aman Garg <aman13garg> |
| Component: | POIFS | Assignee: | POI Developers List <dev> |
| Status: | RESOLVED WORKSFORME | ||
| Severity: | blocker | ||
| Priority: | P2 | ||
| Version: | 3.14-FINAL | ||
| Target Milestone: | --- | ||
| Hardware: | PC | ||
| OS: | Linux | ||
| Attachments: | While comparing these two files | ||
|
Description
Aman Garg
2017-03-23 10:32:07 UTC
Can you include the minimal amount of code that makes it possible to reproduce this? public void compareExcels() {
try {
File currentDirectory = new File(new File("exceldownloads").getAbsolutePath());
// get input excel files
FileInputStream excellFile1 = new FileInputStream(
new File(currentDirectory.getAbsoluteFile() + "/"+ "Production.xlsx"));
FileInputStream excellFile2 = new FileInputStream(
new File(currentDirectory.getAbsoluteFile() +"/" + "Testing.xlsx"));
// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook1 = new XSSFWorkbook(excellFile1);
XSSFWorkbook workbook2 = new XSSFWorkbook(excellFile2);
XSSFFont font1 = workbook1.createFont();
font1.setColor(XSSFFont.COLOR_RED);
font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
XSSFFont font2 = workbook2.createFont();
font2.setColor(XSSFFont.COLOR_RED);
font2.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
XSSFCellStyle style1 = workbook1.createCellStyle();
style1.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
style1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style1.setFont(font1);
XSSFCellStyle style2 = workbook2.createCellStyle();
style2.setFillBackgroundColor(IndexedColors.YELLOW.getIndex());
style2.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
style2.setFont(font2);
// Get first/desired sheet from the workbook
XSSFSheet sheet1 = workbook1.getSheetAt(0);
XSSFSheet sheet2 = workbook2.getSheetAt(0);
// Compare sheets
if (compareTwoSheets(sheet1, sheet2, style1, style2)) {
Reporter.log("<li><font size='3' color='blueviolet' face='arial'>"
+ "\n\nBoth Excels are equal" + "</li></font size>");
} else {
Reporter.log("<li><font size='3' color='red' face='arial'>"
+ "\n\n Both excels are not equal" + "</li></font size>");
}
// close files
excellFile1.close();
excellFile2.close();
workbook1.close();
workbook2.close();
FileOutputStream fileOut = new FileOutputStream(
new File(currentDirectory.getAbsoluteFile() + "/" + "Production.xlsx"));
workbook1.write(fileOut);
fileOut.close();
FileOutputStream fileOut1 = new FileOutputStream(
new File(currentDirectory.getAbsoluteFile() + "/" + "Testing.xlsx"));
workbook2.write(fileOut1);
fileOut1.close();
} catch (Exception e) {
e.printStackTrace();
}
}
// Compare Two Sheets
public static boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2, XSSFCellStyle s1,
XSSFCellStyle s2) {
int firstRow1 = sheet1.getFirstRowNum();
int lastRow1 = sheet1.getLastRowNum();
boolean equalSheets = true;
for (int i = firstRow1; i <= lastRow1; i++) {
Reporter.log("\n\nComparing Row " + i);
XSSFRow row1 = sheet1.getRow(i);
XSSFRow row2 = sheet2.getRow(i);
if (!compareTwoRows(row1, row2, s1, s2)) {
equalSheets = false;
Reporter.log("<li><font size='1' color='red' face='arial'>"
+ "\nRow " + i + " - Not Equal" + "</li></font size>");
} else {
Reporter.log("<li><font size='1' color='bluevoilet' face='arial'>"
+ "\nRow " + i + " - Equal" + "</li></font size>");
}
}
return equalSheets;
}
// Compare Two Rows
public static boolean compareTwoRows(XSSFRow row1, XSSFRow row2, XSSFCellStyle s1,
XSSFCellStyle s2) {
if ((row1 == null) && (row2 == null)) {
return true;
} else if ((row1 == null) || (row2 == null)) {
return false;
}
int firstCell1 = row1.getFirstCellNum();
int lastCell1 = row1.getLastCellNum();
boolean equalRows = true;
// Compare all cells in a row
for (int i = firstCell1; i <= lastCell1; i++) {
XSSFCell cell1 = row1.getCell(i);
XSSFCell cell2 = row2.getCell(i);
if (!compareTwoCells(cell1, cell2)) {
equalRows = false;
cell1.setCellStyle(s1);
cell2.setCellStyle(s2);
Reporter.log("<li><font size='1' color='red' face='arial'>"
+ "\nCell " + i + " - Not Equal" + "</li></font size>");
} else {
Reporter.log("<li><font size='1' color='bluevoilet' face='arial'>"
+ "\nCell " + i + " - Equal" + "</li></font size>");
}
}
return equalRows;
}
// Compare Two Cells
public static boolean compareTwoCells(XSSFCell cell1, XSSFCell cell2) {
if ((cell1 == null) && (cell2 == null)) {
return true;
} else if ((cell1 == null) || (cell2 == null)) {
return false;
}
boolean equalCells = false;
int type1 = cell1.getCellType();
int type2 = cell2.getCellType();
if (type1 == type2) {
if (cell1.getCellStyle().equals(cell2.getCellStyle())) {
// Compare cells based on its type
switch (cell1.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
if (cell1.getCellFormula().equals(cell2.getCellFormula())) {
equalCells = true;
}
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (cell1.getNumericCellValue() == cell2.getNumericCellValue()) {
equalCells = true;
}
break;
case HSSFCell.CELL_TYPE_STRING:
if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
equalCells = true;
}
break;
case HSSFCell.CELL_TYPE_BLANK:
if (cell2.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
equalCells = true;
}
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
if (cell1.getBooleanCellValue() == cell2.getBooleanCellValue()) {
equalCells = true;
}
break;
case HSSFCell.CELL_TYPE_ERROR:
if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) {
equalCells = true;
}
break;
default:
if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
equalCells = true;
}
break;
}
} else {
return false;
}
} else {
return false;
}
return equalCells;
}
Error is coming due to fileout.close. After close when we try to save the xlsx file. The workflow needs to be first write() then close(), we recently updated the javadoc accordingly, please try to exchange this and see if it works then. No response for some time so I expect the suggestion did work here. |