Easyexcel·读取excel
使用poi读取excel容易内存溢出-Easyexcel可以很好的解决这个问题
依赖
<?xml version="1.0" encoding="UTF-8"?>
<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.0</modelVersion>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.7.1</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<groupId>com.mm</groupId>
<artifactId>shop_pro</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>shop_pro</name>
<description>shop_pro</description>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<!--mysql驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
</dependency>
<!--数据表实体类接口管理-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.2</version>
</dependency>
<!--excel导入到mysql-->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-test</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
<!--转json类型-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.28</version>
</dependency>
<!--页面模板-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
<resources>
<!--资源管理-->
<resource>
<directory>src/main/resources</directory>
<filtering>true</filtering>
</resource>
</resources>
</build>
</project>
数据表
-- 商品记账表
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');
表的实体类
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;
}
}
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;
}
}
接口
表的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();
}
此接口用于将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();
}
接口的实现类
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();
}
}
测试数据导写入到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);
}
}
}
读取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);
}
}
读取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;
}
}
自定义读取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);
}
}
前端页面
<!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数据到mysql</h1>
<form id="s11" >
<label for="path1">请输入excel存放路径:</label><br> <!--src/main/resources/22.xlsx 0608-->
<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>
启动类
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);
}
}
配置信息
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
启动后登录页面查看
http://localhost:8089/index