hutool工具 导出excel 自定义样式------excel压缩jar

默认样式导出

//excel
public void test() {
//随机值
        String fileUuid = UuidKit.getUUIDNoMinus();
        String path = (PathKit.getWebRootPath() + "/" + fileUuid + "/workModel/").replace("\\", "/");
        //头部
        String[] headers = {"序号", "编码", "创建时间", "工种名称", "是否特殊", "特殊描述", "状态"};
        Collection coll = new ArrayList();
        List<String> row = CollUtil.newArrayList(headers);
        coll.add(row);
        for (int i = 0; i < users.size(); i++) {
           
		//数据
        String[] colums = {String.valueOf(i + 1), users.get(i).getCode(), users.get(i).getCreateTime(),
                    users.get(i).getName(), users.get(i).getStr("is_special_name"),
                    users.get(i).getSpecialContent(), users.get(i).getStr("stateName")};
            List<String> row2 = CollUtil.newArrayList(colums);
            coll.add(row2);
        }
        //生成excel
        String excelName = "工种表";
        Integer count = 7;
        downloadExcel(path, excelName, count, coll);
		//压缩jar
        File file = ZipUtil.zip(PathKit.getWebRootPath() + "/" + fileUuid + "/workModel");
        String url = getUrl(file, excelName);
        if (StrKit.isBlank(url)){
            re("失败");
            return;
        }
        String format = DateUtil.format(new Date(), "yyyyMMddHHmmss");
        rsPara(format+excelName,Kv.by("url",url));

        //删除下载文件
        String filePath = (PathKit.getWebRootPath() + "/" + fileUuid).replace("\\", "/");
        Runner r1 = new Runner();
        r1.setPathName(filePath);
        Thread t = new Thread(r1);
        t.start();
        }

/excel下载
    public static void downloadExcel(String path, String excelName, Integer count, Collection coll) {

        List<List<String>> rows = CollUtil.newArrayList(coll);
        ExcelWriter writer = ExcelUtil.getWriter(path + excelName + ".xlsx");
        // 合并单元格后的标题行，使用默认标题样式
        writer.merge(count - 1, excelName);
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(rows, true);
        // 关闭writer，释放内存
        writer.close();
    }
