List of usage examples for org.apache.poi.ss.usermodel Sheet getFirstRowNum
int getFirstRowNum();
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override public List<LinkedList<Object>> importVendorSpecificFuelLog(InputStream is, LinkedHashMap<String, String> vendorSpecificColumns, Long vendor, HashMap<String, Object> additionalVendorData) throws Exception { List<LinkedList<Object>> data = new ArrayList<LinkedList<Object>>(); try {/*from ww w. ja va 2s. com*/ POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); Sheet sheet = wb.getSheetAt(0); Row titleRow = sheet.getRow(sheet.getFirstRowNum()); LinkedHashMap<String, Integer> orderedColIndexes = getOrderedColumnIndexes(titleRow, vendorSpecificColumns); Set<Entry<String, Integer>> keySet = orderedColIndexes.entrySet(); System.out.println("Physical number of rows in Excel = " + sheet.getPhysicalNumberOfRows()); System.out.println("While reading values from vendor specific Excel Sheet: "); Map criterias = new HashMap(); criterias.put("id", vendor); FuelVendor fuelVendor = genericDAO.findByCriteria(FuelVendor.class, criterias, "name", false).get(0); boolean stopParsing = false; for (int i = titleRow.getRowNum() + 1; !stopParsing && i <= sheet.getPhysicalNumberOfRows() - 1; i++) { LinkedList<Object> rowObjects = new LinkedList<Object>(); rowObjects.add(fuelVendor.getName()); rowObjects.add(fuelVendor.getCompany().getName()); Row row = sheet.getRow(i); Iterator<Entry<String, Integer>> iterator = keySet.iterator(); while (iterator.hasNext()) { Entry<String, Integer> entry = iterator.next(); // corresponding column not found in actual column list, find in additionalVendorData if (entry.getValue() == -1) { System.out.println("Additional vendor data = " + additionalVendorData); System.out.println("Column " + entry.getKey() + " not found in Vendor Excel, checking in additionalVendorData"); Object cellValueObj = additionalVendorData.get(entry.getKey()); if (cellValueObj != null) { rowObjects.add(cellValueObj); } else { rowObjects.add(StringUtils.EMPTY); } continue; } Object cellValueObj = getCellValue((HSSFCell) row.getCell(entry.getValue()), true); if (cellValueObj != null && cellValueObj.toString().equalsIgnoreCase("END_OF_DATA")) { System.out.println("Received END_OF_DATA"); stopParsing = true; rowObjects.clear(); break; } rowObjects.add(cellValueObj); } if (!stopParsing) { data.add(rowObjects); } } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } return data; }
From source file:com.primovision.lutransport.service.ImportMainSheetServiceImpl.java
@Override public List<LinkedList<Object>> importTollCompanySpecificTollTag(InputStream is, LinkedHashMap<String, String> tollCompanySpecificColumns, Long tollCompanyId) throws Exception { List<LinkedList<Object>> data = new ArrayList<LinkedList<Object>>(); try {/*from www . ja v a 2 s . c o m*/ POIFSFileSystem fs = new POIFSFileSystem(is); HSSFWorkbook wb = new HSSFWorkbook(fs); Sheet sheet = wb.getSheetAt(0); Row titleRow = sheet.getRow(sheet.getFirstRowNum()); LinkedHashMap<String, Integer> orderedColIndexes = getOrderedColumnIndexes(titleRow, tollCompanySpecificColumns); Set<Entry<String, Integer>> keySet = orderedColIndexes.entrySet(); System.out.println("Physical number of rows in Excel = " + sheet.getPhysicalNumberOfRows()); System.out.println("While reading values from vendor specific Excel Sheet: "); Map criterias = new HashMap(); criterias.put("id", tollCompanyId); TollCompany tollCompany = genericDAO.findByCriteria(TollCompany.class, criterias, "name", false).get(0); //boolean stopParsing = false; for (int i = titleRow.getRowNum() + 1; i <= sheet.getPhysicalNumberOfRows() - 1; i++) { Row row = sheet.getRow(i); Object firstCellValueObj = getCellValue((HSSFCell) row.getCell(0), true); if (firstCellValueObj != null && firstCellValueObj.toString().equalsIgnoreCase("END_OF_DATA")) { System.out.println("Received END_OF_DATA"); break; } LinkedList<Object> rowObjects = new LinkedList<Object>(); rowObjects.add(tollCompany.getName()); /*// TODO: For now, need to get logic String company = StringUtils.substringAfterLast(tollCompany.getName(), " "); company = StringUtils.defaultIfEmpty(company, "LU"); rowObjects.add(company);*/ rowObjects.add(tollCompany.getCompany().getName()); Iterator<Entry<String, Integer>> iterator = keySet.iterator(); while (iterator.hasNext()) { Entry<String, Integer> entry = iterator.next(); if (entry.getValue() == -1) { // corresponding column not found rowObjects.add(StringUtils.EMPTY); continue; } Object cellValueObj = getCellValue((HSSFCell) row.getCell(entry.getValue()), true); if (cellValueObj != null) { System.out.println("Adding " + cellValueObj.toString()); } else { System.out.println("Adding NULL"); } rowObjects.add(cellValueObj); } data.add(rowObjects); } } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } return data; }
From source file:com.projectswg.tools.SwgExcelConverter.java
License:Open Source License
public SWGFile convert(Sheet sheet) { Row header = sheet.getRow(sheet.getFirstRowNum()); if (header == null) return null; int headerNum = header.getRowNum(); // Create base datatable iff SWGFile swgFile = new SWGFile("DTII"); swgFile.addForm("0001"); // Create individual iff info int columns = createTableColumnData(swgFile, header); String[] types = createTableTypeData(swgFile, sheet.getRow(headerNum + 1), columns); if (types == null) return null; int rows = sheet.getPhysicalNumberOfRows(); List<DatatableRow> rowList = new ArrayList<>(); for (int i = headerNum + 2; i < rows; i++) { rowList.add(getDataTableRow(sheet.getRow(i), columns, types)); }/*w ww.j ava 2s . c o m*/ createTableRowData(swgFile, rowList); return swgFile; }
From source file:com.tecacet.jflat.excel.PoiExcelReader.java
License:Apache License
protected void readSheet(Sheet sheet, FlatFileReaderCallback<T> callback) { for (int rowIndex = sheet.getFirstRowNum(); rowIndex <= sheet.getLastRowNum(); rowIndex++) { Row row = sheet.getRow(rowIndex); if (rowIndex < skipLines + sheet.getFirstRowNum()) { continue; }/* w ww . ja v a 2s . co m*/ String[] tokens = readRow(row); T bean = rowMapper.getRow(tokens, rowIndex + 1); callback.processRow(rowIndex, tokens, bean); } }
From source file:csv.impl.ExcelReader.java
License:Open Source License
/** * Select the given sheet to be read from. * @param sheet sheet to be selected//from www .j a v a 2s . c o m * @return sheet selected */ public Sheet selectSheet(Sheet sheet) { if (this.sheet != sheet) { this.sheet = sheet; firstRow = sheet.getFirstRowNum(); rowNum = firstRow; lastRow = sheet.getLastRowNum(); currentRow = null; } return this.sheet; }
From source file:de.bund.bfr.knime.pmmlite.io.XlsReader.java
License:Open Source License
private Map<String, Integer> getColumns(Sheet sheet) { Map<String, Integer> columns = new LinkedHashMap<>(); Row firstRow = sheet.getRow(sheet.getFirstRowNum()); if (firstRow == null) { return columns; }//from w ww . ja v a2 s . com for (int i = firstRow.getFirstCellNum(); i <= firstRow.getLastCellNum(); i++) { String name = getData(firstRow.getCell(i)); if (name != null) { columns.put(name, i); } } return columns; }
From source file:de.interactive_instruments.ShapeChange.SBVR.SbvrRuleLoader.java
License:Open Source License
/** * @param sbvrXls/*w ww. j a v a2s. c o m*/ * @return mapping of schema package name to SBVR rules that apply to * classes in this schema * * <ul> * <li>key: class name</li> * <li>value: mapping of schema package name to SBVR rule info * <ul> * <li>key: schema package name ( * {@value #UNSPECIFIED_SCHEMA_PACKAGE_NAME} if no schema package * name has been provided)</li> * <li>value: list of SBVR rules that apply to classes in that * schema (the list is sorted according to lexical order on a) the * class name and b) the rule text)</li> * </ul> * </ul> */ private TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>> parseSBVRRuleInfos(Workbook sbvrXls) { TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>> rules = new TreeMap<String, TreeMap<String, List<SbvrRuleInfo>>>(); if (sbvrXls == null) return null; Sheet rulesSheet = null; for (int i = 0; i < sbvrXls.getNumberOfSheets(); i++) { String sheetName = sbvrXls.getSheetName(i); if (sheetName.equalsIgnoreCase("Constraints")) { rulesSheet = sbvrXls.getSheetAt(i); break; } } if (rulesSheet == null) { result.addError(this, 3); return null; } // read header row to determine which columns contain relevant // information Map<String, Integer> fieldIndexes = new HashMap<String, Integer>(); Row header = rulesSheet.getRow(rulesSheet.getFirstRowNum()); if (header == null) { result.addError(this, 4); return null; } boolean classNameFound = false; boolean commentsFound = false; boolean ruleNameFound = false; boolean ruleTextFound = false; boolean schemaPackageFound = false; for (short i = header.getFirstCellNum(); i < header.getLastCellNum(); i++) { Cell c = header.getCell(i, Row.RETURN_BLANK_AS_NULL); if (c == null) { // this is allowed } else { String value = c.getStringCellValue(); if (value.equalsIgnoreCase(SbvrRuleInfo.CLASS_COLUMN_NAME)) { fieldIndexes.put(SbvrRuleInfo.CLASS_COLUMN_NAME, (int) i); classNameFound = true; } else if (value.equalsIgnoreCase(SbvrRuleInfo.COMMENT_COLUMN_NAME)) { fieldIndexes.put(SbvrRuleInfo.COMMENT_COLUMN_NAME, (int) i); commentsFound = true; } else if (value.equalsIgnoreCase(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME)) { fieldIndexes.put(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME, (int) i); schemaPackageFound = true; } else if (value.equalsIgnoreCase(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME)) { fieldIndexes.put(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME, (int) i); ruleTextFound = true; } else if (value.equalsIgnoreCase(SbvrRuleInfo.RULE_NAME_COLUMN_NAME)) { fieldIndexes.put(SbvrRuleInfo.RULE_NAME_COLUMN_NAME, (int) i); ruleNameFound = true; } } } // if (fieldIndexes.size() != 5) { if (!ruleNameFound && !ruleTextFound) { // log message that required fields were not found result.addError(this, 5); return null; } /* * Read rule content */ for (int i = rulesSheet.getFirstRowNum() + 1; i <= rulesSheet.getLastRowNum(); i++) { Row r = rulesSheet.getRow(i); int rowNumber = i + 1; if (r == null) { // ignore empty rows continue; } SbvrRuleInfo sri = new SbvrRuleInfo(); // get rule name (required) Cell c = r.getCell(fieldIndexes.get(SbvrRuleInfo.RULE_NAME_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL); if (c == null) { // log message result.addWarning(this, 6, "" + rowNumber); continue; } else { String cellValue = c.getStringCellValue(); if (cellValue != null) { if (cellValue.contains(":")) { sri.setName(cellValue.substring(cellValue.lastIndexOf(":") + 1)); } else { sri.setName(cellValue); } } } // get rule text (required) c = r.getCell(fieldIndexes.get(SbvrRuleInfo.RULE_TEXT_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL); if (c == null) { // log message result.addWarning(this, 7, "" + rowNumber); continue; } else { sri.setText(c.getStringCellValue()); } // get comment (optional) if (commentsFound) { c = r.getCell(fieldIndexes.get(SbvrRuleInfo.COMMENT_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL); if (c != null) { sri.setComment(c.getStringCellValue()); } } // get schema package (optional) if (schemaPackageFound) { c = r.getCell(fieldIndexes.get(SbvrRuleInfo.SCHEMA_PACKAGE_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL); if (c == null) { sri.setSchemaPackageName(UNSPECIFIED_SCHEMA_PACKAGE_NAME); } else { sri.setSchemaPackageName(c.getStringCellValue()); } } /* * get class name (optional when loading from excel because later we * can still try parsing it from the rule text) */ if (classNameFound) { c = r.getCell(fieldIndexes.get(SbvrRuleInfo.CLASS_COLUMN_NAME), Row.RETURN_BLANK_AS_NULL); if (c == null) { /* * then after this we'll try to parse the class name from * the rule text */ } else { sri.setClassName(c.getStringCellValue()); } } if (sri.getClassName() == null) { /* * try parsing the main class name from the rule text */ result.addInfo(this, 10, sri.getName()); String mainClassName = parseClassNameFromRuleText(sri.getText()); if (mainClassName == null) { result.addWarning(this, 8, sri.getName()); continue; } else { sri.setClassName(mainClassName); } } List<SbvrRuleInfo> rulesList; TreeMap<String, List<SbvrRuleInfo>> rulesBySchemaPackageName; if (rules.containsKey(sri.getClassName())) { rulesBySchemaPackageName = rules.get(sri.getClassName()); if (rulesBySchemaPackageName.containsKey(sri.getSchemaPackageName())) { rulesList = rulesBySchemaPackageName.get(sri.getSchemaPackageName()); } else { rulesList = new ArrayList<SbvrRuleInfo>(); rulesBySchemaPackageName.put(sri.getSchemaPackageName(), rulesList); } } else { rulesBySchemaPackageName = new TreeMap<String, List<SbvrRuleInfo>>(); rules.put(sri.getClassName(), rulesBySchemaPackageName); rulesList = new ArrayList<SbvrRuleInfo>(); rulesBySchemaPackageName.put(sri.getSchemaPackageName(), rulesList); } rulesList.add(sri); } // now sort all lists contained in the map for (TreeMap<String, List<SbvrRuleInfo>> rulesBySchemaPackageName : rules.values()) { for (List<SbvrRuleInfo> rulesList : rulesBySchemaPackageName.values()) { Collections.sort(rulesList, new Comparator<SbvrRuleInfo>() { @Override public int compare(SbvrRuleInfo o1, SbvrRuleInfo o2) { int classNameComparison = o1.getClassName().compareTo(o2.getClassName()); if (classNameComparison != 0) { return classNameComparison; } else { return o1.getText().compareTo(o2.getText()); } } }); } } return rules; }
From source file:de.topicmapslab.jexc.eXql.grammar.expression.FunctionExpression.java
License:Apache License
/** * Interpretation method for previous function * /*from w w w. j a v a 2 s . c om*/ * @param workBook * the workbook * @param row * the row * @return the index of the previous row satisfying the given property or in * minimum the first row * @throws JeXcException * thrown if operation fails */ private Object interpretPreviousFunction(Workbook workBook, Row row) throws JeXcException { ExqlExpression ex = getExpressions().get(0); Sheet sheet = row.getSheet(); for (int i = row.getRowNum(); i >= sheet.getFirstRowNum() + 1; i--) { Row r = sheet.getRow(i); Object result = ex.interpret(workBook, Arrays.asList(new Row[] { r })); if (result instanceof Collection && !((Collection<?>) result).isEmpty()) { return r.getRowNum(); } } /* * get minimum rows */ return sheet.getFirstRowNum(); }
From source file:eionet.gdem.conversion.excel.reader.ExcelReader.java
License:Mozilla Public License
@Override public void writeContentToInstance(DD_XMLInstance instance) throws Exception { List<DDXmlElement> tables = instance.getTables(); if (tables == null || wb == null) { readerLogger.logNoDefinitionsForTables(); return;// ww w . j a va2s . co m } for (int i = 0; i < tables.size(); i++) { DDXmlElement table = tables.get(i); String tblLocalName = table.getLocalName(); if (tblLocalName != null && tblLocalName.length() > 31) { tblLocalName = tblLocalName.substring(0, 31); } String tblName = table.getName(); String tblAttrs = table.getAttributes(); readerLogger.logStartSheet(tblLocalName); readerLogger.logSheetSchema(instance.getInstanceUrl(), tblLocalName); if (!excelSheetNames.contains(tblLocalName)) { readerLogger.logSheetNotFound(tblLocalName); } Sheet sheet = getSheet(tblLocalName); Sheet metaSheet = getMetaSheet(tblLocalName); if (sheet == null) { readerLogger.logEmptySheet(tblLocalName); continue; } int firstRow = sheet.getFirstRowNum(); int lastRow = sheet.getLastRowNum(); Row row = sheet.getRow(firstRow); Row metaRow = null; List<DDXmlElement> elements = instance.getTblElements(tblName); setColumnMappings(row, elements, true); if (metaSheet != null) { metaRow = metaSheet.getRow(firstRow); setColumnMappings(metaRow, elements, false); } try { logColumnMappings(tblLocalName, row, metaRow, elements); } catch (Exception e) { e.printStackTrace(); readerLogger.logSystemWarning(tblLocalName, "cannot write log about missing or ectra columns."); } instance.writeTableStart(tblName, tblAttrs); instance.setCurRow(tblName); Map<String, DDElement> elemDefs = instance.getElemDefs(tblLocalName); // read data // there are no data rows in the Excel file. We create empty table firstRow = (firstRow == lastRow) ? lastRow : firstRow + 1; int countRows = 0; for (int j = firstRow; j <= lastRow; j++) { row = (firstRow == 0) ? null : sheet.getRow(j); metaRow = (metaSheet != null && firstRow != 0) ? metaSheet.getRow(j) : null; // don't convert empty rows. if (isEmptyRow(row)) { continue; } countRows++; instance.writeRowStart(); for (int k = 0; k < elements.size(); k++) { DDXmlElement elem = elements.get(k); String elemName = elem.getName(); String elemLocalName = elem.getLocalName(); String elemAttributes = elem.getAttributes(); int colIdx = elem.getColIndex(); boolean isMainTable = elem.isMainTable(); String schemaType = null; boolean hasMultipleValues = false; String delim = null; // get element definition info if (elemDefs != null && elemDefs.containsKey(elemLocalName)) { schemaType = elemDefs.get(elemLocalName).getSchemaDataType(); delim = elemDefs.get(elemLocalName).getDelimiter(); hasMultipleValues = elemDefs.get(elemLocalName).isHasMultipleValues(); } String data = ""; if (colIdx > -1) { data = (isMainTable) ? getCellValue(row, colIdx, schemaType) : getCellValue(metaRow, colIdx, null); } if (hasMultipleValues && !Utils.isNullStr(delim)) { String[] values = data.split(delim); for (String value : values) { instance.writeElement(elemName, elemAttributes, value.trim()); } } else { instance.writeElement(elemName, elemAttributes, data); } } instance.writeRowEnd(); } instance.writeTableEnd(tblName); readerLogger.logNumberOfRows(countRows, tblLocalName); readerLogger.logEndSheet(tblLocalName); } }
From source file:eu.esdihumboldt.hale.io.xls.test.writer.XLSInstanceWriterTest.java
License:Open Source License
private void checkHeader(Sheet sheet, List<String> headerNames) throws Exception { Row header = sheet.getRow(sheet.getFirstRowNum()); assertEquals("There are not enough header cells.", headerNames.size(), header.getPhysicalNumberOfCells()); for (Cell cell : header) { assertTrue("Not expecting header cell value.", headerNames.contains(cell.getStringCellValue())); }// www. ja v a2 s . co m }