java操作excel将数据导入mysql数据库中
前期准备
架包下载地址： https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl/2.6.12mysql下载地址： https://dev.mysql.com/downloads/mysql/5.7.html#downloads将架包添加到资源：build path->add External Archives安装启动mysql（windows环境下） 1.用管理员身份打开cmd命令行工具，cd到解压文件的bin目录 2.输入mysqld install回车运行，出现安装成功就可以了。 3.输入net start mysql 启动mysql （或打开windows服务手动启动mysql）
具体实现
package javaExcelMysql;
//导入相关的包 mysql
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
//导入操作Excel的包
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import java.io.File;
import java.io.IOException;
//excel 读取类
class excelRead{
private static String driver = "com.mysql.jdbc.Driver";
private static String url = "jdbc:mysql://localhost:3306/mydatabase";
private static String user = "root";
private static String password = "123456";
private static Connection con = null;
private static PreparedStatement pstatement=null;
public static void readExcel(File path) throws BiffException, IOException{
//连接数据库
try {
Class.forName(driver);
} catch (ClassNotFoundException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
con = DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(con!=null) {
System.out.println("sucess connect to mysql");
}
//读取excel数据表
Workbook workbook = Workbook.getWorkbook(path);
Sheet[] sheets = workbook.getSheets();
if(sheets!=null)
{
for(Sheet sheet:sheets)
{
//获取行数
int rows = sheet.getRows();
System.out.println(rows);
//获取列数
int cols = sheet.getColumns();
System.out.println(cols);
//读取数据
for(int row = 2;row<rows;row++) //z这里row从2开始是因为去除了表头占的两行
{
String values[] = new String[3];
for(int col=0;col<cols;col++)
{
//将每行不同列的内容放入数组
values[col] = sheet.getCell(col,row).getContents();
}
//将读取出来的内容写入mysql数据库
try {
pstatement = con.prepareStatement("insert test7 values(?,?,?);");
pstatement.setNString(1, values[0]);
pstatement.setNString(2,values[1]);
pstatement.setNString(3,values[2]);
pstatement.executeUpdate(); //执行sql语句插入内容
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
workbook.close();
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public class testExcel2Mysql {
public static void main(String[] args) throws BiffException, IOException{
File path = new File("D:\\javaio\\testDataExcel\\test_double.xls");
excelRead.readExcel(path); // 传入要操作的excel路径
}
}
