Create XLSX File JDBC Java Example

In the last tutorial, we focussed on creating an Excel document in Office 97 – 2003 format, by reading data over a JDBC connection using Apache POI Java library. The example code showed us how easy it is to combine POI + JDBC components and construct a simple code that achieved what we wanted to do. This post can be considered as an extension of the last post, however, we will focus on creating Excel spreadsheet in New Microsoft Office format (XLSX, 2007 and 2012 formats at the time of writing ) using POI. You have to make some simple adjustments to the code we provided in the last example. We will explain that and provide a fully working code for this scenario.

Write XLSX 2007 File over JDBC – Input Data

For this code to work, we create a SQL table and populate some rows into it. We will use Oracle database as our data source. You can use this table or alter it to suit to your needs. The script is provided below:

/* JAVA_EXCEL_2007_JDBC_POI_EXAMPLE.SQL */
create table oracle_to_xlsx
(
EMP_ID NUMBER,
EMP_NAME VARCHAR2(20)
)

insert into oracle_to_xlsx values(1,'Yardy')
insert into oracle_to_xlsx values(2,'Scott')
insert into oracle_to_xlsx values(3,'Beroman')

commit

Required JAR Files – JDBC Data

You will require a number of JAR files for this example to work. They are provided below:

poi-ooxml-3.8.jar, poi-ooxml-schemas-3.8.jar, poi-3.8.jar, ojdbc6.jar, dom4j-1.6.1.jar and xmlbeans-2.3.0.jar.
If you are missing any of these JAR files, you will get exceptions at compile time or at runtime.

Java Program – Create Excel 2007 format File over JDBC – Code

The full code to query an Oracle data source and create a XLSX file from the output data is provided below:

import java.io.FileOutputStream;
import java.io.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; //Write in Office 2007, 2012 format
import org.apache.poi.xssf.usermodel.XSSFSheet; //Write in Office 2007, 2012 format
import org.apache.poi.ss.usermodel.*;
import java.util.*;
import java.sql.*; 
public class jdbc_xlsx_file {  
        public static void main(String[] args) throws Exception{
                
                /* Create Connection objects */
                Class.forName ("oracle.jdbc.OracleDriver"); 
                Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@//localhost:1521/xe", "hr", "hr");
                Statement stmt = conn.createStatement();
                /* Create 2007 format Workbook and Worksheet objects */
                XSSFWorkbook new_workbook = new XSSFWorkbook(); //create a blank workbook object
                XSSFSheet  sheet = new_workbook.createSheet("EMP_DETAILS");  //create a worksheet with caption score_details
                /* Define the SQL query */
                ResultSet query_set = stmt.executeQuery("select EMP_ID,EMP_NAME from oracle_to_xlsx");
                /* Create Map for Excel Data */
                Map<String, Object[]> excel_data = new HashMap<String, Object[]>(); //create a map and define data
                int row_counter=0;
                /* Populate data into the Map */
                while (query_set.next()) {
                                row_counter=row_counter+1;
                                String emp_id = query_set.getString("EMP_ID");
                                String emp_name=query_set.getString("EMP_NAME");
                                excel_data.put(Integer.toString(row_counter), new Object[] {emp_id, emp_name});
                                }
                /* Close all DB related objects */
                query_set.close();
                stmt.close(); 
                conn.close();
                
                /* Load data into logical worksheet */           
                Set<String> keyset = excel_data.keySet();
                int rownum = 0;
                for (String key : keyset) { //loop through the data and add them to the cell
                        Row row = sheet.createRow(rownum++);
                        Object [] objArr = excel_data.get(key);
                        int cellnum = 0;
                        for (Object obj : objArr) {
                                Cell cell = row.createCell(cellnum++);
                                if(obj instanceof Double)
                                        cell.setCellValue((Double)obj);
                                else
                                        cell.setCellValue((String)obj);
                                }
                }
 
                FileOutputStream output_file = new FileOutputStream(new File("JDBC_POI_XLSX_File.xlsx")); //create XLS file
                new_workbook.write(output_file);//write excel document to output stream
                output_file.close(); //close the file
                
        }
}

XLSX JDBC Output

The code above creates a XLSX file at runtime with the contents populated from Oracle data source.

With this, we come to the end of this tutorial. This should give you some insight to get started with creating XLSX format files over JDBC connection using Apache POI, Java. If you are stuck, you can send your question and code to us through the comments section of this blog.

This Tutorial: How to create 2007, 2012 format Excel Files in Apache POI, over JDBC datasource from Oracle?
Keywords: Excel 2007 format, 2012 format, Apache POI, Java Example, JDBC Program
Next Tutorial: Using OpenCSV and POI, how to convert CSV file to XLS format?