List of usage examples for org.apache.poi.ss.usermodel Row getLastCellNum
short getLastCellNum();
From source file:courtscheduler.persistence.CourtScheduleIO.java
License:Apache License
private Team processRow(Row currentRow, CourtScheduleInfo info) { short columnCount = currentRow.getLastCellNum(); int columnCounter = 0; currentRowNum = currentRow.getRowNum(); currentColumnNum = 0;// w ww . j av a 2 s . c om Integer teamId = null; String teamName = ""; Integer conference = null; String year = ""; String gender = ""; String grade = ""; String level = ""; String requests = ""; String notSameTimeAs = ""; Team team = new Team(); while (columnCounter < columnCount) { Cell cell = currentRow.getCell(columnCounter); if (cell == null) { if (teamId == null) { System.out.println( "================================================================================"); break; } else { columnCounter++; continue; // if the cell is null just jump to the next iteration } } currentColumnNum = cell.getColumnIndex(); if (columnCounter == 0) { int index = cell.toString().indexOf("."); String teamString = cell.toString().substring(0, index); try { teamId = Integer.parseInt(teamString); team.setTeamId(teamId); team.getDontPlay().addSharedTeam(teamId); } catch (NumberFormatException e) { //not sure what we should do here, this means a team's id is not being captured String niceMessage = String.format("Could not determine the team id from '%s'", teamString); niceMessage = niceMessage + "\tFound in " + currentCell(); Main.error(niceMessage, e.toString()); } } else if (columnCounter == 1) { team.setConference(getStringValueOfInt(cell.toString())); } else if (columnCounter == 2) { teamName = cell.toString(); team.setTeamName(teamName); } else if (columnCounter == 3) { year = cell.toString(); team.setYear(year); } else if (columnCounter == 4) { gender = cell.toString(); team.setGender(gender); } else if (columnCounter == 5) { team.setGrade(getStringValueOfInt(cell.toString())); if (team.getGrade().trim().equals("")) { warning("Team \"" + teamId + "\" has no grade!" + "\tFound in " + currentCell()); } } else if (columnCounter == 6) { level = cell.toString(); team.setLevel(level); } else if (columnCounter == 7) { requests = cell.toString(); //debug(team.getTeamId().toString()+":"+requests); System.out.println(team.getTeamId() + ": " + requests); processRequestConstraints(team, requests, info); } else if (columnCounter == 8) { notSameTimeAs = cell.toString(); String[] tempSplit = notSameTimeAs.split(","); for (String teamIdStr : tempSplit) { try { int index = teamIdStr.indexOf("."); if (index > -1) { teamId = Integer.parseInt(teamIdStr.substring(0, index)); team.getAvailability().getNotSameTimeAs().addSharedTeam(teamId); team.getDontPlay().addSharedTeam(teamId); } } catch (NumberFormatException nfe) { warning("Unable to add team \"" + teamIdStr + "\" to shared team list because it is not a number" + "\tFound in " + currentCell()); } catch (NullPointerException npe) { warning("team.availability or team.availability.notSameTimeAs is null for " + teamIdStr + "\tFound in " + currentCell()); } } } columnCounter += 1; } return team; }
From source file:courtscheduler.persistence.CourtScheduleIO.java
License:Apache License
public short getColumnWidth(File file) throws Exception { FileInputStream fis = new FileInputStream(file); XSSFWorkbook wb = new XSSFWorkbook(fis); // Get worksheet by index XSSFSheet sh = wb.getSheetAt(0);// w ww . j a v a 2 s. c om short columnWidth = 0; Integer rowCounter = 0; Integer rowCount = sh.getLastRowNum(); while (rowCounter <= rowCount) { Row currentRow = sh.getRow(rowCounter); short columnCount = currentRow.getLastCellNum(); if (columnCount > columnWidth) columnWidth = columnCount; } return columnWidth; }
From source file:coverageqc.data.DoNotCall.java
public static DoNotCall populate(Row xslxHeadingRow, Row xslxDataRow, Integer calltype) { DoNotCall donotcall = new DoNotCall(); int columnNumber; int cellIndex; String[] headerArray;/*from w w w. j a v a2 s .co m*/ HashMap<String, Integer> headings = new HashMap<String, Integer>(); columnNumber = xslxHeadingRow.getLastCellNum(); headerArray = new String[columnNumber]; Iterator<Cell> cellIterator = xslxHeadingRow.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); cellIndex = cell.getColumnIndex(); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: headerArray[cellIndex] = Boolean.toString(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: headerArray[cellIndex] = Double.toString(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: headerArray[cellIndex] = cell.getStringCellValue(); break; default: headerArray[cellIndex] = ""; } } //end while celliterator for (int x = 0; x < headerArray.length; x++) { headings.put(headerArray[x].substring(0, headerArray[x].indexOf("_")), x); } //String[] dataArray = xslxDataLine.split("\t"); if (xslxDataRow.getCell(headings.get("HGVSc")) != null) { donotcall.hgvsc = xslxDataRow.getCell(headings.get("HGVSc").intValue()).getStringCellValue(); } //donotcall.hgvsp = xslxDataRow.getCell(headings.get("HGVSp").intValue()).getStringCellValue(); if (xslxDataRow.getCell(headings.get("ENSP")) != null) { donotcall.ensp = xslxDataRow.getCell(headings.get("ENSP").intValue()).getStringCellValue(); } if (xslxDataRow.getCell(headings.get("Transcript")) != null) { donotcall.transcript = xslxDataRow.getCell(headings.get("Transcript").intValue()).getStringCellValue(); } else { System.out.println( "Transcript_27 column entry is negative! This is essential to do not call! Do not call list needs to be fixed! Crashing to prevent abnormal behavior!"); System.exit(1); } donotcall.coordinate = (long) xslxDataRow.getCell(headings.get("Coordinate").intValue()) .getNumericCellValue(); // CallType is the page of the xlsx : // 1 => Always_Not_Real // 2 => Not_Real_When_Percentage_Low // 3 => Undetermined_Importance if (calltype == 1) { donotcall.callType = "Don't call, always"; } else if (calltype == 2) { donotcall.callType = "If percentage low, don't call"; } else { donotcall.callType = "On lab list, Unknown significance"; } return donotcall; }
From source file:csv.impl.ExcelReader.java
License:Open Source License
/** * Returns the row as Java objects.// w w w . ja va 2s. co m * Values in the array are Java objects depending on the cell type. If the cell contained * a formula, the formula is evaluated before returning the row. * @return values in row * @param row row to read */ public Object[] getValues(Row row) { if (row == null) return null; List<Object> columns = new ArrayList<Object>(); int colCount = row.getLastCellNum(); for (int col = 0; col < colCount; col++) { Cell cell = row.getCell(col); columns.add(getValue(cell)); } return CSVUtils.convertList(columns, getMinimumColumnCount()); }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceImporter.java
License:Open Source License
private String getRowKey(Row row, int borderRowBetweenTopAndBottom, boolean isForTracing) { boolean isProductsOut = row.getRowNum() < borderRowBetweenTopAndBottom && !isForTracing || isForTracing && row.getRowNum() > borderRowBetweenTopAndBottom; String key = ""; for (int j = isProductsOut ? 0 : 1; j < row.getLastCellNum(); j++) { // Start with Lot Number or after Cell cell = row.getCell(j);//from ww w . j av a 2s . c o m if (!isCellEmpty(cell)) { cell.setCellType(Cell.CELL_TYPE_STRING); key += cell.getStringCellValue().trim(); } key += ";"; } while (key.endsWith(";;")) { key = key.substring(0, key.length() - 1); } return key; }
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 ww w. java2s . co m*/ 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/*from w ww .j a v a2 s .com*/ * @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.iteratec.iteraplan.businesslogic.exchange.elasticExcel.workbookdata.SheetContext.java
License:Open Source License
public short getColumnFullParentNameColumnNumber() { Row row = getSheet().getRow(getHeaderRowNumber()); for (short cellnum = row.getFirstCellNum(); cellnum <= row.getLastCellNum(); cellnum++) { Cell cell = row.getCell(cellnum); if (SheetContext.FULL_PARENT_NAME_COLUMN.equals(cell.getStringCellValue())) { return cellnum; }/*from ww w. ja v a 2 s . c o m*/ } return -1; }
From source file:de.iteratec.iteraplan.businesslogic.exchange.legacyExcel.importer.sheets.SheetImporter.java
License:Open Source License
/** * Last cells must not contain an empty String * * @param row// w ww .ja va2 s. c o m * a POI Row * @return int number of cells in row */ private int getRowSize(Row row) { int size = 0; for (int i = row.getLastCellNum(); i >= 0; i--) { Cell cell = row.getCell(i - 1); if (!ExcelImportUtilities.isEmpty(cell)) { size = i; break; } } return size; }
From source file:de.ks.idnadrev.expimp.xls.XlsxExporterTest.java
License:Apache License
@Test public void testExportToManyRelation() throws Exception { File tempFile = File.createTempFile("taskExportTest", ".xlsx"); EntityExportSource<Task> tasks = new EntityExportSource<>(PersistentWork.idsFrom(Task.class), Task.class); EntityExportSource<Tag> tags = new EntityExportSource<>(PersistentWork.idsFrom(Tag.class), Tag.class); XlsxExporter exporter = new XlsxExporter(); exporter.export(tempFile, tasks, tags); Workbook wb = WorkbookFactory.create(tempFile); Sheet taskSheet = wb.getSheet(Task.class.getName()); Sheet tagSheet = wb.getSheet(Tag.class.getName()); assertNotNull(taskSheet);/* w w w .java 2 s. co m*/ assertNotNull(tagSheet); Row firstRow = taskSheet.getRow(0); int pos = 0; Iterator<Cell> cellIterator = firstRow.cellIterator(); String property = PropertyPath.property(Task.class, t -> t.getTags()); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getStringCellValue().equals(property)) { break; } pos++; } assertNotEquals(Task.class.getSimpleName() + "." + property + " not exported", firstRow.getLastCellNum(), pos); Cell cell = taskSheet.getRow(1).getCell(pos); String[] split = StringUtils.split(cell.getStringCellValue(), ToManyColumn.SEPARATOR); assertEquals(2, split.length); assertTrue(Arrays.asList(split).contains("tag" + ToManyColumn.SEPARATOR_REPLACEMENT + "1")); assertTrue(Arrays.asList(split).contains("tag2")); }