一、导入pom依赖
<poi-version>4.0.0</poi-version>
<!-- excel poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>${poi-version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>${poi-version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>${poi-version}</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>${poi-version}</version>
</dependency>
二、导入
1、导入工具类
import com.base.infrastructure.exception.ServiceException;
import com.base.infrastructure.utility.MapUtility;
import com.base.infrastructure.utility.ServiceCheckUtility;
import com.base.infrastructure.utility.StringUtility;
import com.base.infrastructure.utility.excel.analysis.ExcelXlsReader;
import com.base.infrastructure.utility.excel.analysis.ExcelXlsxReader;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* excel2003、2007大数据量解析
*/
public class BigExcelReaderUtil {
//excel2003扩展名
private static final String XLS_LOWER_CASE = "xls";
private static final String XLSX_LOWER_CASE = "xlsx";
//excel2007扩展名
private static final String XLS_UPPER_CASE = "XLS";
private static final String XLSX_UPPER_CASE = "XLSX";
/**
* 根据文件路径解析
*
* @param path
* @return
* @throws Exception
*/
public static Map<String, Object> readExcelPath(String path, int headerIncex, int dataIndex)
throws Exception {
//解析数据返回map
Map<String, Object> map = new HashMap<>();
//表头内容
List<String> header = new ArrayList<>();
//数据内容
//List<List<String>> data = new ArrayList<>();
Map<Integer, List<List<String>>> listMap = new HashMap<>();
//处理excel2003文件
if (path.endsWith(XLS_LOWER_CASE) || path.endsWith(XLS_UPPER_CASE)) {
ExcelXlsReader excelXls = new ExcelXlsReader() {
@Override
public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow, List<String> cellList) {
//ServiceCheckUtility.errorCheck(sheetIndex > 1, "请删除或清除多余的sheet页，只保留一个正确的sheet页");
header.addAll(cellList);
}
@Override
public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow, List<String> cellList) {
List<String> tempList = new ArrayList<>();
tempList.addAll(cellList);
if (listMap.get(sheetIndex) == null) {
List<List<String>> data = new ArrayList<>();
data.add(tempList);
listMap.put(sheetIndex, data);
} else {
listMap.get(sheetIndex).add(tempList);
}
}
};
excelXls.process(path, headerIncex, dataIndex);
}
//处理excel2007文件
else if (path.endsWith(XLSX_LOWER_CASE) || path.endsWith(XLSX_UPPER_CASE)) {
ExcelXlsxReader excelXlsxReader = new ExcelXlsxReader() {
@Override
public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow, List<String> cellList) {
//ServiceCheckUtility.errorCheck(sheetIndex > 1, "请删除或清除多余的sheet页，只保留一个正确的sheet页");
header.addAll(cellList);
}
@Override
public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow, List<String> cellList) {
List<String> tempList = new ArrayList<>();
tempList.addAll(cellList);
if (listMap.get(sheetIndex) == null) {
List<List<String>> data = new ArrayList<>();
data.add(tempList);
listMap.put(sheetIndex, data);
} else {
listMap.get(sheetIndex).add(tempList);
}
}
};
excelXlsxReader.process(path, headerIncex, dataIndex);
} else {
throw new Exception("文件格式错误，上传的excel的扩展名只能是xls或xlsx。");
}
map.put("data", listMap);
map.put("head", header);
return map;
}
/**
* 根据MultipartFile文件对象解析
*
* @param file
* @return
* @throws Exception
*/
public static Map<String, Object> readExcelFile(MultipartFile file, int headerIncex, int dataIndex)
throws Exception {
//文件判空
if (file == null) {
return new HashMap<>();
}
//获取后缀名判断是否为excel
String suffix = file.getOriginalFilename().substring(file.getOriginalFilename().lastIndexOf(".") + 1);
if (isNotExcelPath(suffix)) {
return new HashMap<>();
}
//解析数据返回map
Map<String, Object> map = new HashMap<>();
//表头内容
List<String> header = new ArrayList<>();
//数据内容
List<List<String>> data = new ArrayList<>();
InputStream inputStream = null;
try {
inputStream = file.getInputStream();
if (suffix.endsWith(XLS_LOWER_CASE) || suffix.endsWith(XLS_UPPER_CASE)) {
//处理excel2003文件
ExcelXlsReader excelXls = new ExcelXlsReader() {
@Override
public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow,
List<String> cellList) {
ServiceCheckUtility.errorCheck(sheetIndex > 1, "请删除或清除多余的sheet页，只保留一个正确的sheet页");
header.addAll(cellList);
}
@Override
public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow,
List<String> cellList) {
List<String> tempList = new ArrayList<>();
tempList.addAll(cellList);
data.add(tempList);
}
};
excelXls.process(inputStream, headerIncex, dataIndex);
} else if (suffix.endsWith(XLSX_LOWER_CASE) || suffix.endsWith(XLSX_UPPER_CASE)) {
//处理excel2007文件
ExcelXlsxReader excelXlsx = new ExcelXlsxReader() {
@Override
public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow,
List<String> cellList) {
ServiceCheckUtility.errorCheck(sheetIndex > 1, "请删除或清除多余的sheet页，只保留一个正确的sheet页");
header.addAll(cellList);
}
@Override
public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow,
List<String> cellList) {
List<String> tempList = new ArrayList<>();
tempList.addAll(cellList);
data.add(tempList);
}
};
excelXlsx.process(inputStream, headerIncex, dataIndex);
} else {
throw new ServiceException("文件格式错误，上传的excel的扩展名只能是xls或xlsx。");
}
} finally {
if (inputStream != null) {
inputStream.close();
}
}
map.put("data", data);
map.put("head", header);
return map;
}
/**
* excel路劲检查
*
* @param path
* @return
*/
private static boolean isNotExcelPath(String path) {
if (StringUtility.isEmpty(path)) {
return true;
}
return !path.endsWith(XLS_LOWER_CASE) && !path.endsWith(XLSX_LOWER_CASE) && !path.endsWith(XLS_UPPER_CASE)
&& !path.endsWith(XLSX_UPPER_CASE);
}
}
2、2003版本解析
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.eventusermodel.EventWorkbookBuilder;
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.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
/**
* 用于解决.xls2003版本大数据量问题
*/
public class ExcelXlsReader implements HSSFListener {
private int minColums = -1;
private POIFSFileSystem fs;
/**
* 总行数
*/
private int totalRows = 0;
/**
* 上一行row的序号
*/
private int lastRowNumber;
/**
* 上一单元格的序号
*/
private int lastColumnNumber;
/**
* 是否输出formula，还是它对应的值
*/
private boolean outputFormulaValues = true;
/**
* 用于转换formulas
*/
private EventWorkbookBuilder.SheetRecordCollectingListener workbookBuildingListener;
//excel2003工作簿
private HSSFWorkbook stubWorkbook;
private SSTRecord sstRecord;
private FormatTrackingHSSFListener formatListener;
private final HSSFDataFormatter formatter = new HSSFDataFormatter();
/**
* 文件的绝对路径
*/
private String filePath = "";
//表索引
private int sheetIndex = 0;
private BoundSheetRecord[] orderedBSRs;
@SuppressWarnings({"rawtypes"})
private ArrayList boundSheetRecords = new ArrayList();
private int nextRow;
private int nextColumn;
private boolean outputNextStringRecord;
//当前行
private int curRow = 0;
//存储一行记录所有单元格的容器
private List<String> cellList = new ArrayList<String>();
/**
* 判断整行是否为空行的标记
*/
private boolean flag = false;
//定义列名的行索引
private int headerIncex = 1;
//定义数据的行索引
private int dataIndex = 2;
private String sheetName;
/**
* 根据文件流解析
*
* @param inputStream
* @param headerIncex
* @param dataIndex
* @return
* @throws Exception
*/
public int process(InputStream inputStream, int headerIncex, int dataIndex)
throws Exception {
this.fs = new POIFSFileSystem(inputStream);
return init(headerIncex, dataIndex);
}
/**
* 根据文件路径解析
* @param path
* @param headerIncex
* @param dataIndex
* @return
* @throws Exception
*/
public int process(String path, int headerIncex, int dataIndex)
throws Exception {
this.fs = new POIFSFileSystem(new FileInputStream(path));
return init(headerIncex, dataIndex);
}
/**
* 遍历excel下所有的sheet
*
* @param headerIncex
* @param dataIndex
* @return
* @throws Exception
*/
private int init(int headerIncex, int dataIndex)
throws Exception {
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 EventWorkbookBuilder.SheetRecordCollectingListener(formatListener);
request.addListenerForAllRecords(workbookBuildingListener);
}
if (headerIncex > 0)
//设置表头行
this.headerIncex = headerIncex;
if (dataIndex > 0)
//设置数据行
this.dataIndex = dataIndex;
factory.processWorkbookEvents(request, fs);
//返回该excel文件的总行数，不包括首列和空行
return totalRows;
}
/**
* 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:
//开始处理每个sheet
BOFRecord br = (BOFRecord)record;
if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
//如果有需要，则建立子工作簿
if (workbookBuildingListener != null && stubWorkbook == null) {
stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();
}
if (orderedBSRs == null) {
orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);
}
sheetName = orderedBSRs[sheetIndex].getSheetname();
sheetIndex++;
}
break;
case SSTRecord.sid:
sstRecord = (SSTRecord)record;
break;
case BlankRecord.sid:
//单元格为空白
BlankRecord brec = (BlankRecord)record;
thisRow = brec.getRow();
thisColumn = brec.getColumn();
thisStr = "";
cellList.add(thisColumn, thisStr);
break;
case BoolErrRecord.sid:
//单元格为布尔类型
BoolErrRecord berec = (BoolErrRecord)record;
thisRow = berec.getRow();
thisColumn = berec.getColumn();
thisStr = berec.getBooleanValue() + "";
cellList.add(thisColumn, thisStr);
//如果里面某个单元格含有值，则标识该行不为空行
checkRowIsNull(thisStr);
break;
case FormulaRecord.sid:
//单元格为公式类型
FormulaRecord frec = (FormulaRecord)record;
thisRow = frec.getRow();
thisColumn = frec.getColumn();
if (outputFormulaValues) {
if (Double.isNaN(frec.getValue())) {
outputNextStringRecord = true;
nextRow = frec.getRow();
nextColumn = frec.getColumn();
}
else {
thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
}
}
else {
thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
}
cellList.add(thisColumn, thisStr);
//如果里面某个单元格含有值，则标识该行不为空行
checkRowIsNull(thisStr);
break;
case StringRecord.sid:
//单元格中公式的字符串
if (outputNextStringRecord) {
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;
cellList.add(thisColumn, value.replaceAll("\n", ""));
//如果里面某个单元格含有值，则标识该行不为空行
checkRowIsNull(value);
break;
case LabelSSTRecord.sid:
//单元格为字符串类型
LabelSSTRecord lsrec = (LabelSSTRecord)record;
curRow = thisRow = lsrec.getRow();
thisColumn = lsrec.getColumn();
if (sstRecord == null) {
cellList.add(thisColumn, "");
}
else {
value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();
value = value.equals("") ? "" : value;
cellList.add(thisColumn, value.replaceAll("\n", ""));
//如果里面某个单元格含有值，则标识该行不为空行
checkRowIsNull(value);
}
break;
case NumberRecord.sid:
//单元格为数字类型
NumberRecord numrec = (NumberRecord)record;
curRow = thisRow = numrec.getRow();
thisColumn = numrec.getColumn();
//第一种方式 这个被写死，采用的m/d/yy h:mm格式，不符合要求
//value = formatListener.formatNumberDateCell(numrec).trim();
//第二种方式，参照formatNumberDateCell里面的实现方法编写
Double valueDouble = ((NumberRecord)numrec).getValue();
String formatString = formatListener.getFormatString(numrec);
/*if (formatString.contains("m/d/yy")) {
formatString = "yyyy-MM-dd hh:mm:ss";
}*/
int formatIndex = formatListener.getFormatIndex(numrec);
value = formatter.formatRawCellContents(valueDouble, formatIndex, formatString).trim();
value = value.equals("") ? "" : value;
//向容器加入列值
cellList.add(thisColumn, value.replaceAll("\n", ""));
//如果里面某个单元格含有值，则标识该行不为空行
checkRowIsNull(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();
cellList.add(thisColumn, "");
}
//更新行和列的值
if (thisRow > -1)
lastRowNumber = thisRow;
if (thisColumn > -1)
lastColumnNumber = thisColumn;
//行结束时的操作
if (record instanceof LastCellOfRowDummyRecord) {
if (minColums > 0) {
//列值重新置空
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
}
lastColumnNumber = -1;
if (curRow == headerIncex - 1) {
//表头行结束时，调用sendHeaderRows()方法
sendHeaderRows(filePath, sheetName, sheetIndex, curRow + 1, cellList);
}
//该行不为空行且该行为数据行
if (flag && curRow >= dataIndex - 1) {
//每行数据结束时，调用sendDataRows()方法
sendDataRows(filePath, sheetName, sheetIndex, curRow + 1, cellList);
totalRows++;
}
//清空容器
cellList.clear();
flag = false;
}
}
/**
* 如果里面某个单元格含有值，则标识该行不为空行
* @param value
*/
public void checkRowIsNull(String value) {
if (value != null && !"".equals(value)) {
flag = true;
}
}
/**
* 表头数据
* @param filePath
* @param sheetName
* @param sheetIndex
* @param curRow
* @param cellList
*/
public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow, List<String> cellList) {
}
/**
* 解析数据
* @param filePath
* @param sheetName
* @param sheetIndex
* @param curRow
* @param cellList
*/
public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow, List<String> cellList) {
}
}
3、2007及以上版本
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import com.base.infrastructure.log.LoggerManager;
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;
/**
* 2007版本
*/
public class ExcelXlsxReader extends DefaultHandler {
/**
* 单元格中的数据可能的数据类型
*/
enum CellDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
}
/**
* 共享字符串表
*/
private SharedStringsTable sst;
/**
* 上一次的索引值
*/
private String lastIndex;
/**
* 文件的绝对路径
*/
private String filePath = "";
/**
* 工作表索引
*/
private int sheetIndex = 0;
/**
* sheet名
*/
private String sheetName = "";
/**
* 总行数
*/
private int totalRows = 0;
/**
* 一行内cell集合
*/
private List<String> cellList = new ArrayList<String>();
/**
* 判断整行是否为空行的标记
*/
private boolean flag = false;
/**
* 当前行
*/
private int curRow = 1;
/**
* 前一个列
*/
private int lastCurCol = -1;
/**
* 当前列
*/
private int curCol = 0;
//定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
private int maxcurCol = 0;
// 定义当前读到的列与上一次读到的列中是否有空值（即该单元格什么也没有输入，连空格都不存在）默认为false
private boolean curflag = false;
/**
* T元素标识
*/
private boolean isTElement;
/**
* 异常信息，如果为空则表示没有异常
*/
private String exceptionMessage;
/**
* 单元格数据类型，默认为字符串类型
*/
private CellDataType nextDataType = CellDataType.SSTINDEX;
private final DataFormatter formatter = new DataFormatter();
/**
* 单元格日期格式的索引
*/
private short formatIndex;
/**
* 日期格式字符串
*/
private String formatString;
//定义列名的行索引
private int headerIncex = 1;
//定义数据的行索引
private int dataIndex = 2;
/**
* 单元格
*/
private StylesTable stylesTable;
/**
* 根据文件路径解析
*
* @param path
* @param headerIncex
* @param dataIndex
* @return
* @throws Exception
*/
public int process(String path, int headerIncex, int dataIndex)
throws Exception {
OPCPackage pkg = OPCPackage.open(path);
return init(pkg, headerIncex, dataIndex);
}
/**
* 根据文件流解析（占用内存消耗比文件路径解析大）
*
* @param inputStream
* @param headerIncex
* @param dataIndex
* @return
* @throws Exception
*/
public int process(InputStream inputStream, int headerIncex, int dataIndex)
throws Exception {
OPCPackage pkg = OPCPackage.open(inputStream);
return init(pkg, headerIncex, dataIndex);
}
/**
* 遍历工作簿中所有的电子表格
* 并缓存在mySheetList中
*
* @param pkg
* @param headerIncex
* @param dataIndex
* @return
* @throws Exception
*/
private int init(OPCPackage pkg, int headerIncex, int dataIndex)
throws Exception {
XSSFReader xssfReader = new XSSFReader(pkg);
stylesTable = xssfReader.getStylesTable();
SharedStringsTable sst = xssfReader.getSharedStringsTable();
XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
while (sheets.hasNext()) { //遍历sheet
curRow = 1; //标记初始行为第一行
if (headerIncex > 0)
this.headerIncex = headerIncex; //设置表头行
if (dataIndex > 0)
this.dataIndex = dataIndex; //设置数据行
sheetIndex++;
InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错
sheetName = sheets.getSheetName();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
sheet.close();
}
pkg.close();
return totalRows; //返回该excel文件的总行数，不包括首列和空行
}
/**
* 第一个执行
*
* @param uri
* @param localName
* @param name
* @param attributes
* @throws SAXException
*/
@Override
public void startElement(String uri, String localName, String name, Attributes attributes)
throws SAXException {
//c => 单元格
if ("c".equals(name)) {
//当前单元格的位置
String r = attributes.getValue("r");
int firstDigit = -1;
for (int c = 0; c < r.length(); ++c) {
if (Character.isDigit(r.charAt(c))) {
firstDigit = c;
break;
}
}
curCol = nameToColumn(r.substring(0, firstDigit));//获取当前读取的列数
//设定单元格类型
this.setNextDataType(attributes);
}
//当元素为t时
if ("t".equals(name)) {
isTElement = true;
} else {
isTElement = false;
}
//置空
lastIndex = "";
}
/**
* 第二个执行
* 得到单元格对应的索引值或是内容值
* 如果单元格类型是字符串、INLINESTR、数字、日期，lastIndex则是索引值
* 如果单元格类型是布尔值、错误、公式，lastIndex则是内容值
*
* @param ch
* @param start
* @param length
* @throws SAXException
*/
@Override
public void characters(char[] ch, int start, int length)
throws SAXException {
lastIndex += 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 {
//t元素也包含字符串
if (isTElement) {//这个程序没经过
//将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
String value = lastIndex.trim();
cellList.add(curCol, value.replaceAll("\n", ""));
isTElement = false;
//如果里面某个单元格含有值，则标识该行不为空行
if (value != null && !"".equals(value)) {
flag = true;
}
} else if ("v".equals(name)) {
//v => 单元格的值，如果单元格是字符串，则v标签的值为该字符串在SST中的索引
String value = this.getDataValue(lastIndex.trim(), "");//根据索引值获取对应的单元格值
//补全单元格之间的空单元格
if (curCol - lastCurCol > 1) {
curflag = true;
}
for (int i = lastCurCol; i < curCol; ++i) {
if (curflag && i > lastCurCol) {
cellList.add(i, "");
}
}
if (curCol > -1) {
lastCurCol = curCol;
}
cellList.add(curCol, value.replaceAll("\n", ""));
//如果里面某个单元格含有值，则标识该行不为空行
if (value != null && !"".equals(value.trim())) {
flag = true;
}
} else {
//如果标签名称为row，这说明已到行尾，调用optRows()方法
if ("row".equals(name)) {
//默认第一行为表头，以该行单元格数目为最大数目
if (curRow == headerIncex) {
sendHeaderRows(filePath, sheetName, sheetIndex, curRow, cellList);
maxcurCol = cellList.size() - 1;
}
//补全一行尾部可能缺失的单元格
if (maxcurCol > lastCurCol) {
curflag = true;
}
for (int i = lastCurCol; i < maxcurCol; ++i) {
if (curflag && flag) {
cellList.add(i, "");
}
}
if (flag && curRow >= dataIndex) { //该行不为空行且该行不是第一行，则发送（第一行为列名，不需要）
sendDataRows(filePath, sheetName, sheetIndex, curRow, cellList);
totalRows++;
}
cellList.clear();
curRow++;
curCol = 0;
lastCurCol = -1;
flag = false;
}
}
}
/**
* 处理数据类型
*
* @param attributes
*/
public void setNextDataType(Attributes attributes) {
nextDataType = CellDataType.NUMBER; //cellType为空，则表示该单元格类型为数字
formatIndex = -1;
formatString = null;
String cellType = attributes.getValue("t"); //单元格类型
String cellStyleStr = attributes.getValue("s"); //
//String columnData = attributes.getValue("r"); //获取单元格的位置，如A1,B1
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 (formatString == null) {
nextDataType = CellDataType.NULL;
formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
} else if (formatString.contains("m/d/yy")) {
nextDataType = CellDataType.DATE;
formatString = "yyyy-MM-dd hh:mm:ss";
}
}
}
/**
* 对解析出来的数据进行类型处理
*
* @param value 单元格的值，
* value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值，
* SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值
* @param thisStr 一个空字符串
* @return
*/
public String getDataValue(String value, String thisStr) {
switch (nextDataType) {
// 这几个的顺序不能随便交换，交换了很可能会导致数据错误
case BOOL: //布尔值
char first = value.charAt(0);
thisStr = first == '0' ? "FALSE" : "TRUE";
break;
case ERROR: //错误
thisStr = "\"ERROR:" + value.toString() + '"';
break;
case FORMULA: //公式
thisStr = '"' + value.toString() + '"';
break;
case INLINESTR:
XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
thisStr = rtsi.toString();
rtsi = null;
break;
case SSTINDEX: //字符串
String sstIndex = value.toString();
try {
int idx = Integer.parseInt(sstIndex);
XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));//根据idx索引值获取内容值
thisStr = rtss.toString();
rtss = null;
} catch (NumberFormatException ex) {
thisStr = value.toString();
}
break;
case NUMBER: //数字
if (formatString != null) {
thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
} else {
thisStr = value;
}
thisStr = thisStr.replace("_", "").trim();
break;
case DATE: //日期
thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
// 对日期字符串作特殊处理，去掉T
thisStr = thisStr.replace("T", " ");
break;
default:
thisStr = " ";
break;
}
return thisStr;
}
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;
}
public 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;
}
private int nameToColumn(String name) {
int column = -1;
for (int i = 0; i < name.length(); ++i) {
int c = name.charAt(i);
column = (column + 1) * 26 + c - 'A';
}
return column;
}
/**
* @return the exceptionMessage
*/
public String getExceptionMessage() {
return exceptionMessage;
}
/**
* 表头数据
*
* @param filePath
* @param sheetName
* @param sheetIndex
* @param curRow
* @param cellList
*/
public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow, List<String> cellList) {
}
/**
* 解析数据
*
* @param filePath
* @param sheetName
* @param sheetIndex
* @param curRow
* @param cellList
*/
public void sendDataRows(String filePath, String sheetName, int sheetIndex, int curRow, List<String> cellList) {
}
}
三、导出
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
/**
* EXCEL导出
*/
public class BigExcelExportUtil {
/**
* 私有构造函数
*/
private BigExcelExportUtil() {
}
/**
* 构造EXCEL,并响应客户端
* <功能详细描述>
*
* @param listValues
* @param fileName
* @param sheetName
* @param cellTitle
* @param cellValue
* @param res
* @see [类、类#方法、类#成员]
*/
public static void buildXSLXExcel(List<Map<String, Object>> listValues, String fileName, String sheetName, String[] cellTitle,
String[] cellValue, HttpServletResponse res) {
byte[] bytes = buildXSLXExcelWithSheetName(listValues, sheetName, cellTitle, cellValue);
if (0 == bytes.length) {
return;
}
writeDocResponse(res, bytes, fileName);
}
/**
* 构建EXCEL
* <功能详细描述>
*
* @param listValues
* @param sheetName
* @param cellTitle
* @param cellValue
* @see [类、类#方法、类#成员]
*/
public static byte[] buildXSLXExcelWithSheetName(List<Map<String, Object>> listValues, String sheetName, String[] cellTitle,
String[] cellValue) {
try (ByteArrayOutputStream outStream = new ByteArrayOutputStream();
SXSSFWorkbook workBook = createExcelBook(listValues, sheetName, cellTitle, cellValue);) {
workBook.write(outStream);
outStream.flush();
workBook.dispose();
return outStream.toByteArray();
} catch (Exception e) {
e.printStackTrace();
return new byte[0];
}
}
/**
* 生成WorkBook
* <功能详细描述>
*
* @param listValues
* @param sheetName
* @param cellTitle
* @param cellValue
* @return
* @see [类、类#方法、类#成员]
*/
private static SXSSFWorkbook createExcelBook(List<Map<String, Object>> listValues, String sheetName, String[] cellTitle,
String[] cellValue) {
//创建工作薄
SXSSFWorkbook workBook = new SXSSFWorkbook();
Sheet sheet = workBook.createSheet();
//工作簿名称
workBook.setSheetName(0, sheetName);
//字体设置
Font font = workBook.createFont();
font.setColor(XSSFFont.COLOR_NORMAL);
//font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
font.setBold(true);
//创建格式
XSSFCellStyle cellStyle = (XSSFCellStyle) workBook.createCellStyle();
cellStyle.setFont(font);
//cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//创建第一行标题
Row titleRow = sheet.createRow((short) 0);
for (int i = 0; i < cellTitle.length; i++) {
//创建第1行标题单元格
sheet.setColumnWidth(i, 20 * 256);
//XSSFCell cell = titleRow.createCell(i, 0);
Cell cell = titleRow.createCell(i, CellType.STRING);
cell.setCellStyle(cellStyle);
//cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellType(CellType.STRING);
cell.setCellValue(cellTitle[i]);
}
//第二行开始写入数据
if (listValues != null && listValues.size() > 0) {
//创建格式
XSSFCellStyle style = (XSSFCellStyle) workBook.createCellStyle();
//遍历列表数据
for (int i = 0; i < listValues.size(); i++) {
Row row = sheet.createRow(i + 1);
setRow(cellTitle, cellValue, workBook, sheet, style, listValues.get(i), i, row);
}
}
return workBook;
}
private static void setRow(String[] cellTitle, String[] cellValue,
SXSSFWorkbook workBook, Sheet sheet, XSSFCellStyle style, Map<String, Object> map, int line, Row row) {
for (int j = 0; j < cellTitle.length; j++) {
// 在上面行索引0的位置创建单元格
//XSSFCell cell = titleRow.createCell(i, 0);
Cell cell = row.createCell(j, CellType.STRING);
// 定义单元格为字符串类型
//cell.setCellType(XSSFCell.CELL_TYPE_STRING);
cell.setCellType(CellType.STRING);
//取出列表值
cell.setCellValue(getMapString(map, cellValue[j]));
//style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
style.setAlignment(HorizontalAlignment.CENTER);
cell.setCellStyle(style);
}
}
/**
* 输出文件到客户端
* <功能详细描述>
*
* @param rsp http响应对象
* @param bytes
* @param fileName
* @see [类、类#方法、类#成员]
*/
public static void writeDocResponse(HttpServletResponse rsp, byte[] bytes, String fileName) {
try (OutputStream out = rsp.getOutputStream();) {
//扩展名获取ContentType
rsp.setContentType("application/vnd.ms-excel");
String fileNameURL = URLEncoder.encode(fileName, "UTF-8");
String contentHeader = "attachment; filename=" + fileNameURL + ";" + "filename*=utf-8''" + fileNameURL;
rsp.setHeader("Content-disposition", contentHeader);
//文件写入
out.write(bytes, 0, bytes.length);
} catch (Exception e) {
e.printStackTrace();
}
}
public static String getMapString(Map map, String key) {
return map.get(key) == null ? null : String.valueOf(map.get(key));
}
}