2021-09-24

读取本地的excel文件，处理其中的url数据，使它可以获取到阿里云最新的文件访问链接，并回写到excel最后一列中去

package com.ifs.project.service.impl;

import com.aliyun.oss.HttpMethod;
import com.aliyun.oss.OSS;
import com.aliyun.oss.OSSClientBuilder;
import com.ifs.common.utils.DecryptUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hzero.core.base.BaseConstants;

import java.io.*;
import java.net.URL;
import java.util.Date;

public class ExcelReadAndUpdate {

    public static final String TEST_FILE = "d:doc\\数据提取结果表.xlsx";

    public static void main(String[] args) throws  Exception {
        getIslog(TEST_FILE);
    }


    public static void getIslog(String filepath) {
        OutputStream out = null;
        try {
            File file = new File(filepath);
            BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));
            Workbook workBook = new XSSFWorkbook(in);//获取excel
            Sheet sheet = workBook.getSheetAt(0);//获取第一个sheet页
            int i = 0;//用于显示空行
            for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {//从表格第二行开始遍历
                i += 1;
                Row row = sheet.getRow(rowNum);//获取行
                Cell cell = row.getCell(24);//获取该行的第24列的单元格
                if (null == cell) {
                    System.out.println("null====第" + i + "行");
                    continue;
                }
                cell.setCellType(CellType.STRING);//设置单元格格式，证件号多为纯数字
                String cellVal= cell.getStringCellValue();//获取单元格数据
                String path = StringUtils.substringAfter(cellVal,"com/");
                String fileKey = getFileKey(path);

                Cell cell31 = row.createCell(31);//创建单元格
                cell31.setCellValue(fileKey);//插入单元格数据，不能为空
            }
            out = new FileOutputStream(filepath);
            workBook.write(out);//最后一顶要写入输出流
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static String getFileKey(String filePath) {
        String bucketName = "h****t";

        final String endpoint = "http://oss-cn-shanghai.aliyuncs.com";
        final String accessKeyId = "L******j";
        String accessKeySecret = "*********=";
        String bucketPrefix = "icbs-***-bucket";
        
        // 创建OSSClient实例。
        OSS ossClient = new OSSClientBuilder().build(endpoint, accessKeyId, accessKeySecret);
        // 设置URL过期时间为1小时。
        Date expiration = new Date(System.currentTimeMillis() + 3600 * 1000 * 24);

        // 生成以GET方法访问的签名URL，访客可以直接通过浏览器访问相关内容。
        String bucketString = bucketPrefix + BaseConstants.Symbol.MIDDLE_LINE + bucketName;
        URL url = ossClient.generatePresignedUrl(bucketString,
                filePath, expiration, HttpMethod.GET);

        // 关闭OSSClient。
        ossClient.shutdown();
        return url.toString();
    }

}