表格上传，下载

参考：
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;
    }