1 /* ====================================================================
2 Licensed to the Apache Software Foundation (ASF) under one or more
3 contributor license agreements. See the NOTICE file distributed with
4 this work for additional information regarding copyright ownership.
5 The ASF licenses this file to You under the Apache License, Version 2.0
6 (the "License"); you may not use this file except in compliance with
7 the License. You may obtain a copy of the License at
8
9 http://www.apache.org/licenses/LICENSE-2.0
10
11 Unless required by applicable law or agreed to in writing, software
12 distributed under the License is distributed on an "AS IS" BASIS,
13 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14 See the License for the specific language governing permissions and
15 limitations under the License.
16 ==================================================================== */
17
18
19
20 /*
21 * Cell.java
22 *
23 * Created on September 30, 2001, 3:46 PM
24 */
25 package org.apache.poi.hssf.usermodel;
26
27 import java.text.DateFormat;
28 import java.text.SimpleDateFormat;
29 import java.util.Calendar;
30 import java.util.Date;
31 import java.util.HashMap;
32 import java.util.Iterator;
33
34 import org.apache.poi.hssf.model.FormulaParser;
35 import org.apache.poi.hssf.model.Sheet;
36 import org.apache.poi.hssf.model.Workbook;
37 import org.apache.poi.hssf.record;
38 import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
39 import org.apache.poi.hssf.record.formula.Ptg;
40
41 /**
42 * High level representation of a cell in a row of a spreadsheet.
43 * Cells can be numeric, formula-based or string-based (text). The cell type
44 * specifies this. String cells cannot conatin numbers and numeric cells cannot
45 * contain strings (at least according to our model). Client apps should do the
46 * conversions themselves. Formula cells have the formula string, as well as
47 * the formula result, which can be numeric or string.
48 * <p>
49 * Cells should have their number (0 based) before being added to a row. Only
50 * cells that have values should be added.
51 * <p>
52 *
53 * @author Andrew C. Oliver (acoliver at apache dot org)
54 * @author Dan Sherman (dsherman at isisph.com)
55 * @author Brian Sanders (kestrel at burdell dot org) Active Cell support
56 * @author Yegor Kozlov cell comments support
57 * @version 1.0-pre
58 */
59
60 public class HSSFCell
61 {
62
63 /**
64 * Numeric Cell type (0)
65 * @see #setCellType(int)
66 * @see #getCellType()
67 */
68
69 public final static int CELL_TYPE_NUMERIC = 0;
70
71 /**
72 * String Cell type (1)
73 * @see #setCellType(int)
74 * @see #getCellType()
75 */
76
77 public final static int CELL_TYPE_STRING = 1;
78
79 /**
80 * Formula Cell type (2)
81 * @see #setCellType(int)
82 * @see #getCellType()
83 */
84
85 public final static int CELL_TYPE_FORMULA = 2;
86
87 /**
88 * Blank Cell type (3)
89 * @see #setCellType(int)
90 * @see #getCellType()
91 */
92
93 public final static int CELL_TYPE_BLANK = 3;
94
95 /**
96 * Boolean Cell type (4)
97 * @see #setCellType(int)
98 * @see #getCellType()
99 */
100
101 public final static int CELL_TYPE_BOOLEAN = 4;
102
103 /**
104 * Error Cell type (5)
105 * @see #setCellType(int)
106 * @see #getCellType()
107 */
108
109 public final static int CELL_TYPE_ERROR = 5;
110 public final static short ENCODING_UNCHANGED = -1;
111 public final static short ENCODING_COMPRESSED_UNICODE = 0;
112 public final static short ENCODING_UTF_16 = 1;
113 private int cellType;
114 private HSSFRichTextString stringValue;
115 private short encoding = ENCODING_UNCHANGED;
116 private HSSFWorkbook book;
117 private Sheet sheet;
118 private CellValueRecordInterface record;
119 private HSSFComment comment;
120
121 /**
122 * Creates new Cell - Should only be called by HSSFRow. This creates a cell
123 * from scratch.
124 * <p>
125 * When the cell is initially created it is set to CELL_TYPE_BLANK. Cell types
126 * can be changed/overwritten by calling setCellValue with the appropriate
127 * type as a parameter although conversions from one type to another may be
128 * prohibited.
129 *
130 * @param book - Workbook record of the workbook containing this cell
131 * @param sheet - Sheet record of the sheet containing this cell
132 * @param row - the row of this cell
133 * @param col - the column for this cell
134 *
135 * @see org.apache.poi.hssf.usermodel.HSSFRow#createCell(short)
136 */
137
138 //protected HSSFCell(Workbook book, Sheet sheet, short row, short col)
139 protected HSSFCell(HSSFWorkbook book, Sheet sheet, int row, short col)
140 {
141 checkBounds(col);
142 stringValue = null;
143 this.book = book;
144 this.sheet = sheet;
145
146 // Relying on the fact that by default the cellType is set to 0 which
147 // is different to CELL_TYPE_BLANK hence the following method call correctly
148 // creates a new blank cell.
149 short xfindex = sheet.getXFIndexForColAt(col);
150 setCellType(CELL_TYPE_BLANK, false, row, col,xfindex);
151 }
152
153 /**
154 * Creates new Cell - Should only be called by HSSFRow. This creates a cell
155 * from scratch.
156 *
157 * @param book - Workbook record of the workbook containing this cell
158 * @param sheet - Sheet record of the sheet containing this cell
159 * @param row - the row of this cell
160 * @param col - the column for this cell
161 * @param type - CELL_TYPE_NUMERIC, CELL_TYPE_STRING, CELL_TYPE_FORMULA, CELL_TYPE_BLANK,
162 * CELL_TYPE_BOOLEAN, CELL_TYPE_ERROR
163 * Type of cell
164 * @see org.apache.poi.hssf.usermodel.HSSFRow#createCell(short,int)
165 */
166
167 //protected HSSFCell(Workbook book, Sheet sheet, short row, short col,
168 protected HSSFCell(HSSFWorkbook book, Sheet sheet, int row, short col,
169 int type)
170 {
171 checkBounds(col);
172 cellType = -1; // Force 'setCellType' to create a first Record
173 stringValue = null;
174 this.book = book;
175 this.sheet = sheet;
176
177 short xfindex = sheet.getXFIndexForColAt(col);
178 setCellType(type,false,row,col,xfindex);
179 }
180
181 /**
182 * Creates an HSSFCell from a CellValueRecordInterface. HSSFSheet uses this when
183 * reading in cells from an existing sheet.
184 *
185 * @param book - Workbook record of the workbook containing this cell
186 * @param sheet - Sheet record of the sheet containing this cell
187 * @param cval - the Cell Value Record we wish to represent
188 */
189
190 //protected HSSFCell(Workbook book, Sheet sheet, short row,
191 protected HSSFCell(HSSFWorkbook book, Sheet sheet, int row,
192 CellValueRecordInterface cval)
193 {
194 record = cval;
195 cellType = determineType(cval);
196 stringValue = null;
197 this.book = book;
198 this.sheet = sheet;
199 switch (cellType)
200 {
201 case CELL_TYPE_STRING :
202 stringValue = new HSSFRichTextString(book.getWorkbook(), (LabelSSTRecord ) cval);
203 break;
204
205 case CELL_TYPE_BLANK :
206 break;
207
208 case CELL_TYPE_FORMULA :
209 stringValue=new HSSFRichTextString(((FormulaRecordAggregate) cval).getStringValue());
210 break;
211 }
212 ExtendedFormatRecord xf = book.getWorkbook().getExFormatAt(cval.getXFIndex());
213
214 setCellStyle(new HSSFCellStyle(( short ) cval.getXFIndex(), xf, book));
215 }
216
217 /**
218 * private constructor to prevent blank construction
219 */
220 private HSSFCell()
221 {
222 }
223
224 /**
225 * used internally -- given a cell value record, figure out its type
226 */
227 private int determineType(CellValueRecordInterface cval)
228 {
229 Record record = ( Record ) cval;
230 int sid = record.getSid();
231 int retval = 0;
232
233 switch (sid)
234 {
235
236 case NumberRecord.sid :
237 retval = HSSFCell.CELL_TYPE_NUMERIC;
238 break;
239
240 case BlankRecord.sid :
241 retval = HSSFCell.CELL_TYPE_BLANK;
242 break;
243
244 case LabelSSTRecord.sid :
245 retval = HSSFCell.CELL_TYPE_STRING;
246 break;
247
248 case FormulaRecordAggregate.sid :
249 retval = HSSFCell.CELL_TYPE_FORMULA;
250 break;
251
252 case BoolErrRecord.sid :
253 BoolErrRecord boolErrRecord = ( BoolErrRecord ) record;
254
255 retval = (boolErrRecord.isBoolean())
256 ? HSSFCell.CELL_TYPE_BOOLEAN
257 : HSSFCell.CELL_TYPE_ERROR;
258 break;
259 }
260 return retval;
261 }
262
263 /**
264 * Returns the Workbook that this Cell is bound to
265 * @return
266 */
267 protected Workbook getBoundWorkbook() {
268 return book.getWorkbook();
269 }
270
271 /**
272 * Set the cell's number within the row (0 based).
273 * @param num short the cell number
274 * @deprecated Doesn't update the row's idea of what cell this is, use {@link HSSFRow#moveCell(HSSFCell, short)} instead
275 */
276 public void setCellNum(short num)
277 {
278 record.setColumn(num);
279 }
280
281 /**
282 * Updates the cell record's idea of what
283 * column it belongs in (0 based)
284 * @param num the new cell number
285 */
286 protected void updateCellNum(short num)
287 {
288 record.setColumn(num);
289 }
290
291 /**
292 * get the cell's number within the row
293 * @return short reperesenting the column number (logical!)
294 */
295
296 public short getCellNum()
297 {
298 return record.getColumn();
299 }
300
301 /**
302 * set the cells type (numeric, formula or string)
303 * @see #CELL_TYPE_NUMERIC
304 * @see #CELL_TYPE_STRING
305 * @see #CELL_TYPE_FORMULA
306 * @see #CELL_TYPE_BLANK
307 * @see #CELL_TYPE_BOOLEAN
308 * @see #CELL_TYPE_ERROR
309 */
310
311 public void setCellType(int cellType)
312 {
313 int row=record.getRow();
314 short col=record.getColumn();
315 short styleIndex=record.getXFIndex();
316 setCellType(cellType, true, row, col, styleIndex);
317 }
318
319 /**
320 * sets the cell type. The setValue flag indicates whether to bother about
321 * trying to preserve the current value in the new record if one is created.
322 * <p>
323 * The @see #setCellValue method will call this method with false in setValue
324 * since it will overwrite the cell value later
325 *
326 */
327
328 private void setCellType(int cellType, boolean setValue, int row,short col, short styleIndex)
329 {
330
331 // if (cellType == CELL_TYPE_FORMULA)
332 // {
333 // throw new RuntimeException(
334 // "Formulas have not been implemented in this release");
335 // }
336 if (cellType > CELL_TYPE_ERROR)
337 {
338 throw new RuntimeException("I have no idea what type that is!");
339 }
340 switch (cellType)
341 {
342
343 case CELL_TYPE_FORMULA :
344 FormulaRecordAggregate frec = null;
345
346 if (cellType != this.cellType)
347 {
348 frec = new FormulaRecordAggregate(new FormulaRecord(),null);
349 }
350 else
351 {
352 frec = ( FormulaRecordAggregate ) record;
353 }
354 frec.setColumn(col);
355 if (setValue)
356 {
357 frec.getFormulaRecord().setValue(getNumericCellValue());
358 }
359 frec.setXFIndex(styleIndex);
360 frec.setRow(row);
361 record = frec;
362 break;
363
364 case CELL_TYPE_NUMERIC :
365 NumberRecord nrec = null;
366
367 if (cellType != this.cellType)
368 {
369 nrec = new NumberRecord();
370 }
371 else
372 {
373 nrec = ( NumberRecord ) record;
374 }
375 nrec.setColumn(col);
376 if (setValue)
377 {
378 nrec.setValue(getNumericCellValue());
379 }
380 nrec.setXFIndex(styleIndex);
381 nrec.setRow(row);
382 record = nrec;
383 break;
384
385 case CELL_TYPE_STRING :
386 LabelSSTRecord lrec = null;
387
388 if (cellType != this.cellType)
389 {
390 lrec = new LabelSSTRecord();
391 }
392 else
393 {
394 lrec = ( LabelSSTRecord ) record;
395 }
396 lrec.setColumn(col);
397 lrec.setRow(row);
398 lrec.setXFIndex(styleIndex);
399 if (setValue)
400 {
401 if ((getStringCellValue() != null)
402 && (!getStringCellValue().equals("")))
403 {
404 int sst = 0;
405
406 UnicodeString str = getRichStringCellValue().getUnicodeString();
407 //jmh if (encoding == ENCODING_COMPRESSED_UNICODE)
408 //jmh {
409 // jmh str.setCompressedUnicode();
410 // jmh } else if (encoding == ENCODING_UTF_16)
411 // jmh {
412 // jmh str.setUncompressedUnicode();
413 // jmh }
414 sst = book.getWorkbook().addSSTString(str);
415 lrec.setSSTIndex(sst);
416 getRichStringCellValue().setUnicodeString(book.getWorkbook().getSSTString(sst));
417 }
418 }
419 record = lrec;
420 break;
421
422 case CELL_TYPE_BLANK :
423 BlankRecord brec = null;
424
425 if (cellType != this.cellType)
426 {
427 brec = new BlankRecord();
428 }
429 else
430 {
431 brec = ( BlankRecord ) record;
432 }
433 brec.setColumn(col);
434
435 // During construction the cellStyle may be null for a Blank cell.
436 brec.setXFIndex(styleIndex);
437 brec.setRow(row);
438 record = brec;
439 break;
440
441 case CELL_TYPE_BOOLEAN :
442 BoolErrRecord boolRec = null;
443
444 if (cellType != this.cellType)
445 {
446 boolRec = new BoolErrRecord();
447 }
448 else
449 {
450 boolRec = ( BoolErrRecord ) record;
451 }
452 boolRec.setColumn(col);
453 if (setValue)
454 {
455 boolRec.setValue(convertCellValueToBoolean());
456 }
457 boolRec.setXFIndex(styleIndex);
458 boolRec.setRow(row);
459 record = boolRec;
460 break;
461
462 case CELL_TYPE_ERROR :
463 BoolErrRecord errRec = null;
464
465 if (cellType != this.cellType)
466 {
467 errRec = new BoolErrRecord();
468 }
469 else
470 {
471 errRec = ( BoolErrRecord ) record;
472 }
473 errRec.setColumn(col);
474 if (setValue)
475 {
476 errRec.setValue(getErrorCellValue());
477 }
478 errRec.setXFIndex(styleIndex);
479 errRec.setRow(row);
480 record = errRec;
481 break;
482 }
483 if (cellType != this.cellType &&
484 this.cellType!=-1 ) // Special Value to indicate an uninitialized Cell
485 {
486 int loc = sheet.getLoc();
487
488 sheet.replaceValueRecord(record);
489 sheet.setLoc(loc);
490 }
491 this.cellType = cellType;
492 }
493
494 /**
495 * get the cells type (numeric, formula or string)
496 * @see #CELL_TYPE_STRING
497 * @see #CELL_TYPE_NUMERIC
498 * @see #CELL_TYPE_FORMULA
499 * @see #CELL_TYPE_BOOLEAN
500 * @see #CELL_TYPE_ERROR
501 */
502
503 public int getCellType()
504 {
505 return cellType;
506 }
507
508 /**
509 * set a numeric value for the cell
510 *
511 * @param value the numeric value to set this cell to. For formulas we'll set the
512 * precalculated value, for numerics we'll set its value. For other types we
513 * will change the cell to a numeric cell and set its value.
514 */
515 public void setCellValue(double value)
516 {
517 int row=record.getRow();
518 short col=record.getColumn();
519 short styleIndex=record.getXFIndex();
520 if ((cellType != CELL_TYPE_NUMERIC) && (cellType != CELL_TYPE_FORMULA))
521 {
522 setCellType(CELL_TYPE_NUMERIC, false, row, col, styleIndex);
523 }
524
525 // Save into the apropriate record
526 if(record instanceof FormulaRecordAggregate) {
527 (( FormulaRecordAggregate ) record).getFormulaRecord().setValue(value);
528 } else {
529 (( NumberRecord ) record).setValue(value);
530 }
531 }
532
533 /**
534 * set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
535 * a date.
536 *
537 * @param value the date value to set this cell to. For formulas we'll set the
538 * precalculated value, for numerics we'll set its value. For other types we
539 * will change the cell to a numeric cell and set its value.
540 */
541 public void setCellValue(Date value)
542 {
543 setCellValue(HSSFDateUtil.getExcelDate(value, this.book.getWorkbook().isUsing1904DateWindowing()));
544 }
545
546 /**
547 * set a date value for the cell. Excel treats dates as numeric so you will need to format the cell as
548 * a date.
549 *
550 * This will set the cell value based on the Calendar's timezone. As Excel
551 * does not support timezones this means that both 20:00+03:00 and
552 * 20:00-03:00 will be reported as the same value (20:00) even that there
553 * are 6 hours difference between the two times. This difference can be
554 * preserved by using <code>setCellValue(value.getTime())</code> which will
555 * automatically shift the times to the default timezone.
556 *
557 * @param value the date value to set this cell to. For formulas we'll set the
558 * precalculated value, for numerics we'll set its value. For othertypes we
559 * will change the cell to a numeric cell and set its value.
560 */
561 public void setCellValue(Calendar value)
562 {
563 setCellValue( HSSFDateUtil.getExcelDate(value, this.book.getWorkbook().isUsing1904DateWindowing()) );
564 }
565
566 /**
567 * set a string value for the cell. Please note that if you are using
568 * full 16 bit unicode you should call <code>setEncoding()</code> first.
569 *
570 * @param value value to set the cell to. For formulas we'll set the formula
571 * string, for String cells we'll set its value. For other types we will
572 * change the cell to a string cell and set its value.
573 * If value is null then we will change the cell to a Blank cell.
574 * @deprecated Use setCellValue(HSSFRichTextString) instead.
575 */
576
577 public void setCellValue(String value)
578 {
579 HSSFRichTextString str = new HSSFRichTextString(value);
580 setCellValue(str);
581 }
582
583 /**
584 * set a string value for the cell. Please note that if you are using
585 * full 16 bit unicode you should call <code>setEncoding()</code> first.
586 *
587 * @param value value to set the cell to. For formulas we'll set the formula
588 * string, for String cells we'll set its value. For other types we will
589 * change the cell to a string cell and set its value.
590 * If value is null then we will change the cell to a Blank cell.
591 */
592 public void setCellValue(HSSFRichTextString value) {
593 int row=record.getRow();
594 short col=record.getColumn();
595 short styleIndex=record.getXFIndex();
596 if (value == null) {
597 setCellType(CELL_TYPE_BLANK, false, row, col, styleIndex);
598 return;
599 }
600 if (cellType == CELL_TYPE_FORMULA) {
601 // Set the 'pre-evaluated result' for the formula
602 // note - formulas do not preserve text formatting.
603 FormulaRecordAggregate fr = (FormulaRecordAggregate) record;
604 // must make new sr because fr.getStringRecord() may be null
605 StringRecord sr = new StringRecord();
606 sr.setString(value.getString()); // looses format
607 fr.setStringRecord(sr);
608 return;
609 }
610
611 if (cellType != CELL_TYPE_STRING) {
612 setCellType(CELL_TYPE_STRING, false, row, col, styleIndex);
613 }
614 int index = 0;
615
616 UnicodeString str = value.getUnicodeString();
617 index = book.getWorkbook().addSSTString(str);
618 (( LabelSSTRecord ) record).setSSTIndex(index);
619 stringValue = value;
620 stringValue.setWorkbookReferences(book.getWorkbook(), (( LabelSSTRecord ) record));
621 stringValue.setUnicodeString(book.getWorkbook().getSSTString(index));
622 }
623
624 public void setCellFormula(String formula) {
625 int row=record.getRow();
626 short col=record.getColumn();
627 short styleIndex=record.getXFIndex();
628 //Workbook.currentBook=book;
629 if (formula==null) {
630 setCellType(CELL_TYPE_BLANK,false,row,col,styleIndex);
631 } else {
632 setCellType(CELL_TYPE_FORMULA,false,row,col,styleIndex);
633 FormulaRecordAggregate rec = (FormulaRecordAggregate) record;
634 FormulaRecord frec = rec.getFormulaRecord();
635 frec.setOptions(( short ) 2);
636 frec.setValue(0);
637
638 //only set to default if there is no extended format index already set
639 if (rec.getXFIndex() == (short)0) rec.setXFIndex(( short ) 0x0f);
640 FormulaParser fp = new FormulaParser(formula, book);
641 fp.parse();
642 Ptg[] ptg = fp.getRPNPtg();
643 int size = 0;
644
645 // clear the Ptg Stack
646 for (int i=0, iSize=frec.getNumberOfExpressionTokens(); i<iSize; i++) {
647 frec.popExpressionToken();
648 }
649
650 // fill the Ptg Stack with Ptgs of new formula
651 for (int k = 0; k < ptg.length; k++) {
652 size += ptg[ k ].getSize();
653 frec.pushExpressionToken(ptg[ k ]);
654 }
655 rec.getFormulaRecord().setExpressionLength(( short ) size);
656 //Workbook.currentBook = null;
657 }
658 }
659
660 public String getCellFormula() {
661 //Workbook.currentBook=book;
662 String retval = FormulaParser.toFormulaString(book, ((FormulaRecordAggregate)record).getFormulaRecord().getParsedExpression());
663 //Workbook.currentBook=null;
664 return retval;
665 }
666
667
668 /**
669 * get the value of the cell as a number. For strings we throw an exception.
670 * For blank cells we return a 0.
671 */
672
673 public double getNumericCellValue()
674 {
675 if (cellType == CELL_TYPE_BLANK)
676 {
677 return 0;
678 }
679 if (cellType == CELL_TYPE_STRING)
680 {
681 throw new NumberFormatException(
682 "You cannot get a numeric value from a String based cell");
683 }
684 if (cellType == CELL_TYPE_BOOLEAN)
685 {
686 throw new NumberFormatException(
687 "You cannot get a numeric value from a boolean cell");
688 }
689 if (cellType == CELL_TYPE_ERROR)
690 {
691 throw new NumberFormatException(
692 "You cannot get a numeric value from an error cell");
693 }
694 if(cellType == CELL_TYPE_NUMERIC)
695 {
696 return ((NumberRecord)record).getValue();
697 }
698 if(cellType == CELL_TYPE_FORMULA)
699 {
700 return ((FormulaRecordAggregate)record).getFormulaRecord().getValue();
701 }
702 throw new NumberFormatException("Unknown Record Type in Cell:"+cellType);
703 }
704
705 /**
706 * get the value of the cell as a date. For strings we throw an exception.
707 * For blank cells we return a null.
708 */
709 public Date getDateCellValue()
710 {
711 if (cellType == CELL_TYPE_BLANK)
712 {
713 return null;
714 }
715 if (cellType == CELL_TYPE_STRING)
716 {
717 throw new NumberFormatException(
718 "You cannot get a date value from a String based cell");
719 }
720 if (cellType == CELL_TYPE_BOOLEAN)
721 {
722 throw new NumberFormatException(
723 "You cannot get a date value from a boolean cell");
724 }
725 if (cellType == CELL_TYPE_ERROR)
726 {
727 throw new NumberFormatException(
728 "You cannot get a date value from an error cell");
729 }
730 double value=this.getNumericCellValue();
731 if (book.getWorkbook().isUsing1904DateWindowing()) {
732 return HSSFDateUtil.getJavaDate(value,true);
733 }
734 else {
735 return HSSFDateUtil.getJavaDate(value,false);
736 }
737 }
738
739 /**
740 * get the value of the cell as a string - for numeric cells we throw an exception.
741 * For blank cells we return an empty string.
742 * For formulaCells that are not string Formulas, we return empty String
743 * @deprecated Use the HSSFRichTextString return
744 */
745
746 public String getStringCellValue()
747 {
748 HSSFRichTextString str = getRichStringCellValue();
749 return str.getString();
750 }
751
752 /**
753 * get the value of the cell as a string - for numeric cells we throw an exception.
754 * For blank cells we return an empty string.
755 * For formulaCells that are not string Formulas, we return empty String
756 */
757
758 public HSSFRichTextString getRichStringCellValue()
759 {
760 if (cellType == CELL_TYPE_BLANK)
761 {
762 return new HSSFRichTextString("");
763 }
764 if (cellType == CELL_TYPE_NUMERIC)
765 {
766 throw new NumberFormatException(
767 "You cannot get a string value from a numeric cell");
768 }
769 if (cellType == CELL_TYPE_BOOLEAN)
770 {
771 throw new NumberFormatException(
772 "You cannot get a string value from a boolean cell");
773 }
774 if (cellType == CELL_TYPE_ERROR)
775 {
776 throw new NumberFormatException(
777 "You cannot get a string value from an error cell");
778 }
779 if (cellType == CELL_TYPE_FORMULA)
780 {
781 if (stringValue==null) return new HSSFRichTextString("");
782 }
783 return stringValue;
784 }
785
786 /**
787 * set a boolean value for the cell
788 *
789 * @param value the boolean value to set this cell to. For formulas we'll set the
790 * precalculated value, for booleans we'll set its value. For other types we
791 * will change the cell to a boolean cell and set its value.
792 */
793
794 public void setCellValue(boolean value)
795 {
796 int row=record.getRow();
797 short col=record.getColumn();
798 short styleIndex=record.getXFIndex();
799 if ((cellType != CELL_TYPE_BOOLEAN ) && ( cellType != CELL_TYPE_FORMULA))
800 {
801 setCellType(CELL_TYPE_BOOLEAN, false, row, col, styleIndex);
802 }
803 (( BoolErrRecord ) record).setValue(value);
804 }
805
806 /**
807 * set a error value for the cell
808 *
809 * @param value the error value to set this cell to. For formulas we'll set the
810 * precalculated value ??? IS THIS RIGHT??? , for errors we'll set
811 * its value. For other types we will change the cell to an error
812 * cell and set its value.
813 */
814
815 public void setCellErrorValue(byte value)
816 {
817 int row=record.getRow();
818 short col=record.getColumn();
819 short styleIndex=record.getXFIndex();
820 if (cellType != CELL_TYPE_ERROR) {
821 setCellType(CELL_TYPE_ERROR, false, row, col, styleIndex);
822 }
823 (( BoolErrRecord ) record).setValue(value);
824 }
825 /**
826 * Chooses a new boolean value for the cell when its type is changing.<p/>
827 *
828 * Usually the caller is calling setCellType() with the intention of calling
829 * setCellValue(boolean) straight afterwards. This method only exists to give
830 * the cell a somewhat reasonable value until the setCellValue() call (if at all).
831 * TODO - perhaps a method like setCellTypeAndValue(int, Object) should be introduced to avoid this
832 */
833 private boolean convertCellValueToBoolean() {
834
835 switch (cellType) {
836 case CELL_TYPE_BOOLEAN:
837 return (( BoolErrRecord ) record).getBooleanValue();
838 case CELL_TYPE_STRING:
839 return Boolean.valueOf(((StringRecord)record).getString()).booleanValue();
840 case CELL_TYPE_NUMERIC:
841 return ((NumberRecord)record).getValue() != 0;
842
843 // All other cases convert to false
844 // These choices are not well justified.
845 case CELL_TYPE_FORMULA:
846 // should really evaluate, but HSSFCell can't call HSSFFormulaEvaluator
847 case CELL_TYPE_ERROR:
848 case CELL_TYPE_BLANK:
849 return false;
850 }
851 throw new RuntimeException("Unexpected cell type (" + cellType + ")");
852 }
853
854 /**
855 * get the value of the cell as a boolean. For strings, numbers, and errors, we throw an exception.
856 * For blank cells we return a false.
857 */
858
859 public boolean getBooleanCellValue()
860 {
861 if (cellType == CELL_TYPE_BOOLEAN)
862 {
863 return (( BoolErrRecord ) record).getBooleanValue();
864 }
865 if (cellType == CELL_TYPE_BLANK)
866 {
867 return false;
868 }
869 throw new NumberFormatException(
870 "You cannot get a boolean value from a non-boolean cell");
871 }
872
873 /**
874 * get the value of the cell as an error code. For strings, numbers, and booleans, we throw an exception.
875 * For blank cells we return a 0.
876 */
877
878 public byte getErrorCellValue()
879 {
880 if (cellType == CELL_TYPE_ERROR)
881 {
882 return (( BoolErrRecord ) record).getErrorValue();
883 }
884 if (cellType == CELL_TYPE_BLANK)
885 {
886 return ( byte ) 0;
887 }
888 throw new NumberFormatException(
889 "You cannot get an error value from a non-error cell");
890 }
891
892 /**
893 * set the style for the cell. The style should be an HSSFCellStyle created/retreived from
894 * the HSSFWorkbook.
895 *
896 * @param style reference contained in the workbook
897 * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#createCellStyle()
898 * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#getCellStyleAt(short)
899 */
900
901 public void setCellStyle(HSSFCellStyle style)
902 {
903 record.setXFIndex(style.getIndex());
904 }
905
906 /**
907 * get the style for the cell. This is a reference to a cell style contained in the workbook
908 * object.
909 * @see org.apache.poi.hssf.usermodel.HSSFWorkbook#getCellStyleAt(short)
910 */
911
912 public HSSFCellStyle getCellStyle()
913 {
914 short styleIndex=record.getXFIndex();
915 ExtendedFormatRecord xf = book.getWorkbook().getExFormatAt(styleIndex);
916 return new HSSFCellStyle(styleIndex, xf, book);
917 }
918
919 /**
920 * used for internationalization, currently -1 for unchanged, 0 for compressed unicode or 1 for 16-bit
921 *
922 * @see #ENCODING_UNCHANGED
923 * @see #ENCODING_COMPRESSED_UNICODE
924 * @see #ENCODING_UTF_16
925 *
926 * @return -1, 1 or 0 for unchanged, compressed or uncompressed (used only with String type)
927 *
928 * @deprecated As of 3-Jan-06 POI now automatically handles Unicode without forcing the encoding.
929 */
930 public short getEncoding()
931 {
932 return encoding;
933 }
934
935 /**
936 * set the encoding to either 8 or 16 bit. (US/UK use 8-bit, rest of the western world use 16bit)
937 *
938 * @see #ENCODING_UNCHANGED
939 * @see #ENCODING_COMPRESSED_UNICODE
940 * @see #ENCODING_UTF_16
941 *
942 * @param encoding either ENCODING_COMPRESSED_UNICODE (0) or ENCODING_UTF_16 (1)
943 * @deprecated As of 3-Jan-06 POI now automatically handles Unicode without forcing the encoding.
944 */
945
946 public void setEncoding(short encoding)
947 {
948 this.encoding = encoding;
949 }
950
951 /**
952 * Should only be used by HSSFSheet and friends. Returns the low level CellValueRecordInterface record
953 *
954 * @return CellValueRecordInterface representing the cell via the low level api.
955 */
956
957 protected CellValueRecordInterface getCellValueRecord()
958 {
959 return record;
960 }
961
962 /**
963 * @throws RuntimeException if the bounds are exceeded.
964 */
965 private void checkBounds(int cellNum) {
966 if (cellNum > 255) {
967 throw new RuntimeException("You cannot have more than 255 columns "+
968 "in a given row (IV). Because Excel can't handle it");
969 }
970 else if (cellNum < 0) {
971 throw new RuntimeException("You cannot reference columns with an index of less then 0.");
972 }
973 }
974
975 /**
976 * Sets this cell as the active cell for the worksheet
977 */
978 public void setAsActiveCell()
979 {
980 int row=record.getRow();
981 short col=record.getColumn();
982 this.sheet.setActiveCellRow(row);
983 this.sheet.setActiveCellCol(col);
984 }
985
986 /**
987 * Returns a string representation of the cell
988 *
989 * This method returns a simple representation,
990 * anthing more complex should be in user code, with
991 * knowledge of the semantics of the sheet being processed.
992 *
993 * Formula cells return the formula string,
994 * rather than the formula result.
995 * Dates are displayed in dd-MMM-yyyy format
996 * Errors are displayed as #ERR<errIdx>
997 */
998 public String toString() {
999 switch (getCellType()) {
1000 case CELL_TYPE_BLANK:
1001 return "";
1002 case CELL_TYPE_BOOLEAN:
1003 return getBooleanCellValue()?"TRUE":"FALSE";
1004 case CELL_TYPE_ERROR:
1005 return "#ERR"+getErrorCellValue();
1006 case CELL_TYPE_FORMULA:
1007 return getCellFormula();
1008 case CELL_TYPE_NUMERIC:
1009 //TODO apply the dataformat for this cell
1010 if (HSSFDateUtil.isCellDateFormatted(this)) {
1011 DateFormat sdf = new SimpleDateFormat("dd-MMM-yyyy");
1012 return sdf.format(getDateCellValue());
1013 }else {
1014 return getNumericCellValue() + "";
1015 }
1016 case CELL_TYPE_STRING:
1017 return getStringCellValue();
1018 default:
1019 return "Unknown Cell Type: " + getCellType();
1020 }
1021 }
1022
1023 /**
1024 * Assign a comment to this cell
1025 *
1026 * @param comment comment associated with this cell
1027 */
1028 public void setCellComment(HSSFComment comment){
1029 comment.setRow((short)record.getRow());
1030 comment.setColumn(record.getColumn());
1031 this.comment = comment;
1032 }
1033
1034 /**
1035 * Returns comment associated with this cell
1036 *
1037 * @return comment associated with this cell
1038 */
1039 public HSSFComment getCellComment(){
1040 if (comment == null) {
1041 comment = findCellComment(sheet, record.getRow(), record.getColumn());
1042 }
1043 return comment;
1044 }
1045
1046 /**
1047 * Cell comment finder.
1048 * Returns cell comment for the specified sheet, row and column.
1049 *
1050 * @return cell comment or <code>null</code> if not found
1051 */
1052 protected static HSSFComment findCellComment(Sheet sheet, int row, int column){
1053 HSSFComment comment = null;
1054 HashMap txshapes = new HashMap(); //map shapeId and TextObjectRecord
1055 for (Iterator it = sheet.getRecords().iterator(); it.hasNext(); ) {
1056 Record rec = ( Record ) it.next();
1057 if (rec instanceof NoteRecord){
1058 NoteRecord note = (NoteRecord)rec;
1059 if (note.getRow() == row && note.getColumn() == column){
1060 TextObjectRecord txo = (TextObjectRecord)txshapes.get(new Integer(note.getShapeId()));
1061 comment = new HSSFComment(note, txo);
1062 comment.setRow(note.getRow());
1063 comment.setColumn(note.getColumn());
1064 comment.setAuthor(note.getAuthor());
1065 comment.setVisible(note.getFlags() == NoteRecord.NOTE_VISIBLE);
1066 comment.setString(txo.getStr());
1067 break;
1068 }
1069 } else if (rec instanceof ObjRecord){
1070 ObjRecord obj = (ObjRecord)rec;
1071 SubRecord sub = (SubRecord)obj.getSubRecords().get(0);
1072 if (sub instanceof CommonObjectDataSubRecord){
1073 CommonObjectDataSubRecord cmo = (CommonObjectDataSubRecord)sub;
1074 if (cmo.getObjectType() == CommonObjectDataSubRecord.OBJECT_TYPE_COMMENT){
1075 //find the nearest TextObjectRecord which holds comment's text and map it to its shapeId
1076 while(it.hasNext()) {
1077 rec = ( Record ) it.next();
1078 if (rec instanceof TextObjectRecord) {
1079 txshapes.put(new Integer(cmo.getObjectId()), rec);
1080 break;
1081 }
1082 }
1083
1084 }
1085 }
1086 }
1087 }
1088 return comment;
1089 }
1090
1091 /**
1092 * Returns hyperlink associated with this cell
1093 *
1094 * @return hyperlink associated with this cell or null if not found
1095 */
1096 public HSSFHyperlink getHyperlink(){
1097 for (Iterator it = sheet.getRecords().iterator(); it.hasNext(); ) {
1098 Record rec = ( Record ) it.next();
1099 if (rec instanceof HyperlinkRecord){
1100 HyperlinkRecord link = (HyperlinkRecord)rec;
1101 if(link.getFirstColumn() == record.getColumn() && link.getFirstRow() == record.getRow()){
1102 return new HSSFHyperlink(link);
1103 }
1104 }
1105 }
1106 return null;
1107 }
1108
1109 /**
1110 * Assign a hypelrink to this cell
1111 *
1112 * @param link hypelrink associated with this cell
1113 */
1114 public void setHyperlink(HSSFHyperlink link){
1115 link.setFirstRow(record.getRow());
1116 link.setLastRow(record.getRow());
1117 link.setFirstColumn(record.getColumn());
1118 link.setLastColumn(record.getColumn());
1119
1120 switch(link.getType()){
1121 case HSSFHyperlink.LINK_EMAIL:
1122 case HSSFHyperlink.LINK_URL:
1123 link.setLabel("url");
1124 break;
1125 case HSSFHyperlink.LINK_FILE:
1126 link.setLabel("file");
1127 break;
1128 case HSSFHyperlink.LINK_DOCUMENT:
1129 link.setLabel("place");
1130 break;
1131 }
1132
1133 int eofLoc = sheet.findFirstRecordLocBySid( EOFRecord.sid );
1134 sheet.getRecords().add( eofLoc, link.record );
1135 }
1136 }
Save This Page