Converting Jtable to excel,updated file error

Question

I have wriiten a code to create jTable and export to Excel.I works.But when I open the Excel file the file is not fully recovered. It shows the following problem:

Repaired Part: /xl/worksheets/sheet2.xml part with XML error. Load error. Line 2, column 0. Repaired Part: /xl/worksheets/sheet7.xml part with XML error. Load error. Line 2, column 0. Removed Records: Named range from /xl/workbook.xml part (Workbook) Removed Records: Table from /xl/tables/table1.xml part (Table)

import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.swing.JTextField;
import javax.swing.JLabel;
import javax.swing.JButton;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.awt.event.ActionEvent;

public class GUI {
    private static JTextField textField;
    private static JTextField textField_1;

public static void main(String args[]) throws IOException {
    JFrame frame = new JFrame();
    frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);

    Object rowData[][] = null ;
    Object columnNames[] = { "Column One", "Column Two"};
    DefaultTableModel model = new    DefaultTableModel(rowData,columnNames);
    frame.getContentPane().setLayout(null);
    JTable table = new JTable(model);
    table.setModel(model);
    JScrollPane scrollPane = new JScrollPane(table);
    scrollPane.setBounds(76, 106, 300, 200);
    scrollPane.setVisible(true);
    frame.getContentPane().add(scrollPane);

    textField = new JTextField();
    textField.setBounds(76, 21, 86, 20);
    frame.getContentPane().add(textField);
    textField.setColumns(10);

    textField_1 = new JTextField();
    textField_1.setBounds(76, 61, 86, 20);
    frame.getContentPane().add(textField_1);
    textField_1.setColumns(10);

    JLabel lblNewLabel = new JLabel("Name");
    lblNewLabel.setBounds(20, 24, 46, 14);
    frame.getContentPane().add(lblNewLabel);

    JLabel lblAge = new JLabel("Age");
    lblAge.setBounds(20, 64, 46, 14);
    frame.getContentPane().add(lblAge);



    JButton btnNewButton = new JButton("Get Data");
    btnNewButton.setBounds(235, 40, 89, 23);
    btnNewButton.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent ae) {
            String name = textField.getText().toString();
            int age = Integer.parseInt(textField_1.getText());
            model.addRow(new Object[] {name,age});
            Object obj1 = GetData(table, 0,0 );
            System.out.println("Cell value of 1 column and 1 row :" + obj1);
            Object obj2 = GetData(table, 0,1 );
            System.out.println("Cell value of 2 column and 1 row :" + obj2);

        }
    });



    frame.getContentPane().add(btnNewButton);

    JButton btnNewButton_1 = new JButton("Excel");
    btnNewButton_1.addActionListener(new ActionListener() {
        public void actionPerformed(ActionEvent evt) {
            try {
                writeToExcel(table);
            } catch (Exception e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    });
    btnNewButton_1.setBounds(340, 40, 89, 23);
    frame.getContentPane().add(btnNewButton_1);
    frame.setSize(455, 356);
    frame.setVisible(true);
  }



protected static void writeToExcel(JTable table) throws Exception {

    try {
        File file = new File("IET.xlsx");
        FileInputStream inputStream = new FileInputStream(file);
        XSSFWorkbook workbook = (XSSFWorkbook) WorkbookFactory.create(inputStream);
        XSSFSheet sh = workbook.getSheet("UserInputs");
        TableModel model = table.getModel();
        String value1 = model.getValueAt(0, 0).toString();
        String value2 = model.getValueAt(0, 1).toString();

        workbook.getSheet("UserInputs").getRow(8).getCell(1).setCellValue(value2);


    FileOutputStream fileOut = new FileOutputStream(file);
    workbook.write(fileOut);
    fileOut.close();
    workbook.close();
    System.out.println("File written successfully");
    }
    catch(Exception e){
    System.out.print(e);}
    }



private static Object GetData(JTable table, int x, int y) {

    return table.getValueAt(x, y).toString();
}
  }

Is there something wrong with the code?

Answer 1

Your code only does overwriting one cell B9 in sheet UserInputs. If this leads to the problem:

"Repaired Part: /xl/worksheets/sheet2.xml part with XML error. Load error. Line 2, column 0. Repaired Part: /xl/worksheets/sheet7.xml part with XML error. Load error. Line 2, column 0. Removed Records: Named range from /xl/workbook.xml part (Workbook) Removed Records: Table from /xl/tables/table1.xml part (Table)"

while opening the workbook in Excel, then cell B9 in sheet sheet UserInputs is one of the header cells of a Excel table object. Furthermore at least in sheet2 and in sheet7 are structured table references, such as =Tablename[Columnname], used in formulas and such structured table references also are used in named ranges.

So if B9 in sheet sheet UserInputs is one of the header cells of a Excel table object and is changed now, then at first the table itself is corrupt since one of the column names has changed without updating the table. But also all structured table references, such as =Tablename[Columnname], are corrupt, since Columnname has changed without updating all those formulas.

Updating the table headers would be possible using XSSFTable.updateHeaders. But updating all structured table references in all formulas in all sheets and even in all named ranges would be very expensive upto nearly impossible. So I would recommend not to change header cells of a Excel table object. Let the header cells as they are and change only the content range of the table.

Example:

Let's have the following IET.xlsx file:

As you see, there is a table named Table1 in sheet UserInput and the headers are in row 9.

Now we can overwriting the content range B10:F[10+n] with content from a JTable like so:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.*;
import org.apache.poi.ss.util.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.FileInputStream;

import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;

class ReadAndWriteExcelHavingTableObject {

 static void writeToExcel(XSSFTable exceltable, JTable table) throws Exception {

  int exceltableStartRow = exceltable.getStartRowIndex();
  int exceltableStartCol = exceltable.getStartColIndex();

  XSSFSheet sheet = (XSSFSheet)exceltable.getParent();

  TableModel model = table.getModel();

  int exceltableEndRow = exceltableStartRow + model.getRowCount(); //as much rows as are in the model
  int exceltableEndCol = exceltable.getEndColIndex();

  //write the content
  for (int r = 0; r < model.getRowCount(); r++) {
   for (int c = 0; c < exceltableEndCol - exceltableStartCol + 1; c++) {
    XSSFRow row = sheet.getRow(exceltableStartRow + 1 + r);
    if (row == null) row = sheet.createRow(exceltableStartRow + 1 + r);
    XSSFCell cell = row.getCell(exceltableStartCol + c);
    if (cell == null) cell = row.createCell(exceltableStartCol + c);

    if (c < model.getColumnCount() && model.getValueAt(r, c) instanceof String) {
     String str = (String)model.getValueAt(r, c);
     cell.setCellValue(str);
    } else if (c < model.getColumnCount() && model.getValueAt(r, c) instanceof Double) {
     Double value = (Double)model.getValueAt(r, c);
     cell.setCellValue(value);
    } else if (c >= model.getColumnCount()) { //formula cells
     XSSFCell firstCell = sheet.getRow(exceltableStartRow + 1).getCell(exceltableStartCol + c);
     if (firstCell.getCellTypeEnum() == CellType.FORMULA) { 
      cell.setCellFormula(firstCell.getCellFormula());
     }
    }
   }
  }

  //update the size of exceltable
  exceltable.setCellReferences(new AreaReference(
    new CellReference(exceltableStartRow, exceltableStartCol),
    new CellReference(exceltableEndRow, exceltableEndCol),
    SpreadsheetVersion.EXCEL2007));

 }


 public static void main(String[] args) throws Exception {

  Object rowData[][] = {
          {"Bob", 12.0, 3.0},
          {"Alice", 34.0, 2.5},
          {"Jack", 56.0, 2.0},
          {"John", 78.0, 1.5}
          };
  Object columnNames[] = {"Name", "Amount", "Factor"};
  DefaultTableModel model = new DefaultTableModel(rowData, columnNames);
  JTable table = new JTable(model);
  table.setModel(model);

  File file = new File("IET.xlsx");
  FileInputStream inputStream = new FileInputStream(file);
  XSSFWorkbook workbook = (XSSFWorkbook)WorkbookFactory.create(inputStream);

  XSSFTable exceltable = workbook.getTable("Table1");

  writeToExcel(exceltable, table);

  workbook.setForceFormulaRecalculation(true);

  FileOutputStream fileOut = new FileOutputStream(file);
  workbook.write(fileOut);
  fileOut.close();
  workbook.close();

 }
}

Result will be:

And since no headers are changed, the table itself and all structured references will be not corrupt after this.