Java Code Examples for org.apache.poi.ss.usermodel.Row#getCell()

@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;
	}
 

/**
 * 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();
}
 

/**
 * 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;
}
 

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;
}
 

/**
 * 获取单元格值
 * @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;
}
 

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;
}
 

/**
 * 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));
			}
		}
	}
}
 

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);
}
 

/**
 * {@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);
    }
}
 

/**
 * 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;
}
 

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();
}
 

@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();
	}
}
 

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;
	}
 

/**
 * 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));
	}
}
 

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]);
	}
}
 

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));
}
 

/**
 * 获取单元格值
 * 
 * @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;
}
 

@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();
	}
}
 

@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;
}
 

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));
        }
    }