记一次java实现excel导出

于 2021-02-21 12:58:28 发布
阅读量1.2k 收藏 10
点赞数 5
分类专栏： poi java 文章标签： java excel poi
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_43818323/article/details/113916545
版权

新年过完了哦,小子我又来了,大家新年过的还快乐吗?反正我是只感觉到了"快,",没有感觉到"乐".
在这里插入图片描述

2021年的第一天,就接到新需求了.对,就是那个谁,来来,给个新需求你,甲方爸爸提的,还热乎着的,看到这个列表没,就导出它的数据.

我一看原型图,这展示列表和Excel导出的数据也不一样啊,我还以为我这20几岁的小伙"老眼昏花"了呢.心理虽然一万匹马奔腾而过,但需求还是要做啊,谁让咱是打工人呢,拿人钱财为人消灾呗,干就完事儿了.

在这里插入图片描述

展示列表

在这里插入图片描述

Excel导出表头

在这里插入图片描述

吐槽归吐槽,工作还是要做的哦,根据我多年,咳咳咳,不,一年的工作经验来看,excel导出基本上使用三种工具:

  1. POI: Apache POI是Apache软件基金会的开放源码函式库，POI提供API给Java程序对Microsoft Office格式档案读和写的功能。Apache 基金会的,听起来就很屌有没有,但这是最基本的导出工具,用起来很累,真的很累哟,小子我最后确实也是用这个导出的,没想到吧.
  2. EasyPOI: 国人封装的Excel导出工具.
  3. EasyExcel: 阿里大佬封装的导出工具.

EasyPOI

写个demo试试水

官网地址: http://doc.wupaas.com/docs/easypoi/easypoi-1c0u4mo8p4ro8

导入依赖

<!-- https://mvnrepository.com/artifact/cn.afterturn/easypoi-base -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>3.0.1</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/cn.afterturn/easypoi-annotation -->
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>3.0.1</version>
    </dependency>

是不是很疑惑我为什么要用这么低的版本,往后看你就知道了,谁还不想跟着时代走啊,可人在江湖,身不由己…

实体类创建,lombok大家应该都用过吧,就不说了哦

@ExcelTarget("courseEntity")
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class CourseEntity implements java.io.Serializable{
    /** 主键 */
    private String        id;
    /** 课程名称 */
    @Excel(name = "课程名称", orderNum = "1", width = 25,needMerge = true)
    private String        name;
    /** 老师主键 */
    @ExcelEntity(id = "absent")
    private TeacherEntity mathTeacher;
    @ExcelCollection(name = "学生", orderNum = "4")
    private List<StudentEntity> students;
}

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class StudentEntity implements java.io.Serializable{
    /**
     * id
     */
    private String        id;
    /**
     * 学生姓名
     */
    @Excel(name = "学生姓名", height = 20, width = 30, isImportField = "true_st")
    private String        name;
    /**
     * 学生性别
     */
    @Excel(name = "学生性别", replace = { "男_1", "女_2" }, suffix = "生", isImportField = "true_st")
    private int           sex;

    @Excel(name = "出生日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd", isImportField = "true_st", width = 20)
    private Date birthday;

    @Excel(name = "进校日期", databaseFormat = "yyyyMMddHHmmss", format = "yyyy-MM-dd")
    private Date registrationDate;
}

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
@ExcelTarget("teacherEntity")
public class TeacherEntity implements Serializable {
    private String id;
    /** name */
    @Excel(name = "主讲老师_major,代课老师_absent", orderNum = "1", isImportField = "true_major,true_absent",needMerge = true)
    private String name;
}

导出测试代码

public class Test {
    public static void main(String[] args) throws IOException {
        List<CourseEntity> list = new ArrayList<>();
        for(int i=0;i<10;i++){
            TeacherEntity teacher = TeacherEntity.builder()
                    .id(i + "")
                    .name("张三" + i)
                    .build();
            List<StudentEntity> listStu = new ArrayList<>();
            for(int j =0 ;j<3;j++){
                StudentEntity build = StudentEntity.builder()
                        .id("" + i + j)
                        .sex(0)
                        .name("zhangsan" + i + j)
                        .birthday(new Date())
                        .registrationDate(new Date())
                        .build();
                listStu.add(build);
            }
            CourseEntity build = CourseEntity.builder()
                    //.id(i + "")
                    .name("java从入门到放弃" + i)
                    //.mathTeacher(teacher)
                    .students(listStu)
                    .build();
            list.add(build);
        }
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("2412312", "测试", "测试"),
                CourseEntity.class, list);
        FileOutputStream fos = new FileOutputStream("D:/course.xls");
        workbook.write(fos);
    }
}

