列表分页导出实战

代码实战

Controller类：

  /**
     * 充电效率监控详情接口
     * @param requestData
     * @return
     */
    @PostMapping("queryChargeMonitorOrderDetail")
    public PageMessage queryChargeMonitorOrderDetail(@RequestBody RequestPageEntity<ChargeMonitorOrderEntity> requestData){
        log.info("queryChargeMonitorOrderDetail接口入参：{}", JSON.toJSONString(requestData));
        return convert(requestData.getHeader(),underPowerOrderService.queryChargeMonitorOrderDetail(requestData.getBody(),requestData.getPageIndex(),requestData.getPageSize()));
    }

    /**
     * 充电效率监控详情导出接口
     * @param requestData
     * @param response
     */
    @PostMapping("exportChargeMonitorOrderDetail")
    public void exportChargeMonitorOrderDetail(@RequestBody RequestData<ChargeMonitorOrderEntity> requestData, HttpServletResponse response){
        log.info("exportChargeMonitorOrderDetail接口入参：{}", JSON.toJSONString(requestData));
        List<UnderPowerOrderProcessParam> list=underPowerOrderService.exportChargeMonitorOrderDetail(requestData.getBody());
        try {
            new ExportExcel("充电效率监控详情", UnderPowerOrderProcessParam.class).setDataList(list).write(response, StatisticsHelper.getExcelFileName("充电效率监控详情")).dispose();
        }catch (Exception e){
            log.error("充电效率监控详情导出异常:{}",e);
        }
    }

Service类：

  /**
     * 充电效率监控详情接口
     * @param param
     * @return
     */
    public ResponsePageEntity queryChargeMonitorOrderDetail(ChargeMonitorOrderEntity param, int pageIndex, int pageSize){
        PageHelper.startPage(pageIndex,pageSize);
        List<UnderPowerOrderProcessParam> list= underPowerOrderMapper.queryChargeMonitorOrderDetail(param);
        return DataUtil.transformPageResult(PageInfo.of(list));
    }

    /**
     * 充电效率监控详情导出接口
     * @param param
     * @return
     */
    public List<UnderPowerOrderProcessParam> exportChargeMonitorOrderDetail(ChargeMonitorOrderEntity param){
        List<UnderPowerOrderProcessParam> list= underPowerOrderMapper.queryChargeMonitorOrderDetail(param);
        return list;
    }

    /**
     * 分页结果转换
     *
     * @param pageInfo 分页结果
     * @param <T>      T
     * @return 转换结果信息
     */
    public static <T> ResponsePageEntity transformPageResult(PageInfo<T> pageInfo) {
        ResponsePageEntity responsePage = successPageResponse();
        responsePage.setData(pageInfo.getList());
        responsePage.setPageSize(pageInfo.getPageSize());
        responsePage.setPageIndex(pageInfo.getPageNum());
        responsePage.setTotalPage(pageInfo.getPages());
        responsePage.setTotalCount((int) pageInfo.getTotal());
        return responsePage;
    }

ExportExcel工具：

package com.ykc.util.excel.fieldtype;

