java使用POI 读取excel2010文件 并存入oracle数据库

首先将使用到的数据库操作封装起来

 

import java.sql.*;

public class DB {
	// 单例模式
	/*
	 * 单例模式是一种常用的软件设计模式。在它的核心结构中只包含一个被称为单例类的特殊类。
	 * 通过单例模式可以保证系统中一个类只有一个实例而且该实例易于外界访问，从而方便对实例个数的控制并节约系统资源。
	 * 如果希望在系统中某个类的对象只能存在一个，单例模式是最好的解决方案。
	 */
	private static DB db;// 保证整个系统中只存在DB的一个对象db

	static {// 当使用该类时首先执行static中的语句
		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private DB() {
	}// 单例模式不允许其他类创建对象

	public static Connection getConn() {
		Connection conn = null;
		String url = "jdbc:oracle:thin:@10.218.49.217:1521:KTSC";
		// 数据库连接，oracle代表链接的是oracle数据库；thin:@10.218.49.217代表的是数据库所在的IP地址；
		// 1521代表链接数据库的端口号；KTSC代表的是数据库名称
		String UserName = "A1";// 数据库用户登陆名
		String Password = "!qazjkwsx";// 密码
		try {
			conn = DriverManager.getConnection(url, UserName, Password);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	public static void closeConn(Connection conn) {
		try {
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static PreparedStatement getPstmt(Connection conn, String sql) {
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pstmt;
	}

	public static void closePstmt(PreparedStatement pstmt) {
		try {
			if (pstmt != null) {
				pstmt.close();
				pstmt = null;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

读取指定路径文件夹下的所有.xsl文件 并将其内容读取出来  添加到数据库 这里用到的是POI 3.9的jar包

 

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.io.*;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Test {

	public static void main(String[] args) {
		Connection conn = DB.getConn();
		Test test = new Test();
		String filepath = "F:\\sj work\\test"; //文件夹路径 
		try {
			test.readfile(filepath, conn);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			DB.closeConn(conn);
		}
	}

	/**
	 * 读取某个文件夹下的所有文件
	 */
	public void readfile(String filepath, Connection conn)
			throws FileNotFoundException, IOException {

		File file = new File(filepath);
		if (file.isDirectory()) {
			System.out.println("文件夹");
			String[] filelist = file.list();
			for (int i = 0; i < filelist.length; i++) {
				File readfile = new File(filepath + "\\" + filelist[i]);
				if (!readfile.isDirectory()) {
					System.out.println("path=" + readfile.getPath());
					System.out.println("absolutepath="
							+ readfile.getAbsolutePath());
					System.out.println("name=" + readfile.getName());
					excelToOracle(conn, readfile);
				}
			}
		}
	}

	public void excelToOracle(Connection conn, File file) {
		//假设数据库表中有4个字段username,password,phone,addr
		String username = "";
		String password = "";
		String phone = "";
		String addr = "";
		String sql = "insert into test values (?,?,?,?)";
		PreparedStatement pstmt = DB.getPstmt(conn, sql);
		BufferedInputStream in = null;
		try {
			in = new BufferedInputStream(new FileInputStream(file));
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		}
		XSSFWorkbook workbook = null;
		try {
			workbook = new XSSFWorkbook(in);
		} catch (IOException e) {
			e.printStackTrace();
		}
		XSSFSheet sheet = workbook.getSheetAt(0);

		for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
			XSSFRow row = sheet.getRow(rowIndex);
			if (row == null) {
				continue;
			}
			XSSFCell cell = null;
			for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
				String value = "";
				cell = row.getCell(columnIndex);
				if (cell != null) {
					// 注意：一定要设成这个，否则可能会出现乱码
					// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
					switch (cell.getCellType()) {
					case XSSFCell.CELL_TYPE_STRING:
						value = cell.getStringCellValue();
						break;
					case XSSFCell.CELL_TYPE_NUMERIC:
						if (HSSFDateUtil.isCellDateFormatted(cell)) {
							Date date = cell.getDateCellValue();
							if (date != null) {
								value = new SimpleDateFormat("yyyy-MM-dd")
										.format(date);
							} else {
								value = "";
							}
						} else {
							value = new DecimalFormat("0").format(cell
									.getNumericCellValue());
						}
						break;
					case XSSFCell.CELL_TYPE_FORMULA:
						// 导入时如果为公式生成的数据则无值
						if (!cell.getStringCellValue().equals("")) {
							value = cell.getStringCellValue();
						} else {
							value = cell.getNumericCellValue() + "";
						}
						break;
					case XSSFCell.CELL_TYPE_BLANK:
						break;
					case XSSFCell.CELL_TYPE_ERROR:
						value = "";
						break;
					case XSSFCell.CELL_TYPE_BOOLEAN:
						value = (cell.getBooleanCellValue() == true ? "Y" : "N");
						break;
					default:
						value = "";
					}
				}
				if (columnIndex == 0 && value.trim().equals("")) {
					break;
				}
				//excel表格中字段顺序为:用户名,密码,电话和地址,为方便起见假设字段一一对应
				if (columnIndex == 0) {
					username = value;
				} else if (columnIndex == 1) {
					password = value;
				} else if (columnIndex == 2) {
					phone = value;
				} else if (columnIndex == 3) {
					addr = value;
				}
				System.out.println(value);
			}

			try {
				pstmt.setString(1, username);
				pstmt.setString(2, password);
				pstmt.setString(3, phone);
				pstmt.setString(4, addr);
				pstmt.executeUpdate();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		try {
			in.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		DB.closePstmt(pstmt);
	}
}