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

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

Source:XLSParser.java Github

copy

Full Screen

...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 }...

Full Screen

Full Screen

Source:XLSCache.java Github

copy

Full Screen

...19import java.util.Map;20import java.util.Map.Entry;21import org.apache.poi.ss.usermodel.Workbook;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 } finally...

Full Screen

Full Screen

XLSCache

Using AI Code Generation

copy

Full Screen

1package com.qaprosoft.carina.core.foundation.dataprovider.parser;2import java.io.File;3import java.io.IOException;4import java.util.ArrayList;5import java.util.List;6import java.util.Map;7import org.apache.commons.lang3.StringUtils;8import org.apache.log4j.Logger;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.ss.usermodel.WorkbookFactory;14import org.apache.poi.ss.util.CellRangeAddress;15import org.apache.poi.xssf.usermodel.XSSFSheet;16import org.apache.poi.xssf.usermodel.XSSFWorkbook;17import com.qaprosoft.carina.core.foundation.dataprovider.core.DataContainer;18import com.qaprosoft.carina.core.foundation.dataprovider.core.IDataProvider;19import com.qaprosoft.carina.core.foundation.dataprovider.core.IDataProviderParser;20import com.qaprosoft.carina.core.foundation.dataprovider.core.impl.CSVDataProvider;21import com.qaprosoft.carina.core.foundation.dataprovider.core.impl.XLSDataProvider;22import com.qaprosoft.carina.core.foundation.dataprovider.core.impl.XLSXDataProvider;23import com.qaprosoft.carina.core.foundation.dataprovider.core.impl.XMLDataProvider;24import com.qaprosoft.carina.core.foundation.exception.TestRuntimeException;25import com.qaprosoft.carina.core.foundation.utils.Configuration;26import com.qaprosoft.carina.core.foundation.utils.R;27public class XLSCache implements IDataProviderParser {28 private static final Logger LOGGER = Logger.getLogger(XLSCache.class);29 private static final String XLSX_EXT = ".xlsx";30 private static final String XLS_EXT = ".xls";31 private static final String CSV_EXT = ".csv";32 private static final String XML_EXT = ".xml";33 private static final String XLSX_CACHE_EXT = ".xlsx.cache";34 private static final String XLS_CACHE_EXT = ".xls.cache";35 private static final String CSV_CACHE_EXT = ".csv.cache";36 private static final String XML_CACHE_EXT = ".xml.cache";37 private static final String XLSX_CACHE_PATH = R.CONFIG.get(Configuration.Parameter.CACHE_PATH) + File.separator + "xlsx";38 private static final String XLS_CACHE_PATH = R.CONFIG.get(Configuration.Parameter.CACHE_PATH) + File.separator + "xls";39 private static final String CSV_CACHE_PATH = R.CONFIG.get(Configuration.Parameter.CACHE_PATH) + File.separator + "csv";

Full Screen

Full Screen

XLSCache

Using AI Code Generation

copy

Full Screen

1package com.qaprosoft.carina.demo;2import java.util.HashMap;3import java.util.Map;4import org.testng.annotations.Test;5import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache;6public class XLSCacheTest {7 public void testXLS() throws Exception {8 Object[][] data = XLSCache.getInstance().get("src/test/resources/data/test_data.xls", "test");9 for (Object[] row : data) {10 for (Object cell : row) {11 System.out.print(cell + " ");12 }13 System.out.println();14 }15 Map<String, Integer> columnMapping = new HashMap<String, Integer>();16 columnMapping.put("test", 0);17 columnMapping.put("user", 1);18 columnMapping.put("password", 2);19 data = XLSCache.getInstance().get("src/test/resources/data/test_data.xls", "test", columnMapping);20 for (Object[] row : data) {21 for (Object cell : row) {22 System.out.print(cell + " ");23 }24 System.out.println();25 }26 }27}28package com.qaprosoft.carina.demo;29import java.util.HashMap;30import java.util.Map;31import org.testng.annotations.Test;32import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache;33public class XLSCacheTest {34 public void testXLS() throws Exception {35 Object[][] data = XLSCache.getInstance().get("src/test/resources/data/test_data.xls", "test");36 for (Object[] row : data) {37 for (Object cell : row) {38 System.out.print(cell + " ");39 }40 System.out.println();41 }42 Map<String, Integer> columnMapping = new HashMap<String, Integer>();43 columnMapping.put("test", 0);44 columnMapping.put("user", 1);45 columnMapping.put("password", 2);46 data = XLSCache.getInstance().get("src/test/resources/data/test_data.xls", "test", columnMapping);

Full Screen

Full Screen

XLSCache

Using AI Code Generation

copy

Full Screen

1import java.util.Map;2import org.testng.Assert;3import org.testng.annotations.Test;4import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache;5public class XLSParserTest {6 public void testXLS() {7 Map<String, String> values = XLSCache.getInstance().get("src/test/resources/data/1.xls", "1");

Full Screen

Full Screen

XLSCache

Using AI Code Generation

copy

Full Screen

1import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache;2public class XLSCacheTest {3public static void main(String[] args) {4XLSCache xlsCache = new XLSCache("C:\\Users\\user\\Desktop\\ExcelData.xlsx");5System.out.println(xlsCache.get("Sheet1", "A3"));6System.out.println(xlsCache.get("Sheet1", "A4"));7System.out.println(xlsCache.get("Sheet1", "A5"));8System.out.println(xlsCache.get("Sheet1", "A6"));9System.out.println(xlsCache.get("Sheet1", "A7"));10System.out.println(xlsCache.get("Sheet1", "A8"));11System.out.println(xlsCache.get("Sheet1", "A9"));12System.out.println(xlsCache.get("Sheet1", "A10"));13System.out.println(xlsCache.get("Sheet1", "A11"));14System.out.println(xlsCache.get("Sheet1", "A12"));15System.out.println(xlsCache.get("Sheet1", "A13"));16System.out.println(xlsCache.get("Sheet1", "A14"));17System.out.println(xlsCache.get("Sheet1", "A15"));18System.out.println(xlsCache.get("Sheet1", "A16"));19System.out.println(xlsCache.get("Sheet1", "A17"));20System.out.println(xlsCache.get("Sheet1", "A18"));21System.out.println(xlsCache.get("Sheet1", "A19"));22System.out.println(xlsCache.get("Sheet1", "A20"));23System.out.println(xlsCache.get("Sheet1", "A21"));24System.out.println(xlsCache.get("Sheet1", "A22"));25System.out.println(xlsCache.get("Sheet1", "A23"));26System.out.println(xlsCache.get("Sheet1", "A24"));27System.out.println(xlsCache.get("Sheet1", "A25"));28System.out.println(xlsCache.get("Sheet1", "A26"));29System.out.println(xlsCache.get("Sheet1", "A27"));30System.out.println(xlsCache.get("Sheet1", "A28"));31System.out.println(xlsCache.get("Sheet1", "A29"));32System.out.println(xlsCache.get("Sheet1", "A30"));33System.out.println(xlsCache.get("Sheet1", "A31

Full Screen

Full Screen

XLSCache

Using AI Code Generation

copy

Full Screen

1import org.testng.annotations.Test;2import org.testng.annotations.DataProvider;3import java.util.List;4import java.util.Map;5import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache;6public class Test1 {7 @DataProvider(name = "ExcelDataProvider", parallel = true)8 public Object[][] getExcelData() {9 XLSCache xlsCache = new XLSCache("src/test/resources/data/test_data.xlsx");10 List<Map<String, String>> list = xlsCache.getRecords("Sheet1");11 Object[][] data = new Object[list.size()][];12 for (int i = 0; i < list.size(); i++) {13 Map<String, String> map = list.get(i);14 data[i] = new Object[] { map.get("username"), map.get("password") };15 }16 return data;17 }18 @Test(dataProvider = "ExcelDataProvider")19 public void test(String username, String password) {20 System.out.println("username: " + username + " password: " + password);21 }22}23import org.testng.annotations.Test;24import org.testng.annotations.DataProvider;25import java.util.List;26import java.util.Map;27import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSParser;28public class Test2 {29 @DataProvider(name = "ExcelDataProvider", parallel = true)30 public Object[][] getExcelData() {31 XLSParser xlsParser = new XLSParser("src/test/resources/data/test_data.xlsx");32 List<Map<String, String>> list = xlsParser.getRecords("Sheet1");33 Object[][] data = new Object[list.size()][];34 for (int i = 0; i < list.size(); i++) {35 Map<String, String> map = list.get(i);36 data[i] = new Object[] { map.get("username"), map.get("password") };37 }38 return data;39 }40 @Test(dataProvider = "ExcelDataProvider")41 public void test(String username, String password) {42 System.out.println("username: " + username + " password: " + password);43 }44}

Full Screen

Full Screen

XLSCache

Using AI Code Generation

copy

Full Screen

1import java.io.IOException;2import java.util.List;3import org.testng.annotations.Test;4import com.qaprosoft.carina.core.foundation.dataprovider.parser.XLSCache;5public class ReadExcelData {6 public void readData() throws IOException {7 XLSCache xlsCache = new XLSCache("D:\\TestData.xlsx");8 List<String> list = xlsCache.get("Sheet1");9 System.out.println(list);10 }11}12Your name to display (optional):13Your name to display (optional):

Full Screen

Full Screen

XLSCache

Using AI Code Generation

copy

Full Screen

1public class XLSCache {2 public static void main(String[] args) {3 XLSCache x = new XLSCache();4 x.readXLS();5 }6 public void readXLS() {7 Map<String, List<Map<String, String>>> data = new XLSParser().parseXLS("C:\\Users\\user\\Desktop\\test_data.xlsx");8 System.out.println(data);9 }10}11public class XLSParser {12 public static void main(String[] args) {13 XLSParser x = new XLSParser();14 x.readXLS();15 }16 public void readXLS() {17 Map<String, List<Map<String, String>>> data = new XLSParser().parseXLS("C:\\Users\\user\\Desktop\\test_data.xlsx");18 System.out.println(data);19 }20}21public class XLSParser {22 public static void main(String[] args) {23 XLSParser x = new XLSParser();24 x.readXLS();25 }26 public void readXLS() {27 Map<String, List<Map<String, String>>> data = new XLSParser().parseXLS("C:\\Users\\user\\Desktop\\test_data.xlsx");28 System.out.println(data);29 }30}31public class XLSParser {32 public static void main(String[] args) {33 XLSParser x = new XLSParser();34 x.readXLS();35 }36 public void readXLS() {37 Map<String, List<Map<String, String>>> data = new XLSParser().parseXLS("C:\\Users\\user\\Desktop\\test_data.xlsx");38 System.out.println(data);39 }40}41public class XLSParser {

Full Screen

Full Screen

XLSCache

Using AI Code Generation

copy

Full Screen

1public class XLSCacheTest {2 public static void main(String[] args) {3 XLSCache xlsCache = new XLSCache("src/test/resources/data/TestData.xlsx");4 String[][] data = xlsCache.getData("Sheet1");5 System.out.println(data[0][0]);6 }7}8public class XLSCacheTest {9 public static void main(String[] args) {10 XLSCache xlsCache = new XLSCache("src/test/resources/data/TestData.xlsx");11 String[][] data = xlsCache.getData("Sheet1");12 System.out.println(data[0][0]);13 }14}15public class XLSCacheTest {16 public static void main(String[] args) {17 XLSCache xlsCache = new XLSCache("src/test/resources/data/TestData.xlsx");18 String[][] data = xlsCache.getData("Sheet1");19 System.out.println(data[0][0]);20 }21}22public class XLSCacheTest {23 public static void main(String[] args) {24 XLSCache xlsCache = new XLSCache("src/test/resources/data/TestData.xlsx");25 String[][] data = xlsCache.getData("Sheet1");26 System.out.println(data[0][0]);27 }28}29public class XLSCacheTest {30 public static void main(String[] args) {31 XLSCache xlsCache = new XLSCache("src/test/resources/data/TestData.xlsx");32 String[][] data = xlsCache.getData("Sheet1");33 System.out.println(data[0][0]);34 }35}36public class XLSCacheTest {37 public static void main(String[] args) {38 XLSCache xlsCache = new XLSCache("src/test/resources/data/TestData.xlsx");

Full Screen

Full Screen

XLSCache

Using AI Code Generation

copy

Full Screen

1public class XLSCacheExample {2 public static void main(String[] args) throws Exception {3 XLSCache xlsCache = new XLSCache("C:\\Users\\test\\Desktop\\data.xlsx");4 Map<String, Map<String, String>> data = xlsCache.getData();5 System.out.println(data);6 xlsCache.close();7 }8}9{Sheet1={A1=1, B1=2, C1=3, A2=4, B2=5, C2=6, A3=7, B3=8, C3=9, A4=10, B4=11, C4=12}}

Full Screen

Full Screen

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:

YouTube

You could also refer to video tutorials over LambdaTest YouTube channel to get step by step demonstration from industry experts.

Run Carina automation tests on LambdaTest cloud grid

Perform automation testing on 3000+ real desktop and mobile devices online.

... View sample repo... View DocumentationSign up Free
›
_

Most used methods in XLSCache

Test Your Web Or Mobile Apps On 3000+ Browsers

Signup for free

Try LambdaTest Now !!

Get 100 minutes of automation test minutes FREE!!

...
Sign up with GoogleSign up with Email
Next-Gen App & Browser Testing Cloud

Was this article helpful?

Helpful

NotHelpful