SpringBoot使用Excel導入資料

上一次出了一篇關于excel表格導出教程，相信看過的小夥伴已經能把資料庫的資料以excel表格的形式從後端導出到前端了，這一篇文章我們來看看excel的導入，從前端檔案上傳excel表格，後端對excel表格内容進行讀取，并存儲到資料庫。

1.導入依賴:

首先介紹一下我們需要的pom依賴：

<!--easypoi依賴，excel導入導出的主要依賴-->
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-base</artifactId>
      <version>3.0.3</version>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-web</artifactId>
      <version>3.0.3</version>
    </dependency>
    <dependency>
      <groupId>cn.afterturn</groupId>
      <artifactId>easypoi-annotation</artifactId>
      <version>3.0.3</version>
    </dependency>
    <!-- 阿裡 FastJson 依賴 -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.2.57</version>
    </dependency>

2.主要代碼:

1.一個導入導出工具類:

package com.ruankao.common.utils.easyexcel;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @author yangli
 * @description 基于easy-excel的導入導出工具類
 * easy-excel參考網址：http://easypoi.mydoc.io/#text_217704
 * 該工具類來源：https://www.cnblogs.com/better-farther-world2099/articles/11831695.html
 */
public class EasyExcelUtil {
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);

    }
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
        if (workbook != null) {
            ;
        }
        downLoadExcel(fileName, response, workbook);
    }

    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            //throw new NormalException(e.getMessage());
            e.printStackTrace();
        }
    }
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null) {
            ;
        }
        downLoadExcel(fileName, response, workbook);
    }

    public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
        if (StringUtils.isBlank(filePath)){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        }catch (NoSuchElementException e){
            //throw new NormalException("模闆不能為空");
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
            //throw new NormalException(e.getMessage());
        }
        return list;
    }

    /**
     * excel正常導入(預設隻讀取第一張sheet工作簿)
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
        if (file == null){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        }catch (NoSuchElementException e){
            // throw new NormalException("excel檔案不能為空");
            e.printStackTrace();
        } catch (Exception e) {
            //throw new NormalException(e.getMessage());
            e.printStackTrace();
        }
        return list;
    }

    /**
     * excel自定義導入,記得設定titleRows與headRows
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass, ImportParams params){
        if (file == null){
            return null;
        }
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        }catch (NoSuchElementException e){
            // throw new NormalException("excel檔案不能為空");
            e.printStackTrace();
        } catch (Exception e) {
            //throw new NormalException(e.getMessage());
            e.printStackTrace();
        }
        return list;
    }
}

2.一個導入導出的Vo類：

package com.ruankao.common.utils.easyexcel.model;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.ruankao.common.utils.easyexcel.ExcelModelVaild;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;

import javax.validation.constraints.Max;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Size;

/**
 * @author yangli
 * @description 基礎簡單選擇題-excel導入模闆模型
 * @date 2021-02-08
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class BaseEasyChoiceExcel {

    /**
     * value-->表頭 index-->列（從0開始）
     */
    @NotBlank(message = "第1列：題目内容不能為空;")
    @Size(max = 255, message = "第1列：最大長度為255;")
    @Excel(name = "題目内容", orderNum = "0")
    private String content;

    @NotBlank(message = "第2列：選項内容不能為空;")
    @Size(max = 255, message = "第2列：最大長度為255;")
    @Excel(name = "選項A", orderNum = "1")
    private String optionA;

    @NotBlank(message = "第3列：選項内容不能為空;")
    @Size(max = 255, message = "第3列：最大長度為255;")
    @Excel(name = "選項B", orderNum = "2")
    private String optionB;

    @NotBlank(message = "第4列：選項内容不能為空;")
    @Size(max = 255, message = "第4列：最大長度為255;")
    @Excel(name = "選項C", orderNum = "3")
    private String optionC;

    @NotBlank(message = "第5列：選項内容不能為空;")
    @Size(max = 255, message = "第5列：最大長度為255;")
    @Excel(name = "選項D", orderNum = "4")
    private String optionD;

    @NotBlank(message = "第6列：題目答案不能為空;")
    @Size(max = 255, message = "第6列：最大長度為255;")
    @Excel(name = "題目答案", orderNum = "5")
    private String answer;

    @NotBlank(message = "第7列：題目解析不能為空;")
    @Size(max = 255, message = "第7列：最大長度為255;")
    @Excel(name = "題目解析", orderNum = "6")
    private String answerAnalysis;

}

通過上面的工具類與Vo類就可以實作簡單的excel導入了，來看一下簡單的示例代碼：

/**
     * Excel導入
     */
    @PostMapping("/excelUpload")
    public String importExcel(@RequestParam("file") MultipartFile file) {
        //解析excel
        List<BaseEasyChoiceExcel> userList = ExcelUtil.importExcel(file, 0, 1, BaseEasyChoiceExcel.class);
        //也可以使用String filePath = "xxx.xls";importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass)導入
        System.out.println("導入資料一共【" + userList.size() + "】行");
        System.out.println("導入的資料：" + userList);
        //TODO 儲存資料庫

        return "Success";
    }

到此就可以實作簡單的excel導入功能了，當然小夥伴們可以根據業務需求增加資料校驗與異常處理啦，使程式更加健壯。

SpringBoot使用Excel導入資料

1.導入依賴:

2.主要代碼:

繼續閱讀

jsch連接配接伺服器執行指令傳回的資料中文亂碼問題問題分析解決

jeecg-boot的shiro認證過程2. jwtFilter

springboot項目時間不一緻解決方案一、springboot項目時間不一緻解決方案

spring mvc 頁面傳遞數組背景接收方式spring mvc 頁面傳遞數組背景接收方式

Excel如何将小寫轉大寫金額

個項目的開發過程及成員組成

SpringSecurity整合短信驗證碼認證

基于Springboot+VUE+Mysql的大學生校園疫情防控管理系統

Swagger-UI的使用一、添加依賴二、建立配置類三、注解

将swagger-ui中的接口導入postman

聊一聊Swagger-UI的那些事情軟體簡介Swagger 原生Bootstrap-ui Bootstrap的UIKnife4j Bootstrap的更新版的UI

springboot使用 swagger-ui 2.10.5 有關版本更新帶來的問題

微服務-性能壓測\緩存redis和分布式鎖redisson和SpringCache

java 8 Lamda 求 List 中 Bigdecimal類型的各種值求最大值求最小值求和求平均值

(SpringBoot)日志種類：log、monitor、access、out、gc、backup

spring boot @SpringBootApplication @EnableAutoConfiguration 的工作原理