需要在项目中引入依赖：
<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>xerces</groupId>
<artifactId>xercesImpl</artifactId>
<version>2.9.1</version>
</dependency>1.创建ExcelReaderUtil.java
public class ExcelReaderUtil {
// excel2003扩展名
public static final String EXCEL03_EXTENSION = ".xls";
// excel2007扩展名
public static final String EXCEL07_EXTENSION = ".xlsx";
/**
* 读取Excel文件，可能是03也可能是07版本
*
* @param excel03
* @param excel07
* @param fileName
* @throws Exception
*/
public static void readExcel(IRowReader reader, String fileName) throws Exception {
// 处理excel2003文件
if (fileName.endsWith(EXCEL03_EXTENSION)) {// excel2003扩展名 .xls
Excel2003Reader excel03 = new Excel2003Reader();
excel03.setRowReader(reader);
excel03.process(fileName);
// 处理excel2007文件
} else if (fileName.endsWith(EXCEL07_EXTENSION)) {// excel2007扩展名 .xlsx
Excel2007Reader excel07 = new Excel2007Reader();
excel07.setRowReader(reader);
excel07.process(fileName);
} else {
throw new Exception("文件格式错误，fileName的扩展名只能是xls或xlsx。");
}
}
}2.创建Excel2003Reader.java
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder.SheetRecordCollectingListener;
import org.apache.poi.hssf.eventusermodel.FormatTrackingHSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFEventFactory;
import org.apache.poi.hssf.eventusermodel.HSSFListener;
import org.apache.poi.hssf.eventusermodel.HSSFRequest;
import org.apache.poi.hssf.eventusermodel.MissingRecordAwareHSSFListener;
import org.apache.poi.hssf.eventusermodel.dummyrecord.LastCellOfRowDummyRecord;
import org.apache.poi.hssf.eventusermodel.dummyrecord.MissingCellDummyRecord;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.record.BOFRecord;
import org.apache.poi.hssf.record.BlankRecord;
import org.apache.poi.hssf.record.BoolErrRecord;
import org.apache.poi.hssf.record.BoundSheetRecord;
import org.apache.poi.hssf.record.FormulaRecord;
import org.apache.poi.hssf.record.LabelRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.NumberRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.SSTRecord;
import org.apache.poi.hssf.record.StringRecord;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
* 抽象Excel2003读取器，通过实现HSSFListener监听器，采用事件驱动模式解析excel2003 中的内容，遇到特定事件才会触发，大大减少了内存的使用。
*/
public class Excel2003Reader implements HSSFListener {
private int minColumns = -1;
private POIFSFileSystem fs;
private int lastRowNumber;
private int lastColumnNumber;
/** Should we output the formula, or the value it has? */
private boolean outputFormulaValues = true;
/** For parsing Formulas */
private SheetRecordCollectingListener workbookBuildingListener;
// excel2003工作薄
private HSSFWorkbook stubWorkbook;
// Records we pick up as we process
private SSTRecord sstRecord;
private FormatTrackingHSSFListener formatListener;
// 表索引
private int sheetIndex = -1;
private BoundSheetRecord[] orderedBSRs;
@SuppressWarnings("rawtypes")
private ArrayList boundSheetRecords = new ArrayList();
// For handling formulas with string results
private int nextRow;
private int nextColumn;
private boolean outputNextStringRecord;
// 当前行
private int curRow = 0;
// 存储行记录的容器
private List<String> rowlist = new ArrayList<String>();;
@SuppressWarnings("unused")
private String sheetName;
private IRowReader rowReader;
public void setRowReader(IRowReader rowReader) {
this.rowReader = rowReader;
}
/**
* 遍历excel下所有的sheet
*
* @throws IOException
*/
public void process(String fileName) throws IOException {
this.fs = new POIFSFileSystem(new FileInputStream(fileName));
MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
formatListener = new FormatTrackingHSSFListener(listener);
HSSFEventFactory factory = new HSSFEventFactory();
HSSFRequest request = new HSSFRequest();
if (outputFormulaValues) {
request.addListenerForAllRecords(formatListener);
} else {
workbookBuildingListener = new SheetRecordCollectingListener(formatListener);
request.addListenerForAllRecords(workbookBuildingListener);
}
factory.processWorkbookEvents(request, fs);
}
/**
* HSSFListener 监听方法，处理 Record
*/
@SuppressWarnings("unchecked")
public void processRecord(Record record) {
int thisRow = -1;
int thisColumn = -1;
String thisStr = null;
String value = null;
switch (record.getSid()) {
case BoundSheetRecord.sid:
boundSheetRecords.add(record);
break;
case BOFRecord.sid:
BOFRecord br = (BOFRecord) record;
if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
// 如果有需要，则建立子工作薄
if (workbookBuildingListener != null && stubWorkbook == null) {
stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();
}
sheetIndex++;
if (orderedBSRs == null) {
orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);
}
sheetName = orderedBSRs[sheetIndex].getSheetname();
}
break;
case SSTRecord.sid:
sstRecord = (SSTRecord) record;
break;
case BlankRecord.sid:
BlankRecord brec = (BlankRecord) record;
thisRow = brec.getRow();
thisColumn = brec.getColumn();
thisStr = "";
rowlist.add(thisColumn, thisStr);
break;
case BoolErrRecord.sid: // 单元格为布尔类型
BoolErrRecord berec = (BoolErrRecord) record;
thisRow = berec.getRow();
thisColumn = berec.getColumn();
thisStr = berec.getBooleanValue() + "";
rowlist.add(thisColumn, thisStr);
break;
case FormulaRecord.sid: // 单元格为公式类型
FormulaRecord frec = (FormulaRecord) record;
thisRow = frec.getRow();
thisColumn = frec.getColumn();
if (outputFormulaValues) {
if (Double.isNaN(frec.getValue())) {
// Formula result is a string
// This is stored in the next record
outputNextStringRecord = true;
nextRow = frec.getRow();
nextColumn = frec.getColumn();
} else {
thisStr = formatListener.formatNumberDateCell(frec);
}
} else {
thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
}
rowlist.add(thisColumn, thisStr);
break;
case StringRecord.sid:// 单元格中公式的字符串
if (outputNextStringRecord) {
// String for formula
StringRecord srec = (StringRecord) record;
thisStr = srec.getString();
thisRow = nextRow;
thisColumn = nextColumn;
outputNextStringRecord = false;
}
break;
case LabelRecord.sid:
LabelRecord lrec = (LabelRecord) record;
curRow = thisRow = lrec.getRow();
thisColumn = lrec.getColumn();
value = lrec.getValue().trim();
value = value.equals("") ? " " : value;
this.rowlist.add(thisColumn, value);
break;
case LabelSSTRecord.sid: // 单元格为字符串类型
LabelSSTRecord lsrec = (LabelSSTRecord) record;
curRow = thisRow = lsrec.getRow();
thisColumn = lsrec.getColumn();
if (sstRecord == null) {
rowlist.add(thisColumn, " ");
} else {
value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();
value = value.equals("") ? " " : value;
rowlist.add(thisColumn, value);
}
break;
case NumberRecord.sid: // 单元格为数字类型
NumberRecord numrec = (NumberRecord) record;
curRow = thisRow = numrec.getRow();
thisColumn = numrec.getColumn();
value = formatListener.formatNumberDateCell(numrec).trim();
value = value.equals("") ? " " : value;
// 向容器加入列值
rowlist.add(thisColumn, value);
break;
default:
break;
}
// 遇到新行的操作
if (thisRow != -1 && thisRow != lastRowNumber) {
lastColumnNumber = -1;
}
// 空值的操作
if (record instanceof MissingCellDummyRecord) {
MissingCellDummyRecord mc = (MissingCellDummyRecord) record;
curRow = thisRow = mc.getRow();
thisColumn = mc.getColumn();
rowlist.add(thisColumn, " ");
}
// 更新行和列的值
if (thisRow > -1)
lastRowNumber = thisRow;
if (thisColumn > -1)
lastColumnNumber = thisColumn;
// 行结束时的操作
if (record instanceof LastCellOfRowDummyRecord) {
if (minColumns > 0) {
// 列值重新置空
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
}
lastColumnNumber = -1;
// 每行结束时， 调用getRows() 方法
rowReader.getRows(sheetIndex, curRow, rowlist);
// 清空容器
rowlist.clear();
}
}
}3.创建Excel2007Reader.java
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
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;
/**
* 抽象Excel2007读取器，excel2007的底层数据结构是xml文件，采用SAX的事件驱动的方法解析 xml，需要继承DefaultHandler，在遇到文件内容时，事件会触发，这种做法可以大大降低 内存的耗费，特别使用于大数据量的文件。
*/
public class Excel2007Reader extends DefaultHandler {
// 共享字符串表
private SharedStringsTable sst;
// 上一次的内容
private String lastContents;
private boolean nextIsString;
private int sheetIndex = -1;
private List<String> rowlist = new ArrayList<String>();
// 当前行
private int curRow = 0;
// 当前列
private int curCol = 0;
private IRowReader rowReader;
public void setRowReader(IRowReader rowReader) {
this.rowReader = rowReader;
}
/**
* 只遍历一个电子表格，其中sheetId为要遍历的sheet索引，从1开始，1-3
*
* @param filename
* @param sheetId
* @throws Exception
*/
public void processOneSheet(String filename, int sheetId) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
// 根据 rId# 或 rSheet# 查找sheet
InputStream sheet2 = r.getSheet("rId" + sheetId);
sheetIndex++;
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
sheet2.close();
}
/**
* 遍历工作簿中所有的电子表格
*
* @param filename
* @throws Exception
*/
public void process(String filename) throws Exception {
try {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> sheets = r.getSheetsData();
while (sheets.hasNext()) {
curRow = 0;
sheetIndex++;
InputStream sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
} catch (Exception e) {
e.printStackTrace();
throw new IllegalArgumentException("读取文件失败，此文件可能已损坏，请参照模板重新上传！");
}
}
public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
return parser;
}
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
// c => 单元格
if ("c".equals(name)) {
// 如果下一个元素是 SST 的索引，则将nextIsString标记为true
String cellType = attributes.getValue("t");
if ("s".equals(cellType)) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// 置空
lastContents = "";
}
public void endElement(String uri, String localName, String name) throws SAXException {
// 根据SST的索引值的到单元格的真正要存储的字符串
// 这时characters()方法可能会被调用多次
if (nextIsString) {
try {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
} catch (Exception e) {
}
}
// v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
// 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
if ("v".equals(name)) {
String value = lastContents.trim();
value = value.equals("") ? " " : value;
rowlist.add(curCol, value);
curCol++;
} else {
// 如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
if (name.equals("row")) {
rowReader.getRows(sheetIndex, curRow, rowlist);
rowlist.clear();
curRow++;
curCol = 0;
}
}
}
public void characters(char[] ch, int start, int length) throws SAXException {
// 得到单元格内容的值
lastContents += new String(ch, start, length);
}
}4.创建IRowReader.java
import java.util.List;
public interface IRowReader {
/**
* 业务逻辑实现方法
*
* @param sheetIndex
* @param curRow
* @param rowlist
*/
public void getRows(int sheetIndex, int curRow, List<String> rowlist);
}
5.自定义RowReader.java
import java.util.List;
public class RowReader implements IRowReader {
@Override
public void getRows(int sheetIndex, int curRow, List<String> rowlist) {
// sheetIndex---第几个sheet（从0开始）
// curRow---第几行（从0开始）
// rowlist---该行的单元格数据list
System.out.println(rowlist.toString());
}
}需要注意的地方：
a.有的日期拿到的可能不是字符串格式的日期，03版本的拿到的正常。如：单元格内是“2019-1-24”，拿到以后是43489。1899-12-30往后推43489天，即是2019-1-24
注意：日期那到的除了是一串整数外，还可能拿到的是如：43489.4581554
解析方式：第一步:获取小数点前的整数，按照上述方式获取那么多天后的日期；第二步：获取小数点后的数，并拼接0.后成0.4581554（示例，仅供参考），一天有24*60*60秒，24*60*60*0.4581554后四舍五入，取整数为39585秒（示例，仅供参考）。第三步，将第一步所获取的日期时间往后推39585秒即是对应的日期。
下图中1440是24*60，项目中的日期格式只精确到年月日时分
下图中则未按照上述方式处理，是由于项目中日期格式为年月日
注意try...catch...中代码顺序，try内为excel2007时间处理代码，catch内为excel2003时间处理代码，请勿写反！
b.有的excel标题行可能有10列，但是内容行取到的值组成的rowlist.size()可能只有6列，故在做数据处理时需要特别注意。
6.代码中使用方法，如下图所示：
扫一扫
2952
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
