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

/**
 * 获取Sheet页内容
 *
 * @param sheet
 * @return
 */
public static final List<Object[]> listFromSheet(Sheet sheet) {

    List<Object[]> list = new ArrayList<Object[]>();
    for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
        Row row = sheet.getRow(r);
        if (row == null || row.getPhysicalNumberOfCells() == 0) continue;
        Object[] cells = new Object[row.getLastCellNum()];
        for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell == null) continue;
            //判断是否为日期类型
            cells[c] = getValueFromCell(cell);
        }
        list.add(cells);
    }
    return list;
}
 

@Override
public List<String> mapTitleRow(Sheet sheet){
	Row row = sheet.getRow(getTitleRowIndex());
	int cellCount = row.getPhysicalNumberOfCells();
	List<String> rowValues = new ArrayList<String>();
	
	Cell cell = null;
	Object cellValue = null;
	for(int i=0; i<cellCount; i++){
		cell = row.getCell(i);
		cellValue = ExcelUtils.getCellValue(cell, convertCellTypeAsString);
		String label = ExcelUtils.trimToEmpty(cellValue);
		if(ExcelUtils.isBlank(label) && i-1>0){
			//if title is empty (region column), get the previous title name
			label = rowValues.get(i-1);
		}
		rowValues.add(label);
	}
	return rowValues;
}
 

public static Map<String, Object> mapRow(Row row, List<String> names){
	int cellCount = row.getPhysicalNumberOfCells();
	
	Cell cell = null;
	Object cellValue = null;
	Map<String, Object> rowValue = new HashMap<String, Object>();
	for(int i=0; i<names.size(); i++){
		String colName = names.get(i);
		if(i<=cellCount){
			cell = row.getCell(i);
			cellValue = ExcelUtils.getCellValue(cell);
		}
		rowValue.put(colName, cellValue);
	}
	return rowValue;
}
 

/**
 * Sets the indexes based on the header.
 *
 * @param row
 */
protected void prepare(Row row) {

    for (ImportColumn c : super.getColumns()) {
        ImportColumnExcel column = (ImportColumnExcel) c;
        if (!column.isIndexSpecified()) {
            boolean found = false;
            for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
                row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
                if (row.getCell(i).getStringCellValue().equals(column.getName())) {
                    found = true;
                    column.setIndex(i);
                }
            }
            if (!found) {
                throw new IllegalArgumentException("Index for column '" + column.getName() + "' couldn't be found");
            }
        }
    }
}
 

/**
 * 获取表头元数据
 *
 * @param filePath
 * @return 属性名称列表、字段类型、描述说明
 */
public static Args.Three<List<String>, List<String>, List<String>> getMetaData(String filePath, String sheetName) throws Exception {
    Workbook workBook = getWorkBook(filePath);
    if (workBook == null) {
        return null;
    }
    Sheet sheet = workBook.getSheet(sheetName);
    if (sheet == null) {
        return null;
    }

    List<String> fieldList = new ArrayList<>();
    List<String> typeList = new ArrayList<>();
    List<String> descList = new ArrayList<>();

    //前三行为元数据
    for (int i = 0; i < 3; i++) {
        Row row = sheet.getRow(i);
        if (row == null) {
            continue;
        }
        int lastCellNum = row.getPhysicalNumberOfCells();
        for (int j = 0; j < lastCellNum; j++) {
            String value = row.getCell(j).toString();
            switch (i) {
                case 0:
                    fieldList.add(value);
                    break;
                case 1:
                    typeList.add(value);
                    break;
                default:
                    descList.add(value);
                    break;
            }
        }
    }
    workBook.close();
    return Args.of(fieldList, typeList, descList);
}
 

