List of usage examples for org.apache.poi.ss.usermodel Sheet getFirstRowNum
int getFirstRowNum();
From source file:au.com.onegeek.lambda.parser.Excel2SeleniumParser.java
License:Apache License
private void parse(InputStream stream) throws CannotCompileException, NotFoundException, CannotCreateTestClassException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException { logger.debug("Parsing..."); if (this.dataMap != null && this.tests != null) { return;//from w w w .java2s .co m } this.dataMap = new ArrayList<Map<String, Object>>(); this.tests = new ArrayList<Class<Test>>(); Workbook workbook = null; try { workbook = new XSSFWorkbook(stream); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } logger.debug("workbook" + workbook.toString()); for (int i = 0; i < workbook.getNumberOfSheets(); i++) { Sheet sheet = workbook.getSheetAt(i); if (sheet.getSheetName().startsWith("data")) { // parse key\value pairs HashMap<String, Object> map = new HashMap<String, Object>(); dataMap.add(map); boolean done = false; Row row = sheet.getRow(sheet.getFirstRowNum()); while (!done && row != null && row.getPhysicalNumberOfCells() > 0) { // TODO: parse numerics correctly (i.e. don't add decimal points if not needed) String key = (String) XslxUtil.objectFrom(workbook, row.getCell(0)); String value = null; try { value = (String) XslxUtil.objectFrom(workbook, row.getCell(1)); logger.debug("Adding variable to map: " + key + ":" + value); map.put(key, value); row = sheet.getRow(row.getRowNum() + 1); if (row == null || (row.getRowNum() == sheet.getLastRowNum() + 1)) { done = true; } } catch (NullPointerException e) { //throw new CannotCreateVariableException("No value found for variable '" + key + "' in dataset: " + sheet.getSheetName()); done = true; } } } } JavassistTestBuilderImpl builder = JavassistTestBuilderImpl.getInstance(); // Parse Test sheets into Test objects for (int s = 0; s < workbook.getNumberOfSheets(); s++) { Sheet sheet = workbook.getSheetAt(s); int i = 0; // Ignore data sheets if (sheet.getSheetName().startsWith("suite")) { int maxRows = sheet.getPhysicalNumberOfRows(); int currentRow = sheet.getFirstRowNum(); logger.debug("Nr rows in sheet: " + maxRows); // Create Test Class String testCaseName = "Test" + Excel2SeleniumParser.toCamelCase(sheet.getSheetName()); logger.debug("Creating Test class with name: " + testCaseName); builder.makeTestClass(testCaseName, this.dataMap); boolean testCaseInProgress = false; boolean dataProviderAdded = false; // Get First row, containing the test name and the data to be injected while (i < maxRows) { logger.debug("i: " + i); logger.debug("currentRow: " + currentRow); Row row = sheet.getRow(currentRow); TestCommand command = null; // Check for empty row if (row != null && row.getPhysicalNumberOfCells() != 0) { i++; // Get Cells Iterator<Cell> iterator = row.cellIterator(); while (iterator.hasNext()) { Cell cell = iterator.next(); String cellValue = (cell == null || cell.toString() == "") ? "" : XslxUtil.objectFrom(workbook, cell).toString(); logger.debug("Cell: " + cellValue); if (cellValue.startsWith("test")) { logger.debug("Test case found: " + cellValue + ". Creating Test Case"); // Create new Test CASE try { builder.addTest(cellValue); testCaseInProgress = true; dataProviderAdded = false; } catch (CannotModifyTestMethodException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test Class as there was a variable not found in test assertion. Embedded exception: " + e.getMessage()); } catch (VariableNotFoundException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test Class as there was a variable not found in test assertion. Embedded exception: " + e.getMessage()); } break; } else { if (command == null & !cellValue.equals("")) { logger.debug("Command found: " + cellValue + ". Creating new TestCommand"); command = new TestCommand(cellValue); } else if (!cellValue.equals("")) { logger.debug("Command argument found: " + cellValue); command.addParameter(cellValue); } } } } else { // Blank row could mean a test case has just been completed // Complete last test case by adding a data provider if (testCaseInProgress && !dataProviderAdded) { try { logger.debug("In Progress Test Case now being closed off and added to class..."); builder.addDataProvider(); dataProviderAdded = true; logger.debug("In Progress Test Case now closed off!"); } catch (CannotCreateDataProviderException e) { throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } } } try { if (command != null) { logger.debug("Adding command to method"); builder.appendTestToLastMethod(command); } } catch (CannotModifyTestMethodException e) { throw new CannotCreateTestClassException("Unable to add Test Case '" + command.toString() + "' to Test Class. Embedded exception: " + e.getMessage()); } catch (VariableNotFoundException e) { throw new CannotCreateTestClassException("Unable to add Test Case '" + command.toString() + "' to Test Class as a variable was not found. Embedded exception: " + e.getMessage()); } currentRow++; } // Blank row could mean a test case has just been completed // Complete last test case by adding a data provider logger.debug( "End of rows...Checking if In Progress Test Case now being closed off and added to class..."); if (testCaseInProgress && !dataProviderAdded) { logger.debug(" In Progress Test Case now being closed off and added to class..."); try { builder.addDataProvider(); dataProviderAdded = true; logger.debug("In Progress Test Case now closed off!"); } catch (CannotCreateDataProviderException e) { throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } } if (testCaseInProgress) { logger.debug("Generating class file"); try { this.tests.add(builder.getCreatedClass()); } catch (CannotModifyTestMethodException e) { e.printStackTrace(); throw new CannotCreateTestClassException( "Could not create Test case as a DataProvider for the method could not be created. Embedded exception: " + e.getMessage()); } testCaseInProgress = false; } } } try { stream.close(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } logger.info("Looking at our classes..."); // Look at the Test Objects for (Class<Test> clazz : tests) { logger.info("Class: " + clazz.getName()); for (Method m : clazz.getMethods()) { logger.info("Method: " + m); if (m.getName().equalsIgnoreCase("testRetailDataProvider")) { logger.info("invoking data provider"); Test test = clazz.newInstance(); Object[][] data = (Object[][]) m.invoke(test); for (Object[] obs : data) { for (Object o : obs) { logger.info("data value: " + o); } } } } } }
From source file:cn.edu.zjnu.acm.judge.util.excel.ExcelUtil.java
License:Apache License
private static <T> List<T> parse(Workbook workbook, FormulaEvaluator evaluator, Class<T> type, Locale locale) { MetaInfo metaInfo = MetaInfo.forType(type, locale); Sheet sheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); Iterator<Row> rows = sheet.rowIterator(); if (!rows.hasNext()) { return Collections.emptyList(); }/*w w w . ja v a 2s . c o m*/ Row firstRow = rows.next(); Map<Integer, String> columnIndexToFieldName = Maps.newHashMapWithExpectedSize(metaInfo.size()); for (Iterator<Cell> it = firstRow.cellIterator(); it.hasNext();) { Cell cell = it.next(); JsonElement jsonElement = parseAsJsonElement(cell, evaluator); if (jsonElement != null) { Field field = metaInfo.getField(jsonElement.getAsString()); if (field != null) { String name = field.getName(); int index = cell.getColumnIndex(); columnIndexToFieldName.put(index, name); } } } if (columnIndexToFieldName.isEmpty()) { return Collections.emptyList(); } List<T> result = new ArrayList<>(sheet.getLastRowNum() - sheet.getFirstRowNum()); while (rows.hasNext()) { result.add(parseRow(evaluator, rows.next(), columnIndexToFieldName, type)); } return result; }
From source file:com.actelion.research.spiritapp.ui.util.POIUtils.java
License:Open Source License
public static void autoSizeColumns(Sheet sheet, int maxColWidth, boolean resizeHeight) { ListHashMap<Integer, Integer> col2lens = new ListHashMap<>(); for (int row = sheet.getFirstRowNum(); row <= sheet.getLastRowNum(); row++) { Row r = sheet.getRow(row);/* w w w . j a va 2s .com*/ if (r == null || r.getFirstCellNum() < 0) continue; short maxH = 0; for (int col = r.getFirstCellNum(); col <= r.getLastCellNum(); col++) { Cell c = r.getCell(col); if (c == null || (c.getCellType() != Cell.CELL_TYPE_STRING && c.getCellType() != Cell.CELL_TYPE_NUMERIC)) continue; Font font = sheet.getWorkbook().getFontAt(c.getCellStyle().getFontIndex()); String s = c.getCellType() == Cell.CELL_TYPE_STRING ? c.getStringCellValue() : "" + c.getNumericCellValue(); String[] lines = MiscUtils.split(s, "\n"); int maxLen = 1; for (int i = 0; i < lines.length; i++) { maxLen = Math.max(lines[i].length(), maxLen); } if (font.getFontHeightInPoints() < 12) { col2lens.add(col, 700 + maxLen * (font.getFontHeightInPoints() + (font.getBoldweight() > 500 ? 1 : 0)) * 20); } maxH = (short) Math.max(maxH, 50 + lines.length * (font.getFontHeight() * 1.2)); } if (resizeHeight) r.setHeight(maxH); } for (int col : col2lens.keySet()) { List<Integer> lens = col2lens.get(col); Collections.sort(lens); int len = lens.get(lens.size() - 1); if (lens.size() > 10 && lens.get(lens.size() - 1) > 2 * lens.get(lens.size() - 2)) { len = lens.get(lens.size() - 2); } sheet.setColumnWidth(col, Math.max(Math.min((int) (len * 1.25), maxColWidth > 0 ? maxColWidth : 300000), 1500)); } }
From source file:com.canoo.webtest.plugins.exceltest.ExcelStructureFilter.java
License:Open Source License
public void doExecute() throws Exception { final Workbook excelWorkbook = getExcelWorkbook(); final DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder(); final Document doc = builder.newDocument(); final Element root = doc.createElement("excelWorkbook"); doc.appendChild(root);//from www . ja v a2 s. c om //root.setAttribute("backup", String.valueOf(excelWorkbook.getBackupFlag())); root.setAttribute("backup", String.valueOf(Boolean.FALSE)); root.setAttribute("numberOfFonts", String.valueOf(excelWorkbook.getNumberOfFonts())); root.setAttribute("numberOfCellStyles", String.valueOf(excelWorkbook.getNumCellStyles())); root.setAttribute("numberOfNames", String.valueOf(excelWorkbook.getNumberOfNames())); final Element sheets = doc.createElement("sheets"); for (int i = 0; i < excelWorkbook.getNumberOfSheets(); i++) { final Sheet sheetAt = excelWorkbook.getSheetAt(i); final Element sheetElement = doc.createElement("sheet"); sheetElement.setAttribute("index", String.valueOf(i)); sheetElement.setAttribute("name", excelWorkbook.getSheetName(i)); sheetElement.setAttribute("firstRow", String.valueOf(sheetAt.getFirstRowNum())); sheetElement.setAttribute("lastRow", String.valueOf(sheetAt.getLastRowNum())); sheetElement.setAttribute("physicalRows", String.valueOf(sheetAt.getPhysicalNumberOfRows())); sheetElement.setAttribute("defaultRowHeight", String.valueOf(sheetAt.getDefaultRowHeight())); sheetElement.setAttribute("defaultColumnWidth", String.valueOf(sheetAt.getDefaultColumnWidth())); sheetElement.setAttribute("fitToPage", String.valueOf(sheetAt.getFitToPage())); sheets.appendChild(sheetElement); } root.appendChild(sheets); final StringWriter sw = new StringWriter(); writeXmlFile(doc, sw); ContextHelper.defineAsCurrentResponse(getContext(), sw.toString(), "text/xml", getClass()); }
From source file:com.clican.pluto.dataprocess.engine.processes.ExcelProcessor.java
License:LGPL
public void readExcel(ProcessorContext context, ExcelExecBean execBean) throws Exception { InputStream is = new AutoDecisionResource(execBean.getResource()).getInputStream(); try {//from w w w . jav a 2 s . com Workbook book = WorkbookFactory.create(is); Sheet sheet = book.getSheet(execBean.getSheetName()); List<Map<String, Object>> result = new ArrayList<Map<String, Object>>(); List<String> names = new ArrayList<String>(); Map<String, String> typeMap = execBean.getTypeMap(); int firstRow = sheet.getFirstRowNum(), lastRow = sheet.getLastRowNum(); for (int rowIdx = firstRow; rowIdx < lastRow; rowIdx++) { Row excelRow = sheet.getRow(rowIdx); short minColIx = excelRow.getFirstCellNum(); short maxColIx = excelRow.getLastCellNum(); Map<String, Object> row = new HashMap<String, Object>(); for (int colIdx = minColIx; colIdx < maxColIx; colIdx++) { Cell cell = excelRow.getCell(colIdx, Row.CREATE_NULL_AS_BLANK); if (rowIdx == 0) { names.add(cell.getStringCellValue()); } else { String type = null; if (names.size() > colIdx) { type = typeMap.get(names.get(colIdx)); } if (StringUtils.isNotEmpty(type)) { if (type.equals("string")) { cell.setCellType(Cell.CELL_TYPE_STRING); row.put(names.get(colIdx), cell.getStringCellValue().trim()); } else if (type.equals("double")) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); row.put(names.get(colIdx), cell.getNumericCellValue()); } else if (type.equals("int")) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); row.put(names.get(colIdx), (int) cell.getNumericCellValue()); } else if (type.equals("date")) { row.put(names.get(colIdx), cell.getDateCellValue()); } else { throw new DataProcessException("??Excel?"); } } } } if (rowIdx != 0) { result.add(row); } } context.setAttribute(execBean.getResultName(), result); } finally { if (is != null) { is.close(); } } }
From source file:com.cordys.coe.ac.fileconnector.utils.ExcelRead.java
License:Apache License
/** * Validates the reader-config.xml with the Excel file * * @param vcConfig The validator configuration object. * @param filename Name of the Excel file. * @param dDoc Document conatins the request. * @param iResultNode The record XML structure root node, or zero, if only validation is needed. * @param sheetno Sheet index of the Excel file. * @param startrow row index from which data to be read. * @param endrow row index upto which data to be read. * @param lErrorList LinkedList contains all the errors. */// w w w . j av a 2 s . c o m public static void validate(ValidatorConfig vcConfig, String filename, Document dDoc, int iResultNode, int sheetno, int startrow, int endrow, List<FileException> lErrorList) { try { setRecordsread(0); setEndoffile(false); Workbook book = null; Sheet sheet = null; Row row; FileInputStream fileinp = null; //String sRecordName = vcConfig.mConfigMap.get("excel").lRecordList.get(0).sRecordName; int sheetindex; int noofsheets; if (filename == null) { throw new FileException(LogMessages.PLEASE_PROVIDE_FILE_NAME); } File file = new File(filename); fileinp = new FileInputStream(filename); if (file.exists()) { if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xls")) { try { book = (Workbook) new HSSFWorkbook(fileinp); } catch (IOException ex) { Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex); } } else if (file.getName().substring(file.getName().lastIndexOf(".") + 1).equalsIgnoreCase("xlsx")) { try { book = new XSSFWorkbook(fileinp); } catch (IOException ex) { Logger.getLogger(ExcelRead.class.getName()).log(Level.SEVERE, null, ex); } } else { //ERROR fileinp.close(); throw new FileException(LogMessages.INPUT_FILE_NOT_SUPPORTED); } } else { //ERROR fileinp.close(); throw new FileException(LogMessages.FILE_NOT_FOUND); } if (sheetno != -1) { sheetindex = sheetno; noofsheets = sheetindex + 1; } else { sheetindex = 0; noofsheets = book.getNumberOfSheets(); } //check whether the sheetindex exists or not for (; sheetindex < noofsheets; sheetindex++) { if (sheetindex >= book.getNumberOfSheets()) { //no sheet throw new FileException(LogMessages.NO_SHEET_FOUND, sheetindex); } sheet = book.getSheetAt(sheetindex); if (sheet == null) { throw new FileException(LogMessages.NO_SHEET_FOUND, sheetindex); } } //validate columns //get last column index for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); if (maxcol < row.getLastCellNum()) { maxcol = row.getLastCellNum(); } } //check column index in reader-config ListIterator fieldslist = vcConfig.mConfigMap.get("excel").lRecordList.get(0).lFieldList.listIterator(); while (fieldslist.hasNext()) { FieldType excelfields = (FieldType) fieldslist.next(); try { if (Short.parseShort(excelfields.sColumnIndex) < 0 || Short.parseShort(excelfields.sColumnIndex) >= maxcol) { throw new FileException(LogMessages.COLUMN_INDEX_NOT_FOUND, excelfields.sColumnIndex, (maxcol - 1)); } } catch (NumberFormatException ex) { throw new FileException(ex, LogMessages.COLUMN_INDEX_NOT_VALID, excelfields.sColumnIndex); } } if (endrow == -1) { endrow = sheet.getLastRowNum(); if (startrow == -1) { startrow = 0; } } else { endrow = startrow + endrow - 1; if (endrow > sheet.getLastRowNum()) { endrow = sheet.getLastRowNum(); } } setRecordsread(endrow - startrow + 1); } catch (IOException ex) { lErrorList.add(new FileException(ex, LogMessages.IOEXCEPTION_WHILE_READING_FILE, filename)); } catch (FileException ex) { lErrorList.add(ex); } }
From source file:com.dataart.spreadsheetanalytics.engine.ConverterUtils.java
License:Apache License
/** * Gets an instance of a Workbook ({@link ConverterUtils#newWorkbook(InputStream)}, creates copy of original file, * clears all the cell values, but preserves formatting. *///from w w w .j av a 2s. c om static Workbook clearContent(final Workbook book) { ByteArrayOutputStream originalOut = new ByteArrayOutputStream(); try { book.write(originalOut); } catch (IOException e) { throw new CalculationEngineException(e); } InputStream originalIn = new ByteArrayInputStream(copyOf(originalOut.toByteArray(), originalOut.size())); Workbook w = ConverterUtils.newWorkbook(originalIn); Sheet s = w.getSheetAt(0); //TODO: only one sheet is supported for (int i = s.getFirstRowNum(); i <= s.getLastRowNum(); i++) { Row r = s.getRow(i); if (r == null) { continue; } for (int j = r.getFirstCellNum(); j <= r.getLastCellNum(); j++) { Cell c = r.getCell(j); if (c == null) { continue; } c.setCellType(CELL_TYPE_BLANK); } } return w; }
From source file:com.dataart.spreadsheetanalytics.engine.DataModelConverters.java
License:Apache License
/** * For given {@link Workbook} does convert everything to new {@link DataModel} structure. * Does copy all supported fields (for supported fields see {@link DataModel} class. *//*from ww w. j ava2 s . com*/ static IDataModel toDataModel(final Workbook workbook) { if (workbook == null) { return null; } //add custom functions information workbook.addToolPack(getUdfFinder()); Sheet s = workbook.getSheetAt(0); //TODO: only one sheet is supported if (s == null) { return null; } IDataModel dm = new DataModel(s.getSheetName()); for (int i = s.getFirstRowNum(); i <= s.getLastRowNum(); i++) { Row r = s.getRow(i); if (r == null) { continue; } DmRow row = new DmRow(i); dm.setRow(i, row); for (int j = r.getFirstCellNum(); j < r.getLastCellNum(); j++) { Cell c = r.getCell(j); if (c == null) { continue; } DmCell cell = new DmCell(); row.setCell(j, cell); cell.setAddress(new CellAddress(dm.getDataModelId(), A1Address.fromRowColumn(i, j))); cell.setContent(ConverterUtils.resolveCellValue(c)); } } EvaluationWorkbook evaluationWbook = ConverterUtils.newEvaluationWorkbook(workbook); for (int nIdx = 0; nIdx < workbook.getNumberOfNames(); nIdx++) { Name name = workbook.getNameAt(nIdx); String reference = name.getRefersToFormula(); if (reference == null) { continue; } if (A1Address.isAddress(removeSheetFromNameRef(reference))) { dm.setNamedAddress(name.getNameName(), A1Address.fromA1Address(removeSheetFromNameRef(reference))); } else if (isFormula(reference, evaluationWbook)) { dm.setNamedValue(name.getNameName(), new CellValue(FORMULA_PREFIX + reference)); } else { dm.setNamedValue(name.getNameName(), CellValue.from(reference)); } } return dm; }
From source file:com.dataart.spreadsheetanalytics.engine.DataSetConverters.java
License:Apache License
/** * Converts a {@link Workbook} to new {@link IDataSet}. * Ignores empty rows./*from w w w . j a v a2s. co m*/ * * @throws {@link CalculationEngineException} if {@link Workbook} contains formulas or Cell references. */ static IDataSet toDataSet(final Workbook workbook) { Sheet sheet = workbook.getSheetAt(0); //TODO: this works only for single sheet documents DataSet dataSet = new DataSet(sheet.getSheetName()); for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { IDsRow dsRow = dataSet.addRow(); Row row = sheet.getRow(i); for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) { Cell wbCell = row.getCell(j); if (wbCell != null && wbCell.getCellType() == Cell.CELL_TYPE_FORMULA) { throw new CalculationEngineException("DataSet should not contain formulas"); } IDsCell cell = dsRow.addCell(); cell.setValue(ConverterUtils.resolveCellValue(wbCell)); } } return dataSet; }
From source file:com.exilant.exility.core.XLSReader.java
License:Open Source License
/** * Purpose of this method to read rows from given Excel Sheet. * //from w w w .ja v a2s . c om * @param sheet * an Instance of .ss.usermodel.Sheet class from POI apache. * @return -1 if fail to read sheet else number of columns read successfully * from the sheet. * @throws ExilityException */ public int readASheet(Sheet sheet) throws ExilityException { int nonEmptyFirstRowIdx = 0; int lastRowIdx = 0; int nbrPhysicalRows = sheet.getPhysicalNumberOfRows(); String sheetName = sheet.getSheetName(); if (nbrPhysicalRows < 2) { Spit.out(sheetName + XLSReader.INSUFFICIENT_DATA_ROWS); return -1; } try { nonEmptyFirstRowIdx = sheet.getFirstRowNum(); lastRowIdx = sheet.getLastRowNum(); /* * For checking to valid header.First row must be header. */ Row headerRow = sheet.getRow(nonEmptyFirstRowIdx); int nbrCol = headerRow.getPhysicalNumberOfCells(); for (int colIdx = 0; colIdx < nbrCol; colIdx++) { Cell hCell = headerRow.getCell(colIdx); if (hCell == null || hCell.getCellType() == Cell.CELL_TYPE_BLANK) { Spit.out("Error--->Found blank column " + (colIdx + 1) + " in Sheet " + sheetName + XLSReader.INVALID_HEADER); this.columnsData.clear(); return -1; } String columnName = hCell.getStringCellValue(); this.setDataType(columnName, colIdx); } } catch (Exception e) { Spit.out(sheetName + XLSReader.INVALID_HEADER); Spit.out(e); return -1; } int nbrColumnsInARow = this.columnsData.size(); /* * Loop starts with second data row that is first row(header as column * name) excluded. */ Spit.out(sheetName + ":\n"); for (int rowIdx = (nonEmptyFirstRowIdx + 1); rowIdx <= lastRowIdx; rowIdx++) { Row row = sheet.getRow(rowIdx); if (row == null) { Spit.out(XLSReader.SKIP_BLANK_ROW + rowIdx); continue; } /** * readARow() will throws ExilityException if something goes wrong. */ this.readARow(row, nbrColumnsInARow); } return this.columnsData.size(); }