Java Code Examples for org.apache.poi.ss.usermodel.Row#getCell()
The following examples show how to use
org.apache.poi.ss.usermodel.Row#getCell() .
You can vote up the ones you like or vote down the ones you don't like,
and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: OriginsSheetV1d2.java From tools with Apache License 2.0 | 6 votes |
@SuppressWarnings("deprecation") private String validateRow(Row row) { for (int i = 0; i < NUM_COLS; i++) { Cell cell = row.getCell(i); if (cell == null) { if (REQUIRED[i]) { return "Required cell "+HEADER_TITLES[i]+" missing for row "+String.valueOf(row.getRowNum()+" in Origins Spreadsheet"); } } else { if (i == CREATED_COL) { if (!(cell.getCellTypeEnum() == CellType.NUMERIC)) { return "Created column in origin spreadsheet is not of type Date"; } } // if (cell.getCellType() != Cell.CELL_TYPE_STRING) { // return "Invalid cell format for "+HEADER_TITLES[i]+" for forw "+String.valueOf(row.getRowNum()); // } } } return null; }
Example 2
Source File: JSONConverter.java From carbon-identity-framework with Apache License 2.0 | 6 votes |
/** * Converts xls sheet to json format. * Currently considering the username. * * @param sheet : The XLS sheet that needs to be converted. * @return : Json string which represents the sheet. */ public String xlsToJSON(Sheet sheet) { int limit = sheet.getLastRowNum(); users = new JsonArray(); if (log.isDebugEnabled()) { log.debug("Converting XLS sheet to json."); } for (int i = 1; i < limit + 1; i++) { Row row = sheet.getRow(i); Cell cell = row.getCell(0); String name = cell.getStringCellValue(); JsonPrimitive userJson = new JsonPrimitive(name); users.add(userJson); } content.add(UserMgtConstants.USERS, users); return content.toString(); }
Example 3
Source File: SheetUtil.java From lams with GNU General Public License v2.0 | 6 votes |
/** * Return the cell, taking account of merged regions. Allows you to find the * cell who's contents are shown in a given position in the sheet. * * <p>If the cell at the given co-ordinates is a merged cell, this will * return the primary (top-left) most cell of the merged region. * <p>If the cell at the given co-ordinates is not in a merged region, * then will return the cell itself. * <p>If there is no cell defined at the given co-ordinates, will return * null. * * @param sheet The workbook sheet to look at. * @param rowIx The 0-based index of the row. * @param colIx The 0-based index of the cell. * @return cell at the given location, its base merged cell, or null if not defined * @throws NullPointerException if sheet is null */ public static Cell getCellWithMerges(Sheet sheet, int rowIx, int colIx) { final Cell c = getCell(sheet, rowIx, colIx); if (c != null) return c; for (CellRangeAddress mergedRegion : sheet.getMergedRegions()) { if (mergedRegion.isInRange(rowIx, colIx)) { // The cell wanted is in this merged range // Return the primary (top-left) cell for the range Row r = sheet.getRow(mergedRegion.getFirstRow()); if (r != null) { return r.getCell(mergedRegion.getFirstColumn()); } } } // If we get here, then the cell isn't defined, and doesn't // live within any merged regions return null; }
Example 4
Source File: ExcelOperator.java From minsx-framework with Apache License 2.0 | 6 votes |
private static Cell getMergedRegionCell(Sheet sheet, int rowNum, int columnNum) { int sheetMergeCount = sheet.getNumMergedRegions(); for (int i = 0; i < sheetMergeCount; i++) { CellRangeAddress ca = sheet.getMergedRegion(i); int firstColumn = ca.getFirstColumn(); int lastColumn = ca.getLastColumn(); int firstRow = ca.getFirstRow(); int lastRow = ca.getLastRow(); if (rowNum >= firstRow && rowNum <= lastRow) { if (columnNum >= firstColumn && columnNum <= lastColumn) { Row fRow = sheet.getRow(firstRow); return fRow.getCell(firstColumn); } } } return null; }
Example 5
Source File: ImportExcel.java From Shop-for-JavaWeb with MIT License | 6 votes |
/** * 获取单元格值 * @param row 获取的行 * @param column 获取单元格列号 * @return 单元格值 */ public Object getCellValue(Row row, int column){ Object val = ""; try{ Cell cell = row.getCell(column); if (cell != null){ if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC){ val = cell.getNumericCellValue(); }else if (cell.getCellType() == Cell.CELL_TYPE_STRING){ val = cell.getStringCellValue(); }else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA){ val = cell.getCellFormula(); }else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){ val = cell.getBooleanCellValue(); }else if (cell.getCellType() == Cell.CELL_TYPE_ERROR){ val = cell.getErrorCellValue(); } } }catch (Exception e) { return val; } return val; }
Example 6
Source File: ExcelFileReader.java From birt with Eclipse Public License 1.0 | 6 votes |
public boolean checkXlsEndOfRows(){ for(int cnt=currentRowIndex+1; cnt<= (currentRowIndex + ExcelODAConstants.BLANK_LOOK_AHEAD); cnt++){ Row row = sheet.getRow(cnt); if (row != null) { if (maxColumnIndex == 0) maxColumnIndex = row.getLastCellNum(); for (short colIx = 0; colIx < maxColumnIndex; colIx++) { Cell cell = row.getCell(colIx); String cellVal = getCellValue(cell); if( cell != null && cellVal != null &&!ExcelODAConstants.EMPTY_STRING.equals( cellVal ) ){ return false; } } } } return true; }
Example 7
Source File: VerificationSheet.java From tools with Apache License 2.0 | 5 votes |
/** * Import verification errors * @param verificationErrors Array of verification error message arraylists ordered by docname * @param docNames Name of documents relating to the errors * @throws SpreadsheetException */ public void importVerificationErrors( List<String>[] verificationErrors, String[] docNames) throws SpreadsheetException { if (verificationErrors == null) { throw(new SpreadsheetException("Verification errors not specified on import (null value).")); } if (docNames == null) { throw(new SpreadsheetException("Document names errors not specified on import (null value).")); } if (verificationErrors.length != docNames.length) { throw(new SpreadsheetException("Number of verification errors does not equal the number of documents.")); } if (docNames.length > MultiDocumentSpreadsheet.MAX_DOCUMENTS) { throw(new SpreadsheetException("Too many compare documents - must be less than "+String.valueOf(MultiDocumentSpreadsheet.MAX_DOCUMENTS+1))); } Row header = sheet.getRow(0); int lastRowCreated = 0; for (int i = 0; i < docNames.length; i++) { Cell hCell = header.getCell(i); hCell.setCellValue(docNames[i]); List<String> errors = verificationErrors[i]; if (errors != null) { for (int j = 0; j < errors.size(); j++) { Row errorRow; if (j+1 > lastRowCreated) { errorRow = sheet.createRow(j+1); lastRowCreated = j+1; } else { errorRow = sheet.getRow(j+1); } Cell errorCell = errorRow.createCell(i); errorCell.setCellValue(errors.get(j)); } } } }
Example 8
Source File: CellUtils.java From Octopus with MIT License | 5 votes |
public static void setCellValue(Sheet sheet, int row, int col, String value, CellStyle cellStyle) { Row sheetRow = sheet.getRow(row); if (sheetRow == null) { sheetRow = sheet.createRow(row); } Cell cell = sheetRow.getCell(col); if (cell == null) { cell = sheetRow.createCell(col); } cell.setCellValue(value); cell.setCellStyle(cellStyle); }
Example 9
Source File: ExcelDataProvider.java From NoraUi with GNU Affero General Public License v3.0 | 5 votes |
/** * {@inheritDoc} */ @Override public String readValue(String column, int line) throws TechnicalException { final int colIndex = columns.indexOf(column); final Sheet sheet = workbook.getSheetAt(0); final Row row = sheet.getRow(line); final Cell cell = row.getCell(colIndex); if (cell == null) { return ""; } else { return readCell(cell); } }
Example 10
Source File: ExcelHandler.java From development with Apache License 2.0 | 5 votes |
/** * Read a string from the excel cell in the given row with the given index. * * @param row * the row containing the cell to read. * @param idx * the index of the cell to read. * @param trim * <code>true</code> if the read value should be trimmed before * returning. * @return the read string or null if the cell doesn't exist or contains a * blank string. * @throws TranslationImportException */ public static String getCellValue(Row row, int idx, boolean trim) throws TranslationImportException { String val = null; Cell cell = row.getCell(idx); if (cell != null) { val = cell.getStringCellValue(); if (val != null) { if (trim) { val = val.trim(); } } } return val; }
Example 11
Source File: ReviewersSheet.java From tools with Apache License 2.0 | 5 votes |
public String getReviewer(int rowNum) { Row row = sheet.getRow(rowNum); if (row == null) { return null; } Cell reviewer = row.getCell(REVIEWER_COL); if (reviewer == null) { return null; } return reviewer.getStringCellValue(); }
Example 12
Source File: PackageInfoSheetV09d3.java From tools with Apache License 2.0 | 5 votes |
@Override public String verify() { try { if (sheet == null) { return "Worksheet for SPDX Package Info does not exist"; } if (!OriginsSheet.verifyVersion(version)) { return "Unsupported version "+version; } Row firstRow = sheet.getRow(firstRowNum); for (int i = 0; i < NUM_COLS; i++) { Cell cell = firstRow.getCell(i+firstCellNum); if (cell == null || cell.getStringCellValue() == null || !cell.getStringCellValue().equals(HEADER_TITLES[i])) { return "Column "+HEADER_TITLES[i]+" missing for SPDX Package Info worksheet"; } } // validate rows boolean done = false; int rowNum = firstRowNum + 1; while (!done) { Row row = sheet.getRow(rowNum); if (row == null || row.getCell(firstCellNum) == null) { done = true; } else { String error = validateRow(row); if (error != null) { return error; } rowNum++; } } return null; } catch (Exception ex) { return "Error in verifying SPDX Package Info work sheet: "+ex.getMessage(); } }
Example 13
Source File: NonStandardLicensesSheet.java From tools with Apache License 2.0 | 5 votes |
private String validateRow(Row row) { for (int i = 0; i < NUM_COLS; i++) { Cell cell = row.getCell(i); if (cell == null) { if (REQUIRED[i]) { return "Required cell "+HEADER_TITLES[i]+" missing for row "+String.valueOf(row.getRowNum()); } } else { // if (cell.getCellType() != Cell.CELL_TYPE_STRING) { // return "Invalid cell format for "+HEADER_TITLES[i]+" for forw "+String.valueOf(row.getRowNum()); // } } } return null; }
Example 14
Source File: PoiSheetUtility.java From autopoi with Apache License 2.0 | 5 votes |
/** * Given a sheet, this method deletes a column from a sheet and moves all * the columns to the right of it to the left one cell. * * Note, this method will not update any formula references. * * @param sheet * @param column */ public static void deleteColumn(Sheet sheet, int columnToDelete) { int maxColumn = 0; for (int r = 0; r < sheet.getLastRowNum() + 1; r++) { Row row = sheet.getRow(r); // if no row exists here; then nothing to do; next! if (row == null) continue; // if the row doesn't have this many columns then we are good; next! int lastColumn = row.getLastCellNum(); if (lastColumn > maxColumn) maxColumn = lastColumn; if (lastColumn < columnToDelete) continue; for (int x = columnToDelete + 1; x < lastColumn + 1; x++) { Cell oldCell = row.getCell(x - 1); if (oldCell != null) row.removeCell(oldCell); Cell nextCell = row.getCell(x); if (nextCell != null) { Cell newCell = row.createCell(x - 1, nextCell.getCellType()); cloneCell(newCell, nextCell); } } } // Adjust the column widths for (int c = 0; c < maxColumn; c++) { sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1)); } }
Example 15
Source File: OriginsSheetV1d1.java From tools with Apache License 2.0 | 5 votes |
public void setCreatedBy(String[] createdBy) { if (createdBy == null || createdBy.length < 1) { setDataCellStringValue(CREATED_BY_COL, ""); int i = firstRowNum + DATA_ROW_NUM + 1; Row nextRow = sheet.getRow(i); while (nextRow != null) { Cell createdByCell = nextRow.getCell(CREATED_BY_COL); if (createdByCell != null) { createdByCell.setCellValue(""); } i++; nextRow = sheet.getRow(i); } return; } setDataCellStringValue(CREATED_BY_COL, createdBy[0]); for (int i = 1; i < createdBy.length; i++) { Row row = sheet.getRow(firstRowNum + DATA_ROW_NUM + i); if (row == null) { row = sheet.createRow(firstRowNum + DATA_ROW_NUM + i); } Cell cell = row.getCell(CREATED_BY_COL); if (cell == null) { cell = row.createCell(CREATED_BY_COL); } cell.setCellValue(createdBy[i]); } }
Example 16
Source File: XssfWSheetImpl.java From xlsbeans with Apache License 2.0 | 5 votes |
public WCell getCell(int column, int row) { Row xssfrow = sheet.getRow(row); if (xssfrow == null) { return new XssfBlankWCellImpl(column, row); } Cell cell = xssfrow.getCell(column); if (cell == null) { return new XssfBlankWCellImpl(column, row); } return new XssfWCellImpl(xssfrow.getCell(column)); }
Example 17
Source File: ExcelUtil.java From supplierShop with MIT License | 4 votes |
/** * 获取单元格值 * * @param row 获取的行 * @param column 获取单元格列号 * @return 单元格值 */ public Object getCellValue(Row row, int column) { if (row == null) { return row; } Object val = ""; try { Cell cell = row.getCell(column); if (cell != null) { if (cell.getCellTypeEnum() == CellType.NUMERIC || cell.getCellTypeEnum() == CellType.FORMULA) { val = cell.getNumericCellValue(); if (HSSFDateUtil.isCellDateFormatted(cell)) { val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换 } else { if ((Double) val % 1 > 0) { val = new DecimalFormat("0.00").format(val); } else { val = new DecimalFormat("0").format(val); } } } else if (cell.getCellTypeEnum() == CellType.STRING) { val = cell.getStringCellValue(); } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) { val = cell.getBooleanCellValue(); } else if (cell.getCellTypeEnum() == CellType.ERROR) { val = cell.getErrorCellValue(); } } } catch (Exception e) { return val; } return val; }
Example 18
Source File: OriginsSheetV0d9d4.java From tools with Apache License 2.0 | 4 votes |
@Override public String verify() { try { if (sheet == null) { return "Worksheet for SPDX Origins does not exist"; } // validate version version = getDataCellStringValue(SPREADSHEET_VERSION_COL); if (version == null) { return "Invalid origins spreadsheet - no spreadsheet version found"; } if (!SPDXSpreadsheet.verifyVersion(version)) { return "Spreadsheet version "+version+" not supported."; } Row firstRow = sheet.getRow(firstRowNum); for (int i = 0; i < NUM_COLS; i++) { Cell cell = firstRow.getCell(i+firstCellNum); if (cell == null || cell.getStringCellValue() == null || !cell.getStringCellValue().equals(HEADER_TITLES[i])) { return "Column "+HEADER_TITLES[i]+" missing for SPDX Origins worksheet"; } } // validate rows boolean done = false; int rowNum = firstRowNum + 1; while (!done) { Row row = sheet.getRow(rowNum); if (row == null || row.getCell(SPDX_VERSION_COL) == null) { done = true; } else { String error = validateRow(row); if (error != null) { return error; } rowNum++; } } return null; } catch (Exception ex) { return "Error in verifying SPDX Origins work sheet: "+ex.getMessage(); } }
Example 19
Source File: MSExcelParser.java From hadoopoffice with Apache License 2.0 | 4 votes |
@Override public Object[] getNext() { SpreadSheetCellDAO[] result=null; // all sheets? if (this.sheets==null) { // go on with all sheets if (!nextAllSheets()) { return result; } } else { // go on with specified sheets if (!nextSpecificSheets()) { return result; } } // read row from the sheet currently to be processed Sheet rSheet = this.currentWorkbook.getSheetAt(this.currentSheet); Row rRow = rSheet.getRow(this.currentRow); if ((rRow==null) || (rRow.getLastCellNum()<0)) { this.currentRow++; return new SpreadSheetCellDAO[0]; // emtpy row } result = new SpreadSheetCellDAO[rRow.getLastCellNum()]; for (int i=0;i<rRow.getLastCellNum();i++) { Cell currentCell=rRow.getCell(i); if (currentCell==null) { result[i]=null; } else { String formattedValue=useDataFormatter.formatCellValue(currentCell,this.formulaEvaluator); String formula = ""; if (currentCell.getCellType()==CellType.FORMULA) { formula = currentCell.getCellFormula(); } Comment currentCellComment = currentCell.getCellComment(); String comment = ""; if (currentCellComment!=null) { comment = currentCellComment.getString().getString(); } String address = currentCell.getAddress().toString(); String sheetName = currentCell.getSheet().getSheetName(); SpreadSheetCellDAO mySpreadSheetCellDAO = new SpreadSheetCellDAO(formattedValue,comment,formula,address,sheetName); result[i]=mySpreadSheetCellDAO; } } // increase rows this.currentRow++; return result; }
Example 20
Source File: ExcelUserBulkImport.java From carbon-identity-framework with Apache License 2.0 | 4 votes |
public void addUserList(UserStoreManager userStore) throws UserAdminException { Workbook wb = this.createWorkbook(); Sheet sheet = wb.getSheet(wb.getSheetName(0)); userStoreDomain = config.getUserStoreDomain(); if (sheet == null || sheet.getLastRowNum() == -1) { throw new UserAdminException("The first sheet is empty"); } int limit = sheet.getLastRowNum(); boolean isDuplicate = false; boolean fail = false; for (int i = 1; i < limit + 1; i++) { Row row = sheet.getRow(i); Cell cell = row.getCell(0); String userName = cell.getStringCellValue(); int index; index = userName.indexOf(CarbonConstants.DOMAIN_SEPARATOR); if (index > 0) { String domainFreeName = userName.substring(index + 1); userName = UserCoreUtil.addDomainToName(domainFreeName, userStoreDomain); } else { userName = UserCoreUtil.addDomainToName(userName, userStoreDomain); } if (StringUtils.isNotBlank(userName)) { try { if (!userStore.isExistingUser(userName)) { userStore.addUser(userName, null, null, null, null, true); successCount++; if (log.isDebugEnabled()) { log.debug("User import successful - Username : " + userName); } } else { duplicateCount++; duplicateUsers.add(userName); isDuplicate = true; log.error("User import unsuccessful - Username : " + userName + " - Error: Duplicate user"); duplicateUsers.add(userName); } } catch (UserStoreException e) { fail = true; failCount++; log.error("User import unsuccessful - Username : " + userName + " - Error: " + e.getMessage()); errorUsersMap.put(userName, e.getMessage()); } } } String summeryLog = super.buildBulkImportSummary(); log.info(summeryLog); JSONConverter jsonConverter = new JSONConverter(); String importedUsers = jsonConverter.xlsToJSON(sheet); auditLog.info(String.format(UserMgtConstants.AUDIT_LOG_FORMAT, tenantUser, UserMgtConstants.OPERATION_NAME, userStoreDomain, importedUsers, summeryLog)); if (fail || isDuplicate) { throw new UserAdminException(String.format(UserMgtConstants.ERROR_MESSAGE, successCount, failCount, duplicateCount)); } }