List of usage examples for org.apache.poi.ss.usermodel Sheet getRow
Row getRow(int rownum);
From source file:com.vaadin.addon.spreadsheet.SpreadsheetHandlerImpl.java
@Override public void onPaste(String text) { Workbook workbook = spreadsheet.getWorkbook(); Sheet activesheet = workbook.getSheetAt(workbook.getActiveSheetIndex()); CellReference selectedCellReference = spreadsheet.getSelectedCellReference(); String[] lines;/*from w w w .jav a 2 s. c o m*/ if (text.indexOf("\r\n") > -1) { lines = text.split("\r\n"); } else if (text.indexOf("\n") > -1) { lines = text.split("\n"); } else { lines = text.split("\r"); } int pasteHeight = lines.length; int pasteWidth = 1; for (String line : lines) { String[] tokens = splitOnTab(line); pasteWidth = Math.max(pasteWidth, tokens.length); } int rowIndex = selectedCellReference.getRow(); int colIndex = selectedCellReference.getCol(); // Check for protected cells at target for (int i = 0; i < pasteHeight; i++) { Row row = activesheet.getRow(rowIndex + i); if (row != null) { for (int j = 0; j < pasteWidth; j++) { Cell cell = row.getCell(colIndex + j); if (spreadsheet.isCellLocked(cell)) { protectedCellWriteAttempted(); return; } } } } CellValueCommand command = new CellValueCommand(spreadsheet); CellRangeAddress affectedRange = new CellRangeAddress(rowIndex, rowIndex + pasteHeight - 1, colIndex, colIndex + pasteWidth - 1); command.captureCellRangeValues(affectedRange); for (int i = 0; i < pasteHeight; i++) { String line = lines[i]; Row row = activesheet.getRow(rowIndex + i); if (row == null) { row = activesheet.createRow(rowIndex + i); } String[] tokens = splitOnTab(line); for (int j = 0; j < pasteWidth; j++) { Cell cell = row.getCell(colIndex + j); if (cell == null) { cell = row.createCell(colIndex + j); } if (j < tokens.length) { String cellContent = tokens[j]; Double numVal = SpreadsheetUtil.parseNumber(cell, cellContent, spreadsheet.getLocale()); if (numVal != null) { cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numVal); } else { cell.setCellValue(cellContent); } } else { cell.setCellType(Cell.CELL_TYPE_BLANK); spreadsheet.markCellAsDeleted(cell, true); } spreadsheet.getCellValueManager().markCellForUpdate(cell); spreadsheet.getCellValueManager().getFormulaEvaluator().notifyUpdateCell(cell); } } spreadsheet.getSpreadsheetHistoryManager().addCommand(command); spreadsheet.updateMarkedCells(); // re-set selection to copied area spreadsheet.setSelectionRange(rowIndex, colIndex, rowIndex + pasteHeight - 1, colIndex + pasteWidth - 1); fireCellValueChangeEvent(affectedRange); }
From source file:com.vaadin.addon.spreadsheet.SpreadsheetStyleFactory.java
License:Open Source License
public void reloadActiveSheetColumnRowStyles() { final Workbook workbook = spreadsheet.getWorkbook(); if (spreadsheet.getState().rowIndexToStyleIndex == null) { spreadsheet.getState().rowIndexToStyleIndex = new HashMap<Integer, Integer>( workbook.getNumCellStyles()); } else {// w ww .jav a 2s .c o m spreadsheet.getState().rowIndexToStyleIndex.clear(); } if (spreadsheet.getState().columnIndexToStyleIndex == null) { spreadsheet.getState().columnIndexToStyleIndex = new HashMap<Integer, Integer>( workbook.getNumCellStyles()); } else { spreadsheet.getState().columnIndexToStyleIndex.clear(); } if (spreadsheet.getState().lockedColumnIndexes == null) { spreadsheet.getState().lockedColumnIndexes = new HashSet<Integer>(); } else { spreadsheet.getState().lockedColumnIndexes.clear(); } if (spreadsheet.getState().lockedRowIndexes == null) { spreadsheet.getState().lockedRowIndexes = new HashSet<Integer>(); } else { spreadsheet.getState().lockedRowIndexes.clear(); } Sheet activeSheet = spreadsheet.getActiveSheet(); for (int i = 0; i < spreadsheet.getRows(); i++) { Row row = activeSheet.getRow(i); if (row != null && row.getRowStyle() != null) { int styleIndex = row.getRowStyle().getIndex(); spreadsheet.getState().rowIndexToStyleIndex.put(i + 1, styleIndex); if (row.getRowStyle().getLocked()) { spreadsheet.getState().lockedRowIndexes.add(i + 1); } } else { if (spreadsheet.isActiveSheetProtected()) { spreadsheet.getState().lockedRowIndexes.add(i + 1); } } } for (int i = 0; i < spreadsheet.getColumns(); i++) { if (activeSheet.getColumnStyle(i) != null) { int styleIndex = activeSheet.getColumnStyle(i).getIndex(); spreadsheet.getState().columnIndexToStyleIndex.put(i + 1, styleIndex); if (activeSheet.getColumnStyle(i).getLocked()) { spreadsheet.getState().lockedColumnIndexes.add(i + 1); } } } }
From source file:com.validation.manager.core.tool.requirement.importer.RequirementImporter.java
License:Apache License
@Override public List<Requirement> importFile(boolean header) throws RequirementImportException, VMException { queue.clear();/* w ww.j ava 2 s .com*/ List<Integer> errors = new ArrayList<>(); HashMap<String, Object> parameters = new HashMap<>(); List<Object> result; if (toImport == null) { throw new RequirementImportException("message.requirement.import.file.null"); } else if (!toImport.exists()) { throw new RequirementImportException("message.requirement.import.file.invalid"); } else { //Excel support if (toImport.getName().endsWith(".xls") || toImport.getName().endsWith(".xlsx")) { try { Workbook wb = loadFile(); org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); int r = 0; if (header) { //Skip header row r++; } for (; r < rows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } if (row.getCell(0) == null) { LOG.log(Level.WARNING, "Found an empty row on line: {0}. " + "Stopping processing", r); break; } int cells = row.getPhysicalNumberOfCells(); if (cells < 2) { LOG.log(Level.INFO, "Processing row: {0}", r); LOG.warning(ResourceBundle .getBundle("com.validation.manager.resources.VMMessages", Locale.getDefault()) .getString("message.requirement.import.missing.column") .replaceAll("%c", "" + cells)); errors.add(r); } else { Requirement requirement = new Requirement(); LOG.log(Level.FINE, "Row: {0}", r); for (int c = 0; c < cells; c++) { Cell cell = row.getCell(c); String value = ""; if (cell != null) { switch (cell.getCellTypeEnum()) { case FORMULA: value = cell.getCellFormula(); break; case NUMERIC: value = "" + cell.getNumericCellValue(); break; case STRING: value = cell.getStringCellValue(); break; default: value = ""; break; } } //Remove any extra spaces. value = value.trim(); switch (c) { case 0: //Unique ID LOG.fine("Setting id"); requirement.setUniqueId(value); break; case 1: //Description LOG.fine("Setting desc"); requirement.setDescription(value); break; case 2: //Optional Requirement type LOG.fine("Setting requirement type"); parameters.clear(); parameters.put("name", value); result = namedQuery("RequirementType.findByName", parameters); if (result.isEmpty()) { //Assume a default parameters.clear(); parameters.put("name", "SW"); result = namedQuery("RequirementType.findByName", parameters); } requirement.setRequirementTypeId((RequirementType) result.get(0)); break; case 3: //Optional notes LOG.fine("Setting notes"); requirement.setNotes(value); break; default: throw new RequirementImportException("Invalid column detected: " + c); } LOG.fine(value); } //This shouldn't be null assert rsn != null : "Requirement Spec Node is null?"; requirement.setRequirementSpecNode(rsn); parameters.clear(); parameters.put("status", "general.open"); result = namedQuery("RequirementStatus.findByStatus", parameters); requirement.setRequirementStatusId((RequirementStatus) result.get(0)); assert requirement.getUniqueId() != null && !requirement.getUniqueId().isEmpty() : "Invalid requirement detected!"; try { if (!exists(requirement) && !queue.containsKey(requirement.getUniqueId())) { queue.put(requirement.getUniqueId(), requirement); } } catch (IllegalOrphanException | NonexistentEntityException ex) { Exceptions.printStackTrace(ex); } } } } catch (InvalidFormatException | IOException ex) { LOG.log(Level.SEVERE, null, ex); } finally { try { if (inp != null) { inp.close(); } } catch (IOException ex) { LOG.log(Level.SEVERE, null, ex); } } } else if (toImport.getName().endsWith(".xml")) { throw new RequirementImportException("XML importing not supported yet."); } else if (toImport.getName().endsWith(".doc") || toImport.getName().endsWith(".docx")) { try { TableExtractor te = new TableExtractor(toImport); List<DefaultTableModel> tables = te.extractTables(); Requirement requirement = new Requirement(); LOG.log(Level.INFO, "Imported {0} tables!", tables.size()); int count = 1; for (DefaultTableModel model : tables) { int rows = model.getRowCount(); int cols = model.getColumnCount(); LOG.log(Level.INFO, "Processing table {0} with {1} " + "rows and {2} columns.", new Object[] { count, rows, cols }); for (int r = 0; r < rows; r++) { for (int c = 0; c < cols; c++) { String value = (String) model.getValueAt(rows, cols); switch (c) { case 0: //Unique ID LOG.fine("Setting id"); requirement.setUniqueId(value); break; case 1: //Description LOG.fine("Setting desc"); requirement.setDescription(value); break; case 2: //Requirement type LOG.fine("Setting requirement type"); parameters.clear(); parameters.put("name", value); result = namedQuery("RequirementType.findByName", parameters); if (result.isEmpty()) { //Assume a default parameters.clear(); parameters.put("name", "SW"); result = namedQuery("RequirementType.findByName", parameters); } requirement.setRequirementTypeId((RequirementType) result.get(0)); break; case 3: //Optional notes LOG.fine("Setting notes"); requirement.setNotes(value); break; default: throw new RuntimeException("Invalid column detected: " + c); } } } } } catch (IOException | ClassNotFoundException ex) { Exceptions.printStackTrace(ex); } } else { throw new RequirementImportException("Unsupported file format: " + toImport.getName()); } StringBuilder sb = new StringBuilder("Rows with erros:\n"); errors.stream().forEach((line) -> { sb.append(line).append('\n'); }); if (!errors.isEmpty()) { getDefault().lookup(MessageHandler.class).info(sb.toString()); } return new ArrayList(queue.values()); } }
From source file:com.validation.manager.core.tool.step.importer.StepImporter.java
License:Apache License
@Override public List<Step> importFile(boolean header) throws TestCaseImportException { steps.clear();/* w ww . java 2 s . c o m*/ if (toImport == null) { throw new TestCaseImportException("message.step.import.file.null"); } else if (!toImport.exists()) { throw new TestCaseImportException("message.step.import.file.invalid"); } else { //Excel support if (toImport.getName().endsWith(".xls") || toImport.getName().endsWith(".xlsx")) { InputStream inp = null; try { inp = new FileInputStream(toImport); org.apache.poi.ss.usermodel.Workbook wb = create(inp); org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0); int rows = sheet.getPhysicalNumberOfRows(); int r = 0; if (header) { //Skip header row r++; } for (; r < rows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } int cells = row.getPhysicalNumberOfCells(); if (row.getCell(0) == null) { LOG.log(Level.WARNING, "Found an empty row on line: {0}. " + "Stopping processing", r); break; } if (cells < 2) { throw new TestCaseImportException(RB.getString("message.step.import.missing.column") .replaceAll("%c", "" + cells)); } Step step = new Step(); step.setRequirementList(new ArrayList<>()); HashMap<String, Object> parameters = new HashMap<>(); List<Object> result; LOG.log(Level.FINE, "Row: {0}", r); for (int c = 0; c < cells; c++) { Cell cell = row.getCell(c); String value = null; if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_FORMULA: value = cell.getCellFormula(); break; case Cell.CELL_TYPE_NUMERIC: value = "" + cell.getNumericCellValue(); break; case Cell.CELL_TYPE_STRING: value = cell.getStringCellValue(); break; default: //Do nothing. } } switch (c) { case 0: if (value != null) { //Sequence LOG.fine("Setting sequence"); Integer val = value.contains(".") ? valueOf(value.substring(0, value.indexOf("."))) : valueOf(value); if (!tc.getStepList().isEmpty()) { int max = 0; for (Step s : tc.getStepList()) { if (s.getStepSequence() > max) { max = s.getStepSequence(); } } //Make sure there isn't one on that sequence already val += max; } step.setStepSequence(val); } break; case 1: if (value != null) { //Text LOG.fine("Setting text"); step.setText(value.getBytes("UTF-8")); } break; case 2: //Optional Related requirements if (value != null && !value.trim().isEmpty()) { LOG.fine("Setting related requirements"); StringTokenizer st = new StringTokenizer(value, ","); while (st.hasMoreTokens()) { String token = st.nextToken().trim(); parameters.clear(); parameters.put("uniqueId", token); result = namedQuery("Requirement.findByUniqueId", parameters); if (!result.isEmpty()) { for (Object o : result) { step.getRequirementList().add((Requirement) o); } } } } break; case 3: if (value != null) { //Optional Expected result LOG.fine("Setting expected result"); step.setExpectedResult(value.getBytes("UTF-8")); } break; case 4: if (value != null) { //Optional notes LOG.fine("Setting notes"); step.setNotes(value); } break; default: throw new RuntimeException("Invalid column detected: " + c); } LOG.fine(value); } step.setTestCase(tc); steps.add(step); } } catch (InvalidFormatException | IOException ex) { LOG.log(Level.SEVERE, null, ex); } finally { try { if (inp != null) { inp.close(); } } catch (IOException ex) { LOG.log(Level.SEVERE, null, ex); } } } else if (toImport.getName().endsWith(".xml")) { throw new TestCaseImportException("XML importing not supported yet."); } else { throw new TestCaseImportException("Unsupported file format: " + toImport.getName()); } return steps; } }
From source file:com.virtusa.isq.vtaf.runtime.SeleniumTestBase.java
License:Apache License
/** * Adds the values from excel./* ww w. j a va 2 s . com*/ * * @param path * the path * @param index * the index * @return the string[][] * @throws IOException * Signals that an I/O exception has occurred. * @throws InvalidFormatException * the invalid format exception */ public final String[][] addValuesFromExcel(final String path, final String index) throws IOException, InvalidFormatException { String cellStringValue = null; double cellDoubleValue = 0; Boolean cellBooleanValue; byte cellErrorValue = 0; String[][] arrExcelContent; FileInputStream file = null; Workbook workbook = null; Sheet sheet = null; try { file = new FileInputStream(new File(path)); workbook = WorkbookFactory.create(file); sheet = workbook.getSheetAt(Integer.parseInt(index)); Iterator<Row> rowIterator = sheet.iterator(); arrExcelContent = new String[sheet.getPhysicalNumberOfRows()][]; while (rowIterator.hasNext()) { Row row = rowIterator.next(); int rowNumber = row.getRowNum(); Iterator<Cell> cellIterator = row.cellIterator(); arrExcelContent[rowNumber] = new String[sheet.getRow(rowNumber).getPhysicalNumberOfCells()]; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); int cellNumber = cell.getColumnIndex(); if (cell.getCellType() == Cell.CELL_TYPE_STRING) { cellStringValue = cell.getStringCellValue(); arrExcelContent[rowNumber][cellNumber] = cellStringValue; } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) { cellBooleanValue = cell.getBooleanCellValue(); arrExcelContent[rowNumber][cellNumber] = cellBooleanValue.toString(); } else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) { cellErrorValue = cell.getErrorCellValue(); arrExcelContent[rowNumber][cellNumber] = Byte.toString(cellErrorValue); } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { cellStringValue = cell.getCellFormula(); arrExcelContent[rowNumber][cellNumber] = cellStringValue; } else { cellDoubleValue = cell.getNumericCellValue(); arrExcelContent[rowNumber][cellNumber] = Double.toString(cellDoubleValue); } } } } finally { if (((InputStream) workbook) != null) { ((InputStream) workbook).close(); } } return arrExcelContent; }
From source file:com.wabacus.system.assistant.StandardExcelAssistant.java
License:Open Source License
public void setRegionCellStringValue(Workbook workbook, Sheet sheet, CellRangeAddress region, CellStyle cellStyle, String cellvalue) { createRowAndColInRegion(sheet, region, cellStyle); Row rowTmp = sheet.getRow(region.getFirstRow()); Cell cellTmp = rowTmp.getCell(region.getFirstColumn()); cellTmp.setCellStyle(cellStyle);//from w ww . j a v a 2 s.c om cellTmp.setCellValue(cellvalue); sheet.addMergedRegion(region); }
From source file:com.wabacus.system.assistant.StandardExcelAssistant.java
License:Open Source License
public void setRegionCellRealTypeValue(Workbook workbook, Sheet sheet, CellRangeAddress region, CellStyle cellStyle, CellStyle cellStyleWithFormat, String align, Object cellvalue, IDataType typeObj) { createRowAndColInRegion(sheet, region, cellStyle); Row rowTmp = sheet.getRow(region.getFirstRow());//rowsetRegionStyle() Cell cellTmp = rowTmp.getCell(region.getFirstColumn()); cellTmp.setCellStyle(cellStyle);//w w w . j a v a2 s. c o m this.setCellValue(workbook, align, cellTmp, cellvalue, typeObj, cellStyleWithFormat); sheet.addMergedRegion(region); }
From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java
License:Apache License
/** * Highlight cells based on their values *//*w ww .j a va2s.c o m*/ static void sameCell(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue(84); sheet.createRow(1).createCell(0).setCellValue(74); sheet.createRow(2).createCell(0).setCellValue(50); sheet.createRow(3).createCell(0).setCellValue(51); sheet.createRow(4).createCell(0).setCellValue(49); sheet.createRow(5).createCell(0).setCellValue(41); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Cell Value Is greater than 70 (Blue Fill) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule(ComparisonOperator.GT, "70"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.BLUE.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); // Condition 2: Cell Value Is less than 50 (Green Fill) ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule(ComparisonOperator.LT, "50"); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.GREEN.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") }; sheetCF.addConditionalFormatting(regions, rule1, rule2); sheet.getRow(0).createCell(2).setCellValue("<== Condition 1: Cell Value Is greater than 70 (Blue Fill)"); sheet.getRow(4).createCell(2).setCellValue("<== Condition 2: Cell Value Is less than 50 (Green Fill)"); }
From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java
License:Apache License
/** * Highlight multiple cells based on a formula *///ww w.j av a2s .co m static void multiCell(Sheet sheet) { // header row Row row0 = sheet.createRow(0); row0.createCell(0).setCellValue("Units"); row0.createCell(1).setCellValue("Cost"); row0.createCell(2).setCellValue("Total"); Row row1 = sheet.createRow(1); row1.createCell(0).setCellValue(71); row1.createCell(1).setCellValue(29); row1.createCell(2).setCellValue(2059); Row row2 = sheet.createRow(2); row2.createCell(0).setCellValue(85); row2.createCell(1).setCellValue(29); row2.createCell(2).setCellValue(2059); Row row3 = sheet.createRow(3); row3.createCell(0).setCellValue(71); row3.createCell(1).setCellValue(29); row3.createCell(2).setCellValue(2059); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =$B2>75 (Blue Fill) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("$A2>75"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.BLUE.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A2:C4") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(2).createCell(4).setCellValue("<== Condition 1: Formula Is =$B2>75 (Blue Fill)"); }
From source file:com.wantdo.stat.excel.poi_src.ConditionalFormats.java
License:Apache License
/** * Use Excel conditional formatting to check for errors, * and change the font colour to match the cell colour. * In this example, if formula result is #DIV/0! then it will have white font colour. *//*from w w w . ja v a 2s. c o m*/ static void errors(Sheet sheet) { sheet.createRow(0).createCell(0).setCellValue(84); sheet.createRow(1).createCell(0).setCellValue(0); sheet.createRow(2).createCell(0).setCellFormula("ROUND(A1/A2,0)"); sheet.createRow(3).createCell(0).setCellValue(0); sheet.createRow(4).createCell(0).setCellFormula("ROUND(A6/A4,0)"); sheet.createRow(5).createCell(0).setCellValue(41); SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting(); // Condition 1: Formula Is =ISERROR(C2) (White Font) ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("ISERROR(A1)"); FontFormatting font = rule1.createFontFormatting(); font.setFontColorIndex(IndexedColors.WHITE.index); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A1:A6") }; sheetCF.addConditionalFormatting(regions, rule1); sheet.getRow(2).createCell(1).setCellValue( "<== The error in this cell is hidden. Condition: Formula Is =ISERROR(C2) (White Font)"); sheet.getRow(4).createCell(1).setCellValue( "<== The error in this cell is hidden. Condition: Formula Is =ISERROR(C2) (White Font)"); }