Java Cell类代码示例

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
public Object getCellValue(Cell cell){
    Object value = null;
    DecimalFormat df = new DecimalFormat("0");  //格式化number String字符
    DecimalFormat df2 = new DecimalFormat("0.00");  //格式化数字
    switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            value = cell.getRichStringCellValue().getString();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            if("General".equals(cell.getCellStyle().getDataFormatString())) {
                value = df.format(cell.getNumericCellValue());
            } else {
                value = df2.format(cell.getNumericCellValue());
            }
            break;
        case Cell.CELL_TYPE_BOOLEAN:
            value = cell.getBooleanCellValue();
            break;
        case Cell.CELL_TYPE_BLANK:
            value = "";
            break;
        default:
            break;
    }
    return value;
}
 

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
/**
 * 获取excel列表头
 *
 * @param titleRow excel行
 * @param clz      类型
 * @return ExcelHeader集合
 * @throws InstantiationException 异常
 * @throws IllegalAccessException 异常
 */
public static Map<Integer, ExcelHeader> getHeaderMap(Row titleRow, Class<?> clz)
        throws InstantiationException, IllegalAccessException {

    List<ExcelHeader> headers = getHeaderList(clz);
    Map<Integer, ExcelHeader> maps = new HashMap<>();
    for (Cell c : titleRow) {
        String title = c.getStringCellValue();
        for (ExcelHeader eh : headers) {
            if (eh.getTitle().equals(title.trim())) {
                maps.put(c.getColumnIndex(), eh);
                break;
            }
        }
    }
    return maps;
}
 

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
private void setSheetData(SheetData data, String group) {
	data.setCurrentGroup(group);
	// start from 1
	data.setCurrentIndex(1);
	// get sheet
	Sheet vSheet = getWorkBook().getSheet(group);
	Assert.notNull(vSheet, "Can't get sheet with name: " + group);
	data.setSheet(vSheet);
	// get row number
	int vRowCount = vSheet.getLastRowNum() + 1;
	data.setRowCount(vRowCount);
	// get first row
	Row vRow = vSheet.getRow(0);
	Assert.notNull(vRow, "Invalid format: first row must be title");
	// get column number
	int vColumnCount = vRow.getLastCellNum();
	String[] vTitles = new String[vColumnCount];
	// read titles
	for (int i = 0; i < vColumnCount; ++i) {
		Cell vCell = vRow.getCell(i);
		vTitles[i] = vCell.getStringCellValue();
	}
	data.setTitles(vTitles);
}
 

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
public void print () {
	Sheet sheet =  getOrCreateSummary();
	Iterator<Row> rows = sheet.rowIterator();
	int index=0;
	while (rows.hasNext()) {
		Row row = (Row) rows.next();
		System.out.println("Row ---> " + index);
		Spliterator<Cell> cells = row.spliterator();
		cells.forEachRemaining(new Consumer<Cell> () {
			@Override
			public void accept(Cell cell) {
				System.out.print(cell.toString());
				System.out.print(";");
			}
		});
		System.out.println();
		index++;
	}
}
 

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
@Override
public IRow readRow() {
	SheetData vData = getLocalData();
	int vRowIndex = vData.getCurrentIndex();
	if (vRowIndex < vData.getRowCount()) {
		Row vRow = vData.getSheet().getRow(vRowIndex);
		String[] vTitles = vData.getTitles();
		Cell[] vValues = new Cell[vTitles.length];
		for (int i = 0; i < vTitles.length; ++i) {
			Cell vCell = vRow.getCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
			vValues[i] = vCell;
		}
		vData.setCurrentIndex(vRowIndex + 1);
		return new RowExcelImpl(vRowIndex, vTitles, vValues);
	}
	// read over, remove the local data
	removeLocalData();
	return null;
}
 

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
public void set(int sheetIndex, int x_index, int y_index, String value) {
    try {
        if (wb == null) throw new Exception("未打开文件");
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        Cell cell = null;
        row = sheet.getRow(x_index);
        if (row == null) {
            row = sheet.createRow(x_index);
        }
        cell = row.getCell(y_index);
        if (cell == null) {
            cell = row.createCell(y_index);
        }
        cell.setCellValue(value);
        save();
    } catch (Exception e) {
        logger.error(e.getMessage());
    }
}
 

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
/**
 * Método que se encarga de leer el libro de excel
 * cuya ruta recibimos en el constructor y devuelve una lista 
 * de ciudadanos
 * @return lista de Usuarios de la base de datos
 */
