Let’s see how to export mysql database to excel using java in this little article. We are using java netbeans IDE with XAAMP server for MySQL database. Before we proceed we need a MySQL database with one table atleast and it contains few data which we are going to export into an excel file. Lets break this task into two part as showing below.
Task 01 : Get Data from Database to jTable
Create one jFrame and drag and drop jTable into it. Load MySQL table values into jTable When the jFrame is running.
Create a function as below to retrieve values from MySQL database.
public void getStudent(){
DefaultTableModel tm =(DefaultTableModel) jTableStudent.getModel();
try {
Class.forName("com.mysql.jdbc.Driver");
Connection cn=(Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/sample", "root", "");
Statement smt=cn.createStatement();
PreparedStatement ps = cn.prepareStatement("SELECT * FROM `student`");
ResultSet rs=ps.executeQuery();
tm.setRowCount(0);
while (rs.next()) {
tm.insertRow(tm.getRowCount(), new Object[]{rs.getString("id"),rs.getString("student_id"),rs.getString("student_name"),rs.getString("course")});
}
} catch (ClassNotFoundException ex) {
Logger.getLogger(Test.class.getName()).log(Level.SEVERE, null, ex);
} catch (SQLException ex) {
Logger.getLogger(Test.class.getName()).log(Level.SEVERE, null, ex);
}
}Task 02 : Export jTable into Excel File
Export the jTable with full loaded data into an Excel file while click “Export” button. We will read each row in the jTable using a for loop and add it to a file, then export the same to Excel file using jFileChooser. Find the “Export” button click code below.
Let’s first Create a function as below which read values from jTable and write to a file.
public void export(JTable table, File file){
TableModel m = jTableStudent.getModel();
try {
FileWriter fw = new FileWriter(file);
for (int i=0; i<m.getColumnCount();i++){
fw.write(m.getColumnName(i)+ "\t");
}
fw.write("\n");
for(int i=0; i<m.getRowCount(); i++){
for (int j=0; j<m.getColumnCount();j++){
fw.write(m.getValueAt(i,j).toString()+"\t");
}
fw.write("\n");
}
fw.close();
} catch (IOException ex) {
Logger.getLogger(Test.class.getName()).log(Level.SEVERE, null, ex);
}
}Let’s call the “Export” function while clicking “Export” button. Below code is to use jFileChooser to save the file as Excel and call function we created above.
private void jExportButtonActionPerformed(java.awt.event.ActionEvent evt) {
// TODO add your handling code here:
JFileChooser jChoose = new JFileChooser();
int option = jChoose.showSaveDialog(Test.this);
if(option==JFileChooser.APPROVE_OPTION){
String name = jChoose.getSelectedFile().getName();
String path = jChoose.getSelectedFile().getParentFile().getPath();
String file = path+ "\\"+ name+".xls";
export(jTableStudent, new File(file));
}
} Run the jFrame file and see the results. Hope this is useful