Java HSSFCell类代码示例

import org.apache.poi.hssf.usermodel.HSSFCell; //导入依赖的package包/类
private void makeHeader ( final List<Field> columns, final HSSFSheet sheet )
{
    final Font font = sheet.getWorkbook ().createFont ();
    font.setFontName ( "Arial" );
    font.setBoldweight ( Font.BOLDWEIGHT_BOLD );
    font.setColor ( HSSFColor.WHITE.index );

    final CellStyle style = sheet.getWorkbook ().createCellStyle ();
    style.setFont ( font );
    style.setFillForegroundColor ( HSSFColor.BLACK.index );
    style.setFillPattern ( PatternFormatting.SOLID_FOREGROUND );

    final HSSFRow row = sheet.createRow ( 0 );

    for ( int i = 0; i < columns.size (); i++ )
    {
        final Field field = columns.get ( i );

        final HSSFCell cell = row.createCell ( i );
        cell.setCellValue ( field.getHeader () );
        cell.setCellStyle ( style );
    }
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //导入依赖的package包/类
/**
 * Read the Excel 2003-2007
 * 
 * @param path
 *            the path of the Excel
 * @return
 * @throws IOException
 */
public static String readXls(String path) throws IOException {
	InputStream is = new FileInputStream(path);
	HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
	StringBuffer sb = new StringBuffer("");
	// Read the Sheet
	for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
		HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
		if (hssfSheet == null) {
			continue;
		}
		// Read the Row
		for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = hssfSheet.getRow(rowNum);
			if (hssfRow != null) {
				HSSFCell no = hssfRow.getCell(0);
				HSSFCell name = hssfRow.getCell(1);
				sb.append(no + ":" + name);
				sb.append(";");
			}
		}
	}
	return sb.toString().substring(0, sb.toString().length() - 1);
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //导入依赖的package包/类
private String getCellValue(HSSFCell cell){
	if(cell == null) return "";
	
	switch (cell.getCellType()) {
	case HSSFCell.CELL_TYPE_STRING: return cell.getStringCellValue();
	case HSSFCell.CELL_TYPE_BOOLEAN : return Boolean.toString(cell.getBooleanCellValue());
	case HSSFCell.CELL_TYPE_NUMERIC : 
		if(HSSFDateUtil.isCellDateFormatted(cell))
			return DateUtils.formatDateTime("yyyyMMdd", HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
		else
			return new BigDecimal(cell.getNumericCellValue()).toPlainString();
	case HSSFCell.CELL_TYPE_FORMULA : return "";
	case HSSFCell.CELL_TYPE_BLANK : return "";
	default:return "";
	}
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //导入依赖的package包/类
private void insertItemToSheet(String table, HSSFSheet sheet, ArrayList<String> columns) {
    HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
    Cursor cursor = database.rawQuery("select * from " + table, null);
    cursor.moveToFirst();
    int n = 1;
    while (!cursor.isAfterLast()) {
        HSSFRow rowA = sheet.createRow(n);
        for (int j = 0; j < columns.size(); j++) {
            HSSFCell cellA = rowA.createCell(j);
            if (cursor.getType(j) == Cursor.FIELD_TYPE_BLOB) {
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) j, n, (short) (j + 1), n + 1);
                anchor.setAnchorType(3);
                patriarch.createPicture(anchor, workbook.addPicture(cursor.getBlob(j), HSSFWorkbook.PICTURE_TYPE_JPEG));
            } else {
                cellA.setCellValue(new HSSFRichTextString(cursor.getString(j)));
            }
        }
        n++;
        cursor.moveToNext();
    }
    cursor.close();
}
 

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

			HSSFWorkbook wbObj = new HSSFWorkbook();
			HSSFSheet sheet = wbObj.createSheet(sheetName);

			for (int row = 0; row < tableData.size(); row++) {
				HSSFRow rowObj = sheet.createRow(row);
				rowData = tableData.get(row);
				for (int col = 0; col < rowData.size(); col++) {
					HSSFCell cellObj = rowObj.createCell(col);
					cellObj.setCellValue(rowData.get(col));
				}
			}

			FileOutputStream fileOut = new FileOutputStream(excelFileName);
			wbObj.write(fileOut);
			wbObj.close();
			fileOut.flush();
			fileOut.close();
		}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //导入依赖的package包/类