导出效果
在这里插入图片描述

看起来是不是还不错啊,一对多也能自动合并单元格.本以为就可以借鉴着实现自己的需求,可是,还是太年轻了…

在这里插入图片描述
等我真正用到项目中的时候,就有两个大问题

  1. 项目中已有的poi版本太低,是3.1.0的,这就是我为什么easypoi依赖版本也用的这么低了.
  2. 低版本@Excel注解没有groupname属性,我…喷出一口老血

EasyExcel

EasyExcel真的好用,真香,阿里大佬就是不一样…

文档地址:https://www.yuque.com/easyexcel

导入依赖

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>2.1.6</version>
    </dependency>

实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class ExcelDto implements Serializable {

    @ExcelProperty(value = "坐席人员", index = 0)
    private String signerName;
    @ExcelProperty(value = "所属机构", index = 1)
    private String orgName;
    @ExcelProperty(value = "合作机构", index = 2)
    private String coopOrg;
    @ExcelProperty(value = "所属事业部", index = 3)
    private String bizUnit;
    @ExcelProperty(value = "视频申请次数", index = 4)
    private int applyCount;
    @ExcelProperty(value = "首次接通数量", index = 5)
    private int connCount;
    @ExcelProperty(value = "首次接通率", index = 6)
    private double connRate;
    @ExcelProperty(value = "平均面谈时长", index = 7)
    private double avgTime;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class RowRangeDto {
    private int start;
    private int end;
}

单元格合并策略

public class BizMergeStrategy extends AbstractMergeStrategy {
    private Map<String, List<RowRangeDto>> strategyMap;
    private Sheet sheet;

    public BizMergeStrategy(Map<String, List<RowRangeDto>> strategyMap) {
        this.strategyMap = strategyMap;
    }

    @Override
    protected void merge(org.apache.poi.ss.usermodel.Sheet sheet, Cell cell, Head head, Integer integer) {
        this.sheet = sheet;
        if (cell.getRowIndex() == 1 && cell.getColumnIndex() == 0) {
            /**
             * 保证每个cell被合并一次，如果不加上面的判断，因为是一个cell一个cell操作的，
             * 例如合并A2:A3,当cell为A2时，合并A2,A3，但是当cell为A3时，又是合并A2,A3，
             * 但此时A2,A3已经是合并的单元格了
             */
            for (Map.Entry<String, List<RowRangeDto>> entry : strategyMap.entrySet()) {
                Integer columnIndex = Integer.valueOf(entry.getKey());
                entry.getValue().forEach(rowRange -> {
                    //添加一个合并请求
                    sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),
                            rowRange.getEnd(), columnIndex, columnIndex));
                });
            }
        }
    }
}

