JAVA功能 之 下载excel文件到本地 和 上传excel 保存数据并返回错误信息文件

一. 下载excel文件到本地

controller层

 /**
     * 下载excel文件
     *
     * @param request  请求对象
     * @param response 响应对象
     */
    @RequestMapping("/downLoadAreaHoseFile")
    @ResponseBody
    @ApiOperation(value = "下载excel文件", notes = "下载excel文件", httpMethod = "GET")
    public void downLoadAreaHoseFile(HttpServletRequest request, HttpServletResponse response) throws Exception {
        leAreaService.downLoadAreaHoseFile(request,response);
    }

service层

@Override
    public void downLoadAreaHoseFile(HttpServletRequest request, HttpServletResponse response) {
        HSSFWorkbook workbook = null;
        try {
            //1, 获取文件路径, 创建文件输入流对象, 获取excel文件
            String path = request.getSession().getServletContext().getRealPath("template/areahouseimport.xls");
            FileInputStream fileInputStream = new FileInputStream(path);
            workbook = new HSSFWorkbook(fileInputStream);
            fileInputStream.close();
            // 动态写入数据, 如果不需要对excel文件进行数据写入操作可直接返回
            //    2,获取excel中的工作表 , 根据索引获取
            //    获取小区表]
            //  getSheetAt(索引)  获取excel中的工作表sheet  索引从0开始
            HSSFSheet areaSheet = workbook.getSheetAt(1);

            List<PartnerArea> areaList = selectLeArea(new HashMap<String, Object>());
//  Columns.getColumnLabels(columnNum)  返回[1,columnNum] 共columnNum个对应xls列字母的数组
//  columnNum  列的个数,至少要为1
            String[] columnLabels = Columns.getColumnLabels(areaList.size());

            //创建存放行的集合
            List<HSSFRow> hssfRowList = new ArrayList<>();
            //创建存放小区名称的行
            HSSFRow headRow = areaSheet.createRow(0);
            hssfRowList.add(headRow);

            for (int i = 0; i < areaList.size(); i++) {
                //添加数据到对应单元格
                PartnerArea area = areaList.get(i);
                //添加小区字段信息
                headRow.createCell(i, CellType.STRING).setCellValue(area.getAreaName() + "_" + area.getId());

                Map<String, Object> map = new HashMap<>();
                map.put("areaId", area.getId());
                List<AreaPropertyCharges> chargesList = getChargesByArea(map);

                //初始化命名区域
                HSSFName name = workbook.createName();
                //设置命名区域名称 设置数据的所属范围
                String reference = areaSheet.getSheetName()+"!" + columnLabels[i]+"2:"+columnLabels[i] + (chargesList.size()+2);
                name.setNameName(area.getAreaName()+"_"+area.getId());
                name.setRefersToFormula(reference);

                for (int j = 1; j <= chargesList.size(); j++) {
                    //判断是否创建行存储标准信息的行
                    if (hssfRowList.size()<= j)
                        hssfRowList.add(areaSheet.createRow(j));
                    //获取到当前数据行
                    HSSFRow crow = hssfRowList.get(j);
                    AreaPropertyCharges charges = chargesList.get(j - 1);
                    crow.createCell(i, CellType.STRING).setCellValue(charges.getName() + "_" + charges.getId());
                }
            }
            // 5, 写出文件下载
            //    5.1 获取response自带的输出流
            OutputStream fos = response.getOutputStream();
            String userAgent = request.getHeader("USER-AGENT");
            String fileName = "areahouseimport";
            try {
                if (StringUtils.contains(userAgent, "Mozilla")) {
                    fileName = new String(fileName.getBytes(), "ISO8859-1");
                } else {
                    fileName = URLEncoder.encode(fileName, "utf-8");
                }
                areaSheet.protectSheet("Do you want to know the password");
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=utf-8"); //设置contentType为excel格式
            response.setHeader("content-Disposition", "Attachment;Filename=" + fileName + ".xls");
            //写入到流
            workbook.write(fos);
            fos.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (!Objects.isNull(workbook)) {
                    workbook.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

二.上传excel保存数据并返回错误信息文件

1. 根据传入的excel文件 获取正确的数据集合, 将正确数据保存.
2. 获取正确数据集合的过程对错误信息进行判断整理, 并将错误信息保存.
3. 如果有错误信息, 将错误信息写入文件, 上传云端并返回错误文件路径.

controller层

 /**
     * 导入信息
     *
     * @param request
     * @return
     */
    @RequestMapping("/importAreaHose")
    @ResponseBody
    @ApiOperation(value = "导入信息", notes = "导入信息", httpMethod = "POST")
    public String importAreaHose(MultipartHttpServletRequest request,
                              HttpServletRequest servletRequest,HttpServletResponse response) throws Exception {
        MultipartFile multipartFile = request.getFile("areahouseimport");

        if (Objects.isNull(multipartFile)) {
            return "0";
        }

//    上传文件不对
        if (!".xls".equals(multipartFile.getOriginalFilename().substring(
                multipartFile.getOriginalFilename().indexOf("."),
                multipartFile.getOriginalFilename().length()
        ))) {
            return "-2";
        }
        return leAreaService.importAreaHose(multipartFile.getInputStream(),servletRequest,response);
    }

service层

// 导入信息
 @Override
    public String importAreaHose(InputStream inputStream, HttpServletRequest servletRequest, HttpServletResponse response) {
        try {
            //1, 读取文件中的数据
            List<AreaHouse> areaHouseList = getAreaHourseByFile(inputStream);
            if (CollectionUtils.isEmpty(areaHouseList) && errorAreaHouseMap.size() == 0) {  //为空, 返回0
                return "0";
            }
            //没有错误信息, 正常执行
            if (errorAreaHouseMap.size() == 0) {
                // 2, 遍历添加数据到数据库中
                for (AreaHouse areaHouse : areaHouseList) {
                    submitAreaHouse(areaHouse);
                }
                return "1";
            } else {
                String url = downLoadErrorAreaHouseFile(servletRequest, errorAreaHouseMap, errorMap,inputStream);
                return url;
            }
        } finally {
            if (inputStream!=null) {
                try {
                    inputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

//保存文件导入错误信息
    Map<Integer, String> errorMap = new HashMap<>();
    //保存文件导入错误对象
    Map<Integer, AreaHouse> errorAreaHouseMap = new HashMap<>();
    
 /**
     * 获取文件导入的实体
     *
     * @param inputStream 输入流
     * @return 返回房间信息实体
     */
    private List<AreaHouse> getAreaHourseByFile(InputStream inputStream) {
        //每次调用, 清空
        errorMap.clear();
        errorAreaHouseMap.clear();
        List<AreaHouse> areaHousesList = new ArrayList<>();
        POIFSFileSystem poifsFileSystem = null;
        HSSFWorkbook workbook = null;
        try {
            //1,解析输入流, 获取excel文件对象
            poifsFileSystem = new POIFSFileSystem(inputStream);
            workbook = new HSSFWorkbook(poifsFileSystem);
            //    2, 获取房间信息的sheet表
            HSSFSheet sheet = workbook.getSheetAt(0);
            //    3,遍历每一行获取行数据
            for (int rownum = 1; rownum < sheet.getLastRowNum(); rownum++) {
                HSSFRow row = sheet.getRow(rownum);
                if (row == null) {
                    continue;
                }
                //    4,获取单元格数据
                AreaHouse areaHouse = new AreaHouse();
// 判断数据是否存在和是否有错误
                // 所属小区
                HSSFCell aid = row.getCell(0);

                if (Objects.isNull(aid)||StringUtils.isEmpty(aid.getStringCellValue().trim())) {
                    continue;
                } else {
                    aid.setCellType(CellType.STRING);
                    String[] areaIdAndName = aid.getStringCellValue().split("_");
                    areaHouse.setAreaId(((Long.parseLong(areaIdAndName[1]))));
                    areaHouse.setAreaName(areaIdAndName[0]);
                }

                //    收费标准
                HSSFCell charges = row.getCell(1);
                if (Objects.isNull(charges)||StringUtils.isEmpty(charges.getStringCellValue().trim())) {
                    //如果map中有值就进行值追加
                    if (errorMap.containsKey(rownum) && errorMap.get(rownum) != null) {
                        String value = errorMap.get(rownum) + " ; ";
                        errorMap.put(rownum, value + "收费标准不能为空");
                    } else {
                        errorMap.put(rownum, "收费标准不能为空");
                    }
                } else {
                    charges.setCellType(CellType.STRING);
                    areaHouse.setPropertyChargesId(Long.parseLong(charges.getStringCellValue().split("_")[1]));
                }

              
                //    身份证号码
                HSSFCell cartId = row.getCell(3);
                if (Objects.nonNull(cartId)) {
                    areaHouse.setCardId(cartId.getStringCellValue());
                }
                

              
               
                // 根据小区、楼号、单元、门牌号 查询房间是否重复
                if (areaHouse.getAreaId() != null && areaHouse.getBuildingNumber() != null
                        && areaHouse.getUnitNumber() != null && areaHouse.getHouseNumber() != null) {
                    Map<String, Object> map = new HashMap<>();
                    map.put("areaId", areaHouse.getAreaId());
                    map.put("buildingNumber", areaHouse.getBuildingNumber());
                    map.put("unitNumber", areaHouse.getUnitNumber());
                    map.put("houseNumber", areaHouse.getHouseNumber());
                    int count = getExistsAreaHouse(map);
                    if (count > 0) {
                        if (errorMap.containsKey(rownum) && errorMap.get(rownum) != null) {
                            String value = errorMap.get(rownum) + " ; ";
                            errorMap.put(rownum, value + "小区、楼号、单元、门牌号不能重复");
                        } else {
                            errorMap.put(rownum, "小区、楼号、单元、门牌号不能重复");
                        }
                    }
                }
                //    平米数
                HSSFCell square = row.getCell(9);
                if (square!=null)square.setCellType(CellType.NUMERIC);
                if (Objects.isNull(square)|| square.getNumericCellValue()==0) {
                    if (errorMap.containsKey(rownum) && errorMap.get(rownum) != null) {
                        String value = errorMap.get(rownum) + " ; ";
                        errorMap.put(rownum, value + "平米数不能为空");
                    } else {
                        errorMap.put(rownum, "平米数不能为空");
                    }
                } else {
                    areaHouse.setSquareMeters(new BigDecimal(square.getNumericCellValue()));
                }

                //    物业费到期时间
                HSSFCell dueData = row.getCell(10);
                if (Objects.isNull(dueData)||Objects.isNull(dueData.getDateCellValue())) {
                    if (errorMap.containsKey(rownum) && errorMap.get(rownum) != null) {
                        String value = errorMap.get(rownum) + " ; ";
                        errorMap.put(rownum, value + "物业费到期时间不能为空");
                    } else {
                        errorMap.put(rownum, "物业费到期时间不能为空");
                    }
                } else {
                    //获取时间为Date类型
                    Date dateCellValue = dueData.getDateCellValue();
                    //转换为 LocalDateTime 类型
                    LocalDateTime dueDate = LocalDateTime.ofInstant(dateCellValue.toInstant(), ZoneId.systemDefault());
                    areaHouse.setDueDate(dueDate);
                }
                //    创建时间
                areaHouse.setCreateTime(LocalDateTime.now());
                //默认值
                areaHouse.setType(2);
                if (!errorMap.containsKey(rownum) && errorMap.get(rownum) == null) {
                    areaHousesList.add(areaHouse);
                } else {
                    // 将错误数据放入错误文件夹
                    errorAreaHouseMap.put(rownum, areaHouse);
                }
            }
            return areaHousesList;

        } catch (IOException e) {
            e.printStackTrace();
            return Collections.emptyList();
        }
    }

 /**
     * 上传错误信息的房间文件
     *
     * @param request
     * @param errorAreaHouseMap
     * @param errorMap
     * @return
     */
    private String downLoadErrorAreaHouseFile(HttpServletRequest request,
                                              Map<Integer, AreaHouse> errorAreaHouseMap,
                                              Map<Integer, String> errorMap,
                                              InputStream inputStream) {
        String url = null;
        HSSFWorkbook workbook = null;
        InputStream inputStream2 = null;
        ByteArrayOutputStream bos = null;
        String path = request.getSession().getServletContext().getRealPath("template/areahouseimport_error.xls");

        try {
            inputStream.reset();
            workbook = new HSSFWorkbook(inputStream);
            //    2,获取excel中的工作表 , 根据索引获取
            //添加小区信息表
            HSSFSheet houseSheet = workbook.getSheetAt(0);

            //添加房间数据
            int hlastRowNum = houseSheet.getLastRowNum();

            HSSFRow row11 = houseSheet.getRow(0);
            HSSFCell cell1 = row11.createCell(11);
            cell1.setCellType(CellType.STRING);
            cell1.setCellValue("错误信息");
            //定义红色字体
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFColor.RED.index);
            cellStyle.setFont(font);
            //引用红色字体
            cell1.setCellStyle(cellStyle);

// 设置行数和错误信息
            for (Integer key : errorAreaHouseMap.keySet()) {
                String error = errorMap.get(key);
                HSSFRow row = houseSheet.getRow(key);
                if (row == null) {
                    continue;
                }
                HSSFCell cell = row.getCell(11);
                cell.setCellType(CellType.STRING);
                String oldStringCellValue = cell.getStringCellValue();
                cell.setCellValue(oldStringCellValue+","+error);
                cell.setCellStyle(cellStyle);
            }

            //保存Excel数据到字节流中
            bos = new ByteArrayOutputStream();
            workbook.write(bos);
            byte[] bytes = bos.toByteArray();
            inputStream2 = new ByteArrayInputStream(bytes);

            // 5, 上传文件到云端  (可自行修改)
            url = YunUploadUtils.getInstance().uploadFileToUpYun(
                    upYunMapper.queryUpYunSetting().getUpYunConf(),
                    inputStream2, bytes, path,"UploadOwnerInfoError");

        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (!Objects.isNull(workbook)) {
                    workbook.close();
                }
                if (!Objects.isNull(inputStream2)){
                    inputStream2.close();
                }

                if (!Objects.isNull(bos)){
                    bos.close();
                }

            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return url;
    }