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-dispositionresponse.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);// 重置responseresponse.reset();response.setContentType("application/json");response.setCharacterEncoding("utf-8");try {response.getWriter();} catch (IOException ioException) {ioException.printStackTrace();}}}