springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/dtos/community/ComMngPopulationTagDTO.java
New file @@ -0,0 +1,29 @@ package com.panzhihua.common.model.dtos.community; import io.swagger.annotations.ApiModelProperty; import lombok.Data; import java.io.Serializable; import java.util.Date; /** * 实有人口DTO */ @Data public class ComMngPopulationTagDTO implements Serializable { private static final long serialVersionUID = 1L; /** * 主键id */ @ApiModelProperty(value="主键id") private Long id; /** * 标签字符串（多个以,隔开） */ @ApiModelProperty(value="标签字符串") private String label; } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/dtos/community/ComMngRealCompanyBelongsDTO.java
New file @@ -0,0 +1,19 @@ package com.panzhihua.common.model.dtos.community; import io.swagger.annotations.ApiModel; import io.swagger.annotations.ApiModelProperty; import lombok.Data; /** * @description: 实有单位归属请求参数 * @author: Null * @date: 2021/3/11 10:36 */ @Data @ApiModel("实有单位归属请求参数") public class ComMngRealCompanyBelongsDTO { @ApiModelProperty(value = "社区名称") private String actName; } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/model/vos/community/ComMngRealCompanyBelongsVO.java
New file @@ -0,0 +1,50 @@ package com.panzhihua.common.model.vos.community; import com.fasterxml.jackson.annotation.JsonFormat; import io.swagger.annotations.ApiModelProperty; import lombok.Data; import java.io.Serializable; import java.util.Date; /** * @description: 实有单位归属 * @author: Null * @date: 2021/3/19 12:02 */ @Data public class ComMngRealCompanyBelongsVO implements Serializable { @ApiModelProperty("省份行政区划代码") private Integer provinceAdcode; @ApiModelProperty("省份行政区划名称") private String provinceName; @ApiModelProperty("城市行政区划代码") private Integer cityAdcode; @ApiModelProperty("城市行政区划名称") private String cityName; /**区县行政区划代码*/ @ApiModelProperty("区县行政区划代码") private Integer districtAdcode; @ApiModelProperty("区县行政区划名称") private String districtName; @ApiModelProperty("街道标识") private Long streetId; @ApiModelProperty("街道名称") private String streetName; @ApiModelProperty("社区名称") private String communityName; @ApiModelProperty("社区id") private Long communityId; } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/community/CommunityService.java
@@ -341,6 +341,16 @@ R listSubordinatebuilding(@RequestParam("houseCode") String houseCode); /** * 实有单位归属地 * * @param comMngRealCompanyBelongsDTO * @return */ @GetMapping("/common/data/company/belongs") R belongsComMngRealCompany(@RequestBody ComMngRealCompanyBelongsDTO comMngRealCompanyBelongsDTO); /** * 添加同级地址 * * @param comMngStructHouseVO 同级参数 @@ -1338,15 +1348,6 @@ R listSaveMngCarExcelVO(@RequestBody List<ComMngCarExcelVO> list, @RequestParam("communityId") Long communityId); /** * 分页查询实有人口 * @param comMngPopulationVO 查询参数 * @return 分页集合 */ @PostMapping("/common/data/population/page") R pagePopulation(ComMngPopulationDTO comMngPopulationVO); /** * 社区后台实有单位管理列表 * * @param pageComMngRealCompanyDTO @@ -1572,10 +1573,39 @@ /** * 批量导入实有人口 * * @param list * @param communityId * @return */ @PostMapping("/common/data/population/import") R listSavePopulationServeExcelVO(@RequestBody List<ComMngPopulationServeExcelVO> list,@RequestParam(value = "communityId") Long communityId); R listSavePopulationServeExcelVO(@RequestBody List<ComMngPopulationServeExcelVO> list, @RequestParam(value = "communityId") Long communityId); /** * 分页查询实有人口 * * @param comMngPopulationVO 查询参数 * @return 分页集合 */ @PostMapping("/common/data/population/page") R pagePopulation(ComMngPopulationDTO comMngPopulationVO); /** * 根据id实有人口详细信息 * * @param populationId 实有人口id * @return 实有人口详细信息 */ @PostMapping("/common/data/population/detail") R detailPopulation(@RequestParam(value = "populationId") Long populationId); /** * 根据id修改实有人口标签列表 * * @param populationTagDTO 请求参数 * @return 修改结果 */ @PostMapping("/common/data/population/editTag") R editTagPopulation(ComMngPopulationTagDTO populationTagDTO); } springcloud_k8s_panzhihuazhihuishequ/common/src/main/java/com/panzhihua/common/service/user/UserService.java
@@ -629,4 +629,11 @@ */ @PostMapping("/common/data/special/export") R specialUserExport(@RequestBody ExportSpecialUserDTO exportSpecialUserDTO); /** * 查询所有人员标签 * @return 标签集合 */ @PostMapping("listtag/getTag") R listTags(@RequestParam("communityId") Long communityId); } springcloud_k8s_panzhihuazhihuishequ/community_backstage/pom.xml
@@ -48,6 +48,27 @@ <groupId>com.github.xiaoymin</groupId> <artifactId>swagger-bootstrap-ui</artifactId> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>3.11-beta2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.11-beta2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>3.11-beta2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-excelant</artifactId> <version>3.11-beta2</version> </dependency> </dependencies>
@@ -8,6 +8,7 @@ import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.jcraft.jsch.SftpException; import com.panzhihua.common.constants.Constants; import com.panzhihua.common.controller.BaseController; import com.panzhihua.common.listen.*; import com.panzhihua.common.model.dtos.community.*; @@ -16,6 +17,7 @@ import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.*; import com.panzhihua.common.model.vos.user.ComMngTagVO; import com.panzhihua.common.model.vos.user.ComMngUserTagVO; import com.panzhihua.common.model.vos.user.InputUserInfoVO; import com.panzhihua.common.service.community.CommunityService; import com.panzhihua.common.service.user.UserService; @@ -24,6 +26,9 @@ import com.panzhihua.common.validated.AddGroup; import com.panzhihua.community_backstage.easyexcel.UploadEexcelUserDTOListener; import com.panzhihua.community_backstage.excel.CustomSheetWriteHandler; import com.panzhihua.community_backstage.model.dto.ExcelExportDto; import com.panzhihua.community_backstage.utils.ExportExcelUtil; import com.panzhihua.community_backstage.utils.ExportExcelWrapper; import io.swagger.annotations.Api; import io.swagger.annotations.ApiImplicitParam; import io.swagger.annotations.ApiOperation; @@ -69,7 +74,7 @@ private String companyUrl = "http://panzhihua.nhys.cdnhxx.com/web/%e5%ae%9e%e6%9c%89%e5%8d%95%e4%bd%8d%e5%af%bc%e5%85%a5%e6%a8%a1%e6%9d%bf.xlsx"; @Value("${excel.assetsUrl}") private String assetsUrl = "http://panzhihua.nhys.cdnhxx.com/web/%e5%ae%9e%e6%9c%89%e5%8d%95%e4%bd%8d%e5%af%bc%e5%85%a5%e6%a8%a1%e6%9d%bf.xlsx"; private String excelPopulationUrl = "http://panzhihua.nhys.cdnhxx.com/web/实有人口导入.xlsx"; private String excelPopulationUrl="http://panzhihua.nhys.cdnhxx.com/web/实有人口导入.xlsx"; // FTP 登录用户名 @Value("${ftp.username}") @@ -531,6 +536,12 @@ } @ApiOperation(value = "实有单位归属地", response = ComMngRealCompanyVO.class) @GetMapping("/company/belongs") public R belongsComMngRealCompany(@RequestBody ComMngRealCompanyBelongsDTO comMngRealCompanyBelongsDTO) { return communityService.belongsComMngRealCompany(comMngRealCompanyBelongsDTO); } ///////////// @ApiOperation(value = "分页查询实有资产列表", response = ComMngRealAssetsVO.class) @@ -702,5 +713,45 @@ return R.ok(); } @ApiOperation(value = "实有人口详情", response = ComMngPopulationVO.class) @PostMapping("/population/detail") public R detailPopulation(@RequestParam(value = "populationId") Long populationId) { return communityService.detailPopulation(populationId); } @ApiOperation(value = "实有人口标签修改", response = ComMngPopulationVO.class) @PostMapping("/population/editTag") public R editTagPopulation(@RequestBody ComMngPopulationTagDTO populationTagDTO) { return communityService.editTagPopulation(populationTagDTO); } @ApiOperation(value = "实有人口导入模板") @PostMapping("/population/export") public void exportPopulation(HttpServletRequest request, HttpServletResponse response) { //获取登陆用户 LoginUserInfoVO loginUserInfo = this.getLoginUserInfo(); //获取登陆用户绑定社区id Long communityId = loginUserInfo.getCommunityId(); //创建Excel工具类对象 ExportExcelWrapper<ExcelExportDto> util = new ExportExcelWrapper<>(); //创建示范数据对象list List<ExcelExportDto> list = new ArrayList<>(); //创建表头数组集合 String[] columnNames = new String[50]; //加载默认表头数据 for (int i = 0; i < ExportExcelUtil.ExportField.length; i++) { columnNames[i] = ExportExcelUtil.ExportField[i]; } //查询当前社区标签列表 R tagsResult = userService.listTags(communityId); if(Constants.SUCCESS.equals(tagsResult.getCode())){ List<String> TagsList = (List<String>)tagsResult.getData(); //动态加载标签列表到表头 for (int i = 0; i < TagsList.size(); i++) { columnNames[ExportExcelUtil.ExportField.length + i] = TagsList.get(i) + "(是或否)"; } } //导出模板 util.exportExcel("实有人口导入模板", "实有人口导入模板", columnNames, list, response, ExportExcelUtil.EXCEl_FILE_2007); } } springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/model/dto/ExcelExportDto.java
New file @@ -0,0 +1,60 @@ package com.panzhihua.community_backstage.model.dto; import lombok.Data; @Data public class ExcelExportDto { /** * 家庭成员 */ private String name = "张三"; /** * 性别 */ private String sex = "男"; /** * 年龄 */ private String age = "25"; /** * 是否租住 */ private String isRent = "是"; /** * 街路巷 */ private String road = "樱花街"; /** * 门牌号 */ private String doorNo = "115"; /** * 楼排号 */ private String floor = "15栋"; /** * 单元号 */ private String unitNo = "10"; /** * 户室 */ private String houseNo = "1001"; /** * 民族 */ private String nation = "汉族"; /** * 政治面貌 */ private String politicalOutlook = "群众"; /** * 身份证 */ private String cardNo = "513421199508187219"; /** * 联系方式 */ private String phone = "13768548521"; } springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/utils/ExportExcelUtil.java
New file @@ -0,0 +1,430 @@ package com.panzhihua.community_backstage.utils; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.*; import java.util.regex.Matcher; import java.util.regex.Pattern; import com.panzhihua.common.model.dtos.community.ComMngPopulationTagDTO; import com.panzhihua.community_backstage.model.dto.ExcelExportDto; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import org.apache.poi.xssf.usermodel.XSSFColor; import org.apache.poi.xssf.usermodel.XSSFFont; import org.apache.poi.xssf.usermodel.XSSFRichTextString; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * 导出Excel * @author liuyazhuang * * @param <T> */ public class ExportExcelUtil<T>{ // 2007 版本以上 最大支持1048576行 public final static String EXCEl_FILE_2007 = "2007"; // 2003 版本 最大支持65536 行 public final static String EXCEL_FILE_2003 = "2003"; public final static String[] ExportField = {"家庭成员","性别","年龄","是否租住","街路巷","门牌号","楼排号","单元号","户室","民族","政治面貌","身份证","联系方式"}; /** * <p> * 导出无头部标题行Excel <br> * 时间格式默认：yyyy-MM-dd hh:mm:ss <br> * </p> * * @param title 表格标题 * @param dataset 数据集合 * @param out 输出流 * @param version 2003 或者 2007，不传时默认生成2003版本 */ public void exportExcel(String title, Collection<T> dataset, OutputStream out, String version) { if(StringUtils.isEmpty(version) || EXCEL_FILE_2003.equals(version.trim())){ exportExcel2003(title, null, dataset, out, "yyyy-MM-dd HH:mm:ss"); }else{ exportExcel2007(title, null, dataset, out, "yyyy-MM-dd HH:mm:ss"); } } /** * <p> * 导出带有头部标题行的Excel <br> * 时间格式默认：yyyy-MM-dd hh:mm:ss <br> * </p> * * @param title 表格标题 * @param headers 头部标题集合 * @param dataset 数据集合 * @param out 输出流 * @param version 2003 或者 2007，不传时默认生成2003版本 */ public void exportExcel(String title,String[] headers, Collection<T> dataset, OutputStream out,String version) { if(StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())){ exportExcel2003(title, headers, dataset, out, "yyyy-MM-dd HH:mm:ss"); }else{ exportExcel2007(title, headers, dataset, out, "yyyy-MM-dd HH:mm:ss"); } } /** * <p> * 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中 <br> * 此版本生成2007以上版本的文件 (文件后缀：xlsx) * </p> * * @param title * 表格标题名 * @param headers * 表格头部标题集合 * @param dataset * 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的 * JavaBean属性的数据类型有基本数据类型及String,Date * @param out * 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中 * @param pattern * 如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss" */ @SuppressWarnings({ "unchecked", "rawtypes" }) public void exportExcel2007(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) { // 声明一个工作薄 XSSFWorkbook workbook = new XSSFWorkbook(); // 生成一个表格 XSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(20); // 生成一个样式 XSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(new XSSFColor(java.awt.Color.gray)); style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(XSSFCellStyle.BORDER_THIN); style.setBorderLeft(XSSFCellStyle.BORDER_THIN); style.setBorderRight(XSSFCellStyle.BORDER_THIN); style.setBorderTop(XSSFCellStyle.BORDER_THIN); style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 生成一个字体 XSSFFont font = workbook.createFont(); font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font.setFontName("宋体"); font.setColor(new XSSFColor(java.awt.Color.BLACK)); font.setFontHeightInPoints((short) 11); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 XSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(new XSSFColor(java.awt.Color.WHITE)); style2.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(XSSFCellStyle.BORDER_THIN); style2.setBorderLeft(XSSFCellStyle.BORDER_THIN); style2.setBorderRight(XSSFCellStyle.BORDER_THIN); style2.setBorderTop(XSSFCellStyle.BORDER_THIN); style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 XSSFFont font2 = workbook.createFont(); font2.setBoldweight(XSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 产生表格标题行 XSSFRow row = sheet.createRow(0); XSSFCell cellHeader; for (int i = 0; i < headers.length; i++) { cellHeader = row.createCell(i); cellHeader.setCellStyle(style); cellHeader.setCellValue(new XSSFRichTextString(headers[i])); } // 遍历集合数据，产生数据行 Iterator<T> it = dataset.iterator(); int index = 0; T t; Field[] fields; Field field; XSSFRichTextString richString; Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher; String fieldName; String getMethodName; XSSFCell cell; Class tCls; Method getMethod; Object value; String textValue; SimpleDateFormat sdf = new SimpleDateFormat(pattern); while (it.hasNext()) { index++; row = sheet.createRow(index); t = (T) it.next(); // 利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值 fields = t.getClass().getDeclaredFields(); for (int i = 0; i < fields.length; i++) { cell = row.createCell(i); cell.setCellStyle(style2); field = fields[i]; fieldName = field.getName(); getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { tCls = t.getClass(); getMethod = tCls.getMethod(getMethodName, new Class[] {}); value = getMethod.invoke(t, new Object[] {}); // 判断值的类型后进行强制类型转换 textValue = null; if (value instanceof Integer) { cell.setCellValue((Integer) value); } else if (value instanceof Float) { textValue = String.valueOf((Float) value); cell.setCellValue(textValue); } else if (value instanceof Double) { textValue = String.valueOf((Double) value); cell.setCellValue(textValue); } else if (value instanceof Long) { cell.setCellValue((Long) value); } if (value instanceof Boolean) { textValue = "是"; if (!(Boolean) value) { textValue = "否"; } } else if (value instanceof Date) { textValue = sdf.format((Date) value); } else { // 其它数据类型都当作字符串简单处理 if (value != null) { textValue = value.toString(); } } if (textValue != null) { matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else { richString = new XSSFRichTextString(textValue); cell.setCellValue(richString); } } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { // 清理资源 } } } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); } } /** * <p> * 通用Excel导出方法,利用反射机制遍历对象的所有字段，将数据写入Excel文件中 <br> * 此方法生成2003版本的excel,文件名后缀：xls <br> * </p> * * @param title * 表格标题名 * @param headers * 表格头部标题集合 * @param dataset * 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的 * JavaBean属性的数据类型有基本数据类型及String,Date * @param out * 与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中 * @param pattern * 如果有时间数据，设定输出格式。默认为"yyyy-MM-dd hh:mm:ss" */ @SuppressWarnings({ "unchecked", "rawtypes" }) public void exportExcel2003(String title, String[] headers, Collection<T> dataset, OutputStream out, String pattern) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(20); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setFontName("宋体"); font.setColor(HSSFColor.WHITE.index); font.setFontHeightInPoints((short) 11); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.WHITE.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 产生表格标题行 HSSFRow row = sheet.createRow(0); HSSFCell cellHeader; for (int i = 0; i < headers.length; i++) { cellHeader = row.createCell(i); cellHeader.setCellStyle(style); cellHeader.setCellValue(new HSSFRichTextString(headers[i])); } // 遍历集合数据，产生数据行 Iterator<T> it = dataset.iterator(); int index = 0; T t; Field[] fields; Field field; HSSFRichTextString richString; Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher; String fieldName; String getMethodName; HSSFCell cell; Class tCls; Method getMethod; Object value; String textValue; SimpleDateFormat sdf = new SimpleDateFormat(pattern); while (it.hasNext()) { index++; row = sheet.createRow(index); t = (T) it.next(); // 利用反射，根据JavaBean属性的先后顺序，动态调用getXxx()方法得到属性值 fields = t.getClass().getDeclaredFields(); for (int i = 0; i < fields.length; i++) { cell = row.createCell(i); cell.setCellStyle(style2); field = fields[i]; fieldName = field.getName(); getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { tCls = t.getClass(); getMethod = tCls.getMethod(getMethodName, new Class[] {}); value = getMethod.invoke(t, new Object[] {}); // 判断值的类型后进行强制类型转换 textValue = null; if (value instanceof Integer) { cell.setCellValue((Integer) value); } else if (value instanceof Float) { textValue = String.valueOf((Float) value); cell.setCellValue(textValue); } else if (value instanceof Double) { textValue = String.valueOf((Double) value); cell.setCellValue(textValue); } else if (value instanceof Long) { cell.setCellValue((Long) value); } if (value instanceof Boolean) { textValue = "是"; if (!(Boolean) value) { textValue = "否"; } } else if (value instanceof Date) { textValue = sdf.format((Date) value); } else { // 其它数据类型都当作字符串简单处理 if (value != null) { textValue = value.toString(); } } if (textValue != null) { matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else { richString = new HSSFRichTextString(textValue); cell.setCellValue(richString); } } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { // 清理资源 } } } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); } } public static void main(String[] args) throws Exception{ ExportExcelUtil<ExcelExportDto> util = new ExportExcelUtil<ExcelExportDto>(); List<ExcelExportDto> mapList = new ArrayList<>(); String[] columnNames = new String[50]; for (int i = 0; i < ExportField.length; i++) { columnNames[i] = ExportField[i]; } for (int i = 0; i <= 6; i++) { columnNames[ExportField.length + i] = "1"; } util.exportExcel("实有人口导入模板", columnNames, mapList, new FileOutputStream("E:/实有人口导入模板.xls"), ExportExcelUtil.EXCEl_FILE_2007); } } springcloud_k8s_panzhihuazhihuishequ/community_backstage/src/main/java/com/panzhihua/community_backstage/utils/ExportExcelWrapper.java
New file @@ -0,0 +1,42 @@ package com.panzhihua.community_backstage.utils; import java.net.URLEncoder; import java.util.Collection; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang3.StringUtils; /** * 包装类 * @author liuyazhuang * * @param <T> */ public class ExportExcelWrapper<T> extends ExportExcelUtil<T> { /** * <p> * 导出带有头部标题行的Excel <br> * 时间格式默认：yyyy-MM-dd hh:mm:ss <br> * </p> * * @param title 表格标题 * @param headers 头部标题集合 * @param dataset 数据集合 * @param version 2003 或者 2007，不传时默认生成2003版本 */ public void exportExcel(String fileName, String title, String[] headers, Collection<T> dataset, HttpServletResponse response,String version) { try { response.setContentType("application/vnd.ms-excel"); response.addHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(fileName, "UTF-8") + ".xls"); if(StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())){ exportExcel2003(title, headers, dataset, response.getOutputStream(), "yyyy-MM-dd HH:mm:ss"); }else{ exportExcel2007(title, headers, dataset, response.getOutputStream(), "yyyy-MM-dd HH:mm:ss"); } } catch (Exception e) { e.printStackTrace(); } } }
@@ -3,6 +3,7 @@ import com.panzhihua.common.model.dtos.community.*; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.*; import com.panzhihua.common.service.user.UserService; import com.panzhihua.service_community.service.*; import lombok.extern.slf4j.Slf4j; import org.springframework.transaction.annotation.Transactional; @@ -34,6 +35,7 @@ private ComMngProvinceService comMngProvinceService; @Resource private ComMngPopulationService comMngPopulationService; /** * 小程序用户车辆登记 @@ -130,6 +132,10 @@ return comMngRealCompanyService.detailComMngRealCompany(id); } @GetMapping("/company/belongs") public R belongsComMngRealCompany(@RequestBody ComMngRealCompanyBelongsDTO comMngRealCompanyBelongsDTO) { return comMngRealCompanyService.belongsComMngRealCompany(comMngRealCompanyBelongsDTO); } /** * 社区后台导入实有单位 * @param list 车实有单位列表 @@ -268,4 +274,24 @@ public R listSavePopulationExcelVO(@RequestBody List<ComMngPopulationServeExcelVO> list, @RequestParam(value = "communityId") Long communityId){ return comMngPopulationService.listSavePopulation(list,communityId); } /** * 根据实有人口id查询详情 * @param populationId 实有人口id * @return 实有人口详情查询结果 */ @PostMapping("/population/detail") public R detailPopulation(@RequestParam(value = "populationId") Long populationId) { return comMngPopulationService.detailPopulation(populationId); } /** * 根据实有人口id修改标签列表 * @param populationTagDTO 请求参数 * @return 修改结果 */ @PostMapping("/population/editTag") public R editTagPopulation(@RequestBody ComMngPopulationTagDTO populationTagDTO) { return comMngPopulationService.editTagPopulation(populationTagDTO); } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/dao/ComMngRealCompanyDAO.java
@@ -4,7 +4,9 @@ import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.panzhihua.common.model.dtos.community.ExportRealCompanyExcelDTO; import com.panzhihua.common.model.dtos.community.ComMngRealCompanyBelongsDTO; import com.panzhihua.common.model.dtos.community.PageComMngRealCompanyDTO; import com.panzhihua.common.model.vos.community.ComMngRealCompanyBelongsVO; import com.panzhihua.common.model.vos.community.ComMngRealCompanyExcelVO; import com.panzhihua.common.model.vos.community.ComMngRealCompanyVO; import com.panzhihua.service_community.model.dos.ComMngRealCompanyDO; @@ -74,4 +76,31 @@ " order by t.create_at desc"+ "</script>") List<ComMngRealCompanyExcelVO> exportRealCompanyExcel(@Param(value = "exportRealCompanyExcelDTO") ExportRealCompanyExcelDTO exportRealCompanyExcelDTO); @Select("<script> " + " SELECT distinct " + " act.province_code AS provinceAdcode, " + " act.city_code AS cityAdcode, " + " act.area_code AS districtAdcode, " + " pr.province_name AS provinceName, " + " cty.city_name AS cityName, " + " dis.district_name AS districtName, " + " street.name AS streetName, " + " act.name AS communityName, " + " street.street_id AS streetId, " + " act.community_id AS communityId " + " FROM " + " com_act act " + " LEFT JOIN com_street street ON act.street_id = street.street_id " + " LEFT join com_mng_struct_area_province pr on street.province_code = pr.province_adcode " + " LEFT join com_mng_struct_area_city cty on street.city_code = cty.city_adcode " + " LEFT join com_mng_struct_area_district dis on street.area_code = dis.district_adcode " + " WHERE 1=1 " + "<if test='pageComMngRealCompanyBelongsDTO.actName != null and pageComMngRealCompanyBelongsDTO.actName.trim() != ""'>" + " and act.name = #{pageComMngRealCompanyBelongsDTO.actName} " + " </if> " + " order by act.create_at desc "+ " limit 1 "+ "</script>") ComMngRealCompanyBelongsVO queryComMngRealCompanyBelongs(@Param(value = "pageComMngRealCompanyBelongsDTO") ComMngRealCompanyBelongsDTO comMngRealCompanyBelongsDTO); } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/ComMngPopulationService.java
@@ -1,6 +1,7 @@ package com.panzhihua.service_community.service; import com.panzhihua.common.model.dtos.community.ComMngPopulationDTO; import com.panzhihua.common.model.dtos.community.ComMngPopulationTagDTO; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.ComMngPopulationServeExcelVO; import com.panzhihua.common.model.vos.community.ComMngPopulationVO; @@ -62,4 +63,10 @@ * @return */ R listSavePopulation(List<ComMngPopulationServeExcelVO> list, Long communityId); /** * 根据实有人口id修改用户标签 * @param populationTagDTO 请求参数 * @return 修改结果 */ R editTagPopulation(ComMngPopulationTagDTO populationTagDTO); } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/ComMngRealCompanyService.java
@@ -1,6 +1,7 @@ package com.panzhihua.service_community.service; import com.panzhihua.common.model.dtos.community.ExportRealCompanyExcelDTO; import com.panzhihua.common.model.dtos.community.ComMngRealCompanyBelongsDTO; import com.panzhihua.common.model.dtos.community.PageComMngRealCompanyDTO; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.ComMngRealCompanyExcelVO; @@ -52,4 +53,12 @@ * @return */ R exportRealCompanyExcel(ExportRealCompanyExcelDTO exportRealCompanyExcelDTO); /** * 实有单位详情 * @param comMngRealCompanyBelongsDTO * @return */ R belongsComMngRealCompany(ComMngRealCompanyBelongsDTO comMngRealCompanyBelongsDTO); } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComMngPopulationServiceImpl.java
@@ -9,22 +9,28 @@ import com.google.common.collect.Lists; import com.panzhihua.common.exceptions.ServiceException; import com.panzhihua.common.model.dtos.community.ComMngPopulationDTO; import com.panzhihua.common.model.dtos.community.ComMngPopulationTagDTO; import com.panzhihua.common.model.dtos.community.PageComActDTO; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.*; import com.panzhihua.common.model.vos.user.ComMngFamilyInfoVO; import com.panzhihua.service_community.dao.ComActActivityDAO; import com.panzhihua.service_community.dao.ComActDAO; import com.panzhihua.service_community.dao.ComActVillageDAO; import com.panzhihua.service_community.dao.ComMngPopulationDAO; import com.panzhihua.service_community.model.dos.ComActActivityDO; import com.panzhihua.service_community.model.dos.ComActDO; import com.panzhihua.service_community.model.dos.ComMngPopulationDO; import com.panzhihua.service_community.model.dos.ComMngVillageDO; import com.panzhihua.service_community.service.ComMngPopulationService; import org.springframework.beans.BeanUtils; import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder; import org.springframework.stereotype.Service; import org.springframework.util.ObjectUtils; import javax.annotation.Resource; import java.util.ArrayList; import java.util.Date; import java.util.List; /** @@ -263,4 +269,24 @@ } return true; } /** * 根据id修改实有人口标签 * @param populationTagDTO 请求参数 * @return 修改结果 */ @Override public R editTagPopulation(ComMngPopulationTagDTO populationTagDTO) { ComMngPopulationDO comMngPopulationDO = populationDAO.selectById(populationTagDTO.getId()); if (comMngPopulationDO == null) { return R.fail("未查询到人口记录"); } BeanUtils.copyProperties(populationTagDTO,comMngPopulationDO); int nub = populationDAO.updateById(comMngPopulationDO); if(nub < 1){ return R.fail(); } return R.ok(); } } springcloud_k8s_panzhihuazhihuishequ/service_community/src/main/java/com/panzhihua/service_community/service/impl/ComMngRealCompanyServiceImpl.java
@@ -4,10 +4,11 @@ import com.baomidou.mybatisplus.core.metadata.IPage; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl; import com.panzhihua.common.model.dtos.community.ComMngRealCompanyBelongsDTO; import com.panzhihua.common.model.dtos.community.ExportRealCompanyExcelDTO; import com.panzhihua.common.model.dtos.community.PageComMngRealCompanyDTO; import com.panzhihua.common.model.vos.R; import com.panzhihua.common.model.vos.community.ComMngCarVO; import com.panzhihua.common.model.vos.community.ComMngRealCompanyBelongsVO; import com.panzhihua.common.model.vos.community.ComMngRealCompanyExcelVO; import com.panzhihua.common.model.vos.community.ComMngRealCompanyVO; import com.panzhihua.service_community.dao.ComMngRealCompanyDAO; @@ -132,4 +133,11 @@ List<ComMngRealCompanyExcelVO> list = comMngRealCompanyDAO.exportRealCompanyExcel(exportRealCompanyExcelDTO); return R.ok(list); } @Override public R belongsComMngRealCompany(ComMngRealCompanyBelongsDTO comMngRealCompanyBelongsDTO) { ComMngRealCompanyBelongsVO comMngRealCompanyBelongsVO = comMngRealCompanyDAO.queryComMngRealCompanyBelongs(comMngRealCompanyBelongsDTO); return R.ok(comMngRealCompanyBelongsVO); } } springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/api/UserApi.java
@@ -151,6 +151,15 @@ } /** * 查询当前社区所有标签以及预设标签 * @return 标签集合 */ @PostMapping("listtag/getTag") public R listTags(@RequestParam("communityId") Long communityId){ return userService.listTags(communityId); } /** * 人员详情 * @param userId 用户id * @return 人员详情 springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/service/UserService.java
@@ -351,4 +351,10 @@ * @return */ R getSysUserVOByPhone(String phone); /** * 查询当前社区所有标签以及预设标签 * @return 标签集合 */ R listTags(Long communityId); } springcloud_k8s_panzhihuazhihuishequ/service_user/src/main/java/com/panzhihua/service_user/service/impl/UserServiceImpl.java
@@ -1533,4 +1533,19 @@ // boolean matches = encoder.matches("admin1234567", encode); // System.out.println(matches); } /** * 查询当前社区所有标签以及预设标签 * * @return 标签集合 */ @Override public R listTags(Long communityId) { List<String> list=new ArrayList<>(); List<ComMngUserTagDO> comMngUserTagDOS = comMngUserTagDAO.selectList(new QueryWrapper<ComMngUserTagDO>().eq("sysFlag",1).or().eq("communityId",communityId)); if (!ObjectUtils.isEmpty(comMngUserTagDOS)) { list=comMngUserTagDOS.stream().map(comMngUserTagDO -> comMngUserTagDO.getTagName()).collect(Collectors.toList()); } return R.ok(list); } }
