exsyexcel导入导出功能(包含excel表格样式后端生成)
1、导入依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
2、创建工具类
2.1 ExcelUtil
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.metadata.BaseRowModel;
import com.alibaba.excel.metadata.Sheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.List;
public class EasyExcelUtil {
public static <T> void exportExcel(List<T> list, HttpServletResponse response, String sheetName,Class<T> clz){
try(OutputStream fileOutputStream = response.getOutputStream()){
//文件以流形式返回前端下载
response.setHeader("Content-Disposition", "attachment;filename=" + sheetName + ".xlsx");
response.setContentType("application/x-download");
response.setCharacterEncoding("UTF-8");
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
response.flushBuffer();
ExcelWriter excelWriter = EasyExcel.write(fileOutputStream,clz)
.registerWriteHandler(new CustomCellWriteUtil())
.registerWriteHandler(getStyleStrategy())
.build();
WriteSheet sheet = EasyExcel.writerSheet(0, "导出数据").build();
excelWriter.write(list, sheet);
excelWriter.finish();
fileOutputStream.flush();
}catch (Exception e){
e.printStackTrace();
throw new RuntimeException(e);
}
}
/**
* 设置excel样式
*
* @return
*/
public static HorizontalCellStyleStrategy getStyleStrategy() {
// 头的策略 样式调整
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 头背景 浅绿
headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
WriteFont headWriteFont = new WriteFont();
// 头字号
headWriteFont.setFontHeightInPoints((short) 14);
// 字体样式
headWriteFont.setFontName("宋体");
headWriteCellStyle.setWriteFont(headWriteFont);
// 自动换行
headWriteCellStyle.setWrapped(true);
// 设置细边框
headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
headWriteCellStyle.setBorderRight(BorderStyle.THIN);
headWriteCellStyle.setBorderTop(BorderStyle.THIN);
// 设置边框颜色 25灰度
headWriteCellStyle.setBottomBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
headWriteCellStyle.setTopBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
headWriteCellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
headWriteCellStyle.setRightBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
// 水平对齐方式
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 垂直对齐方式
headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 内容的策略 宋体
WriteCellStyle contentStyle = new WriteCellStyle();
// 设置垂直居中
contentStyle.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置 水平居中
contentStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteFont contentWriteFont = new WriteFont();
// 内容字号
contentWriteFont.setFontHeightInPoints((short) 12);
// 字体样式
contentWriteFont.setFontName("宋体");
contentStyle.setWriteFont(contentWriteFont);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentStyle);
}
}
2.2 CustomCellWriteUtil
import com.alibaba.excel.enums.CellDataTypeEnum;
import com.alibaba.excel.metadata.CellData;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.util.CollectionUtils;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* excel自适应列宽
*/
public class CustomCellWriteUtil extends AbstractColumnWidthStyleStrategy {
private static final int MAX_COLUMN_WIDTH = 255;
private Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);
public CustomCellWriteUtil() {
}
protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
if (needSetWidth) {
Map<Integer, Integer> maxColumnWidthMap = (Map) CACHE.get(writeSheetHolder.getSheetNo());
if (maxColumnWidthMap == null) {
maxColumnWidthMap = new HashMap(16);
CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
}
Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
if (columnWidth >= 0) {
if (columnWidth > MAX_COLUMN_WIDTH) {
columnWidth = MAX_COLUMN_WIDTH;
}
Integer maxColumnWidth = (Integer) ((Map) maxColumnWidthMap).get(cell.getColumnIndex());
if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
((Map) maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
}
}
}
}
private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
if (isHead) {
return cell.getStringCellValue().getBytes().length;
} else {
CellData cellData = (CellData) cellDataList.get(0);
CellDataTypeEnum type = cellData.getType();
if (type == null) {
return -1;
} else {
switch (type) {
case STRING:
return cellData.getStringValue().getBytes().length;
case BOOLEAN:
return cellData.getBooleanValue().toString().getBytes().length;
case NUMBER:
return cellData.getNumberValue().toString().getBytes().length;
default:
return -1;
}
}
}
}
}
3、自定义注解(标记下拉框字段)无下拉框可忽略
3.1 DropDownSetField.java
import java.lang.annotation.*;
/**
* 注解:自定义标记导出excel的下拉数据集
*/
@Documented
// 作用在字段上
@Target(ElementType.FIELD)
// 运行时有效
@Retention(RetentionPolicy.RUNTIME)
public @interface DropDownSetField {
// 固定下拉内容
String[] source() default {};
// 注解内的名称,解析时要注意对应
String name() default "";
}
3.2 ResolveDropAnnotationUtil
import java.util.Map;
import java.util.Optional;
/**
* 处理导入excel下拉框注解的工具类
*/
public class ResolveDropAnnotationUtil {
public static String[] resove(DropDownSetField dropDownSetField, String[] strings) {
if (!Optional.ofNullable(dropDownSetField).isPresent()) {
return null;
}
// 获取固定下拉信息
String[] source = dropDownSetField.source();
if (null != source && source.length > 0) {
return source;
}
if (null != strings && strings.length > 0) {
try {
String[] dynamicSource = strings;
if (null != dynamicSource && dynamicSource.length > 0) {
return dynamicSource;
}
} catch (Exception e) {
e.printStackTrace();
}
}
return null;
}
//插入到map中
public static void insertMap(Map<Integer, String[]> map, String[] params, DropDownSetField dropDownSetField, int i) {
String[] sources = ResolveDropAnnotationUtil.resove(dropDownSetField, params);
if (null != sources && sources.length > 0) {
map.put(i, sources);
}
}
}
4、Controller
@RequestMapping(value = "/exportExcel")
@ResponseBody
public String exportExcel(Commom commom,HttpServletResponse response,String sheetName) {
//查询数据
try{
//todo 根据条件查询data
List<DataVo> list = new ArrayList<>();
for (DataRecord dataRecord : data) {
DataVo dataVo = new DataVo();
BeanUtils.copyProperties(dataRecord, dataVo);
//SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); 有需要可以转换日期格式
list.add(dataVo);
}
if(CollectionUtils.isEmpty(list)){
return "导出失败!";
}
EasyExcelUtil.exportExcel(list,response,sheetName,DataVo.class);
}catch (Exception e){
logger.error(e);
throw new RuntimeException();
}
return "导出成功!";
}
@RequestMapping(value="/importExcel")
@ResponseBody
public String upload(MultipartFile file) throws IOException {
try{
EasyExcel.read(file.getInputStream(), DataVo.class,new DataListener(dataService)).sheet().doRead();
return "success";
}catch (Exception e){
e.printStackTrace();
logger.error(e.getMessage());
}
return "导入失败!";
}
@RequestMapping("/dropDownTemplate")
public void downTemplate(HttpServletResponse response) {
try {
// 获取该类声明的所有字段
Field[] fields = DataVo.class.getDeclaredFields();
// 响应字段对应的下拉集合
Map<Integer, String[]> map = new HashMap<>();
Field field = null;
// 循环判断哪些字段有下拉数据集,并获取
for (int i = 0; i < fields.length; i++) {
field = fields[i];
// 判断注解信息
DropDownSetField dropDownSetField = field.getAnnotation(DropDownSetField.class);
if (null != dropDownSetField) {
String name = dropDownSetField.name();
if (!StringUtils.isEmpty(name)) {
String[] params = new String[0];
// DropDownNameEnum 对应下拉框注解里面的字段名称,要和导出实体类中的name属性对应上
if (name.equals(DropDownNameEnum.COMPANY_TYPE_ENUM.getName())) {
params = Arrays.stream(RoomTypeEnum.values()).map(RoomTypeEnum::getType).toArray(String[]::new);
}else if (name.equals(DropDownNameEnum.Pay_TYPE_ENUM.getName())){
params = Arrays.stream(PayFeesEnum.values()).map(PayFeesEnum::getType).toArray(String[]::new);
}
ResolveDropAnnotationUtil.insertMap(map, params, dropDownSetField, i);
} else {
ResolveDropAnnotationUtil.insertMap(map, null, dropDownSetField, i);
}
}
}
//文件以流形式返回前端下载
String fileName = "ImportTemplate.xlsx";
OutputStream fileOutputStream = null;
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.setContentType("application/x-download");
response.setCharacterEncoding("UTF-8");
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
response.flushBuffer();
fileOutputStream = response.getOutputStream();
ExcelWriter excelWriter = EasyExcel.write(fileOutputStream, DataVo.class)
.registerWriteHandler(new DataImportHandler(map))
.registerWriteHandler(new CustomCellWriteUtil())
.registerWriteHandler(EasyExcelUtil.getStyleStrategy()).build();
WriteSheet sheet = EasyExcel.writerSheet(0, "导入模板").build();
excelWriter.write(null, sheet);
excelWriter.finish();
fileOutputStream.flush();
fileOutputStream.close();
} catch (Exception e) {
logger.info("下载导入模板出错,原因:{}", e);
}
}
5、枚举类
5.1 DropDownNameEnum.java
/**
* excel模板下拉框名称枚举
*/
public enum DropDownNameEnum {
COMPANY_TYPE_ENUM("Drop001","企业类型"),
Pay_TYPE_ENUM("Drop002","缴费状态"),
RENT_CYCLE_ENUM("Drop003","租金结算周期");
private String code;
private String name;
private DropDownNameEnum(String code, String name){
this.code = code;
this.name = name;
}
public String getCode() {
return code;
}
public String getName() {
return name;
}
public void setCode(String code) {
this.code = code;
}
public void setName(String name) {
this.name = name;
}
}
CompanyTypeEnum.java
/**
* 公司类型
*/
public enum CompanyTypeEnum {
GY(1,"国有"),SY(2,"私营");
private int num;
private String type;
CompanyTypeEnum(int num,String type) {
this.num = num;
this.type = type;
}
public int getNum() {
return num;
}
public void setNum(int num) {
this.num = num;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
}
6、实体类
6.1DataVo.java
这里是因为我domain层的字段太多太杂,抽取出来专门导出的字段
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.metadata.BaseRowModel;
import com.sunente.framework.utils.DropDownSetField;
import java.text.ParseException;
@Data
public class DataVo{
@ExcelProperty(value = "企业名称")
private String company;
@ExcelProperty(value = "企业类型")
//这里自定义注解 标志这是下拉框字段
@DropDownSetField(name = "企业类型")
private String companyTypeName;
//时间格式因为待会要做格式判断 所以加了这个index
@ExcelProperty(value = "入驻时间",index = 2)
private String enterTime;
@ExcelProperty(value = "到期时间",index = 3)
private String expirationTime;
@ExcelProperty(value = "缴费状态")
//这里自定义注解 标志这是下拉框字段
@DropDownSetField(name = "缴费状态")
private String statusName;
}
7、监听器
7.1DataListener.java
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import org.springframework.beans.BeanUtils;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class DataListener extends AnalysisEventListener<DataVo> {
private DataService dataService;
private List<Data> list = new ArrayList<>();
public DataListener(DataService dataService) {
this.dataService = dataService;
}
/**
* 批处理阈值500
*/
private static final int BATCH_COUNT = 500;
@Override
public void invoke(DataVo dataVo, AnalysisContext analysisContext) {
try {
Data data = new Data();
BeanUtils.copyProperties(dataVo,data);
//转一下日期格式
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd");
data.setEnterTime(simpleDateFormat.parse(dataVo.getEnterTime()));
//将数据添加到List集合中
list.add(enterCompany);
if (list.size() >= BATCH_COUNT) {
dataService.insertBatch(list);
list.clear();
}
} catch (ParseException e) {
e.printStackTrace();
throw new RuntimeException(e.getMessage());
}
}
//最后队列不满阈值的,全部处理
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
if (list.size()>0){
dataService.insertBatch(list);
list.clear();
}
//这里也可以处理报错的行 把报错行的信息返回
}
}
8、导出模板的样式校验
8.1 DataImportHandler.java
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.stereotype.Component;
import java.util.Map;
@Component
public class DataImportHandler implements SheetWriteHandler {
// 这里的map对应Controller中的insertMap插入的下拉框自定义数据集合
private Map<Integer, String[]> map = null;
public DataImportHandler(Map<Integer, String[]> map) {
this.map = map;
}
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
// 这里可以对cell进行任何操作
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
// k 为存在下拉数据集的单元格下表 v为下拉数据集
map.forEach((k, v) -> {
// 下拉列表约束数据
DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
// 设置下拉单元格的首行 末行 首列 末列
CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k, k);
// 设置约束
DataValidation validation = helper.createValidation(constraint, rangeList);
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示", "请选择下拉框内的数据");
sheet.addValidationData(validation);
});
//设置验证生效的范围(excel起始行,结束行,起始列,结束列) 3-4都是时间格式的列
CellRangeAddressList addressList = new CellRangeAddressList(1, 65536, 3, 4);
//设置验证方式(Date(1990, 1, 1)是excel的日期函数,能成功解析,写成"1990-01-01"解析失败)
//需要其他日期格式,修改第四个参数"yyyy-MM-dd",eg:"yyyy-MM-dd HH:mm:ss"
DataValidationConstraint constraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "Date(1990, 1, 1)", "Date(9999, 12, 31)", "yyyy-MM-dd");
//创建验证对象
DataValidation dataValidation = helper.createValidation(constraint, addressList);
//错误提示信息
dataValidation.createErrorBox("提示", "请输入[yyyy-MM-dd]格式日期,范围:[1990-01-01,9999-12-31]");
dataValidation.setShowErrorBox(true);
//验证和工作簿绑定
sheet.addValidationData(dataValidation);
}
}
9、验证
10、其它校验
如果需要添加其它校验 类型下面的操作
//创建验证对象
DataValidation dataValidation = helper.createValidation(constraint, addressList);
//doto
//验证和工作簿绑定
sheet.addValidationData(dataValidation);
package org.apache.poi.ss.usermodel;
import org.apache.poi.ss.util.CellRangeAddressList;
/**
* @author <a href="rjankiraman@emptoris.com">Radhakrishnan J</a>
*
*/
public interface DataValidationHelper {
// 创建公式列表约束
DataValidationConstraint createFormulaListConstraint(String listFormula);
// 创建显式列表约束
DataValidationConstraint createExplicitListConstraint(String[] listOfValues);
// 创建数字约束
DataValidationConstraint createNumericConstraint(int validationType,int operatorType, String formula1, String formula2);
// 创建文本长度约束
DataValidationConstraint createTextLengthConstraint(int operatorType, String formula1, String formula2);
// 创建小数约束
DataValidationConstraint createDecimalConstraint(int operatorType, String formula1, String formula2);
// 创建整数约束
DataValidationConstraint createIntegerConstraint(int operatorType, String formula1, String formula2);
// 创建日期约束
DataValidationConstraint createDateConstraint(int operatorType, String formula1, String formula2,String dateFormat);
// 创建时间约束
DataValidationConstraint createTimeConstraint(int operatorType, String formula1, String formula2);
// 创建自定义约束
DataValidationConstraint createCustomConstraint(String formula);
// 创建验证
DataValidation createValidation(DataValidationConstraint constraint,CellRangeAddressList cellRangeAddressList);
}
参考:https://blog.csdn.net/qq_30072161/article/details/120973947?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522164187001416780261961347%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fall.%2522%257D&request_id=164187001416780261961347&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~first_rank_ecpm_v1~times_rank-2-120973947.first_rank_v2_pc_rank_v29&utm_term=easyexcel%E5%AF%BC%E5%85%A5%E6%97%B6%E9%97%B4%E6%A0%BC%E5%BC%8F&spm=1018.2226.3001.4187
感谢大佬的文章