• Easyexcel·读取excel

    Easyexcel·读取excel

    使用poi读取excel容易内存溢出-Easyexcel可以很好的解决这个问题

    依赖

    
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0modelVersion>
    <parent>
        <groupId>org.springframework.bootgroupId>
        <artifactId>spring-boot-starter-parentartifactId>
        <version>2.7.1version>
        <relativePath/> 
    parent>
    <groupId>com.mmgroupId>
    <artifactId>shop_proartifactId>
    <version>0.0.1-SNAPSHOTversion>
    <name>shop_proname>
    <description>shop_prodescription>
    <properties>
        <java.version>1.8java.version>
    properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-webartifactId>
        dependency>

        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-testartifactId>
            <scope>testscope>
        dependency>
        
        <dependency>
            <groupId>mysqlgroupId>
            <artifactId>mysql-connector-javaartifactId>
            <version>8.0.29version>
        dependency>
        
        <dependency>
            <groupId>org.mybatis.spring.bootgroupId>
            <artifactId>mybatis-spring-boot-starterartifactId>
            <version>2.2.2version>
        dependency>
        
        
        <dependency>
            <groupId>org.apache.poigroupId>
            <artifactId>poi-ooxml-schemasartifactId>
            <version>4.0.0version>
        dependency>

        <dependency>
            <groupId>org.springframeworkgroupId>
            <artifactId>spring-testartifactId>
        dependency>

        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>easyexcelartifactId>
            <version>2.2.3version>
        dependency>
        
        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>fastjsonartifactId>
            <version>1.2.28version>
        dependency>
        
        <dependency>
            <groupId>org.springframework.bootgroupId>
            <artifactId>spring-boot-starter-thymeleafartifactId>
        dependency>

    dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.bootgroupId>
                <artifactId>spring-boot-maven-pluginartifactId>
            plugin>
        plugins>
        <resources>
            
            <resource>
                <directory>src/main/resourcesdirectory>
                <filtering>truefiltering>
            resource>
        resources>
    build>

project>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90

    数据表

    -- 商品记账表
