问题：Excel在“xxx.xlsx”中发现不可读取的内容。是否恢复此工作薄的内容？【原创】

现象：

点"是(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内容示例：