[开发工具]easyexcel导出

easyexcel导出

一、依赖

        <!--阿里巴巴EasyExcel依赖-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
        </dependency>

参考文档：语雀easyexcel文档

二、导出

普通导出

/**
     * 导出
     *
     * @param response 响应流
     * @param fileName 文件名称
     * @param list     数据
     * @param clazz    class文件
     */
    public static void exportExcel(HttpServletResponse response, String fileName, String sheetName, List<?> list, Class<?> clazz) {
        if (CollectionUtils.isEmpty(list)) {
            throw new RuntimeException();
        }
        if (StringUtils.isEmpty(fileName)) {
            fileName = new Date().toString();
        }

        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            try {
                fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

            EasyExcel.write(response.getOutputStream(), clazz)
                    // 导出文件名
                    .autoCloseStream(Boolean.TRUE).sheet(sheetName)
                    .doWrite(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

实体类

@HeadRowHeight
@HeadStyle(horizontalAlignment = CENTER)
public class ExportAssetModel {

    @ColumnWidth(30)
    @HeadStyle(fillForegroundColor = 40)
    @ContentStyle(wrapped = true)
    @ExcelProperty({"基础属性", "制造商\n(可输入数字0-9字母a-zA-Z,下划线_-以及汉字,最大长字符64位)\n(必填)"})
    private String manufacturer;

    @ColumnWidth(30)
    @HeadStyle(fillForegroundColor = 40)
    @ContentStyle(wrapped = true)
    @ExcelProperty({"基础属性", "型号\n(请输入1-64个字符，可输入中文，数字0-9，字母a-zA-Z，空格，以及字符+._-（）()[]:,/)\n(必填)"})
    private String model;

    @ColumnWidth(30)
    @HeadStyle(fillForegroundColor = 40)
    @ContentStyle(wrapped = true)
    @ExcelProperty({"基础属性", "设备类型\n(请输入1-64个字符，只能输入中文,英文，数字，空格和特殊字符#@_.*-/[]()～′^{}|:;、,‘’)\n(必填)"})
    private String deviceTypeStr;

    @ColumnWidth(30)
    @HeadStyle(fillForegroundColor = 40)
    @ContentStyle(wrapped = true)
    @ExcelProperty({"基础属性", "状态\n(只能输入使用中、未使用)"})
    private String status;

    @ColumnWidth(30)
    @HeadStyle(fillForegroundColor = 40)
    @ContentStyle(wrapped = true)
    @ExcelProperty({"基础属性", "备注\n(请输入1-64个字符，只能输入中文,英文，数字，空格和特殊字符#@_.*-/[]()～′^{}|:;、,‘’)"})
    private String remark;

    @ColumnWidth(30)
    @HeadStyle(fillForegroundColor = 53)
    @ContentStyle(wrapped = true)
    @ExcelProperty({"扩展属性", "高度(U)\n(请输入1-100之间的整数)\n(IT设备、通信设备必填)"})
    private String height;

    @ColumnWidth(30)
    @HeadStyle(fillForegroundColor = 53)
    @ContentStyle(wrapped = true)
    @ExcelProperty({"扩展属性", "重量(Kg)\n(请输入0-9999.99之间的数)"})
    private String weight;

    @ColumnWidth(30)
    @HeadStyle(fillForegroundColor = 53)
    @ContentStyle(wrapped = true)
    @ExcelProperty({"扩展属性", "额定功率(W)\n(请输入0-9999999.99之间的数)\n(IT设备、通信设备必填)"})
    private String ratedPower;

    @ColumnWidth(30)
    @HeadStyle(fillForegroundColor = 53)
    @ContentStyle(wrapped = true)
    @ExcelProperty({"扩展属性", "配电端口数\n(请输入0-2048之间的整数)"})
    private String distributionPort;

    @ColumnWidth(30)
    @HeadStyle(fillForegroundColor = 53)
    @ContentStyle(wrapped = true)
    @ExcelProperty({"扩展属性", "网络端口数\n(请输入0-2048之间的整数)"})
    private String networkPort;
}

导出效果

自定义表头导出

/**
     * 自定义表头导出
     *
     * @param response
     * @param fileName
     * @param sheetName
     * @param list
     * @param head
     */
    public static void customHeadExportExcel(HttpServletResponse response, String fileName, String sheetName, List<List<Object>> list, List<List<String>> head) {
        if (CollectionUtils.isEmpty(list)) {
            throw new RuntimeException();
        }

        if (CollectionUtils.isEmpty(head)) {
            throw new RuntimeException();
        }

        if (StringUtils.isEmpty(fileName)) {
            fileName = new Date().toString();
        }

        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            try {
                fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

            EasyExcel.write(response.getOutputStream())
                    .head(head)
                    //默认策略
//                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerWriteHandler(myHorizontalCellStyleStrategy())
                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25))//简单的列宽策略，列宽20
//                    .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)25,(short)25))//简单的行高策略
                    // 导出文件名
                    .autoCloseStream(Boolean.TRUE)
                    .sheet(sheetName)
                    .doWrite(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

	private static HorizontalCellStyleStrategy myHorizontalCellStyleStrategy() {
        //1 表头样式策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //表头前景设置淡蓝色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setBold(true);
        headWriteFont.setFontName("宋体");
        headWriteFont.setFontHeightInPoints((short) 12);
        headWriteCellStyle.setWriteFont(headWriteFont);

        //内容样式  多个样式则隔行换色
        List<WriteCellStyle> listCntWritCellSty = new ArrayList<>();

        //2 内容样式策略  样式一
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        //内容字体大小
        contentWriteFont.setFontName("宋体");
        contentWriteFont.setFontHeightInPoints((short) 11);
        contentWriteCellStyle.setWriteFont(contentWriteFont);
        //设置自动换行
        contentWriteCellStyle.setWrapped(true);
        //设置垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 头默认了 FillPatternType所以可以不指定。
        contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        //设置背景黄色
        contentWriteCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        //设置水平靠左
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        //设置边框样式
        setBorderStyle(contentWriteCellStyle);
        //内容风格可以定义多个。
        listCntWritCellSty.add(contentWriteCellStyle);

        //2 内容样式策略  样式二
        WriteCellStyle contentWriteCellStyle2 = new WriteCellStyle();
        // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色。
        // 头默认了 FillPatternType所以可以不指定。
        contentWriteCellStyle2.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
        // 背景绿色
        contentWriteCellStyle2.setFillForegroundColor(IndexedColors.GREEN.getIndex());
        //设置垂直居中
        contentWriteCellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
        contentWriteCellStyle2.setHorizontalAlignment(HorizontalAlignment.LEFT);
        //设置边框样式
        setBorderStyle(contentWriteCellStyle2);
        listCntWritCellSty.add(contentWriteCellStyle2);
        // 水平单元格风格综合策略(表头 + 内容)
        // return  new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        return new HorizontalCellStyleStrategy(headWriteCellStyle, listCntWritCellSty);
    }

    /**
     * 设置边框样式
     *
     * @param contentWriteCellStyle
     */
    private static void setBorderStyle(WriteCellStyle contentWriteCellStyle) {
        //设置边框样式
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        // contentWriteCellStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex()); //颜色
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
    }

多sheet导出

public static void exportMoreExcel(HttpServletResponse response, String fileName,
                                       String sheetNameOne,String sheetNameTwo,String sheetNameThree,
                                       List<List<Object>> list, List<List<String>> head,
                                       List<?> listTwo,Class<?> clazzTwo, List<?> listThree,Class<?> clazzThree) {
        if (CollectionUtils.isEmpty(list)) {
            throw new RuntimeException();
        }

        if (CollectionUtils.isEmpty(head)) {
            throw new RuntimeException();
        }

        if (StringUtils.isEmpty(fileName)) {
            fileName = new Date().toString();
        }

        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            try {
                fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

            ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(1, sheetNameOne)
                    .head(head)
                    .registerWriteHandler(myHorizontalCellStyleStrategy())
                    .registerWriteHandler(new SimpleColumnWidthStyleStrategy(25))//简单的列宽策略，列宽20
                    .build();
            excelWriter.write(list, writeSheet);

            writeSheet = EasyExcel.writerSheet(2, sheetNameTwo).head(clazzTwo).build();
            excelWriter.write(listTwo, writeSheet);

            writeSheet = EasyExcel.writerSheet(3, sheetNameThree).head(clazzThree).build();
            excelWriter.write(listThree, writeSheet);
            // 千万别忘记finish 会帮忙关闭流
            excelWriter.finish();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

开发者涨薪指南

48位大咖的思考法则、工作方式、逻辑体系