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
吾乃代码搬运工,侵联删
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 【.NET】调用本地 Deepseek 模型
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库