Springboot2.x整合POI實作資料庫資料下載下傳（檔案形式）

一、環境介紹：

• springboot:2.1.8
• poi:3.10
• Java:1.8
• maven:3.3.9
• mybatis:1.3.1

二、搭建步驟：

1. 引入POI的maven依賴

<dependencies>
	<!--poi導入導出-->
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi</artifactId>
          <version>3.10-FINAL</version>
      </dependency>
      <dependency>
          <groupId>org.apache.poi</groupId>
          <artifactId>poi-ooxml</artifactId>
          <version>3.10.1</version>
      </dependency>
 </dependencies>
           

1. 持久化層

   Mapper接口如下所示：

Mapper檔案如下所示：

<select id="exportUser" resultType="java.util.Map">
    select user_id as userId, username, email, mobile, create_time as createTime from sys_user
  </select>
           

1. 服務層

public Workbook exportUser() {
        //1，建立了一個空的excel檔案
        Workbook workbook = new HSSFWorkbook();
        //2,填充資料：建立sheet
        Sheet sheet = workbook.createSheet("某某公司的員工資訊");
        //标題數組
        String titles[] ={"使用者id","使用者名","郵箱","電話","建立時間"};
        String colums[] ={"userId", "username", "email", "mobile", "createTime"};
        List<Map<String, Object>> maps = sysUserMapper.exportUser();
        Row rowTile = sheet.createRow(0);
        //标題行
        for (int i = 0; i <titles.length ; i++) {
            Cell cell = rowTile.createCell(i);
            cell.setCellValue(titles[i]);
        }
        //周遊資料填充到單元格
        for (int i = 0; i <maps.size() ; i++) {
            //一條記錄應該建立一個Row對象 這裡從第二行開始是以+1
            Row row = sheet.createRow(i+1);//這個是空的，需要填充資料
            //填充單元格
            for (int j = 0; j < titles.length; j++) {
                Cell cell = row.createCell(j);
                //擷取使用者id的值
                Map<String, Object> rowValue = maps.get(i);
                //循環動态設定多個字段的值
                Object o = rowValue.get(colums[j]);//這裡擷取的值可以是"userId"..
                //這裡也就是為什麼查詢資料庫使用map封裝的原因。
                cell.setCellValue(o+"");
            }
        }
        return workbook;
    }
           

1. 表現層

@RequestMapping("/sys/user/export")
    public void exportUser(HttpServletResponse response){
        Workbook workbook = sysUserService.exportUser();
        try {
            //設定響應頭
            response.setContentType("application/octet-stream");//所有檔案都支援
            String fileName = "員工資訊.xls";
            fileName = URLEncoder.encode(fileName,"utf-8");
            response.setHeader("content-disposition","attachment;filename="+fileName);
            //檔案下載下傳
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }