EasyExcel 自定义单元格式的问题。

2020/01/20 17:17
阅读数 365

最近在做一个关于性能测试管理系统,一个新的需求,需要导出测试报告,直接使用了ali的封装的EasyExcel,但是在复杂头与一些样式,就缺少了自定义的灵活性,在官方demo中没有找到很好的解决方法。

不普及基础了,可以直接看官方文档, 直接上解决代码:

 

调用:

List<ApiStatusVO> apiStatusVOList = new ArrayList<>();
        for(int i=0;i<=15;i++){
            ApiStatusVO apiStatusVO = new ApiStatusVO();
            apiStatusVO.setApiName("Apiname"+i);
            apiStatusVO.setApiRt("RT"+i);
            apiStatusVO.setApiStatus("200");
            apiStatusVO.setId(i);
            apiStatusVO.setTransId(String.valueOf(100+i));
            apiStatusVO.setResponseAssert("true");
            apiStatusVOList.add(apiStatusVO);
        }
    String fileName = URLEncoder.encode(new SimpleDateFormat("yyyy-MM-dd-24h").format(new Date())+".xlsx", "UTF-8");
ExcelKit().parseExcelAndSave(apiStatusVOList,fileName );

 

ExcelKit代码:
package com.kit;

import ……public class ExcelKit {
    public static void parseExcelAndSave(List<ApiStatusVO> apiStatusVOS,String fileName){

        EasyExcel.write(fileName, TestReportVO.class)
                .head(head("我是一个标题头","描述:本次压测针对UXX常用接口进行压力测试;"))
                .registerWriteHandler(new CustomCellWriteHandler())
                .sheet("TestReport")
                .doWrite(data(apiStatusVOS));

    }
    /**     * Api 返回内容  数据不全,先自定义赋值
     * @param statusVOS
     * @return 
     */
    private static List<TestReportVO> data(List<ApiStatusVO> statusVOS){
        List<TestReportVO> dataList = new ArrayList<>();
        statusVOS.forEach(ApiStatusVO->{
            TestReportVO status = new TestReportVO();
//            @ExcelProperty("压测接口名称")
            status.setTestName(ApiStatusVO.getApiName());
//            @ExcelProperty("压测时间")
            status.setTestDate(new Date());
//            @ExcelProperty("压测时常(s)")
            status.setTestDuration("100");
//            @ExcelProperty("平均吞吐量(s)")
            status.setTestHandlingCapacity("101");
//            @ExcelProperty("平均响应时间(s)")
            status.setTestResponseTime(ApiStatusVO.getApiRt());
//            @ExcelProperty("最大响应时间(s)")
            status.setTestMaxResponseTime("102");
//            @ExcelProperty("错误率(%)")
            status.setTestErrorRate("13.18");
//            @ExcelProperty("数据库CPU(User)(%)")
            status.setTestCpuPercentum("15.18");
//            @ExcelProperty("数据库TCP连接数")
            status.setTestTcpLinkNum("105");
//            @ExcelProperty("连接池大小")
            status.setTestLinkPooSize("106");
//            @ExcelProperty("网络流量(上传/Mbps)(Min)")
            status.setTestMinFlow("107");
//            @ExcelProperty("网络流量(上传/Mbps)(Max)")
            status.setTestMaxFlow("108");
            dataList.add(status);
        });
        return dataList;
    }

    /**
     * 自定义头
     * @param headTitle   统一头
     * @param headDescTitle  描述头
     * @return   返回整个头list。 头部相同连续的单元格会自动合并。
     */
    private static List<List<String>> head(String headTitle, String headDescTitle){
        List<List<String>> list = new ArrayList<List<String>>();
        //  压测名称    压测时间     压测时常(s)平均吞吐量(s)    平均响应时间(s)    最大响应时间(s) 错误率(%) 数据库CPU(User)(%)    数据库TCP连接数    连接池大小    网络流量(上传/Mbps) 网络流量(下载/Mbps)
        List<String> head1 = new ArrayList<String>();
        head1.add(headTitle);
        head1.add(headDescTitle);
            head1.add(headDescTitle);
            head1.add(headDescTitle);
            head1.add(headDescTitle);
            head1.add(headDescTitle);
            head1.add(headDescTitle);
        head1.add("压测名称");
        List<String> head2 = new ArrayList<String>();
        head2.add(headTitle);
        head2.add(headDescTitle);
            head2.add(headDescTitle);
            head2.add(headDescTitle);
            head2.add(headDescTitle);
            head2.add(headDescTitle);
            head2.add(headDescTitle);
        head2.add("压测时间");
        List<String> head3 = new ArrayList<String>();
        head3.add(headTitle);
        head3.add(headDescTitle);
            head3.add(headDescTitle);
            head3.add(headDescTitle);
            head3.add(headDescTitle);
            head3.add(headDescTitle);
            head3.add(headDescTitle);
        head3.add("压测时常(s)");
        List<String> head4 = new ArrayList<String>();
        head4.add(headTitle);
        head4.add(headDescTitle);
            head4.add(headDescTitle);
            head4.add(headDescTitle);
            head4.add(headDescTitle);
            head4.add(headDescTitle);
            head4.add(headDescTitle);
        head4.add("平均吞吐量(s)");
        List<String> head5 = new ArrayList<String>();
        head5.add(headTitle);
        head5.add(headDescTitle);
            head5.add(headDescTitle);
            head5.add(headDescTitle);
            head5.add(headDescTitle);
            head5.add(headDescTitle);
            head5.add(headDescTitle);
        head5.add("平均响应时间(s)");
        List<String> head6 = new ArrayList<String>();
        head6.add(headTitle);
        head6.add(headDescTitle);
            head6.add(headDescTitle);
            head6.add(headDescTitle);
            head6.add(headDescTitle);
            head6.add(headDescTitle);
            head6.add(headDescTitle);
        head6.add("最大响应时间(s)");
        List<String> head7 = new ArrayList<String>();
        head7.add(headTitle);
        head7.add(headDescTitle);
            head7.add(headDescTitle);
            head7.add(headDescTitle);
            head7.add(headDescTitle);
            head7.add(headDescTitle);
            head7.add(headDescTitle);
        head7.add("错误率(%)");
        List<String> head8 = new ArrayList<String>();
        head8.add(headTitle);
        head8.add(headDescTitle);
            head8.add(headDescTitle);
            head8.add(headDescTitle);
            head8.add(headDescTitle);
            head8.add(headDescTitle);
            head8.add(headDescTitle);
        head8.add("数据库CPU(User)(%)");
        List<String> head9 = new ArrayList<String>();
        head9.add(headTitle);
        head9.add(headDescTitle);
            head9.add(headDescTitle);
            head9.add(headDescTitle);
            head9.add(headDescTitle);
            head9.add(headDescTitle);
            head9.add(headDescTitle);
        head9.add("数据库TCP连接数");
        List<String> head10 = new ArrayList<String>();
        head10.add(headTitle);
        head10.add("负责人:");
            head10.add("齐冰洋");
            head10.add("");
            head10.add("");
            head10.add("");
            head10.add("");
        head10.add("连接池大小");
        List<String> head11 = new ArrayList<String>();
        head11.add(headTitle);
        head11.add("协助人:");
            head11.add("石星");
            head11.add("张刚强");
            head11.add("秦亚飞");
            head11.add("王京朝");
            head11.add("张凯");
        head11.add("网络流量(上传/Mbps)");
        List<String> head12 = new ArrayList<String>();
        head12.add(headTitle);
        head12.add("");
            head12.add("黄色:瓶颈");
            head12.add("橙色:提醒");
            head12.add("红色:严重");
            head12.add("测试时间");
            head12.add("2020-01-01");   //测试时间
        head12.add("网络流量(下载/Mbps)");
        list.add(head1);
        list.add(head2);
        list.add(head3);
        list.add(head4);
        list.add(head5);
        list.add(head6);
        list.add(head7);
        list.add(head8);
        list.add(head9);
        list.add(head10);
        list.add(head11);
        list.add(head12);
        return list;
    }

}

 

