Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance.
Project price only 1 $
You can buy this project and download/modify it how often you want.
matrix.boot.common.utils.ExcelUtil Maven / Gradle / Ivy
package matrix.boot.common.utils;
import com.alibaba.fastjson.JSONObject;
import com.monitorjbl.xlsx.StreamingReader;
import matrix.boot.common.constants.ExcelConstant;
import matrix.boot.common.converter.ExcelColumnConvert;
import matrix.boot.common.converter.ExcelExportSheetListenerConvert;
import matrix.boot.common.enums.ExcelFormatEnum;
import matrix.boot.common.exception.ServiceException;
import matrix.boot.common.listener.ExcelExportMultiSheetListener;
import matrix.boot.common.listener.ExcelExportSingleSheetListener;
import matrix.boot.common.dto.ExcelColumnDto;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.*;
/**
* excel导入导出工具
*
* @author wangcheng
* 2020/7/17
**/
public class ExcelUtil {
private static final int ROW_ACCESS_WINDOW_SIZE = 200;
/**
* 文件路径
*/
private String filePath;
private ExcelUtil() {
}
private ExcelUtil(String filePath) {
this.filePath = filePath;
}
/**
* 获取实例
*
* @param filePath 文件地址
* @return 实例
*/
public static ExcelUtil getInstance(String filePath) {
return new ExcelUtil(filePath);
}
/**
* 导出Bean单个sheet
*
* @param data 数据
* @param excelFormatEnum excel导入类型
* @return 文件名
*/
public String exportSingleForBean(List data, ExcelFormatEnum excelFormatEnum) {
return exportMultiForBean(ExcelExportSheetListenerConvert.convert(data), excelFormatEnum);
}
/**
* 导出Bean单个sheet
*
* @param listener 数据监听器
* @param excelFormatEnum excel导入类型
* @return 文件名
*/
public String exportSingleForBean(ExcelExportSingleSheetListener listener, ExcelFormatEnum excelFormatEnum) {
return exportMultiForBean(ExcelExportSheetListenerConvert.convert(listener), excelFormatEnum);
}
/**
* 导出Map单个sheet
*
* @param data 数据
* @param excelFormatEnum excel导入类型
* @return 文件名
*/
public String exportSingleForMap(List> data, ExcelFormatEnum excelFormatEnum) {
return exportMultiForMap(ExcelExportSheetListenerConvert.convert(data), excelFormatEnum);
}
/**
* 导出Map单个sheet
*
* @param listener 数据监听器
* @param excelFormatEnum excel导入类型
* @return 文件名
*/
public String exportSingleForMap(ExcelExportSingleSheetListener> listener, ExcelFormatEnum excelFormatEnum) {
return exportMultiForMap(ExcelExportSheetListenerConvert.convert(listener), excelFormatEnum);
}
/**
* 导出Bean多个sheet
*
* @param listener 数据监听器
* @param excelFormatEnum excel导入类型
* @return 文件名
*/
public String exportMultiForBean(ExcelExportMultiSheetListener listener, ExcelFormatEnum excelFormatEnum) {
return exportExcel(listener, excelFormatEnum, true);
}
/**
* 导出Map多个sheet
*
* @param listener 数据监听器
* @param excelFormatEnum excel导入类型
* @return 文件名
*/
public String exportMultiForMap(ExcelExportMultiSheetListener> listener, ExcelFormatEnum excelFormatEnum) {
return exportExcel(listener, excelFormatEnum, false);
}
/**
* 导出excel(核心方法)
*
* @param listener 数据监听器
* @param excelFormatEnum excel导入类型
* @param isBean 是否是实体（非实体为map）
* @return 文件名
*/
private String exportExcel(ExcelExportMultiSheetListener listener, ExcelFormatEnum excelFormatEnum, boolean isBean) {
AssertUtil.notNullTip(listener, "listener");
AssertUtil.notNullTip(excelFormatEnum, "excelFormatEnum");
Workbook book = null;
FileOutputStream fos = null;
try {
book = excelFormatEnum.getClazz().equals(SXSSFWorkbook.class) ? new SXSSFWorkbook(ROW_ACCESS_WINDOW_SIZE) : (Workbook) excelFormatEnum.getClazz().newInstance();
//创建列类型字典
Map cellStyleMap = new HashMap<>();
cellStyleMap.put(ExcelConstant.TITLE_CELL_STYLE_KEY, getTitleCellStyle(book));
// 数据sheet页
Set dataSheets = new HashSet<>();
int count = 0;
//调用处理数据前操作
listener.beforeProcessData(book);
//准备写入数据
while (true) {
Map> originData = listener.getData(count++);
//检查数据是否为空（为空停止获取数据）
if (originData == null || originData.size() <= 0
|| originData.values().stream().filter(CollectionUtils::isNotEmpty).count() <= 0) {
break;
}
//解析数据
for (String sheetName : originData.keySet()) {
List originRows = originData.get(sheetName);
if (CollectionUtils.isEmpty(originRows)) {
continue;
}
@SuppressWarnings("unchecked")
List> rows = isBean ? ExcelColumnConvert.convertForBean(originRows) : ExcelColumnConvert.convertForMap((List>) originRows);
//获取当前sheet
Sheet sheet = book.getSheet(sheetName);
if (sheet == null) {
sheet = book.createSheet(sheetName);
}
int rowIndex = sheet.getPhysicalNumberOfRows();
for (List columns : rows) {
//新增标题
if (!dataSheets.contains(sheetName) && dataSheets.add(sheetName)) {
Row excelRow = sheet.createRow(rowIndex++);
//起始列标识
int cellIndex = (excelRow.getLastCellNum() < 0 ? 0 : excelRow.getLastCellNum());
for (ExcelColumnDto column : columns) {
//设置列宽
sheet.setColumnWidth(cellIndex, column.getWidth() * 20);
Cell cell = excelRow.createCell(cellIndex);
//设置标题样式
cell.setCellStyle(cellStyleMap.get(ExcelConstant.TITLE_CELL_STYLE_KEY));
//设置值
cell.setCellValue(column.getName());
cellIndex++;
}
}
//新增数据
Row excelRow = sheet.createRow(rowIndex++);
//起始列标识
int cellIndex = (excelRow.getLastCellNum() < 0 ? 0 : excelRow.getLastCellNum());
for (ExcelColumnDto column : columns) {
Cell cell = excelRow.createCell(cellIndex++);
CellStyle cellStyle = cellStyleMap.get(column.getType());
if (cellStyle == null) {
//创建cellStyle
cellStyle = getExportDefaultCellStyle(column, book);
cellStyleMap.put(column.getType(), cellStyle);
}
//设置样式
cell.setCellStyle(cellStyle);
//如果为空直接放入为空字符串
if (column.getValue() == null) {
cell.setCellValue("");
continue;
}
//设置值
if (Date.class.equals(column.getType())) {
cell.setCellValue((Date) column.getValue());
} else if (Double.class.equals(column.getType())) {
cell.setCellValue((Double) column.getValue());
} else if (Boolean.class.equals(column.getType())) {
cell.setCellValue((Boolean) column.getValue());
} else if (Integer.class.equals(column.getType())) {
cell.setCellValue((Integer) column.getValue());
} else {
cell.setCellValue(String.valueOf(column.getValue()));
}
//后置单元格处理（处理批注，个性化颜色等等功能）
listener.processingData(column, cell);
}
}
}
}
//写入数据结束
//调用处理数据后操作
listener.afterProcessData(book);
//随机文件名称
String fileName = RandomUtil.getUUID() + excelFormatEnum.getSuffix();
fos = new FileOutputStream(new File(filePath, fileName));
book.write(fos);
return fileName;
} catch (Exception e) {
throw new ServiceException(e);
} finally {
BIOStreamUtil.closeStream(fos);
BIOStreamUtil.closeStream(book);
}
}
/**
* 导入excel
*
* @param fileName 文件名
* @param excelFormatEnum excel类型
* @param importCallBack 回调函数
* @return 处理返回的数据
*/
public List importExcel(String fileName, ExcelFormatEnum excelFormatEnum, ExcelImportSingleSheetCallBack importCallBack) {
return this.importExcel(fileName, excelFormatEnum, null, null, 2000, importCallBack);
}
/**
* 导入excel
*
* @param fileName 文件名
* @param excelFormatEnum excel类型
* @param sheetName sheet名称
* @param batchSize 每批次数量
* @param importCallBack 回调函数
* @return 处理返回的数据
*/
public List importExcel(String fileName, ExcelFormatEnum excelFormatEnum, String sheetName, Integer batchSize, ExcelImportSingleSheetCallBack importCallBack) {
return this.importExcel(fileName, excelFormatEnum, sheetName, null, batchSize, importCallBack);
}
/**
* 导入excel
*
* @param fileName 文件名
* @param excelFormatEnum excel类型
* @param sheetName sheet名称
* @param titleRowIndex 标题行Index
* @param batchSize 每批次数量
* @param importCallBack 回调函数
* @return 处理返回的数据
*/
public List importExcel(String fileName, ExcelFormatEnum excelFormatEnum, String sheetName, int titleRowIndex, Integer batchSize, ExcelImportSingleSheetCallBack importCallBack) {
AssertUtil.notNullTip(sheetName, "sheetName");
Map sheetTitleRowIndexMap = new HashMap() {{
put(sheetName, titleRowIndex);
}};
return this.importExcel(fileName, excelFormatEnum, sheetName, sheetTitleRowIndexMap, batchSize, importCallBack);
}
/**
* 导入excel(核心方法)
*
* @param fileName 文件名
* @param excelEnum excel类型
* @param sheetName sheet名称
* @param sheetTitleRowIndexMap 每个sheet页的标题行Index
* @param batchSize 每批次数量
* @param importCallBack 回调函数
* @return 处理返回的数据
*/
private List importExcel(String fileName, ExcelFormatEnum excelEnum, String sheetName,
Map sheetTitleRowIndexMap,
Integer batchSize, ExcelImportSingleSheetCallBack importCallBack) {
AssertUtil.notNullTip(fileName, "fileName");
AssertUtil.notNullTip(importCallBack, "callBack");
Workbook book = null;
FileInputStream fis = null;
try {
File file = new File(filePath, fileName);
AssertUtil.state(file.exists(), "file not found!");
if (excelEnum.getClazz().equals(HSSFWorkbook.class)) {
fis = new FileInputStream(file);
book = new HSSFWorkbook(fis);
} else {
book = StreamingReader.builder().rowCacheSize(ROW_ACCESS_WINDOW_SIZE).open(file);
}
//创建sheet页集合
List sheets = new ArrayList<>();
if (StringUtil.isEmpty(sheetName)) {
for (Sheet sheet : book) {
sheets.add(sheet);
}
} else {
sheets.add(book.getSheet(sheetName));
}
//获取callback上的第一个泛型
@SuppressWarnings("unchecked")
Class clazz = (Class) ReflectUtil.getGenericSuperClassTypes(importCallBack.getClass(), 1);
List params = new ArrayList<>();
List result = new ArrayList<>();
for (Sheet sheet : sheets) {
if (sheet == null) {
continue;
}
//标题行Index
Integer titleRowIndex = sheetTitleRowIndexMap == null ? null : sheetTitleRowIndexMap.get(sheet.getSheetName());
titleRowIndex = titleRowIndex == null ? 0 : titleRowIndex;
List titles = new ArrayList<>();
for (Row row : sheet) {
if (row.getRowNum() <= titleRowIndex) {
if (row.getRowNum() == titleRowIndex) {
for (Cell cell : row) {
titles.add(cell.getStringCellValue());
}
}
continue;
}
JSONObject jsonObject = new JSONObject();
for (int i = 0; i < titles.size(); i++) {
jsonObject.put(titles.get(i), ExcelColumnConvert.convertCellValue(row.getCell(i)));
}
//add数据
params.add(ExcelColumnConvert.convertJsonToGeneric(jsonObject, clazz));
//批处理数据
if (params.size() >= batchSize) {
List processResult = importCallBack.processData(sheet.getSheetName(), params);
if (!CollectionUtils.isEmpty(processResult)) {
result.addAll(processResult);
}
params.clear();
}
}
//批处理数据
if (!CollectionUtils.isEmpty(params)) {
List processResult = importCallBack.processData(sheet.getSheetName(), params);
if (!CollectionUtils.isEmpty(processResult)) {
result.addAll(processResult);
}
params.clear();
}
}
return result;
} catch (Exception e) {
throw new ServiceException(e);
} finally {
BIOStreamUtil.closeStream(book);
BIOStreamUtil.closeStream(fis);
}
}
/**
* 获取默认的单元格样式
*
* @param column 单元格数据
* @param workbook 工作簿
* @return 单元格样式
*/
public static CellStyle getExportDefaultCellStyle(ExcelColumnDto column, Workbook workbook) {
CellStyle cellStyle = workbook.createCellStyle();
if (Date.class.equals(column.getType())) {
cellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
}
cellStyle.setAlignment(HorizontalAlignment.LEFT);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setWrapText(true);
return cellStyle;
}
/**
* 获取批注信息
*
* @param cell 单元格
* @param author 作者
* @param text 批注信息
* @return 批注信息
*/
public static Comment getExportDefaultComment(Cell cell, String author, String text) {
Drawing drawing = cell.getSheet().createDrawingPatriarch();
Comment comment = drawing.createCellComment(drawing.createAnchor(0, 0, 0, 0, cell.getColumnIndex(), cell.getRowIndex(),
cell.getColumnIndex() + 3, cell.getRowIndex() + 5));
if (cell instanceof SXSSFCell || cell instanceof XSSFCell) {
comment.setString(new XSSFRichTextString(text));
} else if (cell instanceof HSSFCell) {
comment.setString(new HSSFRichTextString(text));
}
comment.setAuthor(author);
return comment;
}
/**
* 获取标题单元格样式
* @param workbook 工作薄
*/
public static CellStyle getTitleCellStyle(Workbook workbook) {
//创建标题字体
Font font = workbook.createFont();
font.setBold(true);
//创建默认的CellStyle
CellStyle commonCellStyle = workbook.createCellStyle();
commonCellStyle.setAlignment(HorizontalAlignment.CENTER);
commonCellStyle.setFont(font);
return commonCellStyle;
}
/**
* 导入回调函数
*
* @author WangCheng
* date 2020/7/26
*/
public static abstract class ExcelImportSingleSheetCallBack {
/**
* 处理数据
*
* @param sheetName sheet名
* @param rows 总行数
* @return 处理需要返回的值
*/
public abstract List processData(String sheetName, List rows);
}
}
