EasyExcel+Web导出excel完整代码 附(表格样式调整，列宽自适应)

页面按钮请求

<button pButton type="button" label="Export" (click)="export()"
                            class="ui-button-rounded float-right ui-button-mt-30"></button>

JS方法调用接口

  export() {
        console.log('download report with startDate:' + this.cond.startDate + ' endDate' + this.cond.endDate);
        window.location.href = this.configJsonService.config.service_url + /export?startDate=${this.cond.startDate}&endDate=${this.cond.endDate}&token=${this.appService.token}`;
    }

Controller

  @GetMapping("/export")
    public void exportEmailZip( @RequestParam String startDate, @RequestParam String endDate,
                               HttpServletResponse response)
            throws IOException {
        String merchantName = "";
        // 时间周期
        String period = DateTimeUtils.getDateStr(DateTimeUtils.parseIsoDate(startDate)) + "-" + DateTimeUtils.getDateStr(
                DateTimeUtils.parseIsoDate(endDate));
        String fileName = "YaBand " + merchantName + "_" + period + ".xls";
        // 这里 需要指定写用哪个class去写
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        response.setContentType("application/vnd.ms-excel; charset=utf-8");
        response.setCharacterEncoding("utf-8");
        EasyExcel.write(response.getOutputStream(), AgentMerchantPeriodDto.class)
                .registerWriteHandler(EasyExcelUtil.getStyleStrategy())
                .sheet("订单信息")
                .doWrite(data(startDate, endDate, merchantId));
    }

private List<AgentMerchantPeriodDto> data(String startDate, String endDate, Integer merchantId) {
		// 访问数据库拿到想要的数据并在Impl里面转换数据格式调整样式等等
        return orderInitService.agentPeriodList(startDate, endDate, merchantId);
    }

顺便附上：

表格样式调整

public static HorizontalCellStyleStrategy getStyleStrategy(){
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置为灰色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short)10);
        // 字体样式
        headWriteFont.setFontName("Frozen");
        headWriteFont.setColor(IndexedColors.BLUE.getIndex());
        headWriteCellStyle.setWriteFont(headWriteFont);
        //自动换行
        headWriteCellStyle.setWrapped(true);
        // 水平对齐方式
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        // 垂直对齐方式
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
        //        contentWriteCellStyle.setFillPatternType(FillPatternType.SQUARES);
        // 背景白色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
        // 字体样式
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short)10);
        // 字体样式
        contentWriteFont.setFontName("Calibri");
        contentWriteFont.setColor(IndexedColors.BLACK.getIndex());
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        // 设置边框
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

设置列宽自适应

public class ExcelCellWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
    private static final int MAX_COLUMN_WIDTH = 255;
    private  Map<Integer, Map<Integer, Integer>> CACHE = new HashMap(8);

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = (Map)CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
                maxColumnWidthMap = new HashMap(16);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            if (columnWidth >= 0) {
                if (columnWidth > 255) {
                    columnWidth = 255;
                }

                Integer maxColumnWidth = (Integer)((Map)maxColumnWidthMap).get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    ((Map)maxColumnWidthMap).put(cell.getColumnIndex(), columnWidth);
                    writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }

            }
        }
    }

    private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData cellData = (CellData)cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch(type) {
                    case STRING:
                        return cellData.getStringValue().getBytes().length;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }

}

友情链接：EasyExcel表格样式设置常用注解