public List<CitizenDB> readExcelFile(){
	List<CitizenDB> citizens = new ArrayList<CitizenDB>(); 
	// para cada una de las hojas presentes en el documento de excel
	for(int i=0;i < workbook.getNumberOfSheets();i++){
		XSSFSheet sheet = this.workbook.getSheetAt(i);
		Iterator<Row> rowIterator = sheet.iterator();
		Row row;
		int counter = 0;
		//para cada fila de la hoja
		while(rowIterator.hasNext()){
			row = rowIterator.next();
			if (counter > 0) { //omitimos la cabecera (hay que mirar si hay un metodo de la API)
				Iterator<Cell> cellIterator = row.cellIterator();
				int j = 0;
				CitizenDB user = new CitizenDB();
				while (cellIterator.hasNext()) 	
					this.insertCitizenField(user, j++, cellIterator.next());	
				user.setPassword(new GenerationPassword().passwordGenerator());
				citizens.add(user);
			}
			counter++;
		}
	}
	return citizens;
}
 

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
private String getInsertStatementForCell(String userId, Cell cell) {
    String answer = null;
    switch (cell.getColumnIndex()) {
    case 1:
        answer = getInsertStatement(userId, 2237);
        break;
    case 2:
        answer = getInsertStatement(userId, 4352);
        break;
    case 3:
        answer = getInsertStatement(userId, 3657);
        break;
    case 4:
        answer = getInsertStatement(userId, 5565);
        break;
    }
    
    return answer;
}
 

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
@Override
public void next(ProgressListener listener) {
	currentRow++;
	while (currentRow < totalNumberOfRows + rowOffset && emptyRows[currentRow - rowOffset]) {
		currentRow++;
	}

	if (currentRow >= totalNumberOfRows + rowOffset) {
		throw new NoSuchElementException("No further row in excel sheet.");
	}

	currentRowCells = new Cell[attributeNames.length];
	int columnCounter = 0;
	for (int c = 0; c < totalNumberOfColumns; c++) {
		if (!emptyColumns[c]) {
			currentRowCells[columnCounter] = sheet.getRow(currentRow).getCell(c + columnOffset);
			columnCounter++;
		}
	}

	// notifying progress listener
	if (listener != null) {
		listener.setCompleted(currentRow);
	}
}
 

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
public void feedDetailsSheet(Sheet sheet, boolean exportAsTemplate, List<XLTestStep> xLTestSteps) {
	int index = 0;
	for (XLTestStep xLTestStep : xLTestSteps) {
		index++;
		Row row = sheet.createRow(index);
		Cell cell = row.createCell(STEPNAME_INDEX);
		cell.setCellValue(xLTestStep.getName());
		cell = row.createCell(EXPECTED_OR_ACTION_INDEX);
		cell.setCellValue(xLTestStep.getExpected());
		cell = row.createCell(RESULT_INDEX);
		if (exportAsTemplate)
			cell.setCellValue("");
		else
			cell.setCellValue(xLTestStep.getActual());
		cell = row.createCell(STATUS_INDEX);
		formatCellStatus(sheet, cell);
		if (exportAsTemplate)
			cell.setCellValue("");
		else
			cell.setCellValue(Integer.parseInt(xLTestStep.getStatus()));
	}
	this.autoSize(sheet, new int[] { 0, 1, 2, 3 });
}
 

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
/**
 * {@inheritDoc}
 */
