apache poi:将 jtable 保存到文件 [英] apache poi: saving jtable to a file
问题描述
我最近开始使用 java，当我需要从 jTable 创建一个 excel 文件时，我正面临 apache poi 库的一些问题.
I've recently started working with java and I'm facing some issues with the apache poi library when I need to create an excel file starting from a jTable.
我已经阅读了很多线程并提出了一些无法正常工作的代码(即使这是非常简单的东西并且有很多示例，这让我看起来更加愚蠢)，我希望有人可以提供帮助我出去.
I've read many threads and come up with some code which just won't work (even if this is pretty easy stuff and there were many examples, which makes me look even more dumb) and I was hoping someone could help me out.
所以这里是问题:
a) 为什么应该写入 excel 文件的 for 循环没有填充每个单元格?(excel 文件中唯一包含数据的行是第六行，这也让我想知道为什么它会为 getRowCount/Column 方法计算表模型中的空项目.我也知道它正在打印自定义字符串而不是表本身，但把它留到 b) 点
a) why does the for loop, which is supposed to write the excel file, not fill every cell? (the only row with data in the excel file is the sixth one, which also makes me wonder why does it count null items in the table model for the getRowCount/Column methods..also I know it's printing a custom String and not the table itself but save that for point b)
b) 我应该如何使用 jtable 模型项来填充 excel 文件，因为在创建表时我必须选择对象作为行类型?(我也有特别是对象类型的问题，只要它是一个字符串||整数就没有问题，但是表应该是两者的混合，当您尝试时似乎不起作用使用 setCellValue() 方法与 String|| 整数不同的东西......或者至少我无法让它工作)
b) how am I supposed to use the jtable model items to populate the excel file since when creating the table I had to choose Object as row type? (I'm also having issues with the kind of object especially, as long as it's a String||integer there's no issue at all but the table is supposed to have a mix of both which doesn't seem to work when you try to use the setCellValue() method with something different from the String||integer..or at least I couldn't make it work)
c) 假设我稍后想从我之前创建的文件中填充 jtable，我是否只需要使用解决方案来指向 b)(相反的方法是)使用 bufferedReader 类读取文件后?
c) let's say I'd later want to populate the jtable from the file I previously created, would I simply have to use the solution to point b) (the other way around that is) after reading the file with the bufferedReader class?
免责声明: 代码的第一部分是由 netbeans 自动生成的，你可能已经知道了，我想出的 hssf 部分在最后，但我想你可能想看看整件事，如果看起来有点乱，抱歉.
Disclaimer: the first part of the code is auto-generated by netbeans as you can probably tell, the hssf part I came up with is at the end but I thought you might want to see the whole thing, sorry if it looks kind of messy.
代码如下:
package poitest;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
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.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
public class POITestFrame extends javax.swing.JFrame {
/**
* Creates new form POITestFrame
*/
public POITestFrame() {
initComponents();
}
@SuppressWarnings("unchecked")
// <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
private void initComponents() {
excelButton = new javax.swing.JButton();
jScrollPane1 = new javax.swing.JScrollPane();
jTable1 = new javax.swing.JTable();
setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
excelButton.setText("ESPORTA!");
excelButton.addActionListener(new java.awt.event.ActionListener() {
public void actionPerformed(java.awt.event.ActionEvent evt) {
excelButtonActionPerformed(evt);
}
});
jTable1.setModel(new javax.swing.table.DefaultTableModel(
new Object [][] {
{"Boolean", "Integer", "String", "Array"},
{"x*y", "x+y", "x/y", "x-y"},
{"32", "43", "12", "24"},
{"casa", "cantiere", "museo", "acquario"},
{null, null, null, null},
{null, null, null, null}
},
new String [] {
"Title 1", "Title 2", "Title 3", "Title 4"
}
));
jScrollPane1.setViewportView(jTable1);
javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
getContentPane().setLayout(layout);
layout.setHorizontalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 320, Short.MAX_VALUE)
.addComponent(excelButton, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
);
layout.setVerticalGroup(
layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
.addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
.addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 91, javax.swing.GroupLayout.PREFERRED_SIZE)
.addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
.addComponent(excelButton)
.addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
);
pack();
}// </editor-fold>//GEN-END:initComponents
private void excelButtonActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_excelButtonActionPerformed
try {
PoiWriter(jTable1);
} catch (FileNotFoundException ex) {
Logger.getLogger(POITestFrame.class.getName()).log(Level.SEVERE, null, ex);
} catch (IOException ex) {
Logger.getLogger(POITestFrame.class.getName()).log(Level.SEVERE, null, ex);
}
}//GEN-LAST:event_excelButtonActionPerformed
/**
* @param args the command line arguments
*/
public static void main(String args[]) {
/*
* Set the Nimbus look and feel
*/
//<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
/*
* If Nimbus (introduced in Java SE 6) is not available, stay with the
* default look and feel. For details see
* http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html
*/
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(POITestFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (InstantiationException ex) {
java.util.logging.Logger.getLogger(POITestFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (IllegalAccessException ex) {
java.util.logging.Logger.getLogger(POITestFrame.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
} catch (javax.swing.UnsupportedLookAndFeelException ex) {
java.util.logging.Logger.getLogger(POITestFrame.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 POITestFrame().setVisible(true);
}
});
}
// Variables declaration - do not modify//GEN-BEGIN:variables
private javax.swing.JButton excelButton;
private javax.swing.JScrollPane jScrollPane1;
private javax.swing.JTable jTable1;
// End of variables declaration//GEN-END:variables
private void PoiWriter(JTable jTable1) throws FileNotFoundException, IOException {
TableModel model = jTable1.getModel();
// create a workbook
Workbook wb = new HSSFWorkbook(); // xls file
// create a new sheet
Sheet sheet = wb.createSheet("Foglio di Prova!");
// declare a row object reference
Row r = null;
// declare a cell object reference
Cell c = null;
// create a new file
FileOutputStream fos;
fos = new FileOutputStream("File di Prova.xls");
// create a sheet table rows
int rownum;
for (rownum = (short) 0; rownum < model.getRowCount(); rownum++) {
// create a row
r = sheet.createRow(rownum);
}
for (short cellnum = (short) 0; cellnum < model.getRowCount(); cellnum ++) {
// create a numeric cell
c = r.createCell(cellnum);
// populate table with custom objects
for(int i=0; i < model.getRowCount();i++){
for(int j=0;j < model.getColumnCount();j++){
String aplala = "blabla";
c.setCellValue(aplala);
}
}
}
wb.write(fos);
fos.close();
}
}
PS:如果你想知道为什么我用对象类型构建表:这不是我正在使用的项目，我制作了这个片段来测试 hssf，因为生成的 excel 是非常可编辑的，但事情不是'似乎进展顺利.
PS: If you're wondering why I built the table with Object types: this isn't the project I'm working with, I made this snippet to test hssf out since the resulting excel is quite editable but things don't seem to go well.
PPS:我也尝试使用 tokenizer 类，但我不确定您是否可以像使用 poi lib 一样编辑生成的 excel 文件.
PPS: I tried working with the tokenizer class as well but I'm not sure you can edit the resulting excel file as much as with poi lib.
PPPS:这是我第一次尝试使用 Java，所以请不要太粗鲁！
PPPS: This is my very first attempt with java so, please, don't be too rough!
我希望问题足够清楚，并提前致谢，我正在努力提高编程能力:P
I hope the questions were clear enough and thanks in advance, I do am trying to get better at programming :P
经过一天的练习，这就是我想出的似乎可以与 apache poi 库一起使用的方法，感谢提供良好指导的帮助人员！
after a day of practice this is what I came up with which seems to work with the apache poi library, thanks for the help guys it gave good pointers!
int rowNum;
int colNum;
int tempRows;
int rowCount = model.getRowCount();
int columnCount = model.getColumnCount();
// create the headers
for (colNum = 0; colNum < columnCount; colNum++) {
if (colNum == 0) {
r = sheet.createRow(0);
}
c = r.createCell(colNum);
c.setCellValue(model.getColumnName(colNum));
}
for (rowNum = 0; rowNum < rowCount; rowNum++) {
// create rows + 1 (to account for the headers)
tempRows = rowNum + 1;
r = sheet.createRow(tempRows);
for (short cellnum = 0; cellnum < columnCount; cellnum ++) {
// create cells
c = r.createCell(cellnum);
// add values from table
c.setCellValue(model.getValueAt(rowNum, cellnum).toString());
}
}
如果您认为代码可以改进，请随时发表评论，欢迎提出建议，尤其是像我这样的新手;)
Feel free to comment if you think the code can be improved, suggestions are always welcome, especially to newcomers like myself ;)
再次感谢您的提示，他们真的做到了 ^^
Again, thanks for the tips, they really did the trick ^^
推荐答案
请阅读关于 JTable、创建表模型 和 实现的数据类型、JTable 知道那些Column Classes，并返回合适的值输出到POI；对于 MS Excel 中的大多数类型:
Please read the tutorial about JTable, Creating a Table Model and implemented data types, JTable knows those Column Classes, and returns proper value for output to POI; for most of types in the MS Excel:
你也必须在当前行的单元格内循环
you have to loop inside cells in current row, too
必须为各种数据类型(Date、Double、String)格式化输出
have to format output for various data types (Date, Double, String)
然后你可以专注于创建公式和着色单元格
then you can focus on creating formula(s) and coloring cell(s)
循环TableModel的代码也可以生成MS Excel文件，使用标准的windows分隔符:
Code to loop through TableModel can generate MS Excel File, too, with standard windows delimiters:
public class ExcelCustomerReport {
public ExcelCustomerReport() {
}
public void exportTable(JTable table, File file) throws IOException {
TableModel model = table.getModel();
FileWriter out = new FileWriter(file);
String groupExport = "";
for (int i = 0; i < (model.getColumnCount()); i++) {//* disable export from TableHeaders
groupExport = String.valueOf(model.getColumnName(i));
out.write(String.valueOf(groupExport) + "\t");
}
out.write("\n");
for (int i = 0; i < model.getRowCount(); i++) {
for (int j = 0; j < (model.getColumnCount()); j++) {
if (model.getValueAt(i, j) == null) {
out.write("null" + "\t");
} else {
groupExport = String.valueOf(model.getValueAt(i, j));
out.write(String.valueOf(groupExport) + "\t");
}
}
out.write("\n");
}
out.close();
}
}
这篇关于apache poi:将 jtable 保存到文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
