Home Tutorial Java Poi Retrieve Data from the database and write into excel file

 
 

Share on Google+Share on Google+
Retrieve Data from the database and write into excel file
Posted on: October 29, 2009 at 12:00 AM
Advertisement
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.io.*;
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 {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			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 (rs.next()) {

				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));
				index++;
			}
			FileOutputStream fileOut = new FileOutputStream("c:\\excelFile.xls");
			wb.write(fileOut);
			fileOut.close();
			System.out.println("Data is saved in excel file.");
			rs.close();
			connection.close();
		} catch (Exception e) {
		}
	}
}
Advertisement

Related Tags for Retrieve Data from the database and write into excel file:


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.

Posted on: October 29, 2009

Recommend the tutorial

Advertisements Advertisements
 

 

 

DMCA.com