devbox
Monodyee
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

生成Excel文件并上传到阿里云OSS_excel oss

作者：Monodyee | 2024-05-20 20:19:35

赞

踩

excel oss

代码

		@Resource
	    private ExcelUtils excelUtils;
		@Async("doSomethingExecutor")
        Future doCreateStockOutExcel(Date endTime, Date beginTime, Object storeNo) {
            List<StockOutEo> stockOutEoList = stockOutMapper.findByStoreNo(storeNo.toString(), beginTime, endTime);
            //生成记录在缺货报表列表
            if (EmptyUtil.notEmpty(stockOutEoList)) {
                //在内存中创建一个Excel文件
                XSSFWorkbook workbook = new XSSFWorkbook();
                sheetStockOut(workbook, stockOutEoList);
                String excelName = DateUtil.dateStr6(beginTime) + ".xls";
                String dir = storeNo.toString() + "/" + StockOutEo.EXCEL_DIR;
                String downloadUrl = aliOssUtils.putExcel(workbook, dir, excelName);
                createStockOutExcel(downloadUrl, storeNo.toString(), dir + "/" + excelName, 1);
            } else {
                createStockOutExcel(null, storeNo.toString(), null, 0);
            }
            return new AsyncResult(null);
        }
        
        private void sheetStockOut(XSSFWorkbook workbook, List<StockOutEo> stockOutEoList) {
        XSSFSheet sheet = workbook.createSheet("缺货报表");
        //创建行，0表示第一行
        XSSFRow row = sheet.createRow(0);
        //创建单元格，0表示第一个单元格
        row.createCell(0).setCellValue("缺货报表");
        XSSFRow row1 = sheet.createRow(1);
        ExcelUtils.setTableForm(row1, ExcelUtils.stockOutLists);
        excelUtils.setStockOutTableData(sheet, stockOutEoList);
    }

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30

创建EXCEL工具类

@Component
public class ExcelUtils {

    @Resource
    private P360RequestUtils p360RequestUtils;

    @Resource
    private OsLog osLog;

    @Resource
    private RtisUtil rtisUtil;

    public static List<String> collectRowLists = Arrays.asList("移动收银", "自助收银", "扫码购", "实体销售(不含实体POS收银机)", "屈臣氏云店", "到店业务", "到家业务", "平台销售", "总计");

    public static List<String> collectLists = Arrays.asList("业务类型", "销售额", "会员销售额占比", "销售笔数", "客单价", "销售件数", "客单量", "OB销售额", "EB销售额", "OB销售额占比", "EB销售额占比", "会员卡销售量");
    public static List<String> totalLists = Arrays.asList("订单编号", "订单日期", "订单实付金额", "销售人员", "商品编码", "商品明细", "销售数量", "商品含税销售金额", "订单状态", "退货数量");
    public static List<String> detailLists = Arrays.asList("订单号", "订单日期", "订单渠道", "订单类型", "部门", "品牌", "货号", "商品名称", "数量", "销售金额", "实付金额", "销售人员");
    public static List<String> stockOutLists = Arrays.asList("日期", " 商品编码", "名称", "条形编码", "20点实时库存", "盘点数量", "备注");

    public static void setTableForm(XSSFRow row, List<String> tableFormList) {
        for (int index = 0; index < tableFormList.size(); index++) {
            row.createCell(index).setCellValue(tableFormList.get(index));
        }
    }

    public static void setCollectTableData(XSSFSheet sheet, int index, List<SaleCollectDTO> dataList) {

    }

    public void setDetailTableData(XSSFSheet sheet, List<SaleDetailDTO> dataList) {
        if (null != dataList && !dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                SaleDetailDTO saleDetailDTO = dataList.get(i);
                XSSFRow row = sheet.createRow(i + 2);
                row.createCell(0).setCellValue(saleDetailDTO.getItemNo());
                row.createCell(1).setCellValue(saleDetailDTO.getOrderTime());
                row.createCell(2).setCellValue(saleDetailDTO.getOrderChannel());
                row.createCell(3).setCellValue(saleDetailDTO.getOrderType());
                row.createCell(4).setCellValue(saleDetailDTO.getDepartment());
                row.createCell(5).setCellValue(saleDetailDTO.getBrand());
                row.createCell(6).setCellValue(saleDetailDTO.getItemNo());
                row.createCell(7).setCellValue(saleDetailDTO.getItemNameZht());
                row.createCell(8).setCellValue(saleDetailDTO.getOrderQty());
                row.createCell(9).setCellValue(saleDetailDTO.getOriginPrice().toString());
                row.createCell(10).setCellValue(saleDetailDTO.getActualPrice().toString());
                row.createCell(11).setCellValue(saleDetailDTO.getOptName());
            }
        }
    }

    /**
     * 设置缺货数据
     *
     * @param sheet    sheet
     * @param dataList dataList
     */
    public void setStockOutTableData(XSSFSheet sheet, List<StockOutEo> dataList) {
        if (null != dataList && !dataList.isEmpty()) {
            for (int i = 0; i < dataList.size(); i++) {
                StockOutEo stockOutDTO = dataList.get(i);
                XSSFRow row = sheet.createRow(i + 2);
                //日期
                row.createCell(0).setCellValue(DateUtil.dateStr2(stockOutDTO.getCreateTime()));
                //商品编码
                row.createCell(1).setCellValue(stockOutDTO.getItemNo());
                //名称
                row.createCell(2).setCellValue(stockOutDTO.getItemNameZht());
                //条形编码
                try {
                    AppointProductByItemResp appointProductByItemResp = p360RequestUtils.requestItemDetail(Arrays.asList(stockOutDTO.getItemNo()), stockOutDTO.getStoreNo());
                    if (EmptyUtil.notEmpty(appointProductByItemResp) && appointProductByItemResp.getSuccess()) {
                        ProductList data = appointProductByItemResp.getData();
                        if (EmptyUtil.notEmpty(data) && EmptyUtil.notEmpty(data.getProductList())) {
                            List<Product> productList = data.getProductList();
                            productList.forEach(a -> {
                                String barcode = a.getProductBase().getBarcode();
                                row.createCell(3).setCellValue(barcode);
                            });
                        }
                    }
                } catch (Exception e) {
                    osLog.error(ResultEnum.P360_GET_PRODUCT.getCode(), ResultEnum.P360_GET_PRODUCT.getMsg(), e);
                }
                //实时库存
                try {
                    AvaliableStock availableStock = rtisUtil.getAvailableStock(Arrays.asList(stockOutDTO.getItemNo()), Arrays.asList(stockOutDTO.getStoreNo()));
                    if (EmptyUtil.notEmpty(availableStock) && availableStock.getSuccess()) {
                        StoreList data = availableStock.getData();
                        if (EmptyUtil.notEmpty(data)) {
                            List<Store> storeList = data.getStoreList();
                            if (EmptyUtil.notEmpty(storeList)) {
                                row.createCell(4).setCellValue(storeList.get(0).getQty());
                            }
                        }
                    }
                } catch (Exception e) {
                    osLog.error(ResultEnum.RTIS_GET_PRODUCT.getCode(), ResultEnum.RTIS_GET_PRODUCT.getMsg(), e);
                }
                //盘点数量
                row.createCell(5).setCellValue("");
                //备注
                row.createCell(6).setCellValue("");
            }
        }
    }

}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107

