目录
1.mavn jar
<!-- excel poi jar -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!-- excel poi（编目-excel导入解析使用-fengwei添加） -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!-- excel 解析大数据 -->
<dependency>
<groupId>xerces</groupId>
<artifactId>xercesImpl</artifactId>
<version>2.11.0</version>
</dependency>2 工具类代码
2.1 ExcelUtil
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
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.usermodel.WorkbookFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.tengfan.system.utils.ConfigUtil;
/**
* @Title ExcelUtil.java
* @Description excel 导入导出
*/
public class ExcelUtil {
public static String FILE_SAVE_ROOT_PATH = "D:";// 默认根目录导出到E盘
public static int createRowIndex = 0;// excel创建行下标,用于控制从哪行开始填写数据
public static int SheetMaxRows = 50000;// excel每一个sheet最大存储行数(excel sheet有最大行数问题)
private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
static {
// 初始化根目录:改为读取配置
try {
//config.properties文件中配置的文件物理存储目录（local_path=E:/res）
FILE_SAVE_ROOT_PATH = ConfigUtil.get("local_path");
if (StringUtils.isNotBlank(FILE_SAVE_ROOT_PATH) && FILE_SAVE_ROOT_PATH.contains("/res")) {
FILE_SAVE_ROOT_PATH = FILE_SAVE_ROOT_PATH.replaceAll("/res", "");
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
*
* @Title exportExcel
* @Description
* @param datalist 写入的数据列表 [{"title":"test","img":"img"}...]
* @param headList excel表头列表[{"name":"title(对应数据列表中key)","label":"标题","datatype": "数据类型(number,date,money,string)"]...}
* @param excelName
* @param savePath 存入的相对目录 例如：/res/excel
* @return
* @version
*/
public static ExcelMsg exportExcel(List<Map<String, Object>> datalist, List<Map<String, Object>> headList, String excelName, String savePath) {
ExcelMsg msg = new ExcelMsg();
FileOutputStream fout = null;
try {
// 校验excel数据
msg = validateExcel(datalist, headList);
if (!msg.isSuccess()) {
return msg;
}
// 第一步，创建一个webbook，对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 第二步，在webbook中添加sheet,datalist.size()>SheetMaxRows,需要进行分割在不同的sheet中
int sheetNum = (int) Math.ceil(datalist.size() / Double.valueOf(SheetMaxRows));// sheet个数
for (int i = 1; i <= sheetNum; i++) {
HSSFSheet sheet=null;
if(sheetNum>1){
sheet = wb.createSheet(getExcelName("sheet", excelName, i + ""));
}else{
sheet = wb.createSheet(getExcelName("sheet", excelName, null));
}
// 第三步，创建标题
createExcelTitle(headList, sheet, wb, excelName);
// 第四步，创建表头
createExcelHead(headList, sheet, wb);
// 第五步，创建表数据
int formIndex = (i - 1) * SheetMaxRows;
int toIndex = i * SheetMaxRows >= datalist.size() ? datalist.size() : i * SheetMaxRows;
List<Map<String, Object>> sheetDataList = datalist.subList(formIndex, toIndex);
createExcelData(sheetDataList, headList, sheet, wb);
// excel创建行下标清零
createRowIndex = 0;
}
// 第六步，判断存放excel文件的文件夹是否存在，不存在则创建
File directory = new File(FILE_SAVE_ROOT_PATH + savePath);
if (!directory.exists()) {
directory.mkdirs();
}
// 第七步，写入本地
String excelPath = savePath + "/" + getExcelName("file", excelName, null);
String filePath = FILE_SAVE_ROOT_PATH + excelPath;
fout = new FileOutputStream(filePath);
wb.write(fout);
fout.close();
msg.setSuccess(true);
msg.setObj(excelPath);
msg.setMsg("Excel数据导出成功");
return msg;
} catch (Exception e) {
e.printStackTrace();
msg.setMsg("Excel数据导出失败");
return msg;
} finally {
// 关闭流
if (fout != null) {
try {
fout.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
*
* @Title getDefaultName
* @Description 获取excel默认名称
* @param type 类型 sheet-返回excel Sheet名称 file-返回excel文件名称 title-返回标题
* @param excelName excel名称
* @param suffixName 后缀名称
* @version
*/
private static String getExcelName(String type, String excelName, String suffixName) {
String result = excelName;
SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
if (excelName != null && excelName != "") {
if (type.trim().equalsIgnoreCase("sheet")) {
result = excelName;
} else if (type.trim().equalsIgnoreCase("file")) {
result = excelName + ".xls";
} else if (type.trim().equalsIgnoreCase("title")) {
result = excelName;
}
} else {
if (type.trim().equalsIgnoreCase("sheet")) {
result = sdf.format(new Date());
} else if (type.trim().equalsIgnoreCase("file")) {
result = sdf.format(new Date()) + ".xls";
} else if (type.trim().equalsIgnoreCase("title")) {
sdf = new SimpleDateFormat("yyyy年MM月dd日");
result = sdf.format(new Date()) + "Excel导出数据";
}
}
if (suffixName != null && !suffixName.trim().equals("")) {
result += suffixName;
}
return result;
}
/**
*
* @Title validateExcel
* @Description 校验excel
* @param datalist 数据list
* @param headList 表头list
* @return
* @version
*/
private static ExcelMsg validateExcel(List<Map<String, Object>> datalist, List<Map<String, Object>> headList) {
ExcelMsg msg = new ExcelMsg();
if (datalist == null || datalist.size() == 0) {
msg.setMsg("写入excel数据为空");
return msg;
}
if (headList == null || headList.size() == 0) {
msg.setMsg("写入excel表头数据为空");
return msg;
}
msg.setSuccess(true);
return msg;
}
/**
*
* @Title createExcelTitle
* @Description 创建excel内容标题
* @param headList
* @param sheet
* @param wb
* @param excelName
* @version
*/
private static void createExcelTitle(List<Map<String, Object>> headList, HSSFSheet sheet, HSSFWorkbook wb, String excelName) {
// 在索引0的位置创建行（最顶端的行）
HSSFRow row = sheet.createRow(createRowIndex);
row.setHeightInPoints(48);// 设置行高单位像素，excel 默认行高为13.5 像素值为： (13.5/72)*96=18
// 设置单元格格式
HSSFCellStyle cellStyle = setCellStyleByType("title", wb);
HSSFCell cell = null;
for (int i = 0; i < headList.size(); i++) {
cell = row.createCell(i);
cell.setCellStyle(cellStyle);
if (i <= 0) {
cell.setCellValue(getExcelName("title", excelName, null));
} else {
cell.setCellValue("");
}
}
// 合并第一行,设定合并单元格区域范围 firstRow 0-based lastRow 0-based firstCol 0-based
// lastCol
CellRangeAddress region = new CellRangeAddress(0, 0, 0, headList.size() - 1);
sheet.addMergedRegion(region);
createRowIndex++;
}
/**
*
* @Title createExcelHead
* @Description 创建excel表头
* @param headList excel表头列表 [{"name":"title","label":"标题","datatype":"数据类型
* @param sheet
* @param wb
* @version
*/
private static void createExcelHead(List<Map<String, Object>> headList, HSSFSheet sheet, HSSFWorkbook wb) {
// 在sheet中添加表头第1行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(createRowIndex);
row.setHeightInPoints(20);// 设置行高单位像素，excel 默认行高为13.5 像素值为：(13.5/72)*96=18
// 设置单元格格式
HSSFCellStyle cellStyle = setCellStyleByType("head", wb);
// 创建表头单元格
HSSFCell cell = null;
Map<String, Object> tempMap = null;
for (int i = 0; i < headList.size(); i++) {
tempMap = headList.get(i);
cell = row.createCell(i);
cell.setCellStyle(cellStyle);
cell.setCellValue(tempMap.get("label") + "");
// 设置列宽，api 段信息 Set the width (in units of 1/256th of a character
// width)
sheet.setColumnWidth(i, 25 * 256);
}
createRowIndex++;
}
/**
*
* @Title setCellStyleByType
* @Description 设置单元格格式
* @param type
* @param wb
* @return
* @version
*/
private static HSSFCellStyle setCellStyleByType(String type, HSSFWorkbook wb) {
// 创建单元格样式
HSSFCellStyle cellStyle = wb.createCellStyle();
// 设置字体
HSSFFont font = wb.createFont();
font.setFontName("宋体");
if (type.equals("title")) {
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 18);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
} else if (type.equals("head")) {
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 12);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
} else {
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font.setFontHeightInPoints((short) 12);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 创建一个居左格式
}
cellStyle.setFont(font);
cellStyle.setBottomBorderColor(HSSFColor.BLACK.index); // 设置单元格的边框颜色．
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
cellStyle.setWrapText(true);// 设置自动换行
return cellStyle;
}
/**
*
* @Title createExcelData
* @Description 创建excel列表数据
* @param datalist 写入的数据列表 [{"title":"test","img":"img"}...]
* @param headList excel表头列表 [{"name":"title","label":"标题","datatype":"数据类型"}]
* @param sheet 对应Excel文件中的sheet
* @param wb 对应一个Excel文件
* @version
*/
private static void createExcelData(List<Map<String, Object>> datalist, List<Map<String, Object>> headList, HSSFSheet sheet, HSSFWorkbook wb) {
Map<String, Object> tempMap = null;
HSSFRow row = null;
// 设置单元格格式
HSSFCellStyle cellStyle = setCellStyleByType("data", wb);
// 设置数据格式
HSSFDataFormat format = wb.createDataFormat();
for (int i = 0; i < datalist.size(); i++) {
tempMap = datalist.get(i);
row = sheet.createRow(i + createRowIndex);
row.setHeightInPoints(20);// 设置行高单位像素，excel 默认行高为13.5 像素值为：
// (13.5/72)*96=18
createExcelRowData(tempMap, headList, row, cellStyle, format);
}
}
/**
*
* @Title createExcelRowData
* @Description 创建excel某行数据
* @param rowData {"title":"test","img":"img",...}
* @param headList excel表头列表 [{"name":"title","label":"标题","datatype":"数据类型
* @param row
* @param cellStyle
* @param format
* @version
*/
private static void createExcelRowData(Map<String, Object> rowData, List<Map<String, Object>> headList, HSSFRow row, HSSFCellStyle cellStyle, HSSFDataFormat format) {
HSSFCell cell = null;
Map<String, Object> tempMap = null;
Object datatype = null;
Object cellData = null;
for (int i = 0; i < headList.size(); i++) {
cell = row.createCell(i);
tempMap = headList.get(i);
cellData = rowData.get(tempMap.get("name"));
datatype = tempMap.get("datatype");
setCellDataType(cell, datatype, cellData, cellStyle, format);
}
}
/**
*
* @Title setCellDataType
* @Description 设置单元格格式并赋值
* @param cell 单元格对象
* @param datatype 可以为number,date,money,string 单元格数据类型
* @param cellData 单元格数据
* @param cellStyle
* @param format
* @version
*/
private static void setCellDataType(HSSFCell cell, Object datatype, Object cellData, HSSFCellStyle cellStyle, HSSFDataFormat format) {
try {
// 没有数据值默认为“”
if (cellData == null) {
cell.setCellStyle(cellStyle);
cell.setCellValue("");
return;
}
// 没有指定数据类型，默认为字符型
if (datatype == null) {
cell.setCellStyle(cellStyle);
cell.setCellValue(cellData.toString());
return;
}
if (datatype.toString().trim().equalsIgnoreCase("number")) {
// 数字类型
// 判断data是否为数值型
boolean isNum = cellData.toString().matches("^(-?\\d+)(\\.\\d+)?$");
// 判断data是否为整数（小数部分是否为0）
boolean isInteger = cellData.toString().matches("^[-\\+]?[\\d]*$");
// 判断data是否为百分数（是否包含“%”）
boolean isPercent = cellData.toString().contains("%");
// 如果单元格内容是数值类型，涉及到金钱（金额、本、利），则设置cell的类型为数值型，设置data的类型为数值类型
if (isNum && !isPercent) {
if (isInteger) {
cellStyle.setDataFormat(format.getBuiltinFormat("#,#0"));// 数据格式只显示整数
} else {
cellStyle.setDataFormat(format.getBuiltinFormat("#,##0.00"));// 保留两位小数点
}
// 设置单元格格式
cell.setCellStyle(cellStyle);
// 设置单元格内容为double类型
cell.setCellValue(Double.parseDouble(cellData.toString()));
}
} else if (datatype.toString().trim().equalsIgnoreCase("date")) {
// 日期类型
cellStyle.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));
cell.setCellStyle(cellStyle);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Date date = sdf.parse(cellData.toString());
cell.setCellValue(date);
} else if (datatype.toString().trim().equalsIgnoreCase("money")) {
// 货币
cellStyle.setDataFormat(format.getFormat("¥#,##0"));
cell.setCellStyle(cellStyle);
cell.setCellValue(Double.parseDouble(cellData.toString()));
} else {
cell.setCellStyle(cellStyle);
cell.setCellValue(cellData.toString());
}
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 读取Excel返回的是map对象，其中key是一行，两行，行数，value是map,key是列， value是这行，这列对应的值, 包含第0行
*
* @Title: readExcel @param in 输入流 @throws ExcelParseException
* excel文件为空 @return Map<Integer,Map<Integer,String>>
* 返回的值只能采用Iterator 来进行循环，因为中间有可能会出现空行数据 @throws
*/
public static Map<Integer, Map<Integer, String>> readExcelInclude0Row(InputStream in) throws Exception {
return readExcel(in, true);
}
/**
* 读取Excel返回的是map对象，其中key是一行，两行，行数，value是map,key是列， value是这行，这列对应的值, 不包含第0行
*
* @Title: readExcel @param in 输入流 @throws ExcelParseException
* excel文件为空 @return Map<Integer,Map<Integer,String>>
* 返回的值只能采用Iterator 来进行循环，因为中间有可能会出现空行数据 @throws
*/
public static Map<Integer, Map<Integer, String>> readExcelNotInclude0Row(InputStream in) throws Exception {
return readExcel(in, false);
}
/**
* 读取Excel返回的是map对象，其中key是一行，两行，行数，value是map,key是列，
*
* @Title readExcelInclude0RowByBigData (文件后缀为.xlsx)
* @Description
* @param in 输入流
* @param startRow 数据获取起始行(下标从1开始)
* @param fillBlankColSize 需要填空列数
* @return Map<Integer,Map<Integer,String>>
* @throws Exception
* @version
*/
public static Map<Integer, Map<Integer, String>> readExcelInclude0RowByBigData(InputStream in, int startRow, int fillBlankColSize) throws Exception {
SaxReadExcelUtil read = new SaxReadExcelUtil();
return read.readExcel(in, startRow, fillBlankColSize);
}
private static Map<Integer, Map<Integer, String>> readExcel(InputStream in, boolean isInclude0Column) throws Exception {
Map<Integer, Map<Integer, String>> retValue = new HashMap<Integer, Map<Integer, String>>();
Workbook wb = getWorkbookByInputStream(in);
// 得到工作表格数量
int sheetNum = wb.getNumberOfSheets();
int loopHead = 0;
if (!isInclude0Column) {
loopHead = 1;
}
boolean isBlankExcel = true;
int currentNum = 0;
for (int i = 0; i < sheetNum; i++) {
Sheet childSheet = wb.getSheetAt(i);
// 得到工作表格中的行数
int rowNum = childSheet.getLastRowNum();
// 从第一行开始，第0行为表头，默认不读取
// 循环行
for (int j = loopHead; j <= rowNum; j++) {
final Map<Integer, String> columnContents = new HashMap<Integer, String>();
final Row row = childSheet.getRow(j);
// final int currentNum = j + 1;
if (row == null) {
// retValue.put(currentNum, columnContents);
continue;
}
// 得到列数
int cellNum = row.getLastCellNum();
// 标识非空行但是有一定格式的空白行
boolean isBlankRow = true;
for (int k = 0; k < cellNum; k++) {
Cell cell = row.getCell(k);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
isBlankRow = false;
break;
}
}
// 非空白行则循环列处理
if (!isBlankRow) {
for (int k = 0; k < cellNum; k++) {
String cellValue = "";
Cell cell = row.getCell(k);
if (cell != null) {
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
cellValue = new SimpleDateFormat("yyyy-MM-dd").format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
} else {
// 考虑到本系统中的导入的浮点数以金额居多,因此仅保留两位小数,整数取整(避免“.0”)
DecimalFormat decimalFormat = new DecimalFormat("#.##");
cellValue = decimalFormat.format(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING:
cellValue = cell.getStringCellValue();
break;
default:
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cellValue = cell.getStringCellValue();
break;
}
if (!cellValue.equals("") && cellValue != null) {
isBlankExcel = false;
}
}
columnContents.put(k, cellValue);
}
retValue.put(currentNum, columnContents);
currentNum++;
}
}
loopHead = 0;
if (!isInclude0Column)
loopHead = 1;
}
if (isBlankExcel)
throw new Exception("excel文件为空");
return retValue;
}
private static Workbook getWorkbookByInputStream(InputStream in) throws Exception {
Workbook wb = null;
try {
wb = create(in);
} catch (Exception e) {
logger.error(e.getMessage(), e);
throw new Exception(e);
}
return wb;
}
public static Workbook create(InputStream in) throws Exception {
Workbook ret = null;
try {
ret = WorkbookFactory.create(in);
} catch (IllegalArgumentException e) {
throw new Exception("Excel文件无法解析或版本过高！", e);
}catch (IllegalStateException e) {
String pattern = "The hyperlink for cell .* references relation .*, but that didn't exist!";
if(Pattern.matches(pattern, e.getMessage())){
String value1 = StringUtils.substringBetween(e.getMessage(), "The hyperlink for cell ", "references relation");
String value2 = StringUtils.substringBetween(e.getMessage(), "references relation ", ", but that didn't exist!");
throw new Exception("单元格"+value1+"的超链接引用了关系"+value2+"，但它不存在！",e);
}else{
throw new Exception(e);
}
}
return ret;
}
/**
*
* @Title main
* @Description 测试
* @param args
* @version
*/
public static void main(String[] args) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
List<Map<String, Object>> headList = new ArrayList<Map<String, Object>>();
Map<String, Object> headMap = new HashMap<String, Object>();
headMap.put("name", "title");
headMap.put("label", "标题");
headMap.put("datatype", "string");
headList.add(headMap);
Map<String, Object> headMap1 = new HashMap<String, Object>();
headMap1.put("name", "author");
headMap1.put("label", "作者");
headMap1.put("datatype", "string");
headList.add(headMap1);
Map<String, Object> headMap2 = new HashMap<String, Object>();
headMap2.put("name", "createDate");
headMap2.put("label", "创建日期");
headMap2.put("datatype", "date");
headList.add(headMap2);
Map<String, Object> headMap3 = new HashMap<String, Object>();
headMap3.put("name", "count");
headMap3.put("label", "计数");
headMap3.put("datatype", "number");
headList.add(headMap3);
List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
Map<String, Object> dataMap = new HashMap<String, Object>();
dataMap.put("title", "aa");
dataMap.put("author", "一山");
dataMap.put("createDate", sdf.format(new Date()));
dataMap.put("count", 1);
dataList.add(dataMap);
Map<String, Object> dataMap1 = new HashMap<String, Object>();
dataMap1.put("title", "好啊好啊");
dataMap1.put("createDate", sdf.format(new Date()));
dataMap1.put("count", 10);
dataList.add(dataMap1);
ExcelMsg msg = exportExcel(dataList, headList, "测试", "/res/excel");
System.out.println(msg.getMsg());
}
}
2.2 引用自写类
2.2.1 ExcelMsg
import java.io.Serializable;
/**
* @Title ExcelMsg.java
* @Description excel msg
*/
public class ExcelMsg implements Serializable {
private static final long serialVersionUID = 1L;
private String msg = "";
private boolean success = false;
private Object obj;// 存返回的对象
public String getMsg() {
return msg;
}
public void setMsg(String msg) {
this.msg = msg;
}
public boolean isSuccess() {
return success;
}
public void setSuccess(boolean success) {
this.success = success;
}
public Object getObj() {
return obj;
}
public void setObj(Object obj) {
this.obj = obj;
}
}
2.2.2 CellDataType
/**
* 单元格数据类型
* @Title CellDataType.java
* @Description 单元格数据类型
*/
public enum CellDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
}2.2.3 SaxReadExcelUtil
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.regex.Pattern;
import org.apache.commons.lang3.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.ContentHandler;
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;
/**
* sax excel解析大数据量
*
* @Title SaxReadExcelUtil.java
* @Description sax excel解析大数据量
*/
public class SaxReadExcelUtil {
/** excel数据 */
private static Map<Integer, Map<Integer, String>> dataMap;
/** 行数据 */
private static Map<Integer, String> rowMap;
private static StylesTable stylesTable;
/** 起始行 */
private int startRow;
/** 需要填空列数 */
private int fillBlankColSize = -1;
/** 当前行 */
private int currentRow = 0;
/** 导入excel列数 */
private int colSize = 0;
/**
* 读取excel
*
* @Title readExcel
* @Description
* @param filename 文件名 eg:E:/yade/xx123.xlsx
* @param startRow 起始行
* @param fillBlankColSize 需要填空列数
* @return Map<Integer, Map<Integer, String>>
* @throws Exception
* @version
*/
public Map<Integer, Map<Integer, String>> readExcel(String filename, int startRow, int fillBlankColSize) throws Exception {
dataMap = new HashMap<Integer, Map<Integer, String>>();
if (StringUtils.isBlank(filename)) {
throw new Exception("文件名为空");
}
this.startRow = startRow;
this.fillBlankColSize = fillBlankColSize;
processAllSheets(filename);
fillBlankCell();// 填补空单元格
return dataMap;
}
/**
* 读取excel
*
* @Title readExcel
* @Description
* @param in 输入流对象
* @param startRow 起始行
* @param fillBlankColSize 需要填空列数
* @return Map<Integer, Map<Integer, String>>
* @throws Exception
* @version
*/
public Map<Integer, Map<Integer, String>> readExcel(InputStream in, int startRow, int fillBlankColSize) throws Exception {
dataMap = new HashMap<Integer, Map<Integer, String>>();
if (in == null) {
throw new Exception("文件为空");
}
this.startRow = startRow;
this.fillBlankColSize = fillBlankColSize;
processAllSheets(in);
fillBlankCell();// 填补空单元格
return dataMap;
}
/**
* 获取所有的sheet
*
* @Title processAllSheets
* @Description 获取所有的sheet
* @param fileName 文件名 eg:E:/yade/xx123.xlsx
* @throws Exception
* @version
*/
private void processAllSheets(String fileName) throws Exception {
OPCPackage pkg = OPCPackage.open(fileName);
XSSFReader r = new XSSFReader(pkg);
stylesTable = r.getStylesTable();
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> it = r.getSheetsData();
while (it.hasNext()) {
rowMap = null;
InputStream sheet1 = it.next();
InputSource sheetSource = new InputSource(sheet1);
parser.parse(sheetSource);
sheet1.close();
}
}
/**
* 获取所有的sheet
*
* @Title processAllSheets
* @Description 获取所有的sheet
* @throws Exception
* @version
*/
private void processAllSheets(InputStream in) throws Exception {
OPCPackage pkg = OPCPackage.open(in);
XSSFReader r = new XSSFReader(pkg);
stylesTable = r.getStylesTable();
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> it = r.getSheetsData();
while (it.hasNext()) {
rowMap = null;
InputStream sheet1 = it.next();
InputSource sheetSource = new InputSource(sheet1);
parser.parse(sheetSource);
sheet1.close();
}
}
/**
* 加载sax 解析器
*
* @Title fetchSheetParser
* @Description 加载sax 解析器
* @param sst
* @return
* @throws SAXException
* @version
*/
private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
ContentHandler handler = new SheetHandler(sst);
parser.setContentHandler(handler);
return parser;
}
/**
* 列号转数字 AB7-->28 第28列
*
* @Title covertRowIdtoInt
* @Description 列号转数字 AB7-->28 第28列
* @param rowId 行id
* @return
* @version
*/
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;
}
private boolean isAccess() {
if (currentRow >= startRow) {
return true;
}
return false;
}
/**
* 填补空单元格
*
* @Title fillBlankCell
* @Description 填补空单元格
* @version
*/
private void fillBlankCell() {
if (fillBlankColSize <= 0) {
return;
}
int factFillBlankColSize = fillBlankColSize > colSize ? colSize : fillBlankColSize;// 实际需要填空的列数
System.out.println(factFillBlankColSize);
Iterator i = dataMap.entrySet().iterator();
while (i.hasNext()) {
Map.Entry e = (Map.Entry) i.next();
Map<Integer, String> rowData = (Map<Integer, String>) e.getValue();
for (int k = 0; k < factFillBlankColSize; k++) {
boolean isExist = false;
Iterator j = rowData.entrySet().iterator();
while (j.hasNext()) {
Map.Entry m = (Map.Entry) j.next();
if (((Integer) m.getKey()) == k) {
isExist = true;
break;
}
}
if (!isExist) {
rowData.put(k, "");
}
}
}
}
public static void main(String[] args) throws Exception {
SaxReadExcelUtil reader = new SaxReadExcelUtil();
Map<Integer, Map<Integer, String>> dataMap1 = reader.readExcel("C:/Users/Administrator/Desktop/test111.xlsx", 2,9);
System.out.println(dataMap1);
// System.out.println("===========");
// File file = new File("C:/Users/Administrator/Desktop/test111.xlsx");
// FileInputStream fileInputStream = new FileInputStream(file);
// System.out.println(ExcelUtil.readExcelNotInclude0Row(fileInputStream));
// System.out.println("===========");
// SaxReadExcelUtil reader2 = new SaxReadExcelUtil();
// Map<Integer, Map<Integer, String>> dataMap2 =
// reader2.readExcel(fileInputStream, 2);
// System.out.println(dataMap2);
}
/**
* sax解析handler See org.xml.sax.helpers.DefaultHandler javadocs
* http://tool.oschina.net/uploads/apidocs/jdk-zh/org/xml/sax/helpers/
* DefaultHandler.html
*
* @Title SaxReadExcelUtil.java
* @Description
*/
private class SheetHandler extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private int cellIndex = 0;
private CellDataType nextDataType = CellDataType.SSTINDEX;
private final DataFormatter formatter = new DataFormatter();
private short formatIndex;
private String formatString;
private SheetHandler(SharedStringsTable sst) {
this.sst = sst;
}
/**
* 开始元素 （获取key 值）
*/
@Override
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
if (name.equals("dimension")) {
String dimension = attributes.getValue("ref");
colSize = covertRowIdtoInt(dimension.substring(dimension.indexOf(":") + 1));
// TODO
System.out.println(colSize);
}
// c => 单元格
if (name.equals("c")) {
// cell position
String rowId = attributes.getValue("r");
this.setNextDataType(attributes);
// 判断是否是新的一行
if (Pattern.compile("^A[0-9]+$").matcher(rowId).find()) {
if (rowMap != null && !rowMap.isEmpty() && isAccess()) {
int index = currentRow - startRow;
dataMap.put(index, rowMap);
}
rowMap = new HashMap<Integer, String>();
currentRow++;
cellIndex = 0;
}
if (isAccess()) {
// 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;
}
}
}
lastContents = "";
}
@Override
public void characters(char[] ch, int start, int length) throws SAXException {
if (isAccess()) {
lastContents += new String(ch, start, length);
}
}
/**
* 获取value
*/
@Override
public void endElement(String uri, String localName, String name) throws SAXException {
if (isAccess()) {
if (name.equals("c")) {
cellIndex++;
}
if (nextIsString) {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
nextIsString = false;
}
// v => contents of a cell
if (name.equals("v")) {
String cellValue = this.getDataValue(lastContents.trim());
rowMap.put(cellIndex, cellValue);
}
}
}
/**
* 根据element属性设置数据类型
*
* @param attributes
*/
public void setNextDataType(Attributes attributes) {
nextDataType = CellDataType.NUMBER;
formatIndex = -1;
formatString = null;
String cellType = attributes.getValue("t");
String cellStyleStr = attributes.getValue("s");
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 ("m/d/yy" == formatString) {
nextDataType = CellDataType.DATE;
// full format is "yyyy-MM-dd hh:mm:ss.SSS";
formatString = "yyyy-MM-dd";
}
if (formatString == null) {
nextDataType = CellDataType.NULL;
formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
}
}
}
/**
* 根据数据类型获取数据
*
* @param value
* @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:
thisStr = value.toString();
break;
case NUMBER:
if (formatString != null) {
thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
} else {
// 考虑到本系统中的导入的浮点数以金额居多,因此仅保留两位小数,整数取整(避免“.0”)
DecimalFormat decimalFormat = new DecimalFormat("#.##");
thisStr = decimalFormat.format(value);
}
thisStr = thisStr.replace("_", "").trim();
break;
case DATE:
try {
thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
} catch (NumberFormatException ex) {
thisStr = value.toString();
}
thisStr = thisStr.replace(" ", "");
break;
default:
thisStr = "";
break;
}
return thisStr;
}
}
}
593
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
