JavaFx: Export TableView to excel with name of columns

Question

I'm trying to export tableView to excel using Apache POI

Every thing is well but I need export all my table not just items, I mean with columns names when I use this code:

HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet spreadsheet = workbook.createSheet("sample");

HSSFRow row = null;

for (int i = 0; i < TousEmpSusp.getItems().size(); i++) {
    row = spreadsheet.createRow(i);
    for (int j = 0; j < TousEmpSusp.getColumns().size(); j++) {
        row.createCell(j).setCellValue(TousEmpSusp.getColumns().get(j).getCellData(i).toString());
    }
}

it exports only items, I tried to modify it like this:

   for (int j = 0; j < TousEmpView.getColumns().size(); j++) {
            row.createCell(j).setCellValue(TousEmpView.getColumns().get(j).getText());

        }
        for (int i = 0; i < TousEmpView.getItems().size(); i++) {
            row = spreadsheet.createRow(i+1);
            for (int j = 0; j < TousEmpView.getColumns().size(); j++) {
                row.createCell(j).setCellValue(TousEmpView.getColumns().get(j).getCellData(i).toString());
            }
        }

but it invokes IndexOutOfBoundsException. So how can I export tableView with column names? What should I modify?

Looks like this code should execute correctly, can you point on which line it throws `IndexOutOfBoundsException`? — vl4d1m1r4, Sep 02 '17 at 21:49
Yeah this code works correctly ,but it does not show names of columns,it starts from item one of table view and when i use i=1 it invokes IndexOutOfBoundsException. — Menai Ala Eddine - Aladdin, Sep 02 '17 at 22:18
Which JDK version and Apache POI version are you using? Are you sure you are not using something like: `j <= TousEmpView.getColumns().size()` in your for loop? That will throw the exception when iterating for `i = TousEmpView.getColumns().size()`. — vl4d1m1r4, Sep 02 '17 at 22:32
My problem is how to export tableView starting from columns header name ,my code starts from 1 item of tableView.With this code row.createCell(j).setCellValue(TousEmpView.getColumns().get(j).getText()); can get column name but i have a trick to add this line to my code. — Menai Ala Eddine - Aladdin, Sep 02 '17 at 22:46

score 9 · Accepted Answer · edited Sep 02 '17 at 23:21

Here is a working example that will create a workbook.xls when you run it with the column headers exported. It is pretty much what you wrote in your question so I don't get where it doesn't work.

import javafx.application.Application;
import javafx.application.Platform;
import javafx.beans.property.SimpleStringProperty;
import javafx.beans.property.StringProperty;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.stage.Stage;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class TableViewExample extends Application {

    public static void main(String[] args) {
        launch(args);
    }

    @Override
    public void start(Stage primaryStage) throws IOException {

        TableView<Person> table = new TableView<Person>();

        ObservableList<Person> teamMembers = getTeamMembers();
        table.setItems(teamMembers);

        TableColumn<Person,String> firstNameCol = new TableColumn<Person,String>("First Name");
        firstNameCol.setCellValueFactory(new PropertyValueFactory<Person, String>("firstName"));
        TableColumn<Person,String> lastNameCol = new TableColumn<Person,String>("Last Name");
        lastNameCol.setCellValueFactory(new PropertyValueFactory<Person, String>("lastName"));

        ObservableList<TableColumn<Person, ?>> columns = table.getColumns();
        columns.add(firstNameCol);
        columns.add(lastNameCol);

        Workbook workbook = new HSSFWorkbook();
        Sheet spreadsheet = workbook.createSheet("sample");

        Row row = spreadsheet.createRow(0);

        for (int j = 0; j < table.getColumns().size(); j++) {
            row.createCell(j).setCellValue(table.getColumns().get(j).getText());
        }

        for (int i = 0; i < table.getItems().size(); i++) {
            row = spreadsheet.createRow(i + 1);
            for (int j = 0; j < table.getColumns().size(); j++) {
                if(table.getColumns().get(j).getCellData(i) != null) { 
                    row.createCell(j).setCellValue(table.getColumns().get(j).getCellData(i).toString()); 
                }
                else {
                    row.createCell(j).setCellValue("");
                }   
            }
        }

        FileOutputStream fileOut = new FileOutputStream("workbook.xls");
        workbook.write(fileOut);
        fileOut.close();

        Platform.exit();

    }

    private ObservableList<Person> getTeamMembers() {

        ObservableList<Person> people = FXCollections.observableArrayList();
        Person person1 = new Person();
        person1.setFirstName("John");
        person1.setLastName("Doe");
        people.add(person1);
        people.add(person1);
        people.add(person1);
        people.add(person1);
        people.add(person1);
        people.add(person1);

        Person person2 = new Person();
        person2.setFirstName("Jane");
        person2.setLastName("Doe");
        people.add(person2);
        people.add(person2);
        people.add(person2);
        people.add(person2);
        people.add(person2);

        return people;
    }

    public class Person {
        private StringProperty firstName;
        public void setFirstName(String value) { firstNameProperty().set(value); }
        public String getFirstName() { return firstNameProperty().get(); }
        public StringProperty firstNameProperty() {
            if (firstName == null) firstName = new SimpleStringProperty(this, "firstName");
            return firstName;
        }

        private StringProperty lastName;
        public void setLastName(String value) { lastNameProperty().set(value); }
        public String getLastName() { return lastNameProperty().get(); }
        public StringProperty lastNameProperty() {
            if (lastName == null) lastName = new SimpleStringProperty(this, "lastName");
            return lastName;
        }
    }
}

Perfect I used the same before but when i used i=1 i 'v got problems.Thanks but i will edit for null cells. — Menai Ala Eddine - Aladdin, Sep 02 '17 at 23:11

score 5 · Answer 2 · answered Sep 24 '19 at 07:08

Just pass the TableView to the export method of this class and it will take care of everything

package sample;
import javafx.scene.control.TableView;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelExport<T> {

    public void export(TableView<T> tableView){

        HSSFWorkbook hssfWorkbook=new HSSFWorkbook();
        HSSFSheet hssfSheet=  hssfWorkbook.createSheet("Sheet1");
        HSSFRow firstRow= hssfSheet.createRow(0);

        ///set titles of columns
        for (int i=0; i<tableView.getColumns().size();i++){

            firstRow.createCell(i).setCellValue(tableView.getColumns().get(i).getText());

        }


        for (int row=0; row<tableView.getItems().size();row++){

            HSSFRow hssfRow= hssfSheet.createRow(row+1);

            for (int col=0; col<tableView.getColumns().size(); col++){

                Object celValue = tableView.getColumns().get(col).getCellObservableValue(row).getValue();

                try {
                    if (celValue != null && Double.parseDouble(celValue.toString()) != 0.0) {
                        hssfRow.createCell(col).setCellValue(Double.parseDouble(celValue.toString()));
                    }
                } catch (  NumberFormatException e ){

                    hssfRow.createCell(col).setCellValue(celValue.toString());
                }

            }

        }

        //save excel file and close the workbook
        try {
            hssfWorkbook.write(new FileOutputStream("WorkBook.xls"));
            hssfWorkbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }




    }

}

JavaFx: Export TableView to excel with name of columns

2 Answers2

Linked