使用easypoi导出excel设置表头样式

使用easypoi导出excel设置表头属性

之前使用easypoi导出excel的时候，没太关注这个表头的样式设置，直到前几天看到个需求，需要表头设置蓝底白字，懵了。嘿嘿，不过百度了一下，懂了一点点。

导入easypoi

<easypoi.version>4.0.0</easypoi.version><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-base</artifactId><version>${easypoi.version}</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-web</artifactId><version>${easypoi.version}</version></dependency><dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-annotation</artifactId><version>${easypoi.version}</version></dependency>

ExcelUtil

package com.aqara.project.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 cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;import cn.afterturn.easypoi.handler.inter.IExcelDataModel;import cn.afterturn.easypoi.handler.inter.IExcelModel;import mon.collect.Lists;import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import org.springframework.web.multipart.MultipartFile;import javax.servlet.ServletOutputStream;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.io.Serializable;import java.nio.charset.StandardCharsets;import java.util.Collections;import java.util.HashMap;import java.util.List;import java.util.Map;/*** @Title: excel导出工具类* @Author: jackson* @Date: -09-24 17:44*/@Slf4jpublic class ExcelUtil {static ServletOutputStream out = null;private ExcelUtil() {}/*** 获取总页数*/public static Long getTotalPage(Long totalCount, Long pageSize) {if (totalCount % pageSize == 0) {return totalCount / pageSize;} else {return (totalCount / pageSize) + 1;}}/*** 获取导出表格参数*/public static ExportParams getExportParams(String title, String sheetName) {return new ExportParams(title, sheetName, ExcelType.XSSF);}/*** 适配集合+实体类方式导出*/public static void exportExcel(String fileName, String title, String sheetName, List<?> list,Class<?> pojoClass,HttpServletResponse response) {ExportParams exportParams = new ExportParams(title, sheetName);exportParams.setType(ExcelType.XSSF); //此处格式对应下文文件名后缀xlsxWorkbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);defaultExport(workbook, fileName, response);}public static void exportExcel(String fileName, String title, String sheetName, List<?> list,Class<?> pojoClass,HttpServletResponse response, boolean flag) {ExportParams exportParams = new ExportParams(title, sheetName);exportParams.setType(ExcelType.XSSF); //此处格式对应下文文件名后缀xlsxif (flag) {exportParams.setStyle(ExcelExportTitleStyle.class);}Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);defaultExport(workbook, fileName, response);}/*** 适配集合+实体类方式导出专用于返回列表下还有统计总数、等*/public static Workbook exportExcel(String fileName, String title, String sheetName, List<?> list,Class<?> pojoClass) {ExportParams exportParams = new ExportParams(title, sheetName);//此处格式对应下文文件名后缀xlsxexportParams.setType(ExcelType.XSSF);return ExcelExportUtil.exportExcel(exportParams, pojoClass, list);}/*** 适配动态列的导出方式** @param colList 动态列*/public static void exportExcel(String fileName, String title, String sheetName,List<ExcelExportEntity> colList, List<Map<String, Object>> dataList,HttpServletResponse response) {ExportParams exportParams = new ExportParams(title, sheetName);exportParams.setType(ExcelType.XSSF); // 此处格式对应下文文件名后缀xlsxWorkbook workbook = ExcelExportUtil.exportExcel(exportParams, colList, dataList);defaultExport(workbook, fileName, response);}public static void defaultExport(Workbook workbook, String fileName, HttpServletResponse response) {defaultExport(workbook, fileName, response, "application/msexcel; charset=UTF-8");}public static void defaultBigDataExport(Workbook workbook, String fileName, HttpServletResponse response) {defaultExport(workbook, fileName, response, "application/vnd.openxmlformats-officedocument.drawing+xml; charset=UTF-8");}private static void defaultExport(Workbook workbook, String fileName, HttpServletResponse response, String contentType) {if (workbook == null) {log.warn("导出workbook对象(文件名:{})为空!请检查", fileName);return;}try {response.setCharacterEncoding("UTF-8");response.setHeader("Content-Disposition", "attachment;filename=" +new String(fileName.getBytes("gbk"), "iso8859-1") + ".xlsx");response.setContentType(contentType);workbook.write(response.getOutputStream());} catch (IOException e) {log.error(e.getMessage(), e);}}/*** 导入excel,此种方式支持校验,但是无校验的信息*/public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Integer sheetIndex,Class<T> pojoClass) throws Exception {ImportParams params = getParams(file, titleRows, headerRows);if (params == null) {return Collections.emptyList();}//要读取的 sheet数目params.setSheetNum(1);params.setStartSheetIndex(sheetIndex);return ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);}/*** 导入excel,此种方式支持校验,也可获取校验的信息*/public static <T> ExcelImportResult<T> importExcelMore(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws Exception {ImportParams params = getParams(file, titleRows, headerRows);if (params == null) {return null;}return ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);}private static ImportParams getParams(MultipartFile file, Integer titleRows, Integer headerRows) {if (file == null) {return null;}ImportParams params = new ImportParams();params.setTitleRows(titleRows);params.setHeadRows(headerRows);params.setNeedVerify(true);return params;}/*** 收集excel校验失败的信息*/public static Boolean collectExcelVerifyFailMsg(StringBuilder rowMsg,ExcelImportResult<? extends BaseImportExcelDto> importResult) {List<? extends BaseImportExcelDto> failList = importResult.getFailList();//无错误信息返回 trueif (failList == null || failList.isEmpty()) {return true;}for (BaseImportExcelDto baseImportExcelDto : failList) {rowMsg.append("[excel第" + (baseImportExcelDto.getRowNum() + 1) + "行" +baseImportExcelDto.getErrorMsg() + "]；");}return false;}/*** 导出模板** @param fileName 文件名* @param filePath 模板位置*/public static void exportTemplateExcel(String fileName, String filePath,HttpServletResponse response) {try (InputStream inputStream = ExcelUtil.class.getResourceAsStream(filePath);XSSFWorkbook excelTemplate = new XSSFWorkbook(inputStream);OutputStream outputStream = response.getOutputStream()) {response.reset();response.setHeader("Content-Disposition","attachment;filename=" + new String(fileName.getBytes("gbk"), "iso8859-1")+ ".xlsx");response.setContentType("application/msexcel; charset=UTF-8");// 解决跨域问题response.addHeader("Access-Control-Allow-Origin", "*");excelTemplate.write(outputStream);} catch (Exception e) {log.error(e.getMessage(), e);exportWarnMsg(response, "导出模板失败!,路径：" + filePath);}}/*** 通过response输出响应信息*/public static void exportWarnMsg(HttpServletResponse response, String msg) {try {response.setContentType("application/json;charset-utf-8");ServletOutputStream outputStream = response.getOutputStream();outputStream.write(msg.getBytes(StandardCharsets.UTF_8));outputStream.flush();outputStream.close();} catch (IOException e) {log.error(e.getMessage(), e);}}public static void exportDesign(String fileName, String title, String sheetName, List<?> list, Class<?> pojoClass,HttpServletResponse response) {ExportParams exportParams = new ExportParams(title, sheetName);exportParams.setType(ExcelType.XSSF);Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);Sheet sheet = workbook.getSheet(sheetName);// 隐藏最后一列sheet.setColumnHidden(sheet.getRow(0).getPhysicalNumberOfCells() - 1, true);defaultExport(workbook, fileName, response);}/*** 导入excel,此种方式支持校验,也可获取校验的信息*/public static <T> ExcelImportResult<T> importExcelWithVerifyMessages(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass)throws Exception {ImportParams params = getParams(file, titleRows, headerRows);if (params == null) {return null;}return ExcelImportUtil.importExcelMore(file.getInputStream(), pojoClass, params);}public static class BaseImportExcelDto implements IExcelModel, IExcelDataModel, Serializable {/*** 实现IExcelDataModel接口,返回行号*/private int rowNum;/*** 实现IExcelModel接口,返回不符合的信息*/private String errorMsg;@Overridepublic int getRowNum() {return rowNum;}@Overridepublic void setRowNum(int rowNum) {this.rowNum = rowNum;}@Overridepublic String getErrorMsg() {return errorMsg;}@Overridepublic void setErrorMsg(String errorMsg) {this.errorMsg = errorMsg;}}/*** @Description:创建一个sheet页的数据* @Author: weiwu* @Date: -02-03**/public static Map<String, Object> createOneSheet(String sheetName, String title, Class<?> clazz, List<?> data) {ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);Map<String, Object> map = new HashMap<>();map.put("title", exportParams);//new ExportParams("title"+i, "sheetName"+i, ExcelType.XSSF)map.put("entity", clazz);map.put("data", data);return map;}/*** @Description:生成多个sheet页的Workbook* @Author: weiwu* @Date: -02-03**/public static void exportExcelMultipleSheet(String fileName, List<Map<String, Object>> mapListList, HttpServletResponse response) {Workbook workbook = ExcelExportUtil.exportExcel(mapListList, ExcelType.XSSF);defaultExport(workbook, fileName, response);}public static final boolean checkExtensions(String extension) {return Lists.newArrayList("xls", "xlsx", "XLS", "XLSX").contains(extension);}public static void init(String fileName, HttpServletResponse response) throws IOException {out = response.getOutputStream();response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName).getBytes("gb2312"), StandardCharsets.ISO_8859_1) + ".xlsx");response.setContentType("multipart/form-data");response.setCharacterEncoding("utf-8");}}

一切准备就绪

我们把一个list准备导出（注意实体类的字段加上@Excel的注解哦），一切准备好了发现可以导出来了。但是我们现在要修改样式问题。

ExportParams这个是easypoi，我们点进去发现，他的style属性使用了默认的ExcelExportStylerDefaultImpl这个类的属性，茅塞顿开，我决定自定义一个class，然后set他的style属性，这样不就可以使用我们自定义的属性了？

/*** Excel 导出style*/private Class<?> style = ExcelExportStylerDefaultImpl.class;

开始

我们新增一个类:ExcelExportTitleStyle,通过修改他的getTitleStyle方法进行我们的自定义颜色。

package com.aqara.agreement.utils;import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler;import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;import org.apache.poi.ss.usermodel.BorderStyle;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.Font;import org.apache.poi.ss.usermodel.HorizontalAlignment;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.VerticalAlignment;import org.apache.poi.ss.usermodel.Workbook;/*** 导出自定义title的工具类** @author changjiang.liu* @date /5/23 16:44*/public class ExcelExportTitleStyle extends AbstractExcelExportStylerimplements IExcelExportStyler {public ExcelExportTitleStyle(Workbook workbook) {super.createStyles(workbook);}@Overridepublic CellStyle getTitleStyle(short color) {CellStyle titleStyle = workbook.createCellStyle();// 自定义字体Font font = workbook.createFont();font.setColor(IndexedColors.WHITE1.getIndex());font.setBold(true);font.setFontName("宋体");titleStyle.setFont(font);// 自定义背景色titleStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);titleStyle.setBorderBottom(BorderStyle.THIN);titleStyle.setBorderTop(BorderStyle.THIN);titleStyle.setBorderLeft(BorderStyle.THIN);titleStyle.setBorderRight(BorderStyle.THIN);titleStyle.setAlignment(HorizontalAlignment.CENTER);titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);titleStyle.setWrapText(true);return titleStyle;}@Overridepublic CellStyle stringSeptailStyle(Workbook workbook, boolean isWarp) {CellStyle style = workbook.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);style.setDataFormat(STRING_FORMAT);if (isWarp) {style.setWrapText(true);}return style;}@Overridepublic CellStyle getHeaderStyle(short color) {CellStyle titleStyle = workbook.createCellStyle();Font font = workbook.createFont();font.setFontHeightInPoints((short) 12);titleStyle.setFont(font);titleStyle.setAlignment(HorizontalAlignment.CENTER);titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);return titleStyle;}@Overridepublic CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {CellStyle style = workbook.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);style.setVerticalAlignment(VerticalAlignment.CENTER);style.setDataFormat(STRING_FORMAT);if (isWarp) {style.setWrapText(true);}return style;}}

##效果