这篇博客主要介绍如何利用Java将文件内容解析成JSON格式，作者提供了关键的代码片段，并提到了需要的Apache POI库，包括poi、poi-ooxml和poi-ooxml-schemas的3.17版本。
个人用途，用于将数据解析成json格式使用
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.gta.edu.sdk.util.StringUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
/**
* @author y
* @create 2018-01-18 14:28
* @desc POI读取excel有两种模式，一种是用户模式，一种是事件驱动模式
* 采用SAX事件驱动模式解决XLSX文件，可以有效解决用户模式内存溢出的问题，
* 该模式是POI官方推荐的读取大数据的模式，
* 在用户模式下，数据量较大，Sheet较多，或者是有很多无用的空行的情况下，容易出现内存溢出
* <p>
* 用于解决.xlsx2007版本大数据量问题
**/
public class ExcelXlsxReader extends DefaultHandler {
/**
* 单元格中的数据可能的数据类型
*/
enum CellDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
}
/**
* 共享字符串表
*/
private SharedStringsTable sst;
/**
* 上一次的内容
*/
private String lastContents;
/**
* 文件的绝对路径
*/
private String filePath = "";
/**
* 工作表索引
*/
private int sheetIndex = 0;
/**
* sheet名
*/
private String sheetName = "";
/**
* 一行内cell集合
*/
private List<String> cellList = new ArrayList<>();
/**
* 单个sheet表的列头集合
*/
private List<String> columnList;
/**
* 单个sheet表的每列的数据类型
*/
private List<String> columnType;
//存储sheet表每行
private JSONObject objSheet;
//单个sheet表的数据
private JSONArray arrSheet = new JSONArray();
//保存所有sheet表的数据
private JSONArray tables = new JSONArray();
//保存所有sheet名称
public List<String> sheetNames = new ArrayList<>();
/**
* 字符串标识
*/
private boolean nextIsString;
/**
* 当前行
*/
private int curRow = 1;
private int curSize;
/**
* 当前列
*/
private int curCol = 0;
private int column = 1;
/**
* T元素标识
*/
private boolean isTElement;
/**
* 异常信息，如果为空则表示没有异常
*/
private String exceptionMessage;
/**
* 单元格数据类型，默认为字符串类型
*/
private CellDataType nextDataType = CellDataType.SSTINDEX;
private final DataFormatter formatter = new DataFormatter();
/**
* 单元格日期格式的索引
*/
private short formatIndex;
/**
* 日期格式字符串
*/
private String formatString;
//定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
private String preRef = null, ref = null;
//定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
private String maxRef = null;
/**
* 单元格
*/
private StylesTable stylesTable;
/**
* 有效数据矩形区域,A1:Y2
*/
private String dimension;
/**
* 根据dimension得出每行的数据长度
*/
private int longest;
/**
* 上个有内容的单元格id，判断空单元格
*/
private String lastCellid;
/**
* 上一行id, 判断空行
*/
private String lastRowid;
/**
* 遍历工作簿中所有的电子表格
* 并缓存在mySheetList中
*
* @param file
* @throws Exception
*/
public JSONArray process(File file) throws Exception {
//filePath = filename;
tables.clear();
sheetNames.clear();
//File file=new File(filename);
OPCPackage pkg = OPCPackage.open(file);
XSSFReader xssfReader = new XSSFReader(pkg);
stylesTable = xssfReader.getStylesTable();
SharedStringsTable sst = xssfReader.getSharedStringsTable();
XMLReader parser = this.fetchSheetParser(sst);
//Iterator<InputStream> sheets = xssfReader.getSheetsData();
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
while (sheets.hasNext()) { //遍历sheet
curRow = 1; //标记初始行为第一行
curSize = 0;
arrSheet.clear();//清除历史数据
if (columnList != null) {
columnList.clear();
}
sheetIndex++;
InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错
sheetName = sheets.getSheetName();
sheetNames.add(sheetName);//存储所有sheet名称
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
sheet.close();
objSheet = new JSONObject();
JSONArray json = (JSONArray) arrSheet.clone();
objSheet.put(sheetName, json);
//总数据
tables.add(objSheet);
}
return tables; //返回该excel文件的总行数，不包括首列和空行
}
public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
return parser;
}
/**
* 第一个执行
*
* @param uri
* @param localName
* @param qName
* @param attributes
* @throws SAXException
*/
@Override
public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
// 置空
lastContents = "";
if (qName.equals("dimension")) {
dimension = attributes.getValue("ref");
longest = covertRowIdtoInt(dimension.substring(dimension.indexOf(":") + 1));
} //行开始
if (qName.equals("row")) {
String rowNum = attributes.getValue("r");
//判断空行
if (lastRowid != null) {
//与上一行相差2, 说明中间有空行
int gap = Integer.parseInt(rowNum) - Integer.parseInt(lastRowid);
if (gap > 1) {
gap -= 1;
while (gap > 0) {
//container.add(new ArrayList<>());
gap--;
}
}
}
lastRowid = attributes.getValue("r");
//currentRow = new ArrayList<>();
}
// c => 单元格
if ("c".equals(qName)) {
String rowId = attributes.getValue("r"); //空单元判断，添加空字符到list
if (lastCellid != null) {
int gap = covertRowIdtoInt(rowId) - covertRowIdtoInt(lastCellid);
for (int i = 0; i < gap - 1; i++) {
cellList.add("null");
curCol++;
}
} else { //第一个单元格可能不是在第一列
if (!"A1".equals(rowId)) {
for (int i = 0; i < covertRowIdtoInt(rowId) - 1; i++) {
cellList.add("null");
curCol++;
}
}
}
lastCellid = rowId;
// 前一个单元格的位置
if (preRef == null) {
preRef = attributes.getValue("r");
} else {
preRef = ref;
}
// 当前单元格的位置
ref = attributes.getValue("r");
// 设定单元格类型
this.setNextDataType(attributes);
// Figure out if the value is an index in the SST
String cellType = attributes.getValue("t");
if (cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// 当元素为t时
if ("t".equals(qName)) {
isTElement = true;
} else {
isTElement = false;
}
}
/**
* 第二个执行
* 得到单元格对应的索引值或是内容值
* 如果单元格类型是字符串、INLINESTR、数字、日期，lastContents则是索引值
* 如果单元格类型是布尔值、错误、公式，lastContents则是内容值
*
* @param ch
* @param start
* @param length
* @throws SAXException
*/
@Override
public void characters(char[] ch, int start, int length) throws SAXException {
// 得到单元格内容的值
lastContents += new String(ch, start, length);
}
/**
* 第三个执行
*
* @param uri
* @param localName
* @param name
* @throws SAXException
*/
@Override
public void endElement(String uri, String localName, String name) throws SAXException {
// 根据SST的索引值的到单元格的真正要存储的字符串
// 这时characters()方法可能会被调用多次
if (nextIsString && StringUtils.isNotEmpty(lastContents) && StringUtils.isNumeric(lastContents)) {
try{
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
}catch (IndexOutOfBoundsException e){
}
}
//t元素也包含字符串
if (isTElement) {//这个程序没经过
//将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
String value = lastContents.trim();
cellList.add(curCol, value);
curCol++;
isTElement = false;
} else if ("v".equals(name)) {
// v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
String value = this.getDataValue(lastContents.trim(), "");//根据索引值获取对应的单元格值
cellList.add(curCol, value);
curCol++;
} else {
//如果标签名称为row，这说明已到行尾，调用optRows()方法
if ("row".equals(name)) {
//默认第一行为表头，以该行单元格数目为最大数目
//判断最后一个单元格是否在最后，补齐列数
if (covertRowIdtoInt(lastCellid) < longest) {
for (int i = 0; i < longest - covertRowIdtoInt(lastCellid); i++) {
cellList.add(curCol, "null");
curCol++;
}
}
//container.add(currentRow);
lastCellid = null;
//以下部分皆为自己逻辑设计 json格式 表名.列名+类型：数值
//获取第一行，设置为列名
if (curRow == 1) {
if (cellList.size() > 0) {
int size = cellList.size();
for (int i = 0; i < size; i++) {
if (!StringUtil.isNullOrEmpty(cellList.get(i))) {
curSize++;
}
}
if (curSize > 1) {
columnList = new ArrayList<>();
for (int i = 0; i < size; i++) {
columnList.add(cellList.get(i));
//System.out.print(cellList.get(i) + " ");
}
column = 2;
//System.out.println();
}
} else {
return;
}
}
//判断是否为合并单元格
if (curRow == 2 && curSize < 2) {
columnList = new ArrayList<>();
int size = cellList.size();
for (int i = 0; i < size; i++) {
columnList.add(cellList.get(i));
//System.out.print(cellList.get(i) + " ");
curSize++;
}
//System.out.println();
column = 3;
}
//数据处理与添加
if (curSize > 1 && curRow > (column - 1)) {
int cellSize = cellList.size() - 1;
if (curRow != column) { //获取列的类型
int size = columnList.size();
objSheet = new JSONObject(true);
for (int i = 0; i < size; i++) {
if (cellSize >= i) {
//System.out.print(" " + cellList.get(i));
objSheet.put(sheetName + "." + columnList.get(i) + columnType.get(i), cellList.get(i));
} else {
objSheet.put(sheetName + "." + columnList.get(i) + columnType.get(i), "null");
}
}
//System.out.println();
arrSheet.add(objSheet);
} else {
int size = columnList.size();
String headName = null;
//用于列名验证 isLetterDigitOrChinese判断是否为数值，字符，中文
for (int i = 0; i < size; i++) {
headName = columnList.get(i).trim();
if (headName.length() > 0) {
if (headName.equals("null") || !DataFileTypeUtil.isLetterDigitOrChinese(headName)) {
columnList.set(i, "第" + (i + 1) + "列");
}
} else {
columnList.set(i, "第" + (i + 1) + "列");
}
}
//验证并保存文件类型
theColumnType(cellList,size);
objSheet = new JSONObject(true);
for (int i = 0; i < size; i++) {
if (cellSize >= i) {
//System.out.print(" " + cellList.get(i));
objSheet.put(sheetName + "." + columnList.get(i) + columnType.get(i), cellList.get(i));
} else {
objSheet.put(sheetName + "." + columnList.get(i) + columnType.get(i), "null");
}
}
//System.out.println();
arrSheet.add(objSheet);
}
//ExcelReaderUtil.sendRows(sheetName, sheetIndex, curRow, cellList,objSheet);
}
cellList.clear();
curRow++;
curCol = 0;
preRef = null;
ref = null;
}
}
}
以上为主要代码部分，下面是工具方法，有些不可少
/**
* 判断列的类型
*
* @return
*/
public List<String> theColumnType(List<String> cellList,int colSize) {
columnType = new ArrayList<>();
int size = cellList.size()-1;
for (int i = 0; i < colSize; i++) {
if (size >= i) {
//System.out.print(cellList.get(i) + " ");
if (DataFileTypeUtil.isNumeric01(cellList.get(i))) {
columnType.add("01");
} else if (DataFileTypeUtil.isDateTime02(cellList.get(i))) {
columnType.add("02");
} else if (DataFileTypeUtil.estimateAddressType(cellList.get(i))) {
columnType.add("04");
} else {
columnType.add("00");
}
}else{
columnType.add("00");
}
}
//System.out.println();
return columnType;
}
/**
* 处理数据类型
*
* @param attributes
*/
public void setNextDataType(Attributes attributes) {
nextDataType = CellDataType.NUMBER;
formatIndex = -1;
formatString = null;
String cellType = attributes.getValue("t");
String cellStyleStr = attributes.getValue("s");
String columData = attributes.getValue("r");
if ("b".equals(cellType)) {
nextDataType = CellDataType.BOOL;
} else if ("e".equals(cellType)) {
nextDataType = CellDataType.ERROR;
} else if ("inlineStr".equals(cellType)) {
nextDataType = CellDataType.INLINESTR;
} else if ("s".equals(cellType)) {
nextDataType = CellDataType.SSTINDEX;
} else if ("str".equals(cellType)) {
nextDataType = CellDataType.FORMULA;
}
if (cellStyleStr != null) {
int styleIndex = Integer.parseInt(cellStyleStr);
XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
formatIndex = style.getDataFormat();
formatString = style.getDataFormatString();
//这个if用于将百分数转化为小数，不需要可不加
if(formatString.indexOf("%") == -1){
if (!formatString.equals("000000") && nextDataType != CellDataType.SSTINDEX) {
if (formatIndex == 14 || formatIndex == 31 || formatIndex == 57 || formatIndex == 58
|| formatIndex == 176 || formatIndex == 178 || formatIndex == 179 || (182 <= formatIndex && formatIndex <= 196)
|| (210 <= formatIndex && formatIndex <= 213) || 208 == formatIndex) { // 日期
nextDataType = CellDataType.DATE;
formatString = "yyyy-MM-dd";
} else if (formatIndex == 22 || formatIndex == 177 || formatIndex == 180 || formatIndex == 181) {
nextDataType = CellDataType.DATE;
formatString = "yyyy-MM-dd HH:mm:ss";
} else if (formatIndex == 20 || formatIndex == 32 || formatIndex == 183 || (200 <= formatIndex && formatIndex <= 209)) { // 时间
nextDataType = CellDataType.DATE;
formatString = "HH:mm:ss";
}
}
}else{
formatString = "General";
}
/*if ("m/d/yy" == formatString) {
nextDataType = CellDataType.DATE;
formatString = "yyyy-MM-dd";
}*/
if (formatString == null) {
nextDataType = CellDataType.NULL;
formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
}
}
if (nextDataType == CellDataType.NUMBER) {
if (formatString == null) {
formatString = "General";
}
}
}
/**
* 对解析出来的数据进行类型处理
*
* @param value 单元格的值，
* value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值，
* SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值
* @param thisStr 一个空字符串
* @return
*/
@SuppressWarnings("deprecation")
public String getDataValue(String value, String thisStr) {
switch (nextDataType) {
// 这几个的顺序不能随便交换，交换了很可能会导致数据错误
case BOOL: //布尔值
char first = value.charAt(0);
//thisStr = first == '0' ? "FALSE" : "TRUE";
thisStr = first == '0' ? "0" : "1";
break;
case ERROR: //错误
thisStr = "ERROR:" + value;
break;
case FORMULA: //公式
thisStr = '"' + value + '"';
break;
case INLINESTR:
XSSFRichTextString rtsi = new XSSFRichTextString(value);
thisStr = rtsi.toString();
rtsi = null;
break;
case SSTINDEX: //字符串
if (!isNumber(value)) {
String sstIndex = value;
try {
int idx = Integer.parseInt(sstIndex);
XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));
thisStr = rtss.toString();
rtss = null;
thisStr = value;
} catch (NumberFormatException ex) {
thisStr = value;
}
} else {
thisStr = value;
}
break;
case NUMBER: //数字
if (!isChinese(value)) {
if (formatString != null) {
thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
} else {
thisStr = value;
}
} else {
thisStr = value;
}
break;
case DATE: //日期
if (!isChinese(value)) {
if(value.indexOf("0.") == 0){
formatString = "HH:mm:ss";
}
if(!formatString.equals("yyyy-MM-dd HH:mm:ss") && !formatString.equals("yyyy-MM-dd") && !formatString.equals("HH:mm:ss")){
formatString = "yyyy-MM-dd";
}
thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
// 对日期字符串作特殊处理
//thisStr = thisStr.replace(" ", "T");
} else {
thisStr = value;
}
break;
default:
thisStr = "null";
break;
}
return thisStr;
}
/**
* 计算两个单元格之间的单元格数目(同一行)
*
* @param ref
* @param preRef
* @return
*/
public int countNullCell(String ref, String preRef) {
// excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
String xfd = ref.replaceAll("\\d+", "");
String xfd_1 = preRef.replaceAll("\\d+", "");
xfd = fillChar(xfd, 3, '@', true);
xfd_1 = fillChar(xfd_1, 3, '@', true);
char[] letter = xfd.toCharArray();
char[] letter_1 = xfd_1.toCharArray();
int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]);
return res - 1;
}
/**
* 列号转数字 AB7-->28 第28列 * * @param rowId * @return
*/
public static int covertRowIdtoInt(String rowId) {
int firstDigit = -1;
for (int c = 0; c < rowId.length(); ++c) {
if (Character.isDigit(rowId.charAt(c))) {
firstDigit = c;
break;
}
} //AB7-->AB //AB是列号, 7是行号
String newRowId = rowId.substring(0, firstDigit);
int num = 0;
int result = 0;
int length = newRowId.length();
for (int i = 0; i < length; i++) {
//先取最低位，B
char ch = newRowId.charAt(length - i - 1);
//B表示的十进制2，ascii码相减，以A的ascii码为基准，A表示1，B表示2
num = (int) (ch - 'A' + 1);
//列号转换相当于26进制数转10进制
num *= Math.pow(26, i);
result += num;
}
return result;
}
/**
* 字符串的填充
*
* @param str
* @param len
* @param let
* @param isPre
* @return
*/
String fillChar(String str, int len, char let, boolean isPre) {
int len_1 = str.length();
if (len_1 < len) {
if (isPre) {
for (int i = 0; i < (len - len_1); i++) {
str = let + str;
}
} else {
for (int i = 0; i < (len - len_1); i++) {
str = str + let;
}
}
}
return str;
}
/**
* 判断字符串中是否包含中文
*
* @param str 待校验字符串
* @return 是否为中文
* @warn 不能校验是否为中文标点符号
*/
public boolean isChinese(String str) {
Pattern p = Pattern.compile("[\u4e00-\u9fa5]");
Matcher m = p.matcher(str);
if (m.find()) {
return true;
}
return false;
}
/**
* 判断一个字符串是否是数字。
*
* @param string
* @return
*/
public static boolean isNumber(String string) {
if (string == null) {
return false;
}
Pattern pattern = Pattern.compile("^-?\\d+(\\.\\d+)?$");
return pattern.matcher(string).matches();
}
/**
* @return the exceptionMessage
*/
public String getExceptionMessage() {
return exceptionMessage;
}
}
不怎么喜欢描述，注释都会打，看注释应该能理解，不能就自己走一遍。以下为需要的特殊的jar包
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
扫一扫
1331
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
