Generate External Tables from an Excel Spreadsheet Using Jakarta POI

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." );
}
}

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

3 Comments

  1. Mrudula Gutta on

    Zeger,

    I really need your help. Can you please email me at mrudula_gutta@yahoo.com. 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. Lucas Jellema on

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

    Lucas

  3. Zeger Hendrikse on

    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!