@Test
public void testBizStrategy(){
    List<ExcelDto> excelDtoList = getExcelDtoList();
    //合并策略map
    Map<String, List<RowRangeDto>> strategyMap = ExcelUtils.addMerStrategy(excelDtoList);
    EasyExcel.write(ExcelUtils.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx", ExcelDto.class)
        //注册合并策略
        .registerWriteHandler(new BizMergeStrategy(strategyMap))
        .sheet("Sheet1").doWrite(excelDtoList);
}
/**
     * 创建测试数据
     * @return
     */
private List<ExcelDto> getExcelDtoList() {
    List<ExcelDto> list = new ArrayList<>();
    list.add(new ExcelDto("admin", "朝阳支行", "蔚蓝", "-", 2, 2, 1, 12.5));
    list.add(new ExcelDto("admin", "朝阳支行", "长安新生", "北京", 1, 0, 0, 0));
    list.add(new ExcelDto("admin", "朝阳支行", "长安新生", "山西", 1, 0, 0, 0));
    list.add(new ExcelDto("张三", "丰台支行", "长安新生", "北京", 1, 0, 0, 0));
    list.add(new ExcelDto("张三", "朝阳支行", "蔚蓝", "-", 1, 0, 0, 0));
    list.add(new ExcelDto("张三", "丰台支行", "长安新生", "北京", 1, 0, 0, 0));
    list.add(new ExcelDto("张三", "丰台支行", "长安新生", "山西", 1, 0, 0, 0));
    return list;
}

效果图如下:

在这里插入图片描述
用到项目中,还是因为poi版本的问题,不能用啊,铁子们…

都到这儿,忙活了这么久,竹篮打水一场空啊,难道这就是生活吗?

在这里插入图片描述
男儿有泪不轻弹,只是未到伤心处啊.为了让老板买房换车,我加班加点也要把需求干出来.

花里胡哨的不行,咱就来最实在的.

POI

依赖就随便导了

private void exportDeal(List<Map<String, Object>> dealList) {
        //1.创建一个workbook,对应一个excel文件
        HSSFWorkbook wb = new HSSFWorkbook();

        //2.在workbook中添加一个sheet,对应Excel中的sheet
        HSSFSheet sheet = wb.createSheet("交易列表导出");

        //设置每一列的列宽
        sheet.setColumnWidth(0,256*15);
        sheet.setColumnWidth(1,256*25);
        sheet.setColumnWidth(2,256*25);
        sheet.setColumnWidth(3,256*17);
        sheet.setColumnWidth(4,256*15);
        sheet.setColumnWidth(5,256*15);
        sheet.setColumnWidth(6,256*15);
        sheet.setColumnWidth(7,256*15);
        sheet.setColumnWidth(8,256*15);
        sheet.setColumnWidth(9,256*15);
        sheet.setColumnWidth(10,256*15);
        sheet.setColumnWidth(11,256*15);
        sheet.setColumnWidth(12,256*15);
        sheet.setColumnWidth(13,256*15);
        sheet.setColumnWidth(14,256*15);
        sheet.setColumnWidth(15,256*15);
        sheet.setColumnWidth(16,256*15);
        sheet.setColumnWidth(17,256*15);
        sheet.setColumnWidth(18,256*17);
        sheet.setColumnWidth(19,256*15);
        sheet.setColumnWidth(20,256*15);
        sheet.setColumnWidth(21,256*15);
        sheet.setColumnWidth(22,256*15);
        sheet.setColumnWidth(23,256*15);
        sheet.setColumnWidth(24,256*20);
        sheet.setColumnWidth(25,256*15);
        sheet.setColumnWidth(26,256*15);
        sheet.setColumnWidth(27,256*15);
        sheet.setColumnWidth(28,256*15);
        sheet.setColumnWidth(29,256*15);

        //3.设置样式以及字体样式
        HSSFCellStyle titleStyle = ExcelUtils.createTitleCellStyle(wb);
        HSSFCellStyle headerStyle = ExcelUtils.createHeadCellStyle(wb);
        HSSFCellStyle contentStyle = ExcelUtils.createContentCellStyle(wb);

        //4.创建标题，合并标题单元格
        //行号
        int rowNum = 0;

        //创建第一行，索引从0开始(标题行)
        HSSFRow row0 = sheet.createRow(rowNum++);
        row0.setHeight((short) 800);// 设置行高
        String title = "交易列表";
        HSSFCell c00 = row0.createCell(0);
        c00.setCellValue(title);
        c00.setCellStyle(titleStyle);
        // 合并单元格，参数依次为起始行，结束行，起始列，结束列 （索引0开始）
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 29));//标题合并单元格操作，总列数为16

        //第二行
        HSSFRow row1 = sheet.createRow(rowNum++);
        row1.setHeight((short)500);
        //String[] row_first = {"客户",month+"月发车",srcyear+"年12月31日前货款回收(期前欠款)",year+"年货款回收","截止"+year+"年"+month+"月"+day+"日业务明细","","","","","","","","","",month+"月份新增欠款",month+"月收款合计"};
        String[] row_first = {"交易归属人","成交日期","审批日期","交易编号","楼盘名称","具体地址","客户来源","面积","客户名称","成交价","交易状态","业绩情况","","","","佣金情况","","","业绩分配情况","","","","","","","","","","现金奖","管理津贴"};
        for (int i = 0; i < row_first.length; i++) {
            HSSFCell tempCell = row1.createCell(i);
            tempCell.setCellValue(row_first[i]);
            tempCell.setCellStyle(headerStyle);
        }
        //合并单元格
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));//交易归属人
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));//成交日期
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 2, 2));//审批日期
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 3, 3));//交易编号
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 4, 4));//楼盘名称
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 5, 5));//具体地址
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 6, 6));//客户来源
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 7, 7));//面积
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 8, 8));//客户名称
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 9, 9));//成交价
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 10, 10));//交易状态
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 11, 14));//业绩情况
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 15, 17));//佣金情况
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 18, 27));//业绩分配情况
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 28, 28));//现金奖
        sheet.addMergedRegion(new CellRangeAddress(1, 2, 29, 29));//管理津贴

        //第三行
        HSSFRow row2 = sheet.createRow(rowNum++);
        row2.setHeight((short)500);
        String[] row_second = {"","","","","","","","","","","","总佣金","税费","平台费","公司应收佣金","已收佣金","欠收佣金","最新收款时间","总经理归属部门","总经理","区域经理","营业主管","主管","业绩分成人","职务","可分成业绩","分成后比例","分成后业绩","",""};
        for (int i = 0; i < row_second.length; i++) {
            HSSFCell tempCell = row2.createCell(i);
            tempCell.setCellValue(row_second[i]);
            tempCell.setCellStyle(headerStyle);
        }
        //合并行标志
        int count = 0;
        Boolean isMerge = true;
        //遍历数据
        for(int i=0;i<dealList.size();i++){
            //第3+i行
            HSSFRow tempRow = sheet.createRow(rowNum++);
            //设置行高
            tempRow.setHeight((short)500);
            //获取需要合并的坐标
            //判断list中重合的数据的条数
            String dealId = dealList.get(i).get("dealId").toString();
            //数据中存dealId相同行的总数
            //当待合并行遍历完再次统计
            if(count==0){
                count = dealList.stream().filter(map -> map.get("dealId").toString().equals(dealId)).collect(Collectors.toList()).size();
                isMerge = false;
            }
            if(count>1 && !isMerge){
                //保证重复行只合并一次
                //合并行的坐标计算
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 0, 0));//交易归属人
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 1, 1));//成交日期
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 2, 2));//审批日期
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 3, 3));//交易编号
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 4, 4));//楼盘名称
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 5, 5));//具体地址
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 6, 6));//客户来源
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 7, 7));//面积
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 8, 8));//客户名称
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 9, 9));//成交价
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 10, 10));//交易状态
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 10, 10));//交易状态
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 11, 11));//总佣金
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 12, 12));//税费
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 13, 13));//平台费
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 14, 14));//公司应收佣金
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 15, 15));//已收佣金
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 16, 16));//欠收佣金
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 17, 17));//最新收款时间
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 28, 28));//现金奖
                sheet.addMergedRegion(new CellRangeAddress(rowNum-1, rowNum+count-1-1, 29, 29));//管理津贴
                isMerge = true;
            }
            HSSFCell tempCell0 = tempRow.createCell(0);
            tempCell0.setCellStyle(contentStyle);
            if(dealList.get(i).get("salerName")!=null){
                //交易归属人
                tempCell0.setCellValue(dealList.get(i).get("salerName").toString());
            }
            HSSFCell tempCell1 = tempRow.createCell(1);
            tempCell1.setCellStyle(contentStyle);
            if(dealList.get(i).get("dealTime")!=null){
                //成交日期
                tempCell1.setCellValue(dealList.get(i).get("dealTime").toString());
            }
            HSSFCell tempCell2 = tempRow.createCell(2);
            tempCell2.setCellStyle(contentStyle);
            if(dealList.get(i).get("approveTime")!=null){
                //审批日期
                tempCell2.setCellValue(dealList.get(i).get("approveTime").toString());
            }
            HSSFCell tempCell3 = tempRow.createCell(3);
            tempCell3.setCellStyle(contentStyle);
            if(dealList.get(i).get("dealCode")!=null){
                //交易编号
                tempCell3.setCellValue(dealList.get(i).get("dealCode").toString());
            }
            HSSFCell tempCell4 = tempRow.createCell(4);
            tempCell4.setCellStyle(contentStyle);
            if(dealList.get(i).get("projectName")!=null){
                //楼盘名称
                tempCell4.setCellValue(dealList.get(i).get("projectName").toString());
            }
            HSSFCell tempCell5 = tempRow.createCell(5);
            tempCell5.setCellStyle(contentStyle);
            if(dealList.get(i).get("address")!=null){
                //具体地址
                tempCell5.setCellValue(dealList.get(i).get("address").toString());
            }
            HSSFCell tempCell6 = tempRow.createCell(6);
            tempCell6.setCellStyle(contentStyle);
            if(dealList.get(i).get("mymttj")!=null){
                //客户来源
                tempCell6.setCellValue(dealList.get(i).get("mymttj").toString());
            }
            HSSFCell tempCell7 = tempRow.createCell(7);
            tempCell7.setCellStyle(contentStyle);
            if(dealList.get(i).get("buildArea")!=null){
                //面积
                tempCell7.setCellValue(dealList.get(i).get("buildArea").toString());
            }
            HSSFCell tempCell8 = tempRow.createCell(8);
            tempCell8.setCellStyle(contentStyle);
            if(dealList.get(i).get("username")!=null){
                //客户名称
                tempCell8.setCellValue(dealList.get(i).get("username").toString());
            }
            HSSFCell tempCell9 = tempRow.createCell(9);
            tempCell9.setCellStyle(contentStyle);
            if(dealList.get(i).get("dealPrice")!=null){
                //成交价
                tempCell9.setCellValue(dealList.get(i).get("dealPrice").toString());
            }
            HSSFCell tempCell10 = tempRow.createCell(10);
            tempCell10.setCellStyle(contentStyle);
            if(dealList.get(i).get("dealStatus")!=null){
                //交易状态
                tempCell10.setCellValue(dealList.get(i).get("dealStatus").toString());
            }
            HSSFCell tempCell11 = tempRow.createCell(11);
            tempCell11.setCellStyle(contentStyle);
            if(dealList.get(i).get("commissionPrice")!=null){
                //总佣金
                tempCell11.setCellValue(dealList.get(i).get("commissionPrice").toString());
            }
            HSSFCell tempCell12 = tempRow.createCell(12);
            tempCell12.setCellStyle(contentStyle);
            if(dealList.get(i).get("taxesPrice")!=null){
                //税费
                tempCell12.setCellValue(dealList.get(i).get("taxesPrice").toString());
            }
            HSSFCell tempCell13 = tempRow.createCell(13);
            tempCell13.setCellStyle(contentStyle);
            if(dealList.get(i).get("platformPrice")!=null){
                //平台费
                tempCell13.setCellValue(dealList.get(i).get("platformPrice").toString());
            }
            HSSFCell tempCell14 = tempRow.createCell(14);
            tempCell14.setCellStyle(contentStyle);
            if(dealList.get(i).get("totalCommission")!=null){
                //公司应收佣金
                tempCell14.setCellValue(dealList.get(i).get("totalCommission").toString());
            }
            HSSFCell tempCell15 = tempRow.createCell(15);
            tempCell15.setCellStyle(contentStyle);
            if(dealList.get(i).get("receiptPrice")!=null){
                //已收佣金
                tempCell15.setCellValue(dealList.get(i).get("receiptPrice").toString());
            }
            HSSFCell tempCell16 = tempRow.createCell(16);
            tempCell16.setCellStyle(contentStyle);
            if(dealList.get(i).get("noWithdrawalComm")!=null){
                //欠收佣金
                tempCell16.setCellValue(dealList.get(i).get("noWithdrawalComm").toString());
            }
            HSSFCell tempCell17 = tempRow.createCell(17);
            tempCell17.setCellStyle(contentStyle);
            if(dealList.get(i).get("receiptTime")!=null){
                //最新收款时间
                tempCell17.setCellValue(dealList.get(i).get("receiptTime").toString());
            }
            HSSFCell tempCell18 = tempRow.createCell(18);
            tempCell18.setCellStyle(contentStyle);
            if(dealList.get(i).get("generalManagerDept")!=null){
                //总经理归属部门
                tempCell18.setCellValue(dealList.get(i).get("generalManagerDept").toString());
            }
            HSSFCell tempCell19 = tempRow.createCell(19);
            tempCell19.setCellStyle(contentStyle);
            if(dealList.get(i).get("generalManager")!=null){
                //总经理
                tempCell19.setCellValue(dealList.get(i).get("generalManager").toString());
            }
            HSSFCell tempCell20 = tempRow.createCell(20);
            tempCell20.setCellStyle(contentStyle);
            if(dealList.get(i).get("salesManager")!=null){
                //区域经理
                tempCell20.setCellValue(dealList.get(i).get("salesManager").toString());
            }
            HSSFCell tempCell21 = tempRow.createCell(21);
            tempCell21.setCellStyle(contentStyle);
            if(dealList.get(i).get("salesManager")!=null){
                //营业经理
                tempCell21.setCellValue(dealList.get(i).get("salesManager").toString());
            }
            HSSFCell tempCell22 = tempRow.createCell(22);
            tempCell22.setCellStyle(contentStyle);
            if(dealList.get(i).get("executiveDirector")!=null){
                //主管
                tempCell22.setCellValue(dealList.get(i).get("executiveDirector").toString());
            }
            HSSFCell tempCell23 = tempRow.createCell(23);
            tempCell23.setCellStyle(contentStyle);
            if(dealList.get(i).get("name")!=null){
                //业绩分成人
                tempCell23.setCellValue(dealList.get(i).get("name").toString());
            }
            HSSFCell tempCell24 = tempRow.createCell(24);
            tempCell24.setCellStyle(contentStyle);
            if(dealList.get(i).get("postName")!=null){
                //职务
                tempCell24.setCellValue(dealList.get(i).get("postName").toString());
            }
            HSSFCell tempCell25 = tempRow.createCell(25);
            tempCell25.setCellStyle(contentStyle);
            if(dealList.get(i).get("salerPercentage")!=null){
                //可分成业绩
                tempCell25.setCellValue(dealList.get(i).get("salerPercentage").toString());
            }
            HSSFCell tempCell26 = tempRow.createCell(26);
            tempCell26.setCellStyle(contentStyle);
            if(dealList.get(i).get("shareProportion")!=null){
                //分成比例
                tempCell26.setCellValue(dealList.get(i).get("shareProportion").toString());
            }
            HSSFCell tempCell27 = tempRow.createCell(27);
            tempCell27.setCellStyle(contentStyle);
            if(dealList.get(i).get("personalCommission")!=null){
                //分成后业绩
                tempCell27.setCellValue(dealList.get(i).get("personalCommission").toString());
            }
            HSSFCell tempCell28 = tempRow.createCell(28);
            tempCell28.setCellStyle(contentStyle);
            if(dealList.get(i).get("cashPrice")!=null){
                //现金奖
                tempCell28.setCellValue(dealList.get(i).get("cashPrice").toString());
            }
            HSSFCell tempCell29 = tempRow.createCell(29);
            tempCell29.setCellStyle(contentStyle);
            if(dealList.get(i).get("allowancesPrice")!=null){
                //管理津贴
                tempCell29.setCellValue(dealList.get(i).get("allowancesPrice").toString());
            }
            count--;
        }
        String fileName = "d://交易列表.xls";
        OutputStream out = null;
        try {
            out = new FileOutputStream(fileName);
            if(null != wb && null != out){
                wb.write(out);
                out.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }

    }

