通過 POI 將資料庫中的資料上傳至 OSS 物件儲存

表格的具體樣式可以參考 第三章

我們以 aliyun 的 OSS 為例上傳 上傳方法

一、準備工作

第一步：引入 Apache POI 的依賴

<!-- Maven 方式 -->

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.0</version>
</dependency>

<!-- Gradle 方式 -->

compile group: 'org.apache.poi', name: 'poi', version: '4.0.0'

第二步：引入 JUnit 測試，這裡就不做贅述了。

二、講解測試用例

Test方法

我們先從測試方法著手，再逐步講解測試中所用到的方法。

@Autowired
private UserService userService;

@Test
public void upload(){
    List<User> users = userService.findAll(); // ①

    final ByteArrayOutputStream stream = exportProjectList(users); // ②

    String uri = uploadWorkbook(stream, "學生資料表.xlsx"); // ③

    System.out.println(uri); // ④
}

1. 從資料庫中取出需要匯入 Excel 的資料
2. 方法：exportProjectList 將資料存入 POI 生成的 Excel 中，並將 Excel 轉換為位元組流，用來向物件儲存中上傳
3. 方法：uploadWorkbook 上傳流式檔案，和檔名稱
4. 輸出 aliyun 返回的地址，用於業務的實現，比如 儲存至資料庫 等。

exportProjectList方法

作用：接收從資料庫查詢出來的物件集合，將其插入到 Workbook 中，並生成二進位制的輸出流 ByteArrayOutputStream

表格的具體樣式可以參考 第三章

private ByteArrayOutputStream exportProjectList(User[] users) throws IOException {
        // Workbook 工作區
        final XSSFWorkbook workbook = new XSSFWorkbook();

        // 單字型居中樣式
        final XSSFCellStyle centerStyle = workbook.createCellStyle();
        centerStyle.setAlignment(HorizontalAlignment.CENTER);

        // 字型居中加粗樣式
        final XSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);

        final XSSFFont font = workbook.createFont();
        font.setBold(true);
        headerStyle.setFont(font);

        // Sheet 分頁
        final XSSFSheet sheet = workbook.createSheet("Sheet Name");

        final String[] strings = new String[]{"序號", "學號", "姓名", "年齡", "院系", "班級"};

        // 首行內容填充，以及設定樣式
        setFirstRow(sheet, strings, headerStyle);

        int rowSign = 0;
        for (user user : users) {

            // Row 行
            final XSSFRow row = sheet.createRow(++rowSign);

            // Cell 單元格
            final XSSFCell firstCell = row.createCell(0);
            firstCell.setCellValue(rowSign);
            firstCell.setCellStyle(centerStyle);

            final XSSFCell secondCell = row.createCell(1);
            secondCell.setCellValue(user.getStuNo());
            secondCell.setCellStyle(centerStyle);

            final XSSFCell thirdCell = row.createCell(2);
            thirdCell.setCellValue(user.getName());
            thirdCell.setCellStyle(centerStyle);

            final XSSFCell fourthCell = row.createCell(3);
            fourthCell.setCellValue(user.getAge());
            fourthCell.setCellStyle(centerStyle);

            final XSSFCell fifthCell = row.createCell(4);
            fifthCell.setCellValue(user.getCollege());
            fifthCell.setCellStyle(centerStyle);

            final XSSFCell sixthCell = row.createCell(5);
            sixthCell.setCellValue(user.getClass());
            sixthCell.setCellStyle(centerStyle);
        }

        // 寬度自適應
        for (int s = 0; s < strings.length; s++) {
            sheet.autoSizeColumn(s);
            sheet.setColumnWidth(s, sheet.getColumnWidth(s) * 17 / 10);
        }

        final ByteArrayOutputStream stream = new ByteArrayOutputStream();

        workbook.write(stream);

        return stream;
    }

需要注意的是，每一個單元格 (Cell) 都是不能為空的。

我們將設定頭標題的方法抽離出來，可供其他設定的方法使用。

/**
 * 設定第一行的頭標題
 */
