（1）首先Eclipse要与MySQL使用JDBC进行连接
package com.softeem.utils;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
public class DBConnection {
/*声明链接数据库的基本参数*/
private static String DRIVER;
private static String URL;
private static String USER;
private static String PASSWORD;
//在静态块中加载驱动，防止反复加载
static{
try {
//获取属性对象
Properties props = System.getProperties();
//加载指定属性文件,根据属性名获取属性值
props.load(new FileInputStream("src/jdbc.properties"));
DRIVER = props.getProperty("driver");
URL = props.getProperty("url");
USER = props.getProperty("user");
PASSWORD = props.getProperty("password");
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
//封装获取链接的方法
public static Connection getConn()
{
try {
return DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
}
(2)创建MySQL数据库及其表
（3）首先在Eclispe创建一个WorkBook对象，读取Excel文件
public class ExcelToDB {
public List
ReadgoodExcel(File file){
List
list = new ArrayList<>();
goods gs = null;
Workbook wb = null;
try {
//创建一个工作簿
wb = Workbook.getWorkbook(file);
//获取所有表单对象
Sheet[] sheets = wb.getSheets();
//获取指定索引的表单
Sheet sheet = wb.getSheet("Sheet1");
//获取总行数
int rows = sheet.getRows();
for (int i = 1; i < rows; i++) {
gs = new goods();
String s1 = sheet.getCell(0, i).getContents(); //编号
String s2 = sheet.getCell(1, i).getContents(); //商品名
String s3 = sheet.getCell(2, i).getContents(); //单价
String s4 = sheet.getCell(3, i).getContents(); //折扣率
String s5 = sheet.getCell(4, i).getContents(); //时间
String s6 = sheet.getCell(5, i).getContents(); //库存
String s7 = sheet.getCell(6, i).getContents(); //类别编号
gs.setGoodsname(s2);
gs.setId(TypeTools.getInt(s1));
gs.setPrice(TypeTools.getBigDecimal(s3));
gs.setOffset(TypeTools.getDouble(s4));
// long y = TypeTools.getDate(s5).getTime();
// Date d = new Date(y);
//
gs.setTime(TypeTools.getDate(s5));
gs.setCounts(TypeTools.getInt(s6));
gs.setCid(TypeTools.getInt(s7));
list.add(gs);
}
} catch (BiffException | IOException e) {
e.printStackTrace();
}
finally{
if(wb != null) wb.close();
}
return list;
}
然后将WorkBook对象逐一存储到MySQL表中
public void insertgoods(List
list){
Connection conn = DBConnection.getConn();
PreparedStatement ps = null;
try {
ps = conn.prepareStatement("insert into goods(id,goodsname,price,offset,time,counts,cid) values(?,?,?,?,?,?,?)");
for ( goods g :list ) {
ps.setInt(1,g.getId());
ps.setString(2, g.getGoodsname());
ps.setBigDecimal(3, g.getPrice());
ps.setDouble(4, g.getOffset());
ps.setObject(5,g.getTime());
ps.setInt(6, g.getCounts());
ps.setInt(7, g.getCid());
ps.executeUpdate();
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
try {
if(ps != null) ps.close();
if (conn != null) conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
最后运行即可
