java操作excel
一、java代码生产excel
1、下载模板
/** * 下载导入模板 * * @param authCode * @param templateType * @param servletResponse * @return */ @GetMapping("/downloadTemplate") public BaseResponse downloadTemplate(@RequestParam String authCode, @RequestParam Integer templateType, HttpServletResponse servletResponse) { BaseResponse response = new BaseResponse(); log.info("authCode================:" + authCode); log.info("templateType================:" + templateType); if (templateType != 0) { response.setCode(ConfigConsts.STATUS_CODE_PARAM_ERROR_406); response.setMessage("templateType参数有误"); return response; } List<UserDto> list = new ArrayList<>(); UserDto demo = new UserDto(); demo.setEmplNo("100000290"); demo.setEmplName("张三"); demo.setOnePrice(200d); SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); String s = sdf.format(new Date()); try { demo.setGrantDate(sdf.parse(s)); } catch (Exception e) { log.error(e.getMessage()); } list.add(demo); UserRequest request = new UserRequest(); request.setBenifitItem(templateType); request.setType(1); request.setCompletedTag(""); return downloadTemplate(authCode, servletResponse, list, request); } /** * @param authCode * @param resp * @param list * @param request * @return */ private BaseResponse downloadTemplate(String authCode, HttpServletResponse resp, List<?> list, UserRequest request) { BaseResponse response = new BaseResponse(); String strTitle = ""; try { boolean exists = userService.authCodeCheck(authCode); if (exists) { BaseResponse baseResponse = downloadTemplateOne(request, response); if (baseResponse != null) { return baseResponse; } strTitle = downloadTemplateTwo(request, resp, list); log.info(strTitle + "下载成功"); } else { response.setCode(Conts.STATUS_CODE_PARAM_ERROR); response.setMessage(strTitle + "授权码:" + authCode + ",无效"); } return response; } catch (Exception e) { response.setCode(Conts.ERROR_CODE); response.setMessage(strTitle + "数据导出异常。"); log.error(strTitle + "数据导出异常：Exception=", e); return response; } } private BaseResponse downloadTemplateOne(UserRequest request, BaseResponse response) { if (request.getType() != 1 && request.getType() != 0) { response.setCode(ConfigConsts.STATUS_CODE_PARAM_ERROR_406); response.setMessage("templateType参数有误"); return response; } return null; } private String downloadTemplateTwo(UserRequest request, HttpServletResponse resp, List<?> list) throws Exception { LinkedHashMap<String, String> titleMap = new LinkedHashMap<>(); String strTitle = ""; if (request.getType() == 1) { if (request.getBenifitItem() == 0) { titleMap = MAP_KEY_VALUE_FLFFTZ_JIFEN; strTitle = FLFFTZ_JIFEN_TITLE; } } else if (request.getType() == 0) { if (request.getBenifitItem() == 0 && "n".equalsIgnoreCase(request.getCompletedTag())) { titleMap = MAP_KEY_VALUE_EXPORT; strTitle = StringUtils.isNotEmpty(request.getHrFlowCode()) ? FLFFTZ_TITLE_DETAIL : FLFFTZ_TITLE; } else if (request.getBenifitItem() == 0 && "y".equalsIgnoreCase(request.getCompletedTag())) { titleMap = MAP_KEY_VALUE_EXPORT_YES; strTitle = StringUtils.isNotEmpty(request.getHrFlowCode()) ? FLFFTZ_TITLE_DETAIL : FLFFTZ_TITLE; } } String fileName = strTitle + ".xls"; resp.setContentType(ConfigConsts.APPLICATION_STREAM); resp.setHeader(ConfigConsts.CONTENT_DISP, ExportExcelUtil.getAttachmentFile(fileName)); ExcelUtil.exportExcel(strTitle, strTitle, resp.getOutputStream(), titleMap, list, CLASS_PATH, null, null, null); return strTitle; } 常量： private static final LinkedHashMap<String, String> MAP_KEY_VALUE_FLFFTZ_JIFEN = new LinkedHashMap<>(); private static final LinkedHashMap<String, String> MAP_KEY_VALUE_EXPORT = new LinkedHashMap<>(); private static final LinkedHashMap<String, String> MAP_KEY_VALUE_EXPORT_YES = new LinkedHashMap<>(); private static final String CLASS_PATH = "com.xxx.UserDto"; private static final String FLFFTZ_JIFEN_TITLE = "模板"; private static final String FLFFTZ_TITLE = "数据"; private static final String FLFFTZ_TITLE_DETAIL = "明细数据"; static { // 模板字段 MAP_KEY_VALUE_FLFFTZ_JIFEN.put(Conts.EMPL_NO_NAME, Conts.EMPL_NO); MAP_KEY_VALUE_FLFFTZ_JIFEN.put(Conts.EMPL_NAME_NAME, Conts.EMPL_NAME); // 下载excel字段 MAP_KEY_VALUE_EXPORT.put(Conts.EMPL_NO_NAME, Conts.EMPL_NO); MAP_KEY_VALUE_EXPORT.put(Conts.EMPL_NAME_NAME, Conts.EMPL_NAME); MAP_KEY_VALUE_EXPORT.put(Conts.EMPL_OA_NAME, Conts.EMPL_OA); // 下载excel名细字段 MAP_KEY_VALUE_EXPORT_YES.put(Conts.EMPL_NO_NAME, Conts.EMPL_NO); MAP_KEY_VALUE_EXPORT_YES.put(Conts.EMPL_NAME_NAME, Conts.EMPL_NAME); MAP_KEY_VALUE_EXPORT_YES.put(Conts.EMPL_OA_NAME, Conts.EMPL_OA); }
2、excel工具类
package com.xxx.util; import com.xxx.exception.BusinessException; import com.xxx.exception.ExcelException; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.BeanUtils; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.io.Serializable; import java.lang.reflect.Method; import java.math.BigDecimal; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.*; public class ExcelUtil implements Serializable { /** * serialVersionUID */ private static final long serialVersionUID = 1L; private static final Logger LOGGER = LoggerFactory .getLogger(ExcelUtil.class); //设置Excel读取最大行数 private static final int MAX_ROW = 100000; /** * readExcel:根据传进来的map集合读取Excel以及model读取Excel文件 * * @param fileName Excel文件名 * @param inputStream 输入流 * @param mapSheet sheetName 表头和属性的Map集合,其中Map中Key为Excel列的名称，Value为反射类的属性 * @param classPath 需要映射的model的路径 * @param rowNumIndexStart 表头所在行数(从1开始，即第一行对应行数1) * @return List<T> 读取到的数据集合 * @throws Exception * @author likaixuan, wolfgy * @version 1.1 2017年9月18日 * @since JDK 1.7 */ @SuppressWarnings({"resource", "unchecked"}) public static <T> List<T> readExcel(String fileName, InputStream inputStream, Map<String, Map<String, String>> mapSheet, String classPath, int rowNumIndexStart) throws Exception { //反射用 Class<?> demo = null; Object obj = null; List<Object> list = new ArrayList<>(); demo = Class.forName(classPath); //获取文件名后缀判断文件类型 String fileType = fileName.substring(fileName.lastIndexOf('.') + 1, fileName.length()); //根据文件类型及文件输入流新建工作簿对象 Workbook wb = null; if (fileType.equals("xls")) { wb = new HSSFWorkbook(inputStream); } else if (fileType.equals("xlsx")) { wb = new XSSFWorkbook(inputStream); } else { LOGGER.error("您输入的excel格式不正确"); throw new ExcelException("您输入的excel格式不正确"); } // 遍历每个Sheet表 for (int sheetNum = 0; sheetNum < 1; sheetNum++) { // 表头成功读取标志位。当表头成功读取后，rowNum_x值为表头实际行数 int rowNumX = -1; // 存放每一个field字段对应所在的列的序号 Map<String, Integer> cellmap = new HashMap<>(); // 存放所有的表头字段信息 List<String> headlist = new ArrayList<>(); // 获取当前Sheet表 Sheet hssfSheet = wb.getSheetAt(sheetNum); // 返回表头字段名和属性字段名Map集合中键的集合(Excel列的名称集合) Set<String> keySet = mapSheet.get(hssfSheet.getSheetName()).keySet(); Map<String, String> map = new LinkedHashMap<>(); if (keySet.size() == 1) { Object[] objects = keySet.toArray(); map = mapSheet.get(objects[0]); } else { map = mapSheet.get(hssfSheet.getSheetName()); } //设置默认最大行数,当超出最大行数时返回异常 if (hssfSheet != null && hssfSheet.getLastRowNum() > MAX_ROW) { throw new ExcelException("Excel 数据超过" + MAX_ROW + "行,请检查是否有空行,或分批导入"); } // 遍历Excel中的每一行 for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) { // 当表头成功读取标志位rowNum_x为-1时，说明还未开始读取数据。此时，如果传值指定读取其实行，就从指定行寻找，否则自动寻找。 if (rowNumX == -1) { //判断指定行是否为空 Row hssfRow = hssfSheet.getRow(rowNumIndexStart); if (hssfRow == null) { throw new ExcelException("指定的行为空，请检查"); } //设置当前行为指定行 rowNum = rowNumIndexStart - 1; } //获取当前行 Row hssfRow = hssfSheet.getRow(rowNum); //当前行为空时，跳出本次循环进入下一行 if (hssfRow == null) { continue; } //当前行数据为空时，跳出本次循环进入下一行 boolean flag = false; for (int i = 0; i < hssfRow.getLastCellNum(); i++) { if (hssfRow.getCell(i) != null && !("").equals(hssfRow.getCell(i).toString().trim())) { flag = true; } } if (!flag) { continue; } //获取表头内容 if (rowNumX == -1) { // 循环列Cell for (int cellNum = 0; cellNum <= hssfRow .getLastCellNum(); cellNum++) { Cell hssfCell = hssfRow.getCell(cellNum); //当前cell为空时，跳出本次循环，进入下一列。 if (hssfCell == null) { continue; } //获取当前cell的值(String类型) String tempCellValue = hssfSheet.getRow(rowNum) .getCell(cellNum).getStringCellValue(); //去除空格,空格ASCII码为160 tempCellValue = StringUtils.remove(tempCellValue, (char) 160); tempCellValue = tempCellValue.trim(); //将表头内容放入集合 headlist.add(tempCellValue); //遍历表头字段名和属性字段名Map集合中键的集合(Excel列的名称集合) Iterator<String> it = keySet.iterator(); while (it.hasNext()) { Object key = it.next(); if (StringUtils.isNotBlank(tempCellValue) && StringUtils.equals(tempCellValue, key.toString())) { //将rowNum_x设为实际的表头行数 rowNumX = rowNum; //获取表头每一个field字段对应所在的列的序号 cellmap.put(map.get(key).toString(), cellNum); } } //当rowNum_x为-1时，说明没有在表头找到对应的字段 // 或者对应字段行上面含有不为空白的行字段，返回异常。 if (rowNumX == -1) { LOGGER.error("没有找到对应的字段或者对应字段行上面含有不为空白的行字段"); throw new ExcelException("没有找到对应的字段或者对应字段行上面含有不为空白的行字段"); } } } else { //实例化反射类对象 obj = demo.newInstance(); //遍历并取出所需要的每个属性值 Iterator<String> it = keySet.iterator(); while (it.hasNext()) { //Excel列名 Object key = it.next(); //获取属性对应列数 Integer cellNumX = cellmap.get(map.get(key)); //当属性对应列为空时，结束本次循环，进入下次循环，继续获取其他属性值 if (cellNumX == null || hssfRow.getCell(cellNumX) == null) { continue; } //得到属性名 String attrName = map.get(key); //得到属性类型 Class<?> attrType = BeanUtils.findPropertyType(attrName, new Class[]{obj.getClass()}); //得到属性值 Cell cell = hssfRow.getCell(cellNumX); //特殊Excel转化日期 if (key.equals("业务年月") || key.equals("预扣月份")) { cell.setCellType(CellType.STRING); String strVal = cell.getStringCellValue(); if (strVal.length() != 6) { throw new BusinessException(key + "数据格式不正确"); } String year = strVal.substring(0, 4); String month = strVal.substring(4, 6); String val = year + "年" + month + "月"; setter(obj, attrName, val, attrType, rowNum, cellNumX, key); } else { Object val = getValue(cell, obj, attrName, attrType, rowNum, cellNumX, key); setter(obj, attrName, val, attrType, rowNum, cellNumX, key); } } //将实例化好并设置完属性的对象放入要返回的list中 list.add(obj); } } } wb.close(); inputStream.close(); return (List<T>) list; } /** * <p> * Description:setter(反射set方法给属性赋值)<br /> * </p> * * @param obj 反射类对象 * @param attrName 属性名 * @param attrValue 属性值 * @param attrType 属性类型 * @param row 当前数据在Excel中的具体行数 * @param column 当前数据在Excel中的具体列数 * @param key 当前数据对应的Excel列名 * @throws Exception void * @author likaixuan, wolfgy * @version 1.1 2017年9月18日 * @since JDK 1.7 */ public static void setter(Object obj, String attrName, Object attrValue, Class<?> attrType, int row, int column, Object key) throws Exception { try { //获取反射的方法名 Method method = obj.getClass().getMethod( "set" + StringUtil.toUpperCaseFirstOne(attrName), attrType); if (attrValue != null) { if (attrType == Date.class && attrValue.getClass() == String.class) { Date date = com.xxx.DateUtil.formatReturnDate(attrValue.toString(), com.xxx.util.DateUtil.JDATE_FORMAT_DEFAULT_FULL); attrValue = date; } if (attrType == Integer.class && attrValue.getClass() == String.class) { attrValue = Integer.parseInt(attrValue.toString()); } if (attrType == Double.class && attrValue.getClass() == String.class) { if (attrValue != null && !attrValue.equals("") && !attrValue.equals(" ")) { attrValue = Double.parseDouble(attrValue.toString()); } } } if (attrValue != null && !attrValue.equals("") && !attrValue.equals(" ")) { //进行反射 method.invoke(obj, attrValue); } } catch (Exception e) { LOGGER.error("第" + (row + 1) + " 行 " + (column + 1) + "列 属性：" + key + " 赋值异常 ", e); throw new ExcelException("第" + (row + 1) + " 行 " + (column + 1) + "列 属性：" + key + " 赋值异常 "); } } /** * <p> * Description:getter(反射get方法得到属性值)<br /> * </p> * * @param obj 反射类对象 * @param attrName 属性名 * @throws Exception * @author likaixuan, wolfgy * @version 1.1 2017年9月18日 * @since JDK 1.7 */ public static Object getter(Object obj, String attrName) throws Exception { try { //获取反射的方法名 Method method = obj.getClass().getMethod("get" + StringUtil.toUpperCaseFirstOne(attrName)); //进行反射并获取返回值 Object value = method.invoke(obj); return value; } catch (Exception e) { LOGGER.error("获取反射的方法名错误"); return null; } } /** * <p> * Description:读取当前单元格的值<br /> * </p> * * @param cell 单元格对象 * @param obj 反射类对象 * @param attrName 属性名 * @param attrType 属性类型 * @param row 当前数据在Excel中的具体行数 * @param column 当前数据在Excel中的具体列数 * @param key 当前数据对应的Excel列名 * @return val 当前单元格的值 * @throws Exception * @author likaixuan, wolfgy * @version 1.1 2017年9月18日 * @since JDK 1.7 */ public static Object getValue(Cell cell, Object obj, String attrName, Class<?> attrType, int row, int column, Object key) throws Exception { //新建当前单元格值对象 Object val = null; //判断当前单元格数据类型并取值 if (cell.getCellTypeEnum() == CellType.BOOLEAN) { val = cell.getBooleanCellValue(); } else if (cell.getCellTypeEnum() == CellType.NUMERIC) { if (DateUtil.isCellDateFormatted(cell)) { SimpleDateFormat sdf = new SimpleDateFormat(com.xxx.util.DateUtil.JDATE_FORMAT_DEFAULT_FULL); try { if (attrType == String.class) { val = sdf.format(DateUtil .getJavaDate(cell.getNumericCellValue())); } else { val = StringUtil.dateConvertFormat( sdf.format(DateUtil.getJavaDate( cell.getNumericCellValue()))); } } catch (ParseException e) { LOGGER.error("日期格式转换错误"); throw new ExcelException("第" + (row + 1) + " 行 " + (column + 1) + "列 属性：" + key + " 日期格式转换错误 "); } } else { if (attrType == String.class) { cell.setCellType(CellType.STRING); val = cell.getStringCellValue(); } else if (attrType == BigDecimal.class) { val = BigDecimal.valueOf(cell.getNumericCellValue()); } else if (attrType == long.class) { val = (long) cell.getNumericCellValue(); } else if (attrType == Double.class) { val = cell.getNumericCellValue(); } else if (attrType == Float.class) { val = (float) cell.getNumericCellValue(); } else if (attrType == int.class || attrType == Integer.class) { val = (int) cell.getNumericCellValue(); } else if (attrType == Short.class) { val = (short) cell.getNumericCellValue(); } else { val = cell.getNumericCellValue(); } } } else if (cell.getCellTypeEnum() == CellType.STRING) { val = cell.getStringCellValue(); } return val; } /** * <p> * Description:Excel导出<br /> * </p> * * @param titleText 标题栏内容 * @param out 输出流 * @param map 表头和属性的Map集合,其中Map中Key为Excel列的名称，Value为反射类的属性 * @param list 要输出的对象集合 * @param classPath 需要映射的model的路径 * @param titleStyle 标题栏样式。若为null则直接使用默认样式 * @param headStyle 表头样式。若为null则直接使用默认样式 * @param dataStyle 数据行样式。若为null则直接使用默认样式 * @throws Exception * @author likaixuan, wolfgy * @version 1.1 2017年9月18日 * @since JDK 1.7 * void */ public static void exportExcel(String titleText, String sheetName, OutputStream out, Map<String, String> map, List<?> list, String classPath, HSSFCellStyle titleStyle, HSSFCellStyle headStyle, HSSFCellStyle dataStyle) throws Exception { //创建单元格并设置单元格内容 Set<String> keySet = map.keySet();// 返回键的集合 Iterator<String> it = keySet.iterator(); // 创建HSSFWorkbook对象(excel的文档对象) HSSFWorkbook workbook = new HSSFWorkbook(); // 建立新的sheet对象（excel的表单） HSSFSheet sheet = workbook.createSheet(sheetName); // 设置默认列宽为15 sheet.setDefaultColumnWidth(15); // 合并标题栏单元格 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, keySet.size() - 1)); // 当传入的标题栏样式为空时，创建默认标题栏样式 if (titleStyle == null) { HSSFCellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(HSSFColorPredefined.WHITE.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); style.setAlignment(HorizontalAlignment.CENTER); style.setVerticalAlignment(VerticalAlignment.CENTER); HSSFFont font = workbook.createFont(); font.setColor(HSSFColorPredefined.VIOLET.getIndex()); font.setFontHeightInPoints((short) 18); style.setFont(font); titleStyle = style; } // 当传入的表头样式为空时，创建默认表头样式 if (headStyle == null) { HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColorPredefined.WHITE.getIndex()); style2.setFillPattern(FillPatternType.SOLID_FOREGROUND); style2.setBorderBottom(BorderStyle.THIN); style2.setBorderLeft(BorderStyle.THIN); style2.setBorderRight(BorderStyle.THIN); style2.setBorderTop(BorderStyle.THIN); style2.setAlignment(HorizontalAlignment.CENTER); style2.setVerticalAlignment(VerticalAlignment.CENTER); HSSFFont font2 = workbook.createFont(); font2.setFontHeightInPoints((short) 12); style2.setFont(font2); headStyle = style2; } // 当传入的数据行样式为空时，创建默认数据行样式 if (dataStyle == null) { HSSFCellStyle style3 = workbook.createCellStyle(); style3.setFillForegroundColor(HSSFColorPredefined.LIGHT_YELLOW.getIndex()); style3.setFillPattern(FillPatternType.SOLID_FOREGROUND); style3.setBorderBottom(BorderStyle.THIN); style3.setBorderLeft(BorderStyle.THIN); style3.setBorderRight(BorderStyle.THIN); style3.setBorderTop(BorderStyle.THIN); style3.setAlignment(HorizontalAlignment.CENTER); style3.setVerticalAlignment(VerticalAlignment.CENTER); dataStyle = style3; } // 创建行、单元格对象 HSSFRow row = null; HSSFCell cell = null; // 写入标题行 row = sheet.createRow(0); row.setHeightInPoints(25); cell = row.createCell(0); cell.setCellStyle(titleStyle); HSSFRichTextString textTitle = new HSSFRichTextString(titleText); cell.setCellValue(textTitle); //写入表头 row = sheet.createRow(1);//参数为行索引(excel的行)，可以是0～65535之间的任何一个 Map<String, String> attrMap = new HashMap<>(); int index = 0; while (it.hasNext()) { String key = it.next(); cell = row.createCell(index); cell.setCellValue(key); cell.setCellStyle(headStyle); attrMap.put(Integer.toString(index++), map.get(key)); } //写入数据行 for (int i = 0; i < list.size(); i++) { row = sheet.createRow(i + 2); for (int j = 0; j < map.size(); j++) { //调用getter获取要写入单元格的数据值 Object value = getter(list.get(i), attrMap.get(Integer.toString(j))); cell = row.createCell(j); if (null != value) { if (value.getClass() == Date.class) { if (StringUtils.isNotEmpty(titleText) && titleText.startsWith("台账")){ String valueDate = com.xxx.util.DateUtil.formatTimeYyyy((Date) value); cell.setCellValue(valueDate); }else{ String valueDate = com.xxx.util.DateUtil.formatTime((Date) value); cell.setCellValue(valueDate); } } else { cell.setCellValue(value.toString()); } } else { cell.setCellValue(""); } cell.setCellStyle(dataStyle); } } // 输出Excel文件 try { workbook.write(out); out.flush(); out.close(); workbook.close(); LOGGER.info("导出成功!"); } catch (IOException e) { LOGGER.info("IOException!导出失败！"); } } }
3、导入数据以及校验
/** * 导入数据 * * @param longForToken * @param files * @return * @throws IOException */ @PostMapping("/record") public BaseResponse Imported( @RequestParam("userAccount") String userAccount, @RequestHeader("X-LONGCHAT-Token") String longForToken, @RequestParam("file") MultipartFile[] files) throws IOException { BaseResponse response = new BaseResponse(); if (StringUtils.isEmpty(userAccount) ) { response.setCode(ConfigConsts.STATUS_CODE_PARAM_ERROR_403); response.setMessage("导入人员信息缺少权限参数！"); return response; } try { UserRequest req = new UserRequest(); req.setUserAccount(userAccount); UserModel userModel = userServiceImpl.getUserModelOne(req); if (userModel.getUserList().isEmpty()) { response.setCode(ConfigConsts.STATUS_CODE_PARAM_ERROR); response.setMessage("没有权限"); return response; } req.setUserModelOne(userModel); return ImportedRecord(longForToken, files, req); } catch (Exception e) { log.error("数据导入异常：Exception=", e); response.setCode(ConfigConsts.ERROR_CODE); response.setMessage(e.getMessage()); return response; } } private BaseResponse ImportedRecord(String token, MultipartFile[] files, UserRequest req) throws IOException { BaseResponse responseZhu = new BaseResponse(); String user = redisService.get(ConfigConsts.REDIS_PREFIX + token); BaseResponse validResponse = ExcelFileUtils.validExcelFile(responseZhu, files); if (!StringUtils.equals(validResponse.getCode(), ConfigConsts.SUCCESS_CODE)) { return validResponse; } MultipartFile excelFileZhu = files[0]; log.info("excelFileZhu"); String fileName = excelFileZhu.getOriginalFilename(); InputStream in = excelFileZhu.getInputStream(); UserRecord record = new UserRecord(); try { Map<String, Map<String, String>> sheetMap = new HashMap<>(); sheetMap.put(FLFFTZ_TITLE, MAP_KEY_VALUE_FLFFTZ); List<UserDto> listImported = ExcelUtil.readExcel(fileName, in, sheetMap, CLASS_PATH, 2); if (listImported.isEmpty()) { return new BaseResponse(ConfigConsts.STATUS_CODE_PARAM_ERROR_406, "没有找到需要导入的数据", null); } record.setUserAccount(userAccountZhu); record.setSuccessCount(0); record.setFailureCount(0); record.setTotalCount(listImported.size()); record.setCreateTime(new Date()); userService.insert(record); List<UserDatas> output = BeanUtil.convertList2List(listImported, UserDatas.class); output.forEach(p -> p.setEmplOaAccountManage(userAccountZhu)); output = output.stream().collect(Collectors.collectingAndThen(Collectors.toCollection( () -> new TreeSet<>(Comparator.comparing(o -> o.getEmplNo() + "#" + o.getGrantDate() + "#" + o.getUserName()))), ArrayList::new)); // 查找导入数据中的最大值，最小值 List<Date> dates = listImported.parallelStream().map(UserDto::getGrantDate).collect(Collectors.toList()).parallelStream().distinct().collect(Collectors.toList()).parallelStream().sorted().collect(Collectors.toList()); if (dates.isEmpty()) { return new BaseResponse(ConfigConsts.STATUS_CODE_PARAM_ERROR_406, "日期为空", null); } req.setFindStartStr(DateFormatUtils.thisYear(dates.get(0))); req.setFindEndStr(DateFormatUtils.thisYearEnd(dates.get(dates.size() - 1))); log.info("output--------------------:" + output); return this.batchInsertImportedRecord(output, record, req); } catch (Exception e) { log.error("benifitsImportedRecord导入失败!Exception=", e); return new BaseResponse(ConfigConsts.ERROR_CODE, "数据导入异常", null); } } private BaseResponse batchInsertImportedRecord(List<UserDatas> listImported, BenifitsImportedRecord record, UserRequest req) { StringBuilder sbDetailError = new StringBuilder(); try { /** * 1、数据根据几个索引字段去重 * 2、校验数据，时间，金额，员工编码，根据级联索引看库表是否有值 * 3、有问题的返回，没有问题的导入数据库 */ List<List<UserDatas>> listGroupImported = Lists.partition(listImported, 1500); Integer successCountZhu = 0; for (List<UserDatas> list : listGroupImported) { CheckListImportTaiZhangDto resultDto = this.checkListImportRecordData(list, req); sbDetailError.append(resultDto.getErrorMessage()); Boolean insertResult = userService.batchInsertImportedDetail(resultDto.getListSuccess()); if (insertResult && resultDto.getListSuccess() != null) { successCountZhu += resultDto.getListSuccess().size(); } } record.setSuccessCount(successCountZhu); record.setFailureCount(record.getTotalCount() - successCountZhu); String strMessage = sbDetailError.toString(); log.info("user记录："); if (strMessage.length() > 19000) { strMessage = StringUtils.substring(strMessage, 0, 19000); } record.setMessage("成功" + record.getSuccessCount() + "条;失败" + (record.getTotalCount() - record.getSuccessCount()) + "条;" + strMessage); userService.update(record); return new BaseResponse(ConfigConsts.SUCCESS_CODE, record.getMessage(), null); } catch (BusinessException e) { log.error("数据导入失败:detailDto=" + JSON.toJSONString(listImported) + " Exception=", e); record.setSuccessCount(0); record.setFailureCount(record.getTotalCount()); record.setMessage("成功0条;失败" + listImported.size() + "条;" + e.getMessage()); userService.update(record); return new BaseResponse(ConfigConsts.ERROR_CODE, record.getMessage(), null); } catch (Exception e) { log.error("数据导入失败:detailDto=" + JSON.toJSONString(listImported) + " Exception=", e); record.setSuccessCount(0); record.setFailureCount(record.getTotalCount()); record.setMessage("成功0条;失败" + listImported.size() + "条;" + sbDetailError.toString()); userService.update(record); return new BaseResponse(ConfigConsts.ERROR_CODE, record.getMessage(), null); } } private CheckListImportUserDto checkListImportRecordData(List<UserDatas> listImportedDto, UserRequest req) { CheckListImportUserDto resultDto = new CheckListImportUserDto(); List<UserDatas> listSuccessDetail = new ArrayList<>(); StringBuilder sbResult = new StringBuilder(); List<String> listEmplNo = listImportedDto.stream().map(UserDatas::getEmplNo).distinct().collect(Collectors.toList()); req.setListEmplNo(listEmplNo); List<UserDatas> listUser = userService.selectByListEmplNo(req); List<UserDatas> listImportDatas = userService.selectByListImportDatas(req); log.info("listImportDatas----------------：" + listImportDatas); for (UserDatas detailDto : listImportedDto) { Optional<UserDatas> firstUser = listUser.stream().filter(user -> StringUtils.equals(user.getEmplNo(), detailDto.getEmplNo())) .findFirst(); UserDatas userInfo = null; if (firstUser.isPresent()) { userInfo = firstUser.get(); } String recordData = this.checkImportRecordData(detailDto, userInfo, listImportDatas); if (StringUtils.isBlank(recordData)) { listSuccessDetail.add(detailDto); continue; } sbResult.append(recordData); } resultDto.setErrorMessage(sbResult.toString()); resultDto.setListSuccess(listSuccessDetail); return resultDto; } private String checkImportRecordData(UserDatas detailDto, UserDatas userInfo, List<UserDatas> listImportDatas) { StringBuilder sbDetailError = new StringBuilder(); if (!DateFormatUtils.isyyyyMMdd(detailDto.getGrantDate())) { sbDetailError.append("日期[" + detailDto.getGrantDate() + "]格式不正确,"); } if (!DateFormatUtils.isNumber(detailDto.getOnePrice().toString())) { sbDetailError.append("金额[" + detailDto.getOnePrice() + "]只能为正小数（小数点后最多俩位）,"); } if (null == userInfo) { sbDetailError.append("权限范围内员工编号[" + detailDto.getEmplNo() + "]不存在,"); } else { if (!StringUtils.equals(userInfo.getEmplName(), detailDto.getEmplName())) { sbDetailError.append("姓名[" + detailDto.getEmplName() + "]和系统中[" + userInfo.getEmplName() + "]不一致,"); } } if ("奖金类型".equals(detailDto.getSubsidieName())) { if (!listImportDatas.stream().filter(s -> (StringUtils.isNotEmpty(s.getEmplNo()) && s.getEmplNo().equalsIgnoreCase(detailDto.getEmplNo())) && s.getSubsidieName().equalsIgnoreCase(detailDto.getSubsidieName())).collect(Collectors.toList()).isEmpty()) { sbDetailError.append("姓名[" + detailDto.getEmplName() + "]奖金类型同一个发放周期内，不能有重复数据,"); } } else { log.info("111----------------：" + String.format("%tY", detailDto.getGrantDate())); if (!listImportDatas.stream().filter(s -> (StringUtils.isNotEmpty(s.getEmplNo()) && s.getEmplNo().equalsIgnoreCase(detailDto.getEmplNo())) && String.format("%tY", s.getGrantDate()).equals(String.format("%tY", detailDto.getGrantDate())) && s.getSubsidieName().equalsIgnoreCase(detailDto.getSubsidieName())).collect(Collectors.toList()).isEmpty()) { log.info("2222-：" + detailDto.getEmplName()); sbDetailError.append("姓名[" + detailDto.getEmplName() + "]同一个发放周期同一个奖金类型，不能有重复数据,"); } } StringBuilder sbResult = new StringBuilder(); if (sbDetailError.length() > 0) { sbResult.append(detailDto.getEmplName()); sbResult.append("-"); sbResult.append(detailDto.getEmplNo()); sbResult.append("导入失败;"); sbResult.append(sbDetailError); } return sbResult.toString(); } // excel校验的工具类 @Slf4j public class ExcelFileUtils { public static BaseResponse validExcelFile(BaseResponse response, MultipartFile[] files){ response.setCode(ConfigConsts.SUCCESS_CODE); if (files == null || files.length <= 0) { response.setCode(ConfigConsts.STATUS_CODE_PARAM_ERROR_406); response.setMessage("请选择上传的文件！"); return response; } MultipartFile excelFile = null; excelFile = files[0]; String fileType = ""; String fileName = ""; try { fileName = excelFile.getOriginalFilename(); fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length()); log.info("上传的文件类型为" + fileType); } catch (Exception e) { fileType = ""; } if (!fileType.toLowerCase().equals("xls") && !fileType.toLowerCase().equals("xlsx")) { response.setCode(ConfigConsts.STATUS_CODE_PARAM_ERROR_407); response.setMessage("格式不支持！"); return response; } return response; } } // bean赋值工具类 public class BeanUtil { public static void copyProperties(Object source, Object target) { try { BeanUtils.copyProperties(source, target); } catch (BeansException e) { log.error("BeanUtil property copy failed :BeansException", e); } catch (Exception e) { log.error("BeanUtil property copy failed:Exception", e); } } public static <E, T> List<T> convertList2List(List<E> input, Class<T> clzz) { List<T> output = new ArrayList<>(); if (CollectionUtils.isNotEmpty(input)) { for (E source : input) { T target = BeanUtils.instantiate(clzz); BeanUtil.copyProperties(source, target); output.add(target); } } return output; } } // 日期校验工具类 public class DateFormatUtils { /** * 校验字符串是否是年月格式“20190701” **/ public static boolean isYearMonth(String strYearMonth) { if (null == strYearMonth || strYearMonth.length() != 6) { return false; } String strMonth = StringUtils.substring(strYearMonth, 4, 6); int intMonth = Integer.parseInt(strMonth); if (intMonth >= 1 && intMonth <= 12) { return true; } else { return false; } } /** * 金额验证：正小数，小数点后只能是俩位 */ public static boolean isNumber(String str) { Pattern pattern = Pattern.compile("^(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){0,2})?$"); // 判断小数点后2位的数字的正则表达式 Matcher match = pattern.matcher(str); if (match.matches() == false) { return false; } else { return true; } } /** * 验证日期格式：2020-04-09 */ public static boolean isyyyyMMdd(Date date) { try { SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd"); String format = simpleDateFormat.format(date); Pattern pattern = Pattern.compile("((19|20)[0-9]{2})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01])"); // yyyy-MM-dd Matcher match = pattern.matcher(format); if (match.matches() == false) { return false; } else { return true; } } catch (Exception e) { log.error("发放日期格式校验异常：" + date); return false; } } public static Calendar getCalendarByStr(Date s) { Calendar calendar = Calendar.getInstance(); try { calendar.setTime(s); } catch (Exception e) { log.info("日期为null：" + s + ",e:" + e.getMessage()); } return calendar; } private static int x; // 日期属性：年 public static String thisYearEnd(Date s) { Calendar rr = getCalendarByStr(s); x = rr.get(rr.YEAR); return x + "-12" + "-31"; } public static String thisYear(Date s) { Calendar rr = getCalendarByStr(s); x = rr.get(rr.YEAR); return x + "-01" + "-01"; } }
4、导出数据
/** * 全量导出数据 **/ @ResponseBody @GetMapping("/exportAll") public BaseResponse exportAll( @RequestParam Integer benifitItem, @RequestParam String authCode, @RequestParam String userAccount, @RequestParam String completedTag, HttpServletResponse resp) { BaseResponse response = new BaseResponse(); try { if (benifitItem == 0 || benifitItem == 1 || benifitItem == 2) { UserRequest req = new UserRequest(); req.setUserAccount(userAccount); req.setPageNum(1); req.setPageSize(Integer.MAX_VALUE); req.setBenifitItem(benifitItem); req.setType(0); req.setCompletedTag(completedTag); req.setDetailedIdentification(ConfigConsts.DETAIL_TAG); log.info("下载数据==============：" + req); List<UserResponse> datas = userService.userListForExport(req); return downloadTemplate(authCode, resp, datas, req); } else { response.setCode(ConfigConsts.STATUS_CODE_PARAM_ERROR_406); response.setMessage("benifitItem参数有误"); return response; } } catch (Exception e) { response.setCode(ConfigConsts.ERROR_CODE); response.setMessage("导出数据异常"); log.error("导出数据异常Exception=", e); return response; } }
5 、导出数据，数据是map
工具类
public static final Map<String, String> createMapBeanLog() {
Map<String, String> myMap = new HashMap<>();
myMap.put("name","姓名");
myMap.put("age","年龄");
myMap.put("id","逐渐");
return myMap;
}
public static String dateToStr(Date dateDate) {
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
String dateString = formatter.format(dateDate);
return dateString;
}
/**
* 将长时间格式时间转换为字符串 yyyy-MM-dd HH:mm:ss
*
* @param dateDate
* @return
*/
public static String dateToStrLong(Date dateDate) {
SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateString = formatter.format(dateDate);
return dateString;
}
@Slf4j
public class Response implements Serializable {
private static final String OK = "ok";
private static final String ERROR = "error";
public static final Integer CODE_200 = 200;
public static final Integer CODE_400 = 400;
private Meta meta;
private Object data;
private Integer code;
public Response success() {
this.meta = new Meta(true, OK);
this.data = "成功";
this.code = CODE_200;
return this;
}
public Response success(Object data) {
this.meta = new Meta(true, OK);
this.data = data;
this.code = CODE_200;
return this;
}
public Response failure() {
this.meta = new Meta(false, ERROR);
this.data = "失败";
this.code = CODE_400;
return this;
}
public Response failure(String message) {
this.meta = new Meta(false, message);
this.data = "失败";
this.code = CODE_400;
return this;
}
public Meta getMeta() {
return meta;
}
public Object getData() {
return data;
}
public void setMeta(Meta meta) {
this.meta = meta;
}
public void setData(Object data) {
this.data = data;
}
/**
* 输出json格式
* @return
*/
public String toJson(){
return com.golaxy.jg.utils.UtilsApi.formatJson(com.golaxy.jg.utils.UtilsApi.toJson(this));
}
public static class Meta implements Serializable {
private boolean success;
private String message;
public Meta() {
}
public Meta(boolean success, String message) {
this.success = success;
this.message = message;
}
public boolean isSuccess() {
return success;
}
public String getMessage() {
return message;
}
public void setSuccess(boolean success) {
this.success = success;
}
public void setMessage(String message) {
this.message = message;
}
}
}
controller
@GetMapping("/exportDataOne")
@ApiOperation(value = "导出接口", notes = "导出接口")
public @ResponseBody
Response exportDataOne(
@RequestParam Integer id,
HttpServletResponse resp) {
Response response = new Response();
try {
if (id == null) {
response.failure("benifitItem参数有误");
return response;
}
ResultRecord record = xxxService.findOne(ResultRecord.class, "id", id);
if (record == null) {
response.failure(BaseConfig.DATA_NO);
return response;
}
List<Map<String, Object>> datas = getDatas(record);
return ExcelUtil.downloadTemplate2(resp, datas);
} catch (Exception e) {
response.failure("导出数据异常");
log.error("导出数据异常Exception=", e);
return response;
}
}
导出方法
package com.xxx.excel;
import com.xxx.config.base.BaseConfig;
import com.xxx.utils.Response;
import com.xxx.utils.TimeOperationUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.util.*;
@Slf4j
public class ExcelUtil {
private static final String FLFFTZ_JIFEN_TITLE = "模板";
public static Response downloadTemplate2(HttpServletResponse resp, List<?> list) {
Response response = new Response();
String strTitle = "";
try {
Response baseResponse = downloadTemplateOne2(response);
if (baseResponse != null) {
return baseResponse;
}
strTitle = downloadTemplateTwo2(resp, list);
log.info(strTitle + "下载成功");
return response;
} catch (Exception e) {
response.failure(strTitle + "数据导出异常。");
log.error(strTitle + "数据导出异常：Exception=", e);
return response;
}
}
private static Response downloadTemplateOne2(Response response) {
// 参数校验
return null;
}
private static String downloadTemplateTwo2(HttpServletResponse resp, List<?> list) throws Exception {
String strTitle = "";
Map<String, Object> map = (Map<String, Object>) list.get(0);
LinkedHashMap<String, String> titleMap = new LinkedHashMap<>();
for (String s : map.keySet()) {
titleMap.put(s, BaseConfig.createMapBeanLog().get(s));
}
strTitle = FLFFTZ_JIFEN_TITLE;
String fileName = strTitle + ".xls";
resp.setContentType("");
resp.setHeader("", "");
ExcelUtil.exportExcel2(strTitle, strTitle, resp.getOutputStream(), titleMap, list);
return strTitle;
}
private static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(IndexedColors.WHITE.getIndex());
setField(style);
HSSFFont font = workbook.createFont();
font.setColor(IndexedColors.VIOLET.getIndex());
font.setFontHeightInPoints((short) 18);
style.setFont(font);
return style;
}
private static void setField(HSSFCellStyle style) {
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
style.setBorderTop(BorderStyle.THIN);
style.setAlignment(HorizontalAlignment.CENTER);
style.setVerticalAlignment(VerticalAlignment.CENTER);
}
private static HSSFCellStyle getHeadStyle(HSSFWorkbook workbook) {
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(IndexedColors.WHITE.getIndex());
setField(style2);
HSSFFont font2 = workbook.createFont();
font2.setFontHeightInPoints((short) 12);
style2.setFont(font2);
return style2;
}
private static HSSFCellStyle getCommonStyle(HSSFWorkbook workbook) {
HSSFCellStyle style3 = workbook.createCellStyle();
style3.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
setField(style3);
return style3;
}
public static void exportExcel2(String titleText, String sheetName, OutputStream out, Map<String, String> map, List<?> list) throws Exception {
//创建单元格并设置单元格内容
Set<String> keySet = map.keySet();// 返回键的集合
Iterator<String> it = keySet.iterator();
// 创建HSSFWorkbook对象(excel的文档对象)
HSSFWorkbook workbook = new HSSFWorkbook();
// 建立新的sheet对象（excel的表单）
HSSFSheet sheet = workbook.createSheet(sheetName);
// 设置默认列宽为15
sheet.setDefaultColumnWidth(15);
// 合并标题栏单元格
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, keySet.size() - 1));
HSSFCellStyle titleStyle = getTitleStyle(workbook);
HSSFCellStyle headStyle = getHeadStyle(workbook);
HSSFCellStyle dataStyle = getCommonStyle(workbook);
// 创建行、单元格对象
HSSFRow row = null;
HSSFCell cell = null;
// 写入标题行
row = sheet.createRow(0);
row.setHeightInPoints(25);
cell = row.createCell(0);
cell.setCellStyle(titleStyle);
HSSFRichTextString textTitle = new HSSFRichTextString(titleText);
cell.setCellValue(textTitle);
//写入表头
row = sheet.createRow(1);//参数为行索引(excel的行)，可以是0～65535之间的任何一个
Map<String, String> attrMap = new HashMap<>();
int index = 0;
while (it.hasNext()) {
String key = it.next();
cell = row.createCell(index);
cell.setCellValue(map.get(key));
cell.setCellStyle(headStyle);
attrMap.put(Integer.toString(index++), key);
}
//写入数据行
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 2);
for (int j = 0; j < map.size(); j++) {
//调用getter获取要写入单元格的数据值
Object value = getter2(list.get(i), attrMap.get(Integer.toString(j)));
cell = row.createCell(j);
if (null != value) {
if (value.getClass() == Date.class) {
if (StringUtils.isNotEmpty(titleText) && titleText.startsWith("台账")) {
String valueDate = TimeOperationUtil.dateToStr((Date) value);
cell.setCellValue(valueDate);
} else {
String valueDate = TimeOperationUtil.dateToStrLong((Date) value);
cell.setCellValue(valueDate);
}
} else {
cell.setCellValue(value.toString());
}
} else {
cell.setCellValue("");
}
cell.setCellStyle(dataStyle);
}
}
// 输出Excel文件
try {
workbook.write(out);
out.flush();
out.close();
workbook.close();
log.info("导出成功!");
} catch (IOException e) {
log.info("IOException!导出失败！");
}
}
public static Object getter2(Object obj, String attrName)
throws Exception {
try {
Map<String, Object> map = (Map<String, Object>) obj;
return map.get(attrName);
} catch (Exception e) {
log.error("获取反射的方法名错误");
return null;
}
}
}
二、excel模板导出数据
/** * 报表导出 * * @param reportStatistics * @param response */ @PostMapping("/export") public ResultUtils exportPost(@RequestBody ReportInfo reportStatistics, HttpServletResponse response) { try { log.info("导出param：" + reportStatistics); if (StringUtils.isEmpty(reportStatistics.getTableName()) ) { return ResultUtils.failMsg(Constants.PARAM_NULL); } reportStatistics.setPageNum(null); reportStatistics.setPageSize(null); List<ReportInfo> detaList = reportService.getAllDatas(reportStatistics); if (detaList.isEmpty()) { return ResultUtils.failMsg("当前没有数据"); } boolean checkPermission = reportService.checkPermission(reportStatistics.getUserName()); excelByModel(reportStatistics.getSearchDate(),detaList, response, checkPermission); } catch (Exception e) { log.error("Exception 报表导出：{}", e); } return ResultUtils.ok(); } /** * 导出 * * @param dataSource * @param attendResponse * @throws Exception */ public static void excelByModel(String searchDate,List<ReportInfo> dataSource, HttpServletResponse attendResponse, boolean checkPermission) { try { long millis = System.currentTimeMillis(); String excelName = "attendStatics" + millis; String modelURl = "excelFile" + File.separator + "reportStaticsTwo.xls"; // 不可见 if (!checkPermission) { modelURl = "excelFile" + File.separator + "reportStatics.xls"; } // 设置导出Excel报表的导出形式 attendResponse.setContentType("application/vnd.ms-excel"); // 设置导出Excel报表的响应文件名 String fileAttend = new String(excelName.getBytes("utf-8"), StandardCharsets.ISO_8859_1); attendResponse.setHeader("Content-disposition", "attachment;filename=" + fileAttend + ".xls");//导出的文件名称 // 创建一个输出流 OutputStream fileOut = attendResponse.getOutputStream(); // 读取模板文件路径 InputStream fins = Thread.currentThread().getContextClassLoader().getResourceAsStream(modelURl); POIFSFileSystem fs = new POIFSFileSystem(fins); // 读取Excel模板 HSSFWorkbook wbAttend = new HSSFWorkbook(fs); HSSFSheet sheet = wbAttend.getSheetAt(0);//获取第一页sheet页 sheet.autoSizeColumn(1);//自动调整列宽 HSSFRow rowCellStyle0 = sheet.getRow(0);//sheet页的第一行 rowCellStyle0.getCell(1).setCellValue(rowCellStyle0.getCell(1).getStringCellValue()+searchDate); log.info("24+++++++++"+rowCellStyle0.getCell(1)); HSSFRow rowCellStyle1 = sheet.getRow(2);//sheet页的第二行 HSSFCellStyle columnReport = rowCellStyle1.getCell(1).getCellStyle();//获取sheet页第二行的样式 HSSFDataFormat df = wbAttend.createDataFormat(); //此处设置数据格式 columnReport.setDataFormat(df.getFormat("@")); HSSFCellStyle attendStyle = wbAttend.createCellStyle(); ExcelExportUtils.copyCellModel(dataSource, sheet, attendStyle, 4); //数据填充 HSSFRow row = null; for (int j = 0; j < dataSource.size(); j++) { row = sheet.getRow(j + 3); // 创建第三行 HSSFCell reportCellHead1 = row.getCell(1); //获取模板的第2个单元格b HSSFCell reportCellHead2 = row.getCell(2); // 在该单元格内输入内容 reportCellHead1.setCellValue(StringUtils.isEmpty(dataSource.get(j).getEmployeeID()) ? "" : dataSource.get(j).getEmployeeID()); reportCellHead1.setCellStyle(columnOne01);//获取模板单元格样式 //单元格添加数据 reportCellHead2.setCellValue(StringUtils.isEmpty(dataSource.get(j).getEmployeeName()) ? "" : dataSource.get(j).getEmployeeName()); reportCellHead2.setCellStyle(columnOne01); if (checkPermission) { HSSFCell cellHeard42 = row.getCell(42); HSSFCell cellHeard43 = row.getCell(43); HSSFCell cellHeard44 = row.getCell(44); cellHeard42.setCellValue(dataSource.get(j).getSalaryDays() == null ? "0.00" : dataSource.get(j).getSalaryDays() + ""); // 全薪天数 cellHeard42.setCellStyle(columnReport); cellHeard43.setCellValue(dataSource.get(j).getMonthSalaryDays() == null ? "0.00" : dataSource.get(j).getMonthSalaryDays() + ""); // 当月全薪天数 cellHeard43.setCellStyle(columnReport); cellHeard44.setCellValue(""); // 签名赋值单元格 cellHeard44.setCellStyle(columnReport); } else { HSSFCell cellHeard42 = row.getCell(42); cellHeard42.setCellValue(""); // 签名赋值单元格 cellHeard42.setCellStyle(columnReport); } } log.info("end=========================="); dataSource.clear(); // 写入流 wbAttend.write(fileOut); // 关闭流 fileOut.close(); } catch (Exception e) { log.error("导出报错："+e.toString()); log.error(e.getMessage()); } } // excel工具类： public class ExcelExportUtils { public static void copyCellModel(List<?> dataSource, HSSFSheet sheet, HSSFCellStyle newstyle, int n) { if (dataSource.size() > n) { // 插入行，5是模板中已有的行数 sheet.shiftRows(n, sheet.getLastRowNum(), dataSource.size() - n, true, false); Row sourceRow = sheet.getRow(n - 1); HSSFRow newRow = null; for (int i = 0; i < dataSource.size(); i++) { newRow = sheet.createRow(n + i); newRow.setHeight(sourceRow.getHeight()); HSSFCell newCell = null; for (int j = 0; j < sourceRow.getLastCellNum(); j++) { HSSFCell templateCell = (HSSFCell) sourceRow.getCell(j); if (templateCell != null) { newCell = newRow.createCell(j); //行复制 copyCell(templateCell, newCell, newstyle); } } } } } /** * 复制单元格 * * @param srcCell * @param distCell */ public static void copyCell(HSSFCell srcCell, HSSFCell distCell, HSSFCellStyle newstyle) { copyCellStyle(srcCell.getCellStyle(), newstyle); //样式 distCell.setCellStyle(newstyle); //评论 if (srcCell.getCellComment() != null) { distCell.setCellComment(srcCell.getCellComment()); } } /** * 复制一个单元格样式到目的单元格样式 * * @param fromStyle * @param toStyle */ public static void copyCellStyle(HSSFCellStyle fromStyle, HSSFCellStyle toStyle) { //边框和边框颜色 toStyle.setTopBorderColor(fromStyle.getTopBorderColor()); toStyle.setBottomBorderColor(fromStyle.getBottomBorderColor()); toStyle.setRightBorderColor(fromStyle.getRightBorderColor()); toStyle.setLeftBorderColor(fromStyle.getLeftBorderColor()); //背景和前景 toStyle.setFillBackgroundColor(fromStyle.getFillBackgroundColor()); toStyle.setFillForegroundColor(fromStyle.getFillForegroundColor()); toStyle.setHidden(fromStyle.getHidden()); toStyle.setIndention(fromStyle.getIndention());//首行缩进 toStyle.setLocked(fromStyle.getLocked()); toStyle.setRotation(fromStyle.getRotation());//旋转 toStyle.setWrapText(fromStyle.getWrapText()); } }
