现象：
点”是(Y)”
提示信息中提到的error242440_02.xml文件：
问题重现：
package poi;import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class FoundUnreadRecord { public static void main(String[] args) throws IOException {
int rowNum = 1;
createExcel_Reference(rowNum);
createExcel_WithTips(rowNum); rowNum = 0;
createExcel_Reference(rowNum);
createExcel_WithTips(rowNum);
} private static void createExcel_Reference(int rowNum) throws FileNotFoundException,
IOException {
String fileName = "UnreadRecordTips_Reference"+rowNum+".xlsx"; XSSFWorkbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("UnreadRecordTips");
for (int i = 0; i <= rowNum; i++) {
Row row = sheet.createRow(i);
Cell cell = row.createCell(0);
cell.setCellValue("test" + i);
} write(fileName, wb);
} private static void createExcel_WithTips(int rowNum) throws FileNotFoundException,
IOException {
String fileName = "UnreadRecordTips"+rowNum+".xlsx";;
XSSFWorkbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("UnreadRecordTips");
for (int i = 0; i <= rowNum; i++) {
Row row = sheet.createRow(i);
Cell cell = row.createCell(0);
cell.setCellValue("test" + i);
sheet.addMergedRegion(new CellRangeAddress(0, rowNum, 0, 0));
}
write(fileName, wb);
} private static void write(String fileName, XSSFWorkbook wb)
throws FileNotFoundException, IOException {
OutputStream stream = new FileOutputStream(fileName);
wb.write(stream);
stream.close();
}
}
分析及原因： org.apache.poi.xssf.usermodel.XSSFWorkbook下进行合并单元格操作，
org.apache.poi.ss.util.CellRangeAddress.CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)中涉及的单元格，如果对其中任何一个单元格进行超过一次addMergedRegion操作，则生成的excel打开时，会出现以上提示。TIPS：（1）org.apache.poi.hssf.usermodel.HSSFWorkbook.HSSFWorkbook()无此问题。可能是因为XSSFWorkbook是基于OOXML(.xlsx) file format（2）POI进行一次addMergedRegion操作,每Cell中值仍然可以读取，只是在Excel中没有显示
package poi;import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class MergedRegionReadHiddenValue { private static final String sheetName="mergedRegion"; public static void main(String[] args) throws IOException {
Workbook wb;
wb=new HSSFWorkbook();
String fileName="mergedRegion.xls";
createExcelWithMergedRegion(wb,fileName);
InputStream s=new FileInputStream(fileName);
wb=new HSSFWorkbook(s);
travelSheet(wb, sheetName);
s.close(); System.out.println("xls End.=========================xlsx Begin"); wb=new XSSFWorkbook();
fileName="mergedRegion.xlsx";
createExcelWithMergedRegion(wb,fileName);
s=new FileInputStream(fileName);
wb=new XSSFWorkbook(s);
travelSheet(wb, sheetName);
s.close();
} private static void createExcelWithMergedRegion(Workbook wb,String fileName)
throws FileNotFoundException, IOException { Sheet sheet=wb.createSheet(sheetName);
int lastRowNum=2;
for (int i=0;i<=lastRowNum;i++) {
createRowFillValue(sheet,i);
}
sheet.addMergedRegion(new CellRangeAddress(0, lastRowNum, 0, 0));
write(wb, fileName);
} private static void travelSheet(Workbook wb, String sheetName) {
Sheet sheet;
sheet=wb.getSheet(sheetName);
for (Row row : sheet) {
System.out.println(row.getCell(0).getStringCellValue());
}
} private static void write(Workbook wb, String fileName)
throws FileNotFoundException, IOException {
OutputStream stream=new FileOutputStream(fileName);
wb.write(stream);
stream.close();
} private static void createRowFillValue(Sheet sheet,int rowIdx) {
Row row=sheet.createRow(rowIdx);
Cell cell=row.createCell(0);
cell.setCellValue("CellValue,"+rowIdx+",0");;
}}
OutPut:
CellValue,0,0
CellValue,1,0
CellValue,2,0
xls End.=========================xlsx Begin
CellValue,0,0
CellValue,1,0
CellValue,2,0
生成的Sheet内容示例：
