JavaFX : TableView存取Excel範例

本篇文章介紹如何透過Apache POI存取Excel的實際程式範例

1.在IDE裡裝好Apache POI插件

2.程式碼如下:


MyFXML.java

import javafx.application.Application;
import javafx.fxml.FXMLLoader;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.stage.Stage;

public class MyFXML extends Application{

 @Override
 public void start(Stage stage) throws Exception {
  // TODO Auto-generated method stub
  Parent root = FXMLLoader.load(getClass().getResource("/exampleERP/main.fxml"));
     
        Scene scene = new Scene(root);
        stage.setTitle("Table View Sample");
        stage.setWidth(450);
        stage.setHeight(550);
        stage.setScene(scene);
        stage.show();
 }
 public static void main(String[] args) {
        launch(args);
    }

}

Property.java

import javafx.beans.property.SimpleStringProperty;
import javafx.beans.property.StringProperty;

public class Property {

  private final SimpleStringProperty date = new SimpleStringProperty(this, "date");
     public StringProperty dateProperty() {
         return date ;
     }
     public final String getdate() {
         return dateProperty().get();
     }
     public final void setdate(String date) {
      dateProperty().set(date);
     }

     private final StringProperty formname = new SimpleStringProperty(this, "formname");
     public StringProperty formnameProperty() {
         return formname ;
     }
     public final String getforname() {
         return formnameProperty().get();
     }
     public final void setformname(String formname) {
      formnameProperty().set(formname);
     }

     private final StringProperty name = new SimpleStringProperty(this, "name");
     public StringProperty nameProperty() {
         return name ;
     }
     public final String getname() {
         return nameProperty().get();
     }
     public final void setname(String name) {
      nameProperty().set(name);
     }
     
     private final StringProperty number = new SimpleStringProperty(this, "number");
     public StringProperty numberProperty() {
         return number ;
     }
     public final String getnumber() {
         return numberProperty().get();
     }
     public final void setnumber(String number) {
      numberProperty().set(number);
     }
     
     private final StringProperty ps = new SimpleStringProperty(this, "ps");
     public StringProperty psProperty() {
         return ps ;
     }
     public final String getps() {
         return psProperty().get();
     }
     public final void setps(String ps) {
      psProperty().set(ps);
     }

     public Property() {}

     public Property(String id, String name, String money, String number, String ps) {
         setdate(id);
         setformname(name);
         setname(money);
         setnumber(number);
         setps(ps);
     }
}

mainController.java

import java.net.URL;
import java.util.ResourceBundle;

import javafx.fxml.FXMLLoader;
import javafx.fxml.Initializable;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.stage.Stage;

public class mainController implements Initializable{

 @Override
 public void initialize(URL arg0, ResourceBundle arg1) {
  // TODO Auto-generated method stub
  
 }
 public void changestage(){
  Parent root = null;
     try {
   root = FXMLLoader.load(getClass().getResource("/exampleERP/main2.fxml"));
  } catch (Exception e1) {
   // TODO Auto-generated catch block
   e1.printStackTrace();
  }
     Scene secondscene = new Scene(root);
     Stage secondstage = new Stage();
     secondstage.setScene(secondscene);
     secondstage.show();
 }

}

main2Controller.java

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URL;
import java.time.LocalDate;
import java.util.Date;
import java.util.ResourceBundle;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Row;

import example.Person;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.fxml.FXML;
import javafx.fxml.FXMLLoader;
import javafx.fxml.Initializable;
import javafx.scene.Node;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.ComboBox;
import javafx.scene.control.DatePicker;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TextField;
import javafx.scene.control.TableColumn.CellEditEvent;
import javafx.scene.control.TableColumnBase;
import javafx.scene.control.TableView;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.control.cell.TextFieldTableCell;
import javafx.stage.Stage;

public class main2Controller implements Initializable{
 
 @FXML
 private DatePicker datepicker;
 
 @FXML
 private TextField formname,number,ps,count,search;
 
 @FXML 
 private ComboBox combobox;
 
 @FXML
 private Button insert,exit,btnsave,btnimport,btndelete;
 
 @FXML
 private TableView tableview;
 
 static ObservableList<Property> data =
            FXCollections.observableArrayList();

