How to use getWorkbook method of com.testsigma.util.XLSUtil class

Best Testsigma code snippet using com.testsigma.util.XLSUtil.getWorkbook

Source:TestPlanResultService.java

...197        sheetTitle = result.getReRunParentId() == null ? "Run Result" : "Re-Run " + ++childCount;198      else {199        sheetTitle = "Consolidated Result Summary";200      }201      wrapper.getWorkbook().setSheetName(wrapper.getWorkbook().getSheetIndex(wrapper.getSheet()), sheetTitle);202      wrapper.setCurrentRow(-1);203      setResultDetails(result, wrapper);204      setTestCasesSummary(result, wrapper, isConsolidatedReport);205      setDetailedTestCaseList(result, wrapper, isConsolidatedReport);206      if(isConsolidatedReport) {207        wrapper.getWorkbook().setSheetOrder("Consolidated Result Summary", 0);208        return;209      }210      wrapper.createSheet();211    }212    this.findConsolidatedResultByTestPlanId(testPlanResult);213    this.export(testPlanResult,wrapper,true);214  }215  private void findConsolidatedResultByTestPlanId(TestPlanResult testPlanResult) {216    TestPlanResult childResult = testPlanResult.getTestPlan().getLastRun();217    TestPlanResult tempChildResult = testPlanResult.getChildResult();218    setConsolidatedResults(testPlanResult,childResult);219    while (tempChildResult != null) {220      if(ReRunType.runFailedTestCases(tempChildResult.getReRunType())) {221        testPlanResult.setConsolidatedPassedCount(testPlanResult.getPassedCount() + tempChildResult.getPassedCount());...

Source:TestSuiteResultService.java

...214    testSuiteResultMapper.merge(testCaseGroupResultRequest, testCaseGroupResult);215    update(testCaseGroupResult);216  }217  public void export(TestSuiteResult testSuiteResult, XLSUtil wrapper) throws ResourceNotFoundException {218    wrapper.getWorkbook().setSheetName(wrapper.getWorkbook().getSheetIndex(wrapper.getSheet()),219            "Run result summary");220    setResultDetails(testSuiteResult, wrapper);221    setTestCasesSummary(testSuiteResult, wrapper);222    setDetailedTestCaseList(testSuiteResult, wrapper);223  }224  private void setResultDetails(TestSuiteResult testSuiteResult, XLSUtil wrapper)225          throws ResourceNotFoundException {226    setHeading(wrapper, "TestPlan Details");227    setDetailsKeyValue("Test Plan Name", testSuiteResult.getTestPlanResult().getTestPlan().getName(), wrapper);228    setDetailsKeyValue("Test Machine Name", testSuiteResult.getTestDeviceResult().getTestDevice().getTitle(), wrapper);229    setDetailsKeyValue("Test Suite Name", testSuiteResult.getTestSuite().getName(), wrapper);230    if (testSuiteResult.getTestSuite().getDescription() != null)231      setDetailsKeyValue("Description", testSuiteResult.getTestSuite().getDescription().replaceAll("\\&([a-z0-9]+|#[0-9]{1,6}|#x[0-9a-fA-F]{1,6});|\\<.*?\\>", ""), wrapper);232    setDetailsKeyValue("RunId", testSuiteResult.getId().toString(), wrapper);...

Source:XLSUtil.java

...94     * @param wrapper95     * @return CellStyle96     */97    public static CellStyle getTableHeaderStyle(XLSUtil wrapper) {98        CellStyle colorStyle = wrapper.getWorkbook().createCellStyle();99        //set background color black100        colorStyle.setWrapText(true);101        colorStyle.setFillForegroundColor(HSSFColor.BLACK.index);102        colorStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);103        //text color white and style bold104        Font font = wrapper.getWorkbook().createFont();105        font.setColor(HSSFColor.WHITE.index);106        font.setBoldweight(Font.BOLDWEIGHT_BOLD);107        colorStyle.setFont(font);108        font.setBold(true);109        //align center110        colorStyle.setAlignment(CellStyle.ALIGN_CENTER);111        colorStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);112        return colorStyle;113    }114    /**115     * Return vertical center align style116     *117     * @param wrapper118     * @return CellStyle119     */120    public static CellStyle getAlignStyle(XLSUtil wrapper) {121        CellStyle style = wrapper.getWorkbook().createCellStyle();122        style.setWrapText(true);123        style.setAlignment(CellStyle.ALIGN_LEFT);124        return style;125    }126    public static CellStyle getSecondAlignStyle(XLSUtil wrapper) {127        CellStyle style = wrapper.getWorkbook().createCellStyle();128        style.setWrapText(true);129        style.setAlignment(CellStyle.ALIGN_LEFT);130        Font font = wrapper.getWorkbook().createFont();131        font.setBoldweight(Font.BOLDWEIGHT_BOLD);132        style.setFont(font);133        return style;134    }135    /**136     *137     */138    public void initStyles() {139        if (workbook != null) {140            cellDateStyle = workbook.createCellStyle();141            cellDateStyle.setDataFormat(createHelper.createDataFormat().getFormat("mmm-yy"));142        }143    }144    /**145     * @return Row146     */147    public Row getHeaderRow() {148        Row row = this.getSheet().getRow(0);149        if (null == row) {150            row = this.getSheet().createRow(0);151        }152        return row;153    }154    public Row getDataRow(XLSUtil excel, int newRowIndex) {155        Row row = excel.getSheet().getRow(newRowIndex);156        if (null == row) {157            row = excel.getSheet().createRow(newRowIndex);158        }159        return row;160    }161    /**162     * @return CellStyle163     */164    public CellStyle getHeaderStyle() {165        CellStyle style = this.getWorkbook().createCellStyle();166        getHeaderRow().setRowStyle(style);167        Font font = this.getWorkbook().createFont();168        font.setFontHeightInPoints((short) 10);169        font.setFontName("Arial");170        font.setColor(IndexedColors.WHITE.getIndex());171        font.setBoldweight(Font.BOLDWEIGHT_BOLD);172        font.setItalic(false);173        style = getHeaderRow().getRowStyle();174        style.setFillPattern(CellStyle.SOLID_FOREGROUND);175        style.setFillBackgroundColor(IndexedColors.AQUA.getIndex());176        style.setAlignment(CellStyle.ALIGN_CENTER);177        style.setFont(font);178        style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);179        return style;180    }181    /**182     * @return CellStyle183     */184    public CellStyle getCellDateStyle() {185        return cellDateStyle;186    }187    /**188     * @param dateStr189     * @return Date190     */191    public Date getDateType(String dateStr) {192        Date date = new Date();193        date.setTime(0);194        if (null != dateStr) {195            try {196                date = new SimpleDateFormat("MMM-yy", Locale.US).parse(dateStr);197            } catch (ParseException e) {198                try {199                    date = new SimpleDateFormat("MMMMM yyyy", Locale.US).parse(dateStr);200                } catch (ParseException e1) {201                    // TODO Auto-generated catch block202                    e1.printStackTrace();203                }204            }205        }206        return date;207    }208    public String getRunDate() {209        SimpleDateFormat sdfDate = new SimpleDateFormat("MM/dd/yyyy");210        Date now = new Date();211        String strDate = sdfDate.format(now);212        return strDate;213    }214    public String getRunTime() {215        SimpleDateFormat sdfDate = new SimpleDateFormat("h:mm a");216        Date now = new Date();217        String strDate = sdfDate.format(now);218        return strDate;219    }220    public void evaluateAll() {221        if (createHelper != null) {222            FormulaEvaluator evaluator = createHelper.createFormulaEvaluator();223            evaluator.evaluateAll();224        }225    }226    /**227     * @return SXSSFWorkbook228     */229    public SXSSFWorkbook getWorkbook() {230        return workbook;231    }232    /**233     * @return Sheet234     */235    public Sheet getSheet() {236        return sheet;237    }238    /**239     * @param sheet240     */241    public void setSheet(Sheet sheet) {242        this.sheet = sheet;243    }244    /**245     * @param name246     * @return Sheet247     */248    public Sheet createSheet(String name) {249        this.sheet = this.getWorkbook().createSheet(name);250        return this.sheet;251    }252    public Sheet createSheet() {253        this.sheet = this.getWorkbook().createSheet();254        return this.sheet;255    }256    /**257     * @return int258     */259    public int getCurrentRow() {260        return row;261    }262    /**263     * @param rowNum264     */265    public void setCurrentRow(int rowNum) {266        this.row = rowNum;267    }...

getWorkbook

Using AI Code Generation

1import com.testsigma.util.XLSUtil;2import java.io.File;3import java.io.FileInputStream;4import java.io.FileNotFoundException;5import java.io.IOException;6import java.util.logging.Level;7import java.util.logging.Logger;8import org.apache.poi.hssf.usermodel.HSSFWorkbook;9import org.apache.poi.ss.usermodel.Workbook;10public class 2 {11    public static void main(String[] args) {12        try {13            String filePath = "C:\\Users\\kamal\\Desktop\\Book1.xls";14            FileInputStream fileIn = new FileInputStream(new File(filePath));15            Workbook wb = XLSUtil.getWorkbook(fileIn, filePath);16            System.out.println(wb.getSheetAt(0).getRow(0).getCell(0).getStringCellValue());17        } catch (FileNotFoundException ex) {18            Logger.getLogger(2.class.getName()).log(Level.SEVERE, null, ex);19        } catch (IOException ex) {20            Logger.getLogger(2.class.getName()).log(Level.SEVERE, null, ex);21        }22    }23}24package com.testsigma.util;25import java.io.FileInputStream;26import java.io.FileNotFoundException;27import java.io.IOException;28import java.util.logging.Level;29import java.util.logging.Logger;30import org.apache.poi.hssf.usermodel.HSSFWorkbook;31import org.apache.poi.ss.usermodel.Workbook;32import org.apache.poi.xssf.usermodel.XSSFWorkbook;33public class XLSUtil {34    public static Workbook getWorkbook(FileInputStream fileIn, String filePath) {35        Workbook wb = null;36        try {37            if (filePath.endsWith(".xls")) {38                wb = new HSSFWorkbook(fileIn);39            } else if (filePath.endsWith(".xlsx")) {40                wb = new XSSFWorkbook(fileIn);41            }42        } catch (FileNotFoundException ex) {43            Logger.getLogger(XLSUtil.class.getName()).log(Level.SEVERE, null, ex);44        } catch (IOException ex) {45            Logger.getLogger(XLSUtil.class.getName()).log(Level.SEVERE, null, ex);46        }47        return wb;48    }49}

getWorkbook

Using AI Code Generation

1import com.testsigma.util.XLSUtil;2public class 2 {3    public static void main(String[] args) {4        XLSUtil xls = new XLSUtil();5        xls.getWorkbook("D:\\test.xlsx");6    }7}8import com.testsigma.util.XLSUtil;9public class 3 {10    public static void main(String[] args) {11        XLSUtil xls = new XLSUtil();12        xls.getSheet("D:\\test.xlsx", "Sheet1");13    }14}15import com.testsigma.util.XLSUtil;16public class 4 {17    public static void main(String[] args) {18        XLSUtil xls = new XLSUtil();19        xls.getRowCount("D:\\test.xlsx", "Sheet1");20    }21}22import com.testsigma.util.XLSUtil;23public class 5 {24    public static void main(String[] args) {25        XLSUtil xls = new XLSUtil();26        xls.getCellData("D:\\test.xlsx", "Sheet1", 2, 2);27    }28}29import com.testsigma.util.XLSUtil;30public class 6 {31    public static void main(String[] args) {32        XLSUtil xls = new XLSUtil();33        xls.setCellData("D:\\test.xlsx", "Sheet1", 2, 2, "test");34    }35}36import com.testsigma.util.XLSUtil;37public class 7 {38    public static void main(String[] args) {39        XLSUtil xls = new XLSUtil();40        xls.getCellData("D:\\test.xlsx", "Sheet1", 2, 2);41    }42}43import com.testsigma.util.XLSUtil;44public class 8 {45    public static void main(String

getWorkbook

Using AI Code Generation

1import com.testsigma.util.XLSUtil;2public class 2 {3    public static void main(String[] args) {4        XLSUtil xlsUtil = new XLSUtil();5        XSSFWorkbook workbook = xlsUtil.getWorkbook("C:\\Users\\testsigma\\Desktop\\test.xlsx");6    }7}8import com.testsigma.util.XLSUtil;9public class 3 {10    public static void main(String[] args) {11        XLSUtil xlsUtil = new XLSUtil();12        XSSFSheet sheet = xlsUtil.getSheet("C:\\Users\\testsigma\\Desktop\\test.xlsx", "Sheet1");13    }14}15import com.testsigma.util.XLSUtil;16public class 4 {17    public static void main(String[] args) {18        XLSUtil xlsUtil = new XLSUtil();19        XSSFRow row = xlsUtil.getRow("C:\\Users\\testsigma\\Desktop\\test.xlsx", "Sheet1", 1);20    }21}22import com.testsigma.util.XLSUtil;23public class 5 {24    public static void main(String[] args) {25        XLSUtil xlsUtil = new XLSUtil();26        XSSFCell cell = xlsUtil.getCell("C:\\Users\\testsigma\\Desktop\\test.xlsx", "Sheet1", 1, 1);27    }28}29import com.testsigma.util.XLSUtil;30public class 6 {31    public static void main(String[] args) {32        XLSUtil xlsUtil = new XLSUtil();33        String cellValue = xlsUtil.getCellValue("C:\\Users\\testsigma\\Desktop\\test.xlsx", "Sheet1", 1, 1);34    }35}

getWorkbook

Using AI Code Generation

1import java.io.File;2import java.io.FileInputStream;3import java.io.FileNotFoundException;4import java.io.IOException;5import java.util.Iterator;6import java.util.List;7import java.util.Map;8import java.util.Set;9import java.util.Map.Entry;10import java.util.concurrent.TimeUnit;11import org.apache.poi.ss.usermodel.Cell;12import org.apache.poi.ss.usermodel.Row;13import org.apache.poi.ss.usermodel.Sheet;14import org.apache.poi.ss.usermodel.Workbook;15import org.apache.poi.ss.usermodel.WorkbookFactory;16import org.apache.poi.xssf.usermodel.XSSFWorkbook;17import com.testsigma.util.XLSUtil;18public class XLSUtilTest {19public static void main(String[] args) throws IOException {20Workbook wb = XLSUtil.getWorkbook("C:\\Users\\srikanth\\Desktop\\Book1.xlsx");21Sheet sheet = wb.getSheet("Sheet1");22Iterator<Row> rowIterator = sheet.rowIterator();23while (rowIterator.hasNext()) {24Row row = rowIterator.next();25Iterator<Cell> cellIterator = row.cellIterator();26while (cellIterator.hasNext()) {27Cell cell = cellIterator.next();28System.out.print(cell.getStringCellValue() + "\t");29}30System.out.println();31}32}33}34package com.testsigma.util;35import java.io.File;36import java.io.FileInputStream;37import java.io.FileNotFoundException;38import java.io.IOException;39import java.util.Iterator;40import java.util.List;41import java.util.Map;42import java.util.Set;43import java.util.Map.Entry;44import java.util.concurrent.TimeUnit;45import org.apache.poi.ss.usermodel.Cell;46import org.apache.poi.ss.usermodel.Row;47import org.apache.poi.ss.usermodel.Sheet;48import org.apache.poi.ss.usermodel.Workbook;49import org.apache.poi.ss.usermodel.WorkbookFactory;50import org.apache.poi.xssf.usermodel.XSSFWorkbook;51public class XLSUtil {52public static Workbook getWorkbook(String filePath) throws IOException {53File f = new File(filePath);54Workbook wb = null;55FileInputStream fis = new FileInputStream(f);56if (filePath.endsWith(".xlsx")) {57wb = new XSSFWorkbook(fis);58} else if (filePath.endsWith(".xls")) {59wb = WorkbookFactory.create(fis);60}61return wb;62}63}

getWorkbook

Using AI Code Generation

1Workbook workbook = XLSUtil.getWorkbook("C:/test.xls");2Sheet sheet = XLSUtil.getSheet(workbook, "Sheet1");3Cell cell = XLSUtil.getCell(sheet, 1, 1);4String cellValue = XLSUtil.getCellValue(cell);5Row row = XLSUtil.getRow(sheet, 1);6Cell cell2 = XLSUtil.getCell(row, 1);7String cellValue2 = XLSUtil.getCellValue(cell2);8Cell cell3 = XLSUtil.getCell(sheet, 1, 1);9Cell cell4 = XLSUtil.getCell(row, 1);10String cellValue3 = XLSUtil.getCellValue(cell3);11String cellValue4 = XLSUtil.getCellValue(cell4);12XLSUtil.setCellValue(cell, "test");13XLSUtil.setCellValue(cell2, "test");

getWorkbook

Using AI Code Generation

1import java.io.FileInputStream;2import java.io.FileNotFoundException;3import java.io.IOException;4import java.util.Iterator;5import org.apache.poi.hssf.usermodel.HSSFSheet;6import org.apache.poi.hssf.usermodel.HSSFWorkbook;7import org.apache.poi.ss.usermodel.Cell;8import org.apache.poi.ss.usermodel.Row;9import com.testsigma.util.XLSUtil;10public class ReadDataFromWorkbook {11    public static void main(String[] args) throws FileNotFoundException, IOException {12        HSSFWorkbook workbook = XLSUtil.getWorkbook(new FileInputStream("D:\\test.xls"));13        HSSFSheet sheet = workbook.getSheet("Sheet1");14        Iterator<Row> rowIterator = sheet.iterator();15        while (rowIterator.hasNext()) {16            Row row = rowIterator.next();17            Iterator<Cell> cellIterator = row.cellIterator();18            while (cellIterator.hasNext()) {19                Cell cell = cellIterator.next();20                switch (cell.getCellType()) {21                    System.out.print(cell.getNumericCellValue() + "t");22                    break;23                    System.out.print(cell.getStringCellValue() + "t");24                    break;25                }26            }27            System.out.println("");28        }29    }30}31import java.io.FileNotFoundException;32import java.io.FileOutputStream;33import java.io.IOException;34import java.util.Iterator;35import org.apache.poi.hssf.usermodel.HSSFCell;36import org.apache.poi.hssf.usermodel.HSSFRow;37import org.apache.poi.hssf.usermodel.HSSFSheet;38import org.apache.poi.hssf.usermodel.HSSFWorkbook;39import org.apache.poi.ss.usermodel.Cell;40import org.apache.poi.ss.usermodel.Row;41import com.testsigma.util.XLSUtil;42public class WriteDataToWorkbook {43    public static void main(String[] args) throws FileNotFoundException, IOException {44        HSSFWorkbook workbook = XLSUtil.getWorkbook();45        HSSFSheet sheet = workbook.createSheet("Sheet1");46        HSSFRow row = sheet.createRow(0);47        HSSFCell cell = row.createCell(0);48        cell.setCellValue("Name");49        cell = row.createCell(1);50        cell.setCellValue("Age");51        row = sheet.createRow(1);52        cell = row.createCell(0);53        cell.setCellValue("John");54        cell = row.createCell(1);

getWorkbook

Using AI Code Generation

1import com.testsigma.util.XLSUtil;2Workbook wb = XLSUtil.getWorkbook("C:\\Users\\abc\\Desktop\\New folder\\test.xlsx");3import com.testsigma.util.XLSUtil;4Sheet sheet = XLSUtil.getSheet("C:\\Users\\abc\\Desktop\\New folder\\test.xlsx", "Sheet1");5import com.testsigma.util.XLSUtil;6String cellData = XLSUtil.getCellData("C:\\Users\\abc\\Desktop\\New folder\\test.xlsx", "Sheet1", 1, 1);7import com.testsigma.util.XLSUtil;8XLSUtil.setCellData("C:\\Users\\abc\\Desktop\\New folder\\test.xlsx", "Sheet1", 1, 1, "abc");9import com.testsigma.util.XLSUtil;10int rowCount = XLSUtil.getRowCount("C:\\Users\\abc\\Desktop\\New folder\\test.xlsx", "Sheet1");11import com.testsigma.util.XLSUtil;12int columnCount = XLSUtil.getColumnCount("C:\\Users\\abc\\Desktop\\New folder\\test.xlsx", "Sheet1");13import com.testsigma.util.XLSUtil;14String cellData = XLSUtil.getCellData("C:\\Users\\abc\\Desktop\\New folder\\test.xlsx", "Sheet1", 1, 1);15import com.testsigma.util.XLSUtil;16XLSUtil.setCellData("C:\\Users\\abc\\Desktop\\New folder\\test.xlsx", "Sheet1", 1, 1, "abc");17import com.testsigma.util.XLSUtil;18int rowCount = XLSUtil.getRowCount("C:\\Users\\abc\\Desktop\\New folder\\test.xlsx", "Sheet1");

getWorkbook

Using AI Code Generation

1import com.testsigma.util.XLSUtil;2Workbook wb = XLSUtil.getWorkbook("C:\\Users\\Admin\\Desktop\\Book1.xlsx");3System.out.println("Workbook created");4wb.close();5import com.testsigma.util.XLSUtil;6Workbook wb = XLSUtil.getWorkbook("C:\\Users\\Admin\\Desktop\\Book1.xls");7System.out.println("Workbook created");8wb.close();9import com.testsigma.util.XLSUtil;10Workbook wb = XLSUtil.getWorkbook("C:\\Users\\Admin\\Desktop\\Book1.xlsm");11System.out.println("Workbook created");12wb.close();13import com.testsigma.util.XLSUtil;14Workbook wb = XLSUtil.getWorkbook("C:\\Users\\Admin\\Desktop\\Book1.xltx");15System.out.println("Workbook created");16wb.close();17import com.testsigma.util.XLSUtil;18Workbook wb = XLSUtil.getWorkbook("C:\\Users\\Admin\\Desktop\\Book1.xltm");19System.out.println("Workbook created");20wb.close();21import com.testsigma.util.XLSUtil;22Workbook wb = XLSUtil.getWorkbook("C:\\Users\\Admin\\Desktop\\Book1.xlt");23System.out.println("Workbook created");24wb.close();25import com.testsigma.util.XLSUtil;26Workbook wb = XLSUtil.getWorkbook("C:\\Users\\Admin\\Desktop\\Book1.xltx");27System.out.println("Workbook created");28wb.close();29import com.testsigma.util.XLSUtil;30Workbook wb = XLSUtil.getWorkbook("C:\\Users\\Admin\\Desktop\\Book1.xltx");31System.out.println("Workbook created");

Automation Testing Tutorials

Learn to execute automation testing from scratch with LambdaTest Learning Hub. Right from setting up the prerequisites to run your first automation test, to following best practices and diving deeper into advanced test scenarios. LambdaTest Learning Hubs compile a list of step-by-step guides to help you be proficient with different test automation frameworks i.e. Selenium, Cypress, TestNG etc.