1、基础类描述
- ExcelWriter（导出工具类）
- Query（通用查询）
- Consumer（函数参数）
- SpringBeanUtil（获取bean）
2、代码
ExcelWriter
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.util.PageUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.itxl.common.utils.SpringBeanUtil;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;
import java.util.concurrent.atomic.AtomicInteger;
/**
* @authar liuWei
* @ @date 2024/3/27
*/
public class ExcelWriter<T>{
private Map<String,WriteSheet> sheetMap = null;
private Map<String,WriteTable> tableMap = null;
private List<WriteHandler> writeHandlers = null;
private com.alibaba.excel.ExcelWriter writer = null;
private OutputStream outputStream = null;
private String curSheetMame = "sheet";
private Integer sheetNo = 1;
private Integer cueSheetDataSize = 0;
private static final Integer sheetMaxNum = 1000000;
private List<List<String>> heads = null;
/**
* 响应文件流类型
*/
private static final String CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
/**
* 响应头文件名编码格式
*/
private static final String ENCODE = "UTF-8";
private Long total;
private boolean multiThread = false;
public static <T> ExcelWriter<T> create(){
return new ExcelWriter<>();
}
public ExcelWriter<T> setResponse(HttpServletResponse response,String fileName) throws IOException {
response.setContentType(CONTENT_TYPE);
response.setCharacterEncoding(ENCODE);
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
fileName = URLEncoder.encode(fileName, ENCODE).replaceAll("\\+", "%20");
response.setHeader("fileName",fileName+".xlsx");
return setOutputStream(response.getOutputStream());
}
public ExcelWriter<T> setOutputStream(OutputStream outputStream){
this.outputStream = outputStream;
return this;
}
private WriteSheet getSheet(String sheetName){
if (CollUtil.isEmpty(sheetMap)){
sheetMap = new HashMap<>();
}
if (StrUtil.isEmpty(sheetName)){
sheetName = curSheetMame;
}
if (sheetNo > 1){
sheetName = sheetName + sheetNo;
}
WriteSheet sheet = sheetMap.get(sheetName);
if (sheet == null){
sheet = new WriteSheet();
sheet.setSheetName(sheetName);
sheetMap.put(sheetName,sheet);
}
return sheet;
}
public ExcelWriter<T> setCurSheet(String sheetMame){
this.curSheetMame = sheetMame;
return this;
}
private WriteTable getTable(String tableName){
if (CollUtil.isEmpty(tableMap)){
tableMap = new HashMap<>();
}
if (StrUtil.isEmpty(tableName)){
tableName = "table";
}
return tableMap.computeIfAbsent(tableName, k -> new WriteTable());
}
public ExcelWriter<T> addWriteHandlers(WriteHandler writeHandler){
if (CollUtil.isEmpty(writeHandlers)){
writeHandlers = new ArrayList<>();
}
writeHandlers.add(writeHandler);
return this;
}
public ExcelWriter<T> builder(){
ExcelWriterBuilder write = EasyExcelFactory.write(outputStream);
if (CollUtil.isNotEmpty(writeHandlers)){
for (WriteHandler writeHandler : writeHandlers) {
write.registerWriteHandler(writeHandler);
}
}
this.writer = write.build();
return this;
}
public ExcelWriter<T> setHead(List<String> heads){
return setHeads(parseHead(heads));
}
private List<List<String>> parseHead(List<String> heads) {
List<List<String>> res = new ArrayList<>(heads.size());
for (String head : heads) {
res.add(Collections.singletonList(head));
}
return res;
}
private ExcelWriter<T> setHeads(List<List<String>> heads){
getTable(null).setHead(heads);
return this;
}
public ExcelWriter<T> writer(List<List<Object>> data){
if (CollUtil.isEmpty(data)){
return this;
}
while (cueSheetDataSize + data.size() > sheetMaxNum){
List<List<Object>> left = ListUtil.sub(data, 0, sheetMaxNum - cueSheetDataSize);
writer.write(left,getSheet(null),getTable(null));
data = ListUtil.sub(data, sheetMaxNum - cueSheetDataSize, data.size());
cueSheetDataSize = 0;
sheetNo++;
}
cueSheetDataSize += data.size();
writer.write(data,getSheet(null),getTable(null));
return this;
}
public ExcelWriter<T> writer(Query<List<List<Object>>> query) throws InterruptedException {
return writer(query, query.getPageSize());
}
public ExcelWriter<T> writer(Query<List<List<Object>>> query, Integer pageSize) throws InterruptedException {
if (total == null){
List<List<Object>> accept = query.accept();
return writer(accept);
}
query.setPageSize(pageSize);
int totalPage = PageUtil.totalPage(total, query.getPageSize());
AtomicInteger pageNum = new AtomicInteger(1);
if (!multiThread){
writer(pageNum,totalPage, query);
return this;
}
ThreadPoolTaskExecutor bean = SpringBeanUtil.getBean(ThreadPoolTaskExecutor.class);
List<Future<?>> futures = new ArrayList<>();
int treadPoolSize = Math.min(totalPage, 4);
for (int i = 0; i < treadPoolSize; i++) {
Future<?> future = bean.submit(() -> {
writer(pageNum, totalPage, query);
});
futures.add(future);
}
for (Future<?> future : futures) {
try {
future.get();
} catch (ExecutionException e) {
throw new RuntimeException(e);
}
}
return this;
}
private void writer(AtomicInteger pageNum, Integer totalPage, Query<List<List<Object>>> query){
while (pageNum.get() <= totalPage) {
query.setPageNum(pageNum.getAndIncrement());
List<List<Object>> accept = query.accept();
synchronized (writer){
writer(accept);
}
query.removePage();
}
}
public static List<List<Object>> parseData(List<Object> datas,List<String> fields){
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
List<List<Object>> res = new ArrayList<>(datas.size());
for (Object data : datas) {
JSONObject json = JSONUtil.parseObj(data);
List<Object> objects = new ArrayList<>(fields.size());
for (String field : fields) {
Object o = json.get(field);
if (o == null){
o = "";
}else if (o instanceof Date){
o = format.format(o);
}else{
o = o.toString();
}
objects.add(o);
}
res.add(objects);
}
return res;
}
public void close() {
if (writer == null){
return;
}
writer.close();
}
/**
* 开启分页查询
* @param total
* @return
*/
public ExcelWriter<T> paging(Long total){
this.total = total;
return this;
}
/**
* 开启多线程
* @param total
* @return
*/
public ExcelWriter<T> multiThread(Long total){
this.multiThread = true;
return paging(total);
}
/**
* 合并数据
* @param firstRow
* @param lastRow
* @param firstCol
* @param lastCol
* @return
*/
public ExcelWriter<T> merge(Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol){
List<List<Integer>> mergeInfo = new ArrayList<>();
mergeInfo.add(ListUtils.newArrayList(firstRow,lastRow,firstCol,lastCol));
return merge(mergeInfo);
}
/**
* 合并数据
* @param mergeInfo
* @return
*/
public ExcelWriter<T> merge(List<List<Integer>> mergeInfo) {
SheetWriteHandler mergeHandler = new SheetWriteHandler(){
@Override
public void afterSheetCreate(SheetWriteHandlerContext context) {
for (List<Integer> merge : mergeInfo) {
CellRangeAddress addressList = new CellRangeAddress(merge.get(0), merge.get(1), merge.get(2), merge.get(3));
context.getWriteSheetHolder().getSheet().addMergedRegionUnsafe(addressList);
}
}
};
return addWriteHandlers(mergeHandler);
}
/**
* 设置下拉框
* @param axisInfo
* @param data
* @return
*/
public ExcelWriter<T> setDropDownBox(List<Integer> axisInfo,List<String> data){
SheetWriteHandler dropDownBoxHandler = new SheetWriteHandler() {
@Override
public void afterSheetCreate(SheetWriteHandlerContext context) {
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(axisInfo.get(0), axisInfo.get(1), axisInfo.get(2), axisInfo.get(3));
DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
DataValidationConstraint constraint = helper.createExplicitListConstraint(data.toArray(new String[0]));
DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
}
};
return addWriteHandlers(dropDownBoxHandler);
}
/**
* 设置下拉框
* @param col
* @param data
* @return
*/
public ExcelWriter<T> setDropDownBox(Integer col,List<String> data){
//设置下拉框为col列往下1000行
List<Integer> axisInfo = ListUtils.newArrayList(1,1000,col,col);
return setDropDownBox(axisInfo,data);
}
/**
* 设置列宽
* @return
*/
public ExcelWriter<T> setColumnWidth(List<List<Integer>> columnWidths){
AbstractColumnWidthStyleStrategy strategy = new AbstractColumnWidthStyleStrategy(){
@Override
protected void setColumnWidth(CellWriteHandlerContext context) {
Sheet sheet = context.getWriteSheetHolder().getSheet();
for (List<Integer> columnWidth : columnWidths) {
sheet.setColumnWidth(columnWidth.get(0),columnWidth.get(1) * 256);
}
}
};
return addWriteHandlers(strategy);
}
/**
* 设置列宽
* @return
*/
public ExcelWriter<T> setColumnWidth(Integer col,Integer width){
List<List<Integer>> columnWidths = new ArrayList<>();
columnWidths.add(ListUtils.newArrayList(col,width));
return setColumnWidth(columnWidths);
}
/**
* 设置行高
* @param row
* @param height
* @return
*/
public ExcelWriter<T> setRowHeight(Integer row,Integer height){
List<List<Integer>> rowHeights = new ArrayList<>();
rowHeights.add(ListUtils.newArrayList(row,height));
return setRowHeight(rowHeights);
}
/**
* 设置行高
* @param rowHeights
* @return
*/
private ExcelWriter<T> setRowHeight(List<List<Integer>> rowHeights) {
SheetWriteHandler strategy = new SheetWriteHandler(){
@Override
public void afterSheetCreate(SheetWriteHandlerContext context) {
Sheet sheet = context.getWriteSheetHolder().getSheet();
for (List<Integer> rowHeight : rowHeights) {
Row row = sheet.getRow(rowHeight.get(0));
row.setHeightInPoints(rowHeight.get(1));
}
}
};
return addWriteHandlers(strategy);
}
/**
* 设置单元格样式
* @param firstRow
* @param lastRow
* @param firstCol
* @param lastCol
* @return
*/
public ExcelWriter<T> setCellStyle(Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol,CellStyle cellStyle){
List<List<Integer>> axisInfo = new ArrayList<>();
axisInfo.add(ListUtils.newArrayList(firstRow,lastRow,firstCol,lastCol));
return setCellStyle(axisInfo,cellStyle);
}
/**
* 设置单元格样式
* @param axisInfo
* @return
*/
public ExcelWriter<T> setCellStyle(List<List<Integer>> axisInfo,CellStyle cellStyle){
SheetWriteHandler handler = new SheetWriteHandler(){
@Override
public void afterSheetCreate(SheetWriteHandlerContext context) {
Sheet sheet = context.getWriteSheetHolder().getSheet();
for (List<Integer> axis : axisInfo) {
for (int i = axis.get(0); i < axis.get(1); i++) {
Row row = sheet.getRow(i);
for (int j = axis.get(2); j < axis.get(3); j++) {
//设置样式
Cell cell = row.getCell(j);
cell.setCellStyle(cellStyle);
}
}
}
SheetWriteHandler.super.afterSheetCreate(context);
}
};
return addWriteHandlers(handler);
}
public static void error(HttpServletResponse response,String msg) throws IOException {
response.reset();
response.setContentType("application/json");
response.sendError(HttpServletResponse.SC_NOT_FOUND,msg);
}
}
Query
import org.itxl.common.utils.Consumer;
/**
* @authar liuWei
* @ @date 2024/3/25
*/
public abstract class Query<T> implements Consumer<T> {
private final ThreadLocal<Integer> local = new ThreadLocal<>();
private Integer pageSize = 10000;
public void setPageNum(Integer pageNum){
local.set(pageNum);
}
public Integer getPageNum(){
return local.get();
}
public void removePage(){
local.remove();
}
public Integer getPageSize() {
return pageSize;
}
public void setPageSize(Integer pageSize) {
if (pageSize == null){
return;
}
this.pageSize = pageSize;
}
@Override
public T accept() {
return andThen();
}
}
Consumer
/**
* @authar liuWei
* 通用函数参数
* @param <T>
*/
public interface Consumer<T>{
/**
* 方法实现
* @return T
*/
T andThen();
/**
* 方法执行
* @return T
*/
T accept();
}
SpringBeanUtil
import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;
/**
* @authar liuWei
* @ @date 2024/2/2
*/
@Component
public class SpringBeanUtil implements ApplicationContextAware {
private static ApplicationContext applicationContext;
public static ApplicationContext getApplicationContext() {
return applicationContext;
}
public static Object getBean(String beanName) {
return applicationContext.getBean(beanName);
}
public static <T> T getBean(Class<T> clazz) {
return applicationContext.getBean(clazz);
}
public static <T> T getBean(String beanName, Class<T> clazz) {
return applicationContext.getBean(beanName, clazz);
}
@Override
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
if (SpringBeanUtil.applicationContext == null){
SpringBeanUtil.applicationContext = applicationContext;
}
}
}
3、使用
ExcelWriter<Object> writer = ExcelWriter.create();
try {
writer.setResponse(response,"测试")
.setHead(ListUtils.newArrayList("测试"))
.builder()
.multiThread(436L)
.writer(new Query<List<List<Object>>>() {
@Override
public List<List<Object>> andThen() {
List<Object> data=new ArrayList<>();
Date date = new Date();
data.add(date);
List<List<Object>> res = new ArrayList<>();
res.add(data);
return res;
}
});
} catch (Exception e) {
try {
ExcelWriter.error(response,e.getMessage());
} catch (IOException ex) {
throw new RuntimeException(ex);
}
throw new RuntimeException(e);
}finally {
writer.close();
}
