1 /
55
56
61 package org.apache.poi.hssf.usermodel;
62
63 import org.apache.poi.hssf.model.Workbook;
64 import org.apache.poi.hssf.model.Sheet;
65 import org.apache.poi.hssf.model.FormulaParser;
66 import org.apache.poi.hssf.record.CellValueRecordInterface;
67 import org.apache.poi.hssf.record.Record;
68 import org.apache.poi.hssf.record.FormulaRecord;
69 import org.apache.poi.hssf.record.LabelSSTRecord;
70 import org.apache.poi.hssf.record.NumberRecord;
71 import org.apache.poi.hssf.record.BlankRecord;
72 import org.apache.poi.hssf.record.BoolErrRecord;
73 import org.apache.poi.hssf.record.ExtendedFormatRecord;
74 import org.apache.poi.hssf.record.aggregates.FormulaRecordAggregate;
75 import org.apache.poi.hssf.record.formula.Ptg;
76 import org.apache.poi.hssf.util.SheetReferences;
77
78
79
80 import java.util.Date;
81 import java.util.Calendar;
82
83
99
100 public class HSSFCell
101 {
102
103
108
109 public final static int CELL_TYPE_NUMERIC = 0;
110
111
116
117 public final static int CELL_TYPE_STRING = 1;
118
119
124
125 public final static int CELL_TYPE_FORMULA = 2;
126
127
132
133 public final static int CELL_TYPE_BLANK = 3;
134
135
140
141 public final static int CELL_TYPE_BOOLEAN = 4;
142
143
148
149 public final static int CELL_TYPE_ERROR = 5;
150 public final static short ENCODING_COMPRESSED_UNICODE = 0;
151 public final static short ENCODING_UTF_16 = 1;
152 private short cellNum;
153 private int cellType;
154 private HSSFCellStyle cellStyle;
155 private double cellValue;
156 private String stringValue;
157 private boolean booleanValue;
158 private byte errorValue;
159 private short encoding = ENCODING_COMPRESSED_UNICODE;
160 private Workbook book;
161 private Sheet sheet;
162
163 private int row;
164 private CellValueRecordInterface record;
165
166
182
183
184 protected HSSFCell(Workbook book, Sheet sheet, int row, short col)
185 {
186 checkBounds(col);
187 cellNum = col;
188 this.row = row;
189 cellStyle = null;
190 cellValue = 0;
191 stringValue = null;
192 booleanValue = false;
193 errorValue = ( byte ) 0;
194 this.book = book;
195 this.sheet = sheet;
196
197
198
199
200 setCellType(CELL_TYPE_BLANK, false);
201 ExtendedFormatRecord xf = book.getExFormatAt(0xf);
202
203 setCellStyle(new HSSFCellStyle(( short ) 0xf, xf));
204 }
205
206
221
222
223 protected HSSFCell(Workbook book, Sheet sheet, int row, short col,
224 int type)
225 {
226 checkBounds(col);
227 cellNum = col;
228 this.row = row;
229 cellType = type;
230 cellStyle = null;
231 cellValue = 0;
232 stringValue = null;
233 booleanValue = false;
234 errorValue = ( byte ) 0;
235 this.book = book;
236 this.sheet = sheet;
237 switch (type)
238 {
239
240 case CELL_TYPE_NUMERIC :
241 record = new NumberRecord();
242 (( NumberRecord ) record).setColumn(col);
243 (( NumberRecord ) record).setRow(row);
244 (( NumberRecord ) record).setValue(( short ) 0);
245 (( NumberRecord ) record).setXFIndex(( short ) 0);
246 break;
247
248 case CELL_TYPE_STRING :
249 record = new LabelSSTRecord();
250 (( LabelSSTRecord ) record).setColumn(col);
251 (( LabelSSTRecord ) record).setRow(row);
252 (( LabelSSTRecord ) record).setXFIndex(( short ) 0);
253 break;
254
255 case CELL_TYPE_BLANK :
256 record = new BlankRecord();
257 (( BlankRecord ) record).setColumn(col);
258 (( BlankRecord ) record).setRow(row);
259 (( BlankRecord ) record).setXFIndex(( short ) 0);
260 break;
261
262 case CELL_TYPE_FORMULA :
263 FormulaRecord formulaRecord = new FormulaRecord();
264 record = new FormulaRecordAggregate(formulaRecord,null);
265 formulaRecord.setColumn(col);
266 formulaRecord.setRow(row);
267 formulaRecord.setXFIndex(( short ) 0);
268 case CELL_TYPE_BOOLEAN :
269 record = new BoolErrRecord();
270 (( BoolErrRecord ) record).setColumn(col);
271 (( BoolErrRecord ) record).setRow(row);
272 (( BoolErrRecord ) record).setXFIndex(( short ) 0);
273 (( BoolErrRecord ) record).setValue(false);
274 break;
275
276 case CELL_TYPE_ERROR :
277 record = new BoolErrRecord();
278 (( BoolErrRecord ) record).setColumn(col);
279 (( BoolErrRecord ) record).setRow(row);
280 (( BoolErrRecord ) record).setXFIndex(( short ) 0);
281 (( BoolErrRecord ) record).setValue(( byte ) 0);
282 break;
283 }
284 ExtendedFormatRecord xf = book.getExFormatAt(0xf);
285
286 setCellStyle(new HSSFCellStyle(( short ) 0xf, xf));
287 }
288
289
297
298
299 protected HSSFCell(Workbook book, Sheet sheet, int row,
300 CellValueRecordInterface cval)
301 {
302 cellNum = cval.getColumn();
303 record = cval;
304 this.row = row;
305 cellType = determineType(cval);
306 cellStyle = null;
307 stringValue = null;
308 this.book = book;
309 this.sheet = sheet;
310 switch (cellType)
311 {
312
313 case CELL_TYPE_NUMERIC :
314 cellValue = (( NumberRecord ) cval).getValue();
315 break;
316
317 case CELL_TYPE_STRING :
318 stringValue =
319 book.getSSTString( ( (LabelSSTRecord ) cval).getSSTIndex());
320 break;
321
322 case CELL_TYPE_BLANK :
323 break;
324
325 case CELL_TYPE_FORMULA :
326 cellValue = (( FormulaRecordAggregate ) cval).getFormulaRecord().getValue();
327 break;
328
329 case CELL_TYPE_BOOLEAN :
330 booleanValue = (( BoolErrRecord ) cval).getBooleanValue();
331 break;
332
333 case CELL_TYPE_ERROR :
334 errorValue = (( BoolErrRecord ) cval).getErrorValue();
335 break;
336 }
337 ExtendedFormatRecord xf = book.getExFormatAt(cval.getXFIndex());
338
339 setCellStyle(new HSSFCellStyle(( short ) cval.getXFIndex(), xf));
340 }
341
342
345 private HSSFCell()
346 {
347 }
348
349
352 private int determineType(CellValueRecordInterface cval)
353 {
354 Record record = ( Record ) cval;
355 int sid = record.getSid();
356 int retval = 0;
357
358 switch (sid)
359 {
360
361 case NumberRecord.sid :
362 retval = HSSFCell.CELL_TYPE_NUMERIC;
363 break;
364
365 case BlankRecord.sid :
366 retval = HSSFCell.CELL_TYPE_BLANK;
367 break;
368
369 case LabelSSTRecord.sid :
370 retval = HSSFCell.CELL_TYPE_STRING;
371 break;
372
373 case FormulaRecordAggregate.sid :
374 retval = HSSFCell.CELL_TYPE_FORMULA;
375 break;
376
377 case BoolErrRecord.sid :
378 BoolErrRecord boolErrRecord = ( BoolErrRecord ) record;
379
380 retval = (boolErrRecord.isBoolean())
381 ? HSSFCell.CELL_TYPE_BOOLEAN
382 : HSSFCell.CELL_TYPE_ERROR;
383 break;
384 }
385 return retval;
386 }
387
388
392
393 public void setCellNum(short num)
394 {
395 cellNum = num;
396 record.setColumn(num);
397 }
398
399
403
404 public short getCellNum()
405 {
406 return cellNum;
407 }
408
409
419
420 public void setCellType(int cellType)
421 {
422 setCellType(cellType, true);
423 }
424
425
433
434 private void setCellType(int cellType, boolean setValue)
435 {
436
437
438
439
440
441
442 if (cellType > CELL_TYPE_ERROR)
443 {
444 throw new RuntimeException("I have no idea what type that is!");
445 }
446 switch (cellType)
447 {
448
449 case CELL_TYPE_FORMULA :
450 FormulaRecordAggregate frec = null;
451
452 if (cellType != this.cellType)
453 {
454 frec = new FormulaRecordAggregate(new FormulaRecord(),null);
455 }
456 else
457 {
458 frec = ( FormulaRecordAggregate ) record;
459 }
460 frec.setColumn(getCellNum());
461 if (setValue)
462 {
463 frec.getFormulaRecord().setValue(getNumericCellValue());
464 }
465 frec.setXFIndex(( short ) cellStyle.getIndex());
466 frec.setRow(row);
467 record = frec;
468 break;
469
470 case CELL_TYPE_NUMERIC :
471 NumberRecord nrec = null;
472
473 if (cellType != this.cellType)
474 {
475 nrec = new NumberRecord();
476 }
477 else
478 {
479 nrec = ( NumberRecord ) record;
480 }
481 nrec.setColumn(getCellNum());
482 if (setValue)
483 {
484 nrec.setValue(getNumericCellValue());
485 }
486 nrec.setXFIndex(( short ) cellStyle.getIndex());
487 nrec.setRow(row);
488 record = nrec;
489 break;
490
491 case CELL_TYPE_STRING :
492 LabelSSTRecord lrec = null;
493
494 if (cellType != this.cellType)
495 {
496 lrec = new LabelSSTRecord();
497 }
498 else
499 {
500 lrec = ( LabelSSTRecord ) record;
501 }
502 lrec.setColumn(getCellNum());
503 lrec.setRow(row);
504 lrec.setXFIndex(( short ) cellStyle.getIndex());
505 if (setValue)
506 {
507 if ((getStringCellValue() != null)
508 && (!getStringCellValue().equals("")))
509 {
510 int sst = 0;
511
512 if (encoding == ENCODING_COMPRESSED_UNICODE)
513 {
514 sst = book.addSSTString(getStringCellValue());
515 }
516 if (encoding == ENCODING_UTF_16)
517 {
518 sst = book.addSSTString(getStringCellValue(),
519 true);
520 }
521 lrec.setSSTIndex(sst);
522 }
523 }
524 record = lrec;
525 break;
526
527 case CELL_TYPE_BLANK :
528 BlankRecord brec = null;
529
530 if (cellType != this.cellType)
531 {
532 brec = new BlankRecord();
533 }
534 else
535 {
536 brec = ( BlankRecord ) record;
537 }
538 brec.setColumn(getCellNum());
539
540
541 if (cellStyle != null)
542 {
543 brec.setXFIndex(( short ) cellStyle.getIndex());
544 }
545 else
546 {
547 brec.setXFIndex(( short ) 0);
548 }
549 brec.setRow(row);
550 record = brec;
551 break;
552
553 case CELL_TYPE_BOOLEAN :
554 BoolErrRecord boolRec = null;
555
556 if (cellType != this.cellType)
557 {
558 boolRec = new BoolErrRecord();
559 }
560 else
561 {
562 boolRec = ( BoolErrRecord ) record;
563 }
564 boolRec.setColumn(getCellNum());
565 if (setValue)
566 {
567 boolRec.setValue(getBooleanCellValue());
568 }
569 boolRec.setXFIndex(( short ) cellStyle.getIndex());
570 boolRec.setRow(row);
571 record = boolRec;
572 break;
573
574 case CELL_TYPE_ERROR :
575 BoolErrRecord errRec = null;
576
577 if (cellType != this.cellType)
578 {
579 errRec = new BoolErrRecord();
580 }
581 else
582 {
583 errRec = ( BoolErrRecord ) record;
584 }
585 errRec.setColumn(getCellNum());
586 if (setValue)
587 {
588 errRec.setValue(getErrorCellValue());
589 }
590 errRec.setXFIndex(( short ) cellStyle.getIndex());
591 errRec.setRow(row);
592 record = errRec;
593 break;
594 }
595 if (cellType != this.cellType)
596 {
597 int loc = sheet.getLoc();
598
599 sheet.replaceValueRecord(record);
600 sheet.setLoc(loc);
601 }
602 this.cellType = cellType;
603 }
604
605
613
614 public int getCellType()
615 {
616 return cellType;
617 }
618
619
626 public void setCellValue(double value)
627 {
628 if ((cellType != CELL_TYPE_NUMERIC) && (cellType != CELL_TYPE_FORMULA))
629 {
630 setCellType(CELL_TYPE_NUMERIC, false);
631 }
632 (( NumberRecord ) record).setValue(value);
633 cellValue = value;
634 }
635
636
644 public void setCellValue(Date value)
645 {
646 setCellValue(HSSFDateUtil.getExcelDate(value));
647 }
648
649
657 public void setCellValue(Calendar value)
658 {
659 setCellValue(value.getTime());
660 }
661
662
671
672 public void setCellValue(String value)
673 {
674 if (value == null)
675 {
676 setCellType(CELL_TYPE_BLANK, false);
677 }
678 else
679 {
680 if ((cellType != CELL_TYPE_STRING ) && ( cellType != CELL_TYPE_FORMULA))
681 {
682 setCellType(CELL_TYPE_STRING, false);
683 }
684 int index = 0;
685
686 if (encoding == ENCODING_COMPRESSED_UNICODE)
687 {
688 index = book.addSSTString(value);
689 }
690 if (encoding == ENCODING_UTF_16)
691 {
692 index = book.addSSTString(value, true);
693 }
694 (( LabelSSTRecord ) record).setSSTIndex(index);
695 stringValue = value;
696 }
697 }
698
699 public void setCellFormula(String formula) {
700
701 if (formula==null) {
702 setCellType(CELL_TYPE_BLANK,false);
703 } else {
704 setCellType(CELL_TYPE_FORMULA,false);
705 FormulaRecordAggregate rec = (FormulaRecordAggregate) record;
706 rec.getFormulaRecord().setOptions(( short ) 2);
707 rec.getFormulaRecord().setValue(0);
708 rec.setXFIndex(( short ) 0x0f);
709 FormulaParser fp = new FormulaParser(formula+";",book);
710 fp.parse();
711 Ptg[] ptg = fp.getRPNPtg();
712 int size = 0;
713
714 for (int k = 0; k < ptg.length; k++) {
715 size += ptg[ k ].getSize();
716 rec.getFormulaRecord().pushExpressionToken(ptg[ k ]);
717 }
718 rec.getFormulaRecord().setExpressionLength(( short ) size);
719
720 }
721 }
722
723 public String getCellFormula() {
724
725 SheetReferences refs = book.getSheetReferences();
726 String retval = FormulaParser.toFormulaString(refs, ((FormulaRecordAggregate)record).getFormulaRecord().getParsedExpression());
727
728 return retval;
729 }
730
731
732
736
737 public double getNumericCellValue()
738 {
739 if (cellType == CELL_TYPE_BLANK)
740 {
741 return 0;
742 }
743 if (cellType == CELL_TYPE_STRING)
744 {
745 throw new NumberFormatException(
746 "You cannot get a numeric value from a String based cell");
747 }
748 if (cellType == CELL_TYPE_BOOLEAN)
749 {
750 throw new NumberFormatException(
751 "You cannot get a numeric value from a boolean cell");
752 }
753 if (cellType == CELL_TYPE_ERROR)
754 {
755 throw new NumberFormatException(
756 "You cannot get a numeric value from an error cell");
757 }
758 return cellValue;
759 }
760
761
765 public Date getDateCellValue()
766 {
767 if (cellType == CELL_TYPE_BLANK)
768 {
769 return null;
770 }
771 if (cellType == CELL_TYPE_STRING)
772 {
773 throw new NumberFormatException(
774 "You cannot get a date value from a String based cell");
775 }
776 if (cellType == CELL_TYPE_BOOLEAN)
777 {
778 throw new NumberFormatException(
779 "You cannot get a date value from a boolean cell");
780 }
781 if (cellType == CELL_TYPE_ERROR)
782 {
783 throw new NumberFormatException(
784 "You cannot get a date value from an error cell");
785 }
786 return HSSFDateUtil.getJavaDate(cellValue);
787 }
788
789
793
794 public String getStringCellValue()
795 {
796 if (cellType == CELL_TYPE_BLANK)
797 {
798 return "";
799 }
800 if (cellType == CELL_TYPE_NUMERIC)
801 {
802 throw new NumberFormatException(
803 "You cannot get a string value from a numeric cell");
804 }
805 if (cellType == CELL_TYPE_BOOLEAN)
806 {
807 throw new NumberFormatException(
808 "You cannot get a string value from a boolean cell");
809 }
810 if (cellType == CELL_TYPE_ERROR)
811 {
812 throw new NumberFormatException(
813 "You cannot get a string value from an error cell");
814 }
815 return stringValue;
816 }
817
818
825
826 public void setCellValue(boolean value)
827 {
828 if ((cellType != CELL_TYPE_BOOLEAN ) && ( cellType != CELL_TYPE_FORMULA))
829 {
830 setCellType(CELL_TYPE_BOOLEAN, false);
831 }
832 (( BoolErrRecord ) record).setValue(value);
833 booleanValue = value;
834 }
835
836
844
845 public void setCellErrorValue(byte value)
846 {
847 if ((cellType != CELL_TYPE_ERROR) && (cellType != CELL_TYPE_FORMULA))
848 {
849 setCellType(CELL_TYPE_ERROR, false);
850 }
851 (( BoolErrRecord ) record).setValue(value);
852 errorValue = value;
853 }
854
855
859
860 public boolean getBooleanCellValue()
861 {
862 if (cellType == CELL_TYPE_BOOLEAN)
863 {
864 return booleanValue;
865 }
866 if (cellType == CELL_TYPE_BLANK)
867 {
868 return false;
869 }
870 throw new NumberFormatException(
871 "You cannot get a boolean value from a non-boolean cell");
872 }
873
874
878
879 public byte getErrorCellValue()
880 {
881 if (cellType == CELL_TYPE_ERROR)
882 {
883 return errorValue;
884 }
885 if (cellType == CELL_TYPE_BLANK)
886 {
887 return ( byte ) 0;
888 }
889 throw new NumberFormatException(
890 "You cannot get an error value from a non-error cell");
891 }
892
893
901
902 public void setCellStyle(HSSFCellStyle style)
903 {
904 cellStyle = style;
905 record.setXFIndex(style.getIndex());
906 }
907
908
913
914 public HSSFCellStyle getCellStyle()
915 {
916 return cellStyle;
917 }
918
919
927
928 public short getEncoding()
929 {
930 return encoding;
931 }
932
933
941
942 public void setEncoding(short encoding)
943 {
944 this.encoding = encoding;
945 }
946
947
952
953 protected CellValueRecordInterface getCellValueRecord()
954 {
955 return record;
956 }
957
958
961 private void checkBounds(int cellNum) {
962 if (cellNum > 255) {
963 throw new RuntimeException("You cannot have more than 255 columns "+
964 "in a given row (IV). Because Excel can't handle it");
965 }
966 else if (cellNum < 0) {
967 throw new RuntimeException("You cannot reference columns with an index of less then 0.");
968 }
969 }
970 }
971