SpringBoot整合EasyExcel實現導入導出數據

Posted on 2022-05-14 by WalkonNet

前言

創建一個普通的maven項目即可

項目目錄結構

1.前端

存放在resources/static 下

index.html

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="UTF-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Document</title>
    <!-- 開發環境版本，包含瞭有幫助的命令行警告 -->
    <script src="https://cdn.jsdelivr.net/npm/vue@2/dist/vue.js"></script>
    <!-- 引入樣式 -->
    <link
      rel="stylesheet"
      href="https://unpkg.com/element-ui/lib/theme-chalk/index.css" rel="external nofollow" 
    />
    <!-- 引入組件庫 -->
    <script src="https://unpkg.com/element-ui/lib/index.js"></script>
    <script src="https://unpkg.com/axios/dist/axios.min.js"></script>
  </head>
  <body>
    <div id="app">
      <div class="app-container">
        <div style="margin-bottom: 10px">
          <el-button
            @click="dialogVisible = true"
            type="primary"
            size="mini"
            icon="el-icon-download"
          >
            導入Excel
          </el-button>
          <el-dialog
            title="數據字典導入"
            :visible.sync="dialogVisible"
            width="30%"
          >
            <el-form>
              <el-form-item label="請選擇Excel文件">
                <el-upload
                  :auto-upload="true"
                  :multiple="false"
                  :limit="1"
                  :on-exceed="fileUploadExceed"
                  :on-success="fileUploadSuccess"
                  :on-error="fileUploadError"
                  :action="importUrl"
                  name="file"
                  accept="application/vnd.ms-excel,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                >
                  <!--accept 隻接受某種格式的文件-->
                  <el-button size="small" type="primary">點擊上傳</el-button>
                </el-upload>
              </el-form-item>
            </el-form>
            <div slot="footer" class="dialog-footer">
              <el-button @click="dialogVisible = false">取消</el-button>
            </div>
          </el-dialog>

          <!-- 導出 -->
          <el-button
            @click="exportData"
            type="primary"
            size="mini"
            icon="el-icon-upload2"
          >
            導出Excel
          </el-button>

          <!-- 數據展示 -->
          <el-table :data="list" stripe style="width: 100%">
            <el-table-column prop="name" label="姓名" width="180">
            </el-table-column>
            <el-table-column prop="birthday" label="生日" width="180">
            </el-table-column>
            <el-table-column prop="salary" label="薪資"> </el-table-column>
          </el-table>
          <div>
            <el-pagination
              @size-change="handleSizeChange"
              @current-change="handleCurrentChange"
              :current-page="pageNum"
              :page-sizes="[2, 5, 10,  20]"
              :page-size="pageSize"
              background
              layout="total, sizes, prev, pager, next, jumper"
              :total="total"
            >
            </el-pagination>
          </div>
        </div>
      </div>
    </div>
  </body>
  <script>
    new Vue({
      el: '#app',
      data() {
        return {
          dialogVisible: false, //文件上傳對話框是否顯示
          list: [], // 字典的數據
          importUrl: 'http://localhost:8811/api/excel/import',
          pageNum: 1, // 頁數
          pageSize: 5, // 每頁條數
          total: 1000,
        }
      },
      created() {
        this.showList()
      },
      methods: {
        showList() {
          //使用自定義配置
          const request = axios.create({
            baseURL: 'http://localhost:8811', //url前綴
            timeout: 1000, //超時時間
            // headers: { token: 'helen123456' }, //攜帶令牌
          })
          request
            .get('/api/excel/list', {
              params: {
                pageNum: this.pageNum,
                pageSize: this.pageSize,
              },
            })
            .then((res) => {
              this.total = res.data.size
              this.list = res.data.list
              console.log(res)
            })
        },
        // 上傳多於一個文件時
        fileUploadExceed() {
          this.$message.warning('隻能選取一個文件')
        },
		// 導出
        exportData() {
          window.location.href = 'http://localhost:8811/api/excel/export'
        },

        //上傳成功回調
        fileUploadSuccess(response) {
          if (response.code === 0) {
            this.$message.success('數據導入成功')
            this.dialogVisible = false
          } else {
            this.$message.error(response.message)
          }
        },

        //上傳失敗回調
        fileUploadError(error) {
          this.$message.error('數據導入失敗')
        },
        /**
         * 用戶所選擇當前頁面展示的數據條數
         */
        handleSizeChange(val) {
          console.log(`每頁 ${val} 條`)
          this.pageSize = val
          this.showList()
        },
        handleCurrentChange(val) {
          console.log(`當前頁: ${val}`)
          this.pageNum = val
          this.showList()
        },
      },
    })
  </script>
