Java XSSFCell.setCellType方法代码示例

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包/类
public void writeSheet(String key, Vector<String[]> sheetVector, XSSFWorkbook workbook){
	XSSFSheet worksheet = workbook.createSheet(key);
	int count=0;//keeps track of rows; one below the row int because of header row
	final Pattern NUMERIC = Pattern.compile("^\\d+.?\\d*$");
	//for each row, create the row in excel
	for (int row=0; row<sheetVector.size();row++){
		XSSFRow row1 = worksheet.createRow( count);
		count++;
		//for each col, write it to that row.
		for (int col=0; col<sheetVector.get(0).length;col++){
			XSSFCell cell = row1.createCell(col);
			String val = sheetVector.get(row)[col];
			if(val != null && !val.isEmpty() && NUMERIC.matcher(val).find()) {
				cell.setCellType(Cell.CELL_TYPE_NUMERIC);
				cell.setCellValue(Double.parseDouble(val));
				continue;
			}
			cell.setCellValue(sheetVector.get(row)[col]);
		}
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入方法依赖的package包/类
public void writeSheet(String key, Vector<String[]> sheetVector, XSSFWorkbook workbook) {
	XSSFSheet worksheet = workbook.createSheet(key);
	int count=0;//keeps track of rows; one below the row int because of header row
	final Pattern NUMERIC = Pattern.compile("^\\d+.?\\d*$");
	//for each row, create the row in excel
	for (int row=0; row<sheetVector.size();row++){
		XSSFRow row1 = worksheet.createRow( count);
		count++;
		//for each col, write it to that row.7
		for (int col=0; col<sheetVector.get(row).length;col++) {
			XSSFCell cell = row1.createCell(col);
			if(sheetVector.get(row)[col] != null) {
				String val = sheetVector.get(row)[col];
				//Check if entire value is numeric - if so, set cell type and parseDouble, else write normally
				if(val != null && !val.isEmpty() && NUMERIC.matcher(val).find()) {
					cell.setCellType(Cell.CELL_TYPE_NUMERIC);
					cell.setCellValue(Double.parseDouble(val));
				} else {
					cell.setCellValue(sheetVector.get(row)[col].replace("\"", ""));
				}
			}
		}
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入方法依赖的package包/类
public void writeSheet(String key, Vector<String[]> sheetVector, XSSFWorkbook workbook) {
	XSSFSheet worksheet = workbook.createSheet(key);
	int count=0;//keeps track of rows; one below the row int because of header row
	final Pattern NUMERIC = Pattern.compile("^\\d+.?\\d*$");
	//for each row, create the row in excel
	for (int row=0; row<sheetVector.size();row++){
		XSSFRow row1 = worksheet.createRow( count);
		count++;
		//for each col, write it to that row.7
		for (int col=0; col<sheetVector.get(row).length;col++){
			XSSFCell cell = row1.createCell(col);
			if(sheetVector.get(row)[col] != null) {
				String val = sheetVector.get(row)[col];
				//Check if entire value is numeric - if so, set cell type and parseDouble, else write normally
				if(val != null && !val.isEmpty() && NUMERIC.matcher(val).find()) {
					cell.setCellType(Cell.CELL_TYPE_NUMERIC);
					cell.setCellValue(Double.parseDouble(val));
				} else {
					cell.setCellValue(sheetVector.get(row)[col].replace("\"", ""));
				}
			}
		}
	}
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入方法依赖的package包/类
@Override
protected void printHeader(List<String> header, ByteArrayOutputStream out) {
	wb = new XSSFWorkbook();
       sheet = wb.createSheet("NextReports");

       XSSFRow headerRow = sheet.createRow(0);
       int col = 0;        
	if (header != null) {
		for (String s : header) {
			XSSFCell cell = headerRow.createCell(col);
			cell.setCellType(XSSFCell.CELL_TYPE_STRING);
			if (s == null) {
				s = "";
			}
			cell.setCellValue(wb.getCreationHelper().createRichTextString(s));
			col++;
		}
	}		
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入方法依赖的package包/类
/**
 * @param oldCell
 * @param newCell
 * @param styleMap
 */
public static void copyCell(XSSFCell oldCell, XSSFCell newCell, Map<Integer, XSSFCellStyle> styleMap) {
    if (styleMap != null) {
        if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
            newCell.setCellStyle(oldCell.getCellStyle());
        } else {
            int stHashCode = oldCell.getCellStyle().hashCode();
            XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
            if (newCellStyle == null) {
                newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
                newCellStyle.cloneStyleFrom(oldCell.getCellStyle());
                styleMap.put(stHashCode, newCellStyle);
            }
            newCell.setCellStyle(newCellStyle);
        }
    }
    switch (oldCell.getCellType()) {
        case XSSFCell.CELL_TYPE_STRING:
            newCell.setCellValue(oldCell.getStringCellValue());
            break;
        case XSSFCell.CELL_TYPE_NUMERIC:
            newCell.setCellValue(oldCell.getNumericCellValue());
            break;
        case XSSFCell.CELL_TYPE_BLANK:
            newCell.setCellType(XSSFCell.CELL_TYPE_BLANK);
            break;
        case XSSFCell.CELL_TYPE_BOOLEAN:
            newCell.setCellValue(oldCell.getBooleanCellValue());
            break;
        case XSSFCell.CELL_TYPE_ERROR:
            newCell.setCellErrorValue(oldCell.getErrorCellValue());
            break;
        case XSSFCell.CELL_TYPE_FORMULA:
            newCell.setCellFormula(oldCell.getCellFormula());
            break;
        default:
            break;
    }

}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入方法依赖的package包/类
private void shiftRange(int numRowsDown, int rowStart, int rowEnd, int colStart, int colEnd) throws Exception {
        // loop through from bottom row up to either SHIFT the whole row, or copy necessary columns, insert  new row below, insert the data from the cell and then delete all style and format from the original cell
        for(int j=rowEnd;j>rowStart;j--) {
            XSSFRow row = worksheet.getRow(j);
            XSSFRow newRow = worksheet.getRow(j+numRowsDown);
            if(row == null && newRow == null) 
                continue;
            else if (row == null) {
                worksheet.removeRow(newRow);
                continue;
            }
            if(newRow == null) {
                newRow = worksheet.createRow(j+numRowsDown);
            }
//            newRow = worksheet.getRow(j+numRowsDown);
            //if(row ==null) continue;
            for(int k=colStart;k<=colEnd;k++) {
                XSSFCell cell = row.getCell(k);
                if(cell != null) {
                    XSSFCell newCell = newRow.getCell(k)==null ? newRow.createCell(k) : newRow.getCell(k);
                    if(cell==null) continue;
                    if(cell.getCellType() == XSSFCell.CELL_TYPE_FORMULA) {
                        String calc = cell.getCellFormula();
                        newCell.setCellStyle(cell.getCellStyle());
                        cell.setCellType(XSSFCell.CELL_TYPE_BLANK);
                        this.setCellFormula(newCell, calc);
                    } else if(cell.getCellType()==XSSFCell.CELL_TYPE_BLANK) {
                        newCell.setCellType(XSSFCell.CELL_TYPE_BLANK);
                    } else {
                        newCell.copyCellFrom(cell, new CellCopyPolicy());
                        newCell.setCellStyle(cell.getCellStyle());
                    }
                }
            }
        }
    }
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入方法依赖的package包/类
/**
 * Copy a cell to another cell
 * 
 * @param oldCell cell to be copied
 * @param newCell cell to be created
 * @param styleMap style map
 */
public static void copyCell(XSSFCell oldCell, XSSFCell newCell, Map<Integer, CellStyle> styleMap) {
	if (styleMap != null) {			
		if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) {
			newCell.setCellStyle(oldCell.getCellStyle());
		} else {				
			int stHashCode = oldCell.getCellStyle().hashCode();
			CellStyle newCellStyle = styleMap.get(stHashCode);				
			if (newCellStyle == null) {					
				newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();
				newCellStyle.cloneStyleFrom(oldCell.getCellStyle());					
				styleMap.put(stHashCode, newCellStyle);
			}
			newCell.setCellStyle(newCellStyle);
		}			
	}
	switch (oldCell.getCellType()) {
	case XSSFCell.CELL_TYPE_STRING:
		newCell.setCellValue(oldCell.getStringCellValue());
		break;
	case XSSFCell.CELL_TYPE_NUMERIC:
		newCell.setCellValue(oldCell.getNumericCellValue());
		break;
	case XSSFCell.CELL_TYPE_BLANK:
		newCell.setCellType(XSSFCell.CELL_TYPE_BLANK);
		break;
	case XSSFCell.CELL_TYPE_BOOLEAN:
		newCell.setCellValue(oldCell.getBooleanCellValue());
		break;
	case XSSFCell.CELL_TYPE_ERROR:
		newCell.setCellErrorValue(oldCell.getErrorCellValue());
		break;
	case XSSFCell.CELL_TYPE_FORMULA:
		newCell.setCellFormula(oldCell.getCellFormula());
		break;
	default:
		break;
	}

}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入方法依赖的package包/类
private XSSFRow copyRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum) {
    XSSFRow sourceRow = worksheet.getRow(sourceRowNum);
       worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1, true, false);
       XSSFRow newRow = worksheet.createRow(destinationRowNum);

    // Loop through source columns to add to new row
    for (int i = 0; i < sourceRow.getLastCellNum(); i++) {
        // Grab a copy of the old/new cell
        XSSFCell oldCell = sourceRow.getCell(i);
        XSSFCell newCell = newRow.createCell(i);

        // If the old cell is null jump to next cell
        if (oldCell == null) {
            newCell = null;
            continue;
        }

        newCell.setCellStyle(oldCell.getCellStyle());

        // Set the cell data type
        newCell.setCellType(oldCell.getCellType());
    }

    // If there are are any merged regions in the source row, copy to new row
    for (int i = 0; i < worksheet.getNumMergedRegions(); i++) {
        CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i);
        if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) {
            CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(),
                    (newRow.getRowNum() +
                            (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow()
                                    )),
                    cellRangeAddress.getFirstColumn(),
                    cellRangeAddress.getLastColumn());
            worksheet.addMergedRegion(newCellRangeAddress);
        }
    }
    
    newRow.setHeight(sourceRow.getHeight());
    
    return newRow;
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入方法依赖的package包/类
@Override
protected void createDetailCell(int column, Object element) {
	XSSFCell cell = detailRow.createCell(column);
	cell.setCellType(XSSFCell.CELL_TYPE_STRING);
	cell.setCellValue(wb.getCreationHelper().createRichTextString(element.toString()));
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入方法依赖的package包/类
/**
 * [Flow #-2] 액셀 파일 수정 : 엑샐 파일 내 셀의 내용을 변경하고 저장할 수 있음
 */
@Test
public void testModifyCellContents() throws Exception {
    
    try {
    	String content = "Use \n with word wrap on to create a new line";
    	short rownum = 2;
    	int cellnum = 2;
    	
    	log.debug("testModifyCellContents start....");

    	StringBuffer sb = new StringBuffer();
    	sb.append(fileLocation).append("/").append("testModifyCellContents.xlsx");

    	if (!EgovFileUtil.isExistsFile(sb.toString())) {
    		XSSFWorkbook wbT = new XSSFWorkbook();
     	wbT.createSheet();
	
         // 엑셀 파일 생성
         excelService.createXSSFWorkbook(wbT, sb.toString());
    	}
    	
        // 엑셀 파일 로드
        XSSFWorkbook wb = excelService.loadXSSFWorkbook(sb.toString());
        log.debug("testModifyCellContents after loadWorkbook....");
        
        XSSFSheet sheet = wb.getSheetAt(0);
        Font f2 = wb.createFont();
        CellStyle cs = wb.createCellStyle();
        cs = wb.createCellStyle();
        
        cs.setFont( f2 );
        //Word Wrap MUST be turned on
        cs.setWrapText( true );
        
        XSSFRow row = sheet.createRow( rownum );
        row.setHeight( (short) 0x349 );
        XSSFCell cellx = row.createCell( cellnum );
        cellx.setCellType( SXSSFCell.CELL_TYPE_STRING );
        cellx.setCellValue( new XSSFRichTextString(content) );
        cellx.setCellStyle( cs );
        
        sheet.setColumnWidth( 20, (int) ( ( 50 * 8 ) / ( (double) 1 / 20 ) ) );
        
        //excelService.writeWorkbook(wb);
        
        FileOutputStream outx = new FileOutputStream(sb.toString());
        wb.write(outx);
        outx.close();
        
        // 엑셀 파일 로드
        Workbook wb1 = excelService.loadXSSFWorkbook(sb.toString());
        
        Sheet sheet1 = wb1.getSheetAt(0);
        Row row1 = sheet1.getRow(rownum);
        Cell cell1 = row1.getCell( cellnum );
        
        // 수정된 셀의 내용 점검
        log.debug("cell ###" + cell1.getRichStringCellValue() + "###");
        log.debug("cont ###" + content + "###");
        
        assertNotSame("TEST", cell1.getRichStringCellValue().toString());
        assertEquals(content, cell1.getRichStringCellValue().toString());
        
    } catch (Exception e) {
    	log.error(e.toString());
    	throw new Exception(e);
    } finally {
    	log.debug("testModifyCellContents end....");
    }
}
 

import org.apache.poi.xssf.usermodel.XSSFCell; //导入方法依赖的package包/类
/**
 * Convenient method to set a String as text content in a cell.
 * @param cell the cell in which the text must be put
 * @param text the text to put in the cell
 */
protected void setText(XSSFCell cell, String text) {
	cell.setCellType(XSSFCell.CELL_TYPE_STRING);
	cell.setCellValue(text);
}