KaneAI - World’s First E2E Software Testing Agent.Book a Demo 
How to use XLSUtil method of com.testsigma.util.XLSUtil class
Best Testsigma code snippet using com.testsigma.util.XLSUtil.XLSUtil
Source:TestDataProfilesXLSImportService.java 
1package com.testsigma.service;2import com.testsigma.exception.TestsigmaValidationException;3import com.testsigma.model.StorageAccessLevel;4import com.testsigma.util.ReadExcel;5import com.testsigma.util.XLSUtil;6import lombok.RequiredArgsConstructor;7import lombok.extern.log4j.Log4j2;8import org.apache.commons.collections4.ListUtils;9import org.apache.poi.ss.usermodel.*;10import org.apache.poi.xssf.usermodel.XSSFWorkbook;11import org.springframework.beans.factory.annotation.Autowired;12import org.springframework.stereotype.Service;13import org.springframework.web.multipart.MultipartFile;14import java.io.IOException;15import java.net.URL;16import java.util.*;17import java.util.stream.Collectors;18@Service19@Log4j220@RequiredArgsConstructor(onConstructor = @__(@Autowired))21public class TestDataProfilesXLSImportService extends XLSImportService {22 private final TestDataImportService testDataImportService;23 public void importFile(String name, List<String> passwords, MultipartFile testDataFile, Long versionId, boolean isReplace) throws Exception {24 downloadAndProcessTestData(testDataFile, name, versionId, passwords, !isReplace);25 }26 public void downloadAndProcessTestData(MultipartFile testDataFile, String name, Long versionId, List<String> passwords, boolean canIgnore) throws Exception {27 Map<String, Integer> testDataProfileColumnNameIndexMap;28 Workbook workBook = new XSSFWorkbook(testDataFile.getInputStream());29 Collection<List<Object>> columnNames = ReadExcel.getExelFieldNames(workBook).values();30 try {31 testDataProfileColumnNameIndexMap = TestDataImportService.getFirstSheetFieldIdMap(TestDataImportService.getFiledNames(), columnNames);32 } catch (TestsigmaValidationException e) {33 log.error(e.getMessage(), e);34 incorrectColumnErrors(Arrays.asList(e.getMessage().split(",")));35 return;36 }37 List<List<List<Object>>> sheetsDataRows = getExelDataList(workBook);38 List<String> defaultColumnNames = TestDataImportService.getFiledNames();39 if (sheetsDataRows.size() > 0) {40 log.debug("Processing uploaded test data");41 processTestData(sheetsDataRows, defaultColumnNames, testDataProfileColumnNameIndexMap, Arrays.asList(columnNames.toArray()), name, versionId, passwords, canIgnore);42 log.debug("Added test data to DB after processing");43 }44 }45 public void processTestData(List<List<List<Object>>> sheetsDataList, List<String> fieldNames, Map<String,46 Integer> nameIndexMap, List<Object> columnNames, String name, Long versionId,47 List<String> passwords, boolean canIgnore) throws Exception {48 testDataImportService.initializeEmptyObjects();49 List<List<Object>> errors = new ArrayList<>();50 Integer size = 0;51 try {52 List<List<Object>> firstSheetDataRowsList = sheetsDataList.get(0);53 size = firstSheetDataRowsList.size();54 for (int i = 0; i < firstSheetDataRowsList.size(); i++) {55 List<Object> currentRowDataList = firstSheetDataRowsList.get(i);56 Object errorObject = testDataImportService.getRowObjects(nameIndexMap, currentRowDataList, columnNames);57 if (errorObject instanceof List) {58 ArrayList<Object> currentRowErrorObjectList = (ArrayList<Object>) errorObject;59 if (currentRowErrorObjectList.size() > 0) {60 errors.add(ListUtils.union(currentRowDataList, currentRowErrorObjectList));61 }62 }63 }64 } catch (Exception e) {65 log.error(e.getMessage(), e);66 List<Object> row = new ArrayList<Object>();67 row.add(e.getMessage());68 errors.add(row);69 }70 XLSUtil wrapper = new XLSUtil();71 try {72 if (errors.size() == size) {73 saveErrors(fieldNames, columnNames, nameIndexMap, errors, size);74 } else if (errors.size() > 0) {75 saveErrors(fieldNames, columnNames, nameIndexMap, errors, size);76 testDataImportService.addTestDataToDB(name,columnNames, versionId, passwords, canIgnore);77 } else {78 wrapper.setStorageService(super.getStorageServiceFactory().getStorageService());79 testDataImportService.addTestDataToDB(name,columnNames, versionId, passwords, canIgnore);80 log.info("Test data import successful");81 }82 } catch (Exception e) {83 log.info("Test data import failed");84 log.error(e.getMessage(), e);85 }86 }87 private void saveErrors(List<String> filedNames, List<Object> columnNames, Map<String, Integer> nameIndexMap, List<List<Object>> rows,88 Integer total) throws Exception {89 List<String> columnNamesList = columnNames.stream()90 .map(o -> {91 List<String> tempList = new ArrayList<>((List<String>) o);92 return tempList;93 })94 .collect(Collectors.toList()).get(0);95 XLSUtil wrapper = new XLSUtil();96 wrapper.setStorageService(super.getStorageServiceFactory().getStorageService());97 Row headerRow = wrapper.getHeaderRow();98 CellStyle headerStyle = XLSUtil.getTableHeaderStyle(wrapper);99 for (int i = 0; i < columnNamesList.size(); i++) {100 XLSUtil.createColumn(headerRow, i, columnNamesList.get(i), headerStyle);101 }102 XLSUtil.createColumn(headerRow, columnNamesList.size(), "Errors", headerStyle);103 CellStyle dataStyle = XLSUtil.getAlignStyle(wrapper);104 Row dataRow;105 for (int i = 0; i < rows.size(); i++) {106 dataRow = wrapper.getDataRow(wrapper, i + 1);107 List<Object> row = rows.get(i);108 int j = 0;109 for (; j < filedNames.size(); j++) {110 XLSUtil.createColumn(dataRow, j, row.get(nameIndexMap.get(filedNames.get(j))), dataStyle);111 }112 for (; j < row.size(); j++) {113 XLSUtil.createColumn(dataRow, j, row.get(j), dataStyle);114 }115 }116 if(total.equals(rows.size())) {117 log.info("Test Data import successful");118 } else {119 log.info("Test Data import partially successful");120 }121 }122 @Override123 public Object cellToObject(Cell cell, boolean testData) {124 return new DataFormatter().formatCellValue(cell);125 }126}...
Source:XLSImportService.java
1package com.testsigma.service;2import com.testsigma.config.StorageServiceFactory;3import com.testsigma.exception.ExceptionErrorCodes;4import com.testsigma.exception.TestsigmaValidationException;5import com.testsigma.util.XLSUtil;6import lombok.Getter;7import lombok.extern.log4j.Log4j2;8import org.apache.commons.lang3.StringUtils;9import org.apache.poi.ss.usermodel.*;10import org.apache.poi.ss.util.NumberToTextConverter;11import org.springframework.beans.factory.annotation.Autowired;12import java.util.*;13@Log4j214public abstract class XLSImportService {15 @Getter16 @Autowired17 private StorageServiceFactory storageServiceFactory;18 public List<List<List<Object>>> getExelDataList(19 Workbook testDataWorkBook) throws Exception {20 String errormessage = "";21 int numberOfColumns = 0;22 List<List<List<Object>>> sheetListData = new ArrayList<List<List<Object>>>();23 int noOfSheets = testDataWorkBook.getNumberOfSheets();24 for (int i = 0; i < noOfSheets; i++) {25 List<List<Object>> sheetData = new ArrayList<List<Object>>();26 Sheet sheet = testDataWorkBook.getSheetAt(i);27 int numberOfRows = getNumberOfNonEmptyRows(sheet);28 for (int j = 1; j < numberOfRows; j++) {29 Row row = sheet.getRow(j);30 if (row == null) {31 continue;32 }33 numberOfColumns = sheet.getRow(0).getLastCellNum();34 ArrayList<Object> singleRow = new ArrayList<Object>();35 for (int k = 0; k < numberOfColumns; k++) {36 Cell cell = row.getCell(k);37 if (cell != null) {38 singleRow.add(cellToObject(row.getCell(k), false));39 } else {40 singleRow.add("");41 }42 }43 sheetData.add(singleRow);44 }45 if (sheet.getPhysicalNumberOfRows() > 0) {46 sheetListData.add(sheetData);47 }48 }49 if (errormessage.length() > 1) {50 throw new TestsigmaValidationException(ExceptionErrorCodes.MSG_EXECEL_FILE_CELL_NULL,51 errormessage + "::" + "contains null");52 }53 return sheetListData;54 }55 protected Object cellToObject(Cell cell, boolean isStr) {56 int type;57 Object result = "";58 type = cell.getCellType();59 switch (type) {60 case Cell.CELL_TYPE_NUMERIC:61 if (isStr) {62 result = NumberToTextConverter.toText(cell.getNumericCellValue());63 } else {64 result = cell.getNumericCellValue();65 }66 break;67 case Cell.CELL_TYPE_FORMULA:68 switch (cell.getCachedFormulaResultType()) {69 case Cell.CELL_TYPE_NUMERIC:70 if (isStr) {71 result = NumberToTextConverter.toText(cell.getNumericCellValue());72 } else {73 result = cell.getNumericCellValue();74 }75 break;76 case Cell.CELL_TYPE_STRING:77 result = cell.getRichStringCellValue();78 break;79 }80 break;81 case Cell.CELL_TYPE_STRING:82 result = cell.getStringCellValue();83 break;84 case Cell.CELL_TYPE_BLANK:85 result = "";86 break;87 case Cell.CELL_TYPE_BOOLEAN:88 cell.setCellType(Cell.CELL_TYPE_STRING);89 result = cell.getStringCellValue();90 break;91 case Cell.CELL_TYPE_ERROR:92 default:93 throw new RuntimeException(94 "There is no support for this type of cell");95 }96 return result;97 }98 private int getNumberOfNonEmptyRows(Sheet sheet) {99 int rowIndex = 0, emptyColumnCheckingIndex = 0;100 Iterator<Cell> iterator = sheet.getRow(0).cellIterator();101 while (iterator.hasNext()) {102 Cell cell = iterator.next();103 if (cell != null && !StringUtils.isEmpty(cell.getStringCellValue())104 && (cell.getStringCellValue().equalsIgnoreCase("Testcase Name")105 || cell.getStringCellValue().equalsIgnoreCase("Name"))) {106 break;107 }108 emptyColumnCheckingIndex++;109 }110 for (rowIndex = 1; rowIndex < sheet.getPhysicalNumberOfRows(); rowIndex++) {111 if (sheet.getRow(rowIndex) == null) {112 break;113 }114 if (StringUtils.isEmpty(new DataFormatter().formatCellValue(sheet.getRow(rowIndex).getCell(emptyColumnCheckingIndex))))115 break;116 }117 return rowIndex;118 }119 public void incorrectColumnErrors(List<String> columnNames) {120 XLSUtil wrapper = new XLSUtil();121 wrapper.setStorageService(this.storageServiceFactory.getStorageService());122 Row headerRow = wrapper.getHeaderRow();123 CellStyle headerStyle = XLSUtil.getTableHeaderStyle(wrapper);124 XLSUtil.createColumn(headerRow, 0, "Missing columns", headerStyle);125 CellStyle dataStyle = XLSUtil.getAlignStyle(wrapper);126 for (int i = 0; i < columnNames.size(); i++) {127 Row dataRow = wrapper.getDataRow(wrapper, i + 1);128 XLSUtil.createColumn(dataRow, 0, columnNames.get(i), dataStyle);129 }130 log.error("Incorrect column error found");131 }132}...
Source:TestSuiteResultsController.java
...12import com.testsigma.mapper.TestSuiteResultMapper;13import com.testsigma.model.TestSuiteResult;14import com.testsigma.service.TestSuiteResultService;15import com.testsigma.specification.TestSuiteResultSpecificationsBuilder;16import com.testsigma.util.XLSUtil;17import lombok.RequiredArgsConstructor;18import lombok.extern.log4j.Log4j2;19import org.springframework.beans.factory.annotation.Autowired;20import org.springframework.data.domain.Page;21import org.springframework.data.domain.PageImpl;22import org.springframework.data.domain.Pageable;23import org.springframework.data.jpa.domain.Specification;24import org.springframework.security.access.prepost.PreAuthorize;25import org.springframework.web.bind.annotation.*;26import javax.servlet.http.HttpServletRequest;27import javax.servlet.http.HttpServletResponse;28import java.util.List;29@RestController30@Log4j231@RequestMapping(path = "/test_suite_results")32@RequiredArgsConstructor(onConstructor = @__(@Autowired))33public class TestSuiteResultsController {34 private final TestSuiteResultService testSuiteResultService;35 private final TestSuiteResultMapper testSuiteResultMapper;36 @RequestMapping(method = RequestMethod.GET)37 public Page<TestSuiteResultDTO> index(TestSuiteResultSpecificationsBuilder builder, Pageable pageable) {38 log.info("Request /test_suite_results/");39 Specification<TestSuiteResult> spec = builder.build();40 Page<TestSuiteResult> testSuiteResults = testSuiteResultService.findAll(spec, pageable);41 List<TestSuiteResultDTO> testSuiteResultDTOS =42 testSuiteResultMapper.mapDTO(testSuiteResults.getContent());43 return new PageImpl<>(testSuiteResultDTOS, pageable, testSuiteResults.getTotalElements());44 }45 @RequestMapping(value = {"/{id}"}, method = RequestMethod.GET)46 public TestSuiteResultDTO show(@PathVariable(value = "id") Long id) throws ResourceNotFoundException {47 log.info("Request /test_suite_results/" + id);48 TestSuiteResult testSuiteResult = testSuiteResultService.find(id);49 return testSuiteResultMapper.mapDTO(testSuiteResult);50 }51 @GetMapping(value = "/export/{id}")52 @PreAuthorize("hasPermission('RESULTS','READ')")53 public void exportRunResults(54 HttpServletRequest request,55 @PathVariable(value = "id") Long id,56 HttpServletResponse response) throws ResourceNotFoundException {57 TestSuiteResult testSuiteResult = testSuiteResultService.find(id);58 XLSUtil wrapper = new XLSUtil();59 testSuiteResultService.export(testSuiteResult, wrapper);60 wrapper.writeToStream(request, response, testSuiteResult.getTestSuite().getName());61 }62}...
XLSUtil
Using AI Code Generation
1package com.testsigma.util;2import java.io.File;3import java.io.FileInputStream;4import java.io.FileNotFoundException;5import java.io.FileOutputStream;6import java.io.IOException;7import org.apache.poi.hssf.usermodel.HSSFCell;8import org.apache.poi.hssf.usermodel.HSSFRow;9import org.apache.poi.hssf.usermodel.HSSFSheet;10import org.apache.poi.hssf.usermodel.HSSFWorkbook;11import org.apache.poi.ss.usermodel.Cell;12import org.apache.poi.ss.usermodel.Row;13import org.apache.poi.ss.usermodel.Workbook;14import org.apache.poi.ss.usermodel.WorkbookFactory;15import org.apache.poi.xssf.usermodel.XSSFWorkbook;16public class XLSUtil {17public static String getCellValue(String filePath, String sheetName, int row, int col) {18String value = null;19try {20FileInputStream file = new FileInputStream(new File(filePath));21Workbook workbook = WorkbookFactory.create(file);22org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheet(sheetName);23Row rowObj = sheet.getRow(row);24Cell cell = rowObj.getCell(col);25value = cell.getStringCellValue();26file.close();27} catch (FileNotFoundException e) {28e.printStackTrace();29} catch (IOException e) {30e.printStackTrace();31}32return value;33}34public static void setCellValue(String filePath, String sheetName, int row, int col, String value) {35try {36FileInputStream file = new FileInputStream(new File(filePath));37Workbook workbook = WorkbookFactory.create(file);38org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheet(sheetName);39Row rowObj = sheet.getRow(row);40Cell cell = rowObj.createCell(col);41cell.setCellValue(value);42file.close();43FileOutputStream outFile =new FileOutputStream(new File(filePath));44workbook.write(outFile);45outFile.close();46} catch (FileNotFoundException e) {47e.printStackTrace();48} catch (IOException e) {49e.printStackTrace();50}51}52public static void main(String[] args) {53String filePath = "E:/Selenium/Book1.xls";54String sheetName = "Sheet1";55String value = getCellValue(filePath, sheetName, 0, 0);56System.out.println("The value in the cell is: " + value);57setCellValue(filePath, sheetName, 0, 0, "TestSigma");58value = getCellValue(filePath, sheetName, 0, 0);59System.out.println("The value in the cell is: " + value);60}61}62package com.testsigma.util;63import java.io.File;64import java.io.FileInputStream;65import java.io.FileNotFoundException;66import java.io.FileOutputStream;67import
XLSUtil
Using AI Code Generation
1package com.testsigma.util;2import java.io.File;3import java.io.FileInputStream;4import java.io.FileNotFoundException;5import java.io.FileOutputStream;6import java.io.IOException;7import java.util.ArrayList;8import java.util.Iterator;9import java.util.List;10import org.apache.poi.ss.usermodel.Cell;11import org.apache.poi.ss.usermodel.Row;12import org.apache.poi.ss.usermodel.Sheet;13import org.apache.poi.ss.usermodel.Workbook;14import org.apache.poi.ss.usermodel.WorkbookFactory;15import org.apache.poi.xssf.usermodel.XSSFWorkbook;16public class XLSUtil {17private static final String FILE_PATH = "D:/test.xlsx";18private static final String SHEET_NAME = "Sheet1";19public static void main(String[] args) {20}21public static void createXLSFile() {22Workbook workbook = new XSSFWorkbook();23Sheet sheet = workbook.createSheet(SHEET_NAME);24Row row = sheet.createRow(0);25Cell cell = row.createCell(0);26cell.setCellValue("Name");27cell = row.createCell(1);28cell.setCellValue("Age");29cell = row.createCell(2);30cell.setCellValue("Salary");31row = sheet.createRow(1);32cell = row.createCell(0);33cell.setCellValue("John");34cell = row.createCell(1);35cell.setCellValue("30");36cell = row.createCell(2);37cell.setCellValue("5000");38row = sheet.createRow(2);39cell = row.createCell(0);40cell.setCellValue("Mary");41cell = row.createCell(1);42cell.setCellValue("25");43cell = row.createCell(2);44cell.setCellValue("2000");45row = sheet.createRow(3);46cell = row.createCell(0);47cell.setCellValue("Peter");48cell = row.createCell(1);49cell.setCellValue("35");50cell = row.createCell(2);51cell.setCellValue("10000");52try {53FileOutputStream fos = new FileOutputStream(FILE_PATH);54workbook.write(fos);55fos.close();56} catch (FileNotFoundException e) {57e.printStackTrace();58} catch (IOException e) {59e.printStackTrace();60}61}62public static void readXLSFile() {63try {64FileInputStream fis = new FileInputStream(FILE_PATH);65Workbook workbook = WorkbookFactory.create(fis);66Sheet sheet = workbook.getSheet(SHEET_NAME);67Iterator<Row> rowIterator = sheet.iterator();68while(rowIterator.hasNext()) {69Row row = rowIterator.next();
XLSUtil
Using AI Code Generation
1package com.testsigma.util;2import java.io.File;3import java.io.FileInputStream;4import java.io.FileNotFoundException;5import java.io.IOException;6import java.util.ArrayList;7import java.util.HashMap;8import java.util.List;9import java.util.Map;10import org.apache.poi.ss.usermodel.Cell;11import org.apache.poi.ss.usermodel.Row;12import org.apache.poi.ss.usermodel.Sheet;13import org.apache.poi.ss.usermodel.Workbook;14import org.apache.poi.xssf.usermodel.XSSFWorkbook;15public class XLSUtil {16 public static List<Map<String, String>> getTestData(String excelFilePath, String sheetName) {17 List<Map<String, String>> testData = new ArrayList<Map<String, String>>();18 Workbook workbook = null;19 try {20 workbook = new XSSFWorkbook(new FileInputStream(new File(excelFilePath)));21 } catch (FileNotFoundException e) {22 e.printStackTrace();23 } catch (IOException e) {24 e.printStackTrace();25 }26 Sheet sheet = workbook.getSheet(sheetName);27 Row headerRow = sheet.getRow(0);28 for (int i = 1; i <= sheet.getLastRowNum(); i++) {29 Row currentRow = sheet.getRow(i);30 Map<String, String> currentTestCaseData = new HashMap<String, String>();31 for (int j = 0; j < headerRow.getLastCellNum(); j++) {32 Cell currentHeader = headerRow.getCell(j);33 Cell currentCell = currentRow.getCell(j);34 currentTestCaseData.put(currentHeader.getStringCellValue(), currentCell.getStringCellValue());35 }36 testData.add(currentTestCaseData);37 }38 try {39 workbook.close();40 } catch (IOException e) {41 e.printStackTrace();42 }43 return testData;44 }45}46package com.testsigma.util;47import java.io.IOException;48import java.util.List;49import java.util.Map;50public class TestXLSUtil {51 public static void main(String[] args) throws IOException {52 List<Map<String, String>> testData = XLSUtil.getTestData("C:\\Users\\Srinivas\\Desktop\\Test.xlsx", "Sheet1");53 for (Map<String, String> map : testData) {54 System.out.println(map);55 }56 }57}58package com.testsigma.util;59import java.io.IOException;60import java.util.List;61import java.util.Map;62public class TestXLSUtil {63 public static void main(String
XLSUtil
Using AI Code Generation
1package com.testsigma.util;2import java.io.File;3import java.io.FileInputStream;4import java.io.FileOutputStream;5import java.io.IOException;6import java.util.ArrayList;7import java.util.List;8import org.apache.poi.hssf.usermodel.HSSFRow;9import org.apache.poi.hssf.usermodel.HSSFSheet;10import org.apache.poi.hssf.usermodel.HSSFWorkbook;11import org.apache.poi.ss.usermodel.Cell;12import org.apache.poi.ss.usermodel.Row;13import org.apache.poi.xssf.usermodel.XSSFSheet;14import org.apache.poi.xssf.usermodel.XSSFWorkbook;15import org.apache.poi.ss.usermodel.CellType;16import org.apache.poi.ss.usermodel.Sheet;17import org.apache.poi.ss.usermodel.Workbook;18public class XLSUtil {19private static final String XLSX = "xlsx";20private static final String XLS = "xls";21public static void writeExcel(String fileName, String sheetName, List<String> dataToWrite) throws IOException {22File file = new File(fileName);23FileInputStream inputStream = new FileInputStream(file);24Workbook guru99Workbook = null;25String fileExtensionName = fileName.substring(fileName.indexOf("."));26if(fileExtensionName.equals(".xlsx")){27guru99Workbook = new XSSFWorkbook(inputStream);28}29else if(fileExtensionName.equals(".xls")){30guru99Workbook = new HSSFWorkbook(inputStream);31}32Sheet sheet = guru99Workbook.getSheet(sheetName);33int rowCount = sheet.getLastRowNum()-sheet.getFirstRowNum();34Row row = sheet.getRow(0);35Row newRow = sheet.createRow(rowCount+1);36for(int j = 0; j < row.getLastCellNum(); j++){37Cell cell = newRow.createCell(j);38cell.setCellValue(dataToWrite.get(j));39}40inputStream.close();
XLSUtil
Using AI Code Generation
1package com.testsigma.util;2import java.io.FileInputStream;3import java.io.IOException;4import java.io.InputStream;5import java.util.ArrayList;6import java.util.List;7import org.apache.poi.hssf.usermodel.HSSFCell;8import org.apache.poi.hssf.usermodel.HSSFRow;9import org.apache.poi.hssf.usermodel.HSSFSheet;10import org.apache.poi.hssf.usermodel.HSSFWorkbook;11import org.apache.poi.ss.usermodel.Cell;12import org.apache.poi.ss.usermodel.Row;13import org.apache.poi.ss.usermodel.Workbook;14import org.apache.poi.ss.usermodel.WorkbookFactory;15public class XLSUtil {16public static List<String> readXLS(String fileName, String sheetName) throws IOException {17List<String> data = new ArrayList<String>();18InputStream inp = new FileInputStream(fileName);19Workbook wb = WorkbookFactory.create(inp);20int sheetIndex = wb.getSheetIndex(sheetName);21HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(sheetIndex);22int rows = sheet.getPhysicalNumberOfRows();23for (int r = 0; r < rows; r++) {24HSSFRow row = sheet.getRow(r);25if (row == null) {26continue;27}28int cells = row.getPhysicalNumberOfCells();29for (int c = 0; c < cells; c++) {30HSSFCell cell = row.getCell(c);31if (cell == null) {32continue;33}34data.add(cell.toString());35}36}37return data;38}39public static void writeXLS(String fileName, String sheetName, String data) throws IOException {40InputStream inp = new FileInputStream(fileName);41Workbook wb = WorkbookFactory.create(inp);42int sheetIndex = wb.getSheetIndex(sheetName);43HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(sheetIndex);44int rows = sheet.getPhysicalNumberOfRows();45int cols = sheet.getRow(rows - 1).getPhysicalNumberOfCells();46int row = rows;47int col = cols;48Row r = sheet.getRow(row);49if (r == null) {50r = sheet.createRow(row);51}52Cell cell = r.getCell(col);53if (cell == null) {54cell = r.createCell(col);55}56cell.setCellValue(data);57}58}59package com.testsigma.util;60import java.io.IOException;61import java.util.List;62public class XLSUtilTest {63public static void main(String[] args) {
XLSUtil
Using AI Code Generation
1package com.testsigma.util;2import java.io.FileInputStream;3import java.io.FileNotFoundException;4import java.io.IOException;5import java.util.ArrayList;6import java.util.Iterator;7import org.apache.poi.hssf.usermodel.HSSFSheet;8import org.apache.poi.hssf.usermodel.HSSFWorkbook;9import org.apache.poi.ss.usermodel.Cell;10import org.apache.poi.ss.usermodel.Row;11import org.apache.poi.ss.usermodel.Sheet;12import org.apache.poi.ss.usermodel.Workbook;13import org.apache.poi.xssf.usermodel.XSSFWorkbook;14public class XLSUtil {15 public static ArrayList readExcel(String filePath, String fileName, String sheetName) throws IOException{16 FileInputStream inputStream = new FileInputStream(filePath+"\\"+fileName);17 Workbook guru99Workbook = null;18 String fileExtensionName = fileName.substring(fileName.indexOf("."));19 if(fileExtensionName.equals(".xlsx")){20 guru99Workbook = new XSSFWorkbook(inputStream);21 }22 else if(fileExtensionName.equals(".xls")){23 guru99Workbook = new HSSFWorkbook(inputStream);24 }25 Sheet guru99Sheet = guru99Workbook.getSheet(sheetName);26 int rowCount = guru99Sheet.getLastRowNum()-guru99Sheet.getFirstRowNum();27 ArrayList<String> al = new ArrayList<String>();28 for (int i = 0; i < rowCount+1; i++) {29 Row row = guru99Sheet.getRow(i);30 for (int j = 0; j < row.getLastCellNum(); j++) {31 System.out.print(row.getCell(j).getStringCellValue()+"|| ");32 al.add(row.getCell(j).getStringCellValue());33 }34 System.out.println();35 } 36 return al;37 }38}
XLSUtil
Using AI Code Generation
1package com.testsigma.test;2import java.io.IOException;3import java.util.ArrayList;4import java.util.HashMap;5import org.testng.annotations.Test;6import com.testsigma.util.XLSUtil;7public class ReadExcelData2 {8public void readExcelData() throws IOException {9String filePath = System.getProperty("user.dir") + "/src/test/resources/data/Book1.xlsx";10ArrayList<HashMap<String, String>> data = XLSUtil.readExcel(filePath, "Sheet1");11System.out.println(data);12}13}14package com.testsigma.test;15import java.io.IOException;16import java.util.ArrayList;17import java.util.HashMap;18import org.testng.annotations.Test;19import com.testsigma.util.XLSUtil;20public class ReadExcelData1 {21public void readExcelData() throws IOException {22String filePath = System.getProperty("user.dir") + "/src/test/resources/data/Book1.xlsx";23ArrayList<HashMap<String, String>> data = XLSUtil.readExcel(filePath, "Sheet1");24System.out.println(data);25}26}27package com.testsigma.test;28import java.io.IOException;29import java.util.ArrayList;30import java.util.HashMap;31import org.testng.annotations.Test;32import com.testsigma.util.XLSUtil;33public class ReadExcelData3 {34public void readExcelData() throws IOException {35String filePath = System.getProperty("user.dir") + "/src/test/resources/data/Book1.xlsx";36ArrayList<HashMap<String, String>> data = XLSUtil.readExcel(filePath, "Sheet1");37System.out.println(data);38}39}40package com.testsigma.test;41import java.io.IOException;42import java.util.ArrayList;43import java.util.HashMap;44import org.testng.annotations.Test;45import com.testsigma.util.XLSUtil;46public class ReadExcelData4 {47public void readExcelData() throws IOException {48String filePath = System.getProperty("user.dir") + "/src/test/resources/data/Book1.xlsx";49ArrayList<HashMap<String, String>> data = XLSUtil.readExcel(filePath, "Sheet1");
XLSUtil
Using AI Code Generation
1package com.testsigma.test;2import java.util.List;3import org.testng.annotations.Test;4import com.testsigma.util.XLSUtil;5public class TestExcelRead {6 public void testExcelRead() throws Exception {7 List<List<String>> excelData = XLSUtil.readExcel("C:\\Users\\Administrator\\Desktop\\TestData.xlsx");8 for (List<String> row : excelData) {9 System.out.println(row);10 }11 }12}13package com.testsigma.test;14import java.util.ArrayList;15import java.util.List;16import org.testng.annotations.Test;17import com.testsigma.util.XLSUtil;18public class TestExcelWrite {19 public void testExcelWrite() throws Exception {20 List<List<String>> excelData = new ArrayList<List<String>>();21 List<String> row1 = new ArrayList<String>();22 row1.add("Row 1 Col 1");23 row1.add("Row 1 Col 2");24 row1.add("Row 1 Col 3");25 List<String> row2 = new ArrayList<String>();26 row2.add("Row 2 Col 1");27 row2.add("Row 2 Col 2");28 row2.add("Row 2 Col 3");29 List<String> row3 = new ArrayList<String>();30 row3.add("Row 3 Col 1");31 row3.add("Row 3 Col 2");32 row3.add("Row 3 Col 3");33 excelData.add(row1);34 excelData.add(row2);35 excelData.add(row3);36 XLSUtil.writeExcel("C:\\Users\\Administrator\\Desktop\\TestData.xlsx", excelData);37 }38}39package com.testsigma.test;40import java.util.ArrayList;41import java.util.List;42import org.testng.annotations.Test;43import com.testsigma.util.XLSUtil;44public class TestExcelWrite {
XLSUtil
Using AI Code Generation
1import com.testsigma.util.XLSUtil;2public class XLSUtilTest {3 public static void main(String[] args) {4 String[][] data = XLSUtil.getExcelData("C:\\Users\\TestSigma\\Desktop\\TestData.xls", "Sheet1");5 for (int i = 0; i < data.length; i++) {6 for (int j = 0; j < data[i].length; j++) {7 System.out.print(data[i][j] + "\t");8 }9 System.out.println();10 }11 }12}13import com.testsigma.util.XLSUtil;14public class XLSUtilTest {15 public static void main(String[] args) {16 String[][] data = XLSUtil.getExcelData("C:\\Users\\TestSigma\\Desktop\\TestData.xls", "Sheet1", 2, 2);17 for (int i = 0; i < data.length; i++) {18 for (int j = 0; j < data[i].length; j++) {19 System.out.print(data[i][j] + "\t");20 }21 System.out.println();22 }23 }24}25import com.testsigma.util.XLSUtil;26public class XLSUtilTest {27 public static void main(String[] args) {28 String[][] data = XLSUtil.getExcelData("C:\\Users\\TestSigma\\Desktop\\TestData.xls", "Sheet1", 2, 2, 3, 3);29 for (int i = 0; i < data.length; i++) {30 for (int j = 0; j < data[i].length; j++) {31 System.out.print(data[i][j] + "\t");32 }33 System.out.println();34 }35 }36}37import com.testsigma.util.XLSUtil;38public class XLSUtilTest {39 public static void main(String[] args) {40 String[][] data = XLSUtil.getExcelData("C:\\Users\\TestSigma\\Desktop\\TestData.xls", "Sheet1", 2,
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?
