本文用到的jar :
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.7</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.30</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.30</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
1、CellWriteHandler类用来操作Excel列属性
package hanjq.hanjqtest.easyexcel.select;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.StyleUtil;
import com.alibaba.excel.write.handler.CellWriteHandler;
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 lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import java.util.HashMap;
import java.util.List;
/**
* <pre>
* 操作标题行单元格颜色
* </pre>
*
* @author hanjq
* @version 0.1
*
* <pre>
* 修改记录
* 修改后版本:
* 修改人:
* 修改日期: 2021年9月27日16:39:21
* 修改内容:
* </pre>
*/
@Slf4j
public class TitleHandler implements CellWriteHandler {
//操作列
private List<Integer> columnIndexs;
//颜色
private Short colorIndex;
// 批注<列的下标,批注内容>
private HashMap<Integer,String> annotationsMap;
// 下拉框值
private HashMap<Integer,String[]> dropDownMap;
public TitleHandler(List<Integer> columnIndexs, Short colorIndex, HashMap<Integer, String> annotationsMap) {
this.columnIndexs = columnIndexs;
this.colorIndex = colorIndex;
this.annotationsMap = annotationsMap;
}
public TitleHandler(List<Integer> columnIndexs, Short colorIndex) {
this.columnIndexs = columnIndexs;
this.colorIndex = colorIndex;
}
public TitleHandler(List<Integer> columnIndexs, Short colorIndex, HashMap<Integer, String> annotationsMap, HashMap<Integer, String[]> dropDownMap) {
this.columnIndexs = columnIndexs;
this.colorIndex = colorIndex;
this.annotationsMap = annotationsMap;
this.dropDownMap = dropDownMap;
}
@Override
public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
}
@Override
public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
if(isHead){
// 设置列宽
Sheet sheet = writeSheetHolder.getSheet();
sheet.setColumnWidth(cell.getColumnIndex(), 14 * 256);
writeSheetHolder.getSheet().getRow(0).setHeight((short)(1.8*256));
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
Drawing<?> drawing = sheet.createDrawingPatriarch();
// 设置标题字体样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontName("宋体");
headWriteFont.setFontHeightInPoints((short)14);
headWriteFont.setBold(true);
if (CollectionUtils.isNotEmpty(columnIndexs) &&
colorIndex != null &&
columnIndexs.contains(cell.getColumnIndex())) {
// 设置字体颜色
headWriteFont.setColor(colorIndex);
}
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
CellStyle cellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);
cell.setCellStyle(cellStyle);
if (null != annotationsMap && annotationsMap.containsKey(cell.getColumnIndex())) {
// 批注内容
String context = annotationsMap.get(cell.getColumnIndex());
// 创建绘图对象
Comment comment=drawing.createCellComment(new XSSFClientAnchor(0, 0, 0,0, (short) cell.getColumnIndex(), 0, (short) 5, 5));
comment.setString(new XSSFRichTextString(context));
cell.setCellComment(comment);
}
if(null != dropDownMap &&
!dropDownMap.isEmpty() &&
dropDownMap.containsKey(cell.getColumnIndex())){
String[] datas = dropDownMap.get(cell.getColumnIndex());
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
DataValidationConstraint dvConstraint = dvHelper
.createExplicitListConstraint(datas);
CellRangeAddressList addressList = null;
DataValidation validation = null;
for (int i = 1; i < 1000; i++) {
addressList = new CellRangeAddressList(i, i, cell.getColumnIndex(), cell.getColumnIndex());
validation = dvHelper.createValidation(
dvConstraint, addressList);
sheet.addValidationData(validation);
}
}
}
}
}
2、EasyExcelUtil下载模板工具类
package hanjq.hanjqtest.easyexcel.select;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.builder.ExcelWriterSheetBuilder;
import com.alibaba.excel.write.handler.CellWriteHandler;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import java.io.OutputStream;
import java.util.List;
public class EasyExcelUtil {
/**
* 导出excel
* @param outputStream 输出流
* @param dataList 导出的数据
* @param classT 模板类
* @param sheetName sheetName
* @param cellWriteHandlers 样式处理类
*/
public static void writeExcelWithModel(OutputStream outputStream, List<? extends Object> dataList,
Class<? extends Object> classT, String sheetName, CellWriteHandler... cellWriteHandlers) {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 单元格策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 初始化表格样式
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.write(outputStream, classT).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy);
if (null != cellWriteHandlers && cellWriteHandlers.length > 0) {
for (int i = 0; i < cellWriteHandlers.length; i++) {
excelWriterSheetBuilder.registerWriteHandler(cellWriteHandlers[i]);
}
}
// 开始导出
excelWriterSheetBuilder.doWrite(dataList);
}
}
3、测试类
package hanjq.hanjqtest.easyexcel.select;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import lombok.Data;
import org.apache.poi.ss.usermodel.IndexedColors;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
public class Test{
@Data
@ColumnWidth(20) // 定义列宽
public static class TestVO {
@ExcelProperty(value = "*姓名", index = 0)
private String name;
@ExcelProperty(value = "*年龄", index = 1)
private int age;
@ExcelProperty(value = "学校", index = 2)
private String school;
}
/**
* 测试导出下拉款
* @throws IOException
*/
@org.junit.Test
public void testDropDown() throws IOException {
// 输出流
OutputStream outputStream = new FileOutputStream(new File("G:\\device_data\\1.xlsx"));
// 导出的数据
List<TestVO> dataList = new ArrayList<>();
// 指定标红色的列
List<Integer> columns = Arrays.asList(0, 1);
// 指定批注
HashMap<Integer, String> annotationsMap = new HashMap<>();
annotationsMap.put(0,"第一列标题批注");
annotationsMap.put(1,"第二列标题批注");
HashMap<Integer, String[]> dropDownMap = new HashMap<>();
// 指定下拉框
String[] ags = {"13","34","64"};
String[] school = {"一中","二中","三中"};
dropDownMap.put(1,ags);
dropDownMap.put(2,school);
TitleHandler titleHandler = new TitleHandler(columns, IndexedColors.RED.index,annotationsMap,dropDownMap);
EasyExcelUtil.writeExcelWithModel(outputStream, dataList, TestVO.class, "sheetName", titleHandler);
}
}
此类用的junit插件运行,之前没装过需要操作一下两个步骤: