easypoi实例及大批量插入数据库

最新推荐文章于 2024-05-22 17:18:31 发布
最新推荐文章于 2024-05-22 17:18:31 发布
阅读量2.1k 收藏 8
点赞数
分类专栏： Excel相关操作 mybatis 文章标签： big data spring java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_28326501/article/details/119463936
版权

1、环境

        springboot+mybatis+mysq

2、pom依赖导入

        <!--easypoi-->
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-base</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.1.0</version>
        </dependency>

3、工具类

package com.cc.poi.controller;

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.io.InputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

public class ExcelUtils {
    /**
     * excel 导出
     *
     * @param list           数据
     * @param title          标题
     * @param sheetName      sheet名称
     * @param pojoClass      pojo类型
     * @param fileName       文件名称
     * @param isCreateHeader 是否创建表头
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     *
     * @param list      数据
     * @param title     标题
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  文件名称
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }

    /**
     * excel 导出
     *
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response
     * @param exportParams 导出参数
     */
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     *
     * @param list     数据
     * @param fileName 文件名称
     * @param response
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, fileName, response);
    }

    /**
     * 默认的 excel 导出
     *
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response
     * @param exportParams 导出参数
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 默认的 excel 导出
     *
     * @param list     数据
     * @param fileName 文件名称
     * @param response
     */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 下载
     *
     * @param fileName 文件名称
     * @param response
     * @param workbook excel数据
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param filePath   excel文件路径
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedSave(true);
        params.setSaveUrl("/excel/");
        try {
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("模板不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param file      excel文件
     * @param pojoClass pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
        return importExcel(file, 1, 1, pojoClass);
    }

    /**
     * excel 导入
     *
     * @param file       excel文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        return importExcel(file, titleRows, headerRows, false, pojoClass);
    }

    /**
     * excel 导入
     *
     * @param file       上传的文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param needVerfiy 是否检验excel内容
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
        if (file == null) {
            return null;
        }
        try {
            return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param inputStream 文件输入流
     * @param titleRows   标题行
     * @param headerRows  表头行
     * @param needVerfiy  是否检验excel内容
     * @param pojoClass   pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass) throws IOException {
        if (inputStream == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setSaveUrl("/excel/");
        params.setNeedSave(true);
        params.setNeedVerify(needVerfiy);
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * Excel 类型枚举
     */
    enum ExcelTypeEnum {
        XLS("xls"), XLSX("xlsx");
        private String value;

        ExcelTypeEnum(String value) {
            this.value = value;
        }

        public String getValue() {
            return value;
        }

        public void setValue(String value) {
            this.value = value;
        }
    }
}

4、@Excel注解

        这个是必须使用的注解,如果需求简单只使用这一个注解也是可以的,

        涵盖了常用的Excel需求。其余注解可搜素官网进行了解

属性类型默认值功能

name

String

null

列名,支持name_id

needMerge

boolean

fasle

是否需要纵向合并单元格(用于含有list中,单个的单元格,合并list创建的多个row)

orderNum

String

"0"

列的排序,支持name_id

replace

String[]

{}

值得替换 导出是{a_id,b_id} 导入反过来

savePath

String

"upload"

导入文件保存路径,如果是图片可以填写,默认是upload/className/ IconEntity这个类对应的就是upload/Icon/

type

int

1

导出类型 1 是文本 2 是图片,3 是函数,10 是数字 默认是文本

width

double

10

列宽

height

double

10

列高,后期打算统一使用@ExcelTarget的height,这个会被废弃,注意

isStatistics

boolean

fasle

自动统计数据,在追加一行统计,把所有数据都和输出 这个处理会吞没异常,请注意这一点

isHyperlink

boolean

false

超链接,如果是需要实现接口返回对象

isImportField

boolean

true

校验字段,看看这个字段是不是导入的Excel中有,如果没有说明是错误的Excel,读取失败,支持name_id

exportFormat

String

""

导出的时间格式,以这个是否为空来判断是否需要格式化日期

importFormat

String

""

导入的时间格式,以这个是否为空来判断是否需要格式化日期

format

String

""

时间格式,相当于同时设置了exportFormat 和 importFormat

databaseFormat

String

