Excel工具类 - POI / Easyexcel
程序员文章站
2022-07-13 13:15:25
...
在项目中经常使用Excel 文件做导入导出功能，下面介绍两种经常使用的工具类 Apache POI ，Ali EasyExcel.
Excel 分为03 版 （xls）, 07版（xlsx）,下面介绍03，07导入解析，导出都是07版。
Apache POI
Apache POI 现已更新到 4.1.0 版本，本文使用的 3.17版。
poi 提供3种读写Excel 的方式 ：
- HSSF 对应 03版
.xls后缀 - XSSF 对应 07版
.xlsx后缀 - SXSSF 支持大数据量的操作,通过一个滑动窗口来限制访问Row的数量从而达到低内存占用的目录
poi excel 提供的概念类
- Workbook 对应工作簿
- Sheet 工作表
- Row 行
- Cell 单元格
POI excel 工具类
读取excel
Workook , 获取 cell 值
/**
* 读Excel文件
* @param filePath
* @return
*/
public static Workbook readExcel(String filePath){
Workbook wb = null;
if(filePath==null){
return null;
}
String extString = filePath.substring(filePath.lastIndexOf("."));
InputStream is = null;
try {
is = new FileInputStream(filePath);
if(".xls".equals(extString)){
return wb = new HSSFWorkbook(is);
}else if(".xlsx".equals(extString)){
return wb = new XSSFWorkbook(is);
}else{
return null;
}
} catch (Exception e) {
LOGGER.error("读取Excel文件失败：{}",e);
}
return wb;
}
/**
* 读Excel文件
* @param fileName
* @param is
* @return
*/
public static Workbook readExcel(String fileName,InputStream is){
Workbook wb = null;
if(null == fileName){
return null;
}
String extString = fileName.substring(fileName.lastIndexOf("."));
try {
if(".xls".equals(extString)){
return wb = new HSSFWorkbook(is);
}else if(".xlsx".equals(extString)){
return wb = new XSSFWorkbook(is);
}else{
return wb = null;
}
} catch (Exception e) {
LOGGER.error("读取Excel文件失败：{}",e);
}
return wb;
}
/**
* 获取单元格的值
* @param cell
* @return
*/
public static Object getCellFormatValue(Cell cell){
Object cellValue = null;
if(cell!=null){
switch(cell.getCellTypeEnum()) {
case NUMERIC:{
if(DateUtil.isCellDateFormatted(cell)){
cellValue = new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
}else{
String val= String.valueOf(cell.getNumericCellValue());
if(val.indexOf("E") == -1 && val.indexOf("e") == -1) {
cellValue = val;
}else {
cellValue = new DecimalFormat("#").format(cell.getNumericCellValue());
}
}
break;
}
case STRING:{
cellValue = cell.getRichStringCellValue().getString();
break;
}
default:
cellValue = " ";
}
}else {
cellValue = "--";
}
return cellValue;
}
/**
* 判断指定的单元格是否是合并单元格
* @param sheet
* @param row
* @param column
* @return
*/
public static boolean isMergedRegion(Sheet sheet,int row ,int column) {
int sheetMergeCount = sheet.getNumMergedRegions();
for (int i = 0; i < sheetMergeCount; i++) {
CellRangeAddress range = sheet.getMergedRegion(i);
int firstColumn = range.getFirstColumn();
int lastColumn = range.getLastColumn();
int firstRow = range.getFirstRow();
int lastRow = range.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
return true;
}
}
}
return false;
}
/**
* 获取合并单元格的值
* @param sheet
* @param row
* @param column
* @return
*/
public static Object getMergedRegionValue(Sheet sheet ,int row , int column){
int sheetMergeCount = sheet.getNumMergedRegions();
for(int i = 0 ; i < sheetMergeCount ; i++){
CellRangeAddress ca = sheet.getMergedRegion(i);
int firstColumn = ca.getFirstColumn();
int lastColumn = ca.getLastColumn();
int firstRow = ca.getFirstRow();
int lastRow = ca.getLastRow();
if(row >= firstRow && row <= lastRow){
if(column >= firstColumn && column <= lastColumn){
Row fRow = sheet.getRow(firstRow);
Cell fCell = fRow.getCell(firstColumn);
return getCellFormatValue(fCell) ;
}
}
}
return null ;
}
读取 指定sheet 指定某一列 文本格式
/**
* 读取 指定sheet 指定列 文本格式 （resultCells 排序）
* @param wb
* @param sheetIndex 指定sheet
* @param columnIndex 指定column
* @param startRowIndex 指定开始rowIndex
* @param check 指定cellCheck 校验cell格式
* @return
*/
public static ParseExcellResult readOneSheetOneColumn(Workbook wb,int sheetIndex,int columnIndex,int startRowIndex,CellCheck check){
ParseExcellResult result = new ParseExcellResult(true,"success");
List<String> resultCells = new ArrayList<String>();
List<Integer> errorRows = new ArrayList<Integer>();
Sheet sheet = null;
Row row = null;
String cellData = null;
if(null != wb) {
try {
sheet = wb.getSheetAt(sheetIndex);
int maxRows = sheet.getPhysicalNumberOfRows();
for(int i = startRowIndex; i<= maxRows; i++) {
row = sheet.getRow(i);
if(null != row) {
cellData = (String) ExcelUtil.getCellFormatValue(row.getCell(columnIndex)); // 指定 使用row.getPhysicalNumberOfCells() 可取所有列
}else {
break;
}
if(null != cellData && !cellData.equals(" ")) {
if(check.check(cellData)) {
resultCells.add(cellData);
}else {
errorRows.add(i); // 收集 错误数据 行 index
}
}
}
} catch (Exception e) {
LOGGER.error("解析Excel失败：{}",e);
} finally {
try {
wb.close();
} catch (IOException e) {
}
}
}
if(resultCells.size() <=0) {
result.setSuccess(false);
result.setMessage("指定列无有效数据");
return result;
}
result.setErrorRows(errorRows);
result.setResultCells(resultCells); //resultCells.stream().sorted().collect(Collectors.toList())
return result;
}
CellCheck 接口 （检验cell值）
public interface CellCheck {
boolean check(Object cell);
}
ParseExcellResult （解析结果）
public class ParseExcellResult {
public ParseExcellResult() {
super();
}
public ParseExcellResult(boolean success, String message) {
super();
this.success = success;
this.message = message;
}
public ParseExcellResult(boolean success, String message, List<Integer> errorRows, List<String> resultCells) {
super();
this.success = success;
this.message = message;
this.errorRows = errorRows;
this.resultCells = resultCells;
}
private boolean success;
private String message;
private List<Integer> errorRows; // 收集错误数据 行号
private List<String> resultCells;
public boolean isSuccess() {
return success;
}
public void setSuccess(boolean success) {
this.success = success;
}
public List<Integer> getErrorRows() {
return errorRows;
}
public void setErrorRows(List<Integer> errorRows) {
this.errorRows = errorRows;
}
public List<String> getResultCells() {
return resultCells;
}
public void setResultCells(List<String> resultCells) {
this.resultCells = resultCells;
}
public String getMessage() {
return message;
}
public void setMessage(String message) {
this.message = message;
}
public String toErrorRowsString() {
if(null != this.errorRows && this.errorRows.size() > 0) {
return this.errorRows.toString();
}
return " ";
}
}
导出 xlsx
创建excel (XSSF )
/**
* 创建excel xlsx
* @param paramters 注解
* @param columns 表头
* @param datas 数据
* @return
*/
private static Workbook createXSSFWorkbook(List<String> paramters, List<TableHeader> columns, List<LinkedHashMap<String, Object>> datas){
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet();
int defaultWitdh = 20;
sheet.setDefaultColumnWidth(defaultWitdh);
// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();
// 创建两种字体
Font f = wb.createFont();
Font f2 = wb.createFont();
f.setFontHeightInPoints((short) 12);
f.setColor(IndexedColors.BLACK.getIndex());
f.setFontName("微软雅黑");
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
f2.setFontName("微软雅黑");
cs.setFont(f);
cs.setAlignment(HorizontalAlignment.CENTER);
cs.setVerticalAlignment(VerticalAlignment.CENTER);
cs.setBorderLeft(BorderStyle.THIN);
cs.setBorderRight(BorderStyle.THIN);
cs.setBorderTop(BorderStyle.THIN);
cs.setBorderBottom(BorderStyle.THIN);
cs2.setFont(f2);
cs2.setAlignment(HorizontalAlignment.CENTER);
cs2.setVerticalAlignment(VerticalAlignment.CENTER);
cs2.setBorderLeft(BorderStyle.THIN);
cs2.setBorderRight(BorderStyle.THIN);
cs2.setBorderTop(BorderStyle.THIN);
cs2.setBorderBottom(BorderStyle.THIN);
if(null != paramters && paramters.size() > 0) {
XSSFRow paramterRow = sheet.createRow(0);
for (int i=0,size=paramters.size(); i<size; i++) {
Cell cell = paramterRow.createCell(i);
//cell.setCellStyle(cs2);
cell.setCellValue(paramters.get(i));
}
}
XSSFRow titlerRow = sheet.createRow(1);
for(int i=0,size=columns.size(); i<size; i++){
String name = columns.get(i).getColumnName();
int width = name.length();
if(width > defaultWitdh) {
sheet.setColumnWidth(i, width*256*2);
}
Cell cell = titlerRow.createCell(i);
cell.setCellStyle(cs);
cell.setCellValue(name);
}
XSSFRow row = null;
LinkedHashMap<String, Object> map = null;
for(int j=0,size=datas.size(); j<size; j++){
row = sheet.createRow(j+2);
map = datas.get(j);
String columnCode = null;;
Object value = null;;
Cell cell = null;
for(int k=0,ksize=columns.size(); k<ksize; k++){
columnCode = columns.get(k).getColumnCode();
value = map.get(columnCode);
cell = row.createCell(k);
cell.setCellStyle(cs2);
if(null != value) {
cell.setCellValue(value.toString());
}else {
cell.setCellValue("--");
}
}
}
return wb;
}
创建excel (SXSSF) 大数据量多sheet
/**
* SXSSFWorkbook 多sheet页
* @param paramters
* @param columns
* @param datas
* @param sheetSize max
* @return
*/
private static SXSSFWorkbook createSXSSFWorkbook(List<String> paramters, List<TableHeader> columns, List<LinkedHashMap<String, Object>> datas,Integer sheetSize) {
int totalSize = datas.size();
SXSSFWorkbook wb = new SXSSFWorkbook(1000); //在内存当中保持 1000行
// 创建两种字体
Font f = wb.createFont();
Font f2 = wb.createFont();
f.setFontHeightInPoints((short) 12);
f.setColor(IndexedColors.BLACK.getIndex());
f.setFontName("微软雅黑");
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
f2.setFontName("微软雅黑");
// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();
cs.setFont(f);
cs.setAlignment(HorizontalAlignment.CENTER);
cs.setVerticalAlignment(VerticalAlignment.CENTER);
cs.setBorderLeft(BorderStyle.THIN);
cs.setBorderRight(BorderStyle.THIN);
cs.setBorderTop(BorderStyle.THIN);
cs.setBorderBottom(BorderStyle.THIN);
cs2.setFont(f2);
cs2.setAlignment(HorizontalAlignment.CENTER);
cs2.setVerticalAlignment(VerticalAlignment.CENTER);
cs2.setBorderLeft(BorderStyle.THIN);
cs2.setBorderRight(BorderStyle.THIN);
cs2.setBorderTop(BorderStyle.THIN);
cs2.setBorderBottom(BorderStyle.THIN);
int defaultWitdh = 30;
try {
SXSSFSheet sheet = null;
int sheetNum = 1;
int fromIndex = 0;
int toIndex = 0;
if(totalSize >= sheetSize) {
sheetNum = totalSize%sheetSize == 0 ? totalSize/sheetSize : totalSize/sheetSize+1;
}
for(int i=0; i<sheetNum; i++ ) {
sheet = wb.createSheet("sheet" + (i + 1));
sheet.setDefaultColumnWidth(defaultWitdh);
if(null != paramters && paramters.size() > 0) {
SXSSFRow paramterRow = sheet.createRow(0);
for (int j=0,size=paramters.size(); j<size; j++) {
Cell cell = paramterRow.createCell(j);
//cell.setCellStyle(cs2);
cell.setCellValue(paramters.get(j));
}
}
SXSSFRow titlerRow = sheet.createRow(1);
for(int j=0,size=columns.size(); j<size; j++){
String name = columns.get(j).getColumnName();
int width = name.length();
if(width > defaultWitdh) {
sheet.setColumnWidth(j, width*256*2);
}
Cell cell = titlerRow.createCell(j);
cell.setCellStyle(cs);
cell.setCellValue(name);
}
if(totalSize > 0) {
fromIndex = i*sheetSize;
toIndex = (i+1)*sheetSize;
toIndex = Math.min(toIndex, totalSize);
List<LinkedHashMap<String, Object>> limitData = datas.subList(fromIndex, toIndex);
SXSSFRow row = null;
LinkedHashMap<String, Object> map = null;
for(int k=0,size=limitData.size(); k<size; k++){
row = sheet.createRow(k+2);
map = limitData.get(k);
String columnCode = null;;
Object value = null;;
Cell cell = null;
for(int h=0,hsize=columns.size(); h<hsize; h++){
columnCode = columns.get(h).getColumnCode();
value = map.get(columnCode);
cell = row.createCell(h);
cell.setCellStyle(cs2);
if(null != value) {
cell.setCellValue(value.toString());
}else {
cell.setCellValue("--");
}
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
LOGGER.error("导出Excel失败：{}",e);
}
return wb;
}
浏览器导出 excel
/**
* 导出Excel xlsx
* @param fileName (+.xlsx)
* @param paramters 注解
* @param columns 表头
* @param datas 数据
* @param request
* @param response
*/
public static void exportXlsx(String fileName,List<String> paramters,List<TableHeader> columns,List<LinkedHashMap<String, Object>> datas,HttpServletRequest request,HttpServletResponse response){
String userAgent = request.getHeader("user-agent").toLowerCase();
try {
if (userAgent.contains("msie") || userAgent.contains("like gecko") ) {
// win10 ie edge 浏览器 和其他系统的ie
fileName = URLEncoder.encode((fileName + ".xlsx"), "utf-8");
} else {
//其他的浏览器
fileName = new String((fileName + ".xlsx").getBytes("utf-8"), "ISO8859-1");
}
} catch (Exception e) {
LOGGER.error("打印Excel失败：{}",e);
}
fileName = fileName.replaceAll("\\+", "%20"); // 防止 空格 转 +
exportXlsx(fileName, paramters, columns, datas, response);
}
/**
* 导出 xlsx SXSSFWorkbook
* @param fileName
* @param paramters
* @param columns
* @param datas
* @param response
*/
public static void exportXlsx(String fileName,List<String> paramters,List<TableHeader> columns,List<LinkedHashMap<String, Object>> datas,HttpServletResponse response){
response.reset();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition",
"attachment;filename=" + fileName);
SXSSFWorkbook wb = null;
OutputStream out = null;
try {
wb = createSXSSFWorkbook(paramters,columns, datas,100000); //一个sheet 100000
// Workbook wb = createXSSFWorkbook(paramters,columns, datas);
if(null != wb) {
out = response.getOutputStream();
wb.write(out);
}
} catch (Exception e) {
LOGGER.error("打印Excel失败：{}",e);
} finally {
if (null != wb) {
try {
wb.dispose();
} catch (Exception e) {
e.printStackTrace();
}
}
if (null != out) {
try {
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
表头类
public class TableHeader {
public TableHeader() {
super();
}
public TableHeader(String columnCode, String columnName) {
super();
this.columnCode = columnCode;
this.columnName = columnName;
}
/**
* 英文 对应数据库字段 大写
*/
private String columnCode;
/**
* 中文
*/
private String columnName;
public String getColumnCode() {
return columnCode;
}
public void setColumnCode(String columnCode) {
this.columnCode = columnCode;
}
public String getColumnName() {
return columnName;
}
public void setColumnName(String columnName) {
this.columnName = columnName;
}
}
Alibaba Easyexcel
阿里开源 Excel 工具 ，详细介绍 请移步 https://github.com/alibaba/easyexcel ， 本次使用 1.1.2-beat1
概念类
-
sheet
public Sheet(int sheetNo, int headLineMun) { this.sheetNo = sheetNo; //指定sheet页 从1开始 this.headLineMun = headLineMun; // 指定开始 行 从0开始 } -
AnalysisEventListener 解析excel 监听 抽象类
public abstract class AnalysisEventListener<T> { /** * when analysis one row trigger invoke function * 每解析一行就回调 一次 invoke * @param object one row data 该行数据 * @param context analysis context * AnalysisContext 对象还可以获取当前 sheet，当前行等数据 */ public abstract void invoke(T object, AnalysisContext context); /** * if have something to do after all analysis * 解析完所有数据后会调用该方法 * @param context */ public abstract void doAfterAllAnalysed(AnalysisContext context); } -
BaseRowModel Excel基础模型 继承该类 使用 @ExcelProperty 注解做映射
EasyExcel 工具类
EasyExcelUtil
package com.zewe.easyexcel.util;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.fastjson.JSONObject;
import com.zewe.easyexcel.entity.User;
import com.zewe.easyexcel.listenter.AbstractListener;
import com.zewe.easyexcel.listenter.UserListener;
import com.zewe.exception.ExcelException;
public class EasyExcelUtil {
private static final Logger LOGGER = LoggerFactory.getLogger(EasyExcelUtil.class);
/**
* 文件名是否合法
* @param fileName
* @return
*/
public static boolean checkFileName(String fileName){
if(null == fileName){
return false;
}
if(!fileName.toLowerCase().endsWith(ExcelTypeEnum.XLS.getValue()) && !fileName.toLowerCase().endsWith(ExcelTypeEnum.XLSX.getValue())) {
return false;
}
return true;
}
/**
* 读取 某sheet 从某行开始
* @param filePath
* @param sheetIndex
* @param startRowIndex
* @param trim 是否对内容做trim()增加容错
* @return Object => List<String>
* @throws ExcelException
*/
public static List<Object> readRow(String filePath,Integer sheetIndex, Integer startRowIndex,boolean trim) throws ExcelException{
if(!checkFileName(filePath)) {
throw new ExcelException("文件格式不合法");
}
InputStream in = null;
try {
in = new FileInputStream(filePath);
return EasyExcelUtil.readRow(in, sheetIndex, startRowIndex, trim);
} catch (Exception e) {
LOGGER.error("EasyExcelUtil read error: {}",e);
}finally {
if(null != in) {
try {
in.close();
} catch (IOException e) {
}
}
}
return null;
}
/**
* 读取 某sheet 从某行开始
* @param in
* @param sheetIndex
* @param startRowIndex
* @param trim 是否对内容做trim()增加容错
* @return Object => List<String>
* @throws ExcelException
*/
public static List<Object> readRow(InputStream in,Integer sheetIndex, Integer startRowIndex,boolean trim) throws ExcelException{
try {
final List<Object> rows = new ArrayList<Object>();
Sheet sheet = new Sheet(sheetIndex,startRowIndex); // (某sheet, 某行)
new ExcelReader(in, null, new AnalysisEventListener() { //ExcelListener获取解析结果 并操作
@Override
public void invoke(Object object, AnalysisContext context) {
if(null != object) {
rows.add(object);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// rows.clear();
}
}, trim).read(sheet);
return rows;
} catch (Exception e) {
LOGGER.error("EasyExcelUtil read error: {}",e);
}finally {
if(null != in) {
try {
in.close();
} catch (IOException e) {
}
}
}
return null;
}
/**
* 读取 指定sheet 指定列 文本格式
* @param filePath
* @param sheetIndex
* @param columnIndex
* @param startRowIndex
* @return
* @throws ExcelException
*/
public static List<String> readOneSheetOneColumn(String filePath,int sheetIndex,int startRowIndex,int columnIndex) throws ExcelException{
if(!checkFileName(filePath)) {
throw new ExcelException("文件格式不合法");
}
InputStream in = null;
try {
in = new FileInputStream(filePath);
return EasyExcelUtil.readOneSheetOneColumn(in, sheetIndex,startRowIndex,columnIndex);
} catch (Exception e) {
LOGGER.error("EasyExcelUtil read error: {}",e);
}finally {
if(null != in) {
try {
in.close();
} catch (IOException e) {
}
}
}
return null;
}
/**
* 读取 指定sheet 指定列 文本格式
* @param filePath
* @param sheetIndex
* @param columnIndex
* @param startRowIndex
* @return
* @throws ExcelException
*/
public static List<String> readOneSheetOneColumn(InputStream in,final int sheetIndex,final int startRowIndex,final int columnIndex) throws ExcelException{
final List<String> rows = new ArrayList<String>();
try {
Sheet sheet = new Sheet(sheetIndex,startRowIndex);
new ExcelReader(in, null, new AnalysisEventListener() {
@Override
public void invoke(Object object, AnalysisContext context) {
if(null != object) {
List row = (List<String>)object; // 未指定模型 默认每行为 List<String>
if(row.size() > columnIndex) {
rows.add((String) row.get(columnIndex));
}
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
// rows.clear();
}
}, false).read(sheet);
return rows;
} catch (Exception e) {
LOGGER.error("EasyExcelUtil read error: {}",e);
}finally {
if(null != in) {
try {
in.close();
} catch (IOException e) {
}
}
}
return null;
}
/**
* 读取 某 sheet 从某行开始
* @param filePath
* @param sheetIndex
* @param rowStartIndex
* @param listener 外部监听类
* @param rowModel
* @return
*/
public static <T extends BaseRowModel> List<T> readRow(String filePath,Integer sheetIndex, Integer startRowIndex,AbstractListener<T> listener,Class<T> rowModel){
InputStream in = null;
try {
in = new FileInputStream(filePath);
return EasyExcelUtil.readRow(in, sheetIndex, startRowIndex, listener, rowModel);
} catch (Exception e) {
LOGGER.error("EasyExcelUtil read error: {}",e);
}finally {
if(null != in) {
try {
in.close();
} catch (IOException e) {
}
}
}
return null;
}
/**
* 读取 某 sheet 从某行开始
* @param filePath
* @param sheetIndex
* @param rowStartIndex
* @param listener
* @param rowModel
* @return
*/
public static <T extends BaseRowModel> List<T> readRow(InputStream in,Integer sheetIndex, Integer startRowIndex,AbstractListener<T> listener,Class<T> rowModel){
try {
Sheet sheet = new Sheet(sheetIndex,startRowIndex,rowModel);
ExcelReader reader = new ExcelReader(in, null, listener, false);
reader.read(sheet);
return listener.getDataList();
} catch (Exception e) {
LOGGER.error("EasyExcelUtil read error: {}",e);
}finally {
if(null != in) {
try {
in.close();
} catch (IOException e) {
}
}
}
return null;
}
/**
* 读取 所有 sheet
* @param filePath
* @param sheetIndex
* @param rowStartIndex
* @param listener
* @param rowModel
* @return
*/
public static <T extends BaseRowModel> List<T> readAllSheetRow(InputStream in,AbstractListener<T> listener,Class<T> rowModel){
try {
ExcelReader reader = new ExcelReader(in, null, listener, false);
for(Sheet sheet: reader.getSheets()) {
sheet.setClazz(rowModel);
reader.read(sheet);
}
return listener.getDataList();
} catch (Exception e) {
LOGGER.error("EasyExcelUtil read error: {}",e);
}finally {
if(null != in) {
try {
in.close();
} catch (IOException e) {
}
}
}
return null;
}
/**
* 读取 所有 sheet
* @param filePath
* @param listener
* @param rowModel
* @return
*/
public static <T extends BaseRowModel> List<T> readAllSheetRow(String filePath,AbstractListener<T> listener,Class<T> rowModel){
InputStream in = null;
try {
in = new FileInputStream(filePath);
return EasyExcelUtil.readAllSheetRow(in, listener, rowModel);
} catch (Exception e) {
LOGGER.error("EasyExcelUtil read error: {}",e);
}finally {
if(null != in) {
try {
in.close();
} catch (IOException e) {
}
}
}
return null;
}
/**
* 写xlsx 单个Sheet
* @param out
* @param sheetName
* @param headers 表头
* @param data
*/
public static void writeOneSheet(OutputStream out,String sheetName,List<String> headers,List<List<Object>> data) {
Sheet sheet = new Sheet(1,0);
sheet.setSheetName(sheetName);
if(null != headers) {
List<List<String>> list = new ArrayList<List<String>>();
headers.forEach(e -> list.add(Collections.singletonList(e)));
sheet.setHead(list);
}
ExcelWriter writer = null;
try {
writer = EasyExcelFactory.getWriter(out); // xlsx
writer.write1(data, sheet);
writer.finish();
} catch (Exception e2) {
LOGGER.error("EasyExcelUtil writer error: {}",e2);
} finally {
if(null != out) {
try {
out.close();
} catch (IOException e) {
}
}
}
}
/**
* 写xlsx 单个Sheet
* @param filePath
* @param sheetName
* @param headers
* @param data
* @throws ExcelException
*/
public static void writeOneSheet(String filePath,String sheetName,List<String> headers,List<List<Object>> data) throws ExcelException {
if(!checkFileName(filePath)) {
throw new ExcelException("文件格式不合法");
}
OutputStream out = null;
try {
out = new FileOutputStream(filePath);
EasyExcelUtil.writeOneSheet(out, sheetName, headers, data);
} catch (FileNotFoundException e) {
LOGGER.error("EasyExcelUtil writer error: {}",e);
} finally {
if(null != out) {
try {
out.close();
} catch (IOException e) {
}
}
}
}
/**
* 写xlsx 单个Sheet
* @param out
* @param sheetName
* @param data
* @param rowModel T
*/
public static <T extends BaseRowModel> void writeOneSheet(OutputStream out,String sheetName,List<T> data,Class<T> rowModel){
Sheet sheet = new Sheet(1,0,rowModel);
sheet.setSheetName(sheetName);
sheet.setAutoWidth(true);
ExcelWriter writer = null;
try {
writer = EasyExcelFactory.getWriter(out); // xlsx
writer.write(data, sheet);
writer.finish();
} catch (Exception e) {
LOGGER.error("EasyExcelUtil read error: {}",e);
}finally {
if(null != out) {
try {
out.close();
} catch (IOException e) {
}
}
}
}
/**
* 写xlsx 单个Sheet
* @param filePath
* @param sheetName
* @param data
* @param rowModel
* @throws ExcelException
*/
public static <T extends BaseRowModel> void writeOneSheet(String filePath,String sheetName,List<T> data,Class<T> rowModel) throws ExcelException{
if(!checkFileName(filePath)) {
throw new ExcelException("文件格式不合法");
}
OutputStream out = null;
try {
out = new FileOutputStream(filePath);
EasyExcelUtil.writeOneSheet(out, sheetName, data, rowModel);
} catch (FileNotFoundException e) {
LOGGER.error("EasyExcelUtil writer error: {}",e);
} finally {
if(null != out) {
try {
out.close();
} catch (IOException e) {
}
}
}
}
/**
* 写xlsx 多个个Sheet
* @param out
* @param sheetSize
* @param data
* @param rowModel
* @throws ExcelException
*/
public static <T extends BaseRowModel> void writeSheets(OutputStream out,Integer sheetSize,List<T> data,Class<T> rowModel) throws ExcelException {
int totalSize = data.size();
if(totalSize <= 0) {
throw new ExcelException("data无数据");
}
ExcelWriter writer = null;
try {
writer = EasyExcelFactory.getWriter(out); // xlsx
int sheetNum = totalSize%sheetSize == 0 ? totalSize/sheetSize : totalSize/sheetSize+1;
Sheet sheet = null;
int fromIndex = 0;
int toIndex = 0;
for (int i=1; i<=sheetNum; i++) {
fromIndex = (i-1)*sheetSize;
toIndex = i*sheetSize - 1;
toIndex = Math.min(toIndex, totalSize);
sheet = new Sheet(i, 0, rowModel);
sheet.setSheetName("sheet"+i);
writer.write(data.subList(fromIndex, toIndex), sheet);
}
writer.finish();
} catch (Exception e) {
e.printStackTrace();
LOGGER.error("EasyExcelUtil read error: {}",e);
}finally {
if(null != out) {
try {
out.close();
} catch (IOException e) {
}
}
}
}
/**
* 写xlsx 多个个Sheet
* @param filePath
* @param sheetSize
* @param data
* @param rowModel
* @throws ExcelException
*/
public static <T extends BaseRowModel> void writeSheets(String filePath,Integer sheetSize,List<T> data,Class<T> rowModel) throws ExcelException {
if(!checkFileName(filePath)) {
throw new ExcelException("文件格式不合法");
}
OutputStream out = null;
try {
out = new FileOutputStream(filePath);
EasyExcelUtil.writeSheets(out, sheetSize, data, rowModel);
} catch (FileNotFoundException e) {
LOGGER.error("EasyExcelUtil writer error: {}",e);
} finally {
if(null != out) {
try {
out.close();
} catch (IOException e) {
}
}
}
}
public static void main(String[] args) throws ExcelException {
String filePath = "C:\\Users\\zewe\\Desktop\\template\\test.xlsx";
Integer sheetIndex = 1;
Integer startRowIndex = 1;
Integer columnIndex = 8;
/*String filePath1 = "C:\\Users\\zewe\\Desktop\\template\\test2.xlsx";
String sheetName = "第一个sheet";
List<String> headers = Arrays.asList("A列","B列","C列");
List<List<Object>> data = new ArrayList<List<Object>>();
List<Object> d1 = Arrays.asList(1,2,3);
List<Object> d2 = Arrays.asList(new Date(),new Date(),new Date());
List<Object> d3 = Arrays.asList("汉","字",null);
data.add(d1);data.add(d2);data.add(d3);*/
String filePath2 = "C:\\Users\\zewe\\Desktop\\template\\test3.xlsx";
String sheetName = "第一个sheet";
/* List<User> data = new ArrayList<User>();
for(int i=1; i<=110; i++) {
data.add(new User(new BigDecimal(i),"name"+i,new BigDecimal(i),"adress"+i));
}*/
//List<Object> list = EasyExcelUtil.readRow(filePath, 1, 0, false);
//OneColumnListener listener = new OneColumnListener();
//List<OneColumn> list2 = EasyExcelUtil.readRow(filePath, sheetIndex, startRowIndex, listener, OneColumn.class);
//List<String> list3 = EasyExcelUtil.readOneSheetOneColumn(filePath, sheetIndex,startRowIndex,columnIndex);
//System.out.println(JSONObject.toJSONString(list3));
//EasyExcelUtil.writeOneSheet(filePath2, sheetName, data, User.class);
//EasyExcelUtil.writeSheets(filePath2, 20, data, User.class);
UserListener listener = new UserListener();
List<User> list = EasyExcelUtil.readAllSheetRow(filePath2, listener, User.class);
System.out.println(JSONObject.toJSONString(list));
}
}
自定义监听类 AbstractListener
package com.zewe.easyexcel.listenter;
import java.util.List;
import com.alibaba.excel.event.AnalysisEventListener;
/**
* extends AnalysisEventListener add getDataList
* @author ZeWe
*
* @param <T>
*/
public abstract class AbstractListener<T> extends AnalysisEventListener<T>{
public abstract List<T> getDataList();
}
ExcelListener 示例
package com.zewe.easyexcel.listenter;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
public class ExcelListener extends AnalysisEventListener{
private final List<Object> dataList = new ArrayList<Object>();
/**
* 通过 AnalysisContext 对象还可以获取当前 sheet，当前行等数据
*/
@Override
public void invoke(Object object, AnalysisContext context) {
if(!checkObjAllFieldsIsNull(object)) {
dataList.add(object);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
private static final String SERIAL_VERSION_UID = "serialVersionUID";
/**
* 判断对象中属性值是否全为空
*/
private static boolean checkObjAllFieldsIsNull(Object object) {
if (null == object) {
return true;
}
try {
for (Field f : object.getClass().getDeclaredFields()) {
f.setAccessible(true);
//只校验带ExcelProperty注解的属性
ExcelProperty property = f.getAnnotation(ExcelProperty.class);
if(property == null || SERIAL_VERSION_UID.equals(f.getName())){
continue;
}
if (f.get(object) != null && isNotBlank(f.get(object).toString())) {
return false;
}
}
} catch (Exception e) {
//do something
}
return true;
}
public List<Object> getDataList() {
return dataList;
}
/**
* 是否为空
* @param cs
* @return
*/
private static boolean isBlank(final CharSequence cs) {
int strLen;
if (cs == null || (strLen = cs.length()) == 0) {
return true;
}
for (int i = 0; i < strLen; i++) {
if (!Character.isWhitespace(cs.charAt(i))) {
return false;
}
}
return true;
}
public static boolean isNotBlank(final CharSequence cs) {
return !isBlank(cs);
}
}
User 模型
package com.zewe.easyexcel.entity;
import java.math.BigDecimal;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
public class User extends BaseRowModel{
@ExcelProperty(value="编号",index = 0)
private BigDecimal id;
@ExcelProperty(value="姓名",index = 1)
private String name;
@ExcelProperty(value="年龄",index = 2)
private BigDecimal age; // 直接使用 Integer 解析错误 1 -> 1.0 ?
@ExcelProperty(value="地址",index = 3)
private String adress;
public BigDecimal getId() {
return id;
}
public void setId(BigDecimal id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public BigDecimal getAge() {
return age;
}
public void setAge(BigDecimal age) {
this.age = age;
}
public String getAdress() {
return adress;
}
public void setAdress(String adress) {
this.adress = adress;
}
public User() {
super();
}
public User(BigDecimal id, String name, BigDecimal age, String adress) {
super();
this.id = id;
this.name = name;
this.age = age;
this.adress = adress;
}
}
UserListener
package com.zewe.easyexcel.listenter;
import java.util.ArrayList;
import java.util.List;
import com.alibaba.excel.context.AnalysisContext;
import com.zewe.easyexcel.entity.User;
public class UserListener extends AbstractListener<User>{
final List<User> datas = new ArrayList<User>();
@Override
public List<User> getDataList() {
return datas;
}
@Override
public void invoke(User object, AnalysisContext context) {
if(null != object) {
datas.add(object);
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext context) {
}
}