模版下载和Excel文件导入

模版下载

/*** 生成模版** @param* @return AppResponse*/public AppResponse ExcelFile() throws IOException {// 创建一个新的Excel工作簿Workbook workbook = new XSSFWorkbook();// 创建一个工作表Sheet sheet = workbook.createSheet("页面拨测模板");// 创建行并设置单元格值Row headerRow = sheet.createRow(0);headerRow.createCell(0).setCellValue("序号");headerRow.createCell(1).setCellValue("拨测地址");headerRow.createCell(2).setCellValue("正常状态码");headerRow.createCell(3).setCellValue("页面大小（KB）");headerRow.createCell(4).setCellValue("加载时间（S）");// 设置列宽sheet.setColumnWidth(0, 5000);sheet.setColumnWidth(1, 5000);sheet.setColumnWidth(2, 5000);sheet.setColumnWidth(3, 5000);sheet.setColumnWidth(4, 5000);FileOutputStream outputStream = null;//获取字典项 存储路径//TODO 数据库配置String path = dialDictDataService.getValue(DictConstants.MODEL_FILE);// 保存Excel文件try {outputStream = new FileOutputStream(path + "pageTemplate.xlsx");workbook.write(outputStream);logger.info("DialPageTaskComponent.downloadExcelFile-----------Excel模板生成成功");} catch (IOException e) {e.printStackTrace();logger.info("DialPageTaskComponent.downloadExcelFile-----------Excel模板生成失败");return AppResponse.failed("生成模版失败");} finally {IOUtils.closeQuietly(outputStream);}return AppResponse.ok();}

/*** 下载模版* @param response*/public void downloadPageTemplate(HttpServletResponse response) {OutputStream out = null;InputStream in = null;ByteArrayOutputStream bos = null;String fileName = "pageTemplate导入模版";try {// 读取模板Resource res = new ClassPathResource("pageTemplate.xlsx");XSSFWorkbook workbook = new XSSFWorkbook(res.getInputStream());// 转换为字节流bos = new ByteArrayOutputStream();workbook.write(bos);byte[] barray = bos.toByteArray();in = new ByteArrayInputStream(barray);response.reset();response.setContentType("application/octet-stream");response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");out = response.getOutputStream();byte[] b = new byte[1024];int len;while ((len = in.read(b)) > 0) {out.write(b, 0, len);}out.flush();} catch (Exception e) {logger.error("DialPageTaskComponent.downloadPageTemplate, 下载模板失败", e);} finally {if (null != in) {try {in.close();} catch (IOException e) {logger.error("DialPageTaskComponent.downloadPageTemplate, 关闭资源异常", e);}in = null;}if (null != out) {try {out.close();} catch (IOException e) {logger.error("DialPageTaskComponent.downloadPageTemplate, 关闭资源异常", e);}out = null;}if (null != bos) {try {bos.flush();bos.close();} catch (IOException e) {logger.error("DialPageTaskComponent.downloadPageTemplate, 关闭资源异常", e);}out = null;}}}/*** 导入数据** @param file* @return*/public AppResponse<List<DialPageAddressResponseDTO>> importDial(MultipartFile file) {// 获取文件全名(包含扩展名)String excelName = file.getOriginalFilename();// 上传文件名格式校验if (!".xlsx".equals(excelName.substring(excelName.lastIndexOf(".")))) {logger.info("DialPageTaskComponent.importDial, 导入失败，请导入excel文件格式, {}", excelName);return AppResponse.failed("导入失败，请导入正确的excel文件格式!");}List<DialPageAddressResponseDTO> resultData = null;try {ExcelReader excelReader = ExcelUtil.getReader(file.getInputStream()).setIgnoreEmptyRow(false);if (excelReader.getSheets().size() != 1) {return AppResponse.failed("导入失败，excel内容不标准!");}// 校验excel是否含有图片Workbook workbook = new XSSFWorkbook(file.getInputStream());Map<String, PictureData> picMap = ExcelPicUtil.getPicMap(workbook, 0);if (CollectionUtil.isNotEmpty(picMap)) {return AppResponse.failed("导入失败，excel内容不标准!");}List<List<Object>> read = excelReader.read(0);// 校验excel题头AppResponse<Object> checkTitleAppResponse = checkTitle(read);if (!checkTitleAppResponse.isOk()) {logger.info("DialPageTaskComponent.importDial, 数据校验失败, data={}", checkTitleAppResponse);return AppResponse.failed(checkTitleAppResponse.getMsg());}resultData = this.handleData(read);} catch (Throwable e) {logger.error("DialPageTaskComponent.importDial, 读取文件失败", e);return AppResponse.failed("导入失败，excel读取异常！");}return AppResponse.ok(resultData);}/*** 校验表头** @param read* @return AppResponse<Object>*/private AppResponse<Object> checkTitle(List<List<Object>> read) {if (CollectionUtil.isEmpty(read) || CollectionUtil.size(read) < 4) {logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel内容不可为空, resultData={}", read);return AppResponse.failed("导入失败，excel内容不可为空！");}if (StringUtils.isBlank(String.valueOf(read.get(0).get(0)))) {logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel内容不可为空, read={}", read);return AppResponse.failed("DialPageTaskComponent.checkTitle, 导入失败，excel内容不可为空！");}List<Object> secondRow = read.get(1);if (CollectionUtil.isEmpty(secondRow)) {logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel内容不可为空, read={}", read);return AppResponse.failed("导入失败，excel内容不可为空！");}if (secondRow.size() != 4) {logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel表头不正确, secondRow={}", secondRow.size());return AppResponse.failed("导入失败，excel表头不正确！");}if (!"拨测地址".equals(String.valueOf(secondRow.get(0)))) {logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel表头不正确-拨测地址, dialAddress={}", secondRow.get(0));return AppResponse.failed("导入失败，excel表头不正确！" + String.valueOf(secondRow.get(0)));}if (!"正常响应码".equals(String.valueOf(secondRow.get(1)))) {logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel表头不正确-正常响应码, normalStatusCode={}", secondRow.get(1));return AppResponse.failed("导入失败，excel表头不正确！" + String.valueOf(secondRow.get(1)));}if (!"页面大小".equals(String.valueOf(secondRow.get(2)))) {logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel表头不正确-页面大小, responseTime={}", secondRow.get(2));return AppResponse.failed("导入失败，excel表头不正确！" + String.valueOf(secondRow.get(2)));}if (!"加载时间".equals(String.valueOf(secondRow.get(3)))) {logger.info("DialPageTaskComponent.checkTitle, 导入失败，excel表头不正确-加载时间, responseTime={}", secondRow.get(3));return AppResponse.failed("导入失败，excel表头不正确！" + String.valueOf(secondRow.get(2)));}// 导入数据为空校验AppResponse<Object> checkDataIsEmptyappResponse = checkDataIsEmpty(read);if (!checkDataIsEmptyappResponse.isOk()) {logger.info("DialPageTaskComponent.checkTitle, 导入失败，内容填写不完整, responseData={}", checkDataIsEmptyappResponse);return AppResponse.failed(checkDataIsEmptyappResponse.getMsg());}return AppResponse.ok();}/*** 校验数据是否为空** @param read* @return*/private AppResponse<Object> checkDataIsEmpty(List<List<Object>> read) {// 去除最后的空行removeLastEmptyRow(read);StringBuffer message = new StringBuffer();StringBuffer emptyRowMsg = new StringBuffer();for (int i = 2; i < read.size(); i++) {if (read.get(i).size() == 0) {logger.info("DialPageTaskComponent.checkDataIsEmpty, 导入失败，数据单元格不能为空，{}", read.get(i).toString());emptyRowMsg.append("第").append(i - 1).append("行数据内容为空！").append("</br>");}if (read.get(i).size() < 4) {message.append("第").append(i - 1).append("行，内容填写不完整！" + String.valueOf(read.get(i)).replace("null", "")).append("</br>");}for (int j = 0; j < read.get(i).size(); j++) {if (StringUtils.equalsIgnoreCase(String.valueOf(read.get(i).get(j)), "null")) {message.append("第").append(i - 1).append("行，第" + (j + 1) + "个单元格内容填写不完整！" + String.valueOf(read.get(i).get(j)).replace("null", "")).append("</br>");}}}if (StringUtils.isNotEmpty(emptyRowMsg.toString())) {return AppResponse.failed(emptyRowMsg.toString());}if (StringUtils.isNotEmpty(message.toString())) {return AppResponse.failed(message.toString());}return AppResponse.ok();}/*** 去除最后的空行** @param read*/public void removeLastEmptyRow(List<List<Object>> read) {for (int i = read.size() - 1; i >= 0; i--) {if (CollectionUtil.isNotEmpty(read.get(i))) {break;}read.remove(i);}}/*** 处理数据** @param read 参数* @return List<WhiteList> 返回*/public List<DialPageAddressResponseDTO> handleData(List<List<Object>> read) {List<DialPageAddressResponseDTO> resultData = new ArrayList<>();for (int i = 2; i < read.size(); i++) {DialPageAddressResponseDTO dialPageAddress = new DialPageAddressResponseDTO();dialPageAddress.setDialAddress(String.valueOf(read.get(i).get(0)).replace("null", ""));dialPageAddress.setNormalStatusCode(String.valueOf(read.get(i).get(1)).replace("null", ""));dialPageAddress.setPageSize(Integer.valueOf(String.valueOf(read.get(i).get(2)).replace("null", "")));dialPageAddress.setLoadTime(Integer.valueOf(String.valueOf(read.get(i).get(3)).replace("null", "")));resultData.add(dialPageAddress);}return resultData;}