springboot使用POI导出带下拉框的excel的工具类
1、导入依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.1</version>
</dependency>
2、标题注解
该注解主要用于类的属性上。
package com.xxx.common.annotations;
import java.lang.annotation.*;
/**
* @Auther: xzg
* @Date: 2023/8/17 14:12
* @Description:
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelFiledName {
/*
表头名称
*/
String value() default "";
/*
排序字段
*/
int order() default -1;
/*
表明该列是否为下拉框，默认是false。
*/
boolean dropDownBox() default false;
}
package com.xxx.common.bean.pojo;
/**
* @Author: xzg
* @Date: 2023/8/17 11:03
* @Description:
*/
public class PoiHeaderPojo {
//表头是否是下拉框，默认是false
private boolean whetherDropDownBox = false;
//表头内容
private String headerContent;
//下拉框数据
private String[] dropDownBoxList;
//属性名称
private String fieldName;
public boolean getWhetherDropDownBox() {
return whetherDropDownBox;
}
public void setWhetherDropDownBox(boolean whetherDropDownBox) {
this.whetherDropDownBox = whetherDropDownBox;
}
public String getHeaderContent() {
return headerContent;
}
public void setHeaderContent(String headerContent) {
this.headerContent = headerContent;
}
public boolean isWhetherDropDownBox() {
return whetherDropDownBox;
}
public String[] getDropDownBoxList() {
return dropDownBoxList;
}
public void setDropDownBoxList(String[] dropDownBoxList) {
this.dropDownBoxList = dropDownBoxList;
}
public String getFieldName() {
return fieldName;
}
public void setFieldName(String fieldName) {
this.fieldName = fieldName;
}
}
3、编写工具类
package com.xxx.common.util;
import com.xxx.util.StringUtils;
import com.xxx.common.annotations.ExcelFiledName;
import com.xxx.common.bean.pojo.PoiHeaderPojo;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidationConstraint;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* @Author: xzg
* @Date: 2022/1/4 16:40
* @Description:
*/
public class PoiUtil<T> {
private static final int XLS_MAX_ROW = 65535; //0开始
private static PoiUtil instance;
private PoiUtil() {
}
public static PoiUtil getInstance(){
if (instance == null){
synchronized (PoiUtil.class){
if (instance == null){
instance = new PoiUtil();
}
}
}
return instance;
}
public Workbook exportExcel(String sheetName, List<T> dataList,T t){
return getInstance().initSheet(sheetName, dataList,t, null);
}
public void convertPoiHeaderPojo(List<PoiHeaderPojo> headerPojos, T pojo){
Map<Integer, Field> customFiledMap = new TreeMap<>();
Class<?> aClass = pojo.getClass();
Field[] declaredFields = aClass.getDeclaredFields();
for (Field field : declaredFields) {
ExcelFiledName annotation = field.getAnnotation(ExcelFiledName.class);
if (annotation == null){
continue;
}
int order = annotation.order();
if (customFiledMap.containsKey(order)) {
throw new RuntimeException("order值不允许重复");
}
customFiledMap.put(order, field);
}
for (Integer integer : customFiledMap.keySet()) {
Field field = customFiledMap.get(integer);
PoiHeaderPojo headerPojo = new PoiHeaderPojo();
headerPojos.add(headerPojo);
ExcelFiledName annotation = field.getAnnotation(ExcelFiledName.class);
headerPojo.setFieldName(field.getName());
headerPojo.setHeaderContent(annotation.value());
headerPojo.setWhetherDropDownBox(annotation.dropDownBox());
try {
field.setAccessible(true);
Object o = field.get(pojo);
if (o instanceof String){
String var = (String) o;
String[] split = var.split(",");
headerPojo.setDropDownBoxList(split);
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
/**
* 初始化表格
*
* @param sheetName
* @param headerPojos
* @param dataList
* @param dateFormat
*/
private Workbook initSheet(String sheetName, List<T> dataList,
T t, String dateFormat){
if (sheetName == null){
sheetName = "sheet1";
}
List<PoiHeaderPojo> headerPojos = new ArrayList<>();
convertPoiHeaderPojo(headerPojos,t);
//如果headersName为空 则调用getEntityFieldName方法得到相应对象的field名作为表头行
//getEntityFieldName(headerPojos,dataList);
if (dateFormat == null) {
dateFormat = "yyyy-MM-dd HH:mm:ss";
}
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet(sheetName);
//表头样式
CellStyle headerStyle = wb.createCellStyle();
headerStyle.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
// 设置的背景颜色
headerStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
// 填充效果（全景填充）
headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// 设置字体
Font font = wb.createFont();
// 加粗
font.setBold(true);
headerStyle.setFont(font);
headerStyle.setBorderBottom(BorderStyle.THIN); //下边框
headerStyle.setBorderLeft(BorderStyle.THIN);//左边框
headerStyle.setBorderTop(BorderStyle.THIN);//上边框
headerStyle.setBorderRight(BorderStyle.THIN);//右边框
//生成sheet1内容
Row row = sheet.createRow(0);//第一个sheet的第一行为标题
sheet.createFreezePane(0, 1, 0, 1); //冻结第一行
//写标题
for (int i = 0; i < headerPojos.size(); i++) {
Cell cell = row.createCell(i); //获取第一行的每个单元格
sheet.setColumnWidth(i, 4000); //设置每列的列宽
cell.setCellStyle(style); //加样式
//cell.setCellType(CellType.STRING);
PoiHeaderPojo poiHeaderPojo = headerPojos.get(i);
String headerContent = poiHeaderPojo.getHeaderContent();
cell.setCellValue(headerContent); //往单元格里写数据
if (poiHeaderPojo.getWhetherDropDownBox()){
setDropDownBox(sheet,i,poiHeaderPojo);
}
}
if (dataList == null || dataList.size() == 0){
return wb;
}
//内容样式
CellStyle contentStyle = wb.createCellStyle();
//按headersId过滤dtoList中的数据 set进xls
for (int i = 0; i < dataList.size(); i++) {
row = sheet.createRow(i + 1);
T dtoT = dataList.get(i);
//列号
int k = 0;
for (PoiHeaderPojo headerPojo : headerPojos) {
String fieldName = headerPojo.getFieldName();
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);//拿到属性的get方法
Class dtoCls = dtoT.getClass();//拿到JavaBean对象
//通过JavaBean对象拿到该属性的get方法，从而进行操控
try {
Method getMethod = dtoCls.getMethod(getMethodName, new Class[]{});
Object val = getMethod.invoke(dtoT, new Object[]{});//操控该对象属性的get方法，从而拿到属性值
String textVal = null;
if (val != null) {
if (val instanceof Date) {//如果值的原类型为Date 则按dateFormat方式格式化为String类型
Date date = (Date) val;
SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
textVal = sdf.format(date);
} else {
textVal = String.valueOf(val);//转化成String
}
} else {
textVal = null;
}
Cell hCell = row.createCell(k);//创建单元格
hCell.setCellStyle(contentStyle);//设置数据单元格样式
hCell.setCellValue(textVal);//set单元格数据
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} finally {
k++;
}
}
}
return wb;
}
public static void setDropDownBox(Sheet sheet,int i,PoiHeaderPojo poiHeaderPojo){
// 为标题添加下拉框
DataValidationHelper dvHelper = sheet.getDataValidationHelper();
//创建一个隐藏的sheet作为下拉框的数据来源
createHiddenSheet(sheet,poiHeaderPojo);
//DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(poiHeaderPojo.getDropDownBoxList());
String strFormula = poiHeaderPojo.getFieldName() +"!$A$1:$A$" + poiHeaderPojo.getDropDownBoxList().length;
XSSFDataValidationConstraint dvConstraint = new XSSFDataValidationConstraint(DataValidationConstraint.ValidationType.LIST,strFormula);
CellRangeAddressList addressList = new CellRangeAddressList(1, XLS_MAX_ROW, i, i);
DataValidation validation = dvHelper.createValidation(dvConstraint, addressList);
validation.setSuppressDropDownArrow(true);
validation.setEmptyCellAllowed(true);
validation.setShowPromptBox(true);
validation.setShowErrorBox(true);
validation.createErrorBox("Error", "请选择下拉框中的数据");
validation.createPromptBox("提示", "只能选择下拉框里面的数据");
sheet.addValidationData(validation);
}
public static void createHiddenSheet(Sheet sheet,PoiHeaderPojo poiHeaderPojo) {
Workbook workbook = sheet.getWorkbook();
String fieldName = poiHeaderPojo.getFieldName();
Sheet hiddenSheet = workbook.createSheet(fieldName);
//设置隐藏sheet
workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet),true);
String[] dropDownBoxList = poiHeaderPojo.getDropDownBoxList();
for (int i = 0; i < dropDownBoxList.length; i++) {
Row row = hiddenSheet.createRow(i);
Cell cell = row.createCell(0);
String var = dropDownBoxList[i];
cell.setCellValue(var);
}
}
// public static void main(String[] args) {
//
// List<PoiHeaderPojo> headerPojoList = new ArrayList<>();
//
// Workbook workbook = getInstance().exportExcel(null,null);
//
//
//
// try (FileOutputStream outputStream = new FileOutputStream("/home/excel.xlsx")) {
// workbook.write(outputStream);
// } catch (IOException e) {
// e.printStackTrace();
// }
// }
}
4、web导出示例
编写实体类
package com.xxx.common.bean;
import com.xxx.common.annotations.ExcelFiledName;
import java.math.BigDecimal;
/**
* @Author: xzg
* @Date: 2023/8/16 14:12
* @Description:
*/
public class ExportEntity {
//姓名
@ExcelFiledName(value = "姓名",order = 1)
private String userName;
//性别
@ExcelFiledName(value = "性别",order = 2,dropDownBox = true)
private String sex;
//地址
@ExcelFiledName(value = "地址",order = 3)
private String address;
//setter/getter方法
}
/**
导出模板接口
* @param request
* @param response
* @return
*/
@GetMapping("/exportTemplate")
public void exportTemplate(HttpServletRequest request, HttpServletResponse response){
OutputStream os = null;
try{
ExportEntity exportEntity = new ExportEntity();
exportEntity.setUserName("姓名");
exportEntity.setSex("男,女");
exportEntity.setAddress("上海");
String fileName = String.valueOf(System.currentTimeMillis());
os = PoiUtil.getOutputStream(fileName,request,response);
PoiUtil.getInstance().exportExcel(null,null,exportEntity).write(os);
os.flush();
}catch (Exception e){
e.printStackTrace();
}finally {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
