Generate External Tables from an Excel Spreadsheet Using Jakarta POI americas cup win 2682133k1

Generate External Tables from an Excel Spreadsheet Using Jakarta POI

An interesting article on OTN by Casimir Santeros, bringing the worlds of Java and Oracle together. The assignment: loading data from multi-sheet Excel documents into Oracle database tables. Technologies used: Apache POI to programmatically access the Excel sheet, JDBC, External Tables as the easiest means for loading textual data into the Oracle database. Generate External Tables from an Excel Spreadsheet Using Jakarta POI
A brief code excerpt, demonstrating some of the use of POI :

The POI-specific calls required for processing are as follows:

In the execute() method, the following two lines access the spreadsheet from the file system and create a new workbook object that allows you to manipulate the spreadsheet.
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(spreadsheet));
HSSFWorkbook wb = new HSSFWorkbook(fs);

have access to the HSSFWorkbook object, you can process it by iterating through all of the sheets, rows, and columns. The processWorkbook() method iterates through each of the sheets in the workbook, creates ExternalTable objects using the sheet name, processes each sheet, and extracts the relevant data for populating the ExternalTable objects.

private void processWorkbook(HSSFWorkbook wb) {
for (int i = 0; i < wb.getNumberOfSheets(); i++) { HSSFSheet sheet = wb.getSheetAt(i); ExternalTable table = new ExternalTable(wb.getSheetName(i)); processSheet(sheet, table); System.out.println("...Table "+ table.getName()+ " processed." ); } }

3 Comments

  1. Mrudula Gutta March 31, 2005
  2. Lucas Jellema March 8, 2005
  3. Zeger Hendrikse March 8, 2005