java批量写入excel文件,java读取Excel数据，然后写入到txt文件，并批量保存到oracle数据库中...

ReadExcel类：

package com.linbilin.readExcel;

import java.io.FileInputStream;

import java.io.FileWriter;

import java.sql.Connection;

import java.util.ArrayList;

import java.util.LinkedHashMap;

import java.util.List;

import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

/**

* @author Lin

*

*/

public class ReadExcel {

/**

* @param args

* @throws Exception

*/

public static void main(String[] args) throws Exception {

Connection conn=DbUtil.getConnection("jdbc:oracle:thin:@192.168.9.26:1521:orcl","test", "test", DbUtil.ORACLE_DRIVER);

FileInputStream fileIn = new FileInputStream(

"C:\\Users\\Desktop\\数据项集v1.2.xls");

Workbook wb = new HSSFWorkbook(new POIFSFileSystem(fileIn));

int numOfSheets = wb.getNumberOfSheets();

Sheet sheet = null;

Row row = null;

Cell cell = null;

String cellValue = "";

List> list = new ArrayList>();

Map map = null;

FileWriter fileWrite = new FileWriter(

"C:\\Users\\Desktop\\记录.txt");

// 循环遍历

for (int indexOfSheet = 1; indexOfSheet < numOfSheets; indexOfSheet++) {

sheet = wb.getSheetAt(indexOfSheet);

for (int indexOfRowNum = sheet.getFirstRowNum() + 1, rowNum = sheet

.getLastRowNum(); indexOfRowNum <= rowNum; indexOfRowNum++) {

System.out.println("行" + indexOfRowNum);

row = sheet.getRow(indexOfRowNum);

if (row != null) {

if (row.getCell(0).getStringCellValue().isEmpty()) {

continue;

} else {

map = new LinkedHashMap();

for (int indexOfCell = 0; indexOfCell <= 5; indexOfCell++) {

System.out.println("列" + indexOfCell);

cell = row.getCell(indexOfCell);

if (cell != null) {

cellValue = getCellValue(cell);

fileWrite.write(cellValue + "\r\n");

map.put(indexOfCell + "", cellValue);

}

}

if (!map.isEmpty()) {

list.add(map);

}

}

}

}

}

DbUtil.insertDataItem(conn, list);

//System.out.println(list);

// 关闭流

fileWrite.flush();

fileWrite.close();

fileIn.close();

wb.close();

}

public static String getCellValue(Cell cell) {

int cellType = cell.getCellType();

String cellValue = "";

switch (cellType) {

case HSSFCell.CELL_TYPE_NUMERIC:

cellValue = String.valueOf(cell.getNumericCellValue());

break;

case HSSFCell.CELL_TYPE_FORMULA:

try {

cellValue = cell.getStringCellValue();

} catch (IllegalStateException e) {

cellValue = String.valueOf(cell.getNumericCellValue());

}

break;

default:

cellValue = cell.getStringCellValue();

}

return cellValue.trim();

}

}

DbUtil类：

package com.linbilin.readExcel;

import java.sql.Connection;

import java.sql.Driver;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.List;

import java.util.Map;

import java.util.Properties;

public class DbUtil {

/**

* ORACLE_DRIVER

*/

public static final String ORACLE_DRIVER = "oracle.jdbc.driver.OracleDriver";

/**

* getConnection 通过JDBC URL 和用户名密码，驱动类获取连接

*

* @param jdbcUrl

* String JDBC连接串

* @param userName

* String 用户名

* @param password

* String 密码

* @param driver

* 数据库驱动程序

* @return Connection 数据连接，异常则返回null

* @throws SQLException

* 获取数据连接时失败

* @author:Administrator

*/

public static Connection getConnection(String jdbcUrl, String userName, String password, String driver)

throws SQLException {

String message = null;

try {

if (message == null) {

Properties props = new Properties();

props.put("user", userName);

props.put("password", password);

Driver myDriver;

myDriver = (Driver) Class.forName(driver).newInstance();

return myDriver.connect(jdbcUrl, props);

}

} catch (InstantiationException e) {

e.printStackTrace();

} catch (IllegalAccessException e) {

e.printStackTrace();

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

return null;

}

public static final void close(ResultSet rs,PreparedStatement ps,Connection conn){

if(rs != null){

try {

rs.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if(ps != null){

try {

ps.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

if(conn != null){

try {

conn.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

public static void insertDataItem(Connection conn,List> data){

PreparedStatement pst=null;

try {

conn.setAutoCommit(false);

String sql="insert into t_res_dataitem(ID,NAME_EN,NAME_CN,DATA_TYPE,LENGTH,DE_IDENTIFIER) values(?,?,?,?,?,?)";

pst=conn.prepareStatement(sql);

for(Map row:data){

pst.setString(1,row.get("0"));

pst.setString(2,row.get("1"));

pst.setString(3,row.get("2"));

pst.setString(4,row.get("3"));

pst.setString(5,row.get("4"));

pst.setString(6,row.get("5"));

pst.addBatch();

}

pst.executeBatch();

conn.commit();

} catch (SQLException e) {

e.printStackTrace();

}finally{

close(null, pst, conn);

}

}

public static void insert(Connection conn,List> data){

PreparedStatement pst=null;

try {

conn.setAutoCommit(false);

String sql="insert into t_res_map(ID,name) values(?,?)";

pst=conn.prepareStatement(sql);

for(Map row:data){

pst.setString(1,row.get("2"));

pst.setString(2,row.get("3"));

pst.addBatch();

}

pst.executeBatch();

conn.commit();

} catch (SQLException e) {

e.printStackTrace();

}finally{

close(null, pst, conn);

}

}

}