import com.google.common.collect.Lists;
import com.ykc.util.Encodes;
import com.ykc.util.OssUtils;
import com.ykc.util.Reflections;
import com.ykc.util.excel.annotation.ExcelField;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExportExcel {
    private static Logger log = LoggerFactory.getLogger(ExportExcel.class);
    private SXSSFWorkbook wb;
    private Sheet sheet;
    private Map<String, CellStyle> styles;
    private int rownum;
    private String fileName;
    List<Object[]> annotationList;

    public ExportExcel(String title, Class<?> cls) {
        this(title, cls, 1);
    }

    public ExportExcel(String title, Class<?> cls, int type, int... groups) {
        this.annotationList = Lists.newArrayList();
        Field[] fs = cls.getDeclaredFields();
        Field[] var6 = fs;
        int var7 = fs.length;

        int var8;
        int var14;
        int g;
        int var18;
        int efg;
        for(var8 = 0; var8 < var7; ++var8) {
            Field f = var6[var8];
            ExcelField ef = (ExcelField)f.getAnnotation(ExcelField.class);
            if (ef != null && (ef.type() == 0 || ef.type() == type)) {
                if (groups != null && groups.length > 0) {
                    boolean inGroup = false;
                    int[] var12 = groups;
                    int var13 = groups.length;

                    for(var14 = 0; var14 < var13; ++var14) {
                        g = var12[var14];
                        if (inGroup) {
                            break;
                        }

                        int[] var16 = ef.groups();
                        int var17 = var16.length;

                        for(var18 = 0; var18 < var17; ++var18) {
                            efg = var16[var18];
                            if (g == efg) {
                                inGroup = true;
                                this.annotationList.add(new Object[]{ef, f});
                                break;
                            }
                        }
                    }
                } else {
                    this.annotationList.add(new Object[]{ef, f});
                }
            }
        }

        Method[] ms = cls.getDeclaredMethods();
        Method[] var22 = ms;
        var8 = ms.length;

        for(int var25 = 0; var25 < var8; ++var25) {
            Method m = var22[var25];
            ExcelField ef = (ExcelField)m.getAnnotation(ExcelField.class);
            if (ef != null && (ef.type() == 0 || ef.type() == type)) {
                if (groups != null && groups.length > 0) {
                    boolean inGroup = false;
                    int[] var32 = groups;
                    var14 = groups.length;

                    for(g = 0; g < var14; ++g) {
                        int g = var32[g];
                        if (inGroup) {
                            break;
                        }

                        int[] var34 = ef.groups();
                        var18 = var34.length;

                        for(efg = 0; efg < var18; ++efg) {
                            int efg = var34[efg];
                            if (g == efg) {
                                inGroup = true;
                                this.annotationList.add(new Object[]{ef, m});
                                break;
                            }
                        }
                    }
                } else {
                    this.annotationList.add(new Object[]{ef, m});
                }
            }
        }

        Collections.sort(this.annotationList, new Comparator<Object[]>() {
            public int compare(Object[] o1, Object[] o2) {
                return (new Integer(((ExcelField)o1[0]).sort())).compareTo(new Integer(((ExcelField)o2[0]).sort()));
            }
        });
        List<String> headerList = Lists.newArrayList();

        String t;
        for(Iterator var24 = this.annotationList.iterator(); var24.hasNext(); headerList.add(t)) {
            Object[] os = (Object[])var24.next();
            t = ((ExcelField)os[0]).title();
            if (type == 1) {
                String[] ss = StringUtils.split(t, "**", 2);
                if (ss.length == 2) {
                    t = ss[0];
                }
            }
        }

        this.initialize(title, headerList);
    }

    public ExportExcel(String title, String[] headers) {
        this.annotationList = Lists.newArrayList();
        this.initialize(title, Lists.newArrayList(headers));
    }

    public ExportExcel(String title, List<String> headerList) {
        this.annotationList = Lists.newArrayList();
        this.initialize(title, headerList);
    }

    private void initialize(String title, List<String> headerList) {
        this.wb = new SXSSFWorkbook(500);
        this.sheet = this.wb.createSheet("Export");
        this.styles = this.createStyles(this.wb);
        Row headerRow;
        if (StringUtils.isNotBlank(title)) {
            headerRow = this.sheet.createRow(this.rownum++);
            headerRow.setHeightInPoints(30.0F);
            Cell titleCell = headerRow.createCell(0);
            titleCell.setCellStyle((CellStyle)this.styles.get("title"));
            titleCell.setCellValue(title);
            this.sheet.addMergedRegion(new CellRangeAddress(headerRow.getRowNum(), headerRow.getRowNum(), headerRow.getRowNum(), headerList.size() - 1));
        }

        if (headerList == null) {
            throw new RuntimeException("headerList not null!");
        } else {
            headerRow = this.sheet.createRow(this.rownum++);
            headerRow.setHeightInPoints(16.0F);

            int i;
            for(i = 0; i < headerList.size(); ++i) {
                Cell cell = headerRow.createCell(i);
                cell.setCellStyle((CellStyle)this.styles.get("header"));
                String[] ss = StringUtils.split((String)headerList.get(i), "**", 2);
                if (ss.length == 2) {
                    cell.setCellValue(ss[0]);
                    Comment comment = this.sheet.createDrawingPatriarch().createCellComment(new XSSFClientAnchor(0, 0, 0, 0, 3, 3, 5, 6));
                    comment.setString(new XSSFRichTextString(ss[1]));
                    cell.setCellComment(comment);
                } else {
                    cell.setCellValue((String)headerList.get(i));
                }

                this.sheet.autoSizeColumn(i);
            }

            for(i = 0; i < headerList.size(); ++i) {
                int colWidth = this.sheet.getColumnWidth(i) * 2;
                if (((String)headerList.get(i)).contains("时间")) {
                    this.sheet.setColumnWidth(i, 4000);
                } else {
                    this.sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth);
                }
            }

            log.debug("Initialize success.");
        }
    }

    private Map<String, CellStyle> createStyles(Workbook wb) {
        Map<String, CellStyle> styles = new HashMap();
        CellStyle style = wb.createCellStyle();
        style.setAlignment((short)2);
        style.setVerticalAlignment((short)1);
        Font titleFont = wb.createFont();
        titleFont.setFontName("Arial");
        titleFont.setFontHeightInPoints((short)16);
        titleFont.setBoldweight((short)700);
        style.setFont(titleFont);
        styles.put("title", style);
        style = wb.createCellStyle();
        style.setVerticalAlignment((short)1);
        style.setBorderRight((short)1);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft((short)1);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop((short)1);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom((short)1);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short)10);
        style.setFont(dataFont);
        styles.put("data", style);
        style = wb.createCellStyle();
        style.cloneStyleFrom((CellStyle)styles.get("data"));
        style.setAlignment((short)1);
        styles.put("data1", style);
        style = wb.createCellStyle();
        style.cloneStyleFrom((CellStyle)styles.get("data"));
        style.setAlignment((short)2);
        styles.put("data2", style);
        style = wb.createCellStyle();
        style.cloneStyleFrom((CellStyle)styles.get("data"));
        style.setAlignment((short)3);
        styles.put("data3", style);
        style = wb.createCellStyle();
        style.cloneStyleFrom((CellStyle)styles.get("data"));
        style.setAlignment((short)2);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern((short)1);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short)10);
        headerFont.setBoldweight((short)700);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);
        return styles;
    }

    public Row addRow() {
        return this.sheet.createRow(this.rownum++);
    }

    public String getFileName() {
        return this.fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public Cell addCell(Row row, int column, Object val) {
        return this.addCell(row, column, val, 0, Class.class);
    }

    public Cell addCell(Row row, int column, Object val, int align, Class<?> fieldType) {
        Cell cell = row.createCell(column);
        CellStyle style = (CellStyle)this.styles.get("data" + (align >= 1 && align <= 3 ? align : ""));

        try {
            if (val == null) {
                cell.setCellValue("");
            } else if (val instanceof String) {
                cell.setCellValue((String)val);
            } else if (val instanceof Integer) {
                cell.setCellValue((double)(Integer)val);
            } else if (val instanceof Long) {
                cell.setCellValue((double)(Long)val);
            } else if (val instanceof Double) {
                cell.setCellValue((Double)val);
            } else if (val instanceof Float) {
                cell.setCellValue((double)(Float)val);
            } else if (val instanceof Date) {
                DataFormat format = this.wb.createDataFormat();
                style.setDataFormat(format.getFormat("yyyy-MM-dd HH:mm:ss"));
                cell.setCellValue((Date)val);
            } else if (val instanceof BigDecimal) {
                double doubleVal = ((BigDecimal)val).doubleValue();
                DataFormat format = this.wb.createDataFormat();
                style.setDataFormat(format.getFormat("#,##0.00"));
                cell.setCellValue(doubleVal);
            } else if (fieldType != Class.class) {
                cell.setCellValue((String)fieldType.getMethod("setValue", Object.class).invoke((Object)null, val));
            } else {
                cell.setCellValue((String)Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(), "fieldtype." + val.getClass().getSimpleName() + "Type")).getMethod("setValue", Object.class).invoke((Object)null, val));
            }
        } catch (Exception var11) {
            log.info("Set cell value [" + row.getRowNum() + "," + column + "] error: " + var11.toString());
            cell.setCellValue(val.toString());
        }

        cell.setCellStyle(style);
        return cell;
    }

    public <E> ExportExcel setDataList(List<E> list) {
        Iterator var2 = list.iterator();

        while(var2.hasNext()) {
            E e = var2.next();
            int colunm = 0;
            Row row = this.addRow();
            StringBuilder sb = new StringBuilder();
            Iterator var7 = this.annotationList.iterator();

            while(var7.hasNext()) {
                Object[] os = (Object[])var7.next();
                ExcelField ef = (ExcelField)os[0];
                Object val = null;

                try {
                    if (StringUtils.isNotBlank(ef.value())) {
                        val = Reflections.invokeGetter(e, ef.value());
                    } else if (os[1] instanceof Field) {
                        val = Reflections.invokeGetter(e, ((Field)os[1]).getName());
                    } else if (os[1] instanceof Method) {
                        val = Reflections.invokeMethod(e, ((Method)os[1]).getName(), new Class[0], new Object[0]);
                    }

                    if (StringUtils.isNotBlank(ef.dictType())) {
                        val = val == null ? "" : val.toString();
                    }
                } catch (Exception var12) {
                    log.error("异常", e);
                    val = "";
                }

                this.addCell(row, colunm++, val, ef.align(), ef.fieldType());
                sb.append(val + ", ");
            }
        }

        log.info("拼装数据结束，共：" + (null != list ? list.size() : "空") + "条");
        return this;
    }

    public <E> ExportExcel setDataListBatch(List<E> list) {
        Iterator var2 = list.iterator();

        while(var2.hasNext()) {
            E e = var2.next();
            int colunm = 0;
            Row row = this.addRow();
            new StringBuilder();
            List<String> val = (List)Reflections.invokeGetter(e, "ExcelFieldValue");
            Iterator var8 = val.iterator();

            while(var8.hasNext()) {
                String v = (String)var8.next();
                this.addCell(row, colunm++, v, 2, String.class);
            }
        }

        log.info("拼装数据结束，共：" + (null != list ? list.size() : "空") + "条");
        return this;
    }

    public ExportExcel write(OutputStream os) throws IOException {
        this.wb.write(os);
        return this;
    }

    public ExportExcel write(HttpServletResponse response, String fileName) throws IOException {
        response.reset();
        response.setContentType("application/octet-stream; charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + Encodes.urlEncode(fileName));
        this.write(response.getOutputStream());
        return this;
    }

    public ExportExcel writeFile() throws FileNotFoundException, IOException {
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        this.write(os);
        byte[] content = os.toByteArray();
        InputStream is = new ByteArrayInputStream(content);
        String uuid = UUID.randomUUID().toString().replace("-", "");
        String fileName = OssUtils.putFileIntoBuecket("wtayvyzzmyanm9vx-car-test", is, uuid + ".xls", 100L, "12");
        this.setFileName(fileName);
        return this;
    }

    public ExportExcel dispose() {
        this.wb.dispose();
        return this;
    }
}