 @Override
 public void initialize(URL location, ResourceBundle resources) {
  // TODO Auto-generated method stub
  
  tableview.setEditable(true);
  
  TableColumn dateT= new TableColumn("日期");
  dateT.setMinWidth(100);
  dateT.setCellValueFactory(
            new PropertyValueFactory<Property, String>("date"));
  dateT.setCellFactory(TextFieldTableCell.forTableColumn());
  dateT.setOnEditCommit(
            new EventHandler<CellEditEvent<Property, String>>() {
                @Override
                public void handle(CellEditEvent<Property, String> t) {
                    ((Property) t.getTableView().getItems().get(
                            t.getTablePosition().getRow())
                            ).setdate(t.getNewValue());
                }
            }
        );
  TableColumn formnameT = new TableColumn("單號");
  formnameT.setMinWidth(100);
  formnameT.setCellValueFactory(
            new PropertyValueFactory<Property, String>("formname"));
  formnameT.setCellFactory(TextFieldTableCell.forTableColumn());
  formnameT.setOnEditCommit(
            new EventHandler<CellEditEvent<Property, String>>() {
                @Override
                public void handle(CellEditEvent<Property, String> t) {
                    ((Property) t.getTableView().getItems().get(
                            t.getTablePosition().getRow())
                            ).setformname(t.getNewValue());
                }
            }
        );
  TableColumn nameT = new TableColumn("品名");
  nameT.setMinWidth(100);
  nameT.setCellValueFactory(
            new PropertyValueFactory<Property, String>("name"));
  nameT.setCellFactory(TextFieldTableCell.forTableColumn());
  nameT.setOnEditCommit(
            new EventHandler<CellEditEvent<Property, String>>() {
                @Override
                public void handle(CellEditEvent<Property, String> t) {
                    ((Property) t.getTableView().getItems().get(
                            t.getTablePosition().getRow())
                            ).setname(t.getNewValue());
                }
            }
        );
  TableColumn numberT = new TableColumn("數量");
  numberT.setMinWidth(100);
  numberT.setCellValueFactory(
            new PropertyValueFactory<Property, String>("number"));
  numberT.setCellFactory(TextFieldTableCell.forTableColumn());
  numberT.setOnEditCommit(
            new EventHandler<CellEditEvent<Property, String>>() {
                @Override
                public void handle(CellEditEvent<Property, String> t) {
                    ((Property) t.getTableView().getItems().get(
                            t.getTablePosition().getRow())
                            ).setnumber(t.getNewValue());
                }
            }
        );
  TableColumn psT = new TableColumn("標註");
  psT.setMinWidth(100);
  psT.setCellValueFactory(
            new PropertyValueFactory<Property, String>("ps"));
  psT.setCellFactory(TextFieldTableCell.forTableColumn());
  psT.setOnEditCommit(
            new EventHandler<CellEditEvent<Property, String>>() {
                @Override
                public void handle(CellEditEvent<Property, String> t) {
                    ((Property) t.getTableView().getItems().get(
                            t.getTablePosition().getRow())
                            ).setps(t.getNewValue());
                }
            }
        );
  combobox.getItems().addAll("鐵塊","鉛塊","銅塊");
  
  tableview.setItems(data);
  tableview.getColumns().addAll(dateT, formnameT, nameT, numberT, psT);
  count.setText(String.valueOf(data.size()));
       
  
   insert.setOnAction(new EventHandler<ActionEvent>() {
             @Override
             public void handle(ActionEvent e) {
              String date = datepicker.getValue().toString();

              data.add(new Property(
                   date,
                   formname.getText(),
                   combobox.getSelectionModel().getSelectedItem().toString(),
                   number.getText(),
                   ps.getText()));
                 formname.clear();
                
                 number.clear();
                 ps.clear();
                 count.setText(String.valueOf(data.size()));
                 
             }
         });
   
   
   ObservableList data =  tableview.getItems();
   search.textProperty().addListener((ObservableValue<? extends String> observable, String oldValue, String newValue) -> {
               if (oldValue != null && (newValue.length() < oldValue.length())) {
                   tableview.setItems(data);
               }
               String value = newValue.toLowerCase();
               ObservableList<Property> subentries = FXCollections.observableArrayList();

               long ct = tableview.getColumns().stream().count();
               for (int i = 0; i < tableview.getItems().size(); i++) {
                   for (int j = 0; j < ct; j++) {
                       String entry = "" + ((TableColumnBase) tableview.getColumns().get(j)).getCellData(i);
                       if (entry.toLowerCase().contains(value)) {
                           subentries.add((Property) tableview.getItems().get(i));
                           
                           break;
                       }
                       
                   }
               }
               tableview.setItems(subentries);
               count.setText(String.valueOf(subentries.size()));
           });
   
   exit.setOnAction(new EventHandler<ActionEvent>() {
             @Override
             public void handle(ActionEvent e) {
              Stage stage = (Stage)exit.getScene().getWindow();
              stage.close();
             }
         });
   
   btnsave.setOnAction(new EventHandler<ActionEvent>() {
             @Override
             public void handle(ActionEvent e) {
              try {
                     FileOutputStream fileOut = new FileOutputStream("poi-test.xls");
                     HSSFWorkbook workbook = new HSSFWorkbook();
                     HSSFSheet worksheet = workbook.createSheet("POI Worksheet");

                     // index from 0,0... cell A1 is cell(0,0)
                     HSSFRow row1 = worksheet.createRow((short) 0);

                     HSSFCell cellA1 = row1.createCell((short) 0);
                     cellA1.setCellValue("日期");
                     HSSFCellStyle cellStyle = workbook.createCellStyle();
                     cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
                     cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                     cellA1.setCellStyle(cellStyle);

                     HSSFCell cellB1 = row1.createCell((short) 1);
                     cellB1.setCellValue("單號");
                     cellStyle = workbook.createCellStyle();
                     cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
                     cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                     cellB1.setCellStyle(cellStyle);

                     HSSFCell cellC1 = row1.createCell((short) 2);
                     cellC1.setCellValue("品名");
                     cellStyle = workbook.createCellStyle();
                     cellStyle.setFillForegroundColor(HSSFColor.LIGHT_GREEN.index);
                     cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                     cellC1.setCellStyle(cellStyle);
                                

                     HSSFCell cellD1 = row1.createCell((short) 3);
                     cellD1.setCellValue("數量");
                     cellStyle = workbook.createCellStyle();
                     cellStyle.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
                     cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                     cellD1.setCellStyle(cellStyle);
                     
                     HSSFCell cellE1 = row1.createCell((short) 4);
                     cellE1.setCellValue("標註");
                     cellStyle = workbook.createCellStyle();
                     cellStyle.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
                     cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
                     cellE1.setCellStyle(cellStyle);
                     
                     for(int i=0;i<data.size();i++){
                      HSSFRow row2 = worksheet.createRow((short) i+1);
                      row2.createCell(0).setCellValue(((Property) data.get(i)).getdate());
                      row2.createCell(1).setCellValue(((Property) data.get(i)).getforname());
                      row2.createCell(2).setCellValue(((Property) data.get(i)).getname());
                      row2.createCell(3).setCellValue(((Property) data.get(i)).getnumber());
                      row2.createCell(4).setCellValue(((Property) data.get(i)).getps());
                     }

                     workbook.write(fileOut);
                     fileOut.flush();
                     fileOut.close();
                 } catch (FileNotFoundException e1) {
                     e1.printStackTrace();
                 } catch (IOException e2) {
                     e2.printStackTrace();
                 }
             }
         });
   
   btnimport.setOnAction(new EventHandler<ActionEvent>() {
             @Override
             public void handle(ActionEvent e) {
              try {
      data.clear();
      HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream("poi-test.xls"));
      HSSFSheet sheet = workbook.getSheetAt(0);
      HSSFRow row;
      for(int i=1;i<=sheet.getLastRowNum();i++){
       row=sheet.getRow(i);
       data.add(new Property(
                     row.getCell(0).getStringCellValue(),
                     row.getCell(1).getStringCellValue(),
                     row.getCell(2).getStringCellValue(),
                     row.getCell(3).getStringCellValue(),
                     row.getCell(4).getStringCellValue()));
       
       
      }
      count.setText(String.valueOf(data.size()));
      
     } catch (FileNotFoundException e1) {
      // TODO Auto-generated catch block
      e1.printStackTrace();
     }catch (IOException e2) {
                     e2.printStackTrace();
                 }
              
                    
             }
         });
   btndelete.setOnAction(new EventHandler<ActionEvent>() {
             @Override
             public void handle(ActionEvent e) {
                   Object selectedItem = tableview.getSelectionModel().getSelectedItem();
                  tableview.getItems().remove(selectedItem);
                  
             }


    
         });
   
   
 }

}

PS:上述的存檔方法是不能選擇存檔地方的，一律都存到專案資料夾底下，如果想要另存新檔可以參考下面這段程式碼:

buttonSave.setOnAction(new EventHandler() {

          @Override
          public void handle(ActionEvent event) {
              FileChooser fileChooser = new FileChooser();

              //Set extension filter
              FileChooser.ExtensionFilter extFilter = new FileChooser.ExtensionFilter("excel files (*.xls)", "*.xls");
              fileChooser.getExtensionFilters().add(extFilter);

              //Show save file dialog
              File file = fileChooser.showSaveDialog(primaryStage);

              if(file != null){
                  SaveFile(file);
                  System.out.println("hi");
              }
          }
      });