Extract ZIP and Verify Data in Excel while Automation

     I have a requirement to unzip and verify data in excel file while automation. Following code (especially the function extractZIPAndVerifyDataInExcel(String zipFilePath, String sheetName, String columnName,String valueToVerify)) will explain how to do unzip and verify the data in excel:

/**

 * Verify data in excel after unzip the file

 * 

 * @author sanojs

 * @since 30-August-2018

 * @param zipFilePath

 * @param sheetName

 * @param columnName

 * @param valueToVerify

 * @return

 * @throws IOException

 */

public static boolean extractZIPAndVerifyDataInExcel(String zipFilePath, String sheetName, String columnName,

String valueToVerify) throws IOException {

boolean dataFound = false;

ArrayList<String> list = new ArrayList<String>();

InputStream in = null;

XSSFWorkbook wb = null;

String filePath = unZip(zipFilePath, System.getProperty(“user.home”) + “\\Downloads\\”);

try {

if (filePath == null || filePath.trim().equals(“”))

System.out.println(“Excel file path missing”);

if (filePath.endsWith(“.xlsx”) || filePath.endsWith(“.xls”)) {

in = new FileInputStream(filePath);

wb = new XSSFWorkbook(in);

} else {

System.out.println(“Please pass valid Excel file path”);

}

int index = wb.getSheetIndex(sheetName);

if (index == -1)

System.out.println(“Invalid sheet name”);

XSSFSheet mySheet = wb.getSheetAt(index);

Iterator<Row> rowIter = mySheet.rowIterator();

Row row;

Cell cell;

int columnIndex = 0;

boolean hasColumn = false;

if (rowIter.hasNext()) {

row = (Row) rowIter.next();

Iterator<Cell> cellItreator = row.cellIterator();

while (cellItreator.hasNext()) {

Cell nextCell = cellItreator.next();

if (nextCell.getStringCellValue() != null && nextCell.getStringCellValue().equals(columnName)) {

hasColumn = true;

columnIndex = nextCell.getColumnIndex();

}

}

}

if (hasColumn) {

while (rowIter.hasNext()) {

row = (Row) rowIter.next();

cell = row.getCell(columnIndex);

if (cell != null) {

if (cell.getCellType() == Cell.CELL_TYPE_STRING)

list.add(cell.getStringCellValue());

else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

list.add(cell.getNumericCellValue() + “”);

} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA)

list.add(cell.getNumericCellValue() + “”);

else

list.add(cell.toString());

}

}

if (list.contains(valueToVerify)) {

dataFound = true;

}

} else

System.out.println(“Invalid Column name”);

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

wb.close();

in.close();

} catch (Exception e) {

}

}

return dataFound;

}

 /**

 * Extracts a zip file specified by the zipFilePath to a directory specified by

 * destDirectory (will be created if does not exists)

 * 

 * @author sanojs

 * @since 30-August-2018

 * @param zipFilePath

 * @param destDirectory

 * @throws IOException

 */

private static String unZip(String zipFilePath, String destDirectory) throws IOException {

String filePath = null;

File destDir = new File(destDirectory);

if (!destDir.exists()) {

destDir.mkdir();

}

ZipInputStream zipIn = new ZipInputStream(new FileInputStream(zipFilePath));

ZipEntry entry = zipIn.getNextEntry();

while (entry != null) {

filePath = destDirectory + File.separator + entry.getName();

if (!entry.isDirectory()) {

extractFile(zipIn, filePath);

} else {

File dir = new File(filePath);

dir.mkdir();

}

zipIn.closeEntry();

entry = zipIn.getNextEntry();

}

zipIn.close();

return filePath;

}

/**

 * Extracts a zip entry (file entry)

 * 

 * @author sanojs

 * @since 30-August-2018

 * @param zipIn

 * @param filePath

 * @throws IOException

 */

private static void extractFile(ZipInputStream zipIn, String filePath) throws IOException {

BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(filePath));

byte[] bytesIn = new byte[4096];

int read = 0;

while ((read = zipIn.read(bytesIn)) != -1) {

bos.write(bytesIn, 0, read);

}

bos.close();

}