拦截,写入样式、控制列宽:

package com.kit;

import ……public class CustomCellWriteHandler extends AbstractCellStyleStrategy implements CellWriteHandler  {
    private static final Integer width = 18;

    Workbook workbooks;
    @Override
    public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) {
    }
    @Override
    public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
    }
    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean isHead) {
        this.initCellStyle(writeSheetHolder.getSheet().getWorkbook());
        writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), width * 256);
        this.setHeadCellStyle(cell,head,integer);
    }
    @Override
    protected void initCellStyle(Workbook workbook) {
        this.workbooks  = workbook;
    }
    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer integer) {
        //头部Top样式
        if(cell.getRowIndex()==0){
            cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,16));
        }else if((cell.getRowIndex()==1 && cell.getColumnIndex()>8)){
            //负责人 、协助人
            cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,IndexedColors.GREEN));
        }else if(cell.getColumnIndex() == 11 && (cell.getRowIndex() == 2 || cell.getRowIndex() == 3 || cell.getRowIndex() == 4 || cell.getRowIndex() == 5)){
            //警告
            switch (cell.getRowIndex()){
                case 2:
                    cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,IndexedColors.YELLOW));
                    break;
                case 3:
                    cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,IndexedColors.ORANGE));
                    break;
                case 4:
                    cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,IndexedColors.RED));
                    break;
                case 5:
                    cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,IndexedColors.GREEN));
                    break;
            }
        }else if(cell.getRowIndex() == 7 && cell.getColumnIndex() == 0 ){
            //标题底色
            cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,IndexedColors.GREEN));

        }else if(cell.getRowIndex() == 7 && cell.getColumnIndex() != 0 ){
            //标题底色
            cell.setCellStyle(PoiUtils.getColumnTopStyle(workbooks,IndexedColors.ORANGE));
        }else {
            cell.setCellStyle(PoiUtils.getColumnStyle(workbooks));
        }
        if(cell.getRowIndex() > 7){
            cell.setCellStyle(PoiUtils.getColumnStyle(workbooks));
        }
    }
    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer integer) {
    }

}

 

 



