Extract ZIP and Verify Data in Excel while Automation

vba_tricks_and-tips_excel_macro_to_unzip_files

     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();
}
Please try the above code snippet in your automation execution if  you have the same kind of requirement to verify the data in excel after extract the zip file.
make it perfect !

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s