XLSX - Format Dates in Apache POI - Java Example

Formatting dates in XLSX workbook - Introduction

In this tutorial, we will explain how to format date cells in XLSX workbook types, by using built in formatters, available in Apache POI, with example Java programs. This is an extension of our XLS version, so we will be providing only the full Java program with comments where applicable. You should read the XLS version of this tutorial for a base understanding of how styles / formatters work in POI. 

Java Program - Format date - XLSX - Example

The complete Java program to format dates in XLSX workbooks with built in formatters is provided below:

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import java.util.Calendar;
import java.util.GregorianCalendar;
import org.apache.poi.ss.usermodel.BuiltinFormats;
public class FormatDateXLSX {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet */
                XSSFWorkbook my_workbook = new XSSFWorkbook();
                XSSFSheet my_sheet = my_workbook.createSheet("Format Date");            
                /* Create two styles that take different formats */
                XSSFCellStyle my_style_0 = my_workbook.createCellStyle();
                XSSFCellStyle my_style_1 = my_workbook.createCellStyle();
                /* We use BuiltinFormats directly in case of XLSX */
                /* Note that we are converting Integer to Short */
                my_style_0.setDataFormat((short)BuiltinFormats.getBuiltinFormat("m/d/yy"));
                my_style_1.setDataFormat((short)BuiltinFormats.getBuiltinFormat("d-mmm-yy"));
                /* Define data using java.util.Calendar */
                Calendar calendar = new GregorianCalendar();
                calendar.set(1990,Calendar.APRIL,2);
                /* Create Cell Data and Attach Date formats */          
                Row row = my_sheet.createRow(0);                
                Cell cell = row.createCell(0);
                cell.setCellValue(calendar);            
                cell.setCellStyle(my_style_0); //style 1                
                row = my_sheet.createRow(1);            
                cell = row.createCell(1);
                cell.setCellValue(calendar);            
                cell.setCellStyle(my_style_1); //style 2                                        
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\xlsx_date_formatting.xlsx"));
                my_workbook.write(out);
                out.close();
        }
}

Couple of things to note about the program. We are using BuiltinFormats class directly to get the built in format, and in the process we have to convert Integer to short in Java, to pass it to setDataFormat method. Other than this, this example is a direct replica of the XLS example.

The output of this program, formats the dates as per the masks provided, on the expected lines. A screenshot of the output is provided below:

XLSX - Format Date - Apache POI Example Program - Java Output 

That completes the tutorial to use Built in formatters to create different date formats in XLSX workbooks. We will now discuss how to use custom date formatters in Excel with examples.

Format Date Excel Cell - Java POI Example Program

Formatting Dates Using Apache POI - Introduction

In this tutorial, let us examine how to use built in formatters available in Apache POI, to format your date cells in Excel to the pattern you want, in Java, with an example program. Formatting a cell to a specific date value, requires a style (HSSFCellStyle) to be attached to it. In the style, you can use the method setDataFormat method and specify the formatter you want to use. Let us see a step by step guide in POI to attach built in date formatters to XLS documents in this example.

createCellStyle - Specify Date Format

The first step is to create your workbook and add a worksheet. We have done this many times now. After this, you should use workbook.createCellStyle() method and define styles in your workbook. Later, we will attach a date pattern to these styles. Java code snippet for this step is provided below:

                /* Create Workbook and Worksheet */
                HSSFWorkbook my_workbook = new HSSFWorkbook();
                HSSFSheet my_sheet = my_workbook.createSheet("Cell Date");              
                /* Create two styles that take different formats */
                HSSFCellStyle my_style_0 = my_workbook.createCellStyle();
                HSSFCellStyle my_style_1 = my_workbook.createCellStyle();

setDataFormat - Attach Date formats to Style

In this step, we use HSSFDataFormat class to retrieve some built in date formats that is available in POI. This is done using getBuiltinFormat method. The value retrieved is then passed to setDataFormat method, which attaches a date format to the styles created in step - 1. We use two styles in this example. Refer below for a code sample:

                /* Define date formats with the style */                        
                my_style_0.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
                my_style_1.setDataFormat(HSSFDataFormat.getBuiltinFormat("d-mmm-yy"));
                /* Define data using java.util.Calendar */

Attach Cells with Date Formats