private void setFirstRow(Sheet sheet, String[] cellTitles, XSSFCellStyle headerStyle) {
    final Row row = sheet.createRow(0);

    int cellSign = 0;

    for (String cellName : cellTitles) {
        final Cell cell = row.createCell(cellSign++);
        cell.setCellValue(cellName);
        cell.setCellStyle(headerStyle);
    }
}

在做完前面的工作之後，我們就可以開始寫測試方法了

uploadWorkbook方法

該方法用於：接收 Workbook 生成的流，並將其儲存至 OSS 上，本方法適用於 aliyun 的 OSS 物件儲存，其他的具體實現都可以從官方API中查到。

private String uploadWorkbook(ByteArrayOutputStream stream, String fileId) throws IOException {
    final ByteArrayInputStream inputStream = new ByteArrayInputStream(stream.toByteArray()); // ①

    ObjectMetadata objectMetadata = new ObjectMetadata();
    objectMetadata.setContentLength(stream.size());
    objectMetadata.setContentEncoding("utf-8");
    objectMetadata.setCacheControl("no-cache");
    objectMetadata.setHeader("Pragma", "no-cache");
    objectMetadata.setContentDisposition("inline;filename=" + "學生表資料.xlsx"); // ②

    return ossCore.uploadByStream(fileId, objectMetadata, inputStream); // ③
}

1. 將輸出流轉換為輸入流
2. 儲存物件的媒體資訊
3. 上傳至 aliyun 的 OSS 物件儲存，具體實現可以看 第四部分

三、設定Excel單元格樣式

建立sheet

HSSFCellStyle cellStyle = wb.createCellStyle();  

一、設定背景色:

cellStyle.setFillForegroundColor((short) 13);// 設定背景色  
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  

二、設定邊框:

cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下邊框  
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左邊框  
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上邊框  
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右邊框  

三、設定居中:

cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中  

四、設定字型:

HSSFFont font = wb.createFont();  
font.setFontName("黑體");  
font.setFontHeightInPoints((short) 16);//設定字型大小  
  
HSSFFont font2 = wb.createFont();  
font2.setFontName("仿宋_GB2312");  
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗體顯示  
font2.setFontHeightInPoints((short) 12);  
  
cellStyle.setFont(font);//選擇需要用到的字型格式  

五、設定列寬:

sheet.setColumnWidth(0, 3766); //第一個引數代表列id(從0開始),第2個引數代表寬度值  參考 ："2012-08-10"的寬度為2500  

六、設定自動換行:

cellStyle.setWrapText(true);//設定自動換行  

七、合併單元格:

Region region1 = new Region(0, (short) 0, 0, (short) 6);//引數1：行號 引數2：起始列號 引數3：行號 引數4：終止列號
//此方法在POI3.8中已經被廢棄，建議使用下面一個  
或者用

CellRangeAddress region1 = new CellRangeAddress(rowNumber, rowNumber, (short) 0, (short) 11);

//引數1：起始行 引數2：終止行 引數3：起始列 引數4：終止列
但應注意兩個構造方法的引數不是一樣的，具體使用哪個取決於POI的不同版本。


sheet.addMergedRegion(region1);

四、上傳至OSS等物件儲存

作用：上傳 stream流 至 OSS

public String uploadByStream(String fileId, ObjectMetadata metadata, InputStream in) {
        String ossUrl = null;
        OSSClient ossClient = new OSSClient(ENDPOINT, ACCESS_KEY_ID, ACCESS_KEY_SECRET);
        ossClientMap.put(fileId, ossClient);
        try {
            putUploadProgress(fileId, 0);
            PutObjectRequest req = new PutObjectRequest(OssConnectionUtil.getBucketName(), fileId, in, metadata);
            req.setProgressListener(new UploadProgressListener(req));
            PutObjectResult putResult = ossClient.putObject(req);
            if (putResult.getETag() != null) {
                ossUrl =  getOssURL(ossClient, fileId);
            }
            return ossUrl;
        } catch(ClientException ce) {
            return null;
        } catch (Exception e) {
            return null;
        } finally {
            ossClient.shutdown();
            ossClientMap.remove(fileId);
        }
    }