参考:
https://blog.csdn.net/romantic_321/article/details/80917941
/** * 导出信息 */ public void exportExcel(ExportReq req, HttpServletResponse response) { logger.info("进入生成Excel文件的方法"); ResultObject<ListResultObject<Rsp>> result = new ResultObject<ListResultObject<Rsp>>(); ListResultObject<Rsp> data = new ListResultObject<Rsp>(); // 输出流 OutputStream oStream = null; try { //创建工作簿 HSSFWorkbook wb = new HSSFWorkbook(); //创建sheet HSSFSheet sheet = wb.createSheet("价格价列表"); //创建表头 HSSFRow row = sheet.createRow(0); //创建单元格 row.createCell(0).setCellValue("维护人姓名"); row.createCell(1).setCellValue("平台买入价(元/升)"); row.createCell(2).setCellValue("平台卖出价(元/升)"); row.createCell(3).setCellValue("维护时间"); row.createCell(4).setCellValue("油品名称"); row.createCell(5).setCellValue("油价日期"); row.createCell(6).setCellValue("地区名称"); row.createCell(7).setCellValue("审核状态"); List<Rsp> items = null; if (CollectionUtils.isEmpty(req.getSelectList())) { ResultObject<ListResultObject<Rsp>> query = queryExport(req); items = query.getData().getItems(); } else { items = req.getSelectList(); } if (!ObjectUtils.isEmpty(items)) { for (int i = 0; i < items.size(); i++) { Rsp rsp = items.get(i); //创建表头 HSSFRow lrow = sheet.createRow(i + 1); //创建单元格 lrow.createCell(0).setCellValue(rsp.getApplyerName()); lrow.createCell(1).setCellValue(String.valueOf(rsp.getBuyPrice())); lrow.createCell(2).setCellValue(String.valueOf(rsp.getSalePrice())); String dateToString = DateUtil.formatDateToString(rsp.getLastUpdateTime(), DateFormatType.YYYY_MM_DD_HH_MM_SS.getValue()); lrow.createCell(3).setCellValue(dateToString); lrow.createCell(4).setCellValue(rsp.getOilName()); lrow.createCell(5).setCellValue(rsp.getOilDate()); lrow.createCell(6).setCellValue(rsp.getRegionName()); lrow.createCell(7).setCellValue(rsp.getAuditStatus()); } } String format = new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date()); String fileName = "信息表" + format + ".xls"; //根据response获取输出流 // 设置下载类型 // response.setContentType("application/force-download;charset=UTF-8"); // response.setContentType("application/octet-streem"); response.setContentType("application/ms-excel;charset=UTF-8"); // 设置文件的名称 response.setHeader("Content-Disposition", "attachment;filename=".concat(String.valueOf(URLEncoder.encode(fileName, "UTF-8")))); logger.info("导出Excel表格" + fileName + "成功!"); oStream = response.getOutputStream(); //把工作薄写入到输出流 wb.write(oStream); } catch (Exception e) { e.printStackTrace(); logger.error("导出失败!" + e.getMessage()); throw new ServiceException("导出失败!" + e.getMessage()); } finally { try { oStream.close(); } catch (IOException e1) { e1.printStackTrace(); } } result.success(ErrorCodeEnum.SUCCESS, data); }
导入:
/** * 导入信息 */ @Transactional(rollbackFor = Exception.class) public ResultObject<Integer> importPrice(PriceImportReq priceImportReq) throws IOException, NotExistsDBRecordException { ResultObject<Integer> result = new ResultObject<Integer>(); //1. 解析文件格式是否正确(csv 或者 xls) String filePath = priceImportReq.getFilePath(); checkFileType(filePath); FileInputStream fileIn = new FileInputStream(filePath); checkImportOperator(priceImportReq); //根据指定的文件输入流导入Excel从而产生Workbook对象 Workbook wb = new HSSFWorkbook(fileIn); //2. 文件上传到fastdfs //3. 下载 fastdfs 的文件 //4. 解析每一行的表格数据 插入数据库 Sheet sheet = wb.getSheetAt(0); List<PriceRsp> priceRsps = new ArrayList<>(); for (int i = 0; i < sheet.getLastRowNum(); i++) { PriceRsp priceRsp = new OilPriceRsp(); Row row = sheet.getRow(i + 1); // 日期 oilPriceRsp.setOilDate(new HSSFDataFormatter().formatCellValue(row.getCell(0))); row.getCell(1).getStringCellValue(); oilPriceRsp.setRegionName(row.getCell(2).getStringCellValue()); oilPriceRsp.setOilName(row.getCell(3).getStringCellValue()); oilPriceRsp.setSalePrice(BigDecimal.valueOf(row.getCell(4).getNumericCellValue())); oilPriceRsp.setBuyPrice(BigDecimal.valueOf(row.getCell(5).getNumericCellValue())); oilPriceRsp.setApplyerId(oilPriceImportReq.getOperatorId()); oilPriceRsp.setApplyerName(oilPriceImportReq.getOperatorName()); oilPriceRsps.add(oilPriceRsp); } if (CollectionUtils.isEmpty(oilPriceRsps)) { throw new NotExistsDBRecordException(ErrorCodeEnum.IMPORT_OIL_PRICE_OIL_NOT_NULL, oilPriceImportReq); }
// 插入的逻辑 insertOilPriceImport(oilPriceRsps); //5. 删除 fastdfs 文件 result.success(ErrorCodeEnum.SUCCESS, 1); return result; }