We will now create some data in the worksheet and attach the styles to it. We use "Row" and "Cell" objects to define data. As this is a date based cell, we use java.util.Calendar and java.util.GregorianCalendar to define a date and attach it to the cell. This step is the key as it defines the cell value and attaches the format we created earlier to the cell. A code snippet to do this is provided below:

                /* Define data using java.util.Calendar */
                Calendar calendar = new GregorianCalendar();
                calendar.set(1982,Calendar.NOVEMBER,25);
                /* Create Cell Data and Attach Date formats */          
                Row row = my_sheet.createRow(0);                
                Cell cell = row.createCell(0);
                cell.setCellValue(calendar);            
                cell.setCellStyle(my_style_0); //style 1                
                row = my_sheet.createRow(1);            
                cell = row.createCell(1);
                cell.setCellValue(calendar);            
                cell.setCellStyle(my_style_1); //style 2                                        

Finally, we write the workbook to a file to see the formats we applied.

Date Format to Excel Cells - Apache POI - Complete Java Program Example

The full Java code that explains how to use built in formatters to format date cell data in a XLS workbook is provided below:

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
import java.util.Calendar;
import java.util.GregorianCalendar;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
public class FormatCellData {  
        public static void main(String[] args) throws Exception{
                /* Create Workbook and Worksheet */
                HSSFWorkbook my_workbook = new HSSFWorkbook();
                HSSFSheet my_sheet = my_workbook.createSheet("Cell Date");              
                /* Create two styles that take different formats */
                HSSFCellStyle my_style_0 = my_workbook.createCellStyle();
                HSSFCellStyle my_style_1 = my_workbook.createCellStyle();
                /* Define date formats with the style */                        
                my_style_0.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
                my_style_1.setDataFormat(HSSFDataFormat.getBuiltinFormat("d-mmm-yy"));
                /* Define data using java.util.Calendar */
                Calendar calendar = new GregorianCalendar();
                calendar.set(1982,Calendar.NOVEMBER,25);
                /* Create Cell Data and Attach Date formats */          
                Row row = my_sheet.createRow(0);                
                Cell cell = row.createCell(0);
                cell.setCellValue(calendar);            
                cell.setCellStyle(my_style_0); //style 1                
                row = my_sheet.createRow(1);            
                cell = row.createCell(1);
                cell.setCellValue(calendar);            
                cell.setCellStyle(my_style_1); //style 2                                        
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\cell_date_format.xls"));
                my_workbook.write(out);
                out.close();
        }
}

A screenshot of the cell data produced in the output is provided below: (output cell highlighted in yellow, with the format provided against it)

Excel POI - Format Cell Date Example - Java Program - Output

That completes a quick tutorial to use built in date formatters in POI to format your date data in Excel. In the next article, we will see how to write a custom formatter, instead of using built in formatters.

Bulk Load CSV File Data to Oracle Java Example Program

CSV Bulk Load to Oracle - Introduction

This tutorial discusses how to do bulk insert of CSV file data to Oracle in Java, by using OpenCSV  / JDBC approach with an example program. In the last post, we discussed how to do a row-by-row insert of CSV file data to Oracle using Open CSV / JDBC in Java. We mentioned that this row by row processing can be slow when the amount of data in the CSV file is huge. In such cases, following a bulk insert approach could be very helpful. Let us setup an input table / CSV file data and discuss how to perform bulk insertion in Java with suitable examples.

Input table / CSV File Data

We cannot produce a huge file here. We will have a short file but still cover the batch insert approach. Here is our input Oracle table / CSV file data.

CREATE TABLE CSV_2_ORACLE
(
USER_ID VARCHAR2(20),
USER_AGE NUMBER
)

CSV File data is provided below

A,40
B,34
C,50
D,30
E,44
F,35
G,60

Bulk Insert - CSV to Oracle - Java Example Program

We will follow standard batching approach available in JDBC to push the data in the CSV file as a bulk approach to the Oracle table. The complete Java program to accomplish this is provided below:

import java.io.*;
import au.com.bytecode.opencsv.CSVReader;
import java.util.*;
import java.sql.*; 
public class bulkInsertCSVtoOracle {  
        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");
                PreparedStatement sql_statement = null;
                String jdbc_insert_sql = "INSERT INTO CSV_2_ORACLE"
                                + "(USER_ID, USER_AGE) VALUES"
                                + "(?,?)";
                sql_statement = conn.prepareStatement(jdbc_insert_sql);
                /* Read CSV file in OpenCSV */
                String inputCSVFile = "inputdata.csv";
                CSVReader reader = new CSVReader(new FileReader(inputCSVFile));         
                String [] nextLine; 
                int lnNum = 0; 
                //loop file , add records to batch
                while ((nextLine = reader.readNext()) != null) {
                        lnNum++;
                        /* Bind CSV file input to table columns */
                        sql_statement.setString(1, nextLine[0]);
                        /* Bind Age as double */
                        /* Need to convert string to double here */
                        sql_statement.setDouble(2,Double.parseDouble(nextLine[1]));
                        // Add the record to batch
                        sql_statement.addBatch();
                }                       
                //We are now ready to perform a bulk batch insert              
                int[] totalRecords = new int[7];
                try {
                        totalRecords = sql_statement.executeBatch();
                } catch(BatchUpdateException e) {
                        //you should handle exception for failed records here
                        totalRecords = e.getUpdateCounts();
                }
                System.out.println ("Total records inserted in bulk from CSV file " + totalRecords.length);                
                /* Close prepared statement */
                sql_statement.close();
                /* COMMIT transaction */
                conn.commit();
                /* Close connection */
                conn.close();
        }
}

We have used PreparedStatement again, but instead of executing the statement, we used addBatch method to batch the execution in bulk. Once we have done this for all the records, we call executeBatch() method to bulk load the records to Oracle table. Easy. There is also a section of code that you can use for exception handling. This example is consistent across all database platforms and you should be able to use it for any database provided you have the right driver JAR files viz Oracle, MySQL etc. The output of this program is shown below. As expected, it has loaded all the records in our CSV file.

Bulk Load CSV Data to Oracle - Java Open CSV JDBC Batch - Example Program - Output

That completes our tutorial on bulk loading of CSV data into Oracle in Java. If you have any questions, you can post it in the comments section. Stay connected to our blog. 

Insert CSV Data to Oracle - Java JDBC Example Program

CSV to Oracle - Introduction

In this tutorial, we will discuss how to insert CSV file data to a table in Oracle, by using Java and OpenCSV library. If you look for a non Java based solution, there are powerful options for this, but when you use Java you can do transformations to the input before inserting it to a table if required. Our approach would be to parse the input CSV file using openCSV in Java, and then use JDBC to insert one row at a time / bulk insert into Oracle. We will cover both these approaches in this tutorial, as we move on.

Input CSV Data and Oracle Table

To work with the example in this tutorial, we need a sample CSV file and Oracle table. Use the scripts below to create a table in Oracle and data to create a CSV file. You can use your own CSV file, but make sure you alter the table suitably.

CREATE TABLE CSV_2_ORACLE
(
USER_ID VARCHAR2(20),
USER_AGE NUMBER
)

A sample CSV file that you can use for this tutorial is provided below:

A,40
B,34
C,50
D,30

Save this file as inputdata.csv. Download openCSV and have the JAR file opencsv-2.3.jar in your classpath. We are ready to go.

CSV to Oracle - Java Program Example - Open CSV - JDBC

The full Java program that does a row by row processing / insert of CSV file data to Oracle is provided below. You should be using this if the data in your CSV file is very less. For large CSV files, you should consider doing a bulk processing of CSV data.

import java.io.*;
import au.com.bytecode.opencsv.CSVReader;
import java.util.*;
import java.sql.*; 
public class csvToOracle {  
        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");
                PreparedStatement sql_statement = null;
                /* Create the insert statement */
                String jdbc_insert_sql = "INSERT INTO CSV_2_ORACLE"
                                + "(USER_ID, USER_AGE) VALUES"
                                + "(?,?)";
                sql_statement = conn.prepareStatement(jdbc_insert_sql);
                /* Read CSV file in OpenCSV */
                String inputCSVFile = "inputdata.csv";
                CSVReader reader = new CSVReader(new FileReader(inputCSVFile));
                /* Variables to loop through the CSV File */
                String [] nextLine; /* for every line in the file */            
                int lnNum = 0; /* line number */
                while ((nextLine = reader.readNext()) != null) {
                        lnNum++;
                        /* Bind CSV file input to table columns */
                        sql_statement.setString(1, nextLine[0]);
                        /* Bind Age as double */
                        /* Need to convert string to double here */
                        sql_statement.setDouble(2,Double.parseDouble(nextLine[1]));
                        /* execute the insert statement */
                        sql_statement.executeUpdate();
                }               
                /* Close prepared statement */
                sql_statement.close();
                /* COMMIT transaction */
                conn.commit();
                /* Close connection */
                conn.close();
        }
}

