poi读取excel数据到数据库中

最近应朋友请求，做一个小程序读取excel数据到Mysql数据库中，项目分析如下
1,大批量数据，多个excel，几万行数据，插入数据库，其中涉及到主外键关联；
2，附带复制每条广告信息，打折信息对应的图片，视频复制到特定文件夹下，同时以全球唯一标识符重新命名，文件名插入带数据库中
3，项目所用jar包为poi-bin-3.8-beta3-20110606.tar.gz中的
4、不足之处，请各位指正，我继续完善

/**
 * 
 */
package cn.edu.zzuli;

/**
 * @author moon
 *
 */
import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.UUID;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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.poifs.filesystem.POIFSFileSystem;

public class POITest {
	private static Connection conn = null;
	private static Statement stmt = null;

	private static boolean connectDB() {

		String url = "";
		String username = "root";
		String password = "123456";

		// 加载驱动程序以连接数据库
		try {

			Class.forName("com.mysql.jdbc.Driver");
			url = "jdbc:mysql://127.0.0.1:3306/huang";

			conn = DriverManager.getConnection(url, username, password);
			stmt = conn.createStatement();
		}
		// 捕获加载驱动程序异常
		catch (ClassNotFoundException cnfex) {
			System.err.println("装载JDBC驱动程序失败。");
			cnfex.printStackTrace();
			return false;
		}
		// 捕获连接数据库异常
		catch (SQLException e) {
			System.err.println("无法连接数据库");
			e.printStackTrace();
			return false;
		}
		return true;
	}

