java将excel数据导入数据库_JAVA_编程开发_程序员俱乐部

class="java" name="code">
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;
	}
}