OSS工具类

@Component
public class AliOssUtils {
    @Value("${oss.ali.endpoint:}")
    private String endpoint;
    @Value("${oss.ali.accessKeyId:}")
    private String accessKeyId;
    @Value("${oss.ali.accessKeySecret:}")
    private String accessKeySecret;
    @Value("${oss.ali.bucketName:}")
    private String bucketName;
    @Value("${oss.ali.domainUrl:}")
    private String domainUrl;

    @Resource
    private OsLog osLog;

    public OSS getOssClient() {
        // 创建OSSClient实例。
        return new OSSClientBuilder().build(endpoint, accessKeyId, accessKeySecret);
    }

    public String getDomainUrl() {
        return domainUrl;
    }

    /**
     * 上传excel
     *
     * @param workbook
     * @param dir       目录
     * @param excelName 文件名
     * @return
     */
    public String putExcel(XSSFWorkbook workbook, String dir, String excelName) {
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            workbook.write(bos);
        } catch (IOException e) {
        } finally {
            try {
                bos.close();
            } catch (IOException e) {
            }
        }
        byte[] brray = bos.toByteArray();
        InputStream is = new ByteArrayInputStream(brray);
        OSS ossClient = getOssClient();
        try {
            // 创建PutObjectRequest对象。
            PutObjectRequest putObjectRequest = new PutObjectRequest(bucketName, dir + "/" + excelName, is);
            // 如果需要上传时设置存储类型和访问权限，请参考以下示例代码。
            // ObjectMetadata metadata = new ObjectMetadata();
            // metadata.setHeader(OSSHeaders.OSS_STORAGE_CLASS, StorageClass.Standard.toString());
            // metadata.setObjectAcl(CannedAccessControlList.Private);
            // putObjectRequest.setMetadata(metadata);
            // 上传文件
            ossClient.putObject(putObjectRequest);
            //设置过期时间
            LocalDateTime time = LocalDateTime.now().plusDays(5);
            Date expiration = Date.from(time.atZone(ZoneId.systemDefault()).toInstant());
            return ossClient.generatePresignedUrl(bucketName, dir + "/" + excelName, expiration).toString();
        } catch (OSSException oe) {
            osLog.error(ResultEnum.SYS_ERROR.getCode(), ResultEnum.SYS_ERROR.getMsg(), oe.getErrorMessage());
        } catch (ClientException ce) {
            osLog.error(ResultEnum.SYS_ERROR.getCode(), ResultEnum.SYS_ERROR.getMsg(), ce.getErrorMessage());
        } finally {
            if (ossClient != null) {
                ossClient.shutdown();
            }
        }
        return null;
    }

    public void deleteExcel(String excelUrl) {
        if (EmptyUtil.notEmpty(excelUrl)) {
            OSS ossClient = getOssClient();
            try {
                boolean b = ossClient.doesObjectExist(bucketName, excelUrl);
                if (b) {
                    ossClient.deleteObject(bucketName, excelUrl);
                }
            } catch (OSSException oe) {
                osLog.error(ResultEnum.SYS_ERROR.getCode(), ResultEnum.SYS_ERROR.getMsg(), oe.getErrorMessage());
            } catch (ClientException ce) {
                osLog.error(ResultEnum.SYS_ERROR.getCode(), ResultEnum.SYS_ERROR.getMsg(), ce.getErrorMessage());
            } finally {
                if (EmptyUtil.notEmpty(ossClient)) {
                    ossClient.shutdown();
                }
            }
        }

    }
}

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/Monodyee/article/detail/599158
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号