java 插入oracle_Java读取xls文件并插入oracle数据库表中

package com.icss.util;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.text.DecimalFormat;

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

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

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

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

public class exlFile {

public static String outputFile =

"E:\\NQ_SCHEDULE_INFO.xls";

public static String fileToBeRead =

"E:\\NQ_SCHEDULE_INFO.xls";

public void CreateExcel() {

try {

// 创建新的Excel 工作簿

HSSFWorkbook workbook = new

HSSFWorkbook();

//

在Excel工作簿中建一工作表，其名为缺省值

//

如要新建一名为"效益指标"的工作表，其语句为：

// HSSFSheet sheet =

workbook.createSheet("效益指标");

HSSFSheet sheet =

workbook.createSheet();

// 在索引0的位置创建行(最顶端的行)

HSSFRow row =

sheet.createRow((short) 0);

//在索引0的位置创建单元格(左上端)

HSSFCell cell =

row.createCell((short) 0);

// 定义单元格为字符串类型

cell.setCellType(HSSFCell.CELL_TYPE_STRING);

// 在单元格中输入一些内容

cell.setCellValue("sweater");

// 新建一输出文件流

FileOutputStream fOut = new

FileOutputStream(outputFile);

// 把相应的Excel 工作簿存盘

workbook.write(fOut);

fOut.flush();

// 操作结束，关闭文件

fOut.close();

System.out.println("文件生成...");

} catch (Exception e)

{

System.out.println("已运行

xlCreate() : " + e);

}

}

public void readExcel() {

//将被表示成1.3922433397E10的手机号转化为13922433397,不一定是最好的转换方法

DecimalFormat df = new DecimalFormat("#");

try {

// 创建对Excel工作簿文件的引用

HSSFWorkbook workbook = new HSSFWorkbook(new

FileInputStream(

fileToBeRead));

//System.out

// .println("===SheetsNum==="

+ workbook.getNumberOfSheets());//获取sheet数

for (int numSheets = 0; numSheets

< workbook.getNumberOfSheets(); numSheets++) {

if (null !=

workbook.getSheetAt(numSheets)) {

HSSFSheet

aSheet = workbook.getSheetAt(numSheets);//获得一个sheet

//System.out.println("+++getFirstRowNum+++" +

//

aSheet.getFirstRowNum());//

//System.out.println("+++getLastRowNum+++" +

//

aSheet.getLastRowNum());

for (int

rowNumOfSheet = 0; rowNumOfSheet <= aSheet

.getLastRowNum(); rowNumOfSheet++) {

if (null != aSheet.getRow(rowNumOfSheet)) {

HSSFRow aRow = aSheet.getRow(rowNumOfSheet);

//System.out.println(">>>getFirstCellNum

< <

// aRow.getFirstCellNum());

//System.out.println(">>>getLastCellNum

< <

// aRow.getLastCellNum());

for (short cellNumOfRow = 0; cellNumOfRow <=

aRow

.getLastCellNum(); cellNumOfRow++) {

if (null != aRow.getCell(cellNumOfRow)) {

HSSFCell aCell = aRow.getCell(cellNumOfRow);

int cellType = aCell.getCellType();

System.out.println(cellNumOfRow+" cellType :"+cellType);

switch (cellType) {

case 0://Numeric

String strCell = df.format(aCell

.getNumericCellValue());

System.out.println("strCell0===============:"+strCell);

break;

case 1://String

strCell = aCell.getStringCellValue();

System.out.println("strCell1￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥:"+strCell);

break;

default:

System.out.println("格式不对不读");//其它格式的数据

}

}

}

}

}

}

}

} catch (Exception e) {

System.out.println("ReadExcelError" + e);

}

}

public static void main(String[] args) throws

