/**
* @author:Soumya.D
*
* Contains Code related to excel sheet
*/
package com.hr.generic;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import com.hr.automation.utilities.ReadExcelFile;
import config.Constants;
public class ExcelUtils extends DriverScript{
private static HSSFRow ExcelRow;
private static HSSFWorkbook ExcelWBook;
private static HSSFSheet ExcelWSheet;
private static org.apache.poi.hssf.usermodel.HSSFCell ExcelCell;
public static void setExcelFile(String Path) throws Exception
{
try
{
FileInputStream ExcelFile = new FileInputStream(Path);
ExcelWBook = new HSSFWorkbook(ExcelFile);
}
catch (Exception e)
{
e.printStackTrace();
Log.error("Class Utils | Method setExcelFile | Exception desc : "+e.getMessage());
DriverScript.bResult = false;
}
}
public static int getRowCount(String SheetName){
int iNumber=0;
try {
ExcelWSheet = ExcelWBook.getSheet(SheetName);
iNumber=ExcelWSheet.getPhysicalNumberOfRows();
//cell.setCellType(Cell.CELL_TYPE_STRING);
//ExcelWSheet.getRow(iNumber).getCell(0).setCellType(Cell.CELL_TYPE_STRING);
//ExcelWSheet.getRow(iNumber).getCell(0).setCellValue(true);
} catch (Exception e){
e.printStackTrace();
Log.error("Class Utils | Method getRowCount | Exception desc : "+e.getMessage());
DriverScript.bResult = false;
}
return iNumber;
}
public static String getCellData(int RowNum, int ColNum, String SheetName ) throws Exception{
try{
ExcelWSheet = ExcelWBook.getSheet(SheetName);
ExcelCell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
String CellData = ExcelCell.getStringCellValue();
return CellData;
}catch (Exception e){
e.printStackTrace();
Log.error("Class Utils | Method getCellData | Exception desc : "+e.getMessage());
DriverScript.bResult = false;
return"";
}
}
public static int getRowContains(String sTestCaseName, int colNum,String SheetName) throws Exception{
int iRowNum=0;
try {
//ExcelWSheet = ExcelWBook.getSheet(SheetName);
int rowCount = ExcelUtils.getRowCount(SheetName);
for (; iRowNum<rowCount; iRowNum++){
if (ExcelUtils.getCellData(iRowNum,colNum,SheetName).equalsIgnoreCase(sTestCaseName)){
break;
}
}
} catch (Exception e){
e.printStackTrace();
Log.error("Class Utils | Method getRowContains | Exception desc : "+e.getMessage());
DriverScript.bResult = false;
}
return iRowNum;
}
public static int getTestStepsCount(String SheetName, String sTestCaseID, int iTestCaseStart) throws Exception{
try {
for(int i=iTestCaseStart;i<ExcelUtils.getRowCount(SheetName);i++){
if(!sTestCaseID.equals(ExcelUtils.getCellData(i, Constants.Col_TestCaseID, SheetName))){
int number = i;
return number;
}
}
ExcelWSheet = ExcelWBook.getSheet(SheetName);
int number=ExcelWSheet.getLastRowNum()+1;
return number;
} catch (Exception e){
e.printStackTrace();
Log.error("Class Utils | Method getRowContains | Exception desc : "+e.getMessage());
DriverScript.bResult = false;
return 0;
}
}
public static boolean fileCopy(String sSource,String sDest)throws Exception
{
try
{
ReadExcelFile readTestCase=new ReadExcelFile();
List<Map<String, String>> testStepsList = new ArrayList<Map<String, String>>();
FileInputStream fileInputStreamSource = new FileInputStream(sSource);
HSSFWorkbook workbookSource = new HSSFWorkbook(fileInputStreamSource);
int iNumOfSheetsDest=workbookSource.getNumberOfSheets();
String sSourceSheetName="";
HSSFWorkbook workbookDest=new HSSFWorkbook();
HSSFSheet sheetDest=null;
for(int iSheets=0;iSheets<iNumOfSheetsDest;iSheets++)
{
sSourceSheetName= workbookSource.getSheetName(iSheets);
// sSourceSheetName= workbookSource.getSheetName(1);
//By Sadguna:Added extra condition to copy only the test cases and steps sheets
if(sSourceSheetName.contains("Cases") || sSourceSheetName.contains("Steps"))
{
FileOutputStream fileOutputStreamDest = new FileOutputStream(new File(sDest));
sheetDest= workbookDest.createSheet(sSourceSheetName);
testStepsList=readTestCase.copyExcelRecords(sSource,sSourceSheetName);
//for header
if(testStepsList.equals(null))
{
Log.error("Class Utils | Method fileCopy | Exception desc : getExcelRecords returns null");
fileOutputStreamDest.close();
DriverScript.bResult = false;
return false;
}
Row row =null;
Map<String, String> rowHeader ; Map<String, String> rowData ;
int coloumnindex=0;
if(sSourceSheetName.contains("Steps"))
{
coloumnindex=0;
rowHeader = testStepsList.get(coloumnindex);
row = sheetDest.createRow(coloumnindex);
/*List<String> keys = new ArrayList<String>(rowHeader.keySet());
for(int i=0;i<keys.size();i++)
{
Cell cell = row.createCell(i);
cell.setCellValue(keys.get(i));
}*///Doesnt work as the order in which values are put in hashmap is not the same as when they are retreived.
int i=0;
while(i<rowHeader.size())
{
Cell cell = row.createCell(i);
switch (i) {
case 0:
cell.setCellValue("Test Case ID");
break;
case 1:
cell.setCellValue("TS_ID");
break;
case 2:
cell.setCellValue("Steps");
break;
case 3:
cell.setCellValue("ActionKeyword");
break;
case 4:
cell.setCellValue("Object");
break;
case 5:
cell.setCellValue("Testdata");
break;
case 6:
cell.setCellValue("RESULT");
break;
case 7:
cell.setCellValue("ScreenShotOnFAILURE");
break;
case 8:
cell.setCellValue("Comments");
break;
case 9:
cell.setCellValue("DynamicValueName");
break;
default:
break;
}
i++;
}
}
else if(sSourceSheetName.contains("Cases"))
{
coloumnindex=0;
rowHeader = testStepsList.get(coloumnindex);
row = sheetDest.createRow(coloumnindex);
int i=0;
while(i<rowHeader.size())
{
Cell cell = row.createCell(i);
switch (i) {
case 0:
cell.setCellValue("Test Case ID");
break;
case 1:
cell.setCellValue("TC_Name");
break;
case 2:
cell.setCellValue("Runmode");
break;
case 3:
cell.setCellValue("Status");
break;
//comment below
//case 4:
//cell.setCellValue("Environment");
//break;
case 4:
cell.setCellValue("StartTime");
break;
case 5:
cell.setCellValue("EndTime");
break;
default:
break;
}
i++;
}
}
coloumnindex=0;
int cellIndex = 0;
for (int index = 0; index < testStepsList.size(); index++)
{
rowData = testStepsList.get(index);
row = sheetDest.createRow(index+1);
for (String columnName : rowData.keySet())
{
//System.out.println("columnName "+columnName);
Cell cell = row.createCell(cellIndex);
try{
String cellText=sheetDest.getRow(0).getCell(cellIndex).getStringCellValue();
cell.setCellValue(rowData.get(cellText));
}
catch(Exception e)
{
e.printStackTrace();
}
//System.out.println("cell "+cell);
cellIndex++;
}
cellIndex=0;
}
workbookDest.write(fileOutputStreamDest);
fileOutputStreamDest.flush();
coloumnindex=0;
}
}
}
catch(Exception e){
e.printStackTrace();
Log.error("Class Utils | Method fileCopy | Exception desc : "+e.getMessage());
DriverScript.bResult = false;
return false;
}
return true;
}
@SuppressWarnings("static-access")
public static void setCellData(String Result, int RowNum, int ColNum, String SheetName) throws Exception {
try{
ExcelWSheet = ExcelWBook.getSheet(SheetName);
ExcelRow = ExcelWSheet.getRow(RowNum);
ExcelCell = ExcelRow.getCell(ColNum, ExcelRow.RETURN_BLANK_AS_NULL);
if (ExcelCell == null) {
ExcelCell = ExcelRow.createCell(ColNum);
ExcelCell.setCellValue(Result);
} else {
ExcelCell.setCellValue(Result);
}
FileOutputStream fileOut = new FileOutputStream(configProps.getProperty("DestPath"));
ExcelWBook.write(fileOut);
fileOut.close();
}catch(Exception e){
e.printStackTrace();
DriverScript.bResult = false;
}
}
}
Monday, August 29, 2016
Excelutils
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment