Home Tutorial Java Swing Retrieve Data into JTable and export it to Excel File

 
 

Share on Google+Share on Google+
Retrieve Data into JTable and export it to Excel File
Posted on: October 23, 2009 at 12:00 AM
Advertisement
In this section, you will learn how to retrieve the data from the database in the table and then export it to the Excel file.

Java Retrieve Data into JTable and export it to Excel File

In this section, you will learn how to retrieve the data from the database in the table and then export it to the Excel file. For this, we have created a table in the database named 'employee'. We have retrieved the data and stored it into Vector which is then added to table. To export the table data to excel file, we have used POI library. Now to open the created file, we have used the following code:

Runtime rt = Runtime.getRuntime();
rt.exec("cmd.exe /C start C:\\Hello.xls");

Here is the code of JTableToExcel.java

import javax.swing.*;
import java.awt.*;
import java.io.*;
import java.awt.event.*;
import java.sql.*;
import java.util.*;
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;

class Form extends JFrame{
    ResultSet rs;
    Form(){
    final Vector columnNames = new Vector();
        final Vector data = new Vector();
        JButton button=new JButton("Export");
    JPanel panel=new JPanel();
    JPanel panel1=new JPanel();
    JPanel panel2=new JPanel();
      try{
    Connection con = null;
    Class.forName("com.mysql.jdbc.Driver");
      con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test""root""root");
        Statement st = con.createStatement();
        rs= st.executeQuery("Select * from employee");
    ResultSetMetaData md = rs.getMetaData();
int columns = md.getColumnCount();
for (int i = 1; i <= columns; i++) {
columnNames.addElementmd.getColumnName(i) );
}
while (rs.next()) {
Vector row = new Vector(columns);
for (int i = 1; i <= columns; i++) {
row.addElementrs.getObject(i) );
}
data.addElementrow );
}
}
catch(Exception e){}
JTable table = new JTable(data, columnNames);
JScrollPane scrollPane = new JScrollPane(table);
panel1.add(scrollPane);
panel2.add(button);
panel.add(panel1);
panel.add(panel2);
add(panel);
button.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent ev){
try{
Connection con = null;
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test""root""root");
Statement st = con.createStatement();
rs= st.executeQuery("Select * from employee");
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("Excel Sheet");
HSSFRow rowhead = sheet.createRow((short)0);
rowhead.createCell((short0).setCellValue(" Id");
rowhead.createCell((short1).setCellValue(" Name");
rowhead.createCell((short2).setCellValue(" Address");
rowhead.createCell((short3).setCellValue(" Salary");
int index=1;
while(rs.next()){
HSSFRow row = sheet.createRow((short)index);
row.createCell((short0).setCellValue(rs.getInt(1));
row.createCell((short1).setCellValue(rs.getString(2));
row.createCell((short2).setCellValue(rs.getString(3));
row.createCell((short3).setCellValue(rs.getInt(4));
index++;
}
FileOutputStream fileOut = new FileOutputStream("c:\\Hello.xls");
wb.write(fileOut);
fileOut.close();
Runtime rt = Runtime.getRuntime();
rt.exec("cmd.exe /C start C:\\Hello.xls");
}
catch(Exception e){}
}
});
}
}
 class JTableToExcel{
  public static void main(String arg[])
  {
    try
    {
    Form frame=new Form();
    frame.setSize(450,200);
    frame.setVisible(true);
    }
  catch(Exception e)
    {}
  }
}
Advertisement

Related Tags for Retrieve Data into JTable and export it to 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 23, 2009

Recommend the tutorial

Advertisements Advertisements
 

 

 

DMCA.com