如有疑问可加easypoi官方群,QQ群号码: 116844390
引入依赖,此样式基于springboot版本.依赖版本如下(maven):<dependency><groupId>cn.afterturn</groupId><artifactId>easypoi-spring-boot-starter</artifactId><version>4.3.0</version></dependency>easypoi官方源码地址:gitee:/lemur/easypoi?_from=gitee_search开发文档地址:/docs/easypoi/easypoi-1c0u4mo8p4ro8
导出样式如下图
使用方式
添加样式工具类:
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.ss.usermodel.*;/*** @author 王鑫磊* @date /11/04*/public class ExcelStyleUtil 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 = 10;/*** 大标题样式*/private CellStyle headerStyle;/*** 每列标题样式*/private CellStyle titleStyle;/*** 数据行样式*/private CellStyle styles;public ExcelStyleUtil(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*/@Overridepublic CellStyle getHeaderStyle(short color) {return headerStyle;}/*** 每列标题样式** @param color* @return*/@Overridepublic CellStyle getTitleStyle(short color) {return titleStyle;}/*** 数据行样式** @param parity 可以用来表示奇偶行* @param entity 数据内容* @return 样式*/@Overridepublic CellStyle getStyles(boolean parity, ExcelExportEntity entity) {return styles;}/*** 获取样式方法** @param dataRow 数据行* @param obj对象* @param data 数据*/@Overridepublic CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {return getStyles(true, entity);}/*** 模板使用的样式设置*/@Overridepublic 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.GREY_25_PERCENT.getIndex());style.setFillPattern(FillPatternType.SOLID_FOREGROUND);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(true);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;}}
添加导出工具类(导出工具类中书用到了一个工具包hutool-all强力推荐、使用时引入最新的就可以):
import cn.afterturn.easypoi.excel.ExcelExportUtil;import cn.afterturn.easypoi.excel.entity.ExportParams;import cn.afterturn.easypoi.excel.entity.TemplateExportParams;import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;import cn.hutool.core.date.DateUtil;import cn.hutool.core.util.StrUtil;import lombok.extern.slf4j.Slf4j;import org.apache.poi.ss.usermodel.Workbook;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.io.OutputStream;import .URLEncoder;import java.util.ArrayList;import java.util.List;import java.util.Map;/*** Office导出工具类** @author 王鑫磊* @date /11/04**/@Slf4jpublic class OfficeExportUtil {/** 允许导出的最大条数 */private static final Integer EXPORT_EXCEL_MAX_NUM = 10000;/*** 获取导出的 Workbook对象** @param title大标题* @param sheetName 页签名* @param object 导出实体* @param list数据集合* @return Workbook*/public static Workbook getWorkbook(String title, String sheetName, Class<?> object, List<?> list) {//判断导出数据是否为空if (list == null) {list = new ArrayList<>();}//判断导出数据数量是否超过限定值if (list.size() > EXPORT_EXCEL_MAX_NUM) {title = "导出数据行数超过:" + EXPORT_EXCEL_MAX_NUM + "条,无法导出、请添加导出条件!";list = new ArrayList<>();}//获取导出参数ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);//设置导出样式exportParams.setStyle(ExcelStyleUtil.class);//设置行高exportParams.setHeight((short) 6);//输出Workbook流return ExcelExportUtil.exportExcel(exportParams, object, list);}/*** 获取导出的 Workbook对象** @param path 模板路径* @param map 导出内容map* @return Workbook*/public static Workbook getWorkbook(String path, Map<String, Object> map) {//获取导出模板TemplateExportParams params = new TemplateExportParams(path);//设置导出样式params.setStyle(ExcelStyleUtil.class);//输出Workbook流return ExcelExportUtil.exportExcel(params, map);}/*** 导出Excel** @param workbook workbook流* @param fileName 文件名* @param response 响应*/public static void exportExcel(Workbook workbook, String fileName, HttpServletResponse response) {//给文件名拼接上日期fileName = fileName + StrUtil.UNDERLINE + DateUtil.today();//输出文件try (OutputStream out = response.getOutputStream()) {//获取文件名并转码String name = URLEncoder.encode(fileName, "UTF-8");//编码response.setCharacterEncoding("UTF-8");// 设置强制下载不打开response.setContentType("application/force-download");// 下载文件的默认名称response.setHeader("Content-Disposition", "attachment;filename=" + name + ".xlsx");//输出表格workbook.write(out);} catch (IOException e) {log.error("文件导出异常,详情如下:", e);} finally {try {if (workbook != null) {//关闭输出流workbook.close();}} catch (IOException e) {log.error("文件导出异常,详情如下:", e);}}}}
使用excel导出工具类
//导出excelOfficeExportUtil.exportExcel(OfficeExportUtil.getWorkbook("大标题", "页签名", 注解类.class, 数据集合), "导出文件名", response);
