Java XSSFRow.getCell方法代码示例

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
/**
 * @param srcSheet the sheet to copy.
 * @param destSheet the sheet to create.
 * @param srcRow the row to copy.
 * @param destRow the row to create.
 * @param styleMap -
 */
public static void copyRow(XSSFSheet srcSheet, XSSFSheet destSheet, XSSFRow srcRow, XSSFRow destRow, Map<Integer, XSSFCellStyle> styleMap) {
    // manage a list of merged zone in order to not insert two times a merged zone  
    Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>();
    destRow.setHeight(srcRow.getHeight());
    // pour chaque row  
    for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) {
        if(j<0){
        }else{
            XSSFCell oldCell = srcRow.getCell(j);   // ancienne cell  
        XSSFCell newCell = destRow.getCell(j);  // new cell   
        if (oldCell != null) {
            if (newCell == null) {
                newCell = destRow.createCell(j);
            }
            // copy chaque cell  
            copyCell(oldCell, newCell, styleMap);
                  // copy les informations de fusion entre les cellules  
            //System.out.println("row num: " + srcRow.getRowNum() + " , col: " + (short)oldCell.getColumnIndex());  
            CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex());

            if (mergedRegion != null) {
                //System.out.println("Selected merged region: " + mergedRegion.toString());  
                CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn());
                //System.out.println("New merged region: " + newMergedRegion.toString());  
                CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion);
                if (isNewMergedRegion(wrapper, mergedRegions)) {
                    mergedRegions.add(wrapper);
                    destSheet.addMergedRegion(wrapper.range);
                }
            }
        }
        }
        
    }

}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
/**
 * Read the Excel 2010
 * 
 * @param path
 *            the path of the excel file
 * @return
 * @throws IOException
 */
public static String readXlsx(String path) throws IOException {
	InputStream is = new FileInputStream(path);
	XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
	StringBuffer sb = new StringBuffer("");
	// Read the Sheet
	for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
		XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
		if (xssfSheet == null) {
			continue;
		}
		// Read the Row
		for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
			XSSFRow xssfRow = xssfSheet.getRow(rowNum);
			if (xssfRow != null) {
				XSSFCell no = xssfRow.getCell(0);
				XSSFCell name = xssfRow.getCell(1);
				sb.append(no + ":" + name);
				sb.append(";");
			}
		}
	}
	return sb.toString().substring(0, sb.toString().length() - 1);
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
private void readHeader(XSSFRow firstRow, int startCell,
		List<Cell> headerList, List<Cell> keyList) {
	for(int i = startCell; i < firstRow.getLastCellNum();  i++) {
		if(firstRow.getCell(i)!=null){
			String header = trim(firstRow,i ); 
			if(header!= null && !header.isEmpty()){
				if(isKey(header)){
					keyList.add(new Cell(i, header)); 
				}
				else{
					headerList.add(new Cell(i,header)); 
					}
				}
			}
		}
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public List<String[]> readAllLines(int sheetIndex){
	XSSFSheet sheet = getWorkBook().getSheetAt(sheetIndex);
	
	List<String[]> rows = CollectionFactory.newArrayList();
	for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
		XSSFRow row = sheet.getRow(i);
		if (row != null) {
			String[] rowValues = new String[row.getPhysicalNumberOfCells()];
			for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
				rowValues[j] = (row.getCell(j) != null)? getReader().read(row.getCell(j)) : "";
			}
			rows.add(rowValues);
		}
	}
	return rows;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public static Object getValue(XSSFSheet sheet, int row, int col) {
    try {

        XSSFRow xrow = sheet.getRow(row);
        XSSFCell cell = xrow.getCell(col);

        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC:
                return cell.getNumericCellValue();
            case Cell.CELL_TYPE_STRING:
                return (new DateComponentFormatter(
                        ComponentManager.getInstance().getComponentFormatDefaults().getSelectedDateFormat()))
                        .stringToValue(cell.getStringCellValue());
        }

    } catch (ParseException e) {
        e.printStackTrace();
    }

    return null;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
private String fromXSSFRowtoCSV(XSSFRow row){
    StringBuffer csvRow = new StringBuffer();
    int l = row.getLastCellNum();
    for (int i=0;i<l;i++){
        XSSFCell cell = row.getCell((short)i);
        String cellValue = "";
        if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
            cellValue = "";
        } else if (cell.getCellType()== HSSFCell.CELL_TYPE_STRING){
            cellValue = "\"" + cell.getStringCellValue() + "\"";
        } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
            double value = cell.getNumericCellValue();
            cellValue = getNumberFormat().format(value);
            cellValue = "\"" + cellValue + "\"";
        }

        csvRow.append(cellValue);

        if (i<l){
            csvRow.append(getCsvDelimiter().toCharArray()[0]);
        }
    }
    return csvRow.toString();
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
private Row readRow(XSSFRow poiRow, int rowNo) {
	row = new Row();

	row.setHeight((int) poiRow.getHeightInPoints());
	int firstCellNum = poiRow.getFirstCellNum();
	if (firstCellNum >= 0) {
		for (int i = 0; i < firstCellNum; i++) {
			Cell defaultCell = createDefaultCell(rowNo, i);
			row.addCell(defaultCell);
		}

		int lastCellNum = poiRow.getLastCellNum();
		for (int i = firstCellNum; i < lastCellNum; i++) {
			poiCell = poiRow.getCell(i);
			if (poiCell == null) {
				cell = createDefaultCell(rowNo, i);
			} else {
				cell = readCell(poiCell);
			}
			row.addCell(cell);
		}
	}

	return row;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public static boolean compareTwoRows(XSSFRow row1, XSSFRow row2) {
    if ((row1 == null) && (row2 == null)) {
        return true;
    } else if ((row1 == null) || (row2 == null)) {
        return false;
    }

    int firstCell1 = row1.getFirstCellNum();
    int lastCell1 = row1.getLastCellNum();
    boolean equalRows = true;

    // Compare all cells in a row
    for (int i = firstCell1; i <= lastCell1; i++) {
        XSSFCell cell1 = row1.getCell(i);
        XSSFCell cell2 = row2.getCell(i);
        if (!compareTwoCells(cell1, cell2)) {
            equalRows = false;
            break;
        }
    }
    return equalRows;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
private void fillExtraFields(String tablename, Object id, XSSFRow row, LinkedHashSet<String> de, int startCol) throws SQLException {
	// ExtraFields
	if (id != null && de != null) {
		String sql = "Select * from " + MyDBI.delimitL("ExtraFields") + " WHERE " + MyDBI.delimitL("tablename") + "='" + tablename  + "' AND " + MyDBI.delimitL("id") + "=" + id;
		ResultSet rs2 = DBKernel.getResultSet(sql, false);
		if (rs2 != null && rs2.first()) {
			do {
				String s = rs2.getString("attribute");
				int j=0;
				for (String e : de) {
					if (s.equalsIgnoreCase(e)) {
						XSSFCell cell = row.getCell(startCol+j);
						if (cell == null) cell = row.createCell(startCol+j);
						cell.setCellValue(rs2.getString("value"));
						break;
					}
					j++;
				}
			} while (rs2.next());
		}	
	}		
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
@Override
public EmpVO mappingColumn(XSSFRow row) {
	XSSFCell cell0 = row.getCell(0);
   	XSSFCell cell1 = row.getCell(1);
   	XSSFCell cell2 = row.getCell(2);

	EmpVO vo = new EmpVO();

	vo.setEmpNo(new BigDecimal(cell0.getNumericCellValue()));
	vo.setEmpName(EgovExcelUtil.getValue(cell1));
	vo.setJob(EgovExcelUtil.getValue(cell2));
	
	log.debug("########### vo is " + vo.getEmpNo());
	log.debug("########### vo is " + vo.getEmpName());
	log.debug("########### vo is " + vo.getJob());
	
	return vo;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
@Override
public ZipVO mappingColumn(XSSFRow row) {
	XSSFCell cell0 = row.getCell(0);
   	XSSFCell cell1 = row.getCell(1);
   	XSSFCell cell2 = row.getCell(2);
   	XSSFCell cell3 = row.getCell(3);
   	XSSFCell cell4 = row.getCell(4);
   	XSSFCell cell5 = row.getCell(5);
   	XSSFCell cell6 = row.getCell(6);
   	XSSFCell cell7 = row.getCell(7);

	ZipVO vo = new ZipVO();

	vo.setZipNo(new BigDecimal(cell0.getNumericCellValue()));
	vo.setSerNo(new BigDecimal(cell1.getNumericCellValue()));
	vo.setSidoNm(EgovExcelUtil.getValue(cell2));
	vo.setCggNm(EgovExcelUtil.getValue(cell3));
	vo.setUmdNm(EgovExcelUtil.getValue(cell4));
	vo.setBdNm(EgovExcelUtil.getValue(cell5));
	vo.setJibun(EgovExcelUtil.getValue(cell6));
	vo.setRegId(EgovExcelUtil.getValue(cell7));
	
	return vo;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
/**
 * 셀 Data type 테스트
 *   셀의 값이 Null 인경우 오류발생
 */
@Test
public void testCellDataFormat() throws Exception {
	StringBuffer sb = new StringBuffer();
	sb.append(fileLocation).append("/").append("testDataFormat.xlsx");

    XSSFWorkbook wb = excelService.loadXSSFWorkbook(sb.toString());
    XSSFSheet sheet = wb.getSheetAt(0);
    
    XSSFRow row = sheet.getRow(7);
	XSSFCell cell = row.getCell(0);

	assertEquals("2009/04/01", EgovExcelUtil.getValue(cell));

	row = sheet.getRow(8);
	cell = row.getCell(0);

	assertEquals("2009/04/02", EgovExcelUtil.getValue(cell));
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public static XSSFCell createCellIfNotPresent(XSSFRow row, int colIndex) {
	String message = "XSSFRow must not be null!";
	Objects.requireNonNull(row, () -> message);
	XSSFCell cell = row.getCell(colIndex);
	if (Objects.isNull(cell)) {
		cell = row.createCell(colIndex);
	}
	return cell;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public static XSSFCell createCellIfNotPresent(XSSFRow row,int colIndex){
	String message="XSSFRow must not be null!";
	Objects.requireNonNull(row, () -> message);
	XSSFCell cell=row.getCell(colIndex);
	if(Objects.isNull(cell)){
		cell=row.createCell(colIndex);
	}
	return cell;
}
 

import org.apache.poi.xssf.usermodel.XSSFRow; //导入方法依赖的package包/类
public static void applyStyleToRange(XSSFSheet sheet, XSSFCellStyle style, int rowStart, int colStart, int rowEnd, int colEnd) {
    for (int r = rowStart; r <= rowEnd; r++) {
        for (int c = colStart; c <= colEnd; c++) {
            XSSFRow row = sheet.getRow(r);

            if (row != null) {
                XSSFCell cell = row.getCell(c);

                if (cell != null) {
                    cell.setCellStyle(style);
                }
            }
        }
    }
}