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;
}
}
java将excel数据导入数据库
最新推荐文章于 2025-08-08 22:01:10 发布