Java XSSFCell类代码示例

import org.apache.poi.xssf.usermodel.XSSFCell; //导入依赖的package包/类
public static void writeXLSXFile() throws IOException {

			// @SuppressWarnings("resource")
			XSSFWorkbook wbObj = new XSSFWorkbook();
			XSSFSheet sheet = wbObj.createSheet(sheetName);
			for (int row = 0; row < tableData.size(); row++) {
				XSSFRow rowObj = sheet.createRow(row);
				rowData = tableData.get(row);
				for (int col = 0; col < rowData.size(); col++) {
					XSSFCell cell = rowObj.createCell(col);
					cell.setCellValue(rowData.get(col));
					logger.info("Writing " + row + " " + col + "  " + rowData.get(col));
				}
			}
			FileOutputStream fileOut = new FileOutputStream(excelFileName);
			wbObj.write(fileOut);
			wbObj.close();
			fileOut.flush();
			fileOut.close();
		}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入依赖的package包/类
/**
 * Gets the cell value.
 *
 * @param cell the cell
 * @return the cell value
 */
private String getCellValue( XSSFCell cell )
{
	if (cell != null)
	{
		switch (cell.getCellType())
		{
			case XSSFCell.CELL_TYPE_BLANK:
				return null;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				return String.valueOf( cell.getBooleanCellValue() );
			case XSSFCell.CELL_TYPE_NUMERIC:
				return String.valueOf( ( int ) cell.getNumericCellValue() );
			case XSSFCell.CELL_TYPE_STRING:
				return cell.getRichStringCellValue().toString();

		}
	}
	return null;
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入依赖的package包/类
public static void insertDedicatedStyleCell(XSSFRow row,int col,XSSFCellStyle style,String value){
	String message="XSSFRow or XSSFCellStyle must not be null!";
	Objects.requireNonNull(row, () -> message);
	Objects.requireNonNull(style, () -> message);
	XSSFCell cell=createCellIfNotPresent(row,col);
	setCellProperties(createCellIfNotPresent(row, col), value, style);
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入依赖的package包/类
public Object get(XSSFCell cell) {
    if (cell != null) {
        int cellType = cell.getCellType();
        switch (cellType) {
            case Cell.CELL_TYPE_NUMERIC:
                return cell.getNumericCellValue();
            case Cell.CELL_TYPE_BOOLEAN:
                return cell.getBooleanCellValue();
            default:
                try {
                    return cell.getStringCellValue();
                } catch (IllegalStateException mismatch) {
                    return null;
                }
        }
    } else {
        return null;
    }

}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入依赖的package包/类
private static String microsoftExcelDocumentToString(InputStream inputStream) throws IOException, OpenXML4JException, XmlException {
    StringBuilder sb = new StringBuilder();

    try (InputStream excelStream = new BufferedInputStream(inputStream)) {
        if (POIFSFileSystem.hasPOIFSHeader(excelStream)) { // Before 2007 format files
            POIFSFileSystem excelFS = new POIFSFileSystem(excelStream);
            ExcelExtractor excelExtractor = new ExcelExtractor(excelFS);
            sb.append(excelExtractor.getText());
            excelExtractor.close();
        } else { // New format
            XSSFWorkbook workBook = new XSSFWorkbook(excelStream);
            int numberOfSheets = workBook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                XSSFSheet sheet = workBook.getSheetAt(0);
                Iterator<Row> rowIterator = sheet.rowIterator();
                while (rowIterator.hasNext()) {
                    XSSFRow row = (XSSFRow) rowIterator.next();
                    Iterator<Cell> cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {
                        XSSFCell cell = (XSSFCell) cellIterator.next();
                        sb.append(cell.toString());
                        sb.append(" ");
                    }
                    sb.append("\n");
                }
                sb.append("\n");
            }
        }
    }

    return sb.toString();
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入依赖的package包/类
/**
 * Gets the cell value.
 *
 * @param cell the cell
 * @return the cell value
 */
private String getCellValue( XSSFCell cell )
{
	if (cell != null)
	{
		switch (cell.getCellType())
		{
			case XSSFCell.CELL_TYPE_BLANK:
				return null;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				return String.valueOf( cell.getBooleanCellValue() );
			case XSSFCell.CELL_TYPE_NUMERIC:
				return String.valueOf( cell.getNumericCellValue() );
			case XSSFCell.CELL_TYPE_STRING:
				return cell.getRichStringCellValue().toString();
		}
	}
	return null;
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入依赖的package包/类
/**
 * Gets the cell value.
 *
 * @param cell the cell
 * @return the cell value
 */
private String getCellValue( XSSFCell cell )
{
	if (cell != null)
	{
		switch (cell.getCellType())
		{
			case XSSFCell.CELL_TYPE_BLANK:
				return null;
			case XSSFCell.CELL_TYPE_BOOLEAN:
				return String.valueOf( cell.getBooleanCellValue() );
			case XSSFCell.CELL_TYPE_NUMERIC:
			{
			    String useValue = String.valueOf( cell.getNumericCellValue() );
			    if ( useValue.endsWith( ".0" ) )
			        return useValue.split( "\\." )[0];
			    else
			        return useValue;
			}
			case XSSFCell.CELL_TYPE_STRING:
				return cell.getRichStringCellValue().toString();
		}
	}
	return null;
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入依赖的package包/类
private String getCellValue( XSSFCell cell )
{
    if (cell != null )
    {
        switch (cell.getCellType())
        {
            case XSSFCell.CELL_TYPE_BLANK:
                return null;
            case XSSFCell.CELL_TYPE_BOOLEAN:
                return String.valueOf( cell.getBooleanCellValue() );
            case XSSFCell.CELL_TYPE_NUMERIC:
                return String.valueOf( cell.getNumericCellValue() );
            case XSSFCell.CELL_TYPE_STRING:
                return cell.getRichStringCellValue().toString();
        }
    }
    return null;
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入依赖的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.XSSFCell; //导入依赖的package包/类
public void updateCellValue(String cellPosition, String value) throws Exception {
    
    String sheetNumTxt = cellPosition.indexOf("[")>-1 ? cellPosition.substring(cellPosition.indexOf("[")) : null;
        if(sheetNumTxt != null) {
            this.sheetNum = new Integer(sheetNumTxt.substring(0,sheetNumTxt.length()-1));
            cellPosition = cellPosition.substring(cellPosition.indexOf("["));
        } else {
            this.sheetNum = 0;
        }
        worksheet = workbook.getSheetAt(this.sheetNum);
    
    CellReference c = new CellReference(cellPosition);
    XSSFCell cell = worksheet.getRow(c.getRow()).getCell(c.getCol());
    if(cell == null) throw new Exception("Invalid cell reference:" + cellPosition);
    if(value == null) {
       cell.setCellType(XSSFCell.CELL_TYPE_BLANK);
    } else if(cell.getCellType()==XSSFCell.CELL_TYPE_FORMULA) {
        this.setCellFormula(cell, value);
    } else {
        cell.setCellValue(value);
    }
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入依赖的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.XSSFCell; //导入依赖的package包/类
@Override
public ValidationResult validate(XSSFCell cell) {
	if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
		String stringCellValue = cell.getStringCellValue();
		int length;
		if (stringCellValue == null) {
			length = 0;
		} else {
			length = stringCellValue.length();
		}
		if (!operator.check((double) length)) {
			return new ValidationResultImpl(cell.getSheet().getSheetName(), cell.getReference(), false, "Error!");
		}
	} else {
		// TODO how to handle this situation
	}
	return null;
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入依赖的package包/类
protected String stringValueOf(CellValue value) {
    switch (value.getCellType()) {
        case XSSFCell.CELL_TYPE_STRING:
            return value.getStringValue();
        case XSSFCell.CELL_TYPE_NUMERIC:
            return String.valueOf(value.getNumberValue());
        case XSSFCell.CELL_TYPE_BLANK:
            return "";
        case XSSFCell.CELL_TYPE_BOOLEAN:
            return String.valueOf(value.getBooleanValue());
        case XSSFCell.CELL_TYPE_ERROR:
            return "error";
        default:
            return "unknown";
    }
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入依赖的package包/类
/**
 * 获取单元格字符串值
 *
 * @param cell
 * @return
 *
 */
public static String getStrCellVal(XSSFCell cell) {
    if (cell == null) {
        // 如果参数对象为空,
        // 则直接退出!
        return null;
    }

    switch (cell.getCellType()) {
    case XSSFCell.CELL_TYPE_STRING:
        return cell.getStringCellValue();
    case XSSFCell.CELL_TYPE_BOOLEAN:
        return cell.getBooleanCellValue() ? "true" : "false";
    default:
        // 抛出异常
        throw new XlsxTmplError(MessageFormat.format(
            ERR_CELL_TYPE,
            getSheetIndex(cell) + 1,
            getSheetName(cell),
            String.valueOf(cell.getRowIndex() + 1),
            getColName(cell.getColumnIndex()),
            String.class.getName(),
            getCellTypeName(cell)
        ));
    }
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入依赖的package包/类
/**
 * 获取单元格类型名称
 *
 * @param cell
 * @return
 *
 */
public static String getCellTypeName(XSSFCell cell) {
    if (cell == null) {
        return "null";
    }

    switch (cell.getCellType()) {
    case XSSFCell.CELL_TYPE_BLANK: return "CELL_TYPE_BLANK";
    case XSSFCell.CELL_TYPE_BOOLEAN: return "CELL_TYPE_BOOLEAN";
    case XSSFCell.CELL_TYPE_ERROR: return "CELL_TYPE_ERROR";
    case XSSFCell.CELL_TYPE_FORMULA: return "CELL_TYPE_FORMULA";
    case XSSFCell.CELL_TYPE_NUMERIC: return "CELL_TYPE_NUMERIC";
    case XSSFCell.CELL_TYPE_STRING: return "CELL_TYPE_STRING";
    default: return "unknown";
    }
}