封装下Excel导出

1. 使用方法

1.1 对象使用注解

@ExcelColumn(name = "页面1",freeze = "0,1,1,2"，autoWidth=true)
public class ProductExcelSheetOneVO {
　　　private Long id;

    /**
     * 订单号
     */
    @ExcelColumn(name = "服单订单号", order = 1, hyperLinkTemplate = "https://xxx?code={}&test={}", hyperLinkPattern = {"orderCode","id"})
    private String orderCode;
}

1.2 构造数据

List<Object> sheetOneVOS = Lists.newArrayList();
List<Object> sheetTwoVOS = Lists.newArrayList();
ExcelExportUtil.exportExcelStram(response.getOutputStream(), Lists.newArrayList(sheetData,sheetData2));

2. 注解

@Target({ElementType.TYPE, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelColumn {
    String name();

    String hyperLinkTemplate() default "";

    String[] hyperLinkPattern() default {};

    int order() default 0;

    String datePattern() default "yyyy-MM-dd HH:mm:ss";

    /**
     * 冻结表头
     * 有则为启用
     * 格式为   1,2
     *  或     1,2,3,4
     * @return
     */
    String freeze() default "";

    boolean autoWidth() default false;
}

3. 执行方法

public class ExcelExportUtil {


    /**
     * excel 文件输出
     *
     * @param outputStream
     * @param sheetList
     * @throws IOException
     */
    public static void exportExcelStram(OutputStream outputStream, List<Sheet> sheetList) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();
        sheetList.forEach(m -> initSheet(m.getContentList(), m.getClzz(), workbook));
        workbook.write(outputStream);
    }

    /**
     * 组织单sheet
     *
     * @param content
     * @param clzz
     * @param workbook
     */
    private static void initSheet(List<Object> content, Class clzz, HSSFWorkbook workbook) {

        /**
         * get excel header from annotation
         */
        List<Head> header = Lists.newArrayList();
        Field[] fields = clzz.getDeclaredFields();
        /**
         * init header
         */
        Map<String, Head> headMap = initHeader(header, fields);

        HSSFSheet sheet;
        ExcelColumn rowColumn = (ExcelColumn) clzz.getAnnotation(ExcelColumn.class);

        if (rowColumn != null) {
            sheet = workbook.createSheet(rowColumn.name());
        } else {
            sheet = workbook.createSheet();
        }
        Method[] methods = clzz.getDeclaredMethods();
        Map<String, Method> methodMap = Maps.uniqueIndex(Lists.newArrayList(methods), input -> input.getName().toLowerCase());

        /**
         * init header
         */
        AtomicInteger rowIndex = new AtomicInteger(0);
        HSSFRow headerRow = sheet.createRow(rowIndex.getAndAdd(1));
        AtomicInteger headerIndex = new AtomicInteger(0);
        header.forEach((k) -> {
            HSSFCell cell = headerRow.createCell(headerIndex.getAndAdd(1));
            cell.setCellValue(k.getTitle());
        });
        /**
         * init content
         */
        Collections.synchronizedList(content).parallelStream().forEach(m -> {

            Map<String, Object> contentMap = Maps.newHashMap();
            for (int i = 0; i < fields.length; i++) {
                if (!headMap.keySet().contains(fields[i].getName())) {
                    continue;
                }
                fields[i].setAccessible(true);
                Object val = null;
                try {
//                    val = fields[i].get(m);
                    //get val from get method
                    System.out.println(fields[i].getName());
                    val = methodMap.get("get" + fields[i].getName().toLowerCase()).invoke(m);
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }
                if (fields[i].getType() == Date.class) {
                    contentMap.put(fields[i].getName(), HikDateUtils.getDateString((Date) val, headMap.get(fields[i].getName()).getDatePattern()));
                } else if (fields[i].getType() == BigDecimal.class) {
                    contentMap.put(fields[i].getName(), String.valueOf(val));
                } else {
                    contentMap.put(fields[i].getName(), val == null ? "" : String.valueOf(val));
                }
            }
            HSSFRow row = sheet.createRow(rowIndex.getAndAdd(1));
            AtomicInteger cellIndex = new AtomicInteger(0);
            header.forEach((k) -> {
                HSSFCell cell = row.createCell(cellIndex.getAndAdd(1));
                cell.setCellValue(contentMap.get(k.getCode()) + "");

                if (!StringUtils.isEmpty(k.getHyperlinkTemplate())) {
                    String hyLink = HikStringUtils.append(k.getHyperlinkTemplate(), Lists.newArrayList(k.getHyperlinkPattern()).stream().map(p ->
                            contentMap.get(p) == null ? "" : contentMap.get(p).toString()
                    ).toArray());

                    HSSFHyperlink link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
                    link.setAddress(hyLink);
                    cell.setHyperlink(link);
                }
            });
        });
        /**
         * auto width
         */
        if (rowColumn.autoWidth()) {
            for (int i = 0; i < content.size(); i++) {
                sheet.autoSizeColumn(i, true);
            }
        }
        if (!StringUtils.isEmpty(rowColumn.freeze())) {
            String[] freezeArray = rowColumn.freeze().split(BaseConstants.SPLITER);
            if (freezeArray.length == 2) {
                sheet.createFreezePane(Integer.parseInt(freezeArray[0]), Integer.parseInt(freezeArray[1]));
            }
            if (freezeArray.length == 4) {
                sheet.createFreezePane(Integer.parseInt(freezeArray[0]), Integer.parseInt(freezeArray[1]), Integer.parseInt(freezeArray[2]), Integer.parseInt(freezeArray[3]));
            }

        }
    }

    private static Map<String, Head> initHeader(List<Head> header, Field[] fields) {
        for (int i = 0; i < fields.length; i++) {
            fields[i].setAccessible(true);
            ExcelColumn excelColumn = fields[i].getAnnotation(ExcelColumn.class);
            if (excelColumn == null) {
                continue;
            }
            header.add(new Head(fields[i].getName(), excelColumn.name(), excelColumn.order(), excelColumn.datePattern(), excelColumn.hyperLinkTemplate(), excelColumn.hyperLinkPattern()));
        }
        Collections.sort(header);
        return Maps.uniqueIndex(header, Head::getCode);
    }

    public static class Sheet {
        public Sheet(List<Object> contentList, Class clzz) {
            this.contentList = contentList;
            this.clzz = clzz;
        }

        private List<Object> contentList;
        private Class clzz;

        public List<Object> getContentList() {
            return contentList;
        }

        public void setContentList(List<Object> contentList) {
            this.contentList = contentList;
        }

        public Class getClzz() {
            return clzz;
        }

        public void setClzz(Class clzz) {
            this.clzz = clzz;
        }
    }

    public static class Head implements Comparable<Head> {
        /**
         * code
         */
        private String code;
        /**
         * title
         */
        private String title;
        /**
         * order
         */
        private Integer order;
        /**
         * hyper link template
         */
        private String hyperlinkTemplate;
        /**
         * hyper link pattern
         */
        private String[] hyperlinkPattern;
        /**
         * date format
         */
        private String datePattern;

        /**
         * @param title
         * @param template
         * @param patten
         */
        public Head(String code, String title, Integer order, String datePattern, String template, String... patten) {
            this.code = code;
            this.title = title;
            this.order = order;
            this.datePattern = datePattern;
            this.hyperlinkTemplate = template;
            this.hyperlinkPattern = patten;
        }

        public Head(String code, String title, Integer order) {
            this.code = code;
            this.title = title;
            this.order = order;
        }

        public Integer getOrder() {
            return order;
        }

        public void setOrder(Integer order) {
            this.order = order;
        }

        public String getCode() {
            return code;
        }

        public void setCode(String code) {
            this.code = code;
        }

        public String getTitle() {
            return title;
        }

        public String getHyperlinkTemplate() {
            return hyperlinkTemplate;
        }

        public String[] getHyperlinkPattern() {
            return hyperlinkPattern;
        }

        public String getDatePattern() {
            return datePattern;
        }

        public void setDatePattern(String datePattern) {
            this.datePattern = datePattern;
        }

        @Override
        public int compareTo(Head o) {
            return this.getOrder().compareTo(o.getOrder());
        }
    }
}