实战分享之springboot+easypoi快速业务集成

        1.依赖引入

<!--引入EasyPOI--><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>4.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>4.1.0</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>4.1.0</version></dependency>

        2.封装easypoi工具类

package com.wzz.utils;import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;/*** Excel导入导出工具类* @author pangu*/
public class ExcelUtil {/*** 导出工具类* @param list* @param title* @param sheetName* @param pojoClass* @param fileName* @param isCreateHeader* @param response*/public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, boolean isCreateHeader, HttpServletResponse response){ExportParams exportParams = new ExportParams(title, sheetName);exportParams.setCreateHeadRows(isCreateHeader);defaultExport(list, pojoClass, fileName, response, exportParams);}/*** 导出工具类* @param list* @param title* @param sheetName* @param pojoClass* @param fileName* @param response*/public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,HttpServletResponse response){ExportParams exportParams = new ExportParams(title, sheetName);exportParams.setStyle(ExcelStyleUtil.class);defaultExport(list, pojoClass, fileName, response, exportParams);}public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){defaultExport(list, fileName, response);}private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName,HttpServletResponse response, ExportParams exportParams) {//TODO设置标题颜色Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);if (workbook != null); downLoadExcel(fileName, response, workbook);}private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {try {response.setCharacterEncoding("UTF-8");response.setHeader("content-Type", "application/vnd.ms-excel");response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));workbook.write(response.getOutputStream());} catch (IOException e) {//throw new NormalException(e.getMessage());}}private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);if (workbook != null);downLoadExcel(fileName, response, workbook);}public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){if (StringUtils.isBlank(filePath)){return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);List<T> list = null;try {list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);}catch (NoSuchElementException e){//throw new NormalException("模板不能为空");} catch (Exception e) {e.printStackTrace();//throw new NormalException(e.getMessage());} return list;}public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){if (file == null){ return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);List<T> list = null;try {list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);}catch (NoSuchElementException e){// throw new NormalException("excel文件不能为空");} catch (Exception e) {//throw new NormalException(e.getMessage());System.out.println(e.getMessage());}return list;}}

        3.Controller层直接调用

/*** 导出Excel   excelVo* 注意这里暂时只能用get方法，不能用post*/@GetMapping("/exportExcel")@ApiOperation("根据过滤条件导出excel数据")public void exportExcel(HttpServletResponse response,@RequestParam(name = "pageNo") int pageNo,@RequestParam("pageSize") int pageSize,@RequestParam(name = "unit",required = false) String unit,@RequestParam(name = "username",required = false) String username,@RequestParam(name = "truename",required = false) String truename,@RequestParam(name = "date",required = false) String date) throws IOException {Evorecorddto queryInfo = new Evorecorddto();queryInfo.setPageNo(pageNo);queryInfo.setPageSize(pageSize);if (unit !=null){queryInfo.setUnit(unit);}if (username!=null){queryInfo.setUsername(username);}if (truename!=null){queryInfo.setTruename(truename);}if (date!=null){ObjectMapper objectMapper = new ObjectMapper();Evorecorddto.DateRange dateRange = objectMapper.readValue(date, Evorecorddto.DateRange.class);queryInfo.setDate(dateRange);}// 查询所有答题用户列表List<excelVo> records = evaluationService.excelprint(queryInfo);ExcelUtil.exportExcel(records, null, "答题排名", excelVo.class, "答题排名", response);}

另外需要修改excel导出颜色的话可以修改官方提供的接口

        4.修改接口

package com.wzz.utils;import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;/*** @author:Kevin* @create: 2023-08-24 10:14* @Description:*/public class ExcelStyleUtil implements IExcelExportStyler {// 数据行类型private static final String DATA_STYLES = "dataStyles";// 标题类型private static final String TITLE_STYLES = "titleStyles";//数据行样式private CellStyle styles;// 标题样式private CellStyle titleStyle;public ExcelStyleUtil(Workbook workbook) {this.init(workbook);}private void init(Workbook workbook) {this.styles = initStyles(workbook);this.titleStyle = initTitleStyle(workbook);}@Overridepublic CellStyle getHeaderStyle(short i) {return null;}@Overridepublic CellStyle getTitleStyle(short i) {return titleStyle;}@Overridepublic CellStyle getStyles(boolean b, ExcelExportEntity excelExportEntity) {return styles;}@Overridepublic CellStyle getStyles(Cell cell, int i, ExcelExportEntity excelExportEntity, Object o, Object o1) {return getStyles(true,excelExportEntity);}@Overridepublic CellStyle getTemplateStyles(boolean b, ExcelForEachParams excelForEachParams) {return null;}/*** 初始化--标题行样式* @param workbook* @return*/private CellStyle initTitleStyle(Workbook workbook) {return buildCellStyle(workbook,TITLE_STYLES);}/*** 初始化--数据行样式* @param workbook* @return*/private CellStyle initStyles(Workbook workbook) {return buildCellStyle(workbook,DATA_STYLES);}/*** 设置单元格样式* @param workbook* @param type 类型  用来区分是数据行样式还是标题样式* @return*/private CellStyle buildCellStyle(Workbook workbook,String type) {CellStyle style = workbook.createCellStyle();// 字体样式Font font = workbook.createFont();if(TITLE_STYLES.equals(type)){// 背景色style.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);font.setFontHeightInPoints((short)12);font.setBold(true);font.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());}if(DATA_STYLES.equals(type)){font.setFontHeightInPoints((short)10);}font.setFontName("Courier New");style.setFont(font);// 设置底边框style.setBorderBottom(BorderStyle.THIN);// 设置左边框style.setBorderLeft(BorderStyle.THIN);// 设置右边框;style.setBorderRight(BorderStyle.THIN);// 设置顶边框;style.setBorderTop(BorderStyle.THIN);// 设置底边颜色style.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());// 设置左边框颜色;style.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());// 设置右边框颜色;style.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());// 设置顶边框颜色;style.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());// 设置自动换行;style.setWrapText(false);// 设置水平对齐的样式为居中对齐;style.setAlignment(HorizontalAlignment.CENTER);// 设置垂直对齐的样式为居中对齐;style.setVerticalAlignment(VerticalAlignment.CENTER);return style;}}