Generate External Tables from an Excel Spreadsheet Using Jakarta POI

Lucas Jellema 3

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 thoughts on “Generate External Tables from an Excel Spreadsheet Using Jakarta POI

  1. Zeger,

    I really need your help. Can you please email me at I have several
    questions on POI. I am planning to implement this. Can you please give me your email address
    so that I can communicate with you. Any help on this is really appreciated.

  2. Zeger: you should stop by and do a little presentation for us during one of the KC sessions! How are you doing anyway? Groetjes,


  3. Recently I used POI to import event data from an Excel sheet in a calendar portlet: indeed, it boasts an easy API and it works like a charm!

Comments are closed.

Next Post

Declarative Implementation of Mandatory Master-Detail relationship (Order must have Order Lines)

Since at least 1996, I have been involved with specifying and implementing business rules. Whenever possible, business rules are implemented in the database using declarative constraints. Triggers are the next step when the Unique, Foreign Key and Check Constraints are not sufficient. However, without very precise locking and serialization, trigger-based […]
%d bloggers like this: