解决POI事件驱动模式读取不到Java代码创建的Excel表格数据问题
场景
使用POI官网上的事件驱动模式的示例方法，读取单sheet的Excel表格文件（.xlsx），Microsoft Excel和WPS Excel创建的表格文件可以正常读取数据，但是java代码创建的表格文件（不使用软件打开并保存）却读取不到数据。（原因是rId获取的不对、没有读取t标签）
环境java 1.8、poi-ooxml 4.0.1 、maven工程
解决1、动态获取rId（兼容软件创建的表格和代码创建的表格（普通方式创建））
2、增加读取t标签内容（兼容通过限制对滑动窗口内的行的访问来降低内存占用的方式创建的表格）
Main.java
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.Map;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class Main {
public static void main(String[] args) throws Exception {
ExcelEventUserModel model = new ExcelEventUserModel();
System.out.println("------读取使用Microsoft Excel创建的表格------");
String MSExcelPath = "E:\\tmp\\test\\excelTest\\test-MSExcel.xlsx";
Map<Integer, Map> msExcelData = model.processOneSheet(MSExcelPath);
printExcelData(msExcelData);
System.out.println("------读取使用代码创建的表格------");
String javaExcelPath = "E:\\tmp\\test\\excelTest\\test-JavaExcel.xlsx";
createExcel(javaExcelPath);
Map<Integer, Map> javaExcelData = model.processOneSheet(javaExcelPath);
printExcelData(javaExcelData);
}
private static void printExcelData(Map<Integer, Map> map) {
for (Map.Entry<Integer, Map> entry : map.entrySet()) {
StringBuilder rowData = new StringBuilder();
for (Map.Entry cell : entry.getValue().entrySet()) {
rowData.append(cell.getKey()).append(": ").append(cell.getValue()).append(" ");
}
int row = entry.getKey();
System.out.println(row + "-->" + rowData.toString());
}
}
private static void createExcel(String filePath) throws Exception {
createDirectory(filePath);
OutputStream fileOut = null;
XSSFWorkbook wb = null;
try {
fileOut = new FileOutputStream(filePath);
wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Sheet1");
XSSFRow row_0 = sheet.createRow(0);
row_0.createCell(0).setCellValue("姓名");
row_0.createCell(1).setCellValue("职位");
XSSFRow row_1 = sheet.createRow(1);
row_1.createCell(0).setCellValue("千手纲手");
row_1.createCell(1).setCellValue("五代火影");
XSSFRow row_2 = sheet.createRow(2);
row_2.createCell(0).setCellValue("旗木卡卡西");
row_2.createCell(1).setCellValue("六代火影");
wb.write(fileOut);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (wb != null) wb.close();
} catch (Exception ex) {
ex.printStackTrace();
}
try {
if (fileOut != null) fileOut.close();
} catch (Exception ex) {
ex.printStackTrace();
}
}
}
private static void createDirectory(String filePath) throws Exception {
if (filePath == null || "".equals(filePath.trim())) {
throw new Exception("filePath is empty");
}
File file;
if (filePath.contains(".")) {
file = new File(filePath.substring(0, filePath.lastIndexOf(File.separator)));
} else {
file = new File(filePath);
}
if (!file.exists()) file.mkdirs();
}
}
ExcelEventUserModel.java
import java.io.*;
import java.util.HashMap;
import java.util.Map;
import javax.xml.parsers.ParserConfigurationException;
import org.apache.poi.ooxml.util.SAXHelper;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.xml.sax.*;
import org.xml.sax.helpers.DefaultHandler;
/**
* POI事件驱动模式示例
*
* http://poi.apache.org/components/spreadsheet/how-to.html#xssf_sax_api
*/
public class ExcelEventUserModel {
public Map<Integer, Map> data;
private String rId;
public ExcelEventUserModel() {
this.data = new HashMap();
this.rId = "rId1";
}
/**
* 读取第一个sheet的数据
*
* @param filePath 文件路径
* @return 表格数据
*/
public Map<Integer, Map> processOneSheet(String filePath) {
this.data = new HashMap();
InputStream in = null;
OPCPackage pkg = null;
SharedStringsTable sst = null;
InputStream sheet = null;
try {
in = new BufferedInputStream(new FileInputStream(new File(filePath)));
pkg = OPCPackage.open(in);
XSSFReader r = new XSSFReader(pkg);
// 动态获取rId：通过读取workbook设置rid
setRelationshipId(r);
// 这里输出一下rId
System.out.println("rId = " + rId);
sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
sheet = r.getSheet(rId);
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (sheet != null) sheet.close();
} catch (Exception closeException) {
closeException.printStackTrace();
}
try {
if (sst != null) sst.close();
} catch (Exception closeException) {
closeException.printStackTrace();
}
try {
if (pkg != null) pkg.close();
} catch (Exception closeException) {
closeException.printStackTrace();
}
try {
if (in != null) in.close();
} catch (Exception closeException) {
closeException.printStackTrace();
}
}
return this.data;
}
private void setRelationshipId(XSSFReader r)
throws IOException, InvalidFormatException, SAXException, ParserConfigurationException {
InputStream workbookData = r.getWorkbookData();
InputSource sheetSource = new InputSource(workbookData);
XMLReader parser = WorkbookParser();
parser.parse(sheetSource);
}
private XMLReader WorkbookParser() throws SAXException, ParserConfigurationException {
XMLReader parser = SAXHelper.newXMLReader();
ContentHandler handler = new WorkbookHandler();
parser.setContentHandler(handler);
return parser;
}
private class WorkbookHandler extends DefaultHandler {
private WorkbookHandler() {}
public void startElement(String uri, String localName, String name, Attributes attributes) {
if ("sheet".equals(name)) {
rId = attributes.getValue("r:id");
}
}
public void endElement(String uri, String localName, String name) {}
}
private XMLReader fetchSheetParser(SharedStringsTable sst)
throws SAXException, ParserConfigurationException {
XMLReader parser = SAXHelper.newXMLReader();
ContentHandler handler = new SheetHandler(sst);
parser.setContentHandler(handler);
return parser;
}
/**
* See org.xml.sax.helpers.DefaultHandler javadocs
*/
private class SheetHandler extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private int rowIndex;
private int colIndex;
private Map rowData;
private SheetHandler(SharedStringsTable sst) {
this.sst = sst;
this.rowData = new HashMap();
// 默认设置为第0行
this.rowIndex = 0;
// 默认设置为第0列
this.colIndex = 0;
}
public void startElement(String uri, String localName, String name, Attributes attributes) {
// 一行开始
if ("row".equals(name)) {
this.rowIndex = Integer.parseInt(attributes.getValue("r")) - 1;
}
// 单元格
if ("c".equals(name)) {
String cellType = attributes.getValue("t");
this.nextIsString = "s".equals(cellType);
this.colIndex = getColIndex(attributes.getValue("r"));
}
// Clear contents cache
lastContents = "";
}
public void endElement(String uri, String localName, String name) {
// Process the last contents as required.
// Do now, as characters() may be called more than once
if (nextIsString) {
int idx = Integer.parseInt(lastContents);
lastContents = sst.getItemAt(idx).getString();
nextIsString = false;
}
// v => contents of a cell
if ("v".equals(name) || "t".equals(name)) {
// 放入行数据中，key=列数，value=单元格的值
rowData.put(colIndex, lastContents);
}
// 一行的结束
if ("row".equals(name)) {
// 新的一行，存储上一行的数据
data.put(rowIndex, rowData);
this.rowData = new HashMap();
}
}
public void characters(char[] ch, int start, int length) {
lastContents += new String(ch, start, length);
}
/**
* 转换表格引用为列编号，A-0，B-1
*
* @param cellReference 列引用，例：A1
* @return 表格列位置，从0开始算
*/
private int getColIndex(String cellReference) {
String ref = cellReference.replaceAll("\\d+", "");
int num;
int result = 0;
int length = ref.length();
for (int i = 0; i < length; i++) {
char ch = cellReference.charAt(length - i - 1);
num = ch - 'A' + 1;
num *= Math.pow(26, i);
result += num;
}
return result - 1;
}
/**
* 转换表格引用为行号
*
* @param cellReference 列引用，例：A1
* @return 行号，从0开始
*/
private int getRowIndex(String cellReference) {
String rowIndexStr = cellReference.replaceAll("[a-zA-Z]+", "");
return Integer.parseInt(rowIndexStr) - 1;
}
}
}
maven dependency
org.apache.poi
poi-ooxml
4.0.1
结果
test-MSExcel.xlsx
test-JavaExcel.xlsx
------读取使用Microsoft Excel创建的表格------
rId = rId1
0-->0: 姓名 1: 职位
1-->0: 千手柱间 1: 初代火影
2-->0: 千手扉间 1: 二代火影
3-->0: 猿飞日斩 1: 三代火影
4-->0: 波风水门 1: 四代火影
------读取使用代码创建的表格------
rId = rId3
0-->0: 姓名 1: 职位
1-->0: 千手纲手 1: 五代火影
2-->0: 旗木卡卡西 1: 六代火影
解析
将.xlsx表格文件后缀名改为.zip并解压之后，可以看到很多xml文件。
1、MS Excel创建的.xlsx表格文件，其workbook.xml中的sheet参数为
2、java代码创建的.xlsx表格文件，其workbook.xml中的sheet参数为
注：如果你使用软件打开java代码创建的.xlsx表格文件，并保存一下，你会发现rId3会变成rId1。
3、MS Excel创建的.xlsx表格文件，其sheet1.xml中的sheetData参数为
0
1
2
3
4
5
6
7
8
9
上面存储值的为v标签
其实，这里存的是共享字符串表（代码中的sst）中的引用，其对应的文件为sharedStrings.xml，内容如下
姓名
职位
千手柱间
初代火影
千手扉间
二代火影
猿飞日斩
三代火影
波风水门
四代火影
4、java代码创建的.xlsx表格文件，其sheet1.xml中的sheetData参数为
0
1
2
3
4
5
上面存储值的也是v标签
另：使用通过限制对滑动窗口内的行的访问来降低内存占用的方式创建的表格，
其sheet1.xml中的sheetData参数为
A1
B1
C1
D1
E1
F1
G1
H1
I1
J1
上面存储值的为t标签
【截止到这里】分析了rId值不一样（rId1、rId3），和存储单元格的值的标签页不一样（v、t标签）。（上面的图片和内容有点多，小结一下）
5、POI事件驱动模式读取表格，是通过rId的值获取对应的.xml文件，然后读取对应的.xml中的每个标签，例如sheet1.xml中的读取为：worksheet-->dimension-->sheetViews-->sheetView-->sheetFormatPr-->sheetData-->row-->c-->v-->......不同方式创建的表格文件，标签会有些不同，但表格数据部分sheetData、row、c、v或sheetData、row、c、is、t，表示行和表示单元格的标签都是一样的：row和c。（通过断点调试就很清楚了，断点行：if ("row".equals(name)) {，name即为标签）
6、所以，通过动态获取rId的值和增加对t标签的兼容，就能解决读取不到数据的问题了（虽然场景使用范围不大，仅支持一个sheet的Excel表格文件）。动态获取rId，我是参照读取sheet1.xml的整套方法（fetchSheetParser、SheetHandler）以及对标签的读取流程，自己写了个读取流程，获取workbook.xml中sheet标签中的r:id的值。（好像也不难）
参考链接
https://poi.apache.org/components/spreadsheet/how-to.html#sxssf
说明
1、之前一直被这个问题困扰：为什么POI自己导出的表格，POI自己会读取不了？一直以为这是个大bug，不过，后来折腾了很久，发现了rId和v、t标签的问题。另外，补充个东西：通过限制对滑动窗口内的行的访问来降低内存占用的方式创建Excel，它支持大量数据写，并且不会耗很大的内存，比传统方式创建Excel更优（参考链接中有相关方法，SXSSF (Streaming Usermodel API)部分）。
2、POI事件驱动模式官方示例在参考链接中的XSSF and SAX (Event API)部分。
3、此解决方案仅适用于单sheet表格，暂不支持多sheet表格读取，支持java代码POI普通方式创建表格（Main.java中的createExcel方法）和通过限制对滑动窗口内的行的访问来降低内存占用的方式创建表格（详见参考链接）两种表格创建方式。
（PS：关于志村团藏这个火影就……此例中就不考虑了，哈哈哈（测试数据内容部分））
作者：晨曦之光Wing