SQLException {

try {

//1. 连接Oracle

8/8i/9i的驱动程序：

Class.forName("oracle.jdbc.driver.OracleDriver").newInstance(); //2. 建立连接：

Connection

con=DriverManager.getConnection("jdbc:oracle:thin:@192.168.30.150:1521:orcl","JNNQ","JNNQ");

//

Statement

sm=con.createStatement();

//String sql="insert into

nq_schedule_info names(REPORT_ID,REPORT_CODE,TBY,TBR,

COMMENTT)values (?,?,?,?,?)";

//seq_product.nextval

String sql="insert into

nq_schedule_info values

(nq_schedule_info_sequence.nextval,?,?,?,?,?)";

PreparedStatement

pst=con.prepareStatement(sql);

//将被表示成1.3922433397E10的手机号转化为13922433397,不一定是最好的转换方法

DecimalFormat df = new DecimalFormat("#");

try

{

//

创建对Excel工作簿文件的引用

HSSFWorkbook

workbook = new HSSFWorkbook(new FileInputStream(

fileToBeRead));

//System.out

// .println("===SheetsNum===" +

workbook.getNumberOfSheets());//获取sheet数

for (int

numSheets = 0; numSheets <

workbook.getNumberOfSheets(); numSheets++) {

if (null != workbook.getSheetAt(numSheets)) {

HSSFSheet aSheet = workbook.getSheetAt(numSheets);//获得一个sheet

//System.out.println("+++getFirstRowNum+++" +

// aSheet.getFirstRowNum());//

//System.out.println("+++getLastRowNum+++" +

// aSheet.getLastRowNum());

int p1=0,p4 = 0,p3=0,p0 = 0;

String s2 = null,s5=null;

for (int rowNumOfSheet = 0; rowNumOfSheet <=

aSheet

.getLastRowNum(); rowNumOfSheet++) {

if (null != aSheet.getRow(rowNumOfSheet)) {

HSSFRow aRow = aSheet.getRow(rowNumOfSheet);

//System.out.println(">>>getFirstCellNum

< <

// aRow.getFirstCellNum());

//System.out.println(">>>getLastCellNum

< <

// aRow.getLastCellNum());

for (short cellNumOfRow = 0; cellNumOfRow <=

aRow

.getLastCellNum(); cellNumOfRow++) {

if (null != aRow.getCell(cellNumOfRow)) {

HSSFCell aCell = aRow.getCell(cellNumOfRow);

int cellType = aCell.getCellType();

String strCell;

System.out.println(cellNumOfRow+" cellType :+++++++++++++++"+cellType);

switch (cellType) {

case 0://Numeric

strCell = df.format(aCell

.getNumericCellValue());

System.out.println("strCell0===============:"+strCell);

if(0==cellNumOfRow) p0=Integer.parseInt(strCell);

if(1==cellNumOfRow) p1=Integer.parseInt(strCell);

if(3==cellNumOfRow) p3=Integer.parseInt(strCell);

if(4==cellNumOfRow) p4=Integer.parseInt(strCell);

break;

case 1://String

strCell = aCell.getStringCellValue();

System.out.println("strCell1￥￥￥￥￥￥￥￥￥￥￥￥￥￥￥:"+strCell);

if(2==cellNumOfRow) s2=strCell;

if(5==cellNumOfRow) s5=strCell;

break;

default:

if(0==cellNumOfRow) p0=0;

if(1==cellNumOfRow) p1=0;

if(3==cellNumOfRow) p3=0;

if(4==cellNumOfRow) p4=0;

if(2==cellNumOfRow) s2="0";

if(5==cellNumOfRow) s5="0";

System.out.println("格式不对不读");//其它格式的数据

}

}

}

System.out.println("pppppppppppppppppppppppppppp"+p0+" "+p1+" "+p3+" "+p4+" "+s2+" "+s5+" ");

//pst.setInt(0, "");

pst.setInt(1, p1);

pst.setString(2, s2);

pst.setInt(3, p3);

pst.setInt(4, p4);

pst.setString(5, s5);

pst.execute();

con.commit();

}

}

}

}

} catch

(Exception e) {

System.out.println("????????");

System.out.println("ReadExcelError" + e);

}

ResultSet

rs=sm.executeQuery(sql);

while (rs.next())

{

//对结果的处理

}

rs.close();

sm.close();

con.close();

} catch

(InstantiationException e) {

// TODO Auto-generated catch

block

e.printStackTrace();

} catch (IllegalAccessException e) {

// TODO Auto-generated catch

block

e.printStackTrace();

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch

block

e.printStackTrace();

}

exlFile poi = new

exlFile();

//poi.CreateExcel();

poi.readExcel();

}

}