I need help regarding this, I can't get it right. I want to export JTable data to excel file. There is no error but I am getting only the data it shows and does not include the table headers.
I am running this in a Netbeans 8.2 IDE and had also imported the essential jar files.
Data on my jtable is provided by my mysql database and I need to export it from jtable to an excel file. Anyway tblData is my JTable variable name.
private void btnExportActionPerformed(java.awt.event.ActionEvent evt) {
FileOutputStream excelFOS = null;
BufferedOutputStream excelBOS = null;
XSSFWorkbook wb = null;
JFileChooser excelFileChooser = new JFileChooser();
excelFileChooser.setDialogTitle("Save As");
FileNameExtensionFilter fnef = new FileNameExtensionFilter("Excel Files","xls","xlsx","ods");
excelFileChooser.setFileFilter(fnef);
int excelChooser = excelFileChooser.showSaveDialog(null);
if(excelChooser == JFileChooser.APPROVE_OPTION){
try {
wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Data Sheet");
for(int i = 0; i < tblData.getRowCount(); i++){
XSSFRow excelRow = sheet.createRow(i);
for(int j = 0; j < tblData.getColumnCount(); j++){
XSSFCell excelCell = excelRow.createCell(j);
excelCell.setCellValue(tblData.getValueAt(i, j).toString());
}
}
excelFOS = new FileOutputStream(excelFileChooser.getSelectedFile() + ".xlsx");
excelBOS = new BufferedOutputStream(excelFOS);
wb.write(excelBOS);
JOptionPane.showMessageDialog(null, "Successfully saved.");
} catch (FileNotFoundException ex) {
ex.printStackTrace();
} catch (IOException ex) {
ex.printStackTrace();
} finally {
try {
if(excelBOS != null){
excelBOS.close();
}
if(excelFOS != null){
excelFOS.close();
}
if(wb != null){
wb.close();
}
} catch (IOException ex) {
ex.printStackTrace();
}
} //---- end finally
} //---- end if condition
}
I am expecting for the header to be exported. Can anyone help me with this.
Not really clear what tblData
is in your provided code. But If I would have the requirement to export a JTable
to Excel
, then I would go by TableModel
of the JTable
. First write the column names to first row of the Excel
sheet using TableModel.getColumnName
. Then write the table data to next rows of the Excel
sheet using TableModel.getValueAt
.
Complete example:
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import javax.swing.JTable;
import javax.swing.table.TableModel;
class WriteJTableToExcel {
static void exportToExcel(JTable table, String filePath) throws Exception {
TableModel model = table.getModel();
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet();
Row row;
Cell cell;
// write the column headers
row = sheet.createRow(0);
for (int c = 0; c < model.getColumnCount(); c++) {
cell = row.createCell(c);
cell.setCellValue(model.getColumnName(c));
}
// write the data rows
for (int r = 0; r < model.getRowCount(); r++) {
row = sheet.createRow(r+1);
for (int c = 0; c < model.getColumnCount(); c++) {
cell = row.createCell(c);
Object value = model.getValueAt(r, c);
if (value instanceof String) {
cell.setCellValue((String)value);
} else if (value instanceof Double) {
cell.setCellValue((Double)value);
}
}
}
FileOutputStream out = new FileOutputStream(filePath);
workbook.write(out);
out.close();
workbook.close();
}
public static void main(String[] args) throws Exception {
Object columnNames[] = {"Name", "Amount", "Factor"};
Object rowData[][] = {
{"Bob", 12.0, 3.0},
{"Alice", 34.0, 2.5},
{"Jack", 56.0, 2.0},
{"John", 78.0, 1.5}
};
JTable table = new JTable(rowData, columnNames);
exportToExcel(table, "./Excel.xlsx");
}
}