List of usage examples for org.apache.poi.ss.usermodel Sheet rowIterator
Iterator<Row> rowIterator();
From source file:coolmap.application.io.external.ImportCOntologyFromXLS.java
private void importGMT(Sheet sheet, COntology ontology) throws Exception { int rowCounter = 0; Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next();/* www.j a v a2 s. com*/ if (rowCounter < rowStart) { rowCounter++; continue; } if (columnStart + 2 > row.getLastCellNum()) { continue; } Cell parentCell = row.getCell(columnStart); Cell descriptionCell = row.getCell(columnStart + 1); if (descriptionCell != null && descriptionCell.toString().trim().length() != 0 && parentCell == null && parentCell.toString().trim().length() != 0) { COntology.setAttribute(parentCell.toString().trim(), "gmt.Description", descriptionCell.toString().trim()); } if (parentCell == null || parentCell.toString().trim().length() == 0) { continue; } for (int j = columnStart + 2; j < row.getLastCellNum(); j++) { Cell childCell = row.getCell(j); if (childCell == null || childCell.toString().trim().length() == 0) { continue; } ontology.addRelationshipNoUpdateDepth(parentCell.toString().trim(), childCell.toString().trim()); } rowCounter++; } }
From source file:coolmap.application.io.external.ImportCOntologyFromXLS.java
@Override public void configure(File... file) { try {/*from w w w . j a v a2s . com*/ File inFile = file[0]; String fileNameString = inFile.getName().toLowerCase(); FileInputStream inStream = new FileInputStream(inFile); Workbook workbook = null; if (fileNameString.endsWith("xls")) { workbook = new HSSFWorkbook(inStream); } else if (fileNameString.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(inStream); } int sheetCount = workbook.getNumberOfSheets(); String[] sheetNames = new String[sheetCount]; for (int i = 0; i < sheetNames.length; i++) { String sheetName = workbook.getSheetAt(i).getSheetName(); sheetNames[i] = sheetName == null || sheetName.length() == 0 ? "Untitled" : sheetName; } DefaultTableModel tableModels[] = new DefaultTableModel[sheetCount]; Cell cell; Row row; ArrayList<ArrayList<ArrayList<Object>>> previewData = new ArrayList(); for (int si = 0; si < sheetCount; si++) { //The row iterator automatically skips the blank rows //so only need to figure out how many rows to skip; which is nice //columns, not the same though Sheet sheet = workbook.getSheetAt(si); Iterator<Row> rowIterator = sheet.rowIterator(); int ri = 0; ArrayList<ArrayList<Object>> data = new ArrayList<ArrayList<Object>>(); while (rowIterator.hasNext()) { row = rowIterator.next(); ArrayList<Object> rowData = new ArrayList<>(); for (int j = 0; j < row.getLastCellNum(); j++) { cell = row.getCell(j); try { if (cell == null) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { rowData.add(cell.getStringCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { rowData.add(cell.getNumericCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { rowData.add(cell.getBooleanCellValue()); } else { rowData.add(cell.toString()); } } catch (Exception e) { // CMConsole.logError(" error parsing excel cell: " + cell + ", [" + ri + "," + j + "]"); rowData.add(null); } } data.add(rowData); ri++; if (ri == previewNum) { break; } } //end // System.out.println(data); // now the data is the data // ugh-> this is not a generic importer previewData.add(data); } //end of loop sheets ConfigPanel configPanel = new ConfigPanel(sheetNames, previewData); int returnVal = JOptionPane.showConfirmDialog(CoolMapMaster.getCMainFrame(), configPanel, "Import from Excel: " + inFile.getAbsolutePath(), JOptionPane.OK_CANCEL_OPTION, JOptionPane.PLAIN_MESSAGE, null); if (returnVal == JOptionPane.OK_OPTION) { proceed = true; inStream.close(); workbook = null; //set parameters inFile = file[0]; rowStart = configPanel.getRowStart(); columnStart = configPanel.getColumnStart(); sheetIndex = configPanel.getSheetIndex(); formatIndex = configPanel.getFormatIndex(); } else { //mark operation cancelled proceed = false; } } catch (Exception e) { } }
From source file:coolmap.application.io.external.ImportDataFromXLS.java
@Override public void importFromFile(File inFile) throws Exception { //Ignore the file, choose only a single file //I actually don't know the row count if (!proceed) { throw new Exception("Import from excel was cancelled"); } else {/*from ww w . j a v a 2 s .c om*/ try { String fileNameString = inFile.getName().toLowerCase(); FileInputStream inStream = new FileInputStream(inFile); Workbook workbook = null; if (fileNameString.endsWith("xls")) { workbook = new HSSFWorkbook(inStream); } else if (fileNameString.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(inStream); } Sheet sheet = workbook.getSheetAt(sheetIndex); int rowCounter = 0; //need to first copy the file over ArrayList<ArrayList<Object>> data = new ArrayList<ArrayList<Object>>(); Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); // if (rowCounter < rowStart) { // rowCounter++; // //import ontology rows // // continue; // // //skip first rows // } ArrayList<Object> rowData = new ArrayList<Object>(); for (int i = 0; i < row.getLastCellNum(); i++) { Cell cell = row.getCell(i); // System.out.print(cell + " "); // now add data try { if (cell == null) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { rowData.add(cell.getStringCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { rowData.add(cell.getNumericCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { rowData.add(cell.getBooleanCellValue()); } else { rowData.add(cell.toString()); } } catch (Exception e) { // CMConsole.logError(" error parsing excel cell: " + cell + ", [" + row + "," + i + "]"); rowData.add(null); } } // System.out.println(""); data.add(rowData); } //now I have row data int rowCount = data.size() - rowStart - 1; int columnCount = data.get(0).size() - columnStart - 1; DoubleCMatrix matrix = new DoubleCMatrix(Tools.removeFileExtension(inFile.getName()), rowCount, columnCount); String[] rowNames = new String[rowCount]; String[] columnNames = new String[columnCount]; for (int i = rowStart; i < data.size(); i++) { ArrayList row = data.get(i); if (i == rowStart) { //first row contains names for (int j = columnStart + 1; j < row.size(); j++) { try { columnNames[j - columnStart - 1] = row.get(j).toString(); } catch (Exception e) { columnNames[j - columnStart - 1] = "Untitled " + Tools.randomID(); } } continue; } for (int j = columnStart; j < row.size(); j++) { Object cell = row.get(j); if (j == columnStart) { try { rowNames[i - rowStart - 1] = cell.toString(); } catch (Exception e) { rowNames[i - rowStart - 1] = "Untitled" + Tools.randomID(); } } else { //set values try { Object value = (Double) row.get(j); if (value == null) { matrix.setValue(i - rowStart - 1, j - columnStart - 1, null); } else if (value instanceof Double) { matrix.setValue(i - rowStart - 1, j - columnStart - 1, (Double) value); } else { matrix.setValue(i - rowStart - 1, j - columnStart - 1, Double.NaN); } } catch (Exception e) { matrix.setValue(i - rowStart - 1, j - columnStart - 1, null); } } } //end of iterating columns } //end of iterating rows // matrix.printMatrix(); // matrix.setRowLabels(rowNames); matrix.setColLabels(columnNames); CoolMapObject object = new CoolMapObject(); object.setName(Tools.removeFileExtension(inFile.getName())); object.addBaseCMatrix(matrix); ArrayList<VNode> nodes = new ArrayList<VNode>(); for (Object label : matrix.getRowLabelsAsList()) { nodes.add(new VNode(label.toString())); } object.insertRowNodes(nodes); nodes.clear(); for (Object label : matrix.getColLabelsAsList()) { nodes.add(new VNode(label.toString())); } object.insertColumnNodes(nodes); object.setAggregator(new DoubleDoubleMean()); object.setSnippetConverter(new DoubleSnippet1_3()); object.setViewRenderer(new NumberToColor(), true); object.getCoolMapView().addColumnMap(new ColumnLabels(object)); object.getCoolMapView().addColumnMap(new ColumnTree(object)); object.getCoolMapView().addRowMap(new RowLabels(object)); object.getCoolMapView().addRowMap(new RowTree(object)); importedCoolMaps.clear(); importedCoolMaps.add(object); //////////////////////////////////////////////////////////////// //////////////////////////////////////////////////////////////// // //let's add COntologies if (columnStart > 0) { COntology columnOntology = new COntology( Tools.removeFileExtension(inFile.getName()) + " column ontology", null); ArrayList<Object> columnLabels = data.get(rowStart); //these are column labels for (int i = 0; i < rowStart; i++) { ArrayList ontologyColumn = data.get(i); for (int j = columnStart + 1; j < columnLabels.size(); j++) { Object parent = ontologyColumn.get(j); Object child = columnLabels.get(j); if (parent != null && child != null) { columnOntology.addRelationshipNoUpdateDepth(parent.toString(), child.toString()); } //Also need to create presets } } columnOntology.validate(); // COntologyUtils.printOntology(columnOntology); importedOntologies.add(columnOntology); // need to finish the preset } if (rowStart > 0) { COntology rowOntology = new COntology( Tools.removeFileExtension(inFile.getName()) + " row ontology", null); List rowLabels = Arrays.asList(rowNames); for (int j = 0; j < columnStart; j++) { for (int i = rowStart + 1; i < data.size(); i++) { Object parent = data.get(i).get(j); Object child = rowLabels.get(i - rowStart - 1); if (parent != null && child != null) { rowOntology.addRelationshipNoUpdateDepth(parent.toString(), child.toString()); } } } rowOntology.validate(); COntologyUtils.printOntology(rowOntology); importedOntologies.add(rowOntology); } // create row and column complex combinatorial ontology (intersections) } catch (Exception e) { // e.printStackTrace(); throw new Exception("File error"); } } }
From source file:coolmap.application.io.external.ImportDataFromXLS.java
@Override public void configure(File... file) { //need to popup a secondary dialog; this must be done differently try {//from w w w . j a va 2 s . com File inFile = file[0]; String fileNameString = inFile.getName().toLowerCase(); FileInputStream inStream = new FileInputStream(inFile); Workbook workbook = null; if (fileNameString.endsWith("xls")) { workbook = new HSSFWorkbook(inStream); } else if (fileNameString.toLowerCase().endsWith("xlsx")) { workbook = new XSSFWorkbook(inStream); } int sheetCount = workbook.getNumberOfSheets(); String[] sheetNames = new String[sheetCount]; for (int i = 0; i < sheetNames.length; i++) { String sheetName = workbook.getSheetAt(i).getSheetName(); sheetNames[i] = sheetName == null || sheetName.length() == 0 ? "Untitled" : sheetName; } //also need to get the top 100 rows + all columns DefaultTableModel tableModels[] = new DefaultTableModel[sheetCount]; Cell cell; Row row; ArrayList<ArrayList<ArrayList<Object>>> previewData = new ArrayList(); for (int si = 0; si < sheetCount; si++) { //The row iterator automatically skips the blank rows //so only need to figure out how many rows to skip; which is nice //columns, not the same though Sheet sheet = workbook.getSheetAt(si); Iterator<Row> rowIterator = sheet.rowIterator(); int ri = 0; ArrayList<ArrayList<Object>> data = new ArrayList<ArrayList<Object>>(); while (rowIterator.hasNext()) { row = rowIterator.next(); ArrayList<Object> rowData = new ArrayList<>(); for (int j = 0; j < row.getLastCellNum(); j++) { cell = row.getCell(j); try { if (cell == null) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) { rowData.add(null); } else if (cell.getCellType() == Cell.CELL_TYPE_STRING) { rowData.add(cell.getStringCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) { rowData.add(cell.getNumericCellValue()); } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { rowData.add(cell.getBooleanCellValue()); } else { rowData.add(cell.toString()); } } catch (Exception e) { // CMConsole.logError(" error parsing excel cell: " + cell + ", [" + ri + "," + j + "]"); rowData.add(null); } } data.add(rowData); ri++; if (ri == previewNum) { break; } } //end // System.out.println(data); // now the data is the data // ugh-> this is not a generic importer previewData.add(data); } //end of iterating all sheets ConfigPanel configPanel = new ConfigPanel(sheetNames, previewData); //int returnVal = JOptionPane.showMessageDialog(CoolMapMaster.getCMainFrame(), configPanel); int returnVal = JOptionPane.showConfirmDialog(CoolMapMaster.getCMainFrame(), configPanel, "Import from Excel: " + inFile.getAbsolutePath(), JOptionPane.OK_CANCEL_OPTION, JOptionPane.PLAIN_MESSAGE, null); if (returnVal == JOptionPane.OK_OPTION) { proceed = true; inStream.close(); workbook = null; //set parameters inFile = file[0]; importOntology = configPanel.getImportOntology(); rowStart = configPanel.getRowStart(); columnStart = configPanel.getColumnStart(); sheetIndex = configPanel.getSheetIndex(); } else { //mark operation cancelled proceed = false; } } catch (Exception e) { CMConsole.logError(" failed to import numeric matrix data from: " + file); e.printStackTrace(); } }
From source file:cz.krasny.icalstats.data.classes.output.formats.ToHtml.java
public void printStyles() { //ensureOut(); // First, copy the base css BufferedReader in = null;//from ww w . j a v a 2s .c o m try { in = new BufferedReader(new InputStreamReader(getClass().getResourceAsStream("excelStyle.css"))); String line; while ((line = in.readLine()) != null) { out.format("%s%n", line); } } catch (IOException e) { throw new IllegalStateException("Reading standard css", e); } finally { if (in != null) { try { in.close(); } catch (IOException e) { //noinspection ThrowFromFinallyBlock throw new IllegalStateException("Reading standard css", e); } } } // now add css for each used style Set<CellStyle> seen = new HashSet<CellStyle>(); for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); Iterator<Row> rows = sheet.rowIterator(); while (rows.hasNext()) { Row row = rows.next(); for (Cell cell : row) { CellStyle style = cell.getCellStyle(); if (!seen.contains(style)) { printStyle(style); seen.add(style); } } } } }
From source file:de.iteratec.iteraplan.businesslogic.service.legacyExcel.ExcelExportServiceImplTest.java
License:Open Source License
/** * @param wb the Workbook to be scanned//w w w .j a va 2s. c o m * @param expctContent the string we search for * * @return true if one of the workbook's contains the searched string, false otherwise */ private boolean wbContainsString(ExportWorkbook wb, String expctContent) { for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); for (Iterator<Row> rIt = sheet.rowIterator(); rIt.hasNext();) { Row r = rIt.next(); for (Iterator<Cell> cIt = r.cellIterator(); cIt.hasNext();) { Cell c = cIt.next(); try { if (c.getStringCellValue().equals(expctContent)) { return true; } } catch (IllegalStateException e) { // if numeric cells are encountered } } } } return false; }
From source file:de.iteratec.iteraplan.businesslogic.service.legacyExcel.ExcelExportServiceImplTest.java
License:Open Source License
/** * Method to print a workbook's cells-content. Handy if some errors show up. *//* w w w . ja v a 2 s. c o m*/ @SuppressWarnings("unused") private void printWb(ExportWorkbook wb) { for (int i = 0; i < wb.getNumberOfSheets(); i++) { Sheet sheet = wb.getSheetAt(i); for (Iterator<Row> rIt = sheet.rowIterator(); rIt.hasNext();) { Row r = rIt.next(); for (Iterator<Cell> cIt = r.cellIterator(); cIt.hasNext();) { Cell c = cIt.next(); LOGGER.debug(c.toString()); } } } }
From source file:de.quamoco.qm.editor.export.ResultCalibrationImporter.java
License:Apache License
/** Calibrate a {@link WeightedSumFactorAggregation}. */ private void importWeightedSumFactorAggregation(Sheet sheet, WeightedSumFactorAggregation factorAggregation, EAttribute attribute, int column) { for (Iterator<Row> j = sheet.rowIterator(); j.hasNext();) { Row row = j.next();//from w w w.j a va 2 s . c om if (row.getRowNum() < 2) { continue; } try { Object value = row.getCell(column).getNumericCellValue(); String factorName = row.getCell(0).getStringCellValue(); FactorRanking ranking = getFactorRanking(factorAggregation, factorName); if (attribute.getEAttributeType() == EcorePackage.eINSTANCE.getEInt()) { value = ((Double) value).intValue(); } ranking.eSet(attribute, value); } catch (RuntimeException e) { // ignore } } }
From source file:de.quamoco.qm.editor.export.ResultCalibrationImporter.java
License:Apache License
/** Calibrate a {@link WeightedSumMultiMeasureEvaluation}. */ private void importWeightedSumMultiMeasureEvaluation(Sheet sheet, WeightedSumMultiMeasureEvaluation multiMeasureEvaluation, EAttribute attribute, int column) { for (Iterator<Row> j = sheet.rowIterator(); j.hasNext();) { Row row = j.next();// w w w . j av a 2 s . c o m if (row.getRowNum() < 2) { continue; } try { Object value = row.getCell(column).getNumericCellValue(); String measureName = row.getCell(0).getStringCellValue(); MeasureRanking ranking = getMeasureRanking(multiMeasureEvaluation, measureName); if (attribute.getEAttributeType() == EcorePackage.eINSTANCE.getEInt()) { value = ((Double) value).intValue(); } ranking.eSet(attribute, value); } catch (RuntimeException e) { // ignore } } }
From source file:ec.util.spreadsheet.poi.PoiSheet.java
License:EUPL
public PoiSheet(@Nonnull Sheet sheet) { this.sheet = sheet; this.flyweightCell = new PoiCell(); int maxRow = 0; int maxColumn = 0; Iterator<Row> rowIterator = sheet.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next();/*from ww w . ja v a2 s .c o m*/ maxRow = row.getRowNum() + 1; short lastCellNum = row.getLastCellNum(); if (lastCellNum > maxColumn) { maxColumn = lastCellNum; } } this.rowCount = maxRow; this.columnCount = maxColumn; }