Apache.POI.HSSF获取单元格数据为String类型(参考源码)新
程序员文章站
2022-07-13 13:18:13
...
POI获取单元格数据为String类型
HSSFCell 如何获取数据类型 String
用法:
/**
* .
* 获取单元格数据内容为字符串类型的数据
* TODO
* @param cell Excel单元格
* @returnString 单元格数据内容
*/
private String getStringCellValue(HSSFCell cell) {
String cellValue = "";
if(cell.getCellType()==cell.CELL_TYPE_STRING) {cellValue = cell.getRichStringCellValue().getString(); }
if(cell.getCellType()==cell.CELL_TYPE_NUMERIC) {
cell.setCellType(cell.CELL_TYPE_STRING);
cellValue = String.valueOf(cell.getRichStringCellValue().getString());
}
return cellValue;
}用switch,case编译器会报错。(case值必须为常量)
参考源码:
Licensed to the Apache Software Foundation (ASF) under one or more
package org.apache.poi.hssf.usermodel;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.model.HSSFFormulaParser;
import org.apache.poi.hssf.model.InternalWorkbook;
import org.apache.poi.hssf.record.BlankRecord;
import org.apache.poi.hssf.record.BoolErrRecord;
import org.apache.poi.hssf.record.CellValueRecordInterface;
import org.apache.poi.hssf.record.ExtendedFormatRecord;
import org.apache.poi.hssf.record.FormulaRecord;
import org.apache.poi.hssf.record.HyperlinkRecord;
import org.apache.poi.hssf.record.LabelSSTRecord;
import org.apache.poi.hssf.record.NumberRecord;
import org.apache.poi.hssf.record.Record;
import org.apache.poi.hssf.record.RecordBase;
import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
import org.apache.poi.hssf.record.common.UnicodeString;
import org.apache.poi.ss.SpreadsheetVersion;
import org.apache.poi.ss.formula.FormulaType;
import org.apache.poi.ss.formula.eval.ErrorEval;
import org.apache.poi.ss.formula.ptg.ExpPtg;
import org.apache.poi.ss.formula.ptg.Ptg;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.FormulaError;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.util.CellAddress;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.util.Internal;
import org.apache.poi.util.LocaleUtil;
/**
* High level representation of a cell in a row of a spreadsheet.
* Cells can be numeric, formula-based or string-based (text). The cell type
* specifies this. String cells cannot contain numbers and numeric cells cannot
* contain strings (at least according to our model). Client apps should do the
* conversions themselves. Formula cells have the formula string, as well as
* the formula result, which can be numeric or string.
* <p>
* Cells should have their number (0 based) before being added to a row. Only
* cells that have values should be added.
* <p>
*/
public class HSSFCell implements Cell {
private static final String FILE_FORMAT_NAME = "BIFF8";
/**
* The maximum number of columns in BIFF8
*/
public static final int LAST_COLUMN_NUMBER = SpreadsheetVersion.EXCEL97.getLastColumnIndex(); // 2^8 - 1
private static final String LAST_COLUMN_NAME = SpreadsheetVersion.EXCEL97.getLastColumnName();
public final static short ENCODING_UNCHANGED = -1;
public final static short ENCODING_COMPRESSED_UNICODE = 0;
public final static short ENCODING_UTF_16 = 1;
private final HSSFWorkbook _book;
private final HSSFSheet _sheet;
private CellType _cellType;
private HSSFRichTextString _stringValue;
private CellValueRecordInterface _record;
private HSSFComment _comment;
/**
* Creates new Cell - Should only be called by HSSFRow. This creates a cell
* from scratch.
* <p>
* When the cell is initially created it is set to {@link CellType#BLANK}. Cell types
* can be changed/overwritten by calling setCellValue with the appropriate
* type as a parameter although conversions from one type to another may be
* prohibited.
*
* @param book - Workbook record of the workbook containing this cell
* @param sheet - Sheet record of the sheet containing this cell
* @param row - the row of this cell
* @param col - the column for this cell
*
* @see org.apache.poi.hssf.usermodel.HSSFRow#createCell(int)
*/
protected HSSFCell(HSSFWorkbook book, HSSFSheet sheet, int row, short col)
{
checkBounds(col);
_stringValue = null;
_book = book;
_sheet = sheet;
// Relying on the fact that by default the cellType is set to 0 which
// is different to {@link CellType#BLANK} hence the following method call correctly
// creates a new blank cell.
short xfindex = sheet.getSheet().getXFIndexForColAt(col);
setCellType(CellType.BLANK, false, row, col,xfindex);
}
/**
* Returns the HSSFSheet this cell belongs to
*
* @return the HSSFSheet that owns this cell
*/
public HSSFSheet getSheet() {
return _sheet;
}
/**
* Returns the HSSFRow this cell belongs to
*
* @return the HSSFRow that owns this cell
*/
public HSSFRow getRow() {
int rowIndex = getRowIndex();
return _sheet.getRow(rowIndex);
}
/**
* Creates new Cell - Should only be called by HSSFRow. This creates a cell
* from scratch.
*
* @param book - Workbook record of the workbook containing this cell
* @param sheet - Sheet record of the sheet containing this cell
* @param row - the row of this cell
* @param col - the column for this cell
* @param type - Type of cell
* @see org.apache.poi.hssf.usermodel.HSSFRow#createCell(int,int)
*/
protected HSSFCell(HSSFWorkbook book, HSSFSheet sheet, int row, short col,
CellType type)
{
checkBounds(col);
_cellType = CellType._NONE; // Force 'setCellType' to create a first Record
_stringValue = null;
_book = book;
_sheet = sheet;
short xfindex = sheet.getSheet().getXFIndexForColAt(col);
setCellType(type,false,row,col,xfindex);
}
/**
* Creates an HSSFCell from a CellValueRecordInterface. HSSFSheet uses this when
* reading in cells from an existing sheet.
*
* @param book - Workbook record of the workbook containing this cell
* @param sheet - Sheet record of the sheet containing this cell
* @param cval - the Cell Value Record we wish to represent
*/
protected HSSFCell(HSSFWorkbook book, HSSFSheet sheet, CellValueRecordInterface cval) {
_record = cval;
_cellType = determineType(cval);
_stringValue = null;
_book = book;
_sheet = sheet;
switch (_cellType)
{
case STRING :
_stringValue = new HSSFRichTextString(book.getWorkbook(), (LabelSSTRecord ) cval);
break;
case BLANK :
break;
case FORMULA :
_stringValue=new HSSFRichTextString(((FormulaRecordAggregate) cval).getStringValue());
break;
default :
break;
}
}
/**
* used internally -- given a cell value record, figure out its type
*/
private static CellType determineType(CellValueRecordInterface cval) {
if (cval instanceof FormulaRecordAggregate) {
return CellType.FORMULA;
}
// all others are plain BIFF records
Record record = ( Record ) cval;
switch (record.getSid()) {
case NumberRecord.sid : return CellType.NUMERIC;
case BlankRecord.sid : return CellType.BLANK;
case LabelSSTRecord.sid : return CellType.STRING;
case BoolErrRecord.sid :
BoolErrRecord boolErrRecord = ( BoolErrRecord ) record;
return boolErrRecord.isBoolean()
? CellType.BOOLEAN
: CellType.ERROR;
}
throw new RuntimeException("Bad cell value rec (" + cval.getClass().getName() + ")");
}
/**
* Returns the Workbook that this Cell is bound to
*/
protected InternalWorkbook getBoundWorkbook() {
return _book.getWorkbook();
}
/**
* @return the (zero based) index of the row containing this cell
*/
@Override
public int getRowIndex() {
return _record.getRow();
}
/**
* Updates the cell record's idea of what
* column it belongs in (0 based)
* @param num the new cell number
*/
protected void updateCellNum(short num)
{
_record.setColumn(num);
}
@Override
public int getColumnIndex() {
return _record.getColumn() & 0xFFFF;
}
/**
* {@inheritDoc}
*/
@Override
public CellAddress getAddress() {
return new CellAddress(this);
}
/**
* Set the cells type (numeric, formula or string).
* If the cell currently contains a value, the value will
* be converted to match the new type, if possible.
* @see CellType#NUMERIC
* @see CellType#STRING
* @see CellType#FORMULA
* @see CellType#BLANK
* @see CellType#BOOLEAN
* @see CellType#ERROR
* @deprecated POI 3.15 beta 3. Use {@link #setCellType(CellType)} instead.
*/
@Override
public void setCellType(int cellType) {
setCellType(CellType.forInt(cellType));
}
/**
* Set the cells type (numeric, formula or string).
* If the cell currently contains a value, the value will
* be converted to match the new type, if possible.
*/
@Override
public void setCellType(CellType cellType) {
notifyFormulaChanging();
if(isPartOfArrayFormulaGroup()){
notifyArrayFormulaChanging();
}
int row=_record.getRow();
short col=_record.getColumn();
short styleIndex=_record.getXFIndex();
setCellType(cellType, true, row, col, styleIndex);
}
/**
* sets the cell type. The setValue flag indicates whether to bother about
* trying to preserve the current value in the new record if one is created.
* <p>
* The @see #setCellValue method will call this method with false in setValue
* since it will overwrite the cell value later
*
*/
private void setCellType(CellType cellType, boolean setValue, int row,short col, short styleIndex)
{
switch (cellType)
{
case FORMULA :
FormulaRecordAggregate frec;
if (cellType != _cellType) {
frec = _sheet.getSheet().getRowsAggregate().createFormula(row, col);
} else {
frec = (FormulaRecordAggregate) _record;
frec.setRow(row);
frec.setColumn(col);
}
if (setValue)
{
frec.getFormulaRecord().setValue(getNumericCellValue());
}
frec.setXFIndex(styleIndex);
_record = frec;
break;
case NUMERIC :
NumberRecord nrec = null;
if (cellType != _cellType)
{
nrec = new NumberRecord();
}
else
{
nrec = ( NumberRecord ) _record;
}
nrec.setColumn(col);
if (setValue)
{
nrec.setValue(getNumericCellValue());
}
nrec.setXFIndex(styleIndex);
nrec.setRow(row);
_record = nrec;
break;
case STRING :
LabelSSTRecord lrec;
if (cellType == _cellType) {
lrec = (LabelSSTRecord) _record;
} else {
lrec = new LabelSSTRecord();
lrec.setColumn(col);
lrec.setRow(row);
lrec.setXFIndex(styleIndex);
}
if (setValue) {
String str = convertCellValueToString();
if(str == null) {
// bug 55668: don't try to store null-string when formula
// results in empty/null value
setCellType(CellType.BLANK, false, row, col, styleIndex);
return;
} else {
int sstIndex = _book.getWorkbook().addSSTString(new UnicodeString(str));
lrec.setSSTIndex(sstIndex);
UnicodeString us = _book.getWorkbook().getSSTString(sstIndex);
_stringValue = new HSSFRichTextString();
_stringValue.setUnicodeString(us);
}
}
_record = lrec;
break;
case BLANK :
BlankRecord brec = null;
if (cellType != _cellType)
{
brec = new BlankRecord();
}
else
{
brec = ( BlankRecord ) _record;
}
brec.setColumn(col);
// During construction the cellStyle may be null for a Blank cell.
brec.setXFIndex(styleIndex);
brec.setRow(row);
_record = brec;
break;
case BOOLEAN :
BoolErrRecord boolRec = null;
if (cellType != _cellType)
{
boolRec = new BoolErrRecord();
}
else
{
boolRec = ( BoolErrRecord ) _record;
}
boolRec.setColumn(col);
if (setValue)
{
boolRec.setValue(convertCellValueToBoolean());
}
boolRec.setXFIndex(styleIndex);
boolRec.setRow(row);
_record = boolRec;
break;
case ERROR :
BoolErrRecord errRec = null;
if (cellType != _cellType)
{
errRec = new BoolErrRecord();
}
else
{
errRec = ( BoolErrRecord ) _record;
}
errRec.setColumn(col);
if (setValue)
{
errRec.setValue(FormulaError.VALUE.getCode());
}
errRec.setXFIndex(styleIndex);
errRec.setRow(row);
_record = errRec;
break;
default :
throw new IllegalStateException("Invalid cell type: " + cellType);
}
if (cellType != _cellType &&
_cellType != CellType._NONE ) // Special Value to indicate an uninitialized Cell
{
_sheet.getSheet().replaceValueRecord(_record);
}
_cellType = cellType;
}
/**
* get the cells type (numeric, formula or string)
*
* Will return {@link CellType} in a future version of POI.
* For forwards compatibility, do not hard-code cell type literals in your code.
* @deprecated 3.15. Will be return a {@link CellType} enum in the future.
*/
@Override
public int getCellType()
{
return getCellTypeEnum().getCode();
}
/**
* get the cells type (numeric, formula or string)
* @since POI 3.15 beta 3
* Will be deleted when we make the CellType enum transition. See bug 59791.
*/
@Override
public CellType getCellTypeEnum()
{
return _cellType;
}
/**
* set a numeric value for the cell
*
* @param value the numeric value to set this cell to. For formulas we'll set the
* precalculated value, for numerics we'll set its value. For other types we
* will change the cell to a numeric cell and set its value.
*/
@SuppressWarnings("fallthrough")
@Override
public void setCellValue(double value) {
if(Double.isInfinite(value)) {
// Excel does not support positive/negative infinities,
// rather, it gives a #DIV/0! error in these cases.
setCellErrorValue(FormulaError.DIV0.getCode());
} else if (Double.isNaN(value)){
// Excel does not support Not-a-Number (NaN),
// instead it immediately generates a #NUM! error.
setCellErrorValue(FormulaError.NUM.getCode());
} else {
int row=_record.getRow();
short col=_record.getColumn();
short styleIndex=_record.getXFIndex();
switch (_cellType) {
default:
setCellType(CellType.NUMERIC, false, row, col, styleIndex);
// fall through
case NUMERIC:
(( NumberRecord ) _record).setValue(value);
break;
case FORMULA:
((FormulaRecordAggregate)_record).setCachedDoubleResult(value);
break;
}
}
}
/**
* set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
* a date.
*
* @param value the date value to set this cell to. For formulas we'll set the
* precalculated value, for numerics we'll set its value. For other types we
* will change the cell to a numeric cell and set its value.
*/
public void setCellValue(Date value)
{
setCellValue(HSSFDateUtil.getExcelDate(value, _book.getWorkbook().isUsing1904DateWindowing()));
}
/**
* set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
* a date.
*
* This will set the cell value based on the Calendar's timezone. As Excel
* does not support timezones this means that both 20:00+03:00 and
* 20:00-03:00 will be reported as the same value (20:00) even that there
* are 6 hours difference between the two times. This difference can be
* preserved by using <code>setCellValue(value.getTime())</code> which will
* automatically shift the times to the default timezone.
*
* @param value the date value to set this cell to. For formulas we'll set the
* precalculated value, for numerics we'll set its value. For othertypes we
* will change the cell to a numeric cell and set its value.
*/
public void setCellValue(Calendar value)
{
setCellValue( HSSFDateUtil.getExcelDate(value, _book.getWorkbook().isUsing1904DateWindowing()) );
}
/**
* set a string value for the cell.
*
* @param value value to set the cell to. For formulas we'll set the formula
* cached string result, for String cells we'll set its value. For other types we will
* change the cell to a string cell and set its value.
* If value is null then we will change the cell to a Blank cell.
*/
public void setCellValue(String value) {
HSSFRichTextString str = value == null ? null : new HSSFRichTextString(value);
setCellValue(str);
}
/**
* Set a string value for the cell.
*
* @param value value to set the cell to. For formulas we'll set the formula
* string, for String cells we'll set its value. For other types we will
* change the cell to a string cell and set its value.
* If value is <code>null</code> then we will change the cell to a Blank cell.
*/
public void setCellValue(RichTextString value)
{
int row=_record.getRow();
short col=_record.getColumn();
short styleIndex=_record.getXFIndex();
if (value == null)
{
notifyFormulaChanging();
setCellType(CellType.BLANK, false, row, col, styleIndex);
return;
}
if(value.length() > SpreadsheetVersion.EXCEL97.getMaxTextLength()){
throw new IllegalArgumentException("The maximum length of cell contents (text) is 32,767 characters");
}
if (_cellType == CellType.FORMULA) {
// Set the 'pre-evaluated result' for the formula
// note - formulas do not preserve text formatting.
FormulaRecordAggregate fr = (FormulaRecordAggregate) _record;
fr.setCachedStringResult(value.getString());
// Update our local cache to the un-formatted version
_stringValue = new HSSFRichTextString(value.getString());
// All done
return;
}
// If we get here, we're not dealing with a formula,
// so handle things as a normal rich text cell
if (_cellType != CellType.STRING) {
setCellType(CellType.STRING, false, row, col, styleIndex);
}
int index = 0;
HSSFRichTextString hvalue = (HSSFRichTextString) value;
UnicodeString str = hvalue.getUnicodeString();
index = _book.getWorkbook().addSSTString(str);
(( LabelSSTRecord ) _record).setSSTIndex(index);
_stringValue = hvalue;
_stringValue.setWorkbookReferences(_book.getWorkbook(), (( LabelSSTRecord ) _record));
_stringValue.setUnicodeString(_book.getWorkbook().getSSTString(index));
}
public void setCellFormula(String formula) {
if(isPartOfArrayFormulaGroup()){
notifyArrayFormulaChanging();
}
int row=_record.getRow();
short col=_record.getColumn();
short styleIndex=_record.getXFIndex();
if (formula==null) {
notifyFormulaChanging();
setCellType(CellType.BLANK, false, row, col, styleIndex);
return;
}
int sheetIndex = _book.getSheetIndex(_sheet);
Ptg[] ptgs = HSSFFormulaParser.parse(formula, _book, FormulaType.CELL, sheetIndex);
setCellType(CellType.FORMULA, false, row, col, styleIndex);
FormulaRecordAggregate agg = (FormulaRecordAggregate) _record;
FormulaRecord frec = agg.getFormulaRecord();
frec.setOptions((short) 2);
frec.setValue(0);
//only set to default if there is no extended format index already set
if (agg.getXFIndex() == (short)0) {
agg.setXFIndex((short) 0x0f);
}
agg.setParsedExpression(ptgs);
}
/**
* Should be called any time that a formula could potentially be deleted.
* Does nothing if this cell currently does not hold a formula
*/
private void notifyFormulaChanging() {
if (_record instanceof FormulaRecordAggregate) {
((FormulaRecordAggregate)_record).notifyFormulaChanging();
}
}
public String getCellFormula() {
if (!(_record instanceof FormulaRecordAggregate)) {
throw typeMismatch(CellType.FORMULA, _cellType, true);
}
return HSSFFormulaParser.toFormulaString(_book, ((FormulaRecordAggregate)_record).getFormulaTokens());
}
private static RuntimeException typeMismatch(CellType expectedTypeCode, CellType actualTypeCode, boolean isFormulaCell) {
String msg = "Cannot get a " + expectedTypeCode + " value from a " + actualTypeCode
+ " " + (isFormulaCell ? "formula " : "") + "cell";
return new IllegalStateException(msg);
}
private static void checkFormulaCachedValueType(CellType expectedTypeCode, FormulaRecord fr) {
CellType cachedValueType = CellType.forInt(fr.getCachedResultType());
if (cachedValueType != expectedTypeCode) {
throw typeMismatch(expectedTypeCode, cachedValueType, true);
}
}
/**
* Get the value of the cell as a number.
* For strings we throw an exception.
* For blank cells we return a 0.
* See {@link HSSFDataFormatter} for turning this
* number into a string similar to that which
* Excel would render this number as.
*/
public double getNumericCellValue() {
switch(_cellType) {
case BLANK:
return 0.0;
case NUMERIC:
return ((NumberRecord)_record).getValue();
default:
throw typeMismatch(CellType.NUMERIC, _cellType, false);
case FORMULA:
break;
}
FormulaRecord fr = ((FormulaRecordAggregate)_record).getFormulaRecord();
checkFormulaCachedValueType(CellType.NUMERIC, fr);
return fr.getValue();
}
/**
* Get the value of the cell as a date.
* For strings we throw an exception.
* For blank cells we return a null.
* See {@link HSSFDataFormatter} for formatting
* this date into a string similar to how excel does.
*/
public Date getDateCellValue() {
if (_cellType == CellType.BLANK) {
return null;
}
double value = getNumericCellValue();
if (_book.getWorkbook().isUsing1904DateWindowing()) {
return HSSFDateUtil.getJavaDate(value, true);
}
return HSSFDateUtil.getJavaDate(value, false);
}
/**
* get the value of the cell as a string - for numeric cells we throw an exception.
* For blank cells we return an empty string.
* For formulaCells that are not string Formulas, we throw an exception
*/
public String getStringCellValue()
{
HSSFRichTextString str = getRichStringCellValue();
return str.getString();
}
/**
* get the value of the cell as a string - for numeric cells we throw an exception.
* For blank cells we return an empty string.
* For formulaCells that are not string Formulas, we throw an exception
*/
public HSSFRichTextString getRichStringCellValue() {
switch(_cellType) {
case BLANK:
return new HSSFRichTextString("");
case STRING:
return _stringValue;
default:
throw typeMismatch(CellType.STRING, _cellType, false);
case FORMULA:
break;
}
FormulaRecordAggregate fra = ((FormulaRecordAggregate)_record);
checkFormulaCachedValueType(CellType.STRING, fra.getFormulaRecord());
String strVal = fra.getStringValue();
return new HSSFRichTextString(strVal == null ? "" : strVal);
}
/**
* set a boolean value for the cell
*
* @param value the boolean value to set this cell to. For formulas we'll set the
* precalculated value, for booleans we'll set its value. For other types we
* will change the cell to a boolean cell and set its value.
*/
@SuppressWarnings("fallthrough")
public void setCellValue(boolean value) {
int row=_record.getRow();
short col=_record.getColumn();
short styleIndex=_record.getXFIndex();
switch (_cellType) {
default:
setCellType(CellType.BOOLEAN, false, row, col, styleIndex);
// fall through
case BOOLEAN:
(( BoolErrRecord ) _record).setValue(value);
break;
case FORMULA:
((FormulaRecordAggregate)_record).setCachedBooleanResult(value);
break;
}
}
/**
* set a error value for the cell
*
* @param errorCode the error value to set this cell to. For formulas we'll set the
* precalculated value , for errors we'll set
* its value. For other types we will change the cell to an error
* cell and set its value.
* For error code byte, see {@link FormulaError}.
* @deprecated 3.15 beta 2. Use {@link #setCellErrorValue(FormulaError)} instead.
*/
public void setCellErrorValue(byte errorCode) {
FormulaError error = FormulaError.forInt(errorCode);
setCellErrorValue(error);
}
/**
* set a error value for the cell
*
* @param error the error value to set this cell to. For formulas we'll set the
* precalculated value , for errors we'll set
* its value. For other types we will change the cell to an error
* cell and set its value.
*/
@SuppressWarnings("fallthrough")
public void setCellErrorValue(FormulaError error) {
int row=_record.getRow();
short col=_record.getColumn();
short styleIndex=_record.getXFIndex();
switch (_cellType) {
default:
setCellType(CellType.ERROR, false, row, col, styleIndex);
// fall through
case ERROR:
(( BoolErrRecord ) _record).setValue(error);
break;
case FORMULA:
((FormulaRecordAggregate)_record).setCachedErrorResult(error.getCode());
break;
}
}
/**
* Chooses a new boolean value for the cell when its type is changing.<p/>
*
* Usually the caller is calling setCellType() with the intention of calling
* setCellValue(boolean) straight afterwards. This method only exists to give
* the cell a somewhat reasonable value until the setCellValue() call (if at all).
* TODO - perhaps a method like setCellTypeAndValue(int, Object) should be introduced to avoid this
*/
private boolean convertCellValueToBoolean() {
switch (_cellType) {
case BOOLEAN:
return (( BoolErrRecord ) _record).getBooleanValue();
case STRING:
int sstIndex = ((LabelSSTRecord)_record).getSSTIndex();
String text = _book.getWorkbook().getSSTString(sstIndex).getString();
return Boolean.valueOf(text).booleanValue();
case NUMERIC:
return ((NumberRecord)_record).getValue() != 0;
case FORMULA:
// use cached formula result if it's the right type:
FormulaRecord fr = ((FormulaRecordAggregate)_record).getFormulaRecord();
checkFormulaCachedValueType(CellType.BOOLEAN, fr);
return fr.getCachedBooleanValue();
// Other cases convert to false
// These choices are not well justified.
case ERROR:
case BLANK:
return false;
}
throw new RuntimeException("Unexpected cell type (" + _cellType + ")");
}
private String convertCellValueToString() {
switch (_cellType) {
case BLANK:
return "";
case BOOLEAN:
return ((BoolErrRecord) _record).getBooleanValue() ? "TRUE" : "FALSE";
case STRING:
int sstIndex = ((LabelSSTRecord)_record).getSSTIndex();
return _book.getWorkbook().getSSTString(sstIndex).getString();
case NUMERIC:
return NumberToTextConverter.toText(((NumberRecord)_record).getValue());
case ERROR:
return FormulaError.forInt(((BoolErrRecord)_record).getErrorValue()).getString();
case FORMULA:
// should really evaluate, but HSSFCell can't call HSSFFormulaEvaluator
// just use cached formula result instead
break;
default:
throw new IllegalStateException("Unexpected cell type (" + _cellType + ")");
}
FormulaRecordAggregate fra = ((FormulaRecordAggregate)_record);
FormulaRecord fr = fra.getFormulaRecord();
switch (CellType.forInt(fr.getCachedResultType())) {
case BOOLEAN:
return fr.getCachedBooleanValue() ? "TRUE" : "FALSE";
case STRING:
return fra.getStringValue();
case NUMERIC:
return NumberToTextConverter.toText(fr.getValue());
case ERROR:
return FormulaError.forInt(fr.getCachedErrorValue()).getString();
default:
throw new IllegalStateException("Unexpected formula result type (" + _cellType + ")");
}
}
/**
* get the value of the cell as a boolean. For strings, numbers, and errors, we throw an exception.
* For blank cells we return a false.
*/
@Override
public boolean getBooleanCellValue() {
switch(_cellType) {
case BLANK:
return false;
case BOOLEAN:
return (( BoolErrRecord ) _record).getBooleanValue();
case FORMULA:
break;
default:
throw typeMismatch(CellType.BOOLEAN, _cellType, false);
}
FormulaRecord fr = ((FormulaRecordAggregate)_record).getFormulaRecord();
checkFormulaCachedValueType(CellType.BOOLEAN, fr);
return fr.getCachedBooleanValue();
}
/**
* get the value of the cell as an error code. For strings, numbers, and booleans, we throw an exception.
* For blank cells we return a 0.
*/
@Override
public byte getErrorCellValue() {
switch(_cellType) {
case ERROR:
return (( BoolErrRecord ) _record).getErrorValue();
case FORMULA:
FormulaRecord fr = ((FormulaRecordAggregate)_record).getFormulaRecord();
checkFormulaCachedValueType(CellType.ERROR, fr);
return (byte) fr.getCachedErrorValue();
default:
throw typeMismatch(CellType.ERROR, _cellType, false);
}
}
/**
* <p>Set the style for the cell. The style should be an HSSFCellStyle created/retreived from
* the HSSFWorkbook.</p>
*
* <p>To change the style of a cell without affecting other cells that use the same style,
* use {@link org.apache.poi.ss.util.CellUtil#setCellStyleProperties(org.apache.poi.ss.usermodel.Cell, java.util.Map)}</p>
*
* @param style reference contained in the workbook
* @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createCellStyle()
* @see org.apache.poi.hssf.usermodel.HSSFWorkbook#getCellStyleAt(int)
*/
public void setCellStyle(CellStyle style) {
setCellStyle( (HSSFCellStyle)style );
}
public void setCellStyle(HSSFCellStyle style) {
// A style of null means resetting back to the default style
if (style == null) {
_record.setXFIndex((short)0xf);
return;
}
// Verify the style really does belong to our workbook
style.verifyBelongsToWorkbook(_book);
short styleIndex;
if(style.getUserStyleName() != null) {
styleIndex = applyUserCellStyle(style);
} else {
styleIndex = style.getIndex();
}
// Change our cell record to use this style
_record.setXFIndex(styleIndex);
}
/**
* get the style for the cell. This is a reference to a cell style contained in the workbook
* object.
* @see org.apache.poi.hssf.usermodel.HSSFWorkbook#getCellStyleAt(int)
*/
public HSSFCellStyle getCellStyle()
{
short styleIndex=_record.getXFIndex();
ExtendedFormatRecord xf = _book.getWorkbook().getExFormatAt(styleIndex);
return new HSSFCellStyle(styleIndex, xf, _book);
}
/**
* Should only be used by HSSFSheet and friends. Returns the low level CellValueRecordInterface record
*
* @return CellValueRecordInterface representing the cell via the low level api.
*/
protected CellValueRecordInterface getCellValueRecord()
{
return _record;
}
/**
* @throws RuntimeException if the bounds are exceeded.
*/
private static void checkBounds(int cellIndex) {
if (cellIndex < 0 || cellIndex > LAST_COLUMN_NUMBER) {
throw new IllegalArgumentException("Invalid column index (" + cellIndex
+ "). Allowable column range for " + FILE_FORMAT_NAME + " is (0.."
+ LAST_COLUMN_NUMBER + ") or ('A'..'" + LAST_COLUMN_NAME + "')");
}
}
/**
* {@inheritDoc}
*/
@Override
public void setAsActiveCell()
{
int row=_record.getRow();
short col=_record.getColumn();
_sheet.getSheet().setActiveCellRow(row);
_sheet.getSheet().setActiveCellCol(col);
}
/**
* Returns a string representation of the cell
*
* This method returns a simple representation,
* anything more complex should be in user code, with
* knowledge of the semantics of the sheet being processed.
*
* Formula cells return the formula string,
* rather than the formula result.
* Dates are displayed in dd-MMM-yyyy format
* Errors are displayed as #ERR<errIdx>
*/
public String toString() {
switch (getCellTypeEnum()) {
case BLANK:
return "";
case BOOLEAN:
return getBooleanCellValue()?"TRUE":"FALSE";
case ERROR:
return ErrorEval.getText((( BoolErrRecord ) _record).getErrorValue());
case FORMULA:
return getCellFormula();
case NUMERIC:
//TODO apply the dataformat for this cell
if (HSSFDateUtil.isCellDateFormatted(this)) {
SimpleDateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy", LocaleUtil.getUserLocale());
sdf.setTimeZone(LocaleUtil.getUserTimeZone());
return sdf.format(getDateCellValue());
}
return String.valueOf(getNumericCellValue());
case STRING:
return getStringCellValue();
default:
return "Unknown Cell Type: " + getCellTypeEnum();
}
}
/**
* Assign a comment to this cell. If the supplied
* comment is null, the comment for this cell
* will be removed.
*
* @param comment comment associated with this cell
*/
public void setCellComment(Comment comment){
if(comment == null) {
removeCellComment();
return;
}
comment.setRow(_record.getRow());
comment.setColumn(_record.getColumn());
_comment = (HSSFComment)comment;
}
/**
* Returns comment associated with this cell
*
* @return comment associated with this cell
*/
public HSSFComment getCellComment(){
if (_comment == null) {
_comment = _sheet.findCellComment(_record.getRow(), _record.getColumn());
}
return _comment;
}
/**
* Removes the comment for this cell, if
* there is one.
* WARNING - some versions of excel will loose
* all comments after performing this action!
*/
public void removeCellComment() {
HSSFComment comment = _sheet.findCellComment(_record.getRow(), _record.getColumn());
_comment = null;
if (null == comment){
return;
}
_sheet.getDrawingPatriarch().removeShape(comment);
}
/**
* @return hyperlink associated with this cell or <code>null</code> if not found
*/
@Override
public HSSFHyperlink getHyperlink(){
return _sheet.getHyperlink(_record.getRow(), _record.getColumn());
}
/**
* Assign a hyperlink to this cell. If the supplied hyperlink is null, the
* hyperlink for this cell will be removed.
*
* @param hyperlink hyperlink associated with this cell
*/
@Override
public void setHyperlink(Hyperlink hyperlink){
if (hyperlink == null) {
removeHyperlink();
return;
}
HSSFHyperlink link = (HSSFHyperlink)hyperlink;
link.setFirstRow(_record.getRow());
link.setLastRow(_record.getRow());
link.setFirstColumn(_record.getColumn());
link.setLastColumn(_record.getColumn());
switch(link.getTypeEnum()){
case EMAIL:
case URL:
link.setLabel("url");
break;
case FILE:
link.setLabel("file");
break;
case DOCUMENT:
link.setLabel("place");
break;
default:
break;
}
List<RecordBase> records = _sheet.getSheet().getRecords();
int eofLoc = records.size() - 1;
records.add( eofLoc, link.record );
}
/**
* Removes the hyperlink for this cell, if there is one.
*/
public void removeHyperlink() {
for (Iterator<RecordBase> it = _sheet.getSheet().getRecords().iterator(); it.hasNext();) {
RecordBase rec = it.next();
if (rec instanceof HyperlinkRecord) {
HyperlinkRecord link = (HyperlinkRecord) rec;
if (link.getFirstColumn() == _record.getColumn() && link.getFirstRow() == _record.getRow()) {
it.remove();
return;
}
}
}
}
/**
* Only valid for formula cells
*
* Will return {@link CellType} in a future version of POI.
* For forwards compatibility, do not hard-code cell type literals in your code.
*
* @return one of ({@link CellType#NUMERIC}, {@link CellType#STRING},
* {@link CellType#BOOLEAN}, {@link CellType#ERROR}) depending
* on the cached value of the formula
* @deprecated 3.15. Will return a {@link CellType} enum in the future.
*/
@Override
public int getCachedFormulaResultType() {
return getCachedFormulaResultTypeEnum().getCode();
}
/**
* Only valid for formula cells
* @return one of ({@link CellType#NUMERIC}, {@link CellType#STRING},
* {@link CellType#BOOLEAN}, {@link CellType#ERROR}) depending
* on the cached value of the formula
* @since POI 3.15 beta 3
* Will be deleted when we make the CellType enum transition. See bug 59791.
*/
@Override
public CellType getCachedFormulaResultTypeEnum() {
if (_cellType != CellType.FORMULA) {
throw new IllegalStateException("Only formula cells have cached results");
}
int code = ((FormulaRecordAggregate)_record).getFormulaRecord().getCachedResultType();
return CellType.forInt(code);
}
void setCellArrayFormula(CellRangeAddress range) {
int row = _record.getRow();
short col = _record.getColumn();
short styleIndex = _record.getXFIndex();
setCellType(CellType.FORMULA, false, row, col, styleIndex);
// Billet for formula in rec
Ptg[] ptgsForCell = {new ExpPtg(range.getFirstRow(), range.getFirstColumn())};
FormulaRecordAggregate agg = (FormulaRecordAggregate) _record;
agg.setParsedExpression(ptgsForCell);
}
public CellRangeAddress getArrayFormulaRange() {
if (_cellType != CellType.FORMULA) {
String ref = new CellReference(this).formatAsString();
throw new IllegalStateException("Cell " + ref
+ " is not part of an array formula.");
}
return ((FormulaRecordAggregate)_record).getArrayFormulaRange();
}
public boolean isPartOfArrayFormulaGroup() {
if (_cellType != CellType.FORMULA) {
return false;
}
return ((FormulaRecordAggregate)_record).isPartOfArrayFormula();
}
/**
* The purpose of this method is to validate the cell state prior to modification
*
* @see #notifyArrayFormulaChanging()
*/
void notifyArrayFormulaChanging(String msg){
CellRangeAddress cra = getArrayFormulaRange();
if(cra.getNumberOfCells() > 1) {
throw new IllegalStateException(msg);
}
//un-register the single-cell array formula from the parent XSSFSheet
getRow().getSheet().removeArrayFormula(this);
}
/**
* Called when this cell is modified.
* <p>
* The purpose of this method is to validate the cell state prior to modification.
* </p>
*
* @see #setCellType(int)
* @see #setCellFormula(String)
* @see HSSFRow#removeCell(org.apache.poi.ss.usermodel.Cell)
* @see org.apache.poi.hssf.usermodel.HSSFSheet#removeRow(org.apache.poi.ss.usermodel.Row)
* @see org.apache.poi.hssf.usermodel.HSSFSheet#shiftRows(int, int, int)
* @see org.apache.poi.hssf.usermodel.HSSFSheet#addMergedRegion(org.apache.poi.ss.util.CellRangeAddress)
* @throws IllegalStateException if modification is not allowed
*/
void notifyArrayFormulaChanging(){
CellReference ref = new CellReference(this);
String msg = "Cell "+ref.formatAsString()+" is part of a multi-cell array formula. " +
"You cannot change part of an array.";
notifyArrayFormulaChanging(msg);
}
/**
* Applying a user-defined style (UDS) is special. Excel does not directly reference user-defined styles, but
* instead create a 'proxy' ExtendedFormatRecord referencing the UDS as parent.
*
* The proceudre to apply a UDS is as follows:
*
* 1. search for a ExtendedFormatRecord with parentIndex == style.getIndex()
* and xfType == ExtendedFormatRecord.XF_CELL.
* 2. if not found then create a new ExtendedFormatRecord and copy all attributes from the user-defined style
* and set the parentIndex to be style.getIndex()
* 3. return the index of the ExtendedFormatRecord, this will be assigned to the parent cell record
*
* @param style the user style to apply
*
* @return the index of a ExtendedFormatRecord record that will be referenced by the cell
*/
private short applyUserCellStyle(HSSFCellStyle style){
if(style.getUserStyleName() == null) {
throw new IllegalArgumentException("Expected user-defined style");
}
InternalWorkbook iwb = _book.getWorkbook();
short userXf = -1;
int numfmt = iwb.getNumExFormats();
for(short i = 0; i < numfmt; i++){
ExtendedFormatRecord xf = iwb.getExFormatAt(i);
if(xf.getXFType() == ExtendedFormatRecord.XF_CELL && xf.getParentIndex() == style.getIndex() ){
userXf = i;
break;
}
}
short styleIndex;
if (userXf == -1){
ExtendedFormatRecord xfr = iwb.createCellXF();
xfr.cloneStyleFrom(iwb.getExFormatAt(style.getIndex()));
xfr.setIndentionOptions((short)0);
xfr.setXFType(ExtendedFormatRecord.XF_CELL);
xfr.setParentIndex(style.getIndex());
styleIndex = (short)numfmt;
} else {
styleIndex = userXf;
}
return styleIndex;
}
}
上一篇： POI导出Excel
下一篇： oracle密码修改方法