关于EasyExcel 的一些生成模板，导入导出的使用心得
1 根据实体类生成对应的模板
/** * * excel 模板demo 还可以加一些属性的格式化注解 比如时间 * @author： kuangql@fadada.com * @date： 2020/11/25 15:16 * @description： TODO */ @Data public class DemoExcelEntity { public static final String bigTitle= "填写须知： \n" + "1.第1、2行为固定结构，不可更改；以下示例行，导入前请先删除\n" + "2.请严格按照填写规则输入数据，不合规的数据无法成功导入"; @ExcelProperty(value = {bigTitle,"姓名（必填）"}, index = 0) private String userName; @ExcelProperty(value = {bigTitle,"性别（必填）"}, index = 1) private String userSexName; @ExcelProperty(value = {bigTitle,"手机号码（必填）"}, index = 2) private String userMobile; @ExcelProperty(value = {bigTitle,"出生年月（必填）"}, index = 3) private String userBirthday; @ExcelProperty(value = {bigTitle,"工作单位(必填)"}, index = 4) private String deptName; @ExcelProperty(value = {bigTitle,"职务(必填)"}, index = 5) private String unitPosition; @ExcelProperty(value = {bigTitle,"干部类别(必填)"}, index = 6) private String leaderTypeName; @ExcelProperty(value = {bigTitle,"用户状态(必填)"}, index = 7) private String userStatusName; /** * 每个模板的首行高度， 换行数目+2 乘以15 */ public static int getHeadHeight(){ return (StringUtils.getCharCounts(bigTitle,"\n")+2)*15; } }
2 根据该实体类生成的excel模板样式如下:
3 解释下@ExcelProperty这个注解
3.1 在实体属性上增加这个注解。表示这个属性是会输出到excel单元格里，怎么输出呢，就是由 value 和index 来控制。
3.2 value 存放的值是一个数组。数组元素几个，表示占用几行作为表头。index表示该实体类的属性在单元格的那一列。
3.3 结合我的demo看，我的value 存放的元素大小为2，所以我的表头为2行。如果需要出现占据多行的样式，就在对应的元素位置放相同的内容，就会形成合并单元格。
4
excelUtil 工具类：
/**
* 生成excel模板
*
* @param response
* @param fileName 下载的文件名，
* @param sheetName sheet名
* @param data 导出的数据
* @param model 导出的头
* @param heardHeight 头行高
*/
public static void createTemplate(HttpServletResponse response, String fileName,
String sheetName, List<? extends Object> data,
Class<?> model, int heardHeight) {
HorizontalCellStyleStrategy horizontalCellStyleStrategy = setMyCellStyle();
EasyExcel.write(getOutputStream(fileName, response, ExcelTypeEnum.XLSX), model).
excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
.registerWriteHandler(new TemplateCellWriteHandler(heardHeight))
.registerWriteHandler(horizontalCellStyleStrategy)
.doWrite(data);
}
/**
* 创建我的cell 策略
*
* @return
*/
public static HorizontalCellStyleStrategy setMyCellStyle() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 颜色
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
// 字体
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setWrapped(true);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置内容靠中对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
return horizontalCellStyleStrategy;
}
4.1 controller相关代码：
@ApiOperation(value = "下载模板文件测试-----该方法是测试使用")
@GetMapping("/template2/{type}")
public void template2(@PathVariable("type") String type, HttpServletResponse response) {
String fileName = "导入模板";
String sheetName = "模板";
try {
// List<DemoExcelEntity> sysUserImportExcelList = getSysUserImportExcel();
//输出文件流
//后期根据传参处理获取对应的模板实体类 如果需要导出数据，将nul改为对应List<DemoExcelEntity> 传进去
ExcelUtils.createTemplate(response, fileName, sheetName, null,
DemoExcelEntity.class, DemoExcelEntity.getHeadHeight());
} catch (Exception e) {
e.printStackTrace();
}
}
4.3 读取文件数据：
1 我的文件数据截图：
2 表头占据了2行，所以读取数据的时候需要跳过2行表头。 2行表头等于@ExcelProperty里的value 元素个数
代码如下：
/** * 读取 Excel(第一个 sheet) 指定行开始读取 * @param excel 文件 * @param rowType 模板实体类 * @param header 指定不读取的表头行数， * @param <T> * @return 集合数据 * @throws ExcelException */ public static <T> List<T> readFirstSheetExcel(MultipartFile excel, Class<T> rowType,int header) throws ExcelException { ExcelReader reader = getReader(excel, header); if (reader == null) { return new ArrayList<>(); } return readExcel(reader, rowType, 0); }
3 如果在导入数据的时候，需要将导入的数据的错误信息追加到后一列，可以新建一个实体类
/** * 用于输出 导入的时候，错误信息输出 * @author： kuangql@fadada.com * @date： 2020/11/27 13:37 * @description： TODO */ @Data public class DemoExcelEntityError extends DemoExcelEntity { /** * 说明： 该对象只针对导入的时候，在用户上传的文件后面追加一列错误信息描述 */ @ExcelProperty(value = {bigTitle,"错误描述"}, index = 8) private String errorMessage; }
// 该方法是测试使用 @ApiOperation(value = "上传文件数据------测试使用 返回错误信息 ") @PostMapping("/upload") public void template2(MultipartFile multipartFile, HttpServletResponse response) { List<DemoExcelEntity> demoExcelEntities = ExcelUtils.readFirstSheetExcel(multipartFile, DemoExcelEntity.class, 2); List<DemoExcelEntityError> demoExcelEntityErrorList = new ArrayList<>(); demoExcelEntities.forEach( demoExcelEntity -> { System.out.println(demoExcelEntity.toString()); DemoExcelEntityError demoExcelEntityError = new DemoExcelEntityError(); BeanUtils.copyProperties(demoExcelEntity, demoExcelEntityError); demoExcelEntityError.setErrorMessage("我的错误信息描述"); demoExcelEntityErrorList.add(demoExcelEntityError); } ); ExcelUtils.writeExcel(response, demoExcelEntityErrorList, "demo", "1", ExcelTypeEnum.XLSX); }
效果如下：
好了，基本描述完毕。如下是所有相关的代码：
/**
* excel导入导出工具类
* BeanCopy ExcelException 属于自定义数据，属于可自定义依赖
*
* @version 2.0.0
* @author:
* @date: 2020/10/27
*/
@NoArgsConstructor
public class ExcelUtils {
/**
* 读取 Excel(多个 sheet)
* @param reader 读取的excel
* @param rowModel excel模板实体类
* @param sheetCount sheet
* @param <T>
* @return
*/
public static <T> List<T> readExcel(ExcelReader reader, Class<T> rowModel, int sheetCount) {
if (reader == null) {
return new ArrayList<>();
}
List<ReadSheet> readSheetList = new ArrayList<>();
ExcelListener<T> excelListener = new ExcelListener<>();
ReadSheet readSheet = EasyExcel.readSheet(sheetCount)
.head(rowModel)
.registerReadListener(excelListener)
.build();
readSheetList.add(readSheet);
reader.read(readSheetList);
return getExtendsBeanList(excelListener.getDataList(), rowModel);
}
/**
* 读取 Excel(多个 sheet)
* 将多sheet合并成一个list数据集，通过自定义ExcelReader继承AnalysisEventListener
* 重写invoke doAfterAllAnalysed方法
* getExtendsBeanList 主要是做Bean的属性拷贝 ，可以通过ExcelReader中添加的数据集直接获取
*
* @param excel 文件
* @param header 不读取表头数目（表头2行，读取数据时候从第三行开始，读取几行看对应的模板实体类）
* @param sheetNo
* @param rowModel excel模板实体类
* @return
* @throws ExcelException
*/
private static List[] readExcel(MultipartFile excel,int header, Integer sheetNo, Class<?>[] rowModel) throws ExcelException {
ExcelReader reader = getReader(excel,header);
if (reader == null) {
return new ArrayList[rowModel.length];
}
List[] result = new ArrayList[rowModel.length];
for (int sheetCount = 0; sheetCount < rowModel.length; sheetCount++) {
if (sheetNo != null && sheetNo != sheetCount) {
continue;
}
result[sheetCount].addAll(readExcel(reader, rowModel[sheetCount], sheetCount));
}
return result;
}
/**
* 读取 Excel(多个 sheet)
* 将多sheet合并成一个list数据集，通过自定义ExcelReader继承AnalysisEventListener
* 重写invoke doAfterAllAnalysed方法
* getExtendsBeanList 主要是做Bean的属性拷贝 ，可以通过ExcelReader中添加的数据集直接获取
* @param excel
* @param header 不读取表头数目（表头2行，读取数据时候从第三行开始，读取几行看对应的模板实体类）
* @param rowModel
* @return
* @throws ExcelException
*/
public static List[] readExcel(MultipartFile excel, int header ,Class<?>... rowModel) throws ExcelException {
ExcelReader reader = getReader(excel,header);
if (reader == null) {
return new ArrayList[rowModel.length];
}
List[] result = new ArrayList[rowModel.length];
for (int sheetCount = 0; sheetCount < rowModel.length; sheetCount++) {
result[sheetCount] = new ArrayList<>(readExcel(reader, rowModel[sheetCount], sheetCount));
}
return result;
}
/**
* 读取 Excel(单个 sheet)
* 将多sheet合并成一个list数据集，通过自定义ExcelReader继承AnalysisEventListener
* 重写invoke doAfterAllAnalysed方法
* getExtendsBeanList 主要是做Bean的属性拷贝 ，可以通过ExcelReader中添加的数据集直接获取
*/
/*
public static <T> List<T> readFirstSheetExcel(MultipartFile excel, Class<T> rowType) throws ExcelException {
ExcelReader reader = getReader(excel);
if (reader == null) {
return new ArrayList<>();
}
return readExcel(reader, rowType, 0);
}
*/
/**
* 读取某个 sheet 的 Excel
*
* @param excel 文件
* @param rowModel 实体类映射
* @param sheetNo sheet 的序号 从1开始
* @return Excel 数据 list
*/
public static <T> List readExcel(MultipartFile excel, Class<T> rowModel, int sheetNo) throws ExcelException {
Class[] classes = {rowModel};
return ExcelUtils.readExcel(excel, sheetNo, classes)[0];
}
/**
* 导出 Excel ：一个 sheet，带表头
* 自定义WriterHandler 可以定制行列数据进行灵活化操作
*
* @param response HttpServletResponse
* @param list 数据 list
* @param fileName 导出的文件名
* @param sheetName 导入文件的 sheet 名
*/
public static <T> void writeExcel(HttpServletResponse response, List<T> list,
String fileName, String sheetName, ExcelTypeEnum excelTypeEnum) throws ExcelException {
if (sheetName == null || "".equals(sheetName)) {
sheetName = "sheet1";
}
if (CollectionUtils.isEmpty(list)) {
return;
}
EasyExcel.write(getOutputStream(fileName, response, excelTypeEnum), list.get(0).getClass()).sheet(sheetName).doWrite(list);
}
/**
* 导出 Excel ：一个 sheet，带表头
* 自定义WriterHandler 可以定制行列数据进行灵活化操作
*
* @param response HttpServletResponse
* @param list 数据 list
* @param fileName 导出的文件名
*/
public static <T> void writeExcel(HttpServletResponse response, List<T> list,
String fileName, ExcelTypeEnum excelTypeEnum) throws ExcelException {
if (CollectionUtils.isEmpty(list)) {
return;
}
String sheetName = list.get(0).getClass().getAnnotation(SheetName.class).value();
sheetName = StrUtil.isNotBlank(sheetName) ? sheetName : "sheet1";
EasyExcel.write(getOutputStream(fileName, response, excelTypeEnum), list.get(0).getClass()).sheet(sheetName).doWrite(list);
}
/**
* 导出 Excel ：一个 sheet，带表头
* 自定义WriterHandler 可以定制行列数据进行灵活化操作
*
* @param response HttpServletResponse
* @param fileName 导出的文件名
*/
public static void writeExcel(HttpServletResponse response, String fileName,
ExcelTypeEnum excelTypeEnum, List... lists) throws ExcelException {
ExcelWriter excelWriter = null;
try {
excelWriter = EasyExcel.write(getOutputStream(fileName, response, excelTypeEnum)).build();
for (int count = 0; count < lists.length; count++) {
if (CollectionUtils.isEmpty(lists[count])) {
continue;
}
String sheetName = lists[count].get(0).getClass().getAnnotation(SheetName.class).value();
sheetName = StrUtil.isNotBlank(sheetName) ? sheetName : "sheet" + (count + 1);
WriteSheet writeSheet = EasyExcel.writerSheet(count, sheetName)
.head(lists[count].get(0).getClass())
.build();
excelWriter.write(lists[count], writeSheet);
}
} finally {
if (excelWriter != null) {
excelWriter.finish();
}
}
}
/**
* 导出文件时为Writer生成OutputStream
*/
private static OutputStream getOutputStream(String fileName, HttpServletResponse response, ExcelTypeEnum excelTypeEnum) throws ExcelException {
//创建本地文件
String filePath = fileName + excelTypeEnum.getValue();
try {
fileName = new String(filePath.getBytes(), StandardCharsets.ISO_8859_1);
response.setCharacterEncoding(StandardCharsets.UTF_8.name());
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition", "filename=" + fileName);
return response.getOutputStream();
} catch (IOException e) {
throw new ExcelException(ResultCode.File.CREATE_FILE_FAILED);
}
}
/**
* 返回 ExcelReader
*
* @param excel 需要解析的 Excel 文件
*/
/*
public static ExcelReader getReader(MultipartFile excel) throws ExcelException {
String fileName = excel.getOriginalFilename();
if (fileName == null) {
throw new ExcelException(ResultCode.File.FILE_FORMAT_ERROR);
}
if (!fileName.toLowerCase().endsWith(ExcelTypeEnum.XLS.getValue()) && !fileName.toLowerCase().endsWith(ExcelTypeEnum.XLSX.getValue())) {
throw new ExcelException(ResultCode.File.FILE_FORMAT_ERROR);
}
InputStream inputStream;
try {
inputStream = excel.getInputStream();
return EasyExcel.read(inputStream).build();
} catch (IOException e) {
//do something
}
return null;
}
*/
/**
* 利用BeanCopy转换list
*/
public static <T> List<T> getExtendsBeanList(List<?> list, Class<T> typeClazz) {
return BeanConvertUtils.copyList(list, typeClazz);
}
/**
* 生成excel模板
*
* @param response
* @param fileName 下载的文件名，
* @param sheetName sheet名
* @param data 导出的数据
* @param model 导出的头
* @param heardHeight 头行高
*/
public static void createTemplate(HttpServletResponse response, String fileName,
String sheetName, List<? extends Object> data,
Class<?> model, int heardHeight) {
HorizontalCellStyleStrategy horizontalCellStyleStrategy = setMyCellStyle();
EasyExcel.write(getOutputStream(fileName, response, ExcelTypeEnum.XLSX), model).
excelType(ExcelTypeEnum.XLSX).sheet(sheetName)
.registerWriteHandler(new TemplateCellWriteHandler(heardHeight))
.registerWriteHandler(horizontalCellStyleStrategy)
.doWrite(data);
}
/**
* 创建我的cell 策略
*
* @return
*/
public static HorizontalCellStyleStrategy setMyCellStyle() {
// 头的策略
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
// 设置表头居中对齐
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 颜色
headWriteCellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
WriteFont headWriteFont = new WriteFont();
headWriteFont.setFontHeightInPoints((short) 10);
// 字体
headWriteCellStyle.setWriteFont(headWriteFont);
headWriteCellStyle.setWrapped(true);
// 内容的策略
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
// 设置内容靠中对齐
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
return horizontalCellStyleStrategy;
}
/**
* 读取 Excel(第一个 sheet) 指定行开始读取
* @param excel 文件
* @param rowType 模板实体类
* @param header 指定不读取的表头行数，
* @param <T>
* @return 集合数据
* @throws ExcelException
*/
public static <T> List<T> readFirstSheetExcel(MultipartFile excel, Class<T> rowType,int header) throws ExcelException {
ExcelReader reader = getReader(excel, header);
if (reader == null) {
return new ArrayList<>();
}
return readExcel(reader, rowType, 0);
}
/**
*
* @param excel 需要解析的 Excel 文件
* @param header 指定不读取表头行数，
* @return
* @throws ExcelException
*/
public static ExcelReader getReader(MultipartFile excel,int header) throws ExcelException {
String fileName = excel.getOriginalFilename();
if (fileName == null) {
throw new ExcelException(ResultCode.File.FILE_FORMAT_ERROR);
}
if (!fileName.toLowerCase().endsWith(ExcelTypeEnum.XLS.getValue()) && !fileName.toLowerCase().endsWith(ExcelTypeEnum.XLSX.getValue())) {
throw new ExcelException(ResultCode.File.FILE_FORMAT_ERROR);
}
InputStream inputStream;
try {
inputStream = excel.getInputStream();
return EasyExcel.read(inputStream).
headRowNumber(header).
build();
} catch (IOException e) {
//do something
}
return null;
}
}
2 实体类：
/**
*
* excel 模板demo 还可以加一些属性的格式化注解 比如时间
* @author： kuangql@fadada.com
* @date： 2020/11/25 15:16
* @description： TODO
*/
@Data
public class DemoExcelEntity {
public static final String bigTitle= "填写须知： \n" +
"1.第1、2行为固定结构，不可更改；以下示例行，导入前请先删除\n" +
"2.请严格按照填写规则输入数据，不合规的数据无法成功导入";
@ExcelProperty(value = {bigTitle,"姓名（必填）"}, index = 0)
private String userName;
@ExcelProperty(value = {bigTitle,"性别（必填）"}, index = 1)
private String userSexName;
@ExcelProperty(value = {bigTitle,"手机号码（必填）"}, index = 2)
private String userMobile;
@ExcelProperty(value = {bigTitle,"出生年月（必填）"}, index = 3)
private String userBirthday;
@ExcelProperty(value = {bigTitle,"工作单位(必填)"}, index = 4)
private String deptName;
@ExcelProperty(value = {bigTitle,"职务(必填)"}, index = 5)
private String unitPosition;
@ExcelProperty(value = {bigTitle,"干部类别(必填)"}, index = 6)
private String leaderTypeName;
@ExcelProperty(value = {bigTitle,"用户状态(必填)"}, index = 7)
private String userStatusName;
/**
* 每个模板的首行高度， 换行数目+2 乘以15
*/
public static int getHeadHeight(){
return (StringUtils.getCharCounts(bigTitle,"\n")+2)*15;
}
}
/** * 用于输出 导入的时候，错误信息输出 * @author： kuangql@fadada.com * @date： 2020/11/27 13:37 * @description： TODO */ @Data public class DemoExcelEntityError extends DemoExcelEntity { /** * 说明： 该对象只针对导入的时候，在用户上传的文件后面追加一列错误信息描述 */ @ExcelProperty(value = {bigTitle,"错误描述"}, index = 8) private String errorMessage; }
/**
* excel通用单元格格式类
* @description:
* @author:
* @time: 2020/9/20 9:54
*/
public class TemplateCellWriteHandler implements CellWriteHandler {
/**
* 模板的首行行高 ，通过构造器注入
*/
private int height;
public TemplateCellWriteHandler(int height) {
this.height = height;
}
@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) {
Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
if (cell.getRowIndex() == 0) {
font.setFontHeightInPoints((short) 10);
font.setFontName("宋体");
//加粗
font.setBold(true);
cellStyle.setFont(font);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置 自动换行
cellStyle.setWrapText(true);
Row row = cell.getRow();
row.setHeightInPoints(height);
} if (cell.getRowIndex() == 1) {
font.setFontHeightInPoints((short) 11);
font.setFontName("宋体");
font.setBold(true);
cellStyle.setFont(font);
cellStyle.setAlignment(HorizontalAlignment.LEFT);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
//设置 自动换行
cellStyle.setWrapText(true);
Row row = cell.getRow();
row.setHeightInPoints(26);
}
cell.setCellStyle(cellStyle);
}
}