</html>

2.數據庫

CREATE TABLE `student` (
  `name` varchar(255) DEFAULT NULL COMMENT '姓名',
  `birthday` datetime DEFAULT NULL COMMENT '生日',
  `salary` decimal(10,4) DEFAULT NULL COMMENT '薪資'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3.後端

3.1 contrller

StudentController

@Slf4j
@RestController
@CrossOrigin
@RequestMapping("/api/excel/")
public class StudentController {

    @Resource
    private StudentMapper studentMapper;

    @GetMapping("list")
    public HashMap<String, Object> list(@RequestParam int pageNum,@RequestParam int pageSize){
        // 分頁查詢
        Page<Student> page = new Page<>(pageNum, pageSize);
        studentMapper.selectPage(page,null);
        
        // 封裝數據
        HashMap<String, Object> map = new HashMap<>();
        ArrayList<ExcelStudentDTO> excelDictDTOList = new ArrayList<>();
        // 轉換數據
        page.getRecords().forEach(student -> {
            ExcelStudentDTO studentDTO = new ExcelStudentDTO();
            BeanUtils.copyProperties(student,studentDTO);
            excelDictDTOList.add(studentDTO);
        });
        
        map.put("list",excelDictDTOList);
        map.put("size",page.getTotal());
        return map;
    }

    /**
     * 導入
     * @param file 文件對象
     */
    @RequestMapping("import")
    @Transactional(rollbackFor = {Exception.class})
    public String importData( @RequestParam("file") MultipartFile file){
        try {
            // 讀取文件流
            EasyExcel.read
                    (file.getInputStream(),// 前端上傳的文件
                            ExcelStudentDTO.class,// 跟excel對應的實體類
                            new ExcelDictDTOListener(studentMapper))// 監聽器 
                    .excelType(ExcelTypeEnum.XLSX)// excel的類型
                    .sheet("模板").doRead();
            log.info("importData finished");
        } catch (IOException e) {
           log.info("失敗");
           e.printStackTrace();
        }
        return "上傳成功";
    }

    /**
     * 導入
     */
    @GetMapping("export")
    public String exportData(HttpServletResponse response){

        try {
            // 設置響應體內容
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");

            // 這裡URLEncoder.encode可以防止中文亂碼 當然和easyexcel沒有關系
            String fileName = URLEncoder.encode("myStu", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            EasyExcel.write(response.getOutputStream()
                    ,ExcelStudentDTO.class).sheet().doWrite(studentMapper.selectList(null));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "上傳成功";
    }

}

3.2 mapper

StudentMapper

@Mapper
public interface StudentMapper extends BaseMapper<Student> {
    void insertBatch(List<ExcelStudentDTO> list);
}

StudentMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="look.word.mapper.StudentMapper">
  <insert id="insertBatch" >
    insert into student(name, birthday, salary)
    values
    <foreach collection="list"   item="item" separator=",">
               (
        #{item.name} ,
        #{item.birthday} ,
        #{item.salary} 
        )
    </foreach>
  </insert>
</mapper>

3.3 bean

ExcelStudentDTO

導入數據時要保證excel中列名和ExcelStudentDTO一致奧

/**
 * excel對應的實體類
 * @author jiejie
 */
@Data
public class ExcelStudentDTO {
	// excel中的列名
    @ExcelProperty("姓名")
    private String name;

    @ExcelProperty("生日")
    private Date birthday;

    @ExcelProperty("薪資")
    private BigDecimal salary;
}

Student

/**
 * 數據庫對應的實體類
 * @author jiejie
 */
@Data
@TableName(value = "student")
public class Student {
    /**
     * 姓名
     */
    @TableField(value = "name")
    private String name;

    /**
     * 生日
     */
    @TableField(value = "birthday")
    private Date birthday;

    /**
     * 薪資
     */
    @TableField(value = "salary")
    private BigDecimal salary;

    public static final String COL_NAME = "name";

    public static final String COL_BIRTHDAY = "birthday";

    public static final String COL_SALARY = "salary";
}

3.4 listener

官方文檔

EasyExcel讀取文件需要用到

ExcelDictDTOListener

/**
 * 監聽
 * 再讀取數據的同時 對數據進行插入操作
 * @author : look-word
 * @date : 2022-05-10 21:35
 **/
@Slf4j
//@AllArgsConstructor //全參
@NoArgsConstructor //無參
public class ExcelDictDTOListener extends AnalysisEventListener<ExcelStudentDTO> {


    /**
     * 每隔5條存儲數據庫，實際使用中可以3000條，然後清理list ，方便內存回收
     */
    private static final int BATCH_COUNT = 5;
    List<ExcelStudentDTO> list = new ArrayList<ExcelStudentDTO>();

    private StudentMapper studentMapper;

    //傳入mapper對象
    public ExcelDictDTOListener(StudentMapper studentMapper) {
        this.studentMapper = studentMapper;
    }

    /**
     *遍歷每一行的記錄
     * @param data
     * @param context
     */
    @Override
    public void invoke(ExcelStudentDTO data, AnalysisContext context) {
        log.info("解析到一條記錄: {}", data);
        list.add(data);
        // 達到BATCH_COUNT瞭，需要去存儲一次數據庫，防止數據幾萬條數據在內存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存儲完成清理 list
            list.clear();
        }
    }

    /**
     * 所有數據解析完成瞭 都會來調用
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 這裡也要保存數據，確保最後遺留的數據也存儲到數據庫
        saveData();
        log.info("所有數據解析完成！");
    }

    /**
     * 加上存儲數據庫
     */
    private void saveData() {
        log.info("{}條數據，開始存儲數據庫！", list.size());
        studentMapper.insertBatch(list);  //批量插入
        log.info("存儲數據庫成功！");
    }
}

3.5 config

mybatisPlus分頁插件

MybatisPlusConfig

@Configuration
public class MybatisPlusConfig {

    /**
     * 新的分頁插件,一緩和二緩遵循mybatis的規則,
     * 需要設置 MybatisConfiguration#useDeprecatedExecutor = false
     * 避免緩存出現問題(該屬性會在舊插件移除後一同移除)
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        paginationInnerInterceptor.setDbType(DbType.MYSQL);
        paginationInnerInterceptor.setOverflow(true);
        interceptor.addInnerInterceptor(paginationInnerInterceptor);
        return interceptor;
    }

    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return configuration -> configuration.setUseDeprecatedExecutor(false);
    }
}

3.6 配置文件

application.yaml

server:
  port: 8811
spring:
  datasource: # mysql數據庫連接
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/2022_source_springboot?serverTimezone=GMT%2B8&characterEncoding=utf-8
    username: root
    password: 317311
mybatis-plus:
  configuration:# sql日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  mapper-locations:
    - classpath:mapper/*.xml

4.啟動測試

啟動springboot哦

頁面效果圖

導出效果

註意

導入數據時要保證excel中列名和ExcelStudentDTO一致奧

到此這篇關於SpringBoot整合EasyExcel實現導入導出數據的文章就介紹到這瞭,更多相關SpringBoot EasyExcel導入導出數據內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

SpringBoot整合EasyExcel實現導入導出數據

目錄

前言

1.前端

2.數據庫

3.後端

3.1 contrller

3.2 mapper

3.3 bean

3.4 listener

3.5 config

3.6 配置文件

4.啟動測試

推薦閱讀：

發佈留言取消回覆

近期文章

目錄

前言

1.前端

2.數據庫

3.後端

3.1 contrller

3.2 mapper

3.3 bean

3.4 listener

3.5 config

3.6 配置文件

4.啟動測試

推薦閱讀：

發佈留言 取消回覆

近期文章

標籤

發佈留言取消回覆