Share on Google+Share on Google+

Retrieve Data from the database and write into excel file

In this Java tutorial section, you will learn how to retrieve data from the database and write into the excel file.

Retrieve Data from the database and write into excel file.

In this section, we are going to retrieve data from the database and write into the excel file. We have used Jakarta POI api for this purpose. The class HSSFWorkbook allow us to create the worksheet. The class HSSFSheet is used to create a row.The HSSFRow class create the cell in the excel sheet. With the use of setCellValue() method, we have inserted the database table values in the excel sheet.

Here is the code

import java.sql.*;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;

public class ExcelFile {
	public static void main(String[] args) {
		try {
			Connection connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/test", "root", "root");
			PreparedStatement psmnt = null;
			Statement st = connection.createStatement();
			ResultSet rs = st.executeQuery("Select * from student");

			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("Excel Sheet");
			HSSFRow rowhead = sheet.createRow((short) 0);
			rowhead.createCell((short) 0).setCellValue("Roll No");
			rowhead.createCell((short) 1).setCellValue("Name");
			rowhead.createCell((short) 2).setCellValue("Class");
			rowhead.createCell((short) 3).setCellValue("Marks");
			rowhead.createCell((short) 4).setCellValue("Grade");

			int index = 1;
			while ( {

				HSSFRow row = sheet.createRow((short) index);
				row.createCell((short) 0).setCellValue(rs.getInt(1));
				row.createCell((short) 1).setCellValue(rs.getString(2));
				row.createCell((short) 2).setCellValue(rs.getString(3));
				row.createCell((short) 3).setCellValue(rs.getInt(4));
				row.createCell((short) 4).setCellValue(rs.getString(5));
			FileOutputStream fileOut = new FileOutputStream("c:\\excelFile.xls");
			System.out.println("Data is saved in excel file.");
		} catch (Exception e) {



Posted on: October 29, 2009 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Share this Tutorial Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.


Advertisement null