"yyyyMMddHHmmss"

导出时间设置,如果字段是Date类型则不需要设置 数据库如果是string 类型,这个需要设置这个数据库格式,用以转换时间格式输出

numFormat

String

""

数字格式化,参数是Pattern,使用的对象是DecimalFormat

imageType

int

1

导出类型 1 从file读取 2 是从数据库中读取 默认是文件 同样导入也是一样的

suffix

String

""

文字后缀,如% 90 变成90%

isWrap

boolean

true

是否换行 即支持\n

mergeRely

int[]

{}

合并单元格依赖关系,比如第二列合并是基于第一列 则{0}就可以了

mergeVertical

boolean

fasle

纵向合并内容相同的单元格

fixedIndex

int

-1

对应excel的列,忽略名字

isColumnHidden

boolean

false

导出隐藏列

5、conteroller、service及实体类

        【1】conteroller

package com.cc.poi.controller;
import com.cc.poi.service.EasyPoiService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @author cc
 * @data 2021年08月02日 22:58
 */
@RestController
@RequestMapping("/poi")
public class TestController {

    @Autowired
    private EasyPoiService easyPoiService;

    /**
     * 导出
     */
    @RequestMapping(value = "/downLoadExcel", method = RequestMethod.GET)
    @ResponseBody
    public void downLoadExcel(HttpServletResponse response) {
        try {
            easyPoiService.downLoadExcel(response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     *导入
     */
    @RequestMapping("/importXls")
    @ResponseBody
    public void importSS(@RequestParam("file") MultipartFile file) {
        try {
            easyPoiService.importXls(file);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

}

        【2】service 

package com.cc.poi.service.impl;

import com.cc.common.entity.CcIpRoster;
import com.cc.common.mapper.CcIpRosterMapper;
import com.cc.poi.controller.ExcelUtils;
import com.cc.poi.service.EasyPoiService;
import org.apache.ibatis.session.ExecutorType;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author cc
 * @data 2021年08月04日 0:00
 */
@Service
public class EasyPoiServiceImpl implements EasyPoiService {
    @Autowired
    private CcIpRosterMapper ccIpRosterMapper;
    @Autowired
    SqlSessionFactory sqlSessionFactory;

    /**
     * 导出
     * @author cc
     * @date 2021/8/6 23:19
     * @param response 
     */
    public void downLoadExcel(HttpServletResponse response) throws IOException {
        List<CcIpRoster> personList =  ccIpRosterMapper.selectAll();
        ExcelUtils.exportExcel(personList, null, "测试sheet", CcIpRoster.class, "测试信息表", response);
    }

    /**
     * 导入
     * @author cc
     * @date 2021/8/6 23:19
     * @param file 
     */
    public void importXls(MultipartFile file) throws IOException {
        Date date = new Date();
        List<CcIpRoster> personList = ExcelUtils.importExcel(file,0,1, CcIpRoster.class);
        for (CcIpRoster ccIpRoster : personList) {
            ccIpRoster.setIsDel(0);
            ccIpRoster.setCreatedBy("cc");
            ccIpRoster.setUpdatedBy("cc");
            ccIpRoster.setCreatedTime(date);
            ccIpRoster.setUpdatedTime(date);
        }
        long start = System.currentTimeMillis();
        batchInsert(personList);
        System.out.println("导入excel所花时间：" + (System.currentTimeMillis() - start)/1000 + "'s");
    }

    /**
     * 批量插入
     * @author cc
     * @date 2021/8/6 23:19
     * @param personList 
     */
    private void batchInsert(List<CcIpRoster> personList){
        SqlSession session =  sqlSessionFactory.openSession(ExecutorType.BATCH);
        try{
            CcIpRosterMapper mapper = session.getMapper(CcIpRosterMapper.class);
            List<CcIpRoster> list = new ArrayList<>();
            for(int i=0 ; i< personList.size(); i++){
                list.add(personList.get(i));
                if(i%5000==0 || i==personList.size()-1){
                    mapper.batchInsert(list);
                    list.clear();
                }
            }
            session.commit();
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            session.close();
        }


    }
}

        【3】mapper

<insert id="batchInsert">
    insert into cc_ip_roster
    (ip, is_del, status, created_by, created_time, updated_by, updated_time)
    values 
    <foreach collection="list" item="eimt" index="index" separator=",">
      (
        #{eimt.ip,jdbcType=VARCHAR}, #{eimt.isDel,jdbcType=INTEGER},#{eimt.status,jdbcType=INTEGER},
        #{eimt.createdBy,jdbcType=VARCHAR}, #{eimt.createdTime,jdbcType=TIMESTAMP},
        #{eimt.updatedBy,jdbcType=VARCHAR}, #{eimt.updatedTime,jdbcType=TIMESTAMP}
       )
    </foreach>

  </insert>

        【4】实体

package com.cc.common.entity;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.Id;
import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class CcIpRoster {
    @Id
    private Long id;
    @Excel(name = "IP", orderNum = "0", width = 15)
    private String ip;
    @Excel(name = "状态", orderNum = "1", width = 15)
    private Integer status;

    private Integer isDel;

    private String createdBy;

    private Date createdTime;

    private String updatedBy;

    private Date updatedTime;
}

6、批量保持说明

        ExecutorType.BATCH。为Mybatis内置的一种ExecutorType。ExecutorType有3种，默认的是simple，该模式下它为每个语句的执行创建一个新的预处理语句，单条提交sql；而batch模式重复使用已经预处理的语句，并且批量执行所有更新语句，显然batch性能将更优； 但batch模式也有自己的问题，比如在Insert操作时，在事务没有提交之前，是没有办法获取到自增的id，这在某型情形下是不符合业务要求的

        具体代码再service示例代码中，在本地测试10w条数据，插入时间为4秒

        

7、其他注意事项

        【1】上传文件大小 

                springboot 默认文件上传大小有限制，当excel文件过大时，可能会上传失败.需进行配置

                

         【2】上传的excel列头和对应字段的@Excel的name 属性保持一致。

                 

cc_南柯一梦
关注
  • 0
    点赞
  • 踩
  • 8
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
JAVA实现easypoi批量导入
m0_46300599的博客
04-19 1045
第二种：添加必填字段校验（存在错误数据）ImportParams 参数介绍下。第一种：简单的导入（无校验）第三种：必填校验加自定义校验。
基于easypoi实现的Excel数据批量导入
Ytker-一个简简单单的小博客
12-01 3763
SpringBoot，Excel导入，多线程，分组
java对excel文件里面的数据进行追加或者写入,修改等工具类
最新发布
Welcome to My blog
05-22 383
【代码】java对excel文件里面的数据进行追加或者写入,修改等工具类。
EasyPOI批量导入
用生命研发技术
12-21 1335
1.首先引入依赖 <!--引入easypoi依赖--> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-base</artifactId> <version>4.1.0</version> </dependency&g
使用EasyPOI实现导入导出
登哥你莫惹
03-29 2824
1. 列名名称需要根据配置动态替换 2. 指定列根据条件判断是否需要导出 3. 实现下拉列表 4. 给表头加填充颜色 5. 因为在导出的时候替换了相应列的名称, 所以在导入的时候需要动态的去匹配列名名称(重写importService) 6. 导入的时候过滤空行
Easypoi一对多实现导入
aq_money的博客
09-01 2892
2.1 创建 CertificateConfigCluster 实体类/*** @Excel(needMerge = true) 是否要合并单元格* @Excel(importFormat = "yyyy-MM-dd") importFormat 导入时候处理xls表格的日期格式* @Excel 注解中 还有很多的属性 比如字段判空 等等 大家可以上官网自行查看*/ /*** @Fields certificateName 证书名称。......
easypoi导入excel实现一对多导入
来自外太空的鱼-张小张
09-20 3535
easypoi导入excel实现一对多导入
Qt5开发及实例-CH1301.rar,Qt5使用SQLite数据库完成大批量数据的代码
09-15
Qt5开发及实例,实例CH1301,基于控制台的程序，使用SQLite数据库完成大批量数据的增加、删除、更新和查询操作并输出。 实现步骤如下。 （1）在“QSQLiteEx.pro”文件中添加如下代码： QT += sql （2）源文件“main....
ajax.net对数据库的插入实例
10-27
ajax.net对数据库的插入实例，需要的朋友可以参考一下
使用python读取csv文件快速插入数据库的实例
01-21
如下所示： # -*- coding:utf-8 -*- # auth:ckf # date:20170703 import pandas as pd import cStringIO import warnings from sqlalchemy import create_engine ... 'postgresql+psycopg2://'数据库连接) filename
php使用PDO操作MySQL数据库实例
12-19
描述中提到，PDO是一个数据库操作的公用类，它提供了轻量级且一致的接口。这意味着无论使用MySQL、SQLite还是其他支持的数据库，都可以通过PDO执行SQL查询和获取数据。PDO在PHP 5.1及以上版本中可用，并且从PHP 5.2...
数据库实例概述.pptx
11-14
Oracle数据库实例概述 Oracle数据库实例是指在内存中运行的数据库管理系统，它由一系列内存结构和后台进程组成。在启动数据库时，Oracle首先在内存中获取一定的空间，启动各种用途的后台进程，即创建一个数据库实例...
easypoi使用excel模板导入10万条数据到数据库
zjlovezhc的博客
03-06 446
批量导入10万+条数据到数据库解决方案
easypoi实现Excel表格数据的批量导入
hg_zhh
02-16 2551
项目需要实现人员信息的批量导入功能，不会造轮子，只能用别人的，http://easypoi.mydoc.io/项目使用Springboot+thymeleaf的方式。网上有很多教程，但是多数只写了测试，并没有完整的方法，接下来给出具体步骤。 1.添加依赖 将如下的代码放进pom.xml <dependency> <groupId>cn.after...
微服务 Spring Boot Mybatis-Plus 整合 EasyPOI 实现 Excel 一对多 导入
wanghuichen的博客
12-27 9767
Spring Boot 如何实现 Excel 的一对多 导入？ 一篇文章搞定！
【百万数据导出EasyPOI、MySQL】
学无止境
03-26 1759
为什么是600多万？因为本来想整5000万来着，然后等了半天才600多万，不想等了，应该也够了。
easyPoi实现百万级别导入导出(亲测有效，防止内存溢出）
m0_47944994的博客
04-03 799
easyPoi实现百万级别导入导出(亲测有效，防止内存溢出）
easyexcel导出百万级数据_java使用导出百万级别数据？
weixin_39875832的博客
11-14 1429
用过POI的人都知道，在POI以前的版本中并不支持大数据量的处理，如果数据量过多还会常报OOM错误， 这时候调整JVM的配置参数也不是一个好对策(注：jdk在32位系统中支持的内存不能超过2个G，而在64位中没有限制，但是在64位的系统中，性能并不是太好),好在POI3.8版本新出来了一个SXSSFWorkbook对象，它就是用来解决大数据量以及超大数据量的导入导出操作的，但是SXSSFWorkb...
Excel轻松操控：掌握EasyPoi的妙用技巧
热门推荐
学而时习之，不亦说乎？温故而知新，可以为师矣！
10-13 1万+
本文将深入探讨EasyPoi库的基本使用，帮助读者轻松处理Excel文件。从创建工作簿到定义表头，再到填充数据，EasyPoi为Excel操作提供了简单而强大的解决方案。通过实用的代码示例和易懂的指南，本文将引导读者深入了解EasyPoi的核心功能，让Excel操作变得简单而愉快。无论您是初学者还是有经验的开发者，这篇博客都将成为您Excel处理工具箱中的得力助手。
如何实例化此方法进行数据库插入操作？
04-21
可以使用以下代码进行数据库插入操作： ``` import pymysql # 连接数据库 db = pymysql.connect("localhost", "username", "password", "database_name") # 使用 cursor() 方法创建一个游标对象 cursor cursor = db.cursor() # SQL 插入语句 sql = "INSERT INTO table_name (column1, column2, column3) VALUES (%s, %s, %s)" val = ("value1", "value2", "value3") try: # 执行 SQL 语句 cursor.execute(sql, val) # 提交到数据库执行 db.commit() print("数据插入成功！") except: # 发生错误时回滚 db.rollback() print("数据插入失败！") # 关闭数据库连接 db.close() ```

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

cc_南柯一梦

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值