	/**
	 * 复制图片组件
	 * 
	 * @param path 文件路径
	 * @param picdisname 文件名称
	 * @return
	 */
	public static String copyFileDis(String path, String picdisname) {

		String sj = UUID.randomUUID().toString();
		String picfileLast = picdisname.substring(picdisname.lastIndexOf("."));
		String fromFile = sj + "." + picfileLast;
		File oldpic = new File(path + "\\" + picdisname);
		if(!oldpic.exists()){
			System.out.println("复制文件失败，原文件不存在"+picdisname);
			return null;
		}
		File mepic = new File("D:\\mediaResource\\" + fromFile);
		int byteread = 0;
		InputStream in = null;
		OutputStream out = null;
		try {
			in = new FileInputStream(oldpic);
			out = new FileOutputStream(mepic);
			byte[] buffer = new byte[1024];
			while ((byteread = in.read(buffer)) != -1) {
				out.write(buffer, 0, byteread);
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("复制文件失败");
		} finally {
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}

			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return fromFile;
	}

	/**
	 * 查询出来ads表中ads_id的id最大值
	 */
	public static int findMaxAds() {

		int maxId = 0;
		String sql = "select max(ads_id) from ads";
		try {
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				maxId = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return maxId;

	}

	/**
	 * 从广告说明读取数据到数据库中
	 * 
	 * @param path文件路径
	 * @return
	 * @throws ParseException
	 */
	public static boolean readExcelToDB(String path) throws ParseException {

		
		String picname = "logo.jpg";
		String dispath = path;
		// 复制图片，并获得uuid文件名返回给readExcelToDB
		String uuidjpg = POITest.copyFileDis(dispath, picname);

		String temp = path + "\\广告说明.xls";
		POIFSFileSystem fs = null;
		HSSFWorkbook wb = null;
		try {
			fs = new POIFSFileSystem(new FileInputStream(temp));
			wb = new HSSFWorkbook(fs);
		} catch (IOException e) {
			e.printStackTrace();
			return false;
		}

		HSSFSheet sheet = wb.getSheetAt(0); // 读取excel的sheet，0表示读取第一个、1表示第二个.....
		HSSFRow row = null;
		HSSFCell cell = null;
		int ads_type_id = 0;
		String business = "";
		String introduction = "";
		String address = "";
		String name = "";
		String contact = "";
		int rowNum, cellNum;
		int i;
		rowNum = sheet.getLastRowNum();
		for (i = 1; i <= rowNum; i++) {
			row = sheet.getRow(i);
			cell = row.getCell((short) 0);
			if(null ==cell){
				continue;
			}else{
				business = cell.getStringCellValue();
			}		
			cell = row.getCell((short) 1);
			if(null==cell){
				continue;
			}else{
				ads_type_id = (int) cell.getNumericCellValue();
			}
			
			cell = row.getCell((short) 2);
			if(null == cell){
				continue;
			}else{
				introduction = cell.getStringCellValue();
			}
			
			cell = row.getCell((short) 3);
			if(null == cell){
				continue;
			}else{
				address = cell.getStringCellValue();
			}
			
			cell = row.getCell((short) 4);
			if(null ==cell){
				continue;
			}else{
				contact = cell.getStringCellValue();
			}
			
			String sql = "insert into ads(ads_type_id, business,introduction,address,contact,image) values("
					+ ads_type_id
					+ ",'"
					+ business
					+ "','"
					+ introduction
					+ "','"
					+ address
					+ "','"
					+ contact
					+ "','"
					+ uuidjpg
					+ "')";
			try {
				stmt.executeUpdate(sql);
			} catch (SQLException e1) {
				e1.printStackTrace();
				return false;
			}
			if (POITest.discountNews(path) == true) {
				System.out.println("打折详情注入成功。");
			}
		}

		return true;
	}

	/**
	 * 读取打折信息到数据库，同时复制套餐图片、视频
	 * @param path文件路径
	 * @return
	 * @throws ParseException
	 */
	public static boolean discountNews(String path) throws ParseException {

		// 首先复制图片，并获得uuid文件名返回给discountNews
		String temp = path + "\\打折详情\\打折详情.xls";
		POIFSFileSystem fs = null;
		HSSFWorkbook wb = null;
		try {
			fs = new POIFSFileSystem(new FileInputStream(temp));
			wb = new HSSFWorkbook(fs);
		} catch (IOException e) {
			e.printStackTrace();
			return false;
		}

		HSSFSheet sheet = wb.getSheetAt(0); // 读取excel的sheet，0表示读取第一个、1表示第二个.....
		HSSFRow row = null;
		HSSFCell cell = null;
		int ads_type_id = 0;
		String label = "";
		String detail = "";
		Date begin_time;
		Date end_time;
		Timestamp begin_time_for;
		Timestamp end_time_for;
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		int rowNum, cellNum;
		int adsId = POITest.findMaxAds();
		int i;
		rowNum = sheet.getLastRowNum();
		for (i = 1; i <= rowNum; i++) {
			row = sheet.getRow(i);
			cell = row.getCell((short) 0);
                        //判断为空如何处理
			if(null == cell){
				continue;
			}else{
				
				label = cell.getStringCellValue();
			}
			String picname = label + ".png";
			String dispath = path + "\\打折详情\\";
			String videoname = label+".flv";
			String uuidjpg = POITest.copyFileDis(dispath, picname);
			String videouuidname =  POITest.copyFileDis(dispath, videoname);
			
			cell = row.getCell((short) 1);
			if(null == cell){
				continue;
			}else{
				detail = cell.getStringCellValue();
			}
			
			cell = row.getCell((short) 2);
			if (null == cell) {
				continue;
			} else {
				Double begin_time_int = cell.getNumericCellValue();
				begin_time = HSSFDateUtil.getJavaDate(begin_time_int);
				String begin_time_string = format.format(begin_time);
				begin_time_for = Timestamp.valueOf(begin_time_string);
			}

			cell = row.getCell((short) 3);
			if (null == cell) {
				continue;
			} else {
				Double end_time_int = cell.getNumericCellValue();
				end_time = HSSFDateUtil.getJavaDate(end_time_int);
				String end_time_string = format.format(begin_time);
				end_time_for = Timestamp.valueOf(end_time_string);
			}

			String sql = "insert into discount_inf(ads_Id,label, detail,print_times,click_times,icon,begin_time,end_time,video) values("+ adsId+ ",'"+ label+ "','"
					+ detail+ "',"+ 0+ ","+ 0+ ",'"+ uuidjpg+ "','"+ begin_time_for+ "','"+ end_time_for + "','"+videouuidname+"')";
			try {
				stmt.executeUpdate(sql);
			} catch (SQLException e1) {
				e1.printStackTrace();
				return false;
			}
		}

		return true;

	}

	public static void main(String[] args) throws ParseException {
		if (connectDB() == true) {
			String path = "d:\\广告模板";
			File file = new File(path);
			String[] fileList = file.list();
			for (int i = 0; i < fileList.length; i++) {
				String temp = path + "\\" + fileList[i];
				if (readExcelToDB(temp) == true)
					System.out.println("数据导入成功");
				else
					System.out.println("数据导入失败");
			}
		}

	}
}

评论

2 楼 蓝色的墨 2012-03-31  

学习一下。

1 楼 秦时明月黑 2011-07-01  

自己顶下，小小的鼓励