Unlock 30% off on Manual Testing Annual Plans this Holiday Season.

Copied to Clipboard!

How to use getWorkbook method of com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache class

Best Carina code snippet using com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache.getWorkbook

Source:XLSParser.java

...44	45	public static String parseValue(String locatorKey, String xlsPath, Locale locale)46	{47		String value = null;48		Workbook wb = XLSCache.getWorkbook(xlsPath);49		Sheet sheet = wb.getSheetAt(0);50		List<String> locales = getLocales(sheet);51		if (!locales.contains(locale.getCountry()))52		{53			throw new RuntimeException("Can't find locale '" + locale.getCountry() + "' in xls '" + xlsPath + "'!");54		}55		int cellN = locales.indexOf(locale.getCountry()) + 1;56		List<String> locatorKeys = getLocatorKeys(sheet);57		if (!locatorKeys.contains(locatorKey))58		{59			throw new RuntimeException("Can't find locatorKey '" + locatorKey + "' in xls '" + xlsPath + "'!");60		}61		int rowN = locatorKeys.indexOf(locatorKey) + 1;62		try63		{64			value = getCellValue(sheet.getRow(rowN).getCell(cellN));65		} catch (Exception e)66		{67			throw new RuntimeException("Can't find value for locatorKey '" + locatorKey + "' with locale '" + locale.getCountry()68					+ "' in xls '" + xlsPath + "'!");69		}70		return value;71	}72	73	private static List<String> getLocales(Sheet sheet)74	{75		List<String> locales = new ArrayList<String>();76		int lastCell = sheet.getRow(0).getLastCellNum();77		for (int i = 1; i < lastCell; i++)78		{79			locales.add(getCellValue(sheet.getRow(0).getCell(i)));80		}81		return locales;82	}83	private static List<String> getLocatorKeys(Sheet sheet)84	{85		List<String> locatorKeys = new ArrayList<String>();86		int lastRow = sheet.getLastRowNum();87		for (int i = 1; i <= lastRow; i++)88		{89			locatorKeys.add(getCellValue(sheet.getRow(i).getCell(0)));90		}91		return locatorKeys;92	}93	94	public static String parseValue(String xls, String sheetName, String key)95	{96		String value = null;97		98		Workbook wb = XLSCache.getWorkbook(xls);99		100		Sheet sheet = wb.getSheet(sheetName);101		if(sheet == null)102		{103			throw new InvalidArgsException(String.format("No sheet: '%s' in excel file: '%s'!", sheetName, xls));104		}105		106		boolean isKeyFound = false;107		for(int i = 1; i <= sheet.getLastRowNum(); i++)108		{109			if(key.equals(getCellValue(sheet.getRow(i).getCell(0))))110			{111				value = getCellValue(sheet.getRow(i).getCell(1));112				isKeyFound = true;113				break;114			}115		}116		117		if(!isKeyFound)118		{119			throw new InvalidArgsException(String.format("No key: '%s' on sheet '%s' in excel file: '%s'!", key, sheetName, xls));120		}121		122		return value;123	}124	125	public static XLSTable parseSpreadSheet(String xls, String sheetName)126	{127		return parseSpreadSheet(xls, sheetName, null, null);128	}129	130	public static XLSTable parseSpreadSheet(String xls, String sheetName, String executeColumn, String executeValue)131	{132		XLSTable dataTable;133		if(executeColumn != null && executeValue != null)134		{135			dataTable = new XLSTable(executeColumn, executeValue);136		}137		else138		{139			dataTable = new XLSTable();140		}141		142		Workbook wb = XLSCache.getWorkbook(xls);143		evaluator = wb.getCreationHelper().createFormulaEvaluator();		144		145		Sheet sheet = wb.getSheet(sheetName);146		if(sheet == null)147		{148			throw new InvalidArgsException(String.format("No sheet: '%s' in excel file: '%s'!", sheetName, xls));149		}150		151		try{152			for(int i = 0; i <= sheet.getLastRowNum(); i++)153			{154				if(i == 0)155				{156					dataTable.setHeaders(sheet.getRow(i));157				}158				else159				{160					dataTable.addDataRow(sheet.getRow(i), wb, sheet);161				}162			}163		}164		catch (Exception e) {165			LOGGER.error(e.getMessage(), e);166		}167		return dataTable;168	}169	170	public static String getCellValue(Cell cell)171	{172		if(cell == null) return "";		173			174		switch (cell.getCellType())175		{176		case Cell.CELL_TYPE_STRING:177			return df.formatCellValue(cell).trim();178		case Cell.CELL_TYPE_NUMERIC:179			return df.formatCellValue(cell).trim();180		case Cell.CELL_TYPE_BOOLEAN:181			return df.formatCellValue(cell).trim();182		case Cell.CELL_TYPE_FORMULA:183			return (cell.getCellFormula().contains("[") && cell.getCellFormula().contains("]")) ? null : df.formatCellValue(cell, evaluator).trim();184		case Cell.CELL_TYPE_BLANK:185			return "";186		default:187			return null;188		}189	}190	191	public static XLSChildTable parseCellLinks(Cell cell, Workbook wb, Sheet sheet) 192	{193 		if(cell == null) return null; 		194 		195		if(cell.getCellType() == Cell.CELL_TYPE_FORMULA)196		{197			if(cell.getCellFormula().contains("#This Row"))198			{199				if(cell.getCellFormula().contains("!"))200				{201					// Parse link to the cell with table name in the external doc([2]!Table1[[#This Row],[Header6]])					202					List<String> paths = Arrays.asList(cell.getCellFormula().split("!"));203					int externalLinkNumber = Integer.valueOf(paths.get(0).replaceAll("\\D+", "")) - 1;204					String tableName = paths.get(1).split("\\[")[0];205					if(wb instanceof XSSFWorkbook)206					{							207						ExternalLinksTable link = ((XSSFWorkbook) wb).getExternalLinksTable().get(externalLinkNumber);208						File file = new File(XLSCache.getWorkbookPath(wb));209						XSSFWorkbook childWb = (XSSFWorkbook) XLSCache.getWorkbook(file.getParent() + "/" + link.getLinkedFileName());210						if (childWb == null)  throw new DataLoadingException(String.format("WorkBook '%s' doesn't exist!",  link.getLinkedFileName()));211						for(int i = 0; i < childWb.getNumberOfSheets(); i++)212						{213							XSSFSheet childSheet = childWb.getSheetAt(i);214							for (XSSFTable table : childSheet.getTables())215							{216								if(table.getName().equals(tableName))217								{218									return createChildTable(childSheet, cell.getRowIndex());219								}220							}221						}					222					}223					else224					{225						throw new DataLoadingException("Unsupported format. External links supports only for .xlsx documents.");226					}227				} else228				{229					// Parse link to the cell with table name in the same doc(=Table1[[#This Row],[Header6]])230					List<String> paths = Arrays.asList(cell.getCellFormula().replace("=", "").split("\\["));231					if(wb instanceof XSSFWorkbook)232					{233						for(int i = 0; i < wb.getNumberOfSheets(); i++)234						{235							XSSFSheet childSheet = (XSSFSheet) wb.getSheetAt(i);236							for (XSSFTable table : childSheet.getTables())237							{238								if(table.getName().equals(paths.get(0)))239								{240									return createChildTable(childSheet, cell.getRowIndex());241								}242							}243						}					244					}245					else246					{247						throw new DataLoadingException("Unsupported format. Links with table name supports only for .xlsx documents.");248					}249				}250			} 251			else 252			{								253				String cellValue = cell.getCellFormula().replace("=", "").replace("[", "").replace("]", "!").replace("'", "");254				List<String> paths = Arrays.asList(cellValue.split("!"));			255				int rowNumber = 0;		256				Sheet childSheet = null;								257				258				switch(paths.size())259				{260					// Parse link to the cell in the same sheet(=A4)261					case 1:262						rowNumber = Integer.valueOf(paths.get(0).replaceAll("\\D+", "")) - 1;263						return createChildTable(sheet, rowNumber);264					// Parse link to the cell in another sheet in the same doc(=SheetName!A4)				265					case 2:					266						childSheet = wb.getSheet(paths.get(0));267						if(childSheet == null) throw new DataLoadingException(String.format("Sheet '%s' doesn't exist!",  paths.get(0)));	268						rowNumber = Integer.valueOf(paths.get(1).replaceAll("\\D+", "")) - 1;269						return createChildTable(childSheet, rowNumber);270					// Parse link to the cell in another doc(=[2]SheetName!A4)271					case 3:272						if(wb instanceof XSSFWorkbook)273						{	274							ExternalLinksTable link = ((XSSFWorkbook) wb).getExternalLinksTable().get(Integer.valueOf(paths.get(0)) - 1);275							File file = new File(XLSCache.getWorkbookPath(wb));276							XSSFWorkbook childWb = (XSSFWorkbook) XLSCache.getWorkbook(file.getParent() + "/" +link.getLinkedFileName());						277							278							if (childWb == null)  throw new DataLoadingException(String.format("WorkBook '%s' doesn't exist!",  paths.get(0)));279							childSheet = childWb.getSheet(paths.get(1));280							if(childSheet == null) throw new DataLoadingException(String.format("Sheet '%s' doesn't exist!",  paths.get(0)));281							rowNumber = Integer.valueOf(paths.get(2).replaceAll("\\D+", "")) - 1;282							return createChildTable(childSheet, rowNumber);283						}284						else285						{286							throw new DataLoadingException("Unsupported format. External links supports only for .xlsx documents.");287						}288					default:289						return null;290				}...

Source:XLSCache.java

...22import org.apache.poi.ss.usermodel.WorkbookFactory;23public class XLSCache24{25	private static Map<String, Workbook> xlsCache = new HashMap<String, Workbook>();26	public static synchronized Workbook getWorkbook(String xlsPath)27	{28		if (!xlsCache.keySet().contains(xlsPath))29		{30			Workbook wb;31			try32			{33				InputStream is = ClassLoader.getSystemResourceAsStream(xlsPath);34				try35				{36					wb = WorkbookFactory.create(is);37				} finally38				{39					if (is != null)40					{41						is.close();42					}43				}44			} catch (Exception e)45			{46				throw new RuntimeException("Can't read xls: " + xlsPath);47			}48			xlsCache.put(xlsPath, wb);49		}50		return xlsCache.get(xlsPath);51	}52	53	public static synchronized String getWorkbookPath(Workbook book)54	{55		for (Entry<String, Workbook> entry : xlsCache.entrySet()) 56		{57			if(entry.getValue() == book)58				return entry.getKey();59		}60		return null;61	}62}

getWorkbook

Using AI Code Generation

1package com.qaprosoft.carina.core.foundation.dataprovider.parser;2import java.io.File;3import java.io.FileInputStream;4import java.io.FileNotFoundException;5import java.io.IOException;6import java.util.Iterator;7import org.apache.poi.ss.usermodel.Cell;8import org.apache.poi.ss.usermodel.Row;9import org.apache.poi.ss.usermodel.Sheet;10import org.apache.poi.ss.usermodel.Workbook;11import org.apache.poi.ss.usermodel.WorkbookFactory;12public class XLSReader {13public static void main(String[] args) throws IOException {14File f = new File("C:\\Users\\HP\\Desktop\\Test.xlsx");15FileInputStream fis = new FileInputStream(f);16XLSCache cache = new XLSCache();17Workbook wb = cache.getWorkbook(fis, "Test.xlsx");18Sheet sheet = wb.getSheetAt(0);19Iterator<Row> itr = sheet.iterator();20while (itr.hasNext()) {21Row row = itr.next();22Iterator<Cell> cellIterator = row.cellIterator();23while (cellIterator.hasNext()) {24Cell cell = cellIterator.next();25switch (cell.getCellType()) {26System.out.print(cell.getStringCellValue() + "\t\t");27break;28System.out.print(cell.getNumericCellValue() + "\t\t");29break;30System.out.print(cell.getBooleanCellValue() + "\t\t");31break;32}33}34System.out.println("");35}36}37}38package com.qaprosoft.carina.core.foundation.dataprovider.parser;39import java.io.File;40import java.io.FileInputStream;41import java.io.FileNotFoundException;42import java.io.IOException;43import org.apache.poi.ss.usermodel.Cell;44import org.apache.poi.ss.usermodel.Row;45import org.apache.poi.ss.usermodel.Sheet;46import org.apache.poi.ss.usermodel.Workbook;47import org.apache.poi.ss.usermodel.WorkbookFactory;48public class XLSReader {49public static void main(String[] args) throws IOException {50File f = new File("C:\\Users\\HP\\Desktop\\Test.xlsx");51FileInputStream fis = new FileInputStream(f);52XLSCache cache = new XLSCache();53Workbook wb = cache.getWorkbook(fis, "Test.xlsx");54Sheet sheet = wb.getSheetAt(0);55int rowStart = sheet.getFirstRowNum();

getWorkbook

Using AI Code Generation

1import java.io.File;2import org.testng.annotations.Test;3import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache;4public class Test1 {5public void test1() {6XLSCache xlsCache = new XLSCache();7File file = new File("C:\\Users\\manoj\\Desktop\\test.xlsx");8System.out.println(xlsCache.getWorkbook(file, "Sheet1"));9}10}11import java.io.File;12import org.testng.annotations.Test;13import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache;14public class Test2 {15public void test2() {16XLSCache xlsCache = new XLSCache();17File file = new File("C:\\Users\\manoj\\Desktop\\test.xlsx");18System.out.println(xlsCache.getWorkbook(file, "Sheet2"));19}20}21import java.io.File;22import org.testng.annotations.Test;23import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache;24public class Test3 {25public void test3() {26XLSCache xlsCache = new XLSCache();27File file = new File("C:\\Users\\manoj\\Desktop\\test.xlsx");28System.out.println(xlsCache.getWorkbook(file, "Sheet3"));29}30}31import java.io.File;32import org.testng.annotations.Test;33import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache;34public class Test4 {35public void test4() {36XLSCache xlsCache = new XLSCache();37File file = new File("C:\\Users\\manoj\\Desktop\\test.xlsx");38System.out.println(xlsCache.getWorkbook(file, "Sheet4"));39}40}41import java.io.File;42import org.testng.annotations.Test;43import com.qaprosoft.carina

getWorkbook

Using AI Code Generation

1String workbook = XLSCache.getWorkbook("testData.xlsx");2String workbook = XLSCache.getWorkbook("testData.xlsx");3String workbook = XLSCache.getWorkbook("testData.xlsx");4String workbook = XLSCache.getWorkbook("testData.xlsx");5String workbook = XLSCache.getWorkbook("testData.xlsx");6String workbook = XLSCache.getWorkbook("testData.xlsx");7String workbook = XLSCache.getWorkbook("testData.xlsx");8String workbook = XLSCache.getWorkbook("testData.xlsx");9String workbook = XLSCache.getWorkbook("testData.xlsx");10String workbook = XLSCache.getWorkbook("testData.xlsx");11String workbook = XLSCache.getWorkbook("testData.xlsx");12String workbook = XLSCache.getWorkbook("testData.xlsx");13String workbook = XLSCache.getWorkbook("testData.xlsx");

getWorkbook

Using AI Code Generation

1import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache;2import org.apache.poi.ss.usermodel.*;3import org.testng.Assert;4import org.testng.annotations.Test;5import java.io.File;6import java.io.IOException;7import java.util.Iterator;8import java.util.List;9public class Test1 {10public void test1() throws IOException {11Workbook workbook = XLSCache.getWorkbook(new File("src/test/resources/data/TestData.xlsx"));12Sheet sheet = workbook.getSheet("Sheet1");13Row row = sheet.getRow(0);14Cell cell = row.getCell(0);15String cellValue = cell.getStringCellValue();16Assert.assertEquals(cellValue, "Test Data");17}18}19import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache;20import org.apache.poi.ss.usermodel.*;21import org.testng.Assert;22import org.testng.annotations.Test;23import java.io.File;24import java.io.IOException;25public class Test2 {26public void test2() throws IOException {27Workbook workbook = XLSCache.getWorkbook(new File("src/test/resources/data/TestData.xlsx"));28Sheet sheet = workbook.getSheet("Sheet1");29Row row = sheet.getRow(0);30Cell cell = row.getCell(0);

getWorkbook

Using AI Code Generation

1package com.qaprosoft.carina.demo;2import java.io.IOException;3import java.util.ArrayList;4import org.apache.log4j.Logger;5import org.testng.Assert;6import org.testng.annotations.DataProvider;7import org.testng.annotations.Test;8import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache;9import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSParser;10import com.qaprosoft.carina.core.foundation.utils.Configuration;11public class DemoTest2 {12    private static final Logger LOGGER = Logger.getLogger(DemoTest2.class);13    @DataProvider(name = "DP1", parallel = true)14    public Object[][] dataProvider() throws IOException {15        XLSParser xlsParser = XLSCache.getInstance().getWorkbook(Configuration.get(Configuration.Parameter.TESTDATA_FILE));16        XLSParser xlsSheet = xlsParser.getSheet("Sheet1");17        ArrayList<ArrayList<String>> data = xlsSheet.getData();18        Object[][] dataObjects = new Object[data.size()][];19        for(int i = 0; i < data.size(); i++) {20            Object[] objects = new Object[1];21            for(int j = 0; j < data.get(i).size(); j++) {22                objects[j] = data.get(i).get(j);23            }24            dataObjects[i] = objects;25        }26        return dataObjects;27    }28    @Test(dataProvider = "DP1")29    public void test1(String data) {30        Assert.assertTrue(true);31    }32}33package com.qaprosoft.carina.demo;34import java.io.IOException;35import java.util.ArrayList;36import org.apache.log4j.Logger;37import org.testng.Assert;38import org.testng.annotations.DataProvider;39import org.testng.annotations.Test;40import com.qaprosoft.carina.core.foundation.datapro

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.