Java 在excel文件中导出Jtable

Export Jtable in excel file

提问by user3092878

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

嗨，我是 Java 编程的新手，但这是我的问题，我正在设计 GUI，它进行一些计算，需要在 J 表中显示输入和输出，然后将它们导出到 excel 文件中

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

代码像魅力一样工作，但是当我打开 excel 文件时，我发现它是空的 ..

j Table

j 表

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

excel生成器类

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);


}
}

采纳答案by pezetem

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

对于编写和操作 Excel 文件，请使用 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

如果您想自己创建 Excel 文件而不使用外部库，请记住 Excel 文件数据以 XML 电子表格的形式存储，因此您必须创建这样的文件

<?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

来源：http: //en.wikipedia.org/wiki/Microsoft_Excel

回答by Levenal

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:

正如所推荐的，Apache POI 是一个非常好的用于操作和创建 Excel 文档的 api。它是免费和开源的，因此易于使用。如果您尝试一下，那么这里是您创建文件的一种方法：

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.

另一种方法是学习 Microsoft Excel 文件规范，然后以这种方式手动创建 Excel 文件。您目前正在做的是将表中的数据写入文件，但不是以 Excel 应用程序可以理解的任何方式。

回答by Nidhish

You can solve this without using Apache POI. What worked for me was to remove the String conversion while writing in the excel exporter class.

您可以在不使用 Apache POI 的情况下解决此问题。对我有用的是在 Excel 导出器类中写入时删除字符串转换。

bw.write(model.getValueAt(i,j)+"\t");

See if this works. This is because the getValueAt() function returns an object which has a problem in conversion to String. Let me know.

看看这是否有效。这是因为 getValueAt() 函数返回的对象在转换为 String 时有问题。让我知道。