Monday, August 29, 2016

Excelutils

 /**  
  * @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;  
        }  
     }  
 }  

No comments:

Post a Comment