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.

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.addElement( md.getColumnName(i) );
}
while (rs.next()) {
Vector row = new Vector(columns);
for (int i = 1; i <= columns; i++) {
row.addElement( rs.getObject(i) );
}
data.addElement( row );
}
}
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((short) 0).setCellValue(" Id");
rowhead.createCell((short) 1).setCellValue(" Name");
rowhead.createCell((short) 2).setCellValue(" Address");
rowhead.createCell((short) 3).setCellValue(" Salary");
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));
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)
    {}
  }
}

Ads