This program successfully inserted the CSV data into Oracle table, row by row. After insert, the output as seen from the oracle table is provided below:

Insert CSV Data to Oracle Table - Java Example Program - Open CSV - JDBC - Output

In the next tutorial, we will discuss how to perform bulk insert of CSV file data to Oracle using one of the popular JDBC bulk insert approaches.

Excel to Oracle - Batching - POI JDBC Example Program

Oracle Batching - Introduction

We are exploring different methods to insert data from Excel to Oracle in Java using Apache POI / JDBC . In part 1 of this post, we saw a basic JDBC Example of inserting data in Java. In Part 2, we discussed standard batching approach where we bulk inserted data from Excel to Oracle. In this post, we will discuss Oracle batching as the target database is Oracle. We use OraclePreparedStatement for this. Let us get started in this example. You should refer to Part 1 to get the input data / worksheet to work with the examples.

Excel to Oracle - Oracle Batching - Java POI JDBC Example Program

In Oracle batching, we use OraclePreparedStatement to set the batch size at the beginning where we create the prepared statement. Once this is done, you can read the data from the Excel file using POI normally. When you call statement.execute, the batch size is checked and executed only if the batch size is reached. Even if you have some records in your excel document, that does not meet a batch length, they will be processed when you commit the transaction. There is no change to the way you read data from the Excel workbook. The code example is provided below:

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.*; 
import oracle.jdbc.*;
public class oracleBatching {  
        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");
                PreparedStatement sql_statement = null;
                String jdbc_insert_sql = "INSERT INTO XLS_POI"
                                + "(KEYWORD, TOTAL_COUNT) VALUES"
                                + "(?,?)";
                sql_statement = conn.prepareStatement(jdbc_insert_sql);
                // we set batch size as 5. You should increase this 
                // depending on the number of rows in your Excel document
                ((OraclePreparedStatement)sql_statement).setExecuteBatch(5);
                /* We should now load excel objects and loop through the worksheet data */
                FileInputStream input_document = new FileInputStream(new File("xls_to_oracle.xls"));
                /* Load workbook */
                HSSFWorkbook my_xls_workbook = new HSSFWorkbook(input_document);
                /* Load worksheet */
                HSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0);
                // we loop through and insert data
                Iterator<Row> rowIterator = my_worksheet.iterator(); 
                while(rowIterator.hasNext()) {
                        Row row = rowIterator.next(); 
                        Iterator<Cell> cellIterator = row.cellIterator();
                                while(cellIterator.hasNext()) {
                                        Cell cell = cellIterator.next();
                                        switch(cell.getCellType()) { 
                                        case Cell.CELL_TYPE_STRING: //handle string columns
                                                sql_statement.setString(1, cell.getStringCellValue());                                                                                     
                                                break;
                                        case Cell.CELL_TYPE_NUMERIC: //handle double data
                                                sql_statement.setDouble(2,cell.getNumericCellValue() );
                                                break;
                                        }
                                       
                                }
                                
                                // though we call execute here, it is done only 
                                //when the batch size is reached.
                               try {
                                       sql_statement.execute();
                                } catch(BatchUpdateException e) {
                                //you should handle exception here if required
                                }
                }               
                input_document.close();
                /* Close prepared statement */
                sql_statement.close();
                /* COMMIT transaction */
                // unproccesed batch would get processed anyway.
                conn.commit();
                /* Close connection */
                conn.close();
        }
}

This program does the same as the previous two, but is specific to Oracle database. When executed, it inserted all the five records in the Excel document to Oracle DB table successfully. 

That completes the tutorial series, where we explored different approaches to insert data from Excel to Oracle in Java using POI / JDBC. You can write an XLSX example on the same lines. We would have written an example for this for sure, make sure you check it out in the related post section below. If you have any questions, you can post it in the comments section.

