Excel是一个Java开发中必须会用到的东西，之前博主也发过一篇关于使用Excel的文章，但是最近工作中，发现了一个更好的使用方法，所以，就对之前的博客进行总结，然后就有了这篇新的，万能通用的方法说明书了
好了，闲话不多说，直接上干货了
控制器代码如下
@Operation(summary = "Excel导入", description = "Excel导入")
@GetMapping("/commPointManagement/importExcel")
public ResultBody importExcel(@RequestParam MultipartFile excel,
@RequestParam(value = "creatorId") Long creatorId,
HttpServletResponse response){
try {
return commPointManagementService.importExcel(excel,creatorId,response);
} catch (Exception e) {
return ResultBody.failed("导入失败"+e.getMessage());
}
}
/**
* excel导出
* @param dto
*/
@Operation(summary = "Excel导出", description = "Excel导出")
@PostMapping("/commPointManagement/downloadInfoExcel")
public void downloadInfoExcel(@RequestBody CommPointManagementDTO dto, HttpServletResponse response){
commPointManagementService.downloadInfoExcel(dto,response);
}
/**
* 下载模板
*/
@GetMapping("/commPointManagement/downloadExcelTemplate")
@Operation(summary = "下载模板excel模板", description = "下载模板excel模板")
public void exportExcelFormat(HttpServletResponse response) {
commPointManagementService.exportExcelFormat(response);
}
上面总结了三个方法，都是不使用模板的，代码也有注释，大家可根据注释获取自己需要的方法
service接口就不粘贴了，直接上实现类了
public void exportExcelFormat(HttpServletResponse response) {
List<List<String>> customTitle = new ArrayList<>();
List<String> titles = Lists.newArrayList();
titles.add("字段1");
titles.add("字段2");
titles.add("字段3");
customTitle.add(titles);
try {
EasyPoiUtil.exportExcel("模板名称.xlsx", null, null, Lists.newArrayList(), CommPointManagementVO.class, customTitle, Lists.newArrayList(), response);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
@Override
public ResultBody importExcel(MultipartFile excel,Long creatorId, HttpServletResponse response) throws Exception {
//获取Excel中的数据集合
ExcelImportResult<CommPointManagementBean> importExcel = EasyPoiUtil.importExcel(excel, 1, 0, 0, false, CommPointManagementBean.class);
List<CommPointManagementBean> importList = importExcel.getList();
int failCount = 0;
int insertCount = 0;
if (CollUtil.isEmpty(importList)) {
return ResultBody.ok().msg("导入excel成功");
}
Timestamp time = new Timestamp(new Date().getTime());
for (int i = 0; i < importList.size(); i++) {
CommPointManagementBean date = importList.get(i);
}
return ResultBody.ok().data().msg("导入excel成功");
}
@Override
public void downloadInfoExcel(CommPointManagementDTO dto, HttpServletResponse response) {
//获取数据库数据
IPage<CommPointManagementVO> page = commPointManagementMapper.findPage(new PageParams(dto.getPage(), dto.getLimit()), dto);
List<CommPointManagementVO> records = page.getRecords();
List<List<String>> customTitle = new ArrayList<>();
List<String> titleList = new ArrayList<>();
titleList.add("字段1");
titleList.add("字段2");
titleList.add("字段3");
customTitle.add(titleList);
try {
EasyPoiUtil.exportExcel("表名.xlsx", "表头", "Excel中的页名", records,
CommPointManagementVO.class, customTitle, new ArrayList<>(), response);
} catch (Exception e) {
throw new RuntimeException(e);
}
}
上面方法中的CommPointManagementVO.class是接口返回的实体类，虽然没有注解，但是细心一点看还是能看出来的，里面还有一个工具类EasyPoiUtil，这个就是咱们今天的主角了，主角介绍如下：
package com.secondcloud.system.server.util;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.annotation.ExcelEntity;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.export.ExcelExportService;
import cn.afterturn.easypoi.util.PoiPublicUtil;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.convert.Convert;
import cn.hutool.core.util.StrUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* easyPoi excel通用导入导出
*/
public class EasyPoiUtil {
private static final String SUFFIX = ".xlsx";
/**
* exportExcel 通用导出方法(bean加注解)
*
* @param fileName 文件名
* @param title 标题
* @param sheetName 表单名
* @param list 数据
* @param pojoClass 数据类
* @param customTitle 自定义表头
* @param mergeList 自定义合并
* @param response 响应
* @throws Exception
*/
public static void exportExcel(String fileName, String title, String sheetName, List<?> list, Class<?> pojoClass,
List<List<String>> customTitle, List<CellRangeAddress> mergeList, HttpServletResponse response)
throws Exception {
defaultExport(list, pojoClass, fileName, response, simpleParams(title, sheetName, fileName), customTitle,
mergeList, null);
}
/**
* exportExcelEntity 通用导出方法(自定义ExcelExportEntity)
*
* @param fileName 文件名
* @param title 标题
* @param sheetName 表单名
* @param list 数据
* @param entityList 数据配置
* @param customTitle 自定义表头
* @param mergeList 自定义合并
* @param response 响应
* @throws Exception
*/
public static void exportExcelEntity(String fileName, String title, String sheetName, List<?> list, List<ExcelExportEntity> entityList,
List<List<String>> customTitle, List<CellRangeAddress> mergeList, HttpServletResponse response)
throws Exception {
defaultExport(list, null, fileName, response, simpleParams(title, sheetName, fileName), customTitle,
mergeList, entityList);
}
/**
* exportExcel 多表单导出方法(bean加注解)
*
* @param fileName 文件名
* @param configList title:标题 data:数据 entity:数据类
* @param response 响应
* @throws Exception
*/
public static void exportExcelMultiSheet(String fileName, List<Map<String, Object>> configList,
HttpServletResponse response) throws Exception {
// 根据fileName后缀确定excel文件是xls还是xlsx
ExcelType type = ExcelType.HSSF;
if (StrUtil.endWith(fileName, SUFFIX)) {
type = ExcelType.XSSF;
}
Workbook workbook = ExcelExportUtil.exportExcel(configList, type);
if (workbook != null) {
try {
if (CollectionUtil.isNotEmpty(configList)) {
int sheetIndex = 0;
for (Map<String, Object> map : configList) {
List<List<String>> customTitle = (List<List<String>>) map.get("customTitle");
List<CellRangeAddress> mergeList = (List<CellRangeAddress>) map.get("mergeList");
String sheetTitle = Convert.toStr(map.get("sheetTitle"));
Sheet sheet = workbook.getSheetAt(sheetIndex);
if (sheet != null) {
// 自定义合并
boolean mergeFlag = CollectionUtil.isNotEmpty(mergeList);
if (mergeFlag) {
for (CellRangeAddress merge : mergeList) {
sheet.addMergedRegion(merge);
}
}
// 合并多列标题(暂时支持第一行向左合并 第二行向上合并)
int begin = 0;
int end = 0;
String tmpTitle = null;
// 已合并区域标识 i&j
List<String> mergedList = new ArrayList<>();
int i = 0;
if (sheetTitle != null) {
i++;
}
int line = i;
CellStyle cellStyle = null;
for (List<String> strings : customTitle) {
Row row = sheet.getRow(i);
if (row == null) {
row = sheet.createRow(i);
}
int j = 0;
for (String s : strings) {
Cell cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
} else if (cellStyle == null) {
cellStyle = cell.getCellStyle();
}
cell.setCellValue(s);
if (!mergeFlag) {
// 第一行合并规则
if (i == line) {
if (StrUtil.isEmpty(s)) {
end = j;
}
if (tmpTitle != null && StrUtil.isNotEmpty(s) && !tmpTitle.equals(s)) {
if (end > begin) {
sheet.addMergedRegion(new CellRangeAddress(i, i, begin, end));
for (int f = begin; f < end; f++) {
mergedList.add(i + "&" + f);
}
}
begin = j;
tmpTitle = s;
}
if (tmpTitle == null) {
begin = j;
tmpTitle = s;
}
} else if (i == line + 1) {
// 第二行向上合并
if (StrUtil.isEmpty(s) && !mergedList.contains((i - 1) + "&" + j)) {
sheet.addMergedRegion(new CellRangeAddress(i - 1, i, j, j));
mergedList.add((i - 1) + "&" + j);
mergedList.add(i + "&" + j);
}
}
}
j++;
}
if (!mergeFlag) {
// 第一行合并规则(换行前)
if (i == line) {
if (strings.size() > 0 && end > begin) {
sheet.addMergedRegion(new CellRangeAddress(i, i, begin, end));
for (int f = begin; f < end; f++) {
mergedList.add(i + "&" + f);
}
}
}
}
i++;
}
}
sheetIndex++;
}
}
} catch (Exception e) {
}
downLoadExcel(fileName, response, workbook);
}
}
/**
* importExcel 通用导入方法(bean加注解)
*
* @param file 导入文件
* @param titleRows 标题行数
* @param headerRows 头部行数
* @param keyIndex 主键列 如果这个cell没有值,就跳过，默认0
* @param needVerfiy 是否需要校验，默认false
* @param pojoClass 数据类
* @return
* @throws Exception
*/
public static <T> ExcelImportResult<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows,
Integer keyIndex, Boolean needVerfiy, Class<T> pojoClass) throws Exception {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setKeyIndex(keyIndex);
params.setNeedVerify(needVerfiy);
ExcelImportResult<T> rst = null;
try {
rst = ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
} catch (Exception e) {
e.printStackTrace();
throw e;
}
return rst;
}
/**
* importExcel 指定表单导入方法(bean加注解)
*
* @param file 导入文件
* @param titleRows 标题行数
* @param headerRows 头部行数
* @param startSheetIndex 表单序号
* @param sheetNum 表单数量
* @param keyIndex 主键列 如果这个cell没有值,就跳过\
* @param needVerfiy 是否需要校验，默认false
* @param pojoClass 数据类
* @return
* @throws Exception
*/
public static <T> ExcelImportResult<T> importExcelCommon(MultipartFile file, Integer titleRows, Integer headerRows,
Integer startSheetIndex, Integer sheetNum, Integer keyIndex, Boolean needVerfiy, Class<T> pojoClass)
throws Exception {
if (file == null) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setStartSheetIndex(startSheetIndex);
params.setSheetNum(sheetNum);
params.setKeyIndex(keyIndex);
params.setNeedVerify(needVerfiy);
ExcelImportResult<T> rst = null;
try {
rst = ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);
} catch (Exception e) {
e.printStackTrace();
throw e;
}
return rst;
}
/**
* importExcel 根据文件路径导入excel(bean加注解)
*
* @param filePath
* @param titleRows
* @param headerRows
* @param pojoClass
* @param <T>
* @return
* @throws Exception
*/
public static <T> ExcelImportResult<T> importExcel(String filePath, Integer titleRows, Integer headerRows,
Class<T> pojoClass) throws Exception {
if (StrUtil.isEmpty(filePath)) {
return null;
}
ImportParams params = new ImportParams();
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
ExcelImportResult<T> rst = null;
try {
rst = ExcelImportUtil.importExcelMore(new File(filePath), pojoClass, params);
} catch (Exception e) {
e.printStackTrace();
throw e;
}
return rst;
}
private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response,
ExportParams exportParams, List<List<String>> customTitle, List<CellRangeAddress> mergeList,
List<ExcelExportEntity> entityList) throws Exception {
exportParams.setFixedTitle(false);
Workbook workbook;
int titleLine = 0;
if (CollectionUtil.isNotEmpty(customTitle)) {
titleLine = customTitle.size();
}
// 自定义标题需要在标题下插入行
List<Object> t = new ArrayList<>();
while (titleLine > 1 && CollectionUtil.isNotEmpty(list)) {
t.add(pojoClass.getDeclaredConstructor().newInstance());
titleLine--;
}
t.addAll(list);
if (pojoClass == null) {
workbook = ExcelExportUtil.exportExcel(exportParams, entityList, t);
} else {
workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, t);
}
if (workbook != null) {
// 自定义标题
if (CollectionUtil.isNotEmpty(customTitle)) {
Sheet sheet = workbook.getSheetAt(0);
if (sheet != null) {
// 自定义合并
boolean mergeFlag = CollectionUtil.isNotEmpty(mergeList);
if (mergeFlag) {
for (CellRangeAddress merge : mergeList) {
sheet.addMergedRegion(merge);
}
}
// 合并多列标题(暂时支持第一行向左合并 第二行向上合并)
int begin = 0;
int end = 0;
String tmpTitle = null;
// 已合并区域标识 i&j
List<String> mergedList = new ArrayList<>();
int i = 0;
if (exportParams.getTitle() != null) {
i++;
}
if (exportParams.getSecondTitle() != null) {
i++;
}
int line = i;
CellStyle cellStyle = null;
for (List<String> strings : customTitle) {
Row row = sheet.getRow(i);
if (row == null) {
row = sheet.createRow(i);
}
int j = 0;
for (String s : strings) {
Cell cell = row.getCell(j);
if (cell == null) {
cell = row.createCell(j);
cell.setCellStyle(cellStyle);
} else if (cellStyle == null) {
cellStyle = cell.getCellStyle();
}
cell.setCellValue(s);
if (!mergeFlag) {
// 第一行合并规则
if (i == line) {
if (StrUtil.isEmpty(s)) {
end = j;
}
if (tmpTitle != null && StrUtil.isNotEmpty(s) && !tmpTitle.equals(s)) {
if (end > begin) {
sheet.addMergedRegion(new CellRangeAddress(i, i, begin, end));
for (int f = begin; f < end; f++) {
mergedList.add(i + "&" + f);
}
}
begin = j;
tmpTitle = s;
}
if (tmpTitle == null) {
begin = j;
tmpTitle = s;
}
} else if (i == line + 1) {
// 第二行向上合并
if (StrUtil.isEmpty(s) && !mergedList.contains((i - 1) + "&" + j)) {
sheet.addMergedRegion(new CellRangeAddress(i - 1, i, j, j));
mergedList.add((i - 1) + "&" + j);
mergedList.add(i + "&" + j);
}
}
}
j++;
}
if (!mergeFlag) {
// 第一行合并规则(换行前)
if (i == line) {
if (strings.size() > 0 && end > begin) {
sheet.addMergedRegion(new CellRangeAddress(i, i, begin, end));
for (int f = begin; f < end; f++) {
mergedList.add(i + "&" + f);
}
}
}
}
i++;
}
}
}
downLoadExcel(fileName, response, workbook);
}
}
private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook)
throws Exception {
OutputStream ouputStream = null;
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
response.setHeader("Pragma", "No-cache");
ouputStream = response.getOutputStream();
workbook.write(ouputStream);
} catch (Exception e) {
e.printStackTrace();
throw e;
} finally {
if (ouputStream != null) {
try {
ouputStream.flush();
ouputStream.close();
} catch (Exception e) {
}
}
}
}
/**
* 获取表单配置项
*
* @param title
* @param sheetName
* @param fileName
* @return
*/
public static ExportParams simpleParams(String title, String sheetName, String fileName) {
// 根据fileName后缀确定excel文件是xls还是xlsx
ExcelType type = ExcelType.HSSF;
if (StrUtil.endWith(fileName, SUFFIX)) {
type = ExcelType.XSSF;
}
ExportParams exportParams = new ExportParams(title, sheetName, type);
exportParams.setFixedTitle(false);
return exportParams;
}
/**
* 获取表单配置对象
*
* @param title
* @param sheetName
* @param fileName
* @param pojoClass
* @param list
* @param customTitle 自定义表头
* @param mergeList 自定义合并
* @return
*/
public static Map<String, Object> simpleParamsMap(String title, String sheetName, String fileName, List<?> list,
Class<?> pojoClass, List<List<String>> customTitle, List<CellRangeAddress> mergeList) throws Exception {
Map<String, Object> map = new HashMap<String, Object>();
map.put("title", simpleParams(title, sheetName, fileName));
map.put("entity", pojoClass);
List<Object> array = new ArrayList<>();
if (CollectionUtil.isNotEmpty(customTitle)) {
int size = customTitle.size();
while (size > 1) {
array.add(pojoClass.getDeclaredConstructor().newInstance());
size--;
}
}
if (list != null) {
array.addAll(list);
}
map.put("data", array);
map.put("customTitle", customTitle);
map.put("mergeList", mergeList);
map.put("sheetTitle", title);
return map;
}
/**
* 生成ExcelExportEntity
*
* @param name 名称
* @param value 属性
* @param merge 是否合并列
* @param list 下级entity
* @return
*/
public static ExcelExportEntity getExportEntity(String name, String value, Boolean merge, Double width, Integer orderNum,
String format, List<ExcelExportEntity> list) {
ExcelExportEntity entity = new ExcelExportEntity(name, value);
if (merge != null) {
entity.setNeedMerge(merge);
}
if (width != null) {
entity.setWidth(width);
}
if (orderNum != null) {
entity.setOrderNum(orderNum);
}
if (format != null) {
entity.setFormat(format);
}
if (CollectionUtil.isNotEmpty(list)) {
entity.setList(list);
}
return entity;
}
public static List<ExcelExportEntity> classToEntityList(Class<?> pojoClass) {
List<ExcelExportEntity> rst = new ArrayList<>();
try {
Field[] fileds = PoiPublicUtil.getClassFields(pojoClass);
ExcelTarget etarget = (ExcelTarget) pojoClass.getAnnotation(ExcelTarget.class);
String targetId = etarget == null ? null : etarget.value();
new ExcelExportService().getAllExcelField(null, targetId, fileds, rst, pojoClass, (List) null, (ExcelEntity) null);
} catch (Exception e) {
}
return rst;
}
}
这个工具类一般的格式都能使用的，具体复杂的Excel表格样式，还是使用Excel表格模板去做会更方便点，工具类的方发也有说明，大家自行查看哦。
本篇分享就到这里了，如果有使用问题，可以评论留言，有不对的地方，请批评指正，谢谢！
756
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
