How to read data from Excel in JavaFX application?

Question

I am creating a JavaFX FXMl application with hibernate as ORM in Java. I want to read data from Excel and store it to MySQL using hibernate, but it fails to create Workbook.

I have spend hours to read tutorials and other posts whether in SO or other sites, neither accepted answers nor comments helped me.

In my Form_Main.fxml file I have a button that calls related method to read data from Excel. I have added following JAR files:
* poi-4.1.0
* poi-ooxml-4.1.0
* poi-ooxml-schemas-4.1.0
* xmlbeans-3.0.1
* commons-collections4-4.2
to the project. My imports to read data are as follows:

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFRow;

the method I have defined is:

FileInputStream _File = new FileInputStream(new File("D:\\Students_Info.xlsx"));
XSSFWorkbook _Workbook = new XSSFWorkbook(_File);
XSSFSheet _Sheet = _Workbook.getSheetAt(0);
XSSFRow _Row;

The rest of code is about to writing data to MySQL using hibernate. But it doesn't work.

When I debug my program, at line:

XSSFWorkbook _Workbook = new XSSFWorkbook(_File);

It jumps to file FXMLLoader.java at line:

catch (InvocationTargetException exception) {
     throw new RuntimeException(exception);
}

My Stack Trace (if I am correct) is:

     [java] Exception in thread "JavaFX Application Thread" java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
     [java]     at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1774)
     [java]     at javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1657)
     [java]     at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
     [java]     at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
     [java]     at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
     [java]     at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
     [java]     at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
     [java]     at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
     [java]     at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
     [java]     at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
     [java]     at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
     [java]     at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
     [java]     at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
     [java]     at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
     [java]     at javafx.event.Event.fireEvent(Event.java:198)
     [java]     at javafx.scene.Node.fireEvent(Node.java:8411)
     [java]     at javafx.scene.control.Button.fire(Button.java:185)
     [java]     at com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:182)
     [java]     at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:96)
     [java]     at com.sun.javafx.scene.control.skin.BehaviorSkinBase$1.handle(BehaviorSkinBase.java:89)
     [java]     at com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
     [java]     at com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
     [java]     at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
     [java]     at com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
     [java]     at com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
     [java]     at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
     [java]     at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
     [java]     at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
     [java]     at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
     [java]     at com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
     [java]     at com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
     [java]     at com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
     [java]     at com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
     [java]     at javafx.event.Event.fireEvent(Event.java:198)
     [java]     at javafx.scene.Scene$MouseHandler.process(Scene.java:3757)
     [java]     at javafx.scene.Scene$MouseHandler.access$1500(Scene.java:3485)
     [java]     at javafx.scene.Scene.impl_processMouseEvent(Scene.java:1762)
     [java]     at javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2494)
     [java]     at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:394)
     [java]     at com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
     [java]     at java.security.AccessController.doPrivileged(Native Method)
     [java]     at com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$358(GlassViewEventHandler.java:432)
     [java]     at com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:410)
     [java]     at com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:431)
     [java]     at com.sun.glass.ui.View.handleMouseEvent(View.java:555)
     [java]     at com.sun.glass.ui.View.notifyMouse(View.java:937)
     [java]     at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
     [java]     at com.sun.glass.ui.win.WinApplication.lambda$null$152(WinApplication.java:177)
     [java]     at java.lang.Thread.run(Thread.java:748)
     [java] Caused by: java.lang.reflect.InvocationTargetException
     [java]     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     [java]     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
     [java]     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     [java]     at java.lang.reflect.Method.invoke(Method.java:498)
     [java]     at sun.reflect.misc.Trampoline.invoke(MethodUtil.java:71)
     [java]     at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
     [java]     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     [java]     at java.lang.reflect.Method.invoke(Method.java:498)
     [java]     at sun.reflect.misc.MethodUtil.invoke(MethodUtil.java:275)
     [java]     at javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1771)
     [java]     ... 48 more
     [java] Caused by: java.lang.NoClassDefFoundError: org/apache/commons/collections4/ListValuedMap
     [java]     at school_system.Model.TblStudent.importDataFromFile(TblStudent.java:107)
     [java]     at school_system.Controllers.Controller_Main.handleImportDataButtonAction(Controller_Main.java:34)
     [java]     ... 58 more
     [java] Caused by: java.lang.ClassNotFoundException: org.apache.commons.collections4.ListValuedMap
     [java]     at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
     [java]     at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
     [java]     at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:349)
     [java]     at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
     [java]     ... 60 more

I am guessing you are using the wrong import for the xlsx file type. — SedJ601
– SedJ601, Commented Jul 5, 2019 at 14:03
@AxelRichter, I have corrected my JAR files version. All from poi-4.1.0 zip file. But the problem still remained. — Ali Majed HA
– Ali Majed HA, Commented Jul 5, 2019 at 14:04
@Sedrick Documents say that for Excel 2007 and later I have to use XSSF imports. — Ali Majed HA
– Ali Majed HA, Commented Jul 5, 2019 at 14:06
Also, take the time and learn Java naming conventions. This can be really important when writing coding using JavaFX. — SedJ601
– SedJ601, Commented Jul 5, 2019 at 14:09
The fundamental problem is Caused by: java.lang.ClassNotFoundException: org.apache.commons.collections4.ListValuedMap. Based on the package name, you appear to be missing the Apache Commons Collections dependency—which is probably a transitive dependency of Apache POI. How are you handling dependency management? Maven? Gradle? — Slaw
– Slaw, Commented Jul 5, 2019 at 14:12

SedJ601 · Accepted Answer · 2024-04-11 21:10:11Z

Here is a utility I created to read EXCEL files. Hopefully, it can help you. The Main method is just for testing purposes and can be removed.

Code use

List<List<String>> excelData = ExcelFileUtility.readExcelFile("fileName.xlsx");

Full code:

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.Cell;
import static org.apache.poi.ss.usermodel.CellType.BLANK;
import static org.apache.poi.ss.usermodel.CellType.BOOLEAN;
import static org.apache.poi.ss.usermodel.CellType.FORMULA;
import static org.apache.poi.ss.usermodel.CellType.NUMERIC;
import static org.apache.poi.ss.usermodel.CellType.STRING;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 *
 * @author blj0011
 */
public class ExcelFileUtility
{

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args)
    {
        readExcelFileWithHeaderFirstRow("Copy of Database_Details_Report_AAA.xlsx", false).forEach((t) -> {
            t.forEach((z) -> {
                System.out.print(z + " - ");
            });

            System.out.println();
        });
    }

    public static List<List<String>> readExcelFile(String fileName)
    {
        List<List<String>> excelContent = new ArrayList();
        int columnCounter = 0;
        int rowCounter = 0;

        try (InputStream inputStream = new FileInputStream(fileName)) {
            DataFormatter formatter = new DataFormatter();

            Workbook workbook = WorkbookFactory.create(inputStream);
            Sheet sheet = workbook.getSheetAt(0);

            for (Row row : sheet) {
                List<String> tempList = new ArrayList();

                for (Cell cell : row) {
                    String text = formatter.formatCellValue(cell);
                    System.out.print(++columnCounter + ": " + text);
                    System.out.println(text.length());
                    tempList.add(text);
                }
                columnCounter = 0;
                excelContent.add(tempList);

                ++rowCounter;
                //Used for testing
                //if (rowCounter == 5) {
                //    break;
                //}
                //System.out.println(String.join(" - ", tempList));
                //System.out.println(tempList.size());

            }
        }
        catch (IOException | EncryptedDocumentException ex) {
            System.out.println(ex.toString());
        }

        return excelContent;
    }

    public static List<List<String>> readExcelFileWithHeaderFirstRow(String fileName, boolean allCellHaveData)
    {
        List<List<String>> sheetData = new ArrayList();
        int headerCount = 0;

        try (FileInputStream fis = new FileInputStream(fileName);
             Workbook wb = WorkbookFactory.create(fis)) {

            Sheet sheet = wb.getSheetAt(0);
            for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
                Row row = sheet.getRow(i);
                String value;

                List<String> rowData = new ArrayList();
                //System.out.println("\nROW " + row.getRowNum() + " has " + row.getPhysicalNumberOfCells() + " cell(s).");
                //System.out.println("last cell number: " + row.getLastCellNum());
                if (i == 0) {
                    headerCount = row.getLastCellNum();
                }

                for (int c = 0; c < row.getLastCellNum(); c++) {

                    Cell cell = row.getCell(c);

                    if (cell != null) {
                        switch (cell.getCellType()) {
                            case FORMULA:
                                value = "FORMULA value=" + cell.getCellFormula();
                                rowData.add(cell.getCellFormula());
                                break;

                            case NUMERIC:
                                value = "NUMERIC value=" + cell.getNumericCellValue();
                                rowData.add(Double.toString(cell.getNumericCellValue()));
                                break;

                            case STRING:
                                value = "STRING value=" + cell.getStringCellValue();
                                rowData.add(cell.getStringCellValue());
                                break;

                            case BLANK:
                                value = "<BLANK>";
                                rowData.add("");
                                break;

                            case BOOLEAN:
                                value = "BOOLEAN value-" + cell.getBooleanCellValue();
                                rowData.add(cell.getBooleanCellValue() ? "true" : "false");
                                break;

                            case ERROR:
                                value = "ERROR value=" + cell.getErrorCellValue();
                                rowData.add(Byte.toString(cell.getErrorCellValue()));
                                break;

                            default:
                                value = "UNKNOWN value of type " + cell.getCellType();
                                rowData.add(cell.getCellType().toString());
                        }
                        //System.out.println("CELL col=" + cell.getColumnIndex() + " VALUE=" + value);
                    }
                    else {
                        //System.out.println("CELL col=" + " VALUE=" + "<My Blank>");
                        rowData.add("<My Blank>");
                    }
                }

                if (!allCellHaveData) {
                    int currentRowCount = row.getLastCellNum();
                    while (currentRowCount < headerCount) {
                        rowData.add("<Blank end column>");
                        currentRowCount++;
                    }
                }
                sheetData.add(rowData);
            }
        }
        catch (FileNotFoundException ex) {
            System.out.println(ex);
        }
        catch (IOException ex) {
            System.out.println(ex);
        }

        return sheetData;
    }
}

Collectives™ on Stack Overflow

How to read data from Excel in JavaFX application?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related