将Excel数据批量导入oracle数据库方法

有两种思路:
一.采用JExcelAPI或apache POI来读取Excel中的每行记录,通过程序插入以数据库中; (
可参考http://blog.csdn.net/pengchua/archive/2007/08/09/1733591.aspx)

二.将Excel转化成.csv格式,然后利用Oracle中的sqlldr命令成批导入,具体步骤如下:
1.采用XL2ExternalTables,apache POI 将Excel数据转化为csv格式
测试方法之一:
注意：要将ExternalTableGenerator类中的方法改为public就行了
import java.io.FileInputStream;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class Test {

public static void getCSV(String spreadsheet) {

String sheet = spreadsheet.substring(spreadsheet.lastIndexOf("/"));
POIFSFileSystem fs;
HSSFWorkbook wb;
try {
fs = new POIFSFileSystem(new FileInputStream(spreadsheet));
//绝对路径
wb = new HSSFWorkbook(fs);
ExternalTableGenerator generator generator = new ExternalTableGenerator(sheet);
//文件名
generator.processWorkook(wb);
//要将ExternalTableGenerator类中的方法改为public就行了

} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}

public static void main(String args[]) {

getCSV("C:/Documents and Settings/XXXX/workspace/Test/test.xls");
}
}

测试方法之二：
java -cp XL2ExternalTables.jar;jakarta-poi-1.5.1-final-20020615.jar;jakarta-poi-contrib-1.5.1-final-20020615.jar com.saternos.database.utilities.ExternalTableGenerator new_department_data.xls

2.再利用oracle的sqlldr命令导入数据效率高。

test.ctl
load data
infile 'TEST.csv'
into table TEST
REPLACE
fields terminated by ',' OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(
TEST_ID "SE_AIRCRAFT.nextval" ,
TEST "upper(:TEST)",
TEST_USER "to_number(:TEST_user)",
TEST_DATE DATE "yyyy-mm-dd hh24:mi:ss",
TEST_NOTE

)

sqlldr admin/password@DATABASE control=ctl/TEST.ctl log=out/TEST.log data=data/TEST.csv bad=out/TEST.bad skip=1