load data from excel to oracle

The below code is used to get the data from the excel sheet

import java.io.IOException;

import java.io.InputStream;

import java.util.Iterator;

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

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

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

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

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

public class POIExample {

public static void main( String [] args ) {

try {

InputStream input = POIExample.class.getResourceAsStream( "qa.xls" );

POIFSFileSystem fs = new POIFSFileSystem( input );

HSSFWorkbook wb = new HSSFWorkbook(fs);

HSSFSheet sheet = wb.getSheetAt(0);

// Iterate over each row in the sheet

Iterator rows = sheet.rowIterator();

while( rows.hasNext() ) {

HSSFRow row = (HSSFRow) rows.next();

System.out.println( "Row #" + row.getRowNum() );

// Iterate over each cell in the row and print out the cell's content

Iterator cells = row.cellIterator();

while( cells.hasNext() ) {

HSSFCell cell = (HSSFCell) cells.next();

System.out.println( "Cell #" + cell.getCellNum() );

switch ( cell.getCellType() ) {

case HSSFCell.CELL_TYPE_NUMERIC:

System.out.println( cell.getNumericCellValue() );

break;

case HSSFCell.CELL_TYPE_STRING:

System.out.println( cell.getStringCellValue() );

break;

default:

System.out.println( "unsuported sell type" );

break;

}

}

}

} catch ( IOException ex ) {

ex.printStackTrace();

}

}

}

The below code is used for droping the data into database...

import java.sql.*;

import java.util.*;

public class jdbconnection {

public static void main(String args[]) {

Connection con1 = null;//New Portal Oracle dB connection.

Statement stmt1 = null;//New Portal Oracle dB Statement.

ResultSet rs1 = null;//New Portal Oracle dB ResultSet.

try {

Class.forName("oracle.jdbc.driver.OracleDriver");

con1 = DriverManager.getConnection("jdbc:oracle:thin:@myMachine:1521:test","scott","tiger");

stmt1 = con1.createStatement();

String query = "insert into STUDENT (NAME,AGE) values ('Bala','20')";

System.out.println(query);

stmt1.executeUpdate(query);

}

} catch (Exception ex) {

System.out.println("Error Occured in main code :"+ex);

} finally {

try {

stmt1.close();

con1.close();

} catch (Exception ex1) {

System.out.println("Error Occured in connection close code :"+ex1);

}

}

}

}

U need to change the driver and URL setting and u need the oracle driver jar....

peak-balua at 2007-7-7 15:16:05 >

hi,

can anyone tell me

how to get the connection and insert the string values into the oracle db.

import java.sql.*;

import java.util.*;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.IOException;

import java.util.ArrayList;

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;

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

public class ExcelReader

{

public ExcelReader()

{

readExcel();

}

private void readExcel()

{

try

{

HSSFWorkbook hssfworkbook = null;

ArrayList stringValues = new ArrayList();

ArrayList numericValues = new ArrayList();

POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("D:\\jakarta-tomcat-5.0.28\\test\\work.xls"));

hssfworkbook = new HSSFWorkbook(fs);

HSSFSheet sheet = hssfworkbook.getSheetAt(0);

for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++)

{

HSSFRow row = sheet.getRow(i);

System.out.println("ROWNO:" + row.getRowNum());

for (int j = 0; j < row.getPhysicalNumberOfCells(); j++)

{

HSSFCell cell = row.getCell((short) j);

System.out.println("CellNO:" + cell.getCellNum());

if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)

{

stringValues.add(cell.getStringCellValue());

if(cell.getCellNum() == 0)

{

String operating_unit = cell.getStringCellValue();

System.out.println("operating_unit>"+operating_unit);

}

else if(cell.getCellNum() == 1)

{

String request_no = cell.getStringCellValue();

System.out.println("request_no>"+request_no);

}

else if(cell.getCellNum() == 3)

{

String actionof = cell.getStringCellValue();

System.out.println("actionof>"+actionof);

}

else if(cell.getCellNum() == 4)

{

String datereq = cell.getStringCellValue();

System.out.println("datereq>"+datereq);

}

else if(cell.getCellNum() == 5)

{

String datesent = cell.getStringCellValue();

System.out.println("datesent>"+datesent);

}

else if(cell.getCellNum() == 7)

{

String targetdate = cell.getStringCellValue();

System.out.println("targetdate>"+targetdate);

}

else if(cell.getCellNum() == 8)

{

String expecte = cell.getStringCellValue();

System.out.println("expecte>"+expecte);

}

}

else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)

{

numericValues.add(String.valueOf(cell.getNumericCellValue()));

if(cell.getCellNum() == 2)

{

double items = cell.getNumericCellValue();

System.out.println("items>"+items);

}

else if(cell.getCellNum() == 6)

{

double priority = cell.getNumericCellValue();

System.out.println("priority>"+priority);

}

}

else

{

System.out.println("The cell was nothing we're interested in");

}

}

}

//System.out.println("The cell was nothing we're interested in");

}

catch (FileNotFoundException e)

{

// TODO Auto-generated catch block

e.printStackTrace();

}

catch (IOException e)

{

// TODO Auto-generated catch block

e.printStackTrace();

}

}

public static void main(String[] args)

{

ExcelReader excelReader = new ExcelReader();

}

}

sudhinaa at 2007-7-7 15:16:05 >