导出实体：

package com.ykc.entity.underPowerOrder;

import com.ykc.util.excel.annotation.ExcelField;
import lombok.Data;

import java.util.List;
import java.util.Map;

/**
 * 功率不足订单过程入参UnderPowerOrderProcessParam
 * author：wangfan
 * date：2021.3.11
 */
@Data
public class UnderPowerOrderProcessParam {

    /**
     * 订单号
     */
    @ExcelField(title="订单号", align=2, sort=1,dictType="String")
    private String tradeSeq;

    /**
     * 接收时间
     */
    @ExcelField(title="接收时间", align=2, sort=2,dictType="String")
    private String createTime;

    /**
     * 异常类型原因
     */
    @ExcelField(title="判断结果", align=2, sort=3,dictType="String")
    private String abnormalTypeReason;

    /**
     * 需求电流原值
     */
    @ExcelField(title="需求电流原值", align=2, sort=4,dictType="String")
    private String value1;

    /**
     * 输出电流原值
     */
    @ExcelField(title="输出电流原值", align=2, sort=5,dictType="String")
    private String value2;

    /**
     * 测量电流原值
     */
    @ExcelField(title="测量电流原值", align=2, sort=6,dictType="String")
    private String value3;

    /**
     * 电流清理方式
     */
    @ExcelField(title="电流清理方式", align=2, sort=7,dictType="String")
    private String value4;

