core.files.reader.ReadExcelFile Maven / Gradle / Ivy
package core.files.reader;
import core.reports.TestReporter;
import org.apache.poi.ss.usermodel.*;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import static core.files.FileHelper.getFileAbsolutePath;
import static core.files.validator.FileExtensionValidator.verifyExcelFileType;
import static core.files.validator.FileStatusValidator.isFileExist;
import static core.files.validator.FileStatusValidator.verifyFileStatus;
/**
* Created by Ismail on 12/26/2017.
* This class contains all related methods for Read From
* Excel Sheet File and do actions or retrieve data
* from the file
*/
public class ReadExcelFile {
/*************** Class Methods Section ***************/
// This method retrieve all excel sheets from excel file
private static Workbook readExcelWorkbook(String excelFile) {
// Check if provided file name only without path then add test resources full path
if (!isFileExist(excelFile))
// build full path for excelSheet file
excelFile = getFileAbsolutePath(excelFile);
// Verify excelFile Status
verifyFileStatus(excelFile);
// Verify excelFile content Type
verifyExcelFileType(excelFile);
// Define then Initialize Workbook Object
Workbook workbook = null;
try {
// Read Excel file and retrieve all sheets and content
workbook = WorkbookFactory.create(new FileInputStream(excelFile));
} catch (IOException e) {
TestReporter.error("Unable to read provided excel sheet, Please check your excel path: " + excelFile, e, true);
}
return workbook;
}
//This method retrieve a specific sheet by sheet index number
private static Sheet getExcelSheetByName(String excelFile, String sheetName) {
try {
return readExcelWorkbook(excelFile).getSheet(sheetName);
} catch (Throwable throwable) {
TestReporter.error("Please check if you provided existing sheet name, You provided sheet name: " + sheetName, throwable, true);
return null;
}
}
//This method retrieve a specific sheet by sheet index number
private static Sheet getExcelSheetByIndex(String excelFile, int sheetNo) {
try {
// Check if sheetNo is zero
sheetNo = sheetNo > 0 ? sheetNo : 1;
// Get sheet name
String sheetName = readExcelWorkbook(excelFile).getSheetAt(sheetNo - 1).getSheetName();
return getExcelSheetByName(excelFile, sheetName);
} catch (Throwable throwable) {
TestReporter.error("Please check if you provided existing sheet number, You provided No: " + sheetNo, throwable, true);
return null;
}
}
// This method retrieve specific cell value from specific Row
private static String getCellValue(Cell cell) {
// Extract Cell value from Cell object
switch (cell.getCellType()) {
case _NONE: {
return "";
}
case BLANK: {
return "";
}
case ERROR: {
return "";
}
case STRING: {
return cell.getStringCellValue();
}
case BOOLEAN: {
return String.valueOf(cell.getBooleanCellValue());
}
case FORMULA: {
return "";
}
case NUMERIC: {
return String.valueOf(cell.getNumericCellValue());
}
default:
return "";
}
}
// This method retrieves a Row Object
private static Row getRowObject(Sheet sheet, int rowNo) {
try {
// Check if rowNo is zero
rowNo = rowNo > 0 ? rowNo : 1;
return sheet.getRow(rowNo - 1);
} catch (Throwable throwable) {
TestReporter.error("Please check if you provided exist row number, You provided row No: " + rowNo, throwable, true);
return null;
}
}
// This method retrieves Row Values
private static ArrayList getRowData(Row row) {
// Check if row object isn't null
if (row == null)
TestReporter.error("You provided Row Object with null value in getRowData method.", true);
// Get each cell value and save them to Array
ArrayList rowData = new ArrayList<>();
for (Cell cell : row) {
String cellValue = getCellValue(cell) != null ? !getCellValue(cell).isEmpty() ? getCellValue(cell) : "" : "";
if (!cellValue.isEmpty())
rowData.add(cellValue);
}
return rowData;
}
// This method retrieves Column Values
private static ArrayList getColumnData(ArrayList> rows, int columnNo) {
// Define column values list
ArrayList columnValues = new ArrayList<>();
// Check if column index is zero
columnNo = columnNo > 0 ? columnNo : 1;
for (ArrayList row : rows) {
// Check if cell values exists in the row
String cellValue = row.size() >= columnNo ? row.get(columnNo - 1) : "";
columnValues.add(cellValue);
}
// Remove header info from values
columnValues.remove(0);
return columnValues;
}
// This method retrieves all Rows in specific sheet from sheet object
private static ArrayList> getSheetData(Sheet sheet) {
// Define sheet Rows as Iterator
Iterator allRows = sheet.rowIterator();
// Define rowsData variable to save data of each Row
ArrayList> rowsData = new ArrayList<>();
// Retrieve all data of each row and save as item inside rowsData
while (allRows.hasNext()) {
// Save current row data
Row row = allRows.next();
ArrayList rowData = getRowData(row);
// Retrieve values of row
if (rowData.size() > 0)
rowsData.add(rowData);
}
return rowsData;
}
// This method retrieves number of sheets in excel file
public static int getNumberOfSheets(String excelFile) {
return readExcelWorkbook(excelFile).getNumberOfSheets();
}
// This method retrieves number of specific sheet in excel file
public static int getSheetNumber(String excelFile, String sheetName) {
return readExcelWorkbook(excelFile).getSheetIndex(sheetName) + 1;
}
// This method retrieves sheet name in excel file
public static String getSheetName(String excelFile, int sheetNo) {
// Check if sheetNo is zero
sheetNo = sheetNo > 0 ? sheetNo : 1;
return readExcelWorkbook(excelFile).getSheetName(sheetNo - 1);
}
// This method retrieve all Rows in specific excel sheet number
public static ArrayList> getSheetData(String excelFile, int sheetNo) {
// Retrieve all Sheet Data
return getSheetData(getExcelSheetByIndex(excelFile, sheetNo));
}
// This method retrieves all Rows in specific excel sheet name
public static ArrayList> getSheetData(String excelFile, String sheetName) {
// Retrieve all Sheet Data
return getSheetData(getExcelSheetByName(excelFile, sheetName));
}
// This method retrieve all Rows in first excel sheet
public static ArrayList> getSheetData(String excelFile) {
return getSheetData(excelFile, 1);
}
// This method retrieves row data with file, sheet name and row index parameter
public static ArrayList getRowData(String excelFile, String sheetName, int rowNo) {
// Get Row from the sheet
Row row = getRowObject(getExcelSheetByName(excelFile, sheetName), rowNo);
return getRowData(row);
}
// This method retrieves row data with file, sheet no and row index parameter
public static ArrayList getRowData(String excelFile, int sheetNo, int rowNo) {
// Get Row from the sheet
Row row = getRowObject(getExcelSheetByIndex(excelFile, sheetNo), rowNo);
return getRowData(row);
}
// This method retrieves row data with file and row index parameter
public static ArrayList getRowData(String excelFile, int rowNo) {
// Get Row from the sheet
Row row = getRowObject(getExcelSheetByIndex(excelFile, 1), rowNo);
if (row == null)
TestReporter.error("Please check Row number you provided isn't exist, Row No: " + rowNo, true);
return getRowData(row);
}
// This method retrieves column values with file, sheet name and column index
public static ArrayList getColumnData(String excelFile, String sheetName, int columnNo) {
// Retrieve all sheet data rows
ArrayList> rows = getSheetData(excelFile, sheetName);
// Retrieve column values
return getColumnData(rows, columnNo);
}
// This method retrieves column values with file, sheet number and column index
public static ArrayList getColumnData(String excelFile, int sheetNo, int columnNo) {
// Retrieve all sheet data rows
ArrayList> rows = getSheetData(excelFile, sheetNo);
// Retrieve column values
return getColumnData(rows, columnNo);
}
// This method retrieves column values with file, sheet name and column header
public static ArrayList getColumnData(String excelFile, String sheetName, String headerName) {
// Retrieve all sheet data rows
ArrayList> rows = getSheetData(excelFile, sheetName);
// Extract column index from header name
int columnNo = rows.get(0).indexOf(headerName) + 1;
// Retrieve column values
return getColumnData(rows, columnNo);
}
// This method retrieves column values with file, sheet number and column header
public static ArrayList getColumnData(String excelFile, int sheetNo, String headerName) {
// Retrieve all sheet data rows
ArrayList> rows = getSheetData(excelFile, sheetNo);
// Extract column index from header name
int columnNo = rows.get(0).indexOf(headerName) + 1;
// Check if column number is zero(that means the provided header name isn't exist)
if (columnNo == 0)
TestReporter.error("Please check excel header name you provided isn't exist, You provided: " + headerName, true);
// Retrieve column values
return getColumnData(rows, columnNo);
}
// This method retrieves all rows that contains a specific String
public static ArrayList> getRowsInclude(String excelFile, String sheetName, String containsString) {
// Retrieve all sheet data rows
ArrayList> rows = getSheetData(excelFile, sheetName);
// Extract all rows contains a specific String
ArrayList> rowsContains = new ArrayList<>();
for (ArrayList row : rows) {
for (String cell : row) {
if (cell.contains(containsString))
rowsContains.add(row);
}
}
return rowsContains;
}
// This method retrieves all rows that contains a specific String
public static ArrayList> getRowsInclude(String excelFile, int sheetNo, String containsString) {
// Retrieve all sheet data rows
ArrayList> rows = getSheetData(excelFile, sheetNo);
// Extract all rows contains a specific String
ArrayList> rowsContains = new ArrayList<>();
for (ArrayList row : rows) {
for (String cell : row) {
if (cell.contains(containsString))
rowsContains.add(row);
}
}
return rowsContains;
}
// This method retrieves columns data with file, sheet number and columns indexes
public static ArrayList> getColumnsData(String excelFile, int sheetNo, int... columnsNo) {
// Define list to save all columns values
ArrayList> columnsValues = new ArrayList<>();
// Iterate over all columns Numbers to get all data from them
for (int columnNo : columnsNo) {
// Retrieve all data by header name with getColumnData method
columnsValues.add(getColumnData(excelFile, sheetNo, columnNo));
}
return columnsValues;
}
// This method retrieves columns data with file, sheet name and columns indexes
public static ArrayList> getColumnsData(String excelFile, String sheetName, int... columnsNo) {
// Define list to save all columns values
ArrayList> columnsValues = new ArrayList<>();
// Iterate over all columns Numbers to get all data from them
for (int columnNo : columnsNo) {
// Retrieve all data by header name with getColumnData method
columnsValues.add(getColumnData(excelFile, sheetName, columnNo));
}
return columnsValues;
}
// This method retrieves columns data with file, sheet number and columns headers
public static ArrayList> getColumnsData(String excelFile, int sheetNo, String... headerNames) {
// Define list to save all columns values
ArrayList> columnsValues = new ArrayList<>();
// Iterate over all columns Numbers to get all data from them
for (String headerName : headerNames) {
// Retrieve all data by header name with getColumnData method
columnsValues.add(getColumnData(excelFile, sheetNo, headerName));
}
return columnsValues;
}
// This method retrieves columns data with file, sheet name and columns headers
public static ArrayList> getColumnsData(String excelFile, String sheetName, String... headerNames) {
// Define list to save all columns values
ArrayList> columnsValues = new ArrayList<>();
// Iterate over all columns Numbers to get all data from them
for (String headerName : headerNames) {
// Retrieve all data by header name with getColumnData method
columnsValues.add(getColumnData(excelFile, sheetName, headerName));
}
return columnsValues;
}
}
© 2015 - 2024 Weber Informatics LLC | Privacy Policy