Export Jtable in excel file

Question

hi i am newbie in java programming but here's my problem i am designing GUI which does some calculation and need to show the inputs and outputs in J table then export them into excel file

the code working like charm but when i open the excel file i find it empty ..

j Table

import java.awt.Desktop;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import javax.swing.JFileChooser;


public class Export extends javax.swing.JFrame {


public Export() {
    initComponents();
}


@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code">                          
private void initComponents() {

    jScrollPane1 = new javax.swing.JScrollPane();
    jTable1 = new javax.swing.JTable();
    jButton1 = new javax.swing.JButton();

    setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

    jTable1.setModel(new javax.swing.table.DefaultTableModel(
        new Object [][] {
            {"Channel Model", global.channel_model, global.channel_model},                    
    {"System Bandwidth (MHz)", global.band_width_index,      global.band_width_index},
            {"Cell Edge Rate (Kbps)", global.Rreq_UL, global.Rreq_DL},
            {"Cell edge MCS", global.mcs, global.mcs},
    {"Antenna Configuration", global.DL_antenna_config, global.DL_antenna_config},
            {"Total RB Number", global.number_of_RB, global.number_of_RB},

            {"Tx", "", null},
            {"Max Power (dBm)", global.UE_Tx_power, global.UE_Tx_power},
            {"Cable Loss (dB)", global.cable_loss, global.cable_loss},
            {"Body Loss (dB)", global.body_loss, global.body_loss},
            {"Antenna Gain (dB)", global.UE_antennaGain, global.UE_antennaGain},
            {"EIRB (dB)", "", null},
            {"Rx", null, null},
            {"Antenna Gain (dB)", global.UE_antennaGain, global.UE_antennaGain},
            {"Cable Loss (dB)", global.cable_loss, global.cable_loss},
            {"Body Loss (dB)", global.body_loss, global.body_loss},
            {"Noise Figure (dB)", global.UE_noiseFigure, global.UE_noiseFigure},
            {"Thermal Noise (dB)", null, null},
            {"Interference Margin (dB)", global.Biul, global.Bidl},
            {"SINR (dB)", global.SINR, global.DL_SINR},
            {"Reciver Sensitivty (dB)", "", null},
            {"MAPL", null, null},
            {"Diversity (dB)", global.Tx_diversity_gain, global.Tx_diversity_gain},

            {"Shadow Fading Margin (dB)", global.shadow_margin, global.shadow_margin},
            {"MAPL (dB)", null, null},
            {"Cell Radius (Km)", global.R, global.R},
            {"Area of Dimensioning (Km squar", global.site_area, global.site_area},
            {"Site Type", global.type_of_site, global.type_of_site},
            {"Number of Sites  Due to Coverage", "", null}
        },
        new String [] {
            "Summary", "Uplink", "Downlink"
        }
    ));
    jTable1.setName("Export");
    jScrollPane1.setViewportView(jTable1);
    jTable1.getAccessibleContext().setAccessibleName("\"Export\"");
    jTable1.getAccessibleContext().setAccessibleDescription("");

    jButton1.setText("Export");
    jButton1.addActionListener(new java.awt.event.ActionListener() {
        public void actionPerformed(java.awt.event.ActionEvent evt) {
            jButton1ActionPerformed(evt);
        }
    });

    javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
    getContentPane().setLayout(layout);
    layout.setHorizontalGroup(
        layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
        .addGroup(layout.createSequentialGroup()
            .addContainerGap(562, Short.MAX_VALUE)
            .addComponent(jButton1)
            .addContainerGap())
        .addComponent(jScrollPane1)
    );
    layout.setVerticalGroup(
        layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
        .addGroup(layout.createSequentialGroup()
            .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 455, javax.swing.GroupLayout.PREFERRED_SIZE)
            .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
            .addComponent(jButton1)
            .addGap(0, 0, Short.MAX_VALUE))
    );

