Create Excel File JDBC Data Java Example

In this tutorial, we will explain how to create an Microsoft Excel document (XLS format) by reading data from an Oracle database. We will be using Apache POI Java library to create XLS document and standard Oracle provided libraries to pull data from a table in DB. This example is constructed from the base example of creating an XLS file in Java using Apache POI. You can find references to the article at the bottom of this post. To run the examples provided in this post, you will need POI v 3.8 or above JAR files and ojdbc6.jar file

Create XLS over JDBC Data Source – Input Data

We create a small table in Oracle and define some data into it. This data will be used to load up the Excel document. You can use your own table for this by changing the Java code, which we will provide shortly. For now as this example is a simple one, we will provide scripts for it:

/* JDBC_EXCEL_POI_EXAMPLE.SQL */
create table oracle_to_excel
(
DEPT_ID NUMBER,
DEPT_NAME VARCHAR2(20)
)

insert into oracle_to_excel values(1,'Finance')
insert into oracle_to_excel values(2,'Marketing')
insert into oracle_to_excel values(3,'IT')

commit

Java Code – Create Excel Document in POI Over JDBC – Example

We have the data source ready and in this step, we will read the data from this data source and write this into an Excel file using POI. The step by step code example for this is provided below:

import java.io.FileOutputStream;
import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.*;
import java.util.*;
import java.sql.*; 
public class createxlsjdbc {  
        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 Workbook and Worksheet objects */
                HSSFWorkbook new_workbook = new HSSFWorkbook(); //create a blank workbook object
                HSSFSheet sheet = new_workbook.createSheet("Dept_Details");  //create a worksheet with caption score_details
                /* Define the SQL query */
                ResultSet query_set = stmt.executeQuery("select DEPT_ID,DEPT_NAME from oracle_to_excel");
                /* 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 dept_id = query_set.getString("DEPT_ID");
                                String dept_name=query_set.getString("DEPT_NAME");
                                excel_data.put(Integer.toString(row_counter), new Object[] {dept_id, dept_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("POI_XLS_JDBC.xls")); //create XLS file
                new_workbook.write(output_file);//write excel document to output stream
                output_file.close(); //close the file
                
        }
}

JDBC – Excel Output

This code produces the output excel sheet with the data populated from Oracle table. You can easily extend this to any other database, by using relevant libraries for the target.

That completes the tutorial to create spreadsheet data from table in database. If you have a question on this tutorial, you can post it in the comments section of this blog.

This Tutorial:  How to create Excel document from a JDBC datasource using Apache POI?

Keywords: Java, Apache POI, JDBC, Excel document, Create XLS in Java, Example

Next Tutorial: How to create XLSX spreadsheet over JDBC datasource using Apache POI?