    /**
     * 需求电流门槛值
     */
    @ExcelField(title="需求电流门槛值", align=2, sort=8,dictType="String")
    private String value5;

    /**
     * 需求电流与输出电流绝对值差
     */
    @ExcelField(title="需求电流与输出电流绝对值差", align=2, sort=9,dictType="String")
    private String value6;

    /**
     * 需求电流与输出电流绝对值差（判断参数）
     */
    @ExcelField(title="需求电流与输出电流绝对值差（判断参数）", align=2, sort=10,dictType="String")
    private String value7;

    /**
     * 运维层面功率满足系数
     */
    @ExcelField(title="运维层面功率满足系数", align=2, sort=11,dictType="String")
    private String value8;

    /**
     * 运维层面功率满足系数（判断参数）
     */
    @ExcelField(title="运维层面功率满足系数（判断参数）", align=2, sort=12,dictType="String")
    private String value9;

    /**
     * 运营层面功率满足系数
     */
    @ExcelField(title="运营层面功率满足系数", align=2, sort=13,dictType="String")
    private String value10;

    /**
     * 运营层面功率满足系数（判断参数）
     */
    @ExcelField(title="运营层面功率满足系数（判断参数）", align=2, sort=14,dictType="String")
    private String value11;

    /**
     * 需求电压与电池测量电压差值
     */
    @ExcelField(title="需求电压与电池测量电压差值", align=2, sort=15,dictType="String")
    private String value12;

    /**
     * 需求电压与电池测量电压差值（判断参数）
     */
    @ExcelField(title="需求电压与电池测量电压差值（判断参数）", align=2, sort=16,dictType="String")
    private String value13;

    /**
     * 充电枪最大充电电流
     */
    @ExcelField(title="充电枪最大充电电流", align=2, sort=17,dictType="String")
    private String value14;

    /**
     * 充电枪最大充电电流（判断参数）
     */
    @ExcelField(title="充电枪最大充电电流（判断参数）", align=2, sort=18,dictType="String")
    private String value15;

    /**
     * 电桩校正系数1
     */
    @ExcelField(title="电桩校正系数1", align=2, sort=19,dictType="String")
    private String value16;

    /**
     * 充电模块校正系数1
     */
    @ExcelField(title="充电模块校正系数1", align=2, sort=20,dictType="String")
    private String value17;

    /**
     * 电桩校正系数2
     */
    @ExcelField(title="电桩校正系数2", align=2, sort=21,dictType="String")
    private String value18;

    /**
     * 充电模块校正系数2
     */
    @ExcelField(title="充电模块校正系数2", align=2, sort=22,dictType="String")
    private String value19;

}