private MetaData parseHeader(DataStore dataStore, Row row) {
	MetaData dataStoreMeta = new MetaData();

	int cells = row.getPhysicalNumberOfCells();
	int lastColumn = row.getLastCellNum();

	if (cells != lastColumn) {
		cells = lastColumn;
	}
	this.setNumberOfColumns(cells);
	logger.debug("\nROW " + row.getRowNum() + " has " + cells + " cell(s).");
	for (int c = 0; c < cells; c++) {
		// get single cell
		Cell cell = row.getCell(c);

		Object valueField = null;
		try {
			valueField = parseCell(cell);
		} catch (Throwable t) {
			throw new RuntimeException("Impossible to parse cell [" + c + "]", t);
		}

		if (valueField == null) {
			valueField = new String();
			valueField = "Column " + String.valueOf(c + 1);
		}
		FieldMetadata fieldMeta = new FieldMetadata();
		String fieldName = StringUtils.escapeForSQLColumnName(valueField.toString());
		fieldMeta.setName(fieldName);
		fieldMeta.setType(String.class);

		dataStoreMeta.addFiedMeta(fieldMeta);
	}

	return dataStoreMeta;
}
 

@Override
public List<Object> mapDataRow(List<String> names, Row row, int rowIndex) {
	int cellCount = row.getPhysicalNumberOfCells();

	List<Object> list = new ArrayList<Object>();
	Object val = null;
	for (int i = 0; i < cellCount; i++) {
		Cell cell = row.getCell(i);
		if(cell==null)
			continue;
		val = getCellValue(cell);
		list.add(val);
	}
	return list;
}
 

public static List<String> getRowValues(Row row){
	int cellCount = row.getPhysicalNumberOfCells();
	List<String> rowValues = new ArrayList<String>();
	
	Cell cell = null;
	Object cellValue = null;
	for(int i=0; i<cellCount; i++){
		cell = row.getCell(i);
		cellValue = ExcelUtils.getCellValue(cell);
		rowValues.add(cellValue.toString().trim());
	}
	return rowValues;
}
 

public static void parseCommonRow(Row row, ExcelTemplateValueProvider provider){
	int cellNumbs = row.getPhysicalNumberOfCells();
	for (int cellIndex = 0; cellIndex < cellNumbs; cellIndex++) {
		Cell cell = row.getCell(cellIndex);
		Object cellValue = getCellValue(cell);
		if(cellValue==null)
			continue;

		String cellText = cellValue.toString();
		if(provider.isExpresstion(cellText)){
			Object newCellValue = provider.parseCellValue(cellText);
			provider.setCellValue(cell, newCellValue);
		}
	}
}
 

private String[] readRow(int index) {
    Row row = sheet.getRow(index);
    if (row == null || row.getPhysicalNumberOfCells() == 0) {
        return new String[0];
    }

    String[] record = new String[row.getLastCellNum()];
    for (int i = 0; i < record.length; i++) {
        record[i] =  Optional.ofNullable(row.getCell(i)).map(Object::toString).orElse(null);
    }
    return record;
}
 

private IRecord parseRow(DataStore dataStore, Row row) {

		IRecord record = new Record(dataStore);

		int cells = row.getPhysicalNumberOfCells();
		// int lastColumn = row.getLastCellNum();
		int lastColumn = this.getNumberOfColumns();

		logger.debug("\nROW " + row.getRowNum() + " has " + cells + " cell(s).");
		for (int c = 0; c < lastColumn; c++) {
			// get single cell
			Cell cell = row.getCell(c);

			Object valueField = null;
			try {
				valueField = parseCell(cell);
			} catch (Throwable t) {
				throw new RuntimeException("Impossible to parse cell [" + c + "]", t);
			}

			if (valueField != null && valueField instanceof Double) {
				((FieldMetadata) dataStore.getMetaData().getFieldMeta(c)).setType(Double.class);
			} else if (valueField != null && valueField instanceof BigDecimal) {
				((FieldMetadata) dataStore.getMetaData().getFieldMeta(c)).setType(BigDecimal.class);
			} else if (valueField != null && valueField instanceof Integer) {
				((FieldMetadata) dataStore.getMetaData().getFieldMeta(c)).setType(Integer.class);
			} else if (valueField != null && valueField instanceof Long) {
				((FieldMetadata) dataStore.getMetaData().getFieldMeta(c)).setType(Long.class);
			} else if (valueField != null && valueField instanceof Date) {
				if (valueField instanceof Timestamp) {
					((FieldMetadata) dataStore.getMetaData().getFieldMeta(c)).setType(Timestamp.class);
				} else {
					((FieldMetadata) dataStore.getMetaData().getFieldMeta(c)).setType(Date.class);
				}
			} else {
				((FieldMetadata) dataStore.getMetaData().getFieldMeta(c)).setType(String.class);
			}

			IField field = new Field(valueField);

			record.appendField(field);
		}

		return record;
	}
 

