JTable导出导入表格

JTable导出表格//如果表格里面有空格（即有表格没有数据）会出现空指针异常package com.ren;import java.io.File;import java.io.FileNotFoundException;import java.text.SimpleDateFormat;import java.util.Date;import javax.swing.JOptionPane;import javax.swing.JTable;import jxl.Workbook;import jxl.format.Alignment;import jxl.format.Colour;import jxl.format.UnderlineStyle;import jxl.format.VerticalAlignment;import bel;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;/**** 把JTable中的数据导出到Excel表格中工具类** @author Administrator**/public class JTableToExcel {/**外部调用的方法* @param file* @param heading* @param inscribe* @param table*/public void export(File file, String heading, String inscribe, JTable table) { try {WritableWorkbook workbook = null;// 创建工作薄if (file.exists()) {// 文件已经存在workbook = Workbook.createWorkbook(file, Workbook.getWorkbook(file));} else { // 文件还不存在workbook = Workbook.createWorkbook(file);}// 创建工作表WritableSheet sheet = workbook.createSheet(heading, workbook.getNumberOfSheets());// 取得Table的行数(rowNum), 列数(colNum)int rowNum = table.getRowCount();int colNum = table.getColumnCount();// 填写主标题fillHeading(sheet, heading, colNum);// 填写列名fillColumnName(sheet, table, colNum);// 填写落款fillInscribe(sheet, inscribe, rowNum, colNum);// 填写数据fillCell(sheet, table, rowNum, colNum);// 写入工作表workbook.write();workbook.close();// 导出成功提示框int dialog = JOptionPane.showConfirmDialog(null, "统计表导出成功！是否现在打开？", "提示", JOptionPane.YES_NO_OPTION);if (dialog == JOptionPane.YES_OPTION) {Runtime.getRuntime().exec("cmd /c start \"\" \"" + file + "\"");}} catch (FileNotFoundException e) {JOptionPane.showMessageDialog(null, "导入数据前请关闭工作表");} catch (Exception e) {JOptionPane.showMessageDialog(null, "没有进行筛选");}}/*** 填写主标题* @param sheet* @param heading* @param colNum* @throws WriteException*/private void fillHeading(WritableSheet sheet, String heading, int colNum) throws WriteException {WritableFont font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED);// 定义字体WritableCellFormat format = new WritableCellFormat(font);// 创建格式化对象format.setAlignment(Alignment.CENTRE);// 水平居中显示format.setVerticalAlignment(VerticalAlignment.CENTRE);// 垂直居中显示sheet.mergeCells(0, 0, colNum - 1, 0); // 合并单元格sheet.setRowView(0, 600); // 设置行高sheet.addCell(new Label(0, 0, heading, format));// 填写工作表}/*** 填写列名* @param sheet* @param table* @param colNum* @throws WriteException*/private void fillColumnName(WritableSheet sheet, JTable table, int colNum) throws WriteException {WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);// 定义字体WritableCellFormat format = new WritableCellFormat(font);// 定义格式化对象format.setAlignment(Alignment.CENTRE);// 水平居中显示sheet.setColumnView(0, 15);// 设置列宽for (int col = 0; col < colNum; col++) {Label colName = new Label(col, 1, table.getModel().getColumnName(col), format);sheet.addCell(colName);}}/*** 填写落款* @param sheet* @param inscribe* @param colNum* @param rowNum* @throws WriteException*/private void fillInscribe(WritableSheet sheet, String inscribe, int rowNum, int colNum) throws WriteException {if( inscribe == null || inscribe.length() < 1 ) {inscribe = "导出时间: "+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());}WritableFont font = new WritableFont(WritableFont.ARIAL, 9, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);// 定义字体WritableCellFormat format = new WritableCellFormat(font);// 定义格式化对象format.setAlignment(Alignment.RIGHT);// 水平居中显示sheet.mergeCells(0, rowNum+3, colNum - 1, rowNum+3);// 合并单元格sheet.addCell(new Label(0, rowNum+3, inscribe, format));// 填写工作表}/*** 填写数据* @param sheet* @param talbe* @param rowNum* @param colNum* @throws WriteException*/private void fillCell(WritableSheet sheet, JTable table, int rowNum, int colNum ) throws WriteException {WritableFont font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);// 定义字体WritableCellFormat format = new WritableCellFormat(font);// 定义格式化对象format.setAlignment(Alignment.CENTRE); // 水平居中显示for (int i = 0; i < colNum; i++) { // 列for (int j = 1; j <= rowNum; j++) {// 行String str = table.getValueAt(j - 1, i).toString();Label labelN = new Label(i, j+1, str);try {sheet.addCell(labelN);} catch (Exception e) {e.printStackTrace();}}}}}调用：JButton button = new JButton("导出当前表格");button.addActionListener(new ActionListener() {public void actionPerformed(ActionEvent e) {FileDialog fd = new FileDialog(frame, "保存记录", FileDialog.SAVE);fd.setLocation(400, 250);fd.setVisible(true);String stringfile = fd.getDirectory()+fd.getFile()+".xls";new JTableToExcel().export(new File(stringfile), "员工信息表", "", table_2);}});button.setBounds(10, 442, 192, 23);panel_2.add(button);JTable导出表格package com.ren;import java.io.*;import javax.swing.JOptionPane;import javax.swing.JTable;import javax.swing.table.DefaultTableModel;import jxl.Cell;import jxl.CellType;import belCell;import jxl.NumberCell;import jxl.Sheet;import jxl.Workbook;import jxl.read.biff.BiffException;public class ExcelToJtable {// 参数为“文件”，“默认的模型”，“jtable”public void Import(File file, DefaultTableModel tableModel, JTable jTable) {Workbook rwb = null;Object[] object = new Object[jTable.getColumnCount()];try {FileInputStream is = new FileInputStream(file);rwb = Workbook.getWorkbook(is);// 创建一个workbook类读取excel文件// 得到第i个工作薄Sheet st = rwb.getSheet(0);// 这里有两种方法获取sheet表,1为名字，而为下标，从0开始// //得到st的列数// System.out.println("列"+ st.getColumns());// //得到st的行数// System.out.println("行"+ st.getRows());if (st.getRows() > 2) {//行数大于2的操作for (int t = tableModel.getRowCount() - 1; t >= 0; t--) {tableModel.removeRow(t);}// 行循环for (int i = 2; i < st.getRows(); i++) {//i=2去除表头,表头有多少行，i就等于多少// 列循环for (int j = 0; j < st.getColumns(); j++) {// 得到第j列第i行的数据Cell c00 = st.getCell(j, i);if (c00.getType() == BEL) {LabelCell labelc00 = (LabelCell) c00;object[j] = labelc00.getString();// jTable.setValueAt(labelc00.getString(), i-1, j);} else if (c00.getType() == CellType.NUMBER) {Double numd;int numi;NumberCell numc10 = (NumberCell) c00;numd = new Double(numc10.getValue());numi = numd.intValue();object[j] = numi;// jTable.setValueAt(numi, i-1, j);}}tableModel.addRow(object);}} else {//i=1去除表头,（即表头有多少行，i就等于多少）for (int i = 1; i < st.getRows(); i++) {// 列循环for (int j = 0; j < st.getColumns(); j++) {// 得到第j列第i行的数据Cell c00 = st.getCell(j, i);if (c00.getType() == BEL) {LabelCell labelc00 = (LabelCell) c00;object[j] = labelc00.getString();// jTable.setValueAt(labelc00.getString(), i-1, j);} else if (c00.getType() == CellType.NUMBER) {Double numd;int numi;NumberCell numc10 = (NumberCell) c00;numd = new Double(numc10.getValue());numi = numd.intValue();object[j] = numi;// jTable.setValueAt(numi, i-1, j);}}tableModel.addRow(object);}}/* JOptionPane.showMessageDialog(null, "导入" + file.getName() + "成功","成功", RMATION_MESSAGE);*/} catch (FileNotFoundException e) {//System.out.println("文件" + file.getName() + "没有找到");JOptionPane.showMessageDialog(null,"文件" + file.getName() + "没有找到");} catch (BiffException e) {//System.out.println("文件格式不正确");JOptionPane.showMessageDialog(null,"文件格式不正确");} catch (IOException e) {//System.out.println("文件读写失败");JOptionPane.showMessageDialog(null,"文件读写失败");} finally {// 关闭rwb.close();}}}调用：table_5 = new JTable(); //声明成全局dtm1=new DefaultTableModel(new Object[][] {}, ////声明成全局new String[] {"\u5DE5\u53F7", "\u59D3\u540D", "\u6027\u522B", "\u5E74\u9F84", "\u5165\u5382\u65F6\u95F4", "\u57FA\u672C\u5DE5\u8D44", "\u4EA7\u54C1\u7F16\u53F7", "\u751F\u4EA7\u8F66\u95F4", "\u6C11\u65CF", "\u624B\u673A", "\u751F\u4EA7\u603B\u6570", "\u4EA7\u54C1\u4EF7\u503C", "\u5DE5\u4F5C\u5929\u6570", "\u65F7\u5DE5\u5929\u6570"});table_5.getTableHeader().setReorderingAllowed(false); //表头不可以拖动table_5.setModel(dtm1 );scrollPane_7.setViewportView(table_5);JButton button_10 = new JButton("批量导入信息");button_10.addActionListener(new ActionListener() {public void actionPerformed(ActionEvent e) {FileDialog fd = new FileDialog(frame, "导入数据", FileDialog.LOAD);fd.setLocation(400, 250);fd.setVisible(true);String file_url = fd.getDirectory()+fd.getFile();File file=new File(file_url);new ExcelToJtable().Import(file, dtm1, table_5);table_5.setModel(dtm1 );//再次创建模型（setModel(dtm )）scrollPane_7.setViewportView(table_5);//再次获得视图//数据库添加上表数据int a1 = JOptionPane.showConfirmDialog(null, "你确认要添加以上信息？","确认", JOptionPane.OK_CANCEL_OPTION);if (a1 == JOptionPane.OK_OPTION) {new ClientMain("添加导入的数据");}else{return;}}});button_10.setBounds(441, 522, 112, 23);panel_3.add(button_10);。