easypoi实现excel导出下拉列表
整理一下工作中遇到导出excel下拉列表封装代码
1，导包
导入easypoi依赖包
//Excel
implementation 'cn.afterturn:easypoi-spring-boot-starter:4.4.0'
2，编写工具类
/**
* 下拉列表实现方法
* Excel 添加数据下拉列表的实现方法,使用方式如下<br/>
* 1. 定义map对象(key下拉数据所在的列,value下拉数据所在的内容)<br/>
* <code>
* Map<<Integer, List<DropDownTemplate>> dropDowmMap = new HashMap<>()<br/>
* map.put(1, 实体List)
* </code><br/>
* 2.导出Excel并且写入Excel下拉列表<br/>
* <code>
* ExportParams exportParams = new ExportParams("导出Excel顶部标题", "导出ExcelSheetName");<br/>
* exportParams.setStyle(ExcelStyleUtil.class);<br/>
* Workbook workbook = ExcelExportUtil.exportExcel(exportParams, 导出的实体对象(和导入的实体对象相同).class, new ArrayList<>());<br/>
* dropDowmMap.forEach((key, value) -> {<br/>
* DropDownGenerateReq req = DropDownGenerateReq.builder()<br/>
* .workbook(workbook).cellNum(key).sheetName("导出ExcelSheetName")<br/>
* .entityTemplates(value).build();<br/>
* ExcelDropDownUtils.generate(req);<br/>
* });<br/>
* ExcelUtils.downLoadExcel("导出Excel数据表", response, workbook);<br/>
* </code>
*
*
* @param req
*/
public static void generate(DropDownGenerateReq req) {
if (CollUtil.isEmpty(req.getEntityTemplates()) && CollUtil.isEmpty(req.getStringTemplates())) {
return;
}
//创建隐藏的Excel
String hiddenSheetName = HIDDENSHEET + req.getCellNum();
XSSFSheet hiddenSheet = (XSSFSheet) req.getWorkbook().createSheet(hiddenSheetName);
Row row;
XSSFDataValidationConstraint constraint;
//如果传递了实体模板则按照实体模板处理，否则按照字符串模板处理
if (CollUtil.isNotEmpty(req.getEntityTemplates())) {
//写入下拉数据到新的sheet页中
for (int i = 0; i < req.getEntityTemplates().size(); i++) {
row = hiddenSheet.createRow(i);
DropDownTemplate dropDownTemplate = req.getEntityTemplates().get(i);
Cell cell0 = row.createCell(0);
cell0.setCellValue(dropDownTemplate.getName());
Cell cell1 = row.createCell(1);
cell1.setCellValue(dropDownTemplate.getId());
if (StrUtil.isNotBlank(dropDownTemplate.getCode())) {
Cell cell2 = row.createCell(2);
cell2.setCellValue(dropDownTemplate.getCode());
}
if (StrUtil.isNotBlank(dropDownTemplate.getExtend1())) {
Cell cell3 = row.createCell(3);
cell3.setCellValue(dropDownTemplate.getExtend1());
}
if (StrUtil.isNotBlank(dropDownTemplate.getExtend2())) {
Cell cell4 = row.createCell(4);
cell4.setCellValue(dropDownTemplate.getExtend2());
}
}
//获取新sheet页内容
String strFormula = hiddenSheetName + "!$A$" + req.getStartRowNum() + ":$A$" + req.getEntityTemplates().size();
constraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST, strFormula);
} else {
constraint = new XSSFDataValidationConstraint(req.getStringTemplates().toArray(new String[]{}));
}
// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
CellRangeAddressList regions = new CellRangeAddressList(req.getStartRowNum(), req.getLastRowNum(), req.getCellNum(), req.getCellNum());
// 数据有效性对象
XSSFSheet sheet;
if (StrUtil.isEmpty(req.getSheetName())) {
sheet = (XSSFSheet) req.getWorkbook().getSheetAt(0);
} else {
sheet = (XSSFSheet) req.getWorkbook().getSheet(req.getSheetName());
}
DataValidationHelper help = new XSSFDataValidationHelper(sheet);
DataValidation validation = help.createValidation(constraint, regions);
sheet.addValidationData(validation);
//将新建的sheet页隐藏掉
req.getWorkbook().setSheetHidden(req.getWorkbook().getNumberOfSheets() - 1, true);
}
/**
* 下载
*
* @param fileName 文件名称
* @param response
* @param workbook excel数据
*/
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
ServletOutputStream out = null;
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getName(), "UTF-8"));
out = response.getOutputStream();
workbook.write(out);
} catch (Exception e) {
throw new IOException(e.getMessage());
} finally {
if (out != null) {
out.close();
}
if (workbook != null) {
workbook.close();
}
}
}
/**
* easypoi生成excel方法
* @param entity 表格标题属性
* @param pojoClass Excel对象Class
* @param dataSet Excel对象数据List
*/
public static Workbook exportExcel(ExportParams entity, Class<?> pojoClass,
Collection<?> dataSet) {
Workbook workbook = getWorkbook(entity.getType(), dataSet.size());
new ExcelExportService().createSheet(workbook, entity, pojoClass, dataSet);
return workbook;
}
/**
* excel导出样式
*/
public class ExcelStyleUtil implements IExcelExportStyler {
private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
private static final short FONT_SIZE_TEN = 9;
private static final short FONT_SIZE_ELEVEN = 10;
private static final short FONT_SIZE_TWELVE = 10;
/**
* 大标题样式
*/
private CellStyle headerStyle;
/**
* 每列标题样式
*/
private CellStyle titleStyle;
/**
* 数据行样式
*/
private CellStyle styles;
public ExcelStyleUtil(Workbook workbook) {
this.init(workbook);
}
/**
* 初始化样式
*
* @param workbook
*/
private void init(Workbook workbook) {
this.headerStyle = initHeaderStyle(workbook);
this.titleStyle = initTitleStyle(workbook);
this.styles = initStyles(workbook);
}
/**
* 大标题样式
*
* @param color
* @return
*/
@Override
public CellStyle getHeaderStyle(short color) {
return headerStyle;
}
/**
* 每列标题样式
*
* @param color
* @return
*/
@Override
public CellStyle getTitleStyle(short color) {
return titleStyle;
}
/**
* 数据行样式
*
* @param parity 可以用来表示奇偶行
* @param entity 数据内容
* @return 样式
*/
@Override
public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
return styles;
}
/**
* 获取样式方法
*
* @param dataRow 数据行
* @param obj 对象
* @param data 数据
*/
@Override
public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
return getStyles(true, entity);
}
/**
* 模板使用的样式设置
*/
@Override
public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
return null;
}
/**
* 初始化--大标题样式
*
* @param workbook
* @return
*/
private CellStyle initHeaderStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
return style;
}
/**
* 初始化--每列标题样式
*
* @param workbook
* @return
*/
private CellStyle initTitleStyle(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
//背景色
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
return style;
}
/**
* 初始化--数据行样式
*
* @param workbook
* @return
*/
private CellStyle initStyles(Workbook workbook) {
CellStyle style = getBaseCellStyle(workbook);
style.setFont(getFont(workbook, FONT_SIZE_TEN, false));
style.setDataFormat(STRING_FORMAT);
return style;
}
/**
* 基础样式
*
* @return
*/
private CellStyle getBaseCellStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
//下边框
style.setBorderBottom(BorderStyle.THIN);
//左边框
style.setBorderLeft(BorderStyle.THIN);
//上边框
style.setBorderTop(BorderStyle.THIN);
//右边框
style.setBorderRight(BorderStyle.THIN);
//水平居中
style.setAlignment(HorizontalAlignment.CENTER);
//上下居中
style.setVerticalAlignment(VerticalAlignment.CENTER);
//设置自动换行
style.setWrapText(true);
return style;
}
/**
* 字体样式
*
* @param size 字体大小
* @param isBold 是否加粗
* @return
*/
private Font getFont(Workbook workbook, short size, boolean isBold) {
Font font = workbook.createFont();
//字体样式
font.setFontName("宋体");
//是否加粗
font.setBold(isBold);
//字体大小
font.setFontHeightInPoints(size);
return font;
}
}
3，实体辅助类
/**
* Excel生成下拉列表的对象实体
* @since 2022/11/15
*/
@Data
@Builder
public class DropDownTemplate {
/**
* Id
*/
@Excel(name = "id")
private String id;
/**
* code
*/
@Excel(name = "code")
private String code;
/**
* 名称
*/
@Excel(name = "name")
private String name;
/**
* 扩展字段1
*/
@Excel(name = "extend1")
private String extend1;
/**
* 扩展字段2
*/
@Excel(name = "extend2")
private String extend2;
public DropDownTemplate() {
}
public DropDownTemplate(String id, String name) {
this.id = id;
this.name = name;
}
public DropDownTemplate(String id, String code, String name) {
this.id = id;
this.code = code;
this.name = name;
}
public DropDownTemplate(String id, String code, String name, String extend1, String extend2) {
this.id = id;
this.code = code;
this.name = name;
this.extend1 = extend1;
this.extend2 = extend2;
}
}
/**
* Excel生成下拉列表框请求参数
*
* @date 2022/11/18
*/
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class DropDownGenerateReq {
/**
* 必填-Excel的WorkBook对象
*/
private Workbook workbook;
/**
* 必填-下拉数据所在的列
*/
private Integer cellNum;
/**
* sheet名称
*/
private String sheetName;
/**
* 下拉数据起始行数
*/
@Builder.Default
private Integer startRowNum = 2;
/**
* 下拉数据结束行数
*/
@Builder.Default
private Integer lastRowNum = 999;
/**
* 下拉数据实体模板
*/
private List<DropDownTemplate> entityTemplates;
/**
* 下拉数据字符串数据模板
*/
private List<String> stringTemplates;
}
3，controller层调用
/**
* 获取人员台账导入模板
*
* @param departmentId
* @return
*/
@SneakyThrows(IOException.class)
@GetMapping("public/getImportTemplate")
@ApiOperation(value = "获取台账导入模板")
public void getImportTemplate(HttpServletResponse response) {
//查询下拉需要用到的数组集合
Map<Integer, List<DropDownTemplate>> dropDowmMap = new HashMap<>();
List<DropDownTemplate> dataList = new ArrayList<>();
dataList.add(new DropDownTemplate("1", "中国"));
dataList.add(new DropDownTemplate("2", "韩国"));
dropDowmMap.put(1, dataList);
//导出Excel文档
String title = "台账模板";
String sheetName = "台账模板";
ExportParams exportParams = new ExportParams(title, sheetName);
exportParams.setStyle(ExcelStyleUtil.class);
Workbook workbook = ExcelExportUtil.exportExcel(exportParams, PersonExcelImportDto.class, new ArrayList<>());
dropDowmMap.forEach((key, value) -> {
DropDownGenerateReq req = DropDownGenerateReq.builder()
.workbook(workbook).cellNum(key).sheetName("台账模板")
.entityTemplates(value).build();
ExcelDropDownUtils.generate(req);
});
//将身份证这一列设置成文本格式
//CellStyle textStyle = workbook.createCellStyle();
//textStyle.setDataFormat((short) //BuiltinFormats.getBuiltinFormat("TEXT"));
//Sheet sheet = workbook.getSheet(sheetName);
//sheet.setDefaultColumnStyle(6, textStyle);
ExcelUtils.downLoadExcel("台账数据表", response, workbook);
本代码包含所有的实体及工具方法，直接复制即可使用
扫一扫
3999
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