protected Workbook makeWorkbook(String sheetName, List<List<Object>> rowSpecs){
    Workbook workbook = new HSSFWorkbook();
    Sheet sheet = workbook.createSheet(sheetName);

    for (List<Object> rowSpec : rowSpecs) {
        int rowId = (sheet.getPhysicalNumberOfRows()==0)?0:sheet.getLastRowNum()+1;
        Row row = sheet.createRow(rowId);

        for (Object cellSpec : rowSpec) {
            // Note that sheet.getLastRowNum() and row.getLastCellNum() do not behave alike
            int cellId = (row.getPhysicalNumberOfCells()==0)?0:row.getLastCellNum();
            if (cellSpec == null) {
                row.createCell(cellId).setCellType(Cell.CELL_TYPE_BLANK);
                continue;
            }
            switch (cellSpec.getClass().getCanonicalName()) {
                case "java.lang.Integer":
                    row.createCell(cellId).setCellValue((Integer)cellSpec);
                    break;
                case "java.lang.String":
                    row.createCell(cellId).setCellValue((String)cellSpec);
                    break;
                case "java.lang.Double":
                    row.createCell(cellId).setCellValue((Double)cellSpec);
                    break;
                case "java.lang.Boolean":
                    row.createCell(cellId).setCellValue((Boolean)cellSpec);
                    break;
                case "java.util.Date":
                    row.createCell(cellId).setCellValue((Date)cellSpec);
                    break;
                case "java.util.Calendar":
                    row.createCell(cellId).setCellValue((Calendar)cellSpec);
                    break;
                case "org.apache.poi.ss.formula.Formula":
                    row.createCell(cellId).setCellType(Cell.CELL_TYPE_FORMULA);
                    Cell cell = row.getCell(row.getLastCellNum());
                    cell.setCellFormula(((Formula)cellSpec).toString());
                    break;
                default:
                    row.createCell(cellId).setCellType(Cell.CELL_TYPE_BLANK);
            }
        }
    }
    return workbook;
}
 

/**
 * 엑셀파일로부터 데이터를 읽어 리턴한다.     *
 */
public static List getData(Workbook wb) {
    List excelList = new ArrayList();

    int sheetNum = wb.getNumberOfSheets();

    for (int k=0; k<sheetNum; k++) {
        Sheet sheet = wb.getSheetAt(k);
        int rows = sheet.getPhysicalNumberOfRows();

        for (int r=0; r<rows; r++) {
            Row row = sheet.getRow(r);

            if (row != null) {
                int cells = row.getPhysicalNumberOfCells();
                String[] excelRow = new String[cells];
                for(int c=0; c<cells; c++) {
                    Cell cell = row.getCell(c);
                    if (cell != null) {
                        String value = null;

                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_FORMULA:
                                value = cell.getCellFormula();
                            break;

                            case Cell.CELL_TYPE_NUMERIC:
                                value = "" + Integer.parseInt(String.valueOf(Math.round(cell.getNumericCellValue())));
                            break;

                            case Cell.CELL_TYPE_STRING:
                                value = "" + cell.getStringCellValue();
                            break;

                            case Cell.CELL_TYPE_BLANK:
                                value = "" + cell.getBooleanCellValue();
                            break;

                            case Cell.CELL_TYPE_ERROR:
                                value = "" + cell.getErrorCellValue();
                            break;
                            default:
                        }
                        excelRow[c] = value;
                    }
                }
                excelList.add(excelRow);
            }
        }
    }
    return excelList;
}
 