@Override
public String[] readLine(int line, boolean readResult) throws TechnicalException {
    final Sheet sheet = workbook.getSheetAt(0);
    final Row row = sheet.getRow(line);
    if (row == null || "".equals(readCell(row.getCell(0)))) {
        return null;
    } else {
        final String[] ret = readResult ? new String[columns.size()] : new String[columns.size() - 1];
        Cell cell;
        for (int i = 0; i < ret.length; i++) {
            if ((cell = row.getCell(i)) == null) {
                ret[i] = "";
            } else {
                ret[i] = readCell(cell);
            }
        }
        return ret;
    }
}
 

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
protected void setCellValue(Cell sheetCell, Object value, Class<?> valueType, Object propId) {
    if (null != value) {
        if (!isNumeric(valueType)) {
            if (java.util.Date.class.isAssignableFrom(valueType)) {
                sheetCell.setCellValue((Date) value);
            } else {
                sheetCell.setCellValue(createHelper.createRichTextString(value.toString()));
            }
        } else {
            try {
                // parse all numbers as double, the format will determine how they appear
                final Double d = Double.parseDouble(value.toString());
                sheetCell.setCellValue(d);
            } catch (final NumberFormatException nfe) {
                LOGGER.warning("NumberFormatException parsing a numeric value: " + nfe);
                sheetCell.setCellValue(createHelper.createRichTextString(value.toString()));
            }
        }
    }
}
 

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
protected void formatCellStatus(Sheet sheet, Cell cell) {
	cell.setCellStyle(styles.get("status"));
	SheetConditionalFormatting sheetCF = sheet.getSheetConditionalFormatting();
	ConditionalFormattingRule ruleGreen = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "1");
	PatternFormatting fill1 = ruleGreen.createPatternFormatting();
	fill1.setFillBackgroundColor(IndexedColors.GREEN.index);
	fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	//
	ConditionalFormattingRule ruleRed = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "0");
	PatternFormatting fill2 = ruleRed.createPatternFormatting();
	fill2.setFillBackgroundColor(IndexedColors.RED.index);
	fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	//
	ConditionalFormattingRule ruleOrange = sheetCF.createConditionalFormattingRule(ComparisonOperator.EQUAL, "2");
	PatternFormatting fill3 = ruleOrange.createPatternFormatting();
	fill3.setFillBackgroundColor(IndexedColors.ORANGE.index);
	fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND);
	//
	String name = CellReference.convertNumToColString(cell.getColumnIndex());
	String location = "$" + name + "$" + cell.getRowIndex() + ":$" + name + "$" + (cell.getRowIndex() + 1);

	CellRangeAddress[] regions = { CellRangeAddress.valueOf(location) };
	ConditionalFormattingRule[] cfRules = new ConditionalFormattingRule[] { ruleGreen, ruleRed, ruleOrange };
	sheetCF.addConditionalFormatting(regions, cfRules);
}
 

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
/**
 * 向当前Sheet第一行(1-based)写入标题,若用户没有开启写入标题总开关(即{@link #isWriteTitle}为false),
 * 或者{@link #titles}为空则不会做任何操作
 */
private void writeTitle() {
    if (!this.isWriteTitle || this.titles == null || this.titles.isEmpty()) {
        return;
    }
    this.currentRowInSheet++;
    Row row = this.currentSheetPO.createRow(this.currentRowInSheet);
    row.setHeight(this.rowHeight < 0 ? -1 : this.rowHeight);
    for (int i = 0; i < this.titles.size(); i++) {
        Cell cell = row.createCell(i);
        cell.setCellStyle(this.defaultTitleCellStyle);
        cell.setCellValue(this.titles.get(i));
    }
    this.realRowInSheet++;
    this.realRowInExcel++;
}
 

import org.apache.poi.ss.usermodel.Cell; //导入依赖的package包/类
/**
 * 判断空行
 */
private static boolean isBlankRow(Row row) {
    if (row == null) {
        return true;
    }
    boolean result = true;
    Iterator<Cell> cells = row.cellIterator();
    String value = "";
    while (cells.hasNext()) {
        Cell cell = cells.next();
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC:
                value = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = String.valueOf(cell.getCellFormula());
                break;
        }
        if (StringUtils.isNotBlank(value)) {
            result = false;
            break;
        }
    }

    return result;
}