需求:
根据excle中的msql表定义,创建表到数据库中。
在表定义形式固定,且定义类型规范时,可以分为以下两步来完成。
step1,读取excel表格数据。
step2,将数据转化为sql运行。
如用下图中的表定义,去创建msql数据表。
表1.1
表数据读取
参考文档:
1.https://blog.csdn.net/x_christ1/article/details/83859729
2.https://blog.csdn.net/zhangphil/article/details/85302347
st1.引入maven 坐标
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<!-- 处理excel和上面功能是一样的-->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>
st2.读取数据代码如下
import java.io.File;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class ReadExcel {
public static void main(String[] args) throws Exception {
File xlsFile = new File("test.xls");
// 工作表
Workbook workbook = WorkbookFactory.create(xlsFile);
// 表个数。
int numberOfSheets = workbook.getNumberOfSheets();
// 遍历表。
for (int i = 0; i < numberOfSheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
// 行数。
int rowNumbers = sheet.getLastRowNum() + 1;
// Excel第一行。
Row temp = sheet.getRow(0);
if (temp == null) {
continue;
}
int cells = temp.getPhysicalNumberOfCells();
// 读数据。
for (int row = 0; row < rowNumbers; row++) {
Row r = sheet.getRow(row);
for (int col = 0; col < cells; col++) {
System.out.print(r.getCell(col).toString()+" ");
}
// 换行。
System.out.println();
}
}
}
}
业务相关处理,如一个work.xlsx或者work.xls中有众多的类似表1.1 的sheet的结构,现在将其转为mysql表格创建语句
package com.example.studyspringboot.studyboot.utils.readFile;
import java.io.File;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
public class ReadExcel {
public static void main(String[] args) throws Exception {
File xlsFile = new File("C:\\Users\\Administrator\\Desktop\\company\\project\\work.xlsx");
// 工作表
Workbook workbook = WorkbookFactory.create(xlsFile);
// 表个数。
int numberOfSheets = workbook.getNumberOfSheets();
// 遍历表。
for (int i = 1; i < numberOfSheets; i++) {
Sheet sheet = workbook.getSheetAt(i);
// 行数。
int rowNumbers = sheet.getLastRowNum() + 1;
// Excel第一行。
Row temp = sheet.getRow(0);
if (temp == null) {
continue;
}
//临时表字段
String feilds = "";
//表的第一行获取表名
String tableName="";
//表的描述
String tableDesc="";
//字段数组
String[] feildArray=new String[rowNumbers-1];
// 读数据。
for (int row = 0; row < rowNumbers; row++) {
int cells = temp.getPhysicalNumberOfCells();
Row r = sheet.getRow(row);
for (int col = 0; col < cells+2; col++) {
if(r.getCell(col)==null){
continue;
}
//System.out.print(r.getCell(col).toString()+" ");
feilds+=r.getCell(col).toString()+" ";
}
if(row == 0){
String[] tp = feilds.trim().split("\\s");
tableName=tp[tp.length-2];
tableDesc=tp[tp.length-1];
feilds="";
}else{
feildArray[row-1] = feilds;
feilds="";
}
}
DataToDB.createTable(tableName,tableDesc,feildArray);
}
}
}
生成mysql 语句
package com.example.studyspringboot.studyboot.utils.readFile;
import com.alibaba.fastjson.JSON;
import com.example.studyspringboot.studyboot.utils.jdbc.DBUtils;
import java.sql.PreparedStatement;
public class DataToDB {
public static void createTable(String tablename,String tableDesc,String[] feild) {
// System.out.println("feild="+ JSON.toJSON(feild));
String sql= null;
try {
sql = "create table if not exists "+tablename+" ";
sql+="(";
String[] tempField;
for(int i=0;i<feild.length;i++){
if(feild[i]==null){
continue;
}
tempField = feild[i].trim().split("\\s");
sql+=tempField[1] +" "+tempField[2]+" COMMENT \'"+tempField[0]+"\'";//表字段
sql+=",";
}
sql= sql.substring(0,sql.length()-1);
sql+=")DEFAULT CHARSET=utf8;";
System.out.println("sql=====>");
//修正INT4---> int(4)
sql = sql.replaceAll("INT4","int(4)");
System.out.println(sql);
//sql 可能会出错,由于表格定义不规范
// PreparedStatement ps=null;
//
// ps= DBUtils.getConn().prepareStatement(sql);
// ps.executeUpdate(sql);
// ps.close();
} catch (Exception e) {
System.out.println("建表失败"+e.getMessage());
}
}
}
jdbc链接工具
package com.example.studyspringboot.studyboot.utils.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtils {
//1. 封装获取连接的方法
public static Connection getConn() throws ClassNotFoundException, SQLException {
//注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//获取连接对象
Connection conn = DriverManager.getConnection
("jdbc:mysql://localhost:3306/tempwork","root", "lps123");
//返回连接对象
return conn;
}
//2. 封装关闭资源的方法
public static void close(Connection conn,
Statement stat, ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (stat != null) {
stat.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
生成的sql语句不一定百分之百能运行,因此可以先看看,在连接数据库创建数据表格。