这里使用的是 springboot 项目
导入 easypoi 的 starter
<dependency>
<groupId>cn.afterturn</groupId>
<artifactId>easypoi-spring-boot-starter</artifactId>
<version>4.0.0</version>
</dependency>
// 用于校验 excel 字段
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-validation</artifactId>
</dependency>
ExcelPoiUtil 工具类
/**
* @author sunhw
* @date 2021/5/29
*/
@Slf4j
public class EasyPoiUtil {
/**
* 构建 sheet
*
* @param sheetName sheet 名字
* @param clazz clazz
* @param list 列表
* @return {@link Map<String, Object>}
*/
public static Map<String, Object> buildSheet(String sheetName, Class<?> clazz, List<?> list) {
ExportParams exportParams = new ExportParams();
exportParams.setType(ExcelType.XSSF);
exportParams.setSheetName(sheetName);
// 导出样式
exportParams.setStyle(EasyPoiExcelStyleUtil.class);
Map<String, Object> map = new HashMap<>(4);
// title的参数为ExportParams类型
map.put("title", exportParams);
// 模版导出对应得实体类型
map.put("entity", clazz);
// sheet中要填充得数据
map.put("data", list);
return map;
}
/**
* 下载 excel
*
* 前端如果用xlsx格式接收表格 后台用XSSFWorkbook workbook = new XSSFWorkbook();创建工作薄
* response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
* response.addHeader("Content-Disposition", "attachment;filename=fileName" + ".xlsx");
*
* 前端如果用用xls格式接收表格 后台 用HSSFWorkbook workbook = new HSSFWorkbook();创建工作薄
* response.setContentType("application/vnd.ms-excel");
* response.addHeader("Content-Disposition", "attachment;filename=fileName"+".xls");
*
* @param fileName 文件名称
* @param response 响应
* @param workbook 工作簿
*/
public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
try {
response.setCharacterEncoding("UTF-8");
response.setHeader("Access-Control-Expose-Headers",
"Content-Disposition");
response.setHeader("content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename="
+ URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
workbook.write(response.getOutputStream());
} catch (IOException e) {
log.info("文件下载失败", e);
throw new RuntimeException(e);
}
}
/**
* 功能描述：根据接收的Excel文件来导入Excel,并封装成实体类
*
* @param file 文件
* @param sheetNum 第几个 sheet
* @param titleRows title 行数
* @param headerRows 标题 行数
* @param pojoClass pojo类
* @return {@link List<T>}
*/
public static <T> List<T> importExcel(MultipartFile file, Integer sheetNum, Integer titleRows, Integer headerRows, Class<T> pojoClass) {
if (Objects.isNull(file)) {
return Collections.emptyList();
}
ImportParams params = new ImportParams();
params.setStartSheetIndex(sheetNum);
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass,
params);
} catch (NoSuchElementException e) {
throw new RuntimeException("excel文件不能为空");
} catch (Exception e) {
throw new RuntimeException(e.getMessage());
}
return list;
}
/**
* 功能描述：根据接收的 Excel 文件来导入 Excel,并封装成 Map
*
* @param file 文件
* @param sheetNum 第几个 sheet
* @param titleRows title 行数
* @param headerRows 标题 行数
* @return {@link List<T>}
*/
public static <T> List<T> parseExcelMap(MultipartFile file, Integer sheetNum, Integer titleRows, Integer headerRows) {
if (Objects.isNull(file)) {
return Collections.emptyList();
}
ImportParams params = new ImportParams();
params.setStartSheetIndex(sheetNum);
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
params.setNeedCheckOrder(true);
List<T> list = null;
try {
list = ExcelImportUtil.importExcel(file.getInputStream(), Map.class,
params);
} catch (NoSuchElementException e) {
throw new RuntimeException("excel文件不能为空");
} catch (Exception e) {
log.info("excel 文件读取失败", e);
throw new RuntimeException("文件读取失败");
}
return list;
}
/**
* 得到 Workbook 对象
*
* @param file
* @return
* @throws IOException
*/
public static Workbook getWorkBook(MultipartFile file) throws IOException {
//这样写 excel 能兼容03和07
InputStream is = file.getInputStream();
Workbook hssfWorkbook = null;
try {
hssfWorkbook = new HSSFWorkbook(is);
} catch (Exception ex) {
is = file.getInputStream();
hssfWorkbook = new XSSFWorkbook(is);
}
return hssfWorkbook;
}
/**
* 检查excel
*
* @param file 文件
* @param sheetNum 第几个 sheet
* @param titleRows title 所占行数
* @param headerRows head 所占行数
* @param clazz clazz
* @return {@link ExcelImportResult<T>}
*/
public static <T> ExcelImportResult<T> checkExcel(MultipartFile file, Integer sheetNum, Integer titleRows, Integer headerRows, Class<T> clazz) {
if (Objects.isNull(file)) {
return new ExcelImportResult<>();
}
ImportParams params = new ImportParams();
params.setStartSheetIndex(sheetNum);
params.setTitleRows(titleRows);
params.setHeadRows(headerRows);
// 开启校验
params.setNeedVerify(true);
ExcelImportResult<T> result = null;
try {
result = ExcelImportUtil.importExcelMore(
file.getInputStream(), clazz, params);
} catch (NoSuchElementException e) {
throw new RuntimeException("excel文件不能为空");
} catch (Exception e) {
log.info("excel 文件读取失败", e);
throw new RuntimeException("文件读取失败");
}
return result;
}
}
测试 VO 实体类
@Data
public class CreditDebt implements Serializable, IExcelDataModel, IExcelModel {
private static final long serialVersionUID = 1L;
@Excel(name = "债券代码")
@NotBlank(message = "债券代码不可为空")
private String bondCode;
/**
* 行号
*/
private int rowNum;
/**
* 错误消息
*/
private String errorMsg;
}
@Data
public class IndustryInfo implements Serializable, IExcelDataModel, IExcelModel {
private static final long serialVersionUID = 1L;
@Excel(name = "行业")
@NotBlank(message = "行业名称不可为空")
private String industry;
@Excel(name = "行业整体评级")
@NotBlank(message = "行业整体评级不可为空")
@Pattern(regexp = "^优秀|良好$")
private String industryGood;
/**
* 行号
*/
private int rowNum;
/**
* 错误消息
*/
private String errorMsg;
}
ExcelController Demo
/**
* @author sunhw
* @date 2021/5/28 使用 ExcelPoi 进行 Excel 的导出
*/
@RestController
@RequestMapping("/excel")
public class ExcelController {
/**
* 多 sheet 导出
*/
@GetMapping("/download")
public void download(HttpServletResponse response) {
Map<String, Object> creditSheet = EasyPoiUtil.buildSheet("信用债",
CreditDebt.class, Collections.EMPTY_LIST);
Map<String, Object> industrySheet = EasyPoiUtil.buildSheet("行业",
IndustryInfo.class, Collections.EMPTY_LIST);
List<Map<String, Object>> sheetsList = new ArrayList<>();
sheetsList.add(creditSheet);
sheetsList.add(industrySheet);
Workbook workBook = ExcelExportUtil.exportExcel(sheetsList, ExcelType.XSSF);
EasyPoiUtil.downLoadExcel("资产类型", response, workBook);
}
/**
* 解析 excel 实体类
*
* @param file 文件
*/
@GetMapping("/parse")
public String parse(@RequestParam("file") MultipartFile file) throws IOException {
String fileType = FileTypeUtil.getFileType(file);
if (!FileTypeUtil.isAllowedTypes(fileType)) {
return "文件格式不支持";
}
List<CreditDebt> creditDebtList = EasyPoiUtil.importExcel(file,
0, 0, 1, CreditDebt.class);
List<IndustryInfo> industryInfoList = EasyPoiUtil.importExcel(file,
1, 0, 1, IndustryInfo.class);
ObjectMapper objectMapper = new ObjectMapper();
return objectMapper.writeValueAsString(creditDebtList);
}
/**
* 解析 excel Map结构
*
* @param file 文件
*/
@GetMapping("/parseMap")
public String parseMap(@RequestParam("file") MultipartFile file) throws IOException {
String fileType = FileTypeUtil.getFileType(file);
if (!FileTypeUtil.isAllowedTypes(fileType)) {
return "文件格式不支持";
}
List<List<Object>> excelInfoList = new ArrayList<>();
Workbook workbook = EasyPoiUtil.getWorkBook(file);
int sheetNum = workbook.getNumberOfSheets();
for (int i = 0; i < sheetNum; i++) {
excelInfoList.add(EasyPoiUtil.parseExcelMap(file, i, 0, 1));
}
ObjectMapper objectMapper = new ObjectMapper();
return objectMapper.writeValueAsString(excelInfoList);
}
/**
* 校验 excel
*
* @param file 文件
* @return {@link String}
*/
@GetMapping("/check")
public String check(@RequestParam("file") MultipartFile file) throws Exception {
String fileType = FileTypeUtil.getFileType(file);
if (!FileTypeUtil.isAllowedTypes(fileType)) {
return "文件格式不支持";
}
ExcelImportResult<CreditDebt> result = EasyPoiUtil.checkExcel(file, 0, 0, 1, CreditDebt.class);
ExcelImportResult<IndustryInfo> result2= EasyPoiUtil.checkExcel(file, 1, 0, 1, IndustryInfo.class);
ObjectMapper objectMapper = new ObjectMapper();
System.out.println("是否校验失败: " + result.isVerfiyFail());
System.out.println("校验失败的集合:" + objectMapper.writeValueAsString(result.getFailList()));
System.out.println("校验通过的集合:" + objectMapper.writeValueAsString(result.getList()));
System.out.println("是否校验失败: " + result2.isVerfiyFail());
System.out.println("校验失败的集合:" + objectMapper.writeValueAsString(result2.getFailList()));
System.out.println("校验通过的集合:" + objectMapper.writeValueAsString(result2.getList()));
for (CreditDebt entity : result.getFailList()) {
String msg = "第" + entity.getRowNum() + "行的错误是：" + entity.getErrorMsg();
System.out.println(msg);
}
for (IndustryInfo entity : result2.getFailList()) {
String msg = "第" + entity.getRowNum() + "行的错误是：" + entity.getErrorMsg();
System.out.println(msg);
}
return "false";
}
}
调整 Excel 样式的工具类
/**
* excel风格
*
* @author sunhw
* @date 2021/05/29
*/
public class EasyPoiExcelStyleUtil 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 = 11;
/**
* 大标题样式
*/
private CellStyle headerStyle;
/**
* 每列标题样式
*/
private CellStyle titleStyle;
/**
* 数据行样式
*/
private CellStyle styles;
public EasyPoiExcelStyleUtil(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.WHITE1.getIndex());
style.setFillPattern(FillPatternType.NO_FILL);
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(false);
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;
}
}
参考链接：