SpringBoot+EasyExcel设置excel样式

方式一：使用注解方式设置样式

模板可通过@HeadFontStyle、@HeadStyle、@ContentFontStyle、@ContentStyle、@HeadRowHeight @ContentRowHeight等注解设置excel单元格样式；

//字体样式及字体大小
@HeadFontStyle(fontName = "宋体",fontHeightInPoints = 11)
@ContentFontStyle(fontName = "宋体",fontHeightInPoints = 11)

//标题样式
@HeadStyle(wrapped = BooleanEnum.FALSE,
horizontalAlignment= HorizontalAlignmentEnum.CENTER,
verticalAlignment = VerticalAlignmentEnum.CENTER,
borderLeft = BorderStyleEnum.THIN,
borderRight = BorderStyleEnum.THIN,
borderTop = BorderStyleEnum.THIN,
borderBottom = BorderStyleEnum.THIN,
fillForegroundColor = 49)

//单元格内容样式
@ContentStyle(wrapped = BooleanEnum.FALSE,
horizontalAlignment= HorizontalAlignmentEnum.CENTER,
verticalAlignment = VerticalAlignmentEnum.CENTER,
borderLeft = BorderStyleEnum.THIN,
borderRight = BorderStyleEnum.THIN,
borderTop = BorderStyleEnum.THIN,
borderBottom = BorderStyleEnum.THIN,
fillForegroundColor = 9)

//标题及数据行高
@HeadRowHeight(24)
@ContentRowHeight(25)
public class CertifyOrderDetailsExcel {

@ColumnWidth(10)
@ExcelProperty(value = {"统计","名称"},index = 1)
private String name;

 

@ContentStyle(wrapped = BooleanEnum.TRUE,
        horizontalAlignment= HorizontalAlignmentEnum.CENTER,
        verticalAlignment = VerticalAlignmentEnum.CENTER,
        borderLeft = BorderStyleEnum.THIN,
        borderRight = BorderStyleEnum.THIN,
        borderTop = BorderStyleEnum.THIN,
        borderBottom = BorderStyleEnum.THIN,
        fillForegroundColor = 9)

 

使用easyexcelFactory将数据到出excel文件

业务实现导出核心部分代码如下：

//此处list为需要导出的数据，通过数据库查询

List<Object> list = null;
// ServletOutputStream outputStream = response.getOutputStream();
//将列表导成excel，fileName为文件的具体路径（自行设定），也可为outputStream输出流

//CertifyOrderDetailsExcel.class为上面配置的excel模板类
EasyExcel.write(fileName, CertifyOrderDetailsExcel.class)
.sheet("统计")
.relativeHeadRowIndex(0)
.doWrite(list);

方式二：整体设置样式

public static <T> void writeExcel(HttpServletRequest request, HttpServletResponse response, List<T> data, Class tClass, String name){
        try {
            name = java.net.URLEncoder.encode(name, "UTF-8").replaceAll("\\+", "%20");
            String dateStr = String.valueOf(LocalDate.now().getMonthValue())+"-"+LocalDate.now().getDayOfYear();
            name = name+dateStr;
            //防止下载时中文乱码
            name = new String(name.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            response.setCharacterEncoding("utf-8");
            //添加这个是防止前端拿不到Content-disposition
            response.setHeader("Content-Disposition", "attachment;filename=" + name+".xlsx");
            response.setHeader("Access-Control-Expose-Headers","Content-disposition");
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short) 12);
            headWriteCellStyle.setWriteFont(headWriteFont);
            //内容样式策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();

            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
            contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
            contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
            contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
            contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
            //设置 自动换行
            contentWriteCellStyle.setWrapped(true);
            // 字体策略
            WriteFont contentWriteFont = new WriteFont();
            // 字体大小
            contentWriteFont.setFontHeightInPoints((short) 12);
            contentWriteCellStyle.setWriteFont(contentWriteFont);
            // 这里需要设置不关闭流
            EasyExcelFactory.write(response.getOutputStream(), tClass).registerWriteHandler(new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle)).autoCloseStream(Boolean.FALSE).sheet("毛坯反馈参数(越南)").doWrite(data);
        } catch (Exception e) {
            //log.error("文件导出失败,错误信息{}",e);
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            try {
                response.getWriter();
            } catch (IOException ioException) {
                ioException.printStackTrace();
            }
        }
    }