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.