文章目录

• 问题
• 实战
• • 工具类
  • ExcelExportStylerUtil类——实现自由调样式
• 总结

问题

上一篇文章只使用简单的注解、无法改变字体大小、颜色，单元格颜色、边框问题。

实战

工具类

比上一次只多了一行代码，ExcelExportStylerUtil类只是为了自由实现我们想要的样式。

/*** 导出Excel加边框样式*/public static void exportNewExcel(List list, String title, String sheetName, Class entity, String fileName, HttpServletResponse response) {ExportParams exportParams = new ExportParams(title, sheetName);//添加样式exportParams.setStyle(ExcelExportStylerUtil.class);//冻结表头exportParams.setCreateHeadRows(true);Workbook workbook = ExcelExportUtil.exportExcel(exportParams, entity, list);if (workbook == null) {throw new RuntimeException("Excel表导出失败");}OutputStream outputStream = null;BufferedOutputStream buffOutputStream = null;try {// 指定下载的文件名--设置响应头response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");//response.setContentType("application/vnd.ms-excel;charset=UTF-8");response.setHeader("Pragma", "no-cache");response.setHeader("Cache-Control", "no-cache");response.setDateHeader("Expires", 0);response.setCharacterEncoding("UTF-8");// 导出ExceloutputStream = response.getOutputStream();buffOutputStream = new BufferedOutputStream(outputStream);workbook.write(buffOutputStream);buffOutputStream.flush();} catch (Exception e) {e.printStackTrace();} finally {try {if (outputStream != null) {outputStream.close();}if (buffOutputStream != null) {buffOutputStream.close();}if (workbook != null) {workbook.close();}} catch (Exception e) {e.printStackTrace();}}}

ExcelExportStylerUtil类——实现自由调样式

import cn.afterturn.easypoi.excel.export.styler.AbstractExcelExportStyler;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;public class ExcelExportStylerUtil extends AbstractExcelExportStyler implements IExcelExportStyler {public ExcelExportStylerUtil(Workbook workbook) {super.createStyles(workbook);}@Overridepublic CellStyle getHeaderStyle(short headerColor) {CellStyle titleStyle = workbook.createCellStyle();Font font = workbook.createFont();font.setFontHeightInPoints((short) 12);titleStyle.setFont(font);titleStyle.setAlignment(HorizontalAlignment.CENTER);         // 水平居中titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);   // 上下居中titleStyle.setBorderBottom(BorderStyle.THIN); //下边框titleStyle.setBorderLeft(BorderStyle.THIN);//左边框titleStyle.setBorderTop(BorderStyle.THIN);//上边框titleStyle.setBorderRight(BorderStyle.THIN);//右边框return titleStyle;}@Overridepublic CellStyle getTitleStyle(short color) {CellStyle titleStyle = workbook.createCellStyle();titleStyle.setAlignment(HorizontalAlignment.CENTER);         // 水平居中titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);   // 上下居中titleStyle.setBorderBottom(BorderStyle.THIN); //下边框titleStyle.setBorderLeft(BorderStyle.THIN);//左边框titleStyle.setBorderTop(BorderStyle.THIN);//上边框titleStyle.setBorderRight(BorderStyle.THIN);//右边框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.setBorderBottom(BorderStyle.THIN); //下边框style.setBorderLeft(BorderStyle.THIN);//左边框style.setBorderTop(BorderStyle.THIN);//上边框style.setBorderRight(BorderStyle.THIN);//右边框style.setDataFormat(STRING_FORMAT);if (isWarp) {style.setWrapText(true);}return style;}@Overridepublic CellStyle stringNoneStyle(Workbook workbook, boolean isWarp) {CellStyle style = workbook.createCellStyle();style.setAlignment(HorizontalAlignment.CENTER);         // 水平居中style.setVerticalAlignment(VerticalAlignment.CENTER);   // 上下居中style.setBorderBottom(BorderStyle.THIN); //下边框style.setBorderLeft(BorderStyle.THIN);//左边框style.setBorderTop(BorderStyle.THIN);//上边框style.setBorderRight(BorderStyle.THIN);//右边框style.setDataFormat(STRING_FORMAT);if (isWarp) {style.setWrapText(true);}return style;}
}

总结

在网上浏览了很多其他的博客，发现如果想要实现自己的样式，就需要重新生成一个文本簿，然后再慢慢添加样式，添加表头，但浪费了我们写的实体类注解，很是麻烦。现在，把你想要的样式写入ExcelExportStylerUtil类即可实现，目前只是提供了单元格边框、字体居中的功能。这一篇文章可以与上一篇文章完美融合，使用起来非常丝滑，解决你Excel表的大部分问题。