效果图
1.引入easyExcel pom
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.3.1</version>
</dependency>2.工具类-自定义样式handler-CustomCellWriteHandler
import java.util.HashMap;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.BooleanUtils;
import com.alibaba.excel.write.handler.AbstractCellWriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
/**
* easyexcel自定义行列样式
* 表头背景色，合计行背景色-字体颜色，
* @author cf
*
* @date 2023-11-28 11:18:23
*/
public class CustomCellWriteHandler extends AbstractCellWriteHandler {
// 字体颜色
private Short colorIndex;
// 背景颜色
private Short bgColorIndex;
// 行，以及对应的列，多个列逗号拼接,全列，-1
private HashMap<Integer, String> rowColMap;
/**
*
* @param bgColorIndex -1 不设置
* @param colorIndex -1 不设置
* @param rowColMap value = -1,整行
*/
public CustomCellWriteHandler(Short bgColorIndex, Short colorIndex, HashMap<Integer, String> rowColMap) {
this.colorIndex = colorIndex;
this.bgColorIndex = bgColorIndex;
this.rowColMap = rowColMap;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
// 设置行高
int rowIndex = row.getRowNum();
System.out.println("当前行: " + rowIndex);
short height = 600;
row.setHeight(height);
}
@Override
public void afterCellDispose(CellWriteHandlerContext context) {
Cell cell = context.getCell();
int rowIndex = cell.getRowIndex();
int cellIndex = cell.getColumnIndex();
// 自定义宽度处理
// 自定义样式处理
// 当前事件会在 数据设置到poi的cell里面才会回调
// 判断不是头的情况 如果是fill 的情况 这里会==null 所以用not true
Boolean head = context.getHead();
if (BooleanUtils.isNotTrue(head)) {
// 指定行 列设置背景色
if (null != rowColMap && rowColMap.get(rowIndex) != null && !rowColMap.get(rowIndex).equals("-1")
&& rowColMap.get(rowIndex).contains(cellIndex + "")) {
setCellStyle(context, cell);
// 某几行设置背景色
} else if (null != rowColMap && rowColMap.get(rowIndex) != null && rowColMap.get(rowIndex).equals("-1")) {
setCellStyle(context, cell);
}
// if (cell.getColumnIndex() == 8 && cell.getStringCellValue().contains("已通过")) {
// // 拿到poi的workbook
// Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
// // 这里千万记住 想办法能复用的地方把他缓存起来 一个表格最多创建6W个样式
// // 不同单元格尽量传同一个 cellStyle
// CellStyle cellStyle = workbook.createCellStyle();
// cellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
// // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND
// cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// cell.setCellStyle(cellStyle);
// // 由于这里没有指定dataformat 最后展示的数据 格式可能会不太正确
// // 这里要把 WriteCellData的样式清空， 不然后面还有一个拦截器 FillStyleCellWriteHandler 默认会将 WriteCellStyle 设置到
// // cell里面去 会导致自己设置的不一样（很关键）
// context.getFirstCellData().setWriteCellStyle(null);
// }
} else {
// 表头
}
}
private void setCellStyle(CellWriteHandlerContext context, Cell cell) {
Workbook workbook = context.getWriteWorkbookHolder().getWorkbook();
Font font = workbook.createFont();
font.setFontName("宋体");
font.setBold(true);
if (colorIndex >= 0) {
font.setColor(colorIndex);
}
// font.setFontHeightInPoints((short)11);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(bgColorIndex);
if (bgColorIndex >= 0) {
cellStyle.setFillForegroundColor(bgColorIndex);
}
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cellStyle.setFont(font);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setBorderBottom(BorderStyle.MEDIUM);
cellStyle.setBorderLeft(BorderStyle.MEDIUM);
cellStyle.setBorderRight(BorderStyle.MEDIUM);
cellStyle.setBorderTop(BorderStyle.MEDIUM);
// 样式写入单元格
cell.setCellStyle(cellStyle);
// 由于这里没有指定dataformat 最后展示的数据 格式可能会不太正确
// 这里要把 WriteCellData的样式清空， 不然后面还有一个拦截器 FillStyleCellWriteHandler 默认会将
// WriteCellStyle 设置到
// cell里面去 会导致自己设置的不一样
context.getFirstCellData().setWriteCellStyle(null);
}
/**
* 表头背景色等设置
*
* @return
*/
public static HorizontalCellStyleStrategy getStyleStrategy() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 设置对齐
// headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
// 背景色, 设置为绿色，也是默认颜色
headWriteCellStyle.setFillForegroundColor(IndexedColors.CORNFLOWER_BLUE.getIndex());
// 字体
// WriteFont headWriteFont = new WriteFont();
// headWriteFont.setFontHeightInPoints((short) 12);
// headWriteCellStyle.setWriteFont(headWriteFont);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了
// FillPatternType所以可以不指定
// contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
// 背景绿色
contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE1.getIndex());
// 字体策略
WriteFont contentWriteFont = new WriteFont();
// contentWriteFont.setFontHeightInPoints((short) 12);
contentWriteCellStyle.setWriteFont(contentWriteFont);
// 设置 自动换行
contentWriteCellStyle.setWrapped(true);
// 设置 垂直居中
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置 水平居中
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 设置边框样式
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle,
contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
}3.工具类-导出 ExcelUtils
package com.yintong.xgj.common.util.excel;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Set;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.beans.BeanUtils;
import org.springframework.web.multipart.MultipartFile;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.util.DateUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
/**
* excel工具类
*
*/
public class ExcelUtils {
/**
* Excel导出
*
* @param response response
* @param fileName 文件名
* @param sheetName sheetName
* @param list 数据List
* @param pojoClass 对象Class
* @param columns 自定义列表
*/
public static void exportExcel(HttpServletResponse response, String fileName, String sheetName, List<?> list,
Class<?> pojoClass, Set<String> columns) throws IOException {
if (StringUtils.isBlank(fileName)) {
// 当前日期
fileName = DateUtils.format(new Date());
}
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("UTF-8");
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
// 写入合计行样式；默认表头只有一行
// TotalRowHandler totalRowHandler = new TotalRowHandler(list.size());
// 指定行列
HashMap<Integer, String> rowColMap = new HashMap<>();
// rowColMap.put(1,"1,2");
// rowColMap.put(2,"2");
rowColMap.put(list.size(), "-1");// 合计行，最后一行设置颜色
CustomCellWriteHandler totalRowHandler = new CustomCellWriteHandler((short) -1, IndexedColors.RED.index,
rowColMap);
if (columns != null && columns.size() > 0) {// 自定义列
EasyExcel.write(response.getOutputStream(), pojoClass).includeColumnFieldNames(columns)
.registerWriteHandler(CustomCellWriteHandler.getStyleStrategy())
.registerWriteHandler(totalRowHandler).sheet(sheetName).doWrite(list);
} else {
EasyExcel.write(response.getOutputStream(), pojoClass)
.registerWriteHandler(CustomCellWriteHandler.getStyleStrategy())
.registerWriteHandler(totalRowHandler).sheet(sheetName).doWrite(list);
}
}
/**
* Excel导出，先sourceList转换成List<targetClass>，再导出
*
* @param response response
* @param fileName 文件名
* @param sheetName sheetName
* @param sourceList 原数据List
* @param targetClass 目标对象Class
*/
public static void exportExcelToTarget(HttpServletResponse response, String fileName, String sheetName,
List<?> sourceList, Class<?> targetClass, Set<String> columns) throws Exception {
List<Object> targetList = new ArrayList<Object>(sourceList.size());
for (Object source : sourceList) {
Object target = targetClass.newInstance();
BeanUtils.copyProperties(source, target);
targetList.add(target);
}
exportExcel(response, fileName, sheetName, targetList, targetClass, columns);
}
/**
* 导入
*
* @param file
* @param targetClass
* @return
* @throws Exception
*/
public static List<Object> importExcelToTarget(MultipartFile file, Class<?> targetClass) throws Exception {
List<Object> list = EasyExcel.read(file.getInputStream()).head(targetClass).sheet().doReadSync();
return list;
}
}4.demo
实体
import java.math.BigDecimal;
import java.util.Date;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.HeadStyle;
import com.yintong.xgj.entity.enums.TypeStateConvert;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
@Data
@ApiModel(value = "采购入库导出", description = "")
@HeadStyle(fillBackgroundColor = 24)
public class GoodsInExcel {
@ApiModelProperty(value = "工单号")
@ExcelProperty(value = "工单号")
private String orderNo;
@ApiModelProperty(value = "时间" )
@ExcelProperty(value = "时间" )
@ColumnWidth(20)
@DateTimeFormat(value = "yyyy-MM-dd HH:mm:ss")
private Date actionTime;
@ApiModelProperty(value = "单据类型")
@ExcelProperty(value = "单据类型" ,converter = TypeStateConvert.class)
@ColumnWidth(15)
private Integer typeState;
@ApiModelProperty(value = "配件编码")
@ExcelProperty(value = "配件编码")
@ColumnWidth(15)
private String postionNumber;
@ApiModelProperty(value = "配件名称")
@ExcelProperty(value = "配件名称")
@ColumnWidth(15)
private String gname;
@ApiModelProperty(value = "规格")
@ExcelProperty(value = "规格")
private String spec;
@ApiModelProperty(value = "单位")
@ExcelProperty(value = "单位")
private String unit;
@ApiModelProperty(value = "数量")
@ExcelProperty(value = "数量")
private BigDecimal numbers;
@ApiModelProperty(value = "单价")
@ExcelProperty(value = "单价")
private BigDecimal cost;
@ApiModelProperty(value = "金额")
@ExcelProperty(value = "金额")
private BigDecimal price;
@ApiModelProperty(value = "供应商")
@ExcelProperty(value = "供应商")
private String ftname;
@ApiModelProperty(value = "采购人")
@ExcelProperty(value = "采购人")
private String acUserName;
@ApiModelProperty(value = "备注")
@ExcelProperty(value = "备注")
@ColumnWidth(30)
private String des;
public GoodsInExcel() {
};
}
接口
@ApiOperation(value = "导出",response = GoodsInExcel.class)
@GetMapping("inPageExport")
@Permit(permit = "inPageExport")
public void inPageExport(HttpServletResponse response,GoodsRecordQuery data,String columns) throws Exception {
//查询数据
List<Object> list = recordsService.exportList(data);
//设置自定义导出列
Set<String> hashSet = new HashSet<>();
if (columns != null) {
hashSet.addAll(Arrays.asList(columns.split(",")));
}
//获取或者计算合计行数据
BigDecimal reduce = list.stream().map(gr ->{return gr.getNumbers();}).reduce(BigDecimal.ZERO, BigDecimal::add);
BigDecimal reduce1 = list.stream().map(gr ->{return gr.getPrice();}).reduce(BigDecimal.ZERO, BigDecimal::add);
GoodsRecord hj = new GoodsRecord();
hj.setPrice(reduce1);
hj.setNumbers(reduce);
hj.setOrderNo("合计");
//加入到导出数据最后一行
list.add(hj);
//调用工具类导出接口
ExcelUtils.exportExcelToTarget(response, "明细表", "明细", list, GoodsInExcel.class,hashSet);
}
![[SpringCloud] SpringCloud配置中心的核心原理](https://img-blog.csdnimg.cn/d3cb3e27a4b64d7d8f7968362288c45d.png)
