导入导出excel

上传文件到阿里云oss。

1，查看oss“上传文件”java参考文档

2，传入模板方法要求的参数，MultipartFile接口里存储对应上传文件，相当于上传文件的对象。

3，把需要的参数存入log表里（url之类）

下载文件从oss到服务器。

方法1，使用apach common包下的，FileUtils.copyURLToFile(URL,File)方法，可以根据url下载。

方法2，查看阿里云oss的“下载文件”java参考文档。

下载文件从服务器到客户端

用HttpServletResponse，流的方式调用浏览器下载。

导入excel

 hutool官网查看，主要是把excel转为pojo类

1，得到输入流，ExcelReader reader = ExcelUtil.getReader(ResourceUtil.getStream("aaa.xlsx"));

2，得到内容，List<T> all = reader.read()。官网还有其他方法，不推荐这个方法，因为不能去空格。

3，转为pojo类

第一行是头部信息，从第二行到第2147483647行。

导出excel

poi工具包把pojo转为excel。输出到服务器。

 hutool工具包，给poi做了封装，输出到服务器后，用流的方式从服务器下载到客户端。

    /**
     * 离职导入
     */
    @Override
    public BaseResult proImportWithdrawn(Long fileId, Long userId){
        UploadLogDto byId = uploadLogFeignApi.getById(fileId);
        //去oss上拉取文件到本地
        String s = DownloadURLFile.downloadFromUrl(byId.getFileUrl(), "/tmp/");
        String fileName = DownloadURLFile.getFileNameFromUrl(byId.getFileUrl());
        if (s.equals("Successful!")) {
            //解析文件
            FileInputStream fis = null;
            List<BusiImportWithdrawnVo> pojoList = null;
            try {
                fis= new FileInputStream("/tmp/" + fileName);
                LinkedHashMap<String, String> alias = new LinkedHashMap<>();
                alias.put("姓名", "name");
                alias.put("身份证", "idCardValue");
                alias.put("手机号", "mobile");
                alias.put("企业名称", "corpName");
                alias.put("落地仓名称", "locationName");
                alias.put("落地仓编号", "locationCode");
                alias.put("产品名称", "productName");
                alias.put("产品编号", "productId");
                alias.put("银行卡号", "accountNumber");
                alias.put("开户行", "bankName");
                alias.put("银行卡类型（1:银行卡，2支付宝）", "accountType");
                pojoList = ExcelUtil.excel2Pojo(fis, BusiImportWithdrawnVo.class, alias, 0, 1);
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

   //下面2个是导出
 @Override
    public void exportOrderByRequisition(Long requisitionId, HttpServletResponse response) {
        if(requisitionId == null){
            throw new CommonException(ResultCode.ORDER50027).msgData("申请单ID不能为空");
        }
        RequisitionListVo requisitionListVo = requisitionBaseMapper.getBaseRequisitionById(requisitionId);
        List<RequisitionOrderExcelVo> orderListVoList = requisitionOrderMapper.getOrderListByRequisitionId(requisitionId);
        if(requisitionListVo != null){
            int listSize = orderListVoList != null ? orderListVoList.size() :0;
            String headLine = "【" + (StringUtils.isNotEmpty(requisitionListVo.getRequisitionCode()) ? requisitionListVo.getRequisitionCode() : "")  +"】请款单明细";
            ExcelWriter writer = createHeaderExcel(requisitionListVo,listSize,headLine);
            try {
                HuToolExcelUtil.export(response, RequisitionOrderExcelVo.class, orderListVoList,writer);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 请款单信息封装excel信息
     * @param requisitionListVo
     * @param listSize
     * @param headLine
     * @return
     */
    private ExcelWriter createHeaderExcel(RequisitionListVo requisitionListVo,int listSize,String headLine){
        ExcelWriter writer = null;
        if(requisitionListVo != null){
            if (listSize > 60000){
                writer = ExcelUtil.getBigWriter();
            }else {
                writer = ExcelUtil.getWriter();
            }
            writer.merge(0, 1, 0, 10, headLine, false);
            writer.merge(2, 2, 0, 1, "委托企业", false);
            writer.merge(2, 2, 2, 5, StringUtils.isNotEmpty(requisitionListVo.getCorpName()) ? requisitionListVo.getCorpName() : "", false);
            writer.merge(2, 2, 6, 7, "归属项目", false);
            writer.merge(2, 2, 8, 10, StringUtils.isNotEmpty(requisitionListVo.getProjectName()) ? requisitionListVo.getProjectName() : "", false);
            writer.merge(3, 3, 0, 1, "园区公司", false);
            writer.merge(3, 3, 2, 5, StringUtils.isNotEmpty(requisitionListVo.getParkCorpName()) ? requisitionListVo.getParkCorpName() : "", false);
            writer.merge(3, 3, 6, 7, "出款账号", false);
            writer.merge(3, 3, 8, 10, StringUtils.isNotEmpty(requisitionListVo.getPeyerBankAccount()) ? requisitionListVo.getPeyerBankAccount() : "", false);
            writer.merge(4, 4, 0, 1, "创建时间", false);
            writer.merge(4, 4, 2, 5, requisitionListVo.getCreateTime() != null ? requisitionListVo.getCreateTime() : "", false);
            writer.merge(4, 4, 6, 7, "请款单状态", false);
            writer.merge(4, 4, 8, 10, StringUtils.isNotEmpty(requisitionListVo.getRequisitionStatusName()) ? requisitionListVo.getRequisitionStatusName() : "", false);
            writer.merge(5, 5, 0, 1, "总笔数", false);
            writer.merge(5, 5, 2, 5, requisitionListVo.getPaymentSum() != null ? requisitionListVo.getPaymentSum() : "", false);
            writer.merge(5, 5, 6, 7, "总金额（元）", false);
            writer.merge(5, 5, 8, 10, requisitionListVo.getPaymentAmount() != null ? requisitionListVo.getPaymentAmount() : "", false);
            writer.merge(6, 6, 0, 1, "成功笔数", false);
            writer.merge(6, 6, 2, 5, requisitionListVo.getSuccessSum() != null ? requisitionListVo.getSuccessSum() : "", false);
            writer.merge(6, 6, 6, 7, "实发金额（元）", false);
            writer.merge(6, 6, 8, 10, requisitionListVo.getRealAmount() != null ? requisitionListVo.getRealAmount() : "", false);
            writer.passRows(8);
        }
        return writer;
    }

 导出，

    /**
     * 响应式导出订单
     * @param searchParam
     * @param response
     */
    @Override
    public void exportOrdersNew(String searchParam, HttpServletResponse response) {
        SearchOrdersVo searchOrdersVo = JSONObject.parseObject(searchParam, SearchOrdersVo.class);
        if (searchOrdersVo == null) {
            searchOrdersVo = new SearchOrdersVo();
        }
        List<ExportOrderListVo> list = orderBaseMapper.exportOrdersNew(searchOrdersVo);
        try {
            HuToolExcelUtil.export(response, ExportOrderListVo.class, list);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }