Iseng iseng di tengah kesibukan, karena ada temen yg nanya jg gmana sih jadiin data yg ada di dalam database menjadi excel make java?. Karena saya pernah buat aplikasinya jadi coba saya share gimana buatnya, yg jelas disini kita harus make librari yg bukan bawaannya jdk, disini kita make librarinya apace poi. Untuk lebih jelasnya bisa baca referensi dari blognya kang eko, kang eko jg sebenarnya sudah buat tutorial kayaa gini, tapi dia buatnya diatas maven, jd yg blum terbiasa make maven bakal bingung jalaninnya gimana bukanya juga gimana.hehehe.. oke ini yg dah saya buat, buat klass klass pendukungnya,,
pertama buat databasenya dulu, saya buat dengan nama data. setelah itu buat table dan coba di isi.
CREATE TABLE IF NOT EXISTS `excel` ( `id` int(11) NOT NULL AUTO_INCREMENT, `biner` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `excel` -- INSERT INTO `excel` (`id`, `biner`) VALUES (1, 111101001), (2, 11100100);
klass pertama : Data.java
package databasetoexcel.model;
/**
*
* @author saiful bahri (jepara,1990)
* https://bahrie27.wordpress.com/
*/
public class Data {
private int biner;
public Data() {
}
public int getBiner() {
return biner;
}
public void setBiner(int biner) {
this.biner = biner;
}
}
kelas ke dua : DataImpl.java
package databasetoexcel.model;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
/**
*
* @author saiful bahri (jepara,1990)
* https://bahrie27.wordpress.com/
*/
public class DataImpl {
private Connection conn;
public DataImpl() {
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String db = "jdbc:mysql://localhost:3306/data";
String user = "root";
String pass = "root";
conn = DriverManager.getConnection(db, user, pass);
} catch (SQLException ex) {
Logger.getLogger(DataImpl.class.getName()).log(Level.SEVERE, null, ex);
} catch (ClassNotFoundException ex) {
Logger.getLogger(DataImpl.class.getName()).log(Level.SEVERE, null, ex);
} catch (InstantiationException ex) {
Logger.getLogger(DataImpl.class.getName()).log(Level.SEVERE, null, ex);
} catch (IllegalAccessException ex) {
Logger.getLogger(DataImpl.class.getName()).log(Level.SEVERE, null, ex);
}
}
public void insertDb(int biner){
try {
PreparedStatement ps = conn.prepareStatement("insert into excel values(null,?)");
ps.setInt(1, biner);
ps.executeUpdate();
} catch (SQLException ex) {
Logger.getLogger(DataImpl.class.getName()).log(Level.SEVERE, null, ex);
}
}
public List getAll(){
try {
Statement st = conn.createStatement();
ResultSet rs = st.executeQuery("select *from excel");
List list = new ArrayList();
while (rs.next()) {
Data d=new Data();
d.setBiner(rs.getInt("biner"));
list.add(d);
}
return list;
} catch (SQLException ex) {
Logger.getLogger(DataImpl.class.getName()).log(Level.SEVERE, null, ex);
return null;
}
}
public void deleteAll(){
try {
PreparedStatement ps = conn.prepareStatement("delete from excel");
ps.executeUpdate();
} catch (SQLException ex) {
Logger.getLogger(DataImpl.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
kelas ke tiga :JTableToExcelConverter.java (saya ambil dari codenya kang eko).
/*
* Copyright 2011 Eko Kurniawan Khannedy.
*
* Licensed under the Apache License, Version 2.0 (the "License");
* you may not use this file except in compliance with the License.
* You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
* under the License.
*/
package databasetoexcel.converter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JTable;
import javax.swing.table.TableModel;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
/**
*
* @author Eko Kurniawan Khannedy
*/
public class JTableToExcelConverter {
private JTable jtable;
public JTableToExcelConverter(JTable table) {
this.jtable = table;
}
public void convert(File file) {
// ambil table model
TableModel tableModel = jtable.getModel();
// ambil data header table
List header = new ArrayList();
for (int i = 0; i < tableModel.getColumnCount(); i++) {
header.add(tableModel.getColumnName(i));
}
// ambil data seluruh tabel
List<List> data = new ArrayList<List>();
// ambil data tiap baris
for (int i = 0; i < tableModel.getRowCount(); i++) {
List row = new ArrayList();
for (int j = 0; j < tableModel.getColumnCount(); j++) {
row.add(tableModel.getValueAt(i, j));
}
data.add(row);
}
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
HSSFRow rowHeader = sheet.createRow(0);
for (int i = 0; i < header.size(); i++) {
HSSFCell rowCell = rowHeader.createCell(i);
rowCell.setCellValue(header.get(i));
}
for (int i = 0; i < data.size(); i++) {
HSSFRow row = sheet.createRow(i + 1);
List dataRow = data.get(i);
for (int j = 0; j < dataRow.size(); j++) {
HSSFCell cell = row.createCell(j);
cell.setCellValue(dataRow.get(j).toString());
}
}
FileOutputStream stream = null;
try {
stream = new FileOutputStream(file);
workbook.write(stream);
} catch (IOException ex) {
Logger.getLogger(JTableToExcelConverter.class.getName()).log(Level.SEVERE, null, ex);
} finally {
if (stream != null) {
try {
stream.close();
} catch (IOException ex) {
Logger.getLogger(JTableToExcelConverter.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
}
setelah itu buat form nya. saya beri nama ViewDataToExcel.java screenshot dibawah.
untuk lbh jelas sourcodenya download aja di googlecode.
ini sceen shotnya ketika menambahkan jfilechooser, kalo caranya g kaya gitu nanti hasinya mawot di framenya hehehe
ini acara menambahkan librari poi.jar nya. librarinya ada di folder /lib yg di download.
Silahkan di download dan di plajari. Semoga bermanfaat.. bagi yg ingin tanya2 dapat chat di gtalk : bahrie172
Coretan Seorang Hamba 