eugenp · lor6 · Oct 15, 2024 · Aug 28, 2024 · Sep 8, 2024 · Sep 10, 2024
diff --git a/apache-poi-3/pom.xml b/apache-poi-3/pom.xml
@@ -71,7 +71,7 @@
         <poi-ooxml-schemas.version>4.1.2</poi-ooxml-schemas.version>
         <poiji.version>4.2.0</poiji.version>
         <xmlbeans.version>5.2.0</xmlbeans.version>
-        <fastexcel.version>0.17.0</fastexcel.version>
+        <fastexcel.version>0.18.3</fastexcel.version>
         <jxl.version>2.6.12</jxl.version>
     </properties>
 

diff --git a/apache-poi-3/src/main/java/com/baeldung/emptyrow/fastexcel/FastexcelHelper.java b/apache-poi-3/src/main/java/com/baeldung/emptyrow/fastexcel/FastexcelHelper.java
@@ -0,0 +1,18 @@
+package com.baeldung.emptyrow.fastexcel;
+
+import org.dhatim.fastexcel.reader.Cell;
+import org.dhatim.fastexcel.reader.Row;
+
+public class FastexcelHelper {
+    public boolean isRowEmpty(Row row) {
+        if (row == null) {
+            return true;
+        }
+        for (Cell cell : row) {
+            if (cell != null && !cell.getText().trim().isEmpty()) {
+                return false;
+            }
+        }
+        return true;
+    }
+}
diff --git a/apache-poi-3/src/main/java/com/baeldung/emptyrow/jexcel/JExcelHelper.java b/apache-poi-3/src/main/java/com/baeldung/emptyrow/jexcel/JExcelHelper.java
@@ -0,0 +1,18 @@
+package com.baeldung.emptyrow.jexcel;
+
+import jxl.Cell;
+
+public class JExcelHelper {
+
+    public boolean isRowEmpty(Cell[] row) {
+        if (row == null) {
+            return true;
+        }
+        for (Cell cell : row) {
+            if (cell != null && !cell.getContents().trim().isEmpty()) {
+                return false;
+            }
+        }
+        return true;
+    }
+}
diff --git a/apache-poi-3/src/main/java/com/baeldung/emptyrow/poi/PoiHelper.java b/apache-poi-3/src/main/java/com/baeldung/emptyrow/poi/PoiHelper.java
@@ -0,0 +1,53 @@
+package com.baeldung.emptyrow.poi;
+
+import java.io.FileInputStream;
+import java.io.IOException;
+import java.io.InputStream;
+import java.util.ArrayList;
+import java.util.Collections;
+import java.util.List;
+import java.util.stream.Collectors;
+import java.util.stream.StreamSupport;
+
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.openxml4j.exceptions.OLE2NotOfficeXmlFileException;
+import org.apache.poi.poifs.filesystem.NotOLE2FileException;
+import org.apache.poi.ss.usermodel.Cell;
+import org.apache.poi.ss.usermodel.CellType;
+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.xssf.usermodel.XSSFWorkbook;
+
+public class PoiHelper {
+
+    public Workbook openWorkbook(String filePath) throws IOException {
+        try (InputStream fileInputStream = new FileInputStream(filePath)) {
+            if (filePath.toLowerCase()
+              .endsWith("xlsx")) {
+                return new XSSFWorkbook(fileInputStream);
+            } else if (filePath.toLowerCase()
+              .endsWith("xls")) {
+                return new HSSFWorkbook(fileInputStream);
+            } else {
+                throw new IllegalArgumentException("The specified file is not an Excel file");
+            }
+        } catch (OLE2NotOfficeXmlFileException | NotOLE2FileException e) {
+            throw new IllegalArgumentException(
+              "The file format is not supported. Ensure the file is a valid Excel file.", e);
+        }
+    }
+
+    public boolean isRowEmpty(Row row) {
+        if (row == null) {
+            return true;
+        }
+        for (int cellNum = row.getFirstCellNum(); cellNum < row.getLastCellNum(); cellNum++) {
+            Cell cell = row.getCell(cellNum);
+            if (cell != null && cell.getCellType() != CellType.BLANK) {
+                return false;
+            }
+        }
+        return true;
+    }
+}
diff --git a/apache-poi-3/src/main/resources/empty_excel_file.xls b/apache-poi-3/src/main/resources/empty_excel_file.xls
diff --git a/apache-poi-3/src/main/resources/empty_excel_file.xlsx b/apache-poi-3/src/main/resources/empty_excel_file.xlsx
diff --git a/...-poi-3/src/test/java/com/baeldung/emptyrow/fastexcel/FastexcelDetectEmptyRowUnitTest.java b/...-poi-3/src/test/java/com/baeldung/emptyrow/fastexcel/FastexcelDetectEmptyRowUnitTest.java
@@ -0,0 +1,36 @@
+package com.baeldung.emptyrow.fastexcel;
+
+import static org.junit.Assert.assertTrue;
+
+import java.io.FileInputStream;
+import java.io.IOException;
+import java.util.stream.Stream;
+
+import org.dhatim.fastexcel.reader.ReadableWorkbook;
+import org.dhatim.fastexcel.reader.Row;
+import org.dhatim.fastexcel.reader.Sheet;
+import org.junit.Before;
+import org.junit.Test;
+
+
+public class FastexcelDetectEmptyRowUnitTest {
+
+    private FastexcelHelper fastexcelHelper;
+    private static final String EMPTY_FILE_PATH = "src/main/resources/empty_excel_file.xlsx";
+
+    @Before
+    public void loadExcelFile() throws IOException {
+        fastexcelHelper = new FastexcelHelper();
+    }
+
+    @Test
+    public void givenXLSXFile_whenParsingEmptyFastExcelFile_thenDetectAllRowsAreEmpty() throws IOException {
+        try (FileInputStream file = new FileInputStream(EMPTY_FILE_PATH); ReadableWorkbook wb = new ReadableWorkbook(file)) {
+            Sheet sheet = wb.getFirstSheet();
+            try (Stream<Row> rows = sheet.openStream()) {
+                boolean isEmpty = rows.allMatch(fastexcelHelper::isRowEmpty);
+                assertTrue(isEmpty);
+            }
+        }
+    }
+}
diff --git a/apache-poi-3/src/test/java/com/baeldung/emptyrow/jexcel/JExcelDetectEmptyRowUnitTest.java b/apache-poi-3/src/test/java/com/baeldung/emptyrow/jexcel/JExcelDetectEmptyRowUnitTest.java
@@ -0,0 +1,36 @@
+package com.baeldung.emptyrow.jexcel;
+
+import static org.junit.Assert.assertTrue;
+
+import java.io.File;
+import java.io.IOException;
+import org.junit.Before;
+import org.junit.Test;
+
+import jxl.Cell;
+import jxl.Sheet;
+import jxl.Workbook;
+import jxl.read.biff.BiffException;
+
+
+public class JExcelDetectEmptyRowUnitTest {
+
+    private JExcelHelper jexcelHelper;
+    private static final String EMPTY_FILE_PATH = "src/main/resources/empty_excel_file.xls";
+
+    @Before
+    public void loadExcelFile() throws IOException {
+        jexcelHelper = new JExcelHelper();
+    }
+
+    @Test
+    public void givenXLSFile_whenParsingJExcelFile_thenDetectAllRowsEmpty() throws IOException, BiffException {
+        Workbook workbook = Workbook.getWorkbook(new File(EMPTY_FILE_PATH));
+        Sheet sheet = workbook.getSheet(0);
+
+        for (int rowNum = 0; rowNum < sheet.getRows(); rowNum++) {
+            Cell[] row = sheet.getRow(rowNum);
+            assertTrue(jexcelHelper.isRowEmpty(row));
+        }
+    }
+}
diff --git a/apache-poi-3/src/test/java/com/baeldung/emptyrow/poi/PoiDetectEmptyRowUnitTest.java b/apache-poi-3/src/test/java/com/baeldung/emptyrow/poi/PoiDetectEmptyRowUnitTest.java
@@ -0,0 +1,44 @@
+package com.baeldung.emptyrow.poi;
+
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.junit.Before;
+import org.junit.Test;
+
+import java.io.IOException;
+import static org.junit.jupiter.api.Assertions.assertTrue;
+
+public class PoiDetectEmptyRowUnitTest {
+
+    private PoiHelper poiHelper;
+    private static final String XLS_EMPTY_FILE_PATH = "src/main/resources/empty_excel_file.xls";
+    private static final String XLSX_EMPTY_FILE_PATH = "src/main/resources/empty_excel_file.xlsx";
+
+    @Before
+    public void loadExcelFile() throws IOException {
+        poiHelper = new PoiHelper();
+    }
+
+    @Test
+    public void givenXLSXFile_whenParsingExcelFile_thenDetectAllRowsEmpty() throws IOException {
+        Workbook workbook = poiHelper.openWorkbook(XLSX_EMPTY_FILE_PATH);
+        Sheet sheet = workbook.getSheetAt(0);
+
+        for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
+            Row row = sheet.getRow(rowNum);
+            assertTrue(poiHelper.isRowEmpty(row));
+        }
+    }
+
+    @Test
+    public void givenXLSFile_whenParsingExcelFile_thenDetectAllRowsEmpty() throws IOException {
+        Workbook workbook = poiHelper.openWorkbook(XLS_EMPTY_FILE_PATH);
+        Sheet sheet = workbook.getSheetAt(0);
+
+        for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
+            Row row = sheet.getRow(rowNum);
+            assertTrue(poiHelper.isRowEmpty(row));
+        }
+    }
+}
diff --git a/apache-poi-3/src/test/java/com/baeldung/poi/columnnames/ExcelUtilsUnitTest.java b/apache-poi-3/src/test/java/com/baeldung/poi/columnnames/ExcelUtilsUnitTest.java
@@ -15,30 +15,32 @@ public class ExcelUtilsUnitTest {
     private static final String XLS_TEST_FILE_PATH = "src/main/resources/consumer_info.xls";
     private static final String XLSX_TEST_FILE_PATH = "src/main/resources/food_info.xlsx";
     private static final String SHEET_NAME = "Sheet1";
-@Test
-public void givenExcelFileWithXLSXFormat_whenGetColumnNames_thenReturnsColumnNames() throws IOException {
-    Workbook workbook = ExcelUtils.openWorkbook(XLSX_TEST_FILE_PATH);
-    Sheet sheet = ExcelUtils.getSheet(workbook, SHEET_NAME);
-    List<String> columnNames = ExcelUtils.getColumnNames(sheet);
-
-    assertEquals(4, columnNames.size());
-    assertTrue(columnNames.contains("Category"));
-    assertTrue(columnNames.contains("Name"));
-    assertTrue(columnNames.contains("Measure"));
-    assertTrue(columnNames.contains("Calories"));
-    workbook.close();
-}
-@Test
-public void givenExcelFileWithXLSFormat_whenGetColumnNames_thenReturnsColumnNames() throws IOException {
-    Workbook workbook = ExcelUtils.openWorkbook(XLS_TEST_FILE_PATH);
-    Sheet sheet = ExcelUtils.getSheet(workbook, SHEET_NAME);
-    List<String> columnNames = ExcelUtils.getColumnNames(sheet);
-
-    assertEquals(3, columnNames.size());
-    assertTrue(columnNames.contains("Name"));
-    assertTrue(columnNames.contains("Age"));
-    assertTrue(columnNames.contains("City"));
-
-    workbook.close();
-}
+
+    @Test
+    public void givenExcelFileWithXLSXFormat_whenGetColumnNames_thenReturnsColumnNames() throws IOException {
+        Workbook workbook = ExcelUtils.openWorkbook(XLSX_TEST_FILE_PATH);
+        Sheet sheet = ExcelUtils.getSheet(workbook, SHEET_NAME);
+        List<String> columnNames = ExcelUtils.getColumnNames(sheet);
+
+        assertEquals(4, columnNames.size());
+        assertTrue(columnNames.contains("Category"));
+        assertTrue(columnNames.contains("Name"));
+        assertTrue(columnNames.contains("Measure"));
+        assertTrue(columnNames.contains("Calories"));
+        workbook.close();
+    }
+
+    @Test
+    public void givenExcelFileWithXLSFormat_whenGetColumnNames_thenReturnsColumnNames() throws IOException {
+        Workbook workbook = ExcelUtils.openWorkbook(XLS_TEST_FILE_PATH);
+        Sheet sheet = ExcelUtils.getSheet(workbook, SHEET_NAME);
+        List<String> columnNames = ExcelUtils.getColumnNames(sheet);
+
+        assertEquals(3, columnNames.size());
+        assertTrue(columnNames.contains("Name"));
+        assertTrue(columnNames.contains("Age"));
+        assertTrue(columnNames.contains("City"));
+
+        workbook.close();
+    }
 }