    pack();
    }// </editor-fold>                        

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {                                         
   try{
     JFileChooser fileChooser = new JFileChooser();
    int retval = fileChooser.showSaveDialog(jButton1);

    if (retval == JFileChooser.APPROVE_OPTION) {
        File file = fileChooser.getSelectedFile();
        if (file != null) {
            if (!file.getName().toLowerCase().endsWith(".xls")) {
                file = new File(file.getParentFile(), file.getName() + ".xls");



            }

            try {
                ExcelExporter exp=new ExcelExporter();
                exp.exportTable(jTable1, file);


                Desktop.getDesktop().open(file);
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();

            } catch (FileNotFoundException e) {
                e.printStackTrace();
                System.out.println("not found");
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        }


   }catch(Exception e){
       System.out.println("shit");
   }

}                                        

    public static void main(String args[]) {
            try {
        for (javax.swing.UIManager.LookAndFeelInfo info :     javax.swing.UIManager.getInstalledLookAndFeels()) {
            if ("Nimbus".equals(info.getName())) {
                javax.swing.UIManager.setLookAndFeel(info.getClassName());
                break;
            }
        }
    } catch (ClassNotFoundException ex) {
        java.util.logging.Logger.getLogger(Export.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
    } catch (InstantiationException ex) {
        java.util.logging.Logger.getLogger(Export.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
    } catch (IllegalAccessException ex) {
        java.util.logging.Logger.getLogger(Export.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
    } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(Export.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
    }
    //</editor-fold>

    /* Create and display the form */
    java.awt.EventQueue.invokeLater(new Runnable() {
        public void run() {
            new Export().setVisible(true);
         }
    });
 }

 // Variables declaration - do not modify                     
 private javax.swing.JButton jButton1;
 private javax.swing.JScrollPane jScrollPane1;
 private javax.swing.JTable jTable1;
 // End of variables declaration                   
 }

The excel generator class

import java.io.*;
import javax.swing.table.TableModel;
import javax.swing.*;


public class ExcelExporter {
    ExcelExporter(){}
    public void exportTable(JTable jTable1,File file) throws IOException{
      TableModel model=jTable1.getModel();
      FileWriter out=new FileWriter(file);
      BufferedWriter bw=new BufferedWriter(out);
      for (int i=0;i<model.getColumnCount();i++){
        bw.write(model.getColumnName(i)+"\t");
      }
      bw.write("\n");
      for (int i=0;i<model.getRowCount();i++){
        for (int j=0;j<model.getColumnCount();j++){
          bw.write(model.getValueAt(i,j).toString()+"\t");
        }
        bw.write("\n");
      }
      bw.close();
   System.out.print("Write out to"+file);


}
}

Answer 1

As has been recommended Apache POI is a very good api for manipulating and creating Excel documents. It is free and open source so easily usable. If you try it out then here is one way you could create your file:

private static void writeToExcell(JTable table, Path path) throws FileNotFoundException, IOException {
    new WorkbookFactory();
    Workbook wb = new XSSFWorkbook(); //Excell workbook
    Sheet sheet = wb.createSheet(); //WorkSheet
    Row row = sheet.createRow(2); //Row created at line 3
    TableModel model = table.getModel(); //Table model


    Row headerRow = sheet.createRow(0); //Create row at line 0
    for(int headings = 0; headings < model.getColumnCount(); headings++){ //For each column
        headerRow.createCell(headings).setCellValue(model.getColumnName(headings));//Write column name
    }

    for(int rows = 0; rows < model.getRowCount(); rows++){ //For each table row
        for(int cols = 0; cols < table.getColumnCount(); cols++){ //For each table column
            row.createCell(cols).setCellValue(model.getValueAt(rows, cols).toString()); //Write value
        }

        //Set the row to the next one in the sequence 
        row = sheet.createRow((rows + 3)); 
    }
    wb.write(new FileOutputStream(path.toString()));//Save the file     
}

The alternative would be to learn the Microsoft Excel file specifications and then manually create the Excel file that way. What you are doing at the moment is writing the data in your table to a file, but not in any way that the Excel application can understand it.

Answer 2

For write and manipulating Excel file use Apache POI http://poi.apache.org/

If You want to create Excel file by Yourself without using external library, keep in mind that excel file data is stored in form of XML Spreadsheet, so You will have to create file like that

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1" x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="String">Name</Data></Cell>
    <Cell><Data ss:Type="String">Example</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Value</Data></Cell>
    <Cell><Data ss:Type="Number">123</Data></Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>

source: http://en.wikipedia.org/wiki/Microsoft_Excel