用Java代码读取Ecxel文件，再存入mysql数据库

jxl包是别人做好的专门针对Excel文件操作的开源项目，用里面的类来实现对Excel的实现

DBUtils是一个封装类，里面封装了连接数据库的数据库连接池，对数据库的增删查改操作

package com.yy.readexcel;

import java.io.File;import java.io.IOException;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import com.softeem.dbutils.DBUtils;import com.softeem.dbutils.DBUtils.CallBack;import jxl.Sheet;import jxl.Workbook;import jxl.read.biff.BiffException;/** *  * 读取Ecxel文件，再存入数据库 */public class ExcelDemo {//读取Excel文件，参数是Excel文件，返回值是装着所有Excel数据的集合

 //读取Excel的Goods表public List<Goods> readGoods(File file) {List<Goods> list = new ArrayList<>();Goods goods = null;Workbook workbook = null;try {// 创建一个工作簿workbook = Workbook.getWorkbook(file);// 获取所有表单对象// Sheet[] sheets = workbook.getSheets();// 获取指定索引的表单Sheet sheet = workbook.getSheet(0);// 获取指定名称的表单// Sheet sheet = workbook.getSheet("Sheet1");// 获取总行数int rows = sheet.getRows();for (int i = 1; i < rows; i++) {goods = new Goods();String s1 = sheet.getCell(0, i).getContents(); // idString 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(); // 类别idgoods.setGoodsname(s2);goods.setId(TypeTools.getInt(s1));goods.setPrice(TypeTools.getBigDecimal(s3));goods.setOffset(TypeTools.getDouble(s4));goods.setTime(TypeTools.getDate(s5));goods.setCount(TypeTools.getInt(s6));goods.setCid(TypeTools.getInt(s7));list.add(goods);}} catch (BiffException | IOException e) {e.printStackTrace();} finally {if (workbook != null)workbook.close();}return list;}//读取Excel的type表public List<Type> readType(File file) {List<Type> list = new ArrayList<>();Type type = null;Workbook workbook = null;try {// 创建一个工作簿workbook = Workbook.getWorkbook(file);// 获取所有表单对象// Sheet[] sheets = workbook.getSheets();// 获取指定索引的表单Sheet sheet = workbook.getSheet(0);// 获取指定名称的表单// Sheet sheet = workbook.getSheet("Sheet1");// 获取总行数int rows = sheet.getRows();for (int i = 1; i < rows; i++) {type = new Type();String s1 = sheet.getCell(0, i).getContents(); // idString s2 = sheet.getCell(1, i).getContents(); // 类别名type.setId(TypeTools.getInt(s1));type.setName(s2);list.add(type);}} catch (BiffException | IOException e) {e.printStackTrace();} finally {if (workbook != null)workbook.close();}return list;}// 将读取到的type写入数据库public void addType() {// 读取excelList<Type> type = readType(new File("C:\\Users\\47150\\Desktop\\类别表.xls"));try {PreparedStatement ps = DBUtils.getConn().prepareStatement("insert into type(id, cname) values(?,?)");for (Type type2 : type) {ps.setInt(1, type2.getId());ps.setString(2, type2.getName());ps.addBatch();ps.executeBatch();}} catch (SQLException e) {e.printStackTrace();}}//将读取到的Goods放入数据库public void addGoods() {List<Goods> goods = readGoods(new File("C:\\Users\\47150\\Desktop\\商品表.xls"));try {PreparedStatement ps1 = DBUtils.getConn().prepareStatement("insert into goods(id, goodsname, price, offset, time, count, cid) values(?,?,?,?,?,?,?)");for (Goods goods2 : goods) {ps1.setInt(1, goods2.getId());ps1.setString(2, goods2.getGoodsname());ps1.setBigDecimal(3, goods2.getPrice());ps1.setDouble(4, goods2.getOffset());ps1.setObject(5, goods2.getTime());//这里因为goods里面的time是Date.util包的，不能随便类型转换，就用Object接收它ps1.setInt(6, goods2.getCount());ps1.setInt(7, goods2.getCid());ps1.addBatch();ps1.executeBatch();}} catch (SQLException e) {e.printStackTrace();}}// 根据类别显示所有商品的信息(传入类别的id)public List<Goods> showInfo(int cid, int currentPage, int pageSize) {String sql = "select * from goods where cid = ? order by count limit ?,?";return DBUtils.queryList(sql, new CallBack<Goods>() {@Overridepublic List<Goods> getDatas(ResultSet rs) {List<Goods> goods = null;Goods g = null;goods = new ArrayList<>();try {while (rs.next()) {g = new Goods();g.setId(rs.getInt("id"));g.setGoodsname(rs.getString("goodsname"));g.setPrice(rs.getBigDecimal("price"));g.setOffset(rs.getDouble("offset"));g.setTime(rs.getDate("time"));g.setCount(rs.getInt("count"));g.setCid(rs.getInt("cid"));goods.add(g);}} catch (SQLException e) {e.printStackTrace();}return goods;}}, cid, (currentPage - 1) * pageSize, pageSize);}// 2.模糊搜索,根据商品信息(名称或类别) goodsname, cnamepublic List<Goods> find(String cname, String gname, int currentPage, int pageSize){String sql = "select * from goods,type where goods.cid = type.id and (cname like ? or gname like ?) limit ?,?";

//DBUtils里面封装好的遍历所有数据的方法，第一个参数是sql语句，第二个是抽象方法类（回调处理），第三个是sql语句的参数设置List<Goods> goods2 = DBUtils.queryList(sql, new CallBack<Goods>() {@Overridepublic List<Goods> getDatas(ResultSet rs) {List<Goods> goods = new ArrayList<>();Goods g = null;try {while (rs.next()) { g = new Goods();try {g.setId(rs.getInt("id"));g.setGoodsname(rs.getString("goodsname"));g.setPrice(rs.getBigDecimal("price"));g.setOffset(rs.getDouble("offset"));g.setTime(rs.getDate("time"));g.setCount(rs.getInt("count"));g.setCid(rs.getInt("cid"));} catch (SQLException e) {e.printStackTrace();}goods.add(g);}} catch (SQLException e) {e.printStackTrace();}return goods;}} ,"%" + cname + "%", "%" + gname + "%", (currentPage - 1) * pageSize, pageSize);return goods2;}public static void main(String[] args) {// List<Type> type = new ExcelDemo().readType(new// File("C:\\Users\\47150\\Desktop\\类别表.xls"));// for (Type type2 : type) {// System.out.println(type2.toString());// new ExcelDemo().addType();// new ExcelDemo().addGoods();// List<Goods> good = new ExcelDemo().readGoods(new// File("C:\\Users\\47150\\Desktop\\商品表.xls"));// System.out.println(s);/*List<Goods> good = new ExcelDemo().showInfo(35, 1, 10);for (Goods goods : good) {System.out.println(goods.toString());}*/}}

DBUtils类是封装好的连接数据库，以及数据的CRUD操作，这里没有详细给出，以上就是对Excel数据的读取和写入数据库的Java代码