public class ExcelUtils {


    /**
     * 创建标题样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        //水平居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        //背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
        HSSFFont headerFont1 = (HSSFFont) wb.createFont();
        //字体类型
        headerFont1.setFontName("黑体");
        //字体大小
        headerFont1.setFontHeightInPoints((short)15);
        cellStyle.setFont(headerFont1);
        return cellStyle;
    }

    /**
     * 创建表头样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createHeadCellStyle(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //设置自动换行
        cellStyle.setWrapText(true);
        //设置背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //水平居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        // 设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

        //创建字体样式
        HSSFFont headerFont = (HSSFFont)wb.createFont();
        //字体类型
        headerFont.setFontName("黑体");
        //字体大小
        headerFont.setFontHeightInPoints((short)12);
        //为标题样式添加字体样式
        cellStyle.setFont(headerFont);
        return cellStyle;
    }

    /**
     *  设置表格内容样式
     * @param wb
     * @return
     */
    public static HSSFCellStyle createContentCellStyle(HSSFWorkbook wb){
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //水平居中
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //垂直居中
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        // 设置边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置字体
        HSSFFont font = (HSSFFont)wb.createFont();
        font.setColor((short)8);
        font.setFontHeightInPoints((short)12);
        return cellStyle;
    }
}

效果图如下:
在这里插入图片描述
看,是不是很简单粗暴.

怎么感觉头有点冷,好像没戴帽子,摸了摸日渐稀少的头发,又看了看导出的excel的表格,甲方爸爸,我又行了.

小丑竟是我自己…

参考文章如下:
https://blog.youkuaiyun.com/Aeve_imp/article/details/104910080
https://blog.youkuaiyun.com/weixin_45768223/article/details/103935978

评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值