java将excel数据导入数据库

本文介绍了一个使用Java实现从Excel文件中读取数据并将其插入到MySQL数据库的方法。示例代码展示了如何利用Apache POI库操作Excel文件,并通过JDBC连接MySQL执行插入操作。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >


package com.expai.utils;

import java.io.BufferedWriter;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

/**
* @author shibin
*
*/
public class ExcelMySql {
//加载mysql驱动链接到数据库
public static String driver = "com.mysql.jdbc.Driver";
public static String url = "jdbc:mysql://localhost:3306/expai?characterEncoding=UTF-8";
public static String user = "root";
public static String pwd = "123456";
//读取E盘下的Book1这个excel表
public static String fileToBeRead = "E:/Book1.xls";
@SuppressWarnings("deprecation")
public static void main(String[] args) {
//拼接我想要的数据;
String mm="11";
String dd="08";
String date="2013-"+mm+"-"+dd+" 00:00:00";
String m=mm+dd;
String y="2013"+m+"";
int i=0;
int j=0;
Connection conn = null;
PreparedStatement stmt = null;

try {
conn = getConn();
} catch (Exception e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}

try {
HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
fileToBeRead));
BufferedWriter bw = new BufferedWriter(
new FileWriter("E:\\www.xls"));
// 多sheet
for (int numSheets = 0; numSheets < workbook.getNumberOfSheets(); numSheets++) {
if (null != workbook.getSheetAt(numSheets)) {
HSSFSheet aSheet = workbook.getSheetAt(numSheets);// 获得一个sheet
String sql = "insert into `tb_boutique`(`name`,`price`,`describe`,`source`,`outUrl`,`showDate`,`largeUrl`,`smallUrl`,`praisecount`)"
+ " values(?,?,?,?,?,?,?,?,?)";
System.out.println(sql);
// 对行进行遍历

for (int rowNumOfSheet = 1; rowNumOfSheet <= aSheet
.getLastRowNum(); rowNumOfSheet++) {

try {
stmt = conn.prepareStatement(sql);
} catch (SQLException e1) {
e1.printStackTrace();
}

if (null != aSheet.getRow(rowNumOfSheet)) {
HSSFRow aRow = aSheet.getRow(rowNumOfSheet);
// 对列进行遍历
i++;
j++;
for (short cellNumOfRow = 0; cellNumOfRow < 5; cellNumOfRow++) {

System.out.println("===="+aSheet.getRow(rowNumOfSheet).getCell(cellNumOfRow));
stmt.setString(
6,
date);
if(i==10){
stmt.setString(
7,
"images/large"+y+"/"+m+"-"+i+".jpg");
}else{
stmt.setString(
7,
"images/large"+y+"/"+m+"-0"+i+".jpg");
}


if(j==10){
stmt.setString(
8,
"images/small"+y+"/"+m+"-"+j+".jpg");
}else{
stmt.setString(
8,
"images/small"+y+"/"+m+"-0"+j+".jpg");
}


stmt.setInt(
9,
(int) (Math.random()*1000));

aRow.getCell(cellNumOfRow).setCellType(Cell.CELL_TYPE_STRING);
stmt.setString(cellNumOfRow + 1, aRow
.getCell(cellNumOfRow) == null ? null
: aRow.getCell(cellNumOfRow)
.getStringCellValue());
}

}

try {
stmt.executeUpdate();
} catch (SQLException e) {
bw.write("sheet" + numSheets + " row "
+ rowNumOfSheet + "\n" + e.getMessage());
bw.newLine();
e.printStackTrace();
} finally {
stmt.close();
}
}
}
}
bw.flush();
bw.close();

} catch (Exception e) {
System.out.println("ReadExcelError" + e);
}

try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}

private static Connection getConn() throws Exception {
Class.forName(driver).newInstance();
Connection conn = null;
if (user == null || user.equals("")) {
conn = java.sql.DriverManager.getConnection(url);
} else {
conn = java.sql.DriverManager.getConnection(url, user, pwd);
}

return conn;
}
}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值