基础类描述
- ExcelWriter（导出工具类）
- Query（通用查询）
- Consumer（函数参数）
- SpringBeanUtil（获取bean）
代码
ExcelWriter
import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.util.PageUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.alibaba.excel.util.ListUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.itxl.common.utils.SpringBeanUtil;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.atomic.AtomicInteger;
/**
* @authar liuWei
* @ @date 2024/3/30
*/
public class ExcelWriter <T>{
private HttpServletResponse response;
private ServletOutputStream outputStream;
private boolean memory = false;
private boolean multiThread = false;
private String fileName = "file";
private Workbook workbook = new SXSSFWorkbook();
private String curSheetName = "sheet";
/**
* 响应文件流类型
*/
private static final String CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
/**
* 响应头文件名编码格式
*/
private static final String ENCODE = "UTF-8";
private AtomicInteger curRowIndex;
private Integer total;
private Integer sheetNo = 1;
private static final Integer sheetMaxNum = 1000000;
private List<List<Integer>> widths;
private List<List<Integer>> heights;
private List<List<Integer>> cellStyleIndex;
private List<CellStyle> cellStyles;
private Integer defaultColumnWidth = 20;
private Integer defaultRowHeight = 20;
private List<List<Integer>> cellValueIndex;
private List<String> cellValues;
private List<List<Integer>> mergeInfo;
private List<List<Integer>> dropDownBoxIndex;
private List<List<String>> dropDownBoxDatas;
private List<List<Integer>> chartAnchor;
private List<byte[]> chartBytes;
public ExcelWriter(HttpServletResponse response) {
this.response = response;
}
/**
* 使用内存缓存（加快速度）
* @return
*/
public ExcelWriter<T> memory(){
workbook = new XSSFWorkbook();
return this;
}
/**
* 设置文件名
* @param fileName
* @return
*/
public ExcelWriter<T> setFileName(String fileName){
this.fileName = fileName;
return this;
}
/**
* 初始化响应流
* @return
* @throws IOException
*/
public ExcelWriter<T> initResponse() throws IOException {
response.setContentType(CONTENT_TYPE);
response.setCharacterEncoding(ENCODE);
// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
fileName = URLEncoder.encode(fileName, ENCODE).replaceAll("\\+", "%20");
response.setHeader("fileName",fileName+".xlsx");
this.outputStream = response.getOutputStream();
return this;
}
/**
* 分页
* @param total
* @return
*/
public ExcelWriter<T> paging(Integer total){
this.total = total;
return this;
}
/**
* 开启多线程
* @param total
* @return
*/
public ExcelWriter<T> multiThread(Integer total){
this.multiThread = true;
return paging(total);
}
/**
* 设置头
* @param heads
* @param rowIndex
* @param colIndex
* @return
*/
public ExcelWriter<T> setHead(List<String> heads,Integer rowIndex, Integer colIndex){
for (int i = 0; i < heads.size(); i++) {
setCell(heads.get(i),rowIndex,colIndex+i).setHeadStyle(rowIndex,colIndex+i);
}
return this;
}
/**
* 设置单元格内容
* @param value
* @param rowIndex
* @param colIndex
* @return
*/
public ExcelWriter<T> setCell(String value,Integer rowIndex,Integer colIndex){
if (CollUtil.isEmpty(cellValues)){
cellValues = new ArrayList<>();
}
if (CollUtil.isEmpty(cellValueIndex)){
cellValueIndex = new ArrayList<>();
}
cellValues.add(value);
cellValueIndex.add(ListUtils.newArrayList(rowIndex,colIndex));
return this;
}
/**
* 设置单元格内容
* @param value
* @param rowIndex
* @param colIndex
* @return
*/
private ExcelWriter<T> doSetCell(String value,Integer rowIndex,Integer colIndex){
Sheet sheet = getSheet();
Row row = getRow(sheet,rowIndex);
Cell cell = getCell(row, colIndex);
cell.setCellValue(value);
return this;
}
/**
* 获取sheet
* @return
*/
public Sheet getSheet(){
Sheet sheet = workbook.getSheet(curSheetName + sheetNo);
if (sheet == null){
sheet = workbook.createSheet(curSheetName + sheetNo);
}
return sheet;
}
/**
* 切换sheet
* @param sheetName
* @return
*/
public ExcelWriter<T> setCurSheetName(String sheetName){
this.curSheetName = sheetName;
return this;
}
/**
* 获取Row
* @param sheet
* @param rowIndex
* @return
*/
private Row getRow(Sheet sheet,Integer rowIndex){
Row row = sheet.getRow(rowIndex);
if (row == null){
row = sheet.createRow(rowIndex);
}
return row;
}
/**
* 获取cell
* @param row
* @param colIndex
* @return
*/
private Cell getCell(Row row,Integer colIndex){
Cell cell = row.getCell(colIndex);
if (cell == null){
cell = row.getCell(colIndex);
}
return cell;
}
/**
* 获取空白单元格样式，如果需要使用memory方法，，需要使用memory后调用
* @return
*/
public CellStyle getBlankCellStyle(){
return workbook.createCellStyle();
}
/**
* 设置单元格样式
* @param rowIndex
* @param colIndex
* @param cellStyle
* @return
*/
public ExcelWriter<T> setCellStyle(Integer rowIndex, Integer colIndex, CellStyle cellStyle){
if (CollUtil.isEmpty(cellStyleIndex)){
cellStyleIndex = new ArrayList<>();
}
if (CollUtil.isEmpty(cellStyles)){
cellStyles = new ArrayList<>();
}
cellStyleIndex.add(ListUtils.newArrayList(rowIndex,colIndex));
cellStyles.add(cellStyle);
return this;
}
/**
* 设置头样式
* @param rowIndex
* @param colIndex
* @return
*/
private ExcelWriter<T> setHeadStyle(Integer rowIndex, Integer colIndex){
CellStyle cellStyle = getBlankCellStyle();
//设置头样式
return setCellStyle(rowIndex,colIndex,cellStyle);
}
/**
* 设置列宽
* @param col
* @param width
* @return
*/
public ExcelWriter<T> setColumnWidth(Integer col,Integer width){
if (CollUtil.isEmpty(widths)){
widths = new ArrayList<>();
}
widths.add(ListUtils.newArrayList(col,width));
return this;
}
/**
* 设置列宽
* @param cols 0：列，1：列宽
* @return
*/
public ExcelWriter<T> setColumnWidth(List<List<Integer>> cols){
if (CollUtil.isEmpty(widths)){
widths = new ArrayList<>();
}
widths.addAll(cols);
return this;
}
/**
* 设置行高
* @param rows 0：行，1：行高
* @return
*/
public ExcelWriter<T> setRowHeight(List<List<Integer>> rows){
if (CollUtil.isEmpty(heights)){
heights = new ArrayList<>();
}
heights.addAll(rows);
return this;
}
/**
* 设置行高，列宽
* @param axisInfo 0：行，1：行高，2：列，3：列宽
* @return
*/
public ExcelWriter<T> setRowHeightAndColumnWidth(List<List<Integer>> axisInfo){
for (List<Integer> axis : axisInfo) {
setRowHeightAndColumnWidth(axis.get(0),axis.get(1),axis.get(2),axis.get(3));
}
return this;
}
/**
* 设置行高，列宽
* @param row
* @param rowHeight
* @param col
* @param colWidth
* @return
*/
public ExcelWriter setRowHeightAndColumnWidth(Integer row,Integer rowHeight,Integer col,Integer colWidth){
return setRowHeight(row, rowHeight).setColumnWidth(col, colWidth);
}
/**
* 设置默认列宽
* @param height
* @return
*/
public ExcelWriter<T> setDefaultRowHeight(Integer height){
this.defaultRowHeight = height;
return this;
}
/**
* 设置默认列宽
* @param width
* @return
*/
public ExcelWriter<T> setDefaultColumnWidth(Integer width){
this.defaultColumnWidth = width;
return this;
}
/**
* 设置默认行高，列宽
* @param height
* @param width
* @return
*/
public ExcelWriter<T> setDefaultRowHeightAndColumnWidth(Integer height,Integer width){
return setDefaultColumnWidth(width).setDefaultRowHeight(height);
}
/**
* 设置行高
* @param rowIndex
* @param height
* @return
*/
public ExcelWriter<T> setRowHeight(Integer rowIndex,Integer height){
heights.add(ListUtils.newArrayList(rowIndex,height));
return this;
}
/**
* 写数据
* @param query
* @param row
* @param col
* @return
* @throws IOException
* @throws ExecutionException
* @throws InterruptedException
*/
public ExcelWriter<T> write(Query<List<List<String>>> query,Integer row,Integer col) throws IOException, ExecutionException, InterruptedException {
if (curRowIndex == null){
curRowIndex = new AtomicInteger(row);
}
if (total == null){
write(query.accept(),curRowIndex.get(),col);
return this;
}
int totalPage = PageUtil.totalPage(total, query.getPageSize());
AtomicInteger pageNum = new AtomicInteger(0);
if (!multiThread){
write(query, curRowIndex.get(), col, pageNum,totalPage);
return this;
}
ThreadPoolExecutor poolExecutor = SpringBeanUtil.getBean(ThreadPoolExecutor.class);
List<Future<?>> futures = new ArrayList<>();
for (int i = 0; i < 4; i++) {
Future<?> future =poolExecutor.submit(() -> {
write(query, curRowIndex.get(), col, pageNum,totalPage);
});
futures.add(future);
}
for (Future<?> future : futures) {
future.get();
}
return this;
}
/**
* 写数据
* @param data
* @param row
* @param col
* @throws IOException
*/
public void write(List<List<String>> data, Integer row, Integer col) throws IOException {
if (curRowIndex == null) {
curRowIndex = new AtomicInteger(row);
}
if (curRowIndex.get() + data.size() > sheetMaxNum){
List<List<String>> left = ListUtil.sub(data, 0, sheetMaxNum - curRowIndex.get());
doWrite(left,curRowIndex.get(), col);
data = ListUtil.sub(data, sheetMaxNum - curRowIndex.get(), data.size());
curRowIndex.set(0);
sheetNo++;
}
doWrite(data,curRowIndex.get(), col);
}
private void write(Query<List<List<String>>> query, Integer row, Integer col, AtomicInteger pageNum,int totalPage) {
while (pageNum.get() < totalPage){
query.setPageNum(pageNum.incrementAndGet());
List<List<String>> accept = query.accept();
try {
write(accept, row+(pageNum.get()-1)*query.getPageSize(), col);
} catch (IOException e) {
throw new RuntimeException(e);
}
query.removePage();
}
}
/**
* 写数据
* @return
* @throws IOException
*/
public ExcelWriter<T> write() throws IOException {
workbook.write(outputStream);
return this;
}
/**
* 写数据
* @param data
* @param row
* @param col
* @throws IOException
*/
private void doWrite(List<List<String>> data, Integer row, Integer col) throws IOException {
synchronized (outputStream){
for (int i = 0; i < data.size(); i++) {
for (int j = 0; j < data.get(i).size(); j++) {
doSetCell(data.get(i).get(j),row+i,col+j);
}
}
curRowIndex.set(row+data.size());
write();
}
}
private List<List<String>> parseData(List<T> datas,List<String> fields){
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
List<List<String>> res = new ArrayList<>(datas.size());
for (T data : datas) {
JSONObject json = JSONUtil.parseObj(data);
List<String> 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.toString());
}
res.add(objects);
}
return res;
}
/**
* 编译基础信息
* @return
*/
public ExcelWriter<T> builder(){
Sheet sheet = getSheet();
sheet.setDefaultColumnWidth(defaultColumnWidth * 256);
sheet.setDefaultRowHeight(defaultRowHeight.shortValue());
if (widths != null){
for (List<Integer> width : widths) {
sheet.setColumnWidth(width.get(0),width.get(1));
}
}
if (heights != null){
for (List<Integer> height : heights) {
Row row = getRow(sheet, height.get(0));
row.setHeight(height.get(1).shortValue());
}
}
if (cellStyleIndex != null){
for (int i = 0; i < cellStyleIndex.size(); i++) {
List<Integer> index = cellStyleIndex.get(i);
CellStyle cellStyle = cellStyles.get(i);
Cell cell = getCell(getRow(sheet, index.get(0)), index.get(1));
cell.setCellStyle(cellStyle);
}
}
if (cellValueIndex != null){
for (int i = 0; i < cellValueIndex.size(); i++) {
List<Integer> index = cellValueIndex.get(i);
String value = cellValues.get(i);
doSetCell(value,index.get(0),index.get(1));
}
}
if (mergeInfo != null){
for (List<Integer> merge : mergeInfo) {
doMerge(merge.get(0),merge.get(1),merge.get(2),merge.get(3));
}
}
if (dropDownBoxDatas != null){
for (int i = 0; i < dropDownBoxIndex.size(); i++) {
List<Integer> index = dropDownBoxIndex.get(i);
List<String> data = dropDownBoxDatas.get(i);
doSetDropDownBox(data,index.get(0),index.get(1),index.get(2),index.get(3));
}
}
if (chartAnchor != null){
for (int i = 0; i < chartAnchor.size(); i++) {
List<Integer> anchorIndex = chartAnchor.get(i);
byte[] bytes = chartBytes.get(i);
Drawing<?> drawing =sheet.createDrawingPatriarch();
ClientAnchor anchor = drawing.createAnchor(anchorIndex.get(0), anchorIndex.get(1), anchorIndex.get(2), anchorIndex.get(3), anchorIndex.get(4),
anchorIndex.get(5), anchorIndex.get(6), anchorIndex.get(7));
// 参数分别代表 dx1, dy1, dx2, dy2, col1, row1, col2, row2
// 参数解释：
// dx1, dy1 - 图片左上角相对于单元格左上角的偏移量（以EMU为单位）
// dx2, dy2 - 图片右下角相对于单元格左上角的偏移量（以EMU为单位）
// col1, row1 - 图片左上角的起始单元格位置
// col2, row2 - 图片右下角的结束单元格位置
// 注意：EMU是Excel内部使用的单位，1英寸 = 914400 EMU
// 将图片添加到Workbook中，并获取图片的索引
int pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
// 创建图片并添加到Drawing对象中
drawing.createPicture(anchor, pictureIdx);
}
}
return this;
}
/**
* 设置图表
* @param bytes 图表数据
* @param dx1
* @param dy1
* @param dx2
* @param dy2
* @param col1
* @param row1
* @param col2
* @param row2
* @return
*/
public ExcelWriter<T> setChart(byte[] bytes,int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2){
if (chartAnchor == null){
chartAnchor = new ArrayList<>();
}
if (chartBytes == null){
chartBytes = new ArrayList<>();
}
chartBytes.add(bytes);
chartAnchor.add(ListUtils.newArrayList(dx1,dy1,dx2,dy2,col1,row1,col2,row2));
return this;
}
/**
* 合并单元格
* @param firstRow
* @param lastRow
* @param firstCol
* @param lastCol
*/
private void doMerge(Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol){
CellRangeAddress address = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
getSheet().addMergedRegion(address);
}
/**
* 合并单元格
* @param firstRow
* @param lastRow
* @param firstCol
* @param lastCol
* @return
*/
public ExcelWriter<T> merge(Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol){
if (mergeInfo == null){
mergeInfo = new ArrayList<>();
}
mergeInfo.add(ListUtils.newArrayList(firstRow,lastRow,firstCol,lastCol));
return this;
}
/**
* 合并单元格
* @param mergeInfo
* @return
*/
public ExcelWriter<T> merge(List<List<Integer>> mergeInfo){
if (this.mergeInfo == null){
this.mergeInfo = new ArrayList<>();
}
this.mergeInfo.addAll(mergeInfo);
return this;
}
; /**
* 设置下拉框
* @param data
* @param firstRow
* @param lastRow
* @param firstCol
* @param lastCol
* @return
*/
public ExcelWriter<T> setDropDownBox(List<String> data,Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol){
if (dropDownBoxIndex == null){
dropDownBoxIndex = new ArrayList<>();
}
if (dropDownBoxDatas == null){
dropDownBoxDatas = new ArrayList<>();
}
dropDownBoxIndex.add(ListUtils.newArrayList(firstRow,lastRow,firstCol,lastCol));
dropDownBoxDatas.add(data);
return this;
}
/**
* 设置下拉框
* @param data
* @param firstRow
* @param lastRow
* @param firstCol
* @param lastCol
*/
public void doSetDropDownBox(List<String> data,Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol){
Sheet sheet = getSheet();
DataValidationHelper validationHelper = sheet.getDataValidationHelper();
// 创建列表约束
DataValidationConstraint constraint = validationHelper.createExplicitListConstraint(data.toArray(new String[0]));
// 创建单元格范围，这里设置的是A1单元格
CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
// 创建数据验证对象
DataValidation validation = validationHelper.createValidation(constraint, addressList);
// 处理Excel中的错误警告
validation.setShowErrorBox(true);
validation.createErrorBox("错误", "您必须选择下拉列表中的选项之一。");
// 将数据验证添加到工作表中
sheet.addValidationData(validation);
}
}
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
package org.itxl.common.utils;
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;
}
}
}
关于导出工具类ExcelWriter对于图表的的导出方法的使用
- 通过JFreeChart绘制图片，转为byte数据调用方法使用
1.1 JFreeChart使用请查看Java绘图库JFreeChart的详细使用教程(入门级)
