Unlock 30% off on Manual Testing Annual Plans this Holiday Season. 
Use Code: LCMYEARENDEXCL30
Use Code: LCMYEARENDEXCL30Check icon
Copied to Clipboard!
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.
LambdaTest Learning Hubs:
- JUnit Tutorial
- TestNG Tutorial
- Webdriver Tutorial
- WebDriverIO Tutorial
- Protractor Tutorial
- Selenium 4 Tutorial
- Jenkins Tutorial
- NUnit Tutorial
- Jest Tutorial
- Playwright Tutorial
- Cypress Tutorial
- PyTest Tutorial
YouTube
You could also refer to video tutorials over LambdaTest YouTube channel to get step by step demonstration from industry experts.
Run Testsigma automation tests on LambdaTest cloud grid
Perform automation testing on 3000+ real desktop and mobile devices online.
Most used method in XLSUtil
- XLSUtil
- createColumn
- setValue
- getTableHeaderStyle
- getAlignStyle
- getSecondAlignStyle
- initStyles
- getHeaderRow
- getDataRow
- getHeaderStyle
- getCellDateStyle
- getDateType
- getRunDate
- getRunTime
- evaluateAll
- getWorkbook
- getSheet
- setSheet
- createSheet
- getCurrentRow
- setCurrentRow
- getNewRow
- incRow
- getStatus
- setStatus
- writeToStream
- getBrowserName
Try LambdaTest Now !!
Get 100 minutes of automation test minutes FREE!!
Was this article helpful?