样式设置: 此处代码参考来源:https://blog.csdn.net/qq_15081901/article/details/90202723 (POI封装工具easyexcel导出EXCEL表样式设置)

 

package com.kit;

import……public class PoiUtils {

    /**
     * 首头单元格
     * @param workbook
     * @return
     */

    public static CellStyle getColumnTopStyle(Workbook workbook,int FontSize) {
        if(FontSize == 0){
            FontSize=12;
        }

        // 设置字体
        Font font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) FontSize);
        //字体加粗
        font.setBold(true);
        //设置字体名字
        font.setFontName("宋体");
        //设置样式;
        CellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor((short) 0);
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor((short) 0);
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor((short) 0);
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor((short) 0);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }

    /**
     * 头部自己定义单元格
     * @param workbook
     * @return
     */
    public static CellStyle getColumnTopStyle(Workbook workbook, IndexedColors indexedColors) {
        // 设置字体
        Font font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 12);
        //字体加粗
        font.setBold(true);
        //设置字体名字
        font.setFontName("宋体");
        //设置样式;
        CellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor((short) 0);
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor((short) 0);
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor((short) 0);
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor((short) 0);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        /**
         * 背景色
         */

        if(indexedColors != null){
            style.setFillForegroundColor(indexedColors.getIndex());// 设置背景色
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        }

        return style;
    }

    /*
     * 字段样式
     */
    public static CellStyle getColumnStyle(Workbook workbook) {
        // 设置字体
        Font font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 10);
        //字体加粗
//        font.setBold(true);
        //设置字体名字
        font.setFontName("宋体");
        //设置样式;
        CellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(BorderStyle.THIN);
        //设置底边框颜色;
        style.setBottomBorderColor((short) 0);
        //设置左边框;
        style.setBorderLeft(BorderStyle.THIN);
        //设置左边框颜色;
        style.setLeftBorderColor((short) 0);
        //设置右边框;
        style.setBorderRight(BorderStyle.THIN);
        //设置右边框颜色;
        style.setRightBorderColor((short) 0);
        //设置顶边框;
        style.setBorderTop(BorderStyle.THIN);
        //设置顶边框颜色;
        style.setTopBorderColor((short) 0);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(true);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;

    }


}
TestReportVO 
import ……

@Data
public class TestReportVO {

    @ExcelProperty("压测名称")
    private String testName;
    @ExcelProperty("压测时间")
    private Date testDate;
    @ExcelProperty("压测时常(s)")
    private String testDuration;
    @ExcelProperty("平均吞吐量(s)")
    private String testHandlingCapacity;
    @ExcelProperty("平均响应时间(s)")
    private String testResponseTime ;
    @ExcelProperty("最大响应时间(s)")
    private String testMaxResponseTime;
    @ExcelProperty(错误率(%))
    private String testErrorRate ;
    @ExcelProperty("数据库CPU(User)(%))
    private String testCpuPercentum ;
    @ExcelProperty("数据库TCP连接数")
    private String testTcpLinkNum;
    @ExcelProperty("连接池大小")
    private String testLinkPooSize;
    @ExcelProperty("网络流量(上传/Mbps)")
    private String testMinFlow ;
    @ExcelProperty("网络流量(上传/Mbps)")
    private String testMaxFlow;


}

导出效果:

 

 

 

有问题可以联系 邮箱 253049953@qq.com

TestReportVO
展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部