//线程删除文件
    class Runner implements Runnable {
        private String pathName;

        public void setPathName(String pathName) {
            this.pathName = pathName;
        }
        @Override
        public void run() {
            try {
                Thread.sleep(1000 *60*5 );
                boolean del = FileUtil.del(pathName);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
    }
  

自定义样式导出

public void test() {
       
        List<User> Users= User.dao.template("User.find",
                select).find();
        Collection coll = new ArrayList();
        
        String fileUuid = UuidKit.getUUIDNoMinus();
        String path = (PathKit.getWebRootPath() + "/" + fileUuid + "/gzhdModel/").replace("\\", "/");
        String excelName = "工资核定表";
        BigDecimal grant_fee = new BigDecimal(0);
        BigDecimal practical_grant_fee = new BigDecimal(0);
        for (int i = 0; i < Users.size(); i++) {
            String[] colums = {String.valueOf(i + 1), Users.get(i).get("uname"),
                    Users.get(i).get("bank_card"), Users.get(i).get("idno"),
                    Users.get(i).getGrantFee().toString(), "0", "0",
                    Users.get(i).getPracticalGrantFee().toString(), null, null};

       		
            List<String> row2 = CollUtil.newArrayList(colums);
            coll.add(row2);
           
        }
        //生成excel
        downloadExcel(path, excelName, compensationUsers.size(), coll,
                grant_year,grant_month,grant_fee,practical_grant_fee,project_id,team_id);


        File file = ZipUtil.zip(PathKit.getWebRootPath() + "/" + fileUuid + "/gzhdModel");
        String url = getUrl(file, excelName);
        if (StrKit.isBlank(url)) {
            re("失败");
            return;
        }
        String format = DateUtil.format(new Date(), "yyyyMMddHHmmss");
        rsPara(format + excelName, Kv.by("url", url));
    }

    //excel下载
    public static void downloadExcel(String path, String excelName, Integer count, Collection coll,String grant_year,String grant_month,
                                     BigDecimal grant_fee,BigDecimal practical_grant_fee) {

        List<List<String>> rows = CollUtil.newArrayList(coll);
        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter(path + excelName + ".xlsx");
        String content = "工资审定表";
        writer.merge(0, 0, 0, 9, content, true);
        // 设置样式
        // 定义单元格背景色
        StyleSet style = writer.getStyleSet();
        CellStyle headCellStyle = style.getHeadCellStyle();
        // 第二个参数表示是否也设置头部单元格背景
        style.setBackgroundColor(IndexedColors.WHITE, true);
        //设置内容字体
        Font font = writer.createFont();
        //设置字体
        font.setFontName("宋体");
        //加粗
        font.setBold(true);
        //设置标题字体大小
        font.setFontHeightInPoints((short) 14);
        headCellStyle.setFont(font);
        writer.setStyleSet(style);
        for (int i = 0; i < 10; i++) {
            if(i==3 || i==2){
                writer.setColumnWidth(i, 25);
            }
            else {
                writer.setColumnWidth(i, 15);
            }
        }
        writer.setRowHeight(0, 100);
        for (int i = 0; i < count; i++) {
            writer.setRowHeight(i+4, 30);
        }

        //头部   必须合并单元格
        writer.merge(1, 1, 0, 9, "项目名称", true);
        writer.setRowHeight(1, 40);
        writer.merge(2, 3, 0, 0, "序号", true);
        writer.merge(2, 3, 1, 1, "姓名", true);
        writer.merge(2, 3, 2, 2, "账户（或卡号）", true);
        writer.merge(2, 3, 3, 3, "身份证号", true);
        writer.merge(2, 3, 4, 4, "应发工资", true);
        writer.merge(2, 2, 5, 6, "扣款", true);
        writer.writeCellValue(5, 3, "罚款");
        writer.writeCellValue(6, 3, "预支工资");
        writer.merge(2, 3, 7, 7, "实发工资", true);
        writer.merge(2, 3, 8, 8, "签名", true);
        writer.merge(2, 3, 9, 9, "备注", true);

        //底部 
        writer.writeCellValue(0, count + 4, count + 1);
        writer.writeCellValue(0, count + 5, count + 2);
        //本页合计
        writer.merge(count + 4, count + 4, 1, 3, "本页合计", true);
        writer.writeCellValue(4, count + 4, grant_fee);
        writer.writeCellValue(5, count + 4, "0");
        writer.writeCellValue(6, count + 4, "0");
        writer.writeCellValue(7, count + 4, practical_grant_fee);
        writer.writeCellValue(8, count + 4, null);
        writer.writeCellValue(9, count + 4, null);
        //总计
        writer.merge(count + 5, count + 5, 1, 3, "总计", true);
        writer.writeCellValue(4, count + 5, grant_fee);
        writer.writeCellValue(5, count + 5, "0");
        writer.writeCellValue(6, count + 5, "0");
        writer.writeCellValue(7, count + 5, practical_grant_fee);
        writer.writeCellValue(8, count + 5, null);
        writer.writeCellValue(9, count + 5, null);
        String content1="经理:             公司审核：             项目部：（盖章）              项目经理：             负责人：     ";
        writer.merge(count + 6, count + 6, 0, 9, content1, true);
        writer.setRowHeight(count + 6, 40);
        //空白
        writer.merge(count + 7, count + 7, 0, 9, null, true);
        writer.setRowHeight(count + 7, 60);
        //甘肃天安劳务有限责任公司
        writer.merge(count + 8, count + 8, 0, 9, "公司", true);
        writer.setRowHeight(count + 8, 120);
        //劳务人员工资审定表
        writer.merge(count + 9, count + 9, 0, 9, "工资审定表", true);
        writer.setRowHeight(count + 9, 60);
        //工程名称：
        writer.merge(count + 10, count + 10, 0, 9, "名称：______________________", true);
        writer.setRowHeight(count + 10, 60);
        //劳务队名称：
        writer.merge(count + 11, count + 11, 0, 9, "名称：______________________", true);
        writer.setRowHeight(count + 11, 60);
        //日期
        writer.merge(count + 12, count + 12, 0, 9, "  ______年______月", true);
        writer.setRowHeight(count + 12, 60);
        //数据从第几行开始写入
        writer.passRows(4);
        //一次性写出内容，强制输出标题
        writer.write(rows, true);
        //关闭writer，释放内存
        writer.close();
    }