java操作excel将数据导入mysql数据库中

java操作excel将数据导入mysql数据库中

前期准备

• 架包下载地址：
  https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl/2.6.12
• mysql下载地址：
  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路径 
	}
}