This Series:Insert data from Excel to Oracle Using Java, Apache POI and JDBC
Keywords: Excel, Oracle, Excel to Oracle Using Java, XLS, XLSX, POI,  JDBC
All Parts:
Part - 1: Apache POI / JDBC - Basic Example to Insert Data from Excel Document
Part - 2: JDBC Bulk Insert Approach to Load data into Oracle from Excel
Part - 3: Oracle Batching - Excel data to Oracle - Java POI Example

Batch Insert Excel to Oracle - POI JDBC Example Program

Batch Insert Excel to Oracle - Introduction

In the last part of this series, we explained how to insert data from Excel to Oracle in Java using JDBC / Apache POI, one row at a time. For performance reasons, you may have to perform the insert from Excel to Oracle as a batch insert job. This is especially useful when the number of records in your Excel document is ranging in thousands. There are two approaches you can follow for batch inserts, when it comes to batch insert. These are done through arrays in Java. These are standard batching and oracle batching. In this post, we will see how to insert your excel data to Oracle through standard batching, with an example program.

Standard batching - Excel to Oracle - Java POI JDBC Example Program

Before you go ahead, you should get a copy of the input table / input spreadsheet data from our basic JDBC example insert. (part - 1). In standard batching approach, we use PreparedStatement just as in Part 1. We loop through the Excel data normally. However, we don't insert data for every row we read from Excel. We rather, execute the insert job as a batch. This is shown below:

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 xlsToOracleStandardBatch {  
        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");
                PreparedStatement sql_statement = null;
                String jdbc_insert_sql = "INSERT INTO XLS_POI"
                                + "(KEYWORD, TOTAL_COUNT) VALUES"
                                + "(?,?)";
                sql_statement = conn.prepareStatement(jdbc_insert_sql);
                /* We should now load excel objects and loop through the worksheet data */
                FileInputStream input_document = new FileInputStream(new File("xls_to_oracle.xls"));
                /* Load workbook */
                HSSFWorkbook my_xls_workbook = new HSSFWorkbook(input_document);
                /* Load worksheet */
                HSSFSheet my_worksheet = my_xls_workbook.getSheetAt(0);
                // we loop through and insert data
                Iterator<Row> rowIterator = my_worksheet.iterator(); 
                while(rowIterator.hasNext()) {
                        Row row = rowIterator.next(); 
                        Iterator<Cell> cellIterator = row.cellIterator();
                                while(cellIterator.hasNext()) {
                                        Cell cell = cellIterator.next();
                                        switch(cell.getCellType()) { 
                                        case Cell.CELL_TYPE_STRING: //handle string columns
                                                sql_statement.setString(1, cell.getStringCellValue());                                                                                     
                                                break;
                                        case Cell.CELL_TYPE_NUMERIC: //handle double data
                                                sql_statement.setDouble(2,cell.getNumericCellValue() );
                                                break;
                                        }
                                       
                                }
                // Add the row to a batch processing - standard batch processing example
                sql_statement.addBatch();
                }
                //We are now ready to perform a bulk batch insert
                //our Excel has only 5 records, you should set this suitably
                int[] totalRecords = new int[5];
                try {
                        totalRecords = sql_statement.executeBatch();
                } catch(BatchUpdateException e) {
                        //you should handle exception for failed records here
                        totalRecords = e.getUpdateCounts();
                }
                System.out.println ("Total records inserted : " + totalRecords.length);
                /* Close input stream */
                input_document.close();
                /* Close prepared statement */
                sql_statement.close();
                /* COMMIT transaction */
                conn.commit();
                /* Close connection */
                conn.close();
        }
}

This program successfully inserted the five records into our Oracle table. You may get exceptions depending on your dataset. In that case, you have to handle your exceptions suitably. You should go for a batch mode of insert depending on the number of records that you have in your input Excel workbook. You can read more about batching and understand at the following link.

This Series:Insert data from Excel to Oracle Using Java, Apache POI and JDBC
Keywords: Excel, Oracle, Excel to Oracle Using Java, XLS, XLSX, POI,  JDBC
All Parts:
Part - 1: Apache POI / JDBC - Basic Example to Insert Data from Excel Document
Part - 2: JDBC Bulk Insert Approach to Load data into Oracle from Excel
Part - 3: Oracle Batching - Excel data to Oracle - Java POI Example