/**
 * create table by sheet
 *
 * @param sheet
 */
private void createTable(Sheet sheet) {
    StringBuilder createTableSql = new StringBuilder("CREATE TABLE IF NOT EXISTS ");
    createTableSql.append(sheet.getSheetName());
    createTableSql.append("(");
    Iterator<Row> rit = sheet.rowIterator();
    Row rowHeader = rit.next();
    List<String> columns = new ArrayList<>();
    for (int i = 0; i < rowHeader.getPhysicalNumberOfCells(); i++) {
        createTableSql.append(rowHeader.getCell(i).getStringCellValue());
        if (i == rowHeader.getPhysicalNumberOfCells() - 1) {
            createTableSql.append(" TEXT");
        } else {
            createTableSql.append(" TEXT,");
        }
        columns.add(rowHeader.getCell(i).getStringCellValue());
    }
    createTableSql.append(")");
    database.execSQL(createTableSql.toString());
    while (rit.hasNext()) {
        Row row = rit.next();
        ContentValues values = new ContentValues();
        for (int n = 0; n < row.getPhysicalNumberOfCells(); n++) {
            if (row.getCell(n) == null) {
                continue;
            }
            if (row.getCell(n).getCellType() == Cell.CELL_TYPE_NUMERIC) {
                if (HSSFDateUtil.isCellDateFormatted(row.getCell(n))) {
                    if (sdf == null) {
                        values.put(columns.get(n), DateFormat.getDateTimeInstance().format(row.getCell(n).getDateCellValue()));
                    } else {
                        values.put(columns.get(n), sdf.format(row.getCell(n).getDateCellValue()));
                    }
                } else {
                    String value = getRealStringValueOfDouble(row.getCell(n).getNumericCellValue());
                    values.put(columns.get(n), value);
                }
            } else if (row.getCell(n).getCellType() == Cell.CELL_TYPE_STRING) {
                values.put(columns.get(n), row.getCell(n).getStringCellValue());
            }
        }
        if (values.size() == 0)
            continue;
        long result = database.insert(sheet.getSheetName(), null, values);
        if (result < 0) {
            throw new RuntimeException("Insert value failed!");
        }
    }
}
 

/**
 * 엑셀파일로부터 데이터를 읽어 리턴한다.     *
 */
public static List getData(Workbook wb) {
    List excelList = new ArrayList();

    int sheetNum = wb.getNumberOfSheets();

    for (int k=0; k<sheetNum; k++) {
        Sheet sheet = wb.getSheetAt(k);
        int rows = sheet.getPhysicalNumberOfRows();

        for (int r=0; r<rows; r++) {
            Row row = sheet.getRow(r);

            if (row != null) {
                int cells = row.getPhysicalNumberOfCells();
                String[] excelRow = new String[cells];
                for(int c=0; c<cells; c++) {
                    Cell cell = row.getCell(c);
                    if (cell != null) {
                        String value = null;

                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_FORMULA:
                                value = cell.getCellFormula();
                            break;

                            case Cell.CELL_TYPE_NUMERIC:
                                value = "" + Integer.parseInt(String.valueOf(Math.round(cell.getNumericCellValue())));
                            break;

                            case Cell.CELL_TYPE_STRING:
                                value = "" + cell.getStringCellValue();
                            break;

                            case Cell.CELL_TYPE_BLANK:
                                value = "" + cell.getBooleanCellValue();
                            break;

                            case Cell.CELL_TYPE_ERROR:
                                value = "" + cell.getErrorCellValue();
                            break;
                            default:
                        }
                        excelRow[c] = value;
                    }
                }
                excelList.add(excelRow);
            }
        }
    }
    return excelList;
}
 

public boolean rowHasData(int rowIndex) {
    Row row = sheet.getRow(rowIndex);
    return row != null && row.getPhysicalNumberOfCells() > 0;
}