Export data to Excel using JAVA and Apache POI API

 

Hi Friends! Here I will explain how to export data from database to an excel sheet in JAVA. There are several methods available using which we can export data to an excel sheet. I have used Apache’s POI API to export database table data to excel sheet. In future posts I’ll explain the different methods to export data to excel.

Resources required:
1) mysql-connector.jar – For establishing database connection.
2) jakarta-poi-2.5.jar – This is the jar file required for export excel. You can download it from the link given below.
http://www.java2s.com/Code/JarDownload/jakarta/jakarta-poi-2.5.jar.zip

After creating the javaPorject “ExportExcelProject” in eclipse we have to add the above two jars into build path.
This is a standalone application. The main method included here can be ran to see the result.

Database Script:

create table STUDENT_DETAILS (ID int(3) NOT NULL AUTO_INCREMENT, ROLL_NO char(20), NAME char(30), GENDER char(10), PRIMARY KEY(ID));

Inserting Sample Records

INSERT INTO STUDENT_DETAILS(ROLL_NO, NAME, GENDER) VALUES('ROLL1001','JOHN','MALE');
INSERT INTO STUDENT_DETAILS(ROLL_NO, NAME, GENDER) VALUES('ROLL1002','BRAD','MALE');
INSERT INTO STUDENT_DETAILS(ROLL_NO, NAME, GENDER) VALUES('ROLL1003','MARY','FEMALE');
INSERT INTO STUDENT_DETAILS(ROLL_NO, NAME, GENDER) VALUES('ROLL1001','PATRICIA', 'FEMALE');

I have separated the codes to get database connection, get table data and to exporting data to excel in three different methods. getConnection() is a static method where we are loading the driver class into memory and getting the connection by supplying connection url with username and password. getTableData() is the method where we are calling getConnection() method to get the database connection and after getting the database connection we are getting the table data. Here we are using “STUDENT_DETAILS” table which has 4 columns. After getting the table data we are preparing an ArrayList of Object array where we are storing all the data. doExport() is the method where we are preparing the excel sheet. In code we can see that the excel sheet had been written to “D:/Student_detais.xls” location. If the program executes successfully then the Student_details.xls file will be generated in root of D drive.

package com.exportexcel;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class Exporter {
	private static Connection getConnection(){
		Connection con = null;
		String url = "jdbc:mysql://localhost:3306/test";
		try{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection(url,"root","admin");
		}catch(ClassNotFoundException e){
			e.printStackTrace();
			System.out.println("Driver class not found. Please add MySQL connector jar in classpath");
		}catch(SQLException e){
			e.printStackTrace();
			System.out.println("Exception occured while getting Database connection");
		}
		return con;
	}
	
	public ArrayList<Object[]> getTableData(){
		ArrayList<Object[]> tableDataList = null;
		Connection con = getConnection();
		if(con != null){
			try{
				PreparedStatement ps = con.prepareStatement("SELECT ID,ROLL_NO,NAME,GENDER FROM STUDENT_DETAILS");
				ResultSet result = ps.executeQuery();
				tableDataList = new ArrayList<Object[]>();
				while(result.next()){
					Object[] objArray = new Object[4];
					objArray[0] = result.getInt(1);
					objArray[1] = result.getString(2);
					objArray[2] = result.getString(3);
					objArray[3] = result.getString(4);
					tableDataList.add(objArray);
				}
			}catch(SQLException e){
				e.printStackTrace();
				System.out.println("Unable to create PreparedStatement");
			}
		}
		return tableDataList;
	}
	
	public void doExport(ArrayList<Object[]> dataList){
		if(dataList != null && !dataList.isEmpty()){
			HSSFWorkbook workBook = new HSSFWorkbook();
			HSSFSheet sheet = workBook.createSheet();
			HSSFRow headingRow = sheet.createRow(0);
			headingRow.createCell((short)0).setCellValue("ID");
			headingRow.createCell((short)1).setCellValue("ROLL_NO");
			headingRow.createCell((short)2).setCellValue("NAME");
			headingRow.createCell((short)3).setCellValue("GENDER");
			short rowNo = 1;
			for (Object[] objects : dataList) {
				HSSFRow row = sheet.createRow(rowNo);
				row.createCell((short)0).setCellValue(objects[0].toString());
				row.createCell((short)1).setCellValue(objects[1].toString());
				row.createCell((short)2).setCellValue(objects[2].toString());
				row.createCell((short)3).setCellValue(objects[3].toString());
				rowNo++;
			}
			
			String file = "D:/Student_detais.xls";
			try{
				FileOutputStream fos = new FileOutputStream(file);
				workBook.write(fos);
				fos.flush();
			}catch(FileNotFoundException e){
				e.printStackTrace();
				System.out.println("Invalid directory or file not found");
			}catch(IOException e){
				e.printStackTrace();
				System.out.println("Error occurred while writting excel file to directory");
			}
		}
	}
	
	public static void main(String[] args) {
		Exporter exporter = new Exporter();
		ArrayList<Object[]> dataList = exporter.getTableData();
		if(dataList != null && dataList.size() > 0){
			exporter.doExport(dataList);
		}else{
			System.out.println("There is no data available in the table to export");
		}
	}
}

Leave a comment

SUBSCRIBE TO NEWSLETTER

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Categories