001: /* ====================================================================
002: Licensed to the Apache Software Foundation (ASF) under one or more
003: contributor license agreements. See the NOTICE file distributed with
004: this work for additional information regarding copyright ownership.
005: The ASF licenses this file to You under the Apache License, Version 2.0
006: (the "License"); you may not use this file except in compliance with
007: the License. You may obtain a copy of the License at
008:
009: http://www.apache.org/licenses/LICENSE-2.0
010:
011: Unless required by applicable law or agreed to in writing, software
012: distributed under the License is distributed on an "AS IS" BASIS,
013: WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
014: See the License for the specific language governing permissions and
015: limitations under the License.
016: ==================================================================== */
017:
018: /*
019: * Cell.java
020: *
021: * Created on September 30, 2001, 3:46 PM
022: */
023: package org.apache.poi.hssf.usermodel;
024:
025: import org.apache.poi.hssf.model.FormulaParser;
026: import org.apache.poi.hssf.model.Sheet;
027: import org.apache.poi.hssf.model.Workbook;
028: import org.apache.poi.hssf.record.*;
029: import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
030: import org.apache.poi.hssf.record.formula.Ptg;
031:
032: import java.text.DateFormat;
033: import java.text.SimpleDateFormat;
034: import java.util.*;
035:
036: /**
037: * High level representation of a cell in a row of a spreadsheet.
038: * Cells can be numeric, formula-based or string-based (text). The cell type
039: * specifies this. String cells cannot conatin numbers and numeric cells cannot
040: * contain strings (at least according to our model). Client apps should do the
041: * conversions themselves. Formula cells have the formula string, as well as
042: * the formula result, which can be numeric or string.
043: * <p>
044: * Cells should have their number (0 based) before being added to a row. Only
045: * cells that have values should be added.
046: * <p>
047: *
048: * @author Andrew C. Oliver (acoliver at apache dot org)
049: * @author Dan Sherman (dsherman at isisph.com)
050: * @author Brian Sanders (kestrel at burdell dot org) Active Cell support
051: * @author Yegor Kozlov cell comments support
052: * @version 1.0-pre
053: */
054:
055: public class HSSFCell {
056:
057: /**
058: * Numeric Cell type (0)
059: * @see #setCellType(int)
060: * @see #getCellType()
061: */
062:
063: public final static int CELL_TYPE_NUMERIC = 0;
064:
065: /**
066: * String Cell type (1)
067: * @see #setCellType(int)
068: * @see #getCellType()
069: */
070:
071: public final static int CELL_TYPE_STRING = 1;
072:
073: /**
074: * Formula Cell type (2)
075: * @see #setCellType(int)
076: * @see #getCellType()
077: */
078:
079: public final static int CELL_TYPE_FORMULA = 2;
080:
081: /**
082: * Blank Cell type (3)
083: * @see #setCellType(int)
084: * @see #getCellType()
085: */
086:
087: public final static int CELL_TYPE_BLANK = 3;
088:
089: /**
090: * Boolean Cell type (4)
091: * @see #setCellType(int)
092: * @see #getCellType()
093: */
094:
095: public final static int CELL_TYPE_BOOLEAN = 4;
096:
097: /**
098: * Error Cell type (5)
099: * @see #setCellType(int)
100: * @see #getCellType()
101: */
102:
103: public final static int CELL_TYPE_ERROR = 5;
104: public final static short ENCODING_UNCHANGED = -1;
105: public final static short ENCODING_COMPRESSED_UNICODE = 0;
106: public final static short ENCODING_UTF_16 = 1;
107: private int cellType;
108: private HSSFRichTextString stringValue;
109: private short encoding = ENCODING_UNCHANGED;
110: private Workbook book;
111: private Sheet sheet;
112: private CellValueRecordInterface record;
113: private HSSFComment comment;
114:
115: /**
116: * Creates new Cell - Should only be called by HSSFRow. This creates a cell
117: * from scratch.
118: * <p>
119: * When the cell is initially created it is set to CELL_TYPE_BLANK. Cell types
120: * can be changed/overwritten by calling setCellValue with the appropriate
121: * type as a parameter although conversions from one type to another may be
122: * prohibited.
123: *
124: * @param book - Workbook record of the workbook containing this cell
125: * @param sheet - Sheet record of the sheet containing this cell
126: * @param row - the row of this cell
127: * @param col - the column for this cell
128: *
129: * @see org.apache.poi.hssf.usermodel.HSSFRow#createCell(short)
130: */
131:
132: //protected HSSFCell(Workbook book, Sheet sheet, short row, short col)
133: protected HSSFCell(Workbook book, Sheet sheet, int row, short col) {
134: checkBounds(col);
135: stringValue = null;
136: this .book = book;
137: this .sheet = sheet;
138:
139: // Relying on the fact that by default the cellType is set to 0 which
140: // is different to CELL_TYPE_BLANK hence the following method call correctly
141: // creates a new blank cell.
142: short xfindex = sheet.getXFIndexForColAt(col);
143: setCellType(CELL_TYPE_BLANK, false, row, col, xfindex);
144: }
145:
146: /**
147: * Creates new Cell - Should only be called by HSSFRow. This creates a cell
148: * from scratch.
149: *
150: * @param book - Workbook record of the workbook containing this cell
151: * @param sheet - Sheet record of the sheet containing this cell
152: * @param row - the row of this cell
153: * @param col - the column for this cell
154: * @param type - CELL_TYPE_NUMERIC, CELL_TYPE_STRING, CELL_TYPE_FORMULA, CELL_TYPE_BLANK,
155: * CELL_TYPE_BOOLEAN, CELL_TYPE_ERROR
156: * Type of cell
157: * @see org.apache.poi.hssf.usermodel.HSSFRow#createCell(short,int)
158: */
159:
160: //protected HSSFCell(Workbook book, Sheet sheet, short row, short col,
161: protected HSSFCell(Workbook book, Sheet sheet, int row, short col,
162: int type) {
163: checkBounds(col);
164: cellType = -1; // Force 'setCellType' to create a first Record
165: stringValue = null;
166: this .book = book;
167: this .sheet = sheet;
168:
169: short xfindex = sheet.getXFIndexForColAt(col);
170: setCellType(type, false, row, col, xfindex);
171: }
172:
173: /**
174: * Creates an HSSFCell from a CellValueRecordInterface. HSSFSheet uses this when
175: * reading in cells from an existing sheet.
176: *
177: * @param book - Workbook record of the workbook containing this cell
178: * @param sheet - Sheet record of the sheet containing this cell
179: * @param cval - the Cell Value Record we wish to represent
180: */
181:
182: //protected HSSFCell(Workbook book, Sheet sheet, short row,
183: protected HSSFCell(Workbook book, Sheet sheet, int row,
184: CellValueRecordInterface cval) {
185: record = cval;
186: cellType = determineType(cval);
187: stringValue = null;
188: this .book = book;
189: this .sheet = sheet;
190: switch (cellType) {
191: case CELL_TYPE_STRING:
192: stringValue = new HSSFRichTextString(book,
193: (LabelSSTRecord) cval);
194: break;
195:
196: case CELL_TYPE_BLANK:
197: break;
198:
199: case CELL_TYPE_FORMULA:
200: stringValue = new HSSFRichTextString(
201: ((FormulaRecordAggregate) cval).getStringValue());
202: break;
203: }
204: ExtendedFormatRecord xf = book.getExFormatAt(cval.getXFIndex());
205:
206: setCellStyle(new HSSFCellStyle((short) cval.getXFIndex(), xf));
207: }
208:
209: /**
210: * private constructor to prevent blank construction
211: */
212: private HSSFCell() {
213: }
214:
215: /**
216: * used internally -- given a cell value record, figure out its type
217: */
218: private int determineType(CellValueRecordInterface cval) {
219: Record record = (Record) cval;
220: int sid = record.getSid();
221: int retval = 0;
222:
223: switch (sid) {
224:
225: case NumberRecord.sid:
226: retval = HSSFCell.CELL_TYPE_NUMERIC;
227: break;
228:
229: case BlankRecord.sid:
230: retval = HSSFCell.CELL_TYPE_BLANK;
231: break;
232:
233: case LabelSSTRecord.sid:
234: retval = HSSFCell.CELL_TYPE_STRING;
235: break;
236:
237: case FormulaRecordAggregate.sid:
238: retval = HSSFCell.CELL_TYPE_FORMULA;
239: break;
240:
241: case BoolErrRecord.sid:
242: BoolErrRecord boolErrRecord = (BoolErrRecord) record;
243:
244: retval = (boolErrRecord.isBoolean()) ? HSSFCell.CELL_TYPE_BOOLEAN
245: : HSSFCell.CELL_TYPE_ERROR;
246: break;
247: }
248: return retval;
249: }
250:
251: /**
252: * Returns the Workbook that this Cell is bound to
253: * @return
254: */
255: protected Workbook getBoundWorkbook() {
256: return book;
257: }
258:
259: /**
260: * set the cell's number within the row (0 based)
261: * @param num short the cell number
262: */
263:
264: public void setCellNum(short num) {
265: record.setColumn(num);
266: }
267:
268: /**
269: * get the cell's number within the row
270: * @return short reperesenting the column number (logical!)
271: */
272:
273: public short getCellNum() {
274: return record.getColumn();
275: }
276:
277: /**
278: * set the cells type (numeric, formula or string)
279: * @see #CELL_TYPE_NUMERIC
280: * @see #CELL_TYPE_STRING
281: * @see #CELL_TYPE_FORMULA
282: * @see #CELL_TYPE_BLANK
283: * @see #CELL_TYPE_BOOLEAN
284: * @see #CELL_TYPE_ERROR
285: */
286:
287: public void setCellType(int cellType) {
288: int row = record.getRow();
289: short col = record.getColumn();
290: short styleIndex = record.getXFIndex();
291: setCellType(cellType, true, row, col, styleIndex);
292: }
293:
294: /**
295: * sets the cell type. The setValue flag indicates whether to bother about
296: * trying to preserve the current value in the new record if one is created.
297: * <p>
298: * The @see #setCellValue method will call this method with false in setValue
299: * since it will overwrite the cell value later
300: *
301: */
302:
303: private void setCellType(int cellType, boolean setValue, int row,
304: short col, short styleIndex) {
305:
306: // if (cellType == CELL_TYPE_FORMULA)
307: // {
308: // throw new RuntimeException(
309: // "Formulas have not been implemented in this release");
310: // }
311: if (cellType > CELL_TYPE_ERROR) {
312: throw new RuntimeException(
313: "I have no idea what type that is!");
314: }
315: switch (cellType) {
316:
317: case CELL_TYPE_FORMULA:
318: FormulaRecordAggregate frec = null;
319:
320: if (cellType != this .cellType) {
321: frec = new FormulaRecordAggregate(new FormulaRecord(),
322: null);
323: } else {
324: frec = (FormulaRecordAggregate) record;
325: }
326: frec.setColumn(col);
327: if (setValue) {
328: frec.getFormulaRecord().setValue(getNumericCellValue());
329: }
330: frec.setXFIndex(styleIndex);
331: frec.setRow(row);
332: record = frec;
333: break;
334:
335: case CELL_TYPE_NUMERIC:
336: NumberRecord nrec = null;
337:
338: if (cellType != this .cellType) {
339: nrec = new NumberRecord();
340: } else {
341: nrec = (NumberRecord) record;
342: }
343: nrec.setColumn(col);
344: if (setValue) {
345: nrec.setValue(getNumericCellValue());
346: }
347: nrec.setXFIndex(styleIndex);
348: nrec.setRow(row);
349: record = nrec;
350: break;
351:
352: case CELL_TYPE_STRING:
353: LabelSSTRecord lrec = null;
354:
355: if (cellType != this .cellType) {
356: lrec = new LabelSSTRecord();
357: } else {
358: lrec = (LabelSSTRecord) record;
359: }
360: lrec.setColumn(col);
361: lrec.setRow(row);
362: lrec.setXFIndex(styleIndex);
363: if (setValue) {
364: if ((getStringCellValue() != null)
365: && (!getStringCellValue().equals(""))) {
366: int sst = 0;
367:
368: UnicodeString str = getRichStringCellValue()
369: .getUnicodeString();
370: //jmh if (encoding == ENCODING_COMPRESSED_UNICODE)
371: //jmh {
372: // jmh str.setCompressedUnicode();
373: // jmh } else if (encoding == ENCODING_UTF_16)
374: // jmh {
375: // jmh str.setUncompressedUnicode();
376: // jmh }
377: sst = book.addSSTString(str);
378: lrec.setSSTIndex(sst);
379: getRichStringCellValue().setUnicodeString(
380: book.getSSTString(sst));
381: }
382: }
383: record = lrec;
384: break;
385:
386: case CELL_TYPE_BLANK:
387: BlankRecord brec = null;
388:
389: if (cellType != this .cellType) {
390: brec = new BlankRecord();
391: } else {
392: brec = (BlankRecord) record;
393: }
394: brec.setColumn(col);
395:
396: // During construction the cellStyle may be null for a Blank cell.
397: brec.setXFIndex(styleIndex);
398: brec.setRow(row);
399: record = brec;
400: break;
401:
402: case CELL_TYPE_BOOLEAN:
403: BoolErrRecord boolRec = null;
404:
405: if (cellType != this .cellType) {
406: boolRec = new BoolErrRecord();
407: } else {
408: boolRec = (BoolErrRecord) record;
409: }
410: boolRec.setColumn(col);
411: if (setValue) {
412: boolRec.setValue(getBooleanCellValue());
413: }
414: boolRec.setXFIndex(styleIndex);
415: boolRec.setRow(row);
416: record = boolRec;
417: break;
418:
419: case CELL_TYPE_ERROR:
420: BoolErrRecord errRec = null;
421:
422: if (cellType != this .cellType) {
423: errRec = new BoolErrRecord();
424: } else {
425: errRec = (BoolErrRecord) record;
426: }
427: errRec.setColumn(col);
428: if (setValue) {
429: errRec.setValue(getErrorCellValue());
430: }
431: errRec.setXFIndex(styleIndex);
432: errRec.setRow(row);
433: record = errRec;
434: break;
435: }
436: if (cellType != this .cellType && this .cellType != -1) // Special Value to indicate an uninitialized Cell
437: {
438: int loc = sheet.getLoc();
439:
440: sheet.replaceValueRecord(record);
441: sheet.setLoc(loc);
442: }
443: this .cellType = cellType;
444: }
445:
446: /**
447: * get the cells type (numeric, formula or string)
448: * @see #CELL_TYPE_STRING
449: * @see #CELL_TYPE_NUMERIC
450: * @see #CELL_TYPE_FORMULA
451: * @see #CELL_TYPE_BOOLEAN
452: * @see #CELL_TYPE_ERROR
453: */
454:
455: public int getCellType() {
456: return cellType;
457: }
458:
459: /**
460: * set a numeric value for the cell
461: *
462: * @param value the numeric value to set this cell to. For formulas we'll set the
463: * precalculated value, for numerics we'll set its value. For other types we
464: * will change the cell to a numeric cell and set its value.
465: */
466: public void setCellValue(double value) {
467: int row = record.getRow();
468: short col = record.getColumn();
469: short styleIndex = record.getXFIndex();
470: if ((cellType != CELL_TYPE_NUMERIC)
471: && (cellType != CELL_TYPE_FORMULA)) {
472: setCellType(CELL_TYPE_NUMERIC, false, row, col, styleIndex);
473: }
474: ((NumberRecord) record).setValue(value);
475: }
476:
477: /**
478: * set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
479: * a date.
480: *
481: * @param value the date value to set this cell to. For formulas we'll set the
482: * precalculated value, for numerics we'll set its value. For other types we
483: * will change the cell to a numeric cell and set its value.
484: */
485: public void setCellValue(Date value) {
486: setCellValue(HSSFDateUtil.getExcelDate(value, this .book
487: .isUsing1904DateWindowing()));
488: }
489:
490: /**
491: * set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
492: * a date.
493: *
494: * @param value the date value to set this cell to. For formulas we'll set the
495: * precalculated value, for numerics we'll set its value. For othertypes we
496: * will change the cell to a numeric cell and set its value.
497: */
498: public void setCellValue(Calendar value) {
499: setCellValue(value.getTime());
500: }
501:
502: /**
503: * set a string value for the cell. Please note that if you are using
504: * full 16 bit unicode you should call <code>setEncoding()</code> first.
505: *
506: * @param value value to set the cell to. For formulas we'll set the formula
507: * string, for String cells we'll set its value. For other types we will
508: * change the cell to a string cell and set its value.
509: * If value is null then we will change the cell to a Blank cell.
510: * @deprecated Use setCellValue(HSSFRichTextString) instead.
511: */
512:
513: public void setCellValue(String value) {
514: HSSFRichTextString str = new HSSFRichTextString(value);
515: setCellValue(str);
516: }
517:
518: /**
519: * set a string value for the cell. Please note that if you are using
520: * full 16 bit unicode you should call <code>setEncoding()</code> first.
521: *
522: * @param value value to set the cell to. For formulas we'll set the formula
523: * string, for String cells we'll set its value. For other types we will
524: * change the cell to a string cell and set its value.
525: * If value is null then we will change the cell to a Blank cell.
526: */
527:
528: public void setCellValue(HSSFRichTextString value) {
529: int row = record.getRow();
530: short col = record.getColumn();
531: short styleIndex = record.getXFIndex();
532: if (value == null) {
533: setCellType(CELL_TYPE_BLANK, false, row, col, styleIndex);
534: } else {
535: if ((cellType != CELL_TYPE_STRING)
536: && (cellType != CELL_TYPE_FORMULA)) {
537: setCellType(CELL_TYPE_STRING, false, row, col,
538: styleIndex);
539: }
540: int index = 0;
541:
542: UnicodeString str = value.getUnicodeString();
543: // jmh if (encoding == ENCODING_COMPRESSED_UNICODE)
544: // jmh {
545: // jmh str.setCompressedUnicode();
546: // jmh } else if (encoding == ENCODING_UTF_16)
547: // jmh {
548: // jmh str.setUncompressedUnicode();
549: // jmh }
550: index = book.addSSTString(str);
551: ((LabelSSTRecord) record).setSSTIndex(index);
552: stringValue = value;
553: stringValue.setWorkbookReferences(book,
554: ((LabelSSTRecord) record));
555: stringValue.setUnicodeString(book.getSSTString(index));
556: }
557: }
558:
559: public void setCellFormula(String formula) {
560: int row = record.getRow();
561: short col = record.getColumn();
562: short styleIndex = record.getXFIndex();
563: //Workbook.currentBook=book;
564: if (formula == null) {
565: setCellType(CELL_TYPE_BLANK, false, row, col, styleIndex);
566: } else {
567: setCellType(CELL_TYPE_FORMULA, false, row, col, styleIndex);
568: FormulaRecordAggregate rec = (FormulaRecordAggregate) record;
569: FormulaRecord frec = rec.getFormulaRecord();
570: frec.setOptions((short) 2);
571: frec.setValue(0);
572:
573: //only set to default if there is no extended format index already set
574: if (rec.getXFIndex() == (short) 0)
575: rec.setXFIndex((short) 0x0f);
576: FormulaParser fp = new FormulaParser(formula + ";", book);
577: fp.parse();
578: Ptg[] ptg = fp.getRPNPtg();
579: int size = 0;
580:
581: // clear the Ptg Stack
582: for (int i = 0, iSize = frec.getNumberOfExpressionTokens(); i < iSize; i++) {
583: frec.popExpressionToken();
584: }
585:
586: // fill the Ptg Stack with Ptgs of new formula
587: for (int k = 0; k < ptg.length; k++) {
588: size += ptg[k].getSize();
589: frec.pushExpressionToken(ptg[k]);
590: }
591: rec.getFormulaRecord().setExpressionLength((short) size);
592: //Workbook.currentBook = null;
593: }
594: }
595:
596: public String getCellFormula() {
597: //Workbook.currentBook=book;
598: String retval = FormulaParser.toFormulaString(book,
599: ((FormulaRecordAggregate) record).getFormulaRecord()
600: .getParsedExpression());
601: //Workbook.currentBook=null;
602: return retval;
603: }
604:
605: /**
606: * get the value of the cell as a number. For strings we throw an exception.
607: * For blank cells we return a 0.
608: */
609:
610: public double getNumericCellValue() {
611: if (cellType == CELL_TYPE_BLANK) {
612: return 0;
613: }
614: if (cellType == CELL_TYPE_STRING) {
615: throw new NumberFormatException(
616: "You cannot get a numeric value from a String based cell");
617: }
618: if (cellType == CELL_TYPE_BOOLEAN) {
619: throw new NumberFormatException(
620: "You cannot get a numeric value from a boolean cell");
621: }
622: if (cellType == CELL_TYPE_ERROR) {
623: throw new NumberFormatException(
624: "You cannot get a numeric value from an error cell");
625: }
626: if (cellType == CELL_TYPE_NUMERIC) {
627: return ((NumberRecord) record).getValue();
628: }
629: if (cellType == CELL_TYPE_FORMULA) {
630: return ((FormulaRecordAggregate) record).getFormulaRecord()
631: .getValue();
632: }
633: throw new NumberFormatException("Unknown Record Type in Cell:"
634: + cellType);
635: }
636:
637: /**
638: * get the value of the cell as a date. For strings we throw an exception.
639: * For blank cells we return a null.
640: */
641: public Date getDateCellValue() {
642: if (cellType == CELL_TYPE_BLANK) {
643: return null;
644: }
645: if (cellType == CELL_TYPE_STRING) {
646: throw new NumberFormatException(
647: "You cannot get a date value from a String based cell");
648: }
649: if (cellType == CELL_TYPE_BOOLEAN) {
650: throw new NumberFormatException(
651: "You cannot get a date value from a boolean cell");
652: }
653: if (cellType == CELL_TYPE_ERROR) {
654: throw new NumberFormatException(
655: "You cannot get a date value from an error cell");
656: }
657: double value = this .getNumericCellValue();
658: if (book.isUsing1904DateWindowing()) {
659: return HSSFDateUtil.getJavaDate(value, true);
660: } else {
661: return HSSFDateUtil.getJavaDate(value, false);
662: }
663: }
664:
665: /**
666: * get the value of the cell as a string - for numeric cells we throw an exception.
667: * For blank cells we return an empty string.
668: * For formulaCells that are not string Formulas, we return empty String
669: * @deprecated Use the HSSFRichTextString return
670: */
671:
672: public String getStringCellValue() {
673: HSSFRichTextString str = getRichStringCellValue();
674: return str.getString();
675: }
676:
677: /**
678: * get the value of the cell as a string - for numeric cells we throw an exception.
679: * For blank cells we return an empty string.
680: * For formulaCells that are not string Formulas, we return empty String
681: */
682:
683: public HSSFRichTextString getRichStringCellValue() {
684: if (cellType == CELL_TYPE_BLANK) {
685: return new HSSFRichTextString("");
686: }
687: if (cellType == CELL_TYPE_NUMERIC) {
688: throw new NumberFormatException(
689: "You cannot get a string value from a numeric cell");
690: }
691: if (cellType == CELL_TYPE_BOOLEAN) {
692: throw new NumberFormatException(
693: "You cannot get a string value from a boolean cell");
694: }
695: if (cellType == CELL_TYPE_ERROR) {
696: throw new NumberFormatException(
697: "You cannot get a string value from an error cell");
698: }
699: if (cellType == CELL_TYPE_FORMULA) {
700: if (stringValue == null)
701: return new HSSFRichTextString("");
702: }
703: return stringValue;
704: }
705:
706: /**
707: * set a boolean value for the cell
708: *
709: * @param value the boolean value to set this cell to. For formulas we'll set the
710: * precalculated value, for booleans we'll set its value. For other types we
711: * will change the cell to a boolean cell and set its value.
712: */
713:
714: public void setCellValue(boolean value) {
715: int row = record.getRow();
716: short col = record.getColumn();
717: short styleIndex = record.getXFIndex();
718: if ((cellType != CELL_TYPE_BOOLEAN)
719: && (cellType != CELL_TYPE_FORMULA)) {
720: setCellType(CELL_TYPE_BOOLEAN, false, row, col, styleIndex);
721: }
722: ((BoolErrRecord) record).setValue(value);
723: }
724:
725: /**
726: * set a error value for the cell
727: *
728: * @param value the error value to set this cell to. For formulas we'll set the
729: * precalculated value ??? IS THIS RIGHT??? , for errors we'll set
730: * its value. For other types we will change the cell to an error
731: * cell and set its value.
732: */
733:
734: public void setCellErrorValue(byte value) {
735: int row = record.getRow();
736: short col = record.getColumn();
737: short styleIndex = record.getXFIndex();
738: if ((cellType != CELL_TYPE_ERROR)
739: && (cellType != CELL_TYPE_FORMULA)) {
740: setCellType(CELL_TYPE_ERROR, false, row, col, styleIndex);
741: }
742: ((BoolErrRecord) record).setValue(value);
743: }
744:
745: /**
746: * get the value of the cell as a boolean. For strings, numbers, and errors, we throw an exception.
747: * For blank cells we return a false.
748: */
749:
750: public boolean getBooleanCellValue() {
751: if (cellType == CELL_TYPE_BOOLEAN) {
752: return ((BoolErrRecord) record).getBooleanValue();
753: }
754: if (cellType == CELL_TYPE_BLANK) {
755: return false;
756: }
757: throw new NumberFormatException(
758: "You cannot get a boolean value from a non-boolean cell");
759: }
760:
761: /**
762: * get the value of the cell as an error code. For strings, numbers, and booleans, we throw an exception.
763: * For blank cells we return a 0.
764: */
765:
766: public byte getErrorCellValue() {
767: if (cellType == CELL_TYPE_ERROR) {
768: return ((BoolErrRecord) record).getErrorValue();
769: }
770: if (cellType == CELL_TYPE_BLANK) {
771: return (byte) 0;
772: }
773: throw new NumberFormatException(
774: "You cannot get an error value from a non-error cell");
775: }
776:
777: /**
778: * set the style for the cell. The style should be an HSSFCellStyle created/retreived from
779: * the HSSFWorkbook.
780: *
781: * @param style reference contained in the workbook
782: * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createCellStyle()
783: * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#getCellStyleAt(short)
784: */
785:
786: public void setCellStyle(HSSFCellStyle style) {
787: record.setXFIndex(style.getIndex());
788: }
789:
790: /**
791: * get the style for the cell. This is a reference to a cell style contained in the workbook
792: * object.
793: * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#getCellStyleAt(short)
794: */
795:
796: public HSSFCellStyle getCellStyle() {
797: short styleIndex = record.getXFIndex();
798: ExtendedFormatRecord xf = book.getExFormatAt(styleIndex);
799: return new HSSFCellStyle(styleIndex, xf);
800: }
801:
802: /**
803: * used for internationalization, currently -1 for unchanged, 0 for compressed unicode or 1 for 16-bit
804: *
805: * @see #ENCODING_UNCHANGED
806: * @see #ENCODING_COMPRESSED_UNICODE
807: * @see #ENCODING_UTF_16
808: *
809: * @return -1, 1 or 0 for unchanged, compressed or uncompressed (used only with String type)
810: *
811: * @deprecated As of 3-Jan-06 POI now automatically handles Unicode without forcing the encoding.
812: */
813: public short getEncoding() {
814: return encoding;
815: }
816:
817: /**
818: * set the encoding to either 8 or 16 bit. (US/UK use 8-bit, rest of the western world use 16bit)
819: *
820: * @see #ENCODING_UNCHANGED
821: * @see #ENCODING_COMPRESSED_UNICODE
822: * @see #ENCODING_UTF_16
823: *
824: * @param encoding either ENCODING_COMPRESSED_UNICODE (0) or ENCODING_UTF_16 (1)
825: * @deprecated As of 3-Jan-06 POI now automatically handles Unicode without forcing the encoding.
826: */
827:
828: public void setEncoding(short encoding) {
829: this .encoding = encoding;
830: }
831:
832: /**
833: * Should only be used by HSSFSheet and friends. Returns the low level CellValueRecordInterface record
834: *
835: * @return CellValueRecordInterface representing the cell via the low level api.
836: */
837:
838: protected CellValueRecordInterface getCellValueRecord() {
839: return record;
840: }
841:
842: /**
843: * @throws RuntimeException if the bounds are exceeded.
844: */
845: private void checkBounds(int cellNum) {
846: if (cellNum > 255) {
847: throw new RuntimeException(
848: "You cannot have more than 255 columns "
849: + "in a given row (IV). Because Excel can't handle it");
850: } else if (cellNum < 0) {
851: throw new RuntimeException(
852: "You cannot reference columns with an index of less then 0.");
853: }
854: }
855:
856: /**
857: * Sets this cell as the active cell for the worksheet
858: */
859: public void setAsActiveCell() {
860: int row = record.getRow();
861: short col = record.getColumn();
862: this .sheet.setActiveCellRow(row);
863: this .sheet.setActiveCellCol(col);
864: }
865:
866: /**
867: * Returns a string representation of the cell
868: *
869: * This method returns a simple representation,
870: * anthing more complex should be in user code, with
871: * knowledge of the semantics of the sheet being processed.
872: *
873: * Formula cells return the formula string,
874: * rather than the formula result.
875: * Dates are displayed in dd-MMM-yyyy format
876: * Errors are displayed as #ERR<errIdx>
877: */
878: public String toString() {
879: switch (getCellType()) {
880: case CELL_TYPE_BLANK:
881: return "";
882: case CELL_TYPE_BOOLEAN:
883: return getBooleanCellValue() ? "TRUE" : "FALSE";
884: case CELL_TYPE_ERROR:
885: return "#ERR" + getErrorCellValue();
886: case CELL_TYPE_FORMULA:
887: return getCellFormula();
888: case CELL_TYPE_NUMERIC:
889: //TODO apply the dataformat for this cell
890: if (HSSFDateUtil.isCellDateFormatted(this )) {
891: DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
892: return sdf.format(getDateCellValue());
893: } else {
894: return getNumericCellValue() + "";
895: }
896: case CELL_TYPE_STRING:
897: return getStringCellValue();
898: default:
899: return "Unknown Cell Type: " + getCellType();
900: }
901: }
902:
903: /**
904: * Assign a comment to this cell
905: *
906: * @param comment comment associated with this cell
907: */
908: public void setCellComment(HSSFComment comment) {
909: comment.setRow((short) record.getRow());
910: comment.setColumn(record.getColumn());
911: this .comment = comment;
912: }
913:
914: /**
915: * Returns comment associated with this cell
916: *
917: * @return comment associated with this cell
918: */
919: public HSSFComment getCellComment() {
920: if (comment == null) {
921: comment = findCellComment(sheet, record.getRow(), record
922: .getColumn());
923: }
924: return comment;
925: }
926:
927: /**
928: * Cell comment finder.
929: * Returns cell comment for the specified sheet, row and column.
930: *
931: * @return cell comment or <code>null</code> if not found
932: */
933: protected static HSSFComment findCellComment(Sheet sheet, int row,
934: int column) {
935: HSSFComment comment = null;
936: HashMap txshapes = new HashMap(); //map shapeId and TextObjectRecord
937: for (Iterator it = sheet.getRecords().iterator(); it.hasNext();) {
938: Record rec = (Record) it.next();
939: if (rec instanceof NoteRecord) {
940: NoteRecord note = (NoteRecord) rec;
941: if (note.getRow() == row && note.getColumn() == column) {
942: TextObjectRecord txo = (TextObjectRecord) txshapes
943: .get(new Integer(note.getShapeId()));
944: comment = new HSSFComment(note, txo);
945: comment.setRow(note.getRow());
946: comment.setColumn(note.getColumn());
947: comment.setAuthor(note.getAuthor());
948: comment
949: .setVisible(note.getFlags() == NoteRecord.NOTE_VISIBLE);
950: comment.setString(txo.getStr());
951: break;
952: }
953: } else if (rec instanceof ObjRecord) {
954: ObjRecord obj = (ObjRecord) rec;
955: SubRecord sub = (SubRecord) obj.getSubRecords().get(0);
956: if (sub instanceof CommonObjectDataSubRecord) {
957: CommonObjectDataSubRecord cmo = (CommonObjectDataSubRecord) sub;
958: if (cmo.getObjectType() == CommonObjectDataSubRecord.OBJECT_TYPE_COMMENT) {
959: //find the nearest TextObjectRecord which holds comment's text and map it to its shapeId
960: while (it.hasNext()) {
961: rec = (Record) it.next();
962: if (rec instanceof TextObjectRecord) {
963: txshapes.put(new Integer(cmo
964: .getObjectId()), rec);
965: break;
966: }
967: }
968:
969: }
970: }
971: }
972: }
973: return comment;
974: }
975: }
|