CREATE TABLE `shop_customer` (
  `id` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `shop_name` varchar(100) DEFAULT NULL,
  `shop_number` varchar(100) DEFAULT NULL,
  `shop_time` varchar(100) DEFAULT NULL,
  `order_time` varchar(100) DEFAULT NULL,
  `update_time` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 测试表 excel导入mysql
CREATE TABLE `excel_fazhan_ls` (
  `DATE` varchar(100) DEFAULT NULL COMMENT '账期',
  `PROV_DESC` varchar(100) DEFAULT NULL COMMENT '省份',
  `CITY_DESC` varchar(100) DEFAULT NULL COMMENT '地市',
  `USER_ID` varchar(100) NOT NULL COMMENT '产品id',
  `TAOCAN_NAME` varchar(100) DEFAULT NULL COMMENT '产品名称',
  `channel` varchar(100) DEFAULT NULL COMMENT '渠道',
  `num1` varchar(100) DEFAULT NULL COMMENT '本日新增用户数',
  `num2` varchar(100) DEFAULT NULL COMMENT '存量用户数',
  `num3` varchar(100) DEFAULT NULL COMMENT '本月新增用户数',
  `num4` varchar(100) DEFAULT NULL COMMENT '本日新增用户数（上月同期）',
  `num5` varchar(100) DEFAULT NULL COMMENT '存量用户数（上月同期）',
  `num6` varchar(100) DEFAULT NULL COMMENT '本月新增用户数（上月同期）',
  `TAOCAN` varchar(100) DEFAULT NULL COMMENT '产品',
  KEY `prov` (`PROV_DESC`),
  KEY `city` (`CITY_DESC`),
  KEY `name` (`TAOCAN_NAME`),
  KEY `taocan` (`TAOCAN`),
  KEY `index_date` (`DATE`) USING BTREE,
  KEY `index_user_id` (`USER_ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 导入状态
CREATE TABLE `excel_fazhan_ls_status` (
 status string   --  0导入中 1导入完成
 ) ;

INSERT INTO excel_fazhan_ls_status VALUES ('0');


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41

    表的实体类

    shop_customer表

    package com.mm.shop_pro.mode;

public class shopCustomerMode {

    private String id;
    private String name;
    private String shop_name;
    private String shop_number;
    private String shop_time;
    private String order_time;
    private String update_time;

    @Override
    public String toString() {
        return "shopController{" +
                "id='" + id + '\'' +
                ", name='" + name + '\'' +
                ", shop_name='" + shop_name + '\'' +
                ", shop_number='" + shop_number + '\'' +
                ", shop_time='" + shop_time + '\'' +
                ", order_time='" + order_time + '\'' +
                ", update_time='" + update_time + '\'' +
                '}';
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getShop_name() {
        return shop_name;
    }

    public void setShop_name(String shop_name) {
        this.shop_name = shop_name;
    }

    public String getShop_number() {
        return shop_number;
    }

    public void setShop_number(String shop_number) {
        this.shop_number = shop_number;
    }

    public String getShop_time() {
        return shop_time;
    }

    public void setShop_time(String shop_time) {
        this.shop_time = shop_time;
    }

    public String getOrder_time() {
        return order_time;
    }

    public void setOrder_time(String order_time) {
        this.order_time = order_time;
    }

    public String getUpdate_time() {
        return update_time;
    }

    public void setUpdate_time(String update_time) {
        this.update_time = update_time;
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82

    excel_fazhan_ls表

    package com.mm.shop_pro.mode;

import com.alibaba.excel.annotation.ExcelProperty;

public class Exce_fazhan_mode {
    @ExcelProperty(value = "账期")
    private String date1;
    @ExcelProperty(value = "省份")
    private String prov_desc;
    @ExcelProperty(value = "地市")
    private String city_desc;
    @ExcelProperty(value = "产品id")
    private String user_id;
    @ExcelProperty(value = "产品名称")
    private String taocan_name;
    @ExcelProperty(value = "渠道")
    private String channel;
    @ExcelProperty(value = "本日新增用户数")
    private String num1;
    @ExcelProperty(value = "存量用户数")
    private String num2;
    @ExcelProperty(value = "本月新增用户数")
    private String num3;
    @ExcelProperty(value = "本日新增用户数（上月同期）")
    private String num4;
    @ExcelProperty(value = "存量用户数（上月同期）")
    private String num5;
    @ExcelProperty(value = "本月新增用户数（上月同期）")
    private String num6;
    @ExcelProperty(value = "产品名称2")
    private String taocan;

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    // 判断执行状态
    private String status;

    @Override
    public String toString() {
        return "Exce_fazhan_mode{" +
                "date1='" + date1 + '\'' +
                ", prov_desc='" + prov_desc + '\'' +
                ", city_desc='" + city_desc + '\'' +
                ", user_id='" + user_id + '\'' +
                ", taocan_name='" + taocan_name + '\'' +
                ", channel='" + channel + '\'' +
                ", num1='" + num1 + '\'' +
                ", num2='" + num2 + '\'' +
                ", num3='" + num3 + '\'' +
                ", num4='" + num4 + '\'' +
                ", num5='" + num5 + '\'' +
                ", num6='" + num6 + '\'' +
                ", taocan='" + taocan + '\'' +
                ", status='" + status + '\'' +
                '}';
    }
// @ExcelIgnore  // 忽略字段
   // private String ignore;

    public String getDate1() {
        return date1;
    }

    public void setDate1(String date1) {
        this.date1 = date1;
    }

    public String getProv_desc() {
        return prov_desc;
    }

    public void setProv_desc(String prov_desc) {
        this.prov_desc = prov_desc;
    }

    public String getCity_desc() {
        return city_desc;
    }

    public void setCity_desc(String city_desc) {
        this.city_desc = city_desc;
    }

    public String getUser_id() {
        return user_id;
    }

    public void setUser_id(String user_id) {
        this.user_id = user_id;
    }

    public String getTaocan_name() {
        return taocan_name;
    }

    public void setTaocan_name(String taocan_name) {
        this.taocan_name = taocan_name;
    }

    public String getChannel() {
        return channel;
    }

    public void setChannel(String channel) {
        this.channel = channel;
    }

    public String getNum1() {
        return num1;
    }

    public void setNum1(String num1) {
        this.num1 = num1;
    }

    public String getNum2() {
        return num2;
    }

    public void setNum2(String num2) {
        this.num2 = num2;
    }

    public String getNum3() {
        return num3;
    }

    public void setNum3(String num3) {
        this.num3 = num3;
    }

    public String getNum4() {
        return num4;
    }

    public void setNum4(String num4) {
        this.num4 = num4;
    }

    public String getNum5() {
        return num5;
    }

    public void setNum5(String num5) {
        this.num5 = num5;
    }

    public String getNum6() {
        return num6;
    }

    public void setNum6(String num6) {
        this.num6 = num6;
    }

    public String getTaocan() {
        return taocan;
    }

    public void setTaocan(String taocan) {
        this.taocan = taocan;
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170

    接口

    表的crud操作

    package com.mm.shop_pro.mapper;

import com.mm.shop_pro.mode.Exce_fazhan_mode;
import com.mm.shop_pro.mode.shopCustomerMode;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface shopCustomerMapper {

        @Select("SELECT id, name, shop_name, shop_number, shop_time, order_time, update_time FROM shop_customer limit #{id}")
        public List<shopCustomerMode> shopqy(int id);

        @Insert("INSERT INTO shop_customer (id, name, shop_name, shop_number, shop_time, order_time, update_time) VALUES ( #{id}, #{name}, '','','','','')")
        public void  shopinst(String id,String name);

        @Delete("DELETE FROM shop_customer WHERE ID =  #{id} ")
        public void  shopdetele(String id);

        @Update("UPDATE shop_customer SET id = ,name WHERE id = ")
        public void  shopupdate(String id);

        @Insert("INSERT INTO excel_fazhan_ls (date ,prov_desc ,city_desc ,user_id ,taocan_name ,channel ,num1 ,num2 ,num3 ,num4 ,num5 ,num6 ,taocan ) VALUES (#{date} ,#{prov_desc} ,#{city_desc} ,#{user_id} ,#{taocan_name} ,#{channel} ,#{num1} ,#{num2} ,#{num3} ,#{num4} ,#{num5} ,#{num6} ,#{taocan} )")
        public void  fazhan_insert (String date ,String prov_desc ,String city_desc ,String user_id ,String taocan_name ,String channel ,String num1 ,String num2 ,String num3 ,String num4 ,String num5 ,String num6 ,String taocan);

        @Update("UPDATE excel_fazhan_ls_status SET status = #{status}")
        public void  fazhan_status_update(String status);

        @Select("SELECT status FROM excel_fazhan_ls_status order by status desc  limit 1")
        public List<Exce_fazhan_mode> fazhan_status_select();
}



    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34

    此接口用于将excel数据写入mysql测试表表中

    package com.mm.shop_pro.excel_mysql;

import com.mm.shop_pro.mode.Exce_fazhan_mode;

import java.util.List;

public interface ApiExcelService {
    Exce_fazhan_mode insertd2(String datesss,
                              String prov_desc,
                              String city_desc,
                              String user_id,
                              String taocan_name,
                              String channel,
                              String num1,
                              String num2,
                              String num3,
                              String num4,
                              String num5,
                              String num6,
                              String taocana);
    Exce_fazhan_mode fazhan_status_update(String s);
    public List<Exce_fazhan_mode> fazhan_status_select();

}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25

    接口的实现类

    package com.mm.shop_pro.excel_mysql;

import com.mm.shop_pro.mapper.shopCustomerMapper;
import com.mm.shop_pro.mode.Exce_fazhan_mode;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class ApiExcelServiceImpl implements ApiExcelService {
    @Autowired
    private shopCustomerMapper shopCustomerMapper;
    String datesss = null;
    String prov_desc = null;
    String city_desc = null;
    String user_id = null;
    String taocan_name = null;
    String channel = null;
    String num1 = null;
    String num2 = null;
    String num3 = null;
    String num4 = null;
    String num5 = null;
    String num6 = null;
    String taocanas = null;

    @Override
    public Exce_fazhan_mode insertd2(String datesss, String prov_desc, String city_desc, String user_id, String taocan_name, String channel, String num1, String num2, String num3, String num4, String num5, String num6, String taocana)
    {
        shopCustomerMapper.fazhan_insert(datesss,prov_desc,city_desc,user_id,taocan_name,channel,num1,num2,num3,num4,num5,num6,taocanas);
        System.out.println("插入完成ApiExcel！！！");
        return  null;
    }
    @Override
    public Exce_fazhan_mode fazhan_status_update(String status)
    {
        shopCustomerMapper.fazhan_status_update(status);
        return null;
    }
    @Override
    public List<Exce_fazhan_mode> fazhan_status_select()
    {
        return shopCustomerMapper.fazhan_status_select();
    }

}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48

    测试数据导写入到excle中与读取excel数据

    package com.mm.shop_pro.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.fasterxml.jackson.databind.exc.InvalidFormatException;
import com.mm.shop_pro.mode.Exce_fazhan_mode;

import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Easyexcel_eximp {
   
    public static void main(String[] args) throws IOException, InvalidFormatException, ClassNotFoundException, SQLException {
         // 数据写入excel
         List list=new ArrayList();
         for (int i = 0; i < 10; i++) {
                    Exce_fazhan_mode data = new Exce_fazhan_mode();
                    data.setDate1(String.valueOf(i));
                    data.setTaocan_name("学生" + i);
                    list.add(data);
                }
        String filename = "src/main/resources/user1.xlsx";
        // 创建ExcelWriter对象
        ExcelWriter excelWriter = EasyExcel.write(filename, Easyexcel_eximp.class).build();
        // 创建Sheet对象
        WriteSheet writeSheet = EasyExcel.writerSheet("学生信息").build();
        // 向Excel中写入数据
        excelWriter.write(list , writeSheet);
        // 关闭流
        excelWriter.finish();

        // 读取excel数据
        String fileName = "src/main/resources/22.xlsx";

        try {
            EasyExcel.read(fileName, Exce_fazhan_mode.class,new DemoDataListener())
                .sheet(1).doRead();     // .sheet(0) 可以按索引查sheet，也可以指定sheet名字
        }catch (Exception e){
            System.out.println(e);
        }
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46

    读取excel数据需要自定义AnalysisEventListener类方法

    注：此方法不可以被springboor托管，要想使用数据库操作需要从服务层将接口传入进来声明后才能使用

    package com.mm.shop_pro.controller;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.mm.shop_pro.mode.Exce_fazhan_mode;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class DemoDataListener extends AnalysisEventListener<Exce_fazhan_mode> {
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 600;
    List<Exce_fazhan_mode> list = new ArrayList<Exce_fazhan_mode>();
    /**
     * 这个每一条数据解析都会来调用
     * @param data
     * @param context
     */
    @Override
    public void invoke(Exce_fazhan_mode data, AnalysisContext context) {
        // System.out.println("解析到一条数据:" + JSON.toJSONString(data));
        // System.out.println("数据解析开始======================:");
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            for (int i=0;i<list.size();i++) {
                System.out.println
                        ("打印list数据2:"
                                        +list.get(i).getDate1()+"&&"
                                        +list.get(i).getProv_desc()+"&&"
                                        +list.get(i).getCity_desc()+"&&"
                                        +list.get(i).getUser_id()+"&&"
                                        +list.get(i).getTaocan_name()+"&&"
                                        +list.get(i).getChannel()+"&&"
                                        +list.get(i).getNum1()+"&&"
                                        +list.get(i).getNum2()+"&&"
                                        +list.get(i).getNum3()+"&&"
                                        +list.get(i).getNum4()+"&&"
                                        +list.get(i).getNum5()+"&&"
                                        +list.get(i).getNum6()+"&&"
                                        +list.get(i).getTaocan()+"&&"
                        );
            }
            // 存储完成清理 list
            list.clear();
            // 延迟3秒查看数据打印情况
            try {
                Thread.sleep(3000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("invoke方法执行完后执行此方法-最后一次解析了" + list.size() + "条数据！");
        System.out.println("所有数据解析完成！");
    }
    //读取表头内容
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("读取excel开始时执行此方法-表头信息："+headMap);
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72

    读取excel数据并写入mysql

    控制层

    package com.mm.shop_pro.excel_mysql;

import com.alibaba.excel.EasyExcel;
import com.mm.shop_pro.mode.Exce_fazhan_mode;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.*;

import java.util.ArrayList;
import java.util.List;

@RestController
public class msyqlEasyexcel_eximp {

   private String path1;
   private String sheetname;

   public String getPath1() {
       return path1;
   }

   public void setPath1(String path1) {
       this.path1 = path1;
   }

   public String getSheetname() {
       return sheetname;
   }

   public void setSheetname(String sheetname) {
       this.sheetname = sheetname;
   }

   @Autowired
   private ApiExcelService apiExcelService;

   @CrossOrigin(origins = "*",maxAge = 3600)  // 解决跨域问题
   @RequestMapping(value = "/ajax", method = RequestMethod.POST, consumes = MediaType.APPLICATION_JSON_UTF8_VALUE)
   public String excel_fafang(@RequestBody msyqlEasyexcel_eximp dot){

       String path = dot.getPath1();
       String sheet =dot.getSheetname();
       System.out.println("获取html数据："+path+"@@@"+sheet);

       apiExcelService.fazhan_status_update("0"); // 0进行中，1成功

       // 读取excel数据
       // String fileName = "src/main/resources/权益特惠流量包.xlsx";
       String fileName = path;

       EasyExcel.read(fileName, Exce_fazhan_mode.class,new impmysqlDataListener(apiExcelService))
           .sheet(sheet).doRead();     // .sheet(0) 可以按索引查sheet，也可以指定sheet名字
       return "导入完成！";
   }


   @CrossOrigin(origins = "*",maxAge = 3600)  // 解决跨域问题
   @RequestMapping(value = "/ajaxg", method =RequestMethod.GET )
   public Exce_fazhan_mode excel_fafangselect() {
       Exce_fazhan_mode exce_fazhan_mode = new Exce_fazhan_mode();

       List<Exce_fazhan_mode> list=new ArrayList<>();
       list = apiExcelService.fazhan_status_select();
       exce_fazhan_mode.setStatus( String.valueOf(list.get(0).getStatus()));
       System.out.println("获取Status数据ss："+list);
       return exce_fazhan_mode;
   }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68

    自定义读取excel方法

    package com.mm.shop_pro.excel_mysql;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.mm.shop_pro.mode.Exce_fazhan_mode;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;


// 有个很重要的点 DemoDataListener 不能被spring管理，要每次读取excel都要new,然后里面用到spring可以构造方法传进去
public class impmysqlDataListener extends AnalysisEventListener<Exce_fazhan_mode> {
    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 5;
    List<Exce_fazhan_mode> list = new ArrayList<Exce_fazhan_mode>();

    public ApiExcelService apiExcelService;

    public impmysqlDataListener(ApiExcelService apiExcelService) {
        this.apiExcelService = apiExcelService;
    }

    @Override
    public void invoke(Exce_fazhan_mode data, AnalysisContext context) {
        // System.out.println("解析到一条数据:" + JSON.toJSONString(data));
        // System.out.println("数据解析开始======================:");
        list.add(data);
        System.out.println("当前excel日期:"+list.get(0).getDate1());
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            for (int i=0;i<list.size();i++) {
                System.out.println("打印list数据2:"+list.get(i).getDate1()+"&&" +list.get(i).getProv_desc()+"&&" +list.get(i).getCity_desc()+"&&" +list.get(i).getUser_id()+"&&" +list.get(i).getTaocan_name()+"&&" +list.get(i).getChannel()+"&&" +list.get(i).getNum1()+"&&" +list.get(i).getNum2()+"&&" +list.get(i).getNum3()+"&&" +list.get(i).getNum4()+"&&" +list.get(i).getNum5()+"&&" +list.get(i).getNum6()+"&&" +list.get(i).getTaocan());
                //System.out.println("哦哦哦哦哦");
                apiExcelService.insertd2(
                        String.valueOf(list.get(i).getDate1())
                        ,String.valueOf(list.get(i).getProv_desc())
                        ,String.valueOf(list.get(i).getCity_desc())
                        ,String.valueOf(list.get(i).getUser_id())
                        ,String.valueOf(list.get(i).getTaocan_name())
                        ,String.valueOf(list.get(i).getChannel())
                        ,String.valueOf(list.get(i).getNum1())
                        ,String.valueOf(list.get(i).getNum2())
                        ,String.valueOf(list.get(i).getNum3())
                        ,String.valueOf(list.get(i).getNum4())
                        ,String.valueOf(list.get(i).getNum5())
                        ,String.valueOf(list.get(i).getNum6())
                        ,String.valueOf(list.get(i).getTaocan()));
            }
            // 存储完成清理 list
            list.clear();
            // 延迟查看数据打印情况
            try {
                Thread.sleep(1);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
    }
    /**
     * 所有数据解析完成了 都会来调用
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        System.out.println("最后一次解析了" + list.size() + "条数据！");
        for (int i=0;i<list.size();i++) {
            System.out.println("打印list数据2:"+list.get(i).getDate1()+"&&" +list.get(i).getProv_desc()+"&&" +list.get(i).getCity_desc()+"&&" +list.get(i).getUser_id()+"&&" +list.get(i).getTaocan_name()+"&&" +list.get(i).getChannel()+"&&" +list.get(i).getNum1()+"&&" +list.get(i).getNum2()+"&&" +list.get(i).getNum3()+"&&" +list.get(i).getNum4()+"&&" +list.get(i).getNum5()+"&&" +list.get(i).getNum6()+"&&" +list.get(i).getTaocan());
            //System.out.println("哦哦哦哦哦");
            apiExcelService.insertd2(
                    String.valueOf(list.get(i).getDate1())
                    ,String.valueOf(list.get(i).getProv_desc())
                    ,String.valueOf(list.get(i).getCity_desc())
                    ,String.valueOf(list.get(i).getUser_id())
                    ,String.valueOf(list.get(i).getTaocan_name())
                    ,String.valueOf(list.get(i).getChannel())
                    ,String.valueOf(list.get(i).getNum1())
                    ,String.valueOf(list.get(i).getNum2())
                    ,String.valueOf(list.get(i).getNum3())
                    ,String.valueOf(list.get(i).getNum4())
                    ,String.valueOf(list.get(i).getNum5())
                    ,String.valueOf(list.get(i).getNum6())
                    ,String.valueOf(list.get(i).getTaocan()));
        }
        apiExcelService.fazhan_status_update("1"); // 0进行中，1成功
        System.out.println("所有数据解析完成！");
    }
    //读取表头内容
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        System.out.println("表头信息："+headMap);
    }
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94

    前端页面

    DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>excel数据导入数据库title>
head>
<body>
<script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-2.1.1.min.js">script>
<div>
    <div>
    <h1>点击加载excel数据到mysqlh1>
        <form id="s11" >
            <label for="path1">请输入excel存放路径:label><br> 
            <input type="text" id="path1" name="path1"  >
            <br>
            <label for="sheetname">请输入加载的sheet页名字:label><br>
            <input type="text" id="sheetname" name="sheetname" ><br><br>
            <input type="submit" value="提交"  onclick="login2()">
        form>
    div>
    <div>
            <p style="color: deepskyblue">执行状态p>
            <button id="dd" onclick="login1()" >点击查看button>
            <h1 id="status">状态h1>
    div>

div>


<script type="text/javascript">
    function login1() {  // 查看导入状态
        $.ajax({
            type: 'GET',
            url: "http://localhost:8089/ajaxg",
            dataType: 'json',
            cache: false,
            contentType: "application/json;charset=utf-8",
            success:function(data,status){
                //alert("数据：" + data + "\n状态：" + status);
                console.log("@@@@@@:"+data.status);
                if (data.status=='0'){
                    document.getElementById("status").innerHTML = "导入中";
                }else if (data.status=='1'){
                    document.getElementById("status").innerHTML = "导入完成请查看";
                }
            }
        });
    }

    function login2() {  // 使用post请求给后台发送数据 方法1
        // 获取表单数据
        var vid = s11.path1.value;
        var vname = s11.sheetname.value;
        var data_d={"path1": vid,"sheetname": vname};
        $.ajax({
            url: "http://localhost:8089/ajax",
            //data: JSON.stringify({"id": "123","name": 'jenny'}),  // 将数据转为json格式
            data: JSON.stringify(data_d),  // 将数据转为json格式-这里的数据会默认提交到post请求接口
            type: "POST",
            dataType: "json",
            contentType: "application/json;charset=utf-8",
            success:function(data,status){   // 注意这里没有返回值，应该是服务端的接口返回值一致导致，post返回值应为对象类型
                console.log("数据：" + data + "\n状态：" + status);
            }
        });
    }
script>
body>
html>

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69

    启动类

    package com.mm.shop_pro;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class ShopProApplication {

    public static void main(String[] args) {
        SpringApplication.run(ShopProApplication.class, args);
    }
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    配置信息

    server.port=8089
#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mm?useUnicode=true&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=123456

# 打开配置避免数据表字段出现驼峰命名加载时出现空
mybatis.configuration.map-underscore-to-camel-case=true

#设置页面存放的位置
spring.thymeleaf.prefix=classpath:/templates/
spring.thymeleaf.suffix=.html
#关闭页面缓存-不然下次打开页面可能会出现历史数据
spring.thymeleaf.cache=false 

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14

    启动后登录页面查看
    http://localhost:8089/index

  • 相关阅读:
    js如何定义二位数组然后转josn数据，ajax上传给php，php通过json_decode解析
    RK3568驱动指南｜第七期-设备树-第56章 设备树基本语法
    【OpenCV】- 分水岭算法
    telegrom bot 发送消息
    web前端-html自定义列表
    MyBatisPlus属性自动填充和乐观锁插件+查询删除操作
    库引入方法
    Java：ArrayList源码解析
    图解 LeetCode 算法汇总——二分查找
    测试用例的设计
  • 原文地址：https://blog.csdn.net/qq_43308318/article/details/126078158