Generate External Tables from an Excel Spreadsheet Using Jakarta POI

Lucas Jellema 3
0 0
Read Time:1 Minute, 3 Second

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

About Post Author

Lucas Jellema

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director and Oracle Developer Champion. Solution architect and developer on diverse areas including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and microservices, events in various shapes and forms and many other things. Author of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on user groups and community events and conferences such as JavaOne, Oracle Code, CodeOne, NLJUG JFall and Oracle OpenWorld.
Happy
Happy
0 %
Sad
Sad
0 %
Excited
Excited
0 %
Sleepy
Sleepy
0 %
Angry
Angry
0 %
Surprise
Surprise
0 %

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

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 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. 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. 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: