package com.gemalto.mcx.groovy
import jxl.*
import jxl.write.*
import java.io.FileInputStream
import java.io.FileOutputStream
import java.io.FileNotFoundException
import java.io.IOException
import java.util.Date
import org.apache.poi.hssf.usermodel.HSSFCell
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.hssf.usermodel.HSSFCellStyle
import org.apache.poi.hssf.usermodel.HSSFDataFormat
import org.apache.poi.hssf.usermodel.*
class SoapUiExcel {
def log
def testRunner
public SoapUiExcel(){
}
public SoapUiExcel(log, testRunner) {
this.log = log
this.testRunner = testRunner
}
/*
* Read an Excel worksheet and get the Project Name
* Row8,CellB
*/
def Excel_ProjectName_Reader(pathExcelRunner){
log.info "In Excel_ProjectName_Reader()"
log.info "Opening " + pathExcelRunner
// change these to point at the SoapUi Project Name i.e. "GMPP-Success"
def projNameRow = 7
def projNameCol = 1
//open xls workbook, get a worksheet
//define vars to re-use and set to null
FileInputStream file = new FileInputStream(pathExcelRunner);
HSSFWorkbook workbook = new HSSFWorkbook(file)
/*
*
* Change this getSheetByName after confirming all is working
*
*
*/
HSSFSheet sheet = workbook.getSheetAt(0)
HSSFRow row = null
HSSFCell cell = null
// Row8,ColB is Project Name, used to open a Project in the current workspace
cell = sheet.getRow(projNameRow).getCell(projNameCol)
// Get the Project Name from the cell value
def projectName = cell.getStringCellValue();
log.info "GOT ProjectName FROM: ROW: ${projNameRow}, CELL: ${projNameCol}, PROJECT: ${projectName}"
// close the xls workbook
file.close()
// return the project name
return projectName
}
/*
* Create new workbook with test updates
*
def Excel_TestSuite_StatusUpdate(pathExcelRunner){
log.info "In Excel_TestSuite_Reader()"
log.info "Opening " + pathExcelRunner
FileInputStream file = new FileInputStream(pathExcelRunner);
HSSFWorkbook workbook = new HSSFWorkbook(file)
HSSFSheet sheet = workbook.getSheetAt(0)
HSSFRow row = null
HSSFCell cell = null
String val = null
def testList = []
}
*/
/*
* Reads from Excel2003 file and pulls TestSuite names to run
*/
def Excel_TestSuite_Reader(pathExcelRunner){
log.info "In Excel_TestSuite_Reader()"
log.info "OPEN: " + pathExcelRunner
FileInputStream file = new FileInputStream(pathExcelRunner);
HSSFWorkbook workbook = new HSSFWorkbook(file)
HSSFSheet sheet = workbook.getSheetAt(0)
HSSFRow row = null
HSSFCell cell = null
String val = null
def testList = []
/*
* Iterate through a specified set of rows
* Pull TestSuite name and add to a list ( String Array )
* If the cell is blank, don't add it to the list
*/
for (int i=12; i<26; i++){
cell = sheet.getRow(i).getCell(5)
val = cell.getStringCellValue();
if (val == "TestsEnd") break
if(val != ""){
log.info "GOT ROW:${i} --- CELL:${val} ---"
testList.add(val)
}
}
/*
* Logging list of TestSuites by iterating the list
*/
log.info "Test Suite Listing: "
for ( thisTestSuite in testList ){
log.info "TS: " + thisTestSuite
}
return testList
}
/*
* ==========================================================================
* Practice Methods NOT USED START ============================================
* ==========================================================================
*/
/*
* reads from a file, updates cell text, then outputs to new file
*/
def Excel_Test_Result2(){
FileInputStream file = new FileInputStream("T://output7.xls");
// Might use this var to output to xls file immediately
// So far, must copy to memory as InputStream, do work, then output the whole worksheet to file
// FileOutputStream fileOut = new FileOutputStream("T://output7.xls")
HSSFWorkbook workbook = new HSSFWorkbook(file)
HSSFSheet sheet = workbook.getSheetAt(0)
HSSFCell cell = null
// Green
HSSFCellStyle cellStyleG = workbook.createCellStyle()
cellStyleG.setFillForegroundColor(HSSFColor.GREEN.index)
cellStyleG.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
// Red
HSSFCellStyle cellStyleR = workbook.createCellStyle()
cellStyleR.setFillForegroundColor(HSSFColor.RED.index)
cellStyleR.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
//Yellow
HSSFCellStyle cellStyleY = workbook.createCellStyle()
cellStyleY.setFillForegroundColor(HSSFColor.YELLOW.index)
cellStyleY.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
//Update the value of cell
for (int i=5; i<15; i++){
cell = sheet.getRow(i).getCell(5)
cell.setCellValue("PASS")
cell.setCellStyle(cellStyleG)
}
cell = sheet.getRow(5).getCell(5)
cell.setCellValue("PASS")
cell.setCellStyle(cellStyleG)
cell = sheet.getRow(6).getCell(5)
cell.setCellValue("FAIL")
cell.setCellStyle(cellStyleR)
cell = sheet.getRow(7).getCell(5)
cell.setCellValue("NOTRUN")
cell.setCellStyle(cellStyleY)
// Close File then Open and Write to Output
file.close()
FileOutputStream outFile =new FileOutputStream(new File("T://output8.xls"))
workbook.write(outFile)
outFile.flush()
outFile.close()
}
def Excel_Read(){
try {
FileInputStream fileInputStream = new FileInputStream("T://Temp//mcx_d1_test.xls");
HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
HSSFSheet worksheet = workbook.getSheet("POI Worksheet");
for(def i=0; i<4; i++){
HSSFRow row = worksheet.getRow(i);
HSSFCell cellA = row.getCell((short) 0);
String aVal = cellA.getStringCellValue();
HSSFCell cellB = row.getCell((short) 1);
String bVal = cellB.getStringCellValue();
HSSFCell cellC = row.getCell((short) 2);
boolean cVal = cellC.getBooleanCellValue();
HSSFCell cellD = row.getCell((short) 3);
Date dVal = cellD.getDateCellValue();
System.out.println("A${i+1}: " + aVal);
System.out.println("B${i+1}: " + bVal);
System.out.println("C${i+1}: " + cVal);
System.out.println("D${i+1}: " + dVal);
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
def Excel_Write(){
try {
FileOutputStream fileOut = new FileOutputStream("T://Temp//mcx_d1_test2.xls");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet worksheet = workbook.createSheet("POI Worksheet");
// index from 0,0... cell A1 is cell(0,0)
HSSFRow row1 = worksheet.createRow((short) 0);
HSSFCell cellA1 = row1.createCell((short) 0);
cellA1.setCellValue("Hello");
HSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellA1.setCellStyle(cellStyle);
HSSFCell cellB1 = row1.createCell((short) 1);
cellB1.setCellValue("Goodbye");
cellStyle = workbook.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellB1.setCellStyle(cellStyle);
HSSFCell cellC1 = row1.createCell((short) 2);
cellC1.setCellValue(true);
HSSFCell cellD1 = row1.createCell((short) 3);
cellD1.setCellValue(new Date());
cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(HSSFDataFormat
.getBuiltinFormat("m/d/yy h:mm"));
cellD1.setCellStyle(cellStyle);
workbook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
def Excel_MCX1_Read(){
try {
FileInputStream fileInputStream = new FileInputStream("T://Temp//mcx_d1_test.xls");
HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
HSSFSheet worksheet = workbook.getSheet("Merchants");
for(def i=11; i<18; i++){
HSSFRow row = worksheet.getRow(i);
HSSFCell cellA = row.getCell((short) 3);
String aVal = cellA.getStringCellValue();
HSSFCell cellB = row.getCell((short) 4);
String bVal = cellB.getStringCellValue();
HSSFCell cellC = row.getCell((short) 5);
String cVal = cellC.getStringCellValue();
//HSSFCell cellD = row.getCell((short) 6);
//String dVal = cellD.getStringCellValue();
HSSFCell cellE = row.getCell((short) 7);
String eVal = cellE.getStringCellValue();
HSSFCell cellF = row.getCell((short) 8);
String fVal = cellF.getStringCellValue();
/* HSSFCell cellG = row.getCell((short) 9);
String gVal = cellG.getStringCellValue();*/
HSSFCell cellH = row.getCell((short) 10);
String hVal = cellH.getStringCellValue();
log.info "=============================================="
log.info "A: " + aVal
log.info "B: " + bVal
log.info "C: " + cVal
//log.info"D: " + dVal);
log.info "E: " + eVal
log.info "F: " + fVal
/* log.info "G: " + gVal);*/
log.info "H: " + hVal
log.info "=============================================="
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
def Excel_Result(){
/* Creates a workbook and a worksheet*/
/* WritableWorkbook workbook = Workbook.createWorkbook(new File("t:\\output.xls"))
WritableSheet sheet = workbook.createSheet("Results", 0)
log.info(sheet1.isHidden())
Label label = new Label(5, 6, "Pass"); //column=0=A,row=0=1
sheet.addCell(label);
Label label1 = new Label(5, 7, "Fail");
sheet.addCell(label1);
workbook.write()
workbook.close()
*/ }
def Excel_Test_Result(){
FileInputStream file = new FileInputStream("T://output7.xls");
// FileOutputStream fileOut = new FileOutputStream("T://output7.xls")
HSSFWorkbook workbook = new HSSFWorkbook(file)
HSSFSheet sheet = workbook.getSheetAt(0)
HSSFCell cell = null
// Green
HSSFCellStyle cellStyleG = workbook.createCellStyle()
cellStyleG.setFillForegroundColor(HSSFColor.GREEN.index)
cellStyleG.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
// Red
HSSFCellStyle cellStyleR = workbook.createCellStyle()
cellStyleR.setFillForegroundColor(HSSFColor.RED.index)
cellStyleR.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
//Blue
HSSFCellStyle cellStyleB = workbook.createCellStyle()
cellStyleB.setFillForegroundColor(HSSFColor.YELLOW.index)
cellStyleB.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
//Update the value of cell
cell = sheet.getRow(5).getCell(5)
cell.setCellValue("PASS")
cell.setCellStyle(cellStyleG)
cell = sheet.getRow(6).getCell(5)
cell.setCellValue("FAIL")
cell.setCellStyle(cellStyleR)
cell = sheet.getRow(7).getCell(5)
cell.setCellValue("NOTRUN")
cell.setCellStyle(cellStyleB)
// Close File then Open and Write to Output
file.close()
FileOutputStream outFile =new FileOutputStream(new File("T://output8.xls"))
workbook.write(outFile)
outFile.flush()
outFile.close()
}
/*
* JXL Methods
*/
def XlWriter(){
/* EXAMPLE ONLY */
/* Creates a workbook and a worksheet*/
/*
WritableWorkbook workbook = Workbook.createWorkbook(new File("d:\\output.xls"))
WritableSheet sheet = workbook.createSheet("Worksheet 1", 0)
log.info(sheet1.isHidden())
Label label = new Label(0, 2, "Test data in Column A, Row 3"); //column=0=A,row=0=1
sheet.addCell(label);
Label label1 = new Label(2, 2, "Column C, Row 3");
sheet.addCell(label1);
workbook.write()
workbook.close()
*/
}
def XlReader(){
/* EXAMPLE ONLY */
/* Opens a workbook and reads from a worksheet*/
/*
Workbook workbook1 = Workbook.getWorkbook(new File("d:\\output.xls"))
Sheet sheet1 = workbook1.getSheet(0)
Cell a1 = sheet1.getCell(0,2) // getCell(row,column) -- place some values in myfile.xls
Cell b2 = sheet1.getCell(2,2) // then those values will be acessed using a1, b2 & c3 Cell.
Cell c2 = sheet1.getCell(2,1)
log.info a1.getContents()
log.info b2.getContents()
log.info c2.getContents()
workbook1.close()
*/
}
/*
* ==========================================================================
* Practice Methods NOT USED END ============================================
* ==========================================================================
*/
}
