diff --git a/apache-poi-3/pom.xml b/apache-poi-3/pom.xml index cf03efdb4d46..f6a2b374aadb 100644 --- a/apache-poi-3/pom.xml +++ b/apache-poi-3/pom.xml @@ -71,7 +71,7 @@ 4.1.2 4.2.0 5.2.0 - 0.17.0 + 0.18.3 2.6.12 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 new file mode 100644 index 000000000000..f941d9dd4cfc --- /dev/null +++ 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 new file mode 100644 index 000000000000..d091721a6a19 --- /dev/null +++ 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; + } +} \ No newline at end of file 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 new file mode 100644 index 000000000000..b1ef6dafb7cc --- /dev/null +++ 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 new file mode 100644 index 000000000000..d54aa330613a Binary files /dev/null and b/apache-poi-3/src/main/resources/empty_excel_file.xls differ diff --git a/apache-poi-3/src/main/resources/empty_excel_file.xlsx b/apache-poi-3/src/main/resources/empty_excel_file.xlsx new file mode 100644 index 000000000000..39110f8d0d8a Binary files /dev/null and b/apache-poi-3/src/main/resources/empty_excel_file.xlsx differ diff --git a/apache-poi-3/src/test/java/com/baeldung/emptyrow/fastexcel/FastexcelDetectEmptyRowUnitTest.java b/apache-poi-3/src/test/java/com/baeldung/emptyrow/fastexcel/FastexcelDetectEmptyRowUnitTest.java new file mode 100644 index 000000000000..5416089e6fec --- /dev/null +++ b/apache-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 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 new file mode 100644 index 000000000000..f7378a961c75 --- /dev/null +++ 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 new file mode 100644 index 000000000000..2609d3fa7a75 --- /dev/null +++ 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 index 0fa161f4d677..8661686c194c 100644 --- 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 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 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 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 columnNames = ExcelUtils.getColumnNames(sheet); + + assertEquals(3, columnNames.size()); + assertTrue(columnNames.contains("Name")); + assertTrue(columnNames.contains("Age")); + assertTrue(columnNames.contains("City")); + + workbook.close(); + } }