从Excel中导入数据到MySQL
2021/7/5 19:19:43
本文主要是介绍从Excel中导入数据到MySQL，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com</groupId>
<artifactId>demo</artifactId>
<version>1.0-SNAPSHOT</version>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<configuration>
<source>7</source>
<target>7</target>
</configuration>
</plugin>
</plugins>
</build>
<dependencies>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.73</version>
</dependency>
<dependency>
<groupId>com.monitorjbl</groupId>
<artifactId>xlsx-streamer</artifactId>
<version>2.1.0</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.6</version>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.30</version>
<!-- <scope>provided</scope>-->
</dependency>
</dependencies>
</project>
package com.demo;
import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;
/**
* 还存在的问题：如果Excel中有一列数据是多种数据类型混合，那么只能取第一行的数据类型
* PS：此程序代码中的Thread.sleep(1000)是为了观察运行时的进程，要不然跑的太快了，眼睛跟不上
* */
public class Application {
/*
* 为了程序的可扩展性，从外界互获取MySQL参数
* url，username，password
*
* driver根据MySQL版本变化，暂时可以不动
*/
private static String url = "";
private static String username = "";
private static String password = "";
private static String driver = "com.mysql.jdbc.Driver";
public static void main(String[] args) {
/*
* 测试的时候把这些参数补上，在idea上跑就行
* 要不跑一次输入一次参数，太麻烦了
* 不过sql文件和错误文件得去项目的同目录下才能看到
* */
Scanner scanner = new Scanner(System.in);
//输入文件路径
System.out.print("FileOrDir Path：");
String readFile = scanner.next();
//输入MySQL连接参数
System.out.print("Mysql-jdbcUrl ：");
url = scanner.next();
//输入MySQL用户名参数
System.out.print("Mysql-username：");
username = scanner.next();
//输入MySQL密码参数
System.out.print("Mysql-password：");
password = scanner.next();
//记录任务开始时间
long startTime = System.currentTimeMillis();
//打印一下运行进程
System.out.println(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date()) + "\t正在准备程序运行环境......");
System.out.println(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date()) + "\t正在获取MySQL连接......");
try {
//获得 mysql 的 jdbc 连接
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, username, password);
//获得连接池并将连接池传递给ExcelReadAndWrite类方便调用
ExcelReadAndWrite.statement = conn.createStatement();
/*
* 任务主要执行的入口
* 执行Excel的读和MySQL写操作
*
* 可以在此判断输入的路径是文件或文件夹
* */
int a = 0; //记录下任务数，记不记都行，想看就记，不想看就拉到
File file = new File(readFile); //加载文件或目录
//判断路径是文件还是目录
if (file.isFile()) {
a++;
System.out.println("开启任务-----------" + file.getName());
//执行入口
ExcelReadAndWrite.readExcelFile(file);
} else if (file.isDirectory()) {
File[] files = file.listFiles();
if (files != null) {
//如果是目录，则遍历目录中符合条件的文件
for (int i = 0; i < files.length; i++) {
if (files[i].getName().matches(".*\\.xlsx")) {
System.out.println();
System.out.println("开启任务：" + ++a + "-----------" + files[i].getName());
//执行入口
ExcelReadAndWrite.readExcelFile(files[i]);
System.out.println();
}
}
} else {
System.out.println("你所输入的文件路径下没有找到目标文件。");
}
} else {
System.out.println("您所输入的路径有误。");
}
/*
* 打印一下任务的信息
* */
System.out.println(" ---------------------------------------------------------------------- ");
for (int i = 0; i < ExcelReadAndWrite.listInfo.size(); i++) {
System.out.println(ExcelReadAndWrite.listInfo.get(i));
System.out.println("|----------------------------------------------------------------------|");
}
System.out.println(String.format("|%-64s|", "\tTableSum : " + a));
System.out.println(" ---------------------------------------------------------------------- ");
//关闭MySQL连接
conn.close();
Thread.sleep(1000);
System.out.println(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date()) + "\t所有任务完成，正在关闭......");
}
//处理下可能出现的问题
catch (InterruptedException e) {
System.out.println("Thread异常");
e.printStackTrace();
} catch (SQLException e) {
System.out.println("SQL执行异常");
e.printStackTrace();
} catch (ClassNotFoundException e) {
System.out.println("MySQL驱动加载异常");
e.printStackTrace();
}
//记录任务结束时间
long endTime = System.currentTimeMillis();
System.out.println("任务用时：" + (endTime-startTime)/1000 + "." + (endTime-startTime)%1000 + "s");
System.out.println("再见！");
}
}
package com.demo;
import com.monitorjbl.xlsx.StreamingReader;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.*;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;
public class ExcelReadAndWrite {
/**
* tableName: 文件名去后缀，在MySQL建表时使用这个名字
* statement: MySQL连接池，用来执行sql
* listNames: 存放表头，即字段名，使用ArrayList保存，一次保存，多次使用，查询快些（其实没差多少，保证顺序就行）
* mapNATs: 以键值对的形式保存字段名和字段类型，名字做键，类型做值，（类型不重要，那个查的快用哪个）
* listInfo: 记录一下执行任务的信息
* */
private static String tableName;
static Statement statement;
private static List<String> listNames = new ArrayList<>();
private static Map<String,String> mapNATs = new HashMap<>();
static List<String> listInfo = new ArrayList<>();
/*
* 通过静态代码块添加一下listInfo的表头信息
* */
static {
listInfo.add(String.format("|%-16s|%-16s|%-16s|","\tTableName","\tDataSum","\tFailSum"));
}
/**
* 获取Excel文件
* */
public static void readExcelFile(File file){
try {
System.out.println(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date()) + "\t正在读取Excel文件......");
Thread.sleep(1000);
//获取一下我们要处理的文件的名字，去掉后缀，在MySQL建表的表名也用这个
tableName = file.getName().split("\\.")[0];
System.out.println(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date()) + "\t正在准备" + tableName + "表的处理......");
Thread.sleep(1000);
//流式读取Excel文件,最后产生一个表格
FileInputStream fileInputStream = new FileInputStream(file);
/*
* 流式读取Excel，存入缓存
* rowCacheSize(500) 存入缓存的行数
* bufferSize(10240) 存入缓存的字节数
* open(fileInputStream) 流式读取的位置
*
* 在流式读取时，无法回头，我们无法查询指定行数的信息，即不能使用getRow（i）等方法
* 因为我们在缓存中存放的并不是整张表，当下一批数据存入缓存时会冲掉上一批处理完的数据，我们面对的只有我们指定大小的那部分数据
* */
Workbook workbook = StreamingReader.builder().rowCacheSize(500).bufferSize(10240).open(fileInputStream);
Sheet sheet = workbook.getSheetAt(0);
//解析这个表格并存入MySQL中
analysisSheet(sheet);
//清空list 和 map 中的值 方便遍历文件夹时二次调用
listNames = new ArrayList<>();
mapNATs = new HashMap<>();
//关闭流
workbook.close();
fileInputStream.close();
} catch (IOException | InterruptedException e) {
e.printStackTrace();
}
}
/**
* 解析Excel表格
* */
private static void analysisSheet(Sheet sheet) throws IOException {
//实例化一个写入file，如果出错，就把日志写进去
FileWriter fileWriter1 = new FileWriter(System.getProperty("user.dir") + "\\err.sql");
//我们对解析好的数据采用提交执行 sql语句 的方式将其插入到mysql对应的表中
//在这里创建一个文件保存一下我们的sql语句
FileWriter fileWriter2 = new FileWriter(System.getProperty("user.dir") + "\\" + tableName + ".sql");
//记录我们在指定的表中插入了多少条数据
int a = 0;
//如果sql执行失败，记录一下有多少条失败的
int failSum = 0;
//遍历行
for (Row row : sheet){
a++;
/*
* 从第一行中解析出字段的名字，存入list中
* 字段名就是excel的表头
* 保持与Excel中顺序相同
* */
if (row.getRowNum() == 0){
for (Cell cell : row){
listNames.add(cell.getStringCellValue());
}
}
/*
* 从第二段中解析出字段的类型，与字段名一起以键值对的形式存入一个map中
* 在这里不能使用foreach遍历单元格
* 会出现单元格无值而跳过的情况，使得最后的结果缺失
* 我们期待的结果是，当单元格为空时，返回一个空字符来作为值
* 因为第一行字段名的单元格不存在缺失的情况，所以我们使用字段数量来作为一个单元格数的衡量标准
* */
if(row.getRowNum() == 1){
for (int i = 0; i < listNames.size(); i++) {
//这个方法的作用是传入单元格，返回单元格类型
mapNATs.put(listNames.get(i),getCellType(row.getCell(i)));
}
//此时字段名与字段类型已经解析完成，需要创建一个 sql 的建表语句
//有两种方式，1：一边解析表格一边执行sql 2：等数据全部写完存入list在执行sql
//这里使用第一种，毕竟流式快啊
try {
System.out.println(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date()) + "\t正在准备sql运行环境......");
//记录我们的sql，写进文件
fileWriter2.write("DROP TABLE IF EXISTS `" + tableName + "`;" + "\n");
fileWriter2.write("******************************************************************\n");
//执行sql语句（这条语句是看数据库中是否存在与我们将要创建的表相同名字的表，如果有就删了他）
statement.executeUpdate("DROP TABLE IF EXISTS `" + tableName + "`;");
Thread.sleep(1000);
System.out.println(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date()) + "\t正在创建目标表.....");
fileWriter2.write(sqlCreateTable());
fileWriter2.write("******************************************************************\n");
//这里在数据库中创建表
statement.executeUpdate(sqlCreateTable());
Thread.sleep(1000);
System.out.println(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date()) + "\t开始插入数据.....");
} catch (SQLException | InterruptedException e) {
e.printStackTrace();
}
}
//在这里插入数据
if (row.getRowNum() >= 1){
try {
//每插入500条数据，输出一下当前进程
if (row.getRowNum()%500==0) {
System.out.println(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").format(new Date()) + "\t正在插入数据....." + row.getRowNum());
}
fileWriter2.write(sqlInsertData(row));
//在这执行插入数据的sql
statement.executeUpdate(sqlInsertData(row));
} catch (Exception e) {
//如果出错，次数加1，并且将出错的语句保存到指定的文件中
failSum++;
fileWriter1.write(sqlInsertData(row));
e.printStackTrace();
}
}
}
/*
* 上面这个大的for循环结束，数据就已经存储好了
* 下面收下尾巴
* */
//打印一下任务信息
listInfo.add(String.format("|%-16s|%-16s|%-16s|","\t" + tableName,"\t" + (a-1),"\t" + failSum));
System.out.println("共插入数据：" + (a-1) + "条");
//文件流关闭
fileWriter1.close();
fileWriter2.close();
}
/**
* 获取指定单元格的类型
* */
private static String getCellType(Cell cell){
//处理空值
if (cell==null || cell.getStringCellValue()==null || cell.getStringCellValue().equals("")){
return "String";
}
//根据Excel中的数据类型和数据的格式去推断我们应该以一种什么样的类型将其存入MySQL中
//String-->varchar（256）这个转化无敌，啥都能转........赛高
switch (cell.getCellType()){
case STRING:
return "String";
case NUMERIC:
if (cell.getStringCellValue().matches(".*/.*/.*")) return "datetime";
if (cell.getStringCellValue().matches("[1-9]\\d*\\.*\\d*")) return "int";
case _NONE:
return "String";
}
return "String";
}
/**
* 获取指定单元格的值，并进行格式化
* */
private static String getCellValue(Cell cell, int i){
/*
* 处理空值
* String 类型返回""
* int，datetime 类型返回null
* */
if (cell==null || cell.getStringCellValue()==null || cell.getStringCellValue().equals("")){
if (mapNATs.get(listNames.get(i)).equals("String")){
return "";
}
if (mapNATs.get(listNames.get(i)).equals("int")){
return "null";
}
if (mapNATs.get(listNames.get(i)).equals("datetime")){
return "null";
}
}
/*
* 根据类型和值，返回一种我们想要的数据格式，比如日期的格式
* */
switch (cell.getCellType()){
case STRING:
return cell.getStringCellValue().replace("\"","”");
case NUMERIC:
if (cell.getStringCellValue().matches(".*/.*/.*")) {
Date dateCellValue = cell.getDateCellValue();
SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
return simpleDateFormat.format(dateCellValue);
}
if (cell.getStringCellValue().matches("\\d*")){
return cell.getStringCellValue();
}
}
return cell.getStringCellValue();
}
/**
* 写建表sql
* */
private static String sqlCreateTable(){
/*
* 纯靠拼接字符串
* create table `tableName`(
* name varchar(32),
* age int,
* birth datetime
* )ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
*
* 字段顺序要与Excel中一样！！！！！！！！！！！！！！所以我用ArrayList存的字段名，保持顺序啊，读的时候按顺序读就不会出错
* */
String sql1 = " CREATE TABLE `" + tableName + "` (\n";
StringBuilder sql2 = new StringBuilder();
for (int i = 0; i < listNames.size(); i++) {
if (mapNATs.get(listNames.get(i)).equals("String")) sql2.append(listNames.get(i)).append(" VARCHAR(256) DEFAULT NULL");
if (mapNATs.get(listNames.get(i)).equals("int")) sql2.append(listNames.get(i)).append(" int DEFAULT NULL");
if (mapNATs.get(listNames.get(i)).equals("datetime")) sql2.append(listNames.get(i)).append(" datetime DEFAULT NULL");
if (i == listNames.size()-1){
sql2.append("\n");
}else {
sql2.append(",\n");
}
}
String sql3 = ")ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;\n";
return sql1 + sql2.toString() + sql3;
}
/**
* 写插入语句的sql
* */
private static String sqlInsertData(Row row){
/*
* 拼字符串吧，自己写条sql一边拼一边运行，等不出问题就成功了
* 我自己写完都不知道咋拼出来的
* insert into `tableName` values(value1,value2,value3,.......)
* String类型的值加""双引号
* int类型啥也不加
* datetime类型加''单引号
* 这个顺序很重要，全靠这个顺序拼字符串，一个完整的sql应该是
* insert into `tableName`(field1,field2,field3,......) values(value1,value2,value3,.......)
* 我在这里把字段名省略了，默认为表中的字段顺序，所以值的顺序千万不能错
* 不过我这个能跑，不出错，hhhhhh~~~~~~~~~
* */
String sql1 = " INSERT INTO `" + tableName + "` VALUES(" ;
for (int i = 0; i < listNames.size(); i++) {
switch (mapNATs.get(listNames.get(i))){
case "int":
sql1 = sql1 + getCellValue(row.getCell(i),i);
if (i < listNames.size()-1){
sql1 = sql1 + ",";
}
break;
case "String":
sql1 = sql1 + "\"" + getCellValue(row.getCell(i),i) + "\"";
if (i < listNames.size()-1){
sql1 = sql1 + ",";
}
break;
case "datetime":
if(getCellValue(row.getCell(i), i).equals("null")){
sql1 = sql1 + "null";
}else {
sql1 = sql1 + "\'" + getCellValue(row.getCell(i), i) + "\'";
}
if (i < listNames.size()-1){
sql1 = sql1 + ",";
}
break;
}
}
return sql1 + ");\n";
}
}
package com.demo;
public class ApplictionP {
public static void main(String[] args) {
System.out.println("/*************************************************************************************************************************************");
System.out.println("**************************************************************************************************************************************");
System.out.println("**************************************************************************************************************************************");
System.out.println();
System.out.println("\t\t欢迎使用 奇怪的小王 开发的工具包 ^o^ ");
System.out.println("\t\t在使用过程中如果遇到各种奇怪的问题，请不要惊讶，本人解决不了的......");
System.out.println("\t\t此工具包使用简单，主要功能就是将Excel的数据存储到MySQL中（其实也没有其他功能） QTQ ");
System.out.println("\t\t在使用时只要你输入几个参数就OK了");
System.out.println();
System.out.println("\t\tFileOrDir Path : 你的excel文件所在的路径,支持文件和目录");
System.out.println("\t\t\t\t 文件路径：指定一个Excel文件，也可以是文件夹路径");
System.out.println("\t\t\t\t 目录路径: 指定目录，会遍历目录下的`.xlsx`后缀的文件");
System.out.println("\t\tMysql_jdbcUrl : 你的jdbc连接位置");
System.out.println("\t\tMysql_username : MySQL用户账号");
System.out.println("\t\tMysql_password : MySQL用户密码");
System.out.println();
System.out.println("**************************************************************************************************************************************");
System.out.println("**************************************************************************************************************************************");
System.out.println("*************************************************************************************************************************************/");
}
}
小王同学写着玩的
这篇关于从Excel中导入数据到MySQL的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
- 2024-09-21MySQL集群部署资料：新手入门教程
- 2024-09-21MySQL集群资料：初学者入门指南
- 2024-09-21部署MySQL集群资料：新手入门教程
- 2024-09-20MySQL集群部署教程：新手入门指南
- 2024-09-20MySQL集群教程：初学者必备指南
- 2024-09-20部署MySQL集群项目实战：新手入门教程
- 2024-09-20如何部署MySQL集群：简单教程
- 2024-09-20MySQL集群部署：新手入门指南
- 2024-09-20部署MySQL集群学习：入门指南
- 2024-09-20部署MySQL集群入门教程
