现象：

点"是(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,0CellValue,1,0CellValue,2,0xls End.=========================xlsx BeginCellValue,0,0CellValue,1,0CellValue,2,0

生成的Sheet内容示例：