Easypoi的导出excel样式调整

谁借莪１个温暖的怀抱￠ 2023-01-22 14:54 7阅读 0赞

## 首先写一个样式工具类 ##

可以调整字体大小等等参数

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 2019/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
         */
        @Override
        public CellStyle getHeaderStyle(short color) {
            return headerStyle;
        }
    
        /**
         * 每列标题样式
         *
         * @param color
         * @return
         */
        @Override
        public CellStyle getTitleStyle(short color) {
            return titleStyle;
        }
    
        /**
         * 数据行样式
         *
         * @param parity 可以用来表示奇偶行
         * @param entity 数据内容
         * @return 样式
         */
        @Override
        public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
            return styles;
        }
    
        /**
         * 获取样式方法
         *
         * @param dataRow 数据行
         * @param obj     对象
         * @param data    数据
         */
        @Override
        public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
            return getStyles(true, entity);
        }
    
        /**
         * 模板使用的样式设置
         */
        @Override
        public 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;
        }
    }

## 实际导出的业务类中设置这里就可以设置样式 ##

ExportParams exportParams = new ExportParams(null, “delivery”, ExcelType.XSSF);  
exportParams.setStyle(ExcelStyleUtil.class);

详细代码:

/**
         * 批量导出配送排班表
         */
        @GetMapping("/delivery/export")
        public void export(HttpServletResponse response) {
            logger.info("[manage][DeliveryController][export] start");
            String mm = DateUtil.DateToStr(new Date(), "MM");
            String fileName = "配送排班表" + mm + "月.xlsx";
            ExportParams exportParams  = new ExportParams(null, "delivery", ExcelType.XSSF);
            exportParams.setStyle(ExcelStyleUtil.class);
            Workbook workbook = ExcelExportUtil.exportBigExcel(exportParams,
                    DeliveryScheduleVoPlus.class,
                    excelExportServer,
                    null);
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            try {
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
                workbook.write(response.getOutputStream());
    
            } catch (IOException e) {
                e.printStackTrace();
            }
            logger.info("[manage][DeliveryController][export] export success");
        }