/**
 */
protected void createColumnHeaders() {
	final HSSFRow headersRow = this.sheet.createRow(0);
	final HSSFFont font = this.workbook.createFont();
	font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	final HSSFCellStyle style = this.workbook.createCellStyle();
	style.setFont(font);
	style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
	int counter = 1;
	for (int i = 0; i < this.model.getColumnCount(); i++) {
		final HSSFCell cell = headersRow.createCell(counter++);
		// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		cell.setCellValue(this.model.getColumnName(i));
		cell.setCellStyle(style);
	}
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //导入依赖的package包/类
private void createHeader(HSSFSheet sheet, List<ExpedientConsultaDissenyDto> expedientsConsultaDissenyDto) {
	int rowNum = 0;
	int colNum = 0;

	// Capçalera
	HSSFRow xlsRow = sheet.createRow(rowNum++);

	HSSFCell cell;
	
	cell = xlsRow.createCell(colNum++);
	cell.setCellValue(new HSSFRichTextString(StringUtils.capitalize("Expedient")));
	cell.setCellStyle(headerStyle);
	
	Iterator<Entry<String, DadaIndexadaDto>> it = expedientsConsultaDissenyDto.get(0).getDadesExpedient().entrySet().iterator();
	while (it.hasNext()) {
		Map.Entry<String, DadaIndexadaDto> e = (Map.Entry<String, DadaIndexadaDto>)it.next();
		sheet.autoSizeColumn(colNum);
		cell = xlsRow.createCell(colNum++);
		cell.setCellValue(new HSSFRichTextString(StringUtils.capitalize(e.getValue().getEtiqueta())));
		cell.setCellStyle(headerStyle);
	}
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //导入依赖的package包/类
public static Integer findMatchColumn(final HSSFRow row, final String str) {
    for (int colNum = row.getFirstCellNum(); colNum <= row.getLastCellNum(); colNum++) {
        final HSSFCell cell = row.getCell(colNum);

        if (cell == null) {
            continue;
        }

        if (cell.getCellType() != Cell.CELL_TYPE_STRING) {
            continue;
        }

        final HSSFRichTextString cellValue = cell.getRichStringCellValue();

        if (cellValue.getString().matches(str)) {
            return Integer.valueOf(colNum);
        }
    }

    return null;
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //导入依赖的package包/类
/**
 * See the comment for the given cell
 * 
 * @param cell
 *            the cell
 * @param message
 *            the comment message
 */
public static void setComment(HSSFCell cell, String message) {
	Drawing drawing = cell.getSheet().createDrawingPatriarch();
	CreationHelper factory = cell.getSheet().getWorkbook().getCreationHelper();

	// When the comment box is visible, have it show in a 1x3 space
	ClientAnchor anchor = factory.createClientAnchor();
	anchor.setCol1(cell.getColumnIndex());
	anchor.setCol2(cell.getColumnIndex() + 1);
	anchor.setRow1(cell.getRowIndex());
	anchor.setRow2(cell.getRowIndex() + 1);
	anchor.setDx1(100);
	anchor.setDx2(1000);
	anchor.setDy1(100);
	anchor.setDy2(1000);

	// Create the comment and set the text+author
	Comment comment = drawing.createCellComment(anchor);
	RichTextString str = factory.createRichTextString(message);
	comment.setString(str);
	comment.setAuthor("TURNUS");
	// Assign the comment to the cell
	cell.setCellComment(comment);
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //导入依赖的package包/类
public static int getIntCellValue(HSSFSheet sheet, int r, int c) {
	HSSFRow row = sheet.getRow(r);
	if (row == null) {
		return 0;
	}
	HSSFCell cell = row.getCell(c);

	try {
		if (cell.getCellType() != HSSFCell.CELL_TYPE_NUMERIC) {
			return 0;
		}
	} catch (RuntimeException e) {
		System.err.println("Exception at sheet name:"
				+ sheet.getSheetName() + ", row:" + (r + 1) + ", col:"
				+ (c + 1));
		throw e;
	}

	return (int) cell.getNumericCellValue();
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //导入依赖的package包/类
/**
 * 获取EXCEL文件单元列值
 * 
 * @param row
 * @param point
 * @return
 */
private static String getCellValue(HSSFRow row, int point) {
	String reString = "";
	try {
		HSSFCell cell = row.getCell((short) point);
		if (cell.getCellType() == 1)
			reString = cell.getStringCellValue();
		else if (cell.getCellType() == 0) {
			reString = convert(cell.getNumericCellValue());
			BigDecimal bd = new BigDecimal(reString);
			reString = bd.toPlainString();
		} else {
			reString = "";
		}
		System.out.println(cell.getCellType() + ":" + cell.getCellFormula());
	} catch (Exception localException) {
	}
	return checkNull(reString);
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //导入依赖的package包/类
private boolean parseBooleanCell(HSSFCell cell) {
if (cell != null) {
    String value;
    try {
	cell.setCellType(Cell.CELL_TYPE_STRING);
	if (cell.getStringCellValue() != null) {
	    if (cell.getStringCellValue().trim().length() != 0) {
		emptyRow = false;
	    }
	} else {
	    return false;
	}
	value = cell.getStringCellValue().trim();
    } catch (Exception e) {
	cell.setCellType(Cell.CELL_TYPE_NUMERIC);
	double d = cell.getNumericCellValue();
	emptyRow = false;
	value = new Long(new Double(d).longValue()).toString();
    }
    if (StringUtils.equals(value, "1") || StringUtils.equalsIgnoreCase(value, "true")) {
	return true;
    }
}
return false;
   }
 

import org.apache.poi.hssf.usermodel.HSSFCell; //导入依赖的package包/类
private String parseStringCell(HSSFCell cell) {
if (cell != null) {
    try {
	cell.setCellType(Cell.CELL_TYPE_STRING);
	if (cell.getStringCellValue() != null) {
	    if (cell.getStringCellValue().trim().length() != 0) {
		emptyRow = false;
	    }
	} else {
	    return null;
	}
	// log.debug("string cell value: '"+cell.getStringCellValue().trim()+"'");
	return cell.getStringCellValue().trim();
    } catch (Exception e) {
	cell.setCellType(Cell.CELL_TYPE_NUMERIC);
	double d = cell.getNumericCellValue();
	emptyRow = false;
	// log.debug("numeric cell value: '"+d+"'");
	return (new Long(new Double(d).longValue()).toString());
    }
}
return null;
   }
 

import org.apache.poi.hssf.usermodel.HSSFCell; //导入依赖的package包/类
public static void reader(String filePath) {
	try {
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(filePath));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow row = sheet.getRow(3);
		HSSFCell cell = row.getCell((short) 0);
		int type = cell.getCellType();
		String msg = getCellStringValue(cell);
		System.out.println(type + ":" + msg);
	} catch (IOException e) {
		e.printStackTrace();
	}
}
 

import org.apache.poi.hssf.usermodel.HSSFCell; //导入依赖的package包/类
public void fillCellValue(Sheet sheet, Row row, Cell cell, Object value) {
	if (value != null) {
		if (value instanceof Date) {
			String result = sdf.format(value);
			if (result.endsWith("00:00:00")) {
				result = result.substring(0, 11);
			}
			cell.setCellValue(result);
		} else if (value instanceof Double) {
			cell.setCellValue((Double) value);
		} else if (value instanceof Integer) {
			cell.setCellValue((Integer) value);
		} else if (value instanceof Byte) {
			cell.setCellValue((Byte) value);
		} else if (value instanceof Short) {
			cell.setCellValue((Short) value);
		} else if (value instanceof Boolean) {
			cell.setCellValue((Boolean) value);
		} else if (value instanceof Long) {
			cell.setCellValue((Long) value);
		} else if (value instanceof Float) {
			cell.setCellValue((Float) value);
		} else if (value instanceof BigDecimal) {
			double doubleVal = ((BigDecimal) value).doubleValue();  
			cell.setCellValue(doubleVal);
		} else {
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(value.toString());
		}
	} else {
		cell.setCellValue("");
	}
}