java根据excel模板填充数据并导出

 

excel模板

 

 

方式一(推荐方式二):

部分代码片段

复制代码
  <!--导出导入 excel-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.12</version>
        </dependency>
        <!--jxls导出依赖jar包-->
        <dependency>
            <groupId>net.sf.jxls</groupId>
            <artifactId>jxls-core</artifactId>
            <version>1.0.6</version>
            <scope>compile</scope>
        </dependency>
复制代码

 

复制代码
   //region 导出
                String filePath = systemDir + staticFileBasePath + "/评审专家注册登记表_YJY.xls";
                if(UserInfo.getExpertPageMode() != null && UserInfo.getExpertPageMode() == 2){
                    filePath = systemDir + staticFileBasePath + "/评审专家注册登记表.xls";
                }
                fileInput = new FileInputStream(filePath);
                is = new BufferedInputStream(fileInput);


                List<String> sheetList = new ArrayList<String>();//工作表名称
                sheetList.add("Sheet1");

                LocalDateTime now = LocalDateTime.now();
                List<Map<String,Object>> parmsList = new ArrayList<>();//入参
                Map<String,Object> map = new HashMap<>();
                map.put("year",now.getYear());
                map.put("month",now.getMonth().getValue());
                map.put("day",now.getDayOfMonth());
                map.put("name",NAME);
                map.put("idCard",ID_CARD);
                map.put("phone",PHONE);
                map.put("sex",sex);
                map.put("adress",POSTAL_ADDRESS);
                map.put("email",E_MAIL);
                map.put("industry",work);
                map.put("wrokYear",START_WORK_DATE_YEAR);
                map.put("highDegree",HIGH_DEGREE);
                map.put("graduateSchool",GRADUATE_SCHOOL);
                map.put("industryTxt",INDUSTRY_ID_TXT);
                map.put("levelTxt",LEVEL_ID_TXT);
                map.put("org",ORG);
                map.put("aviodUnit",AVIOD_UNIT);
                map.put("registType",REGISTRATION_TYPE);
                map.put("registSource",ZFCG_SOURCE);
                map.put("mainTxt",mainStr);
                map.put("elseTxt",elseStr);
                map.put("zjType",ZjType);
                map.put("major",MAJOR_TXT);
                map.put("healath",HEALTH);

                parmsList.add(map);

                XLSTransformer transformer = new XLSTransformer();
                HSSFWorkbook workbook = (HSSFWorkbook) transformer.transformMultipleSheetsList(is, parmsList,sheetList,"expert",new HashMap(),0);

                //region 特殊单元格处理
                HSSFSheet hssfSheet = workbook.getSheet("Sheet1");
                if(UserInfo.getExpertPageMode() != null && UserInfo.getExpertPageMode()==2){
                    //在职情况
                    if(StringUtils.isNotEmpty(work)){
                        HSSFCell cell7 = hssfSheet.getRow(7).getCell(2);
                        HSSFCellStyle cellStyle7 = cell7.getCellStyle();//获取单元格样式
                        cellStyle7.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
                        cellStyle7.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
                        if("在职".equals(work)){
                            cell7.setCellValue("☑在职     □离职");
                        }else{
                            cell7.setCellValue("□在职     ☑离职");
                        }
                    }

                    //登记类型
                    if(StringUtils.isNotEmpty(REGISTRATION_TYPE)){
                        HSSFCell cell12 = hssfSheet.getRow(12).getCell(2);
                        HSSFCellStyle cellStyle12 = cell12.getCellStyle();//获取单元格样式
                        cellStyle12.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平居中
                        cellStyle12.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
                        if("1".equals(REGISTRATION_TYPE)){
                            cell12.setCellValue("☑新注册专家     □已获得有关政府部门的评审专家资格认定");
                        }else {
                            cell12.setCellValue("□新注册专家     ☑已获得有关政府部门的评审专家资格认定");
                        }
                    }
                }
                //endregion

                response.setHeader("content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
                response.setContentType("application/msexcel");
                os = response.getOutputStream();
                workbook.write(os);

                //endregion
复制代码

 方式二(推荐):

1、引入easyexcel包

<!--easyexcel-->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.0.5</version>
</dependency>

 

2、部分代码片段

复制代码
 //region 移出、解密敏感字段并脱敏、重组数据
            List<ExportExpert> list = new ArrayList<>();
            for (Map<String,Object> map : retMapList) {

                String PHONE_ = map.get("联系电话").toString();
                String PHONE_des = AesEncryptHelper.AesDecryptSingle(PHONE_,AesSecrect);
                PHONE_des = DesensitizedUtil.mobilePhone(PHONE_des);//手机号脱敏


                String ID_CARD_ = map.get("证件号码").toString();
                String ID_CARD_des = AesEncryptHelper.AesDecryptSingle(ID_CARD_,AesSecrect);
                ID_CARD_des = DesensitizedUtil.idCardNum(ID_CARD_des,5,2);//身份证脱敏,效果如:51343***********1X


                String NAME_ = map.get("姓名").toString();
                NAME_ = DesensitizedUtil.chineseName(NAME_);//中文姓名脱敏


                ExportExpert expert = ExportExpert.builder()
                        .ID(MapUtil.getStr(map,"ID",""))
                        .DEPARTID(MapUtil.getStr(map,"DEPARTID",""))
                        .xm(NAME_)
                        .czd(MapUtil.getStr(map,"常驻地",""))
                        .ggzypspm(MapUtil.getStr(map,"公共资源评审品目",""))
                        .zfcgpspm(MapUtil.getStr(map,"政府采购评审品目",""))
                        .zcyxrq(MapUtil.getStr(map,"职称有效日期",""))
                        .lxdh(PHONE_des)
                        .sflrhmd(MapUtil.getStr(map,"是否列入黑名单",""))
                        .ggzyzjly(MapUtil.getStr(map,"公共资源专家来源",""))
                        .zfcgzjly(MapUtil.getStr(map,"政府采购专家来源",""))
                        .zjlx2(MapUtil.getStr(map,"专家类型",""))
                        .zczy(MapUtil.getStr(map,"职称专业",""))
                        .zcjb(MapUtil.getStr(map,"职称级别",""))
                        .xb(MapUtil.getStr(map,"性别",""))
                        .zjlx2(MapUtil.getStr(map,"专家类型",""))
                        .zjhm(ID_CARD_des)
                        .zzqk(MapUtil.getStr(map,"在职情况",""))
                        .gzdw(MapUtil.getStr(map,"工作单位",""))
                        .xhbddqtyq(MapUtil.getStr(map,"需回避的其他单位",""))
                        .sfkcq(MapUtil.getStr(map,"是否可抽取",""))
                        .zjbm(MapUtil.getStr(map,"专家编码",""))
                        .build();
                list.add(expert);
            }
            //endregion


   String systemDir = System.getProperty("user.dir");
            String fileName = LocalDateTimeUtils.convert(LocalDateTime.now(),"yyyyMMddHHmmss") + ".xls";
            String path = systemDir +basePath +"/"+ fileName;
            File file = new File(path);
            if (!file.getParentFile().exists()) {
                file.getParentFile().mkdirs();
            }

            // 写法1 JDK8+
            // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
            // 如果这里想使用03 则 传入excelType参数即可
            EasyExcel.write(path, ExportExpert.class)
                    .sheet("导出内容")
                    .doWrite(() -> {
                        // 分页查询数据
                        return list;
                    });


            DownLoadFileUtil.downloadFile(response,path,fileName,true);
            //endregion
            
复制代码

 

posted on   五官一体即忢  阅读(7109)  评论(0编辑  收藏  举报

相关博文:
阅读排行:
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
< 2025年2月 >
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 1
2 3 4 5 6 7 8

导航

统计

点击右上角即可分享
微信分享提示