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
101
102 public class HSSFCell
103 {
104
105
110
111 public final static int CELL_TYPE_NUMERIC = 0;
112
113
118
119 public final static int CELL_TYPE_STRING = 1;
120
121
126
127 public final static int CELL_TYPE_FORMULA = 2;
128
129
134
135 public final static int CELL_TYPE_BLANK = 3;
136
137
142
143 public final static int CELL_TYPE_BOOLEAN = 4;
144
145
150
151 public final static int CELL_TYPE_ERROR = 5;
152 public final static short ENCODING_COMPRESSED_UNICODE = 0;
153 public final static short ENCODING_UTF_16 = 1;
154 private short cellNum;
155 private int cellType;
156 private HSSFCellStyle cellStyle;
157 private double cellValue;
158 private String stringValue;
159 private boolean booleanValue;
160 private byte errorValue;
161 private short encoding = ENCODING_COMPRESSED_UNICODE;
162 private Workbook book;
163 private Sheet sheet;
164
165 private int row;
166 private CellValueRecordInterface record;
167
168
184
185
186 protected HSSFCell(Workbook book, Sheet sheet, int row, short col)
187 {
188 checkBounds(col);
189 cellNum = col;
190 this.row = row;
191 cellStyle = null;
192 cellValue = 0;
193 stringValue = null;
194 booleanValue = false;
195 errorValue = ( byte ) 0;
196 this.book = book;
197 this.sheet = sheet;
198
199
200
201
202 setCellType(CELL_TYPE_BLANK, false);
203 ExtendedFormatRecord xf = book.getExFormatAt(0xf);
204
205 setCellStyle(new HSSFCellStyle(( short ) 0xf, xf));
206 }
207
208
223
224
225 protected HSSFCell(Workbook book, Sheet sheet, int row, short col,
226 int type)
227 {
228 checkBounds(col);
229 cellNum = col;
230 this.row = row;
231 cellType = type;
232 cellStyle = null;
233 cellValue = 0;
234 stringValue = null;
235 booleanValue = false;
236 errorValue = ( byte ) 0;
237 this.book = book;
238 this.sheet = sheet;
239 switch (type)
240 {
241
242 case CELL_TYPE_NUMERIC :
243 record = new NumberRecord();
244 (( NumberRecord ) record).setColumn(col);
245 (( NumberRecord ) record).setRow(row);
246 (( NumberRecord ) record).setValue(( short ) 0);
247 (( NumberRecord ) record).setXFIndex(( short ) 0);
248 break;
249
250 case CELL_TYPE_STRING :
251 record = new LabelSSTRecord();
252 (( LabelSSTRecord ) record).setColumn(col);
253 (( LabelSSTRecord ) record).setRow(row);
254 (( LabelSSTRecord ) record).setXFIndex(( short ) 0);
255 break;
256
257 case CELL_TYPE_BLANK :
258 record = new BlankRecord();
259 (( BlankRecord ) record).setColumn(col);
260 (( BlankRecord ) record).setRow(row);
261 (( BlankRecord ) record).setXFIndex(( short ) 0);
262 break;
263
264 case CELL_TYPE_FORMULA :
265 FormulaRecord formulaRecord = new FormulaRecord();
266 record = new FormulaRecordAggregate(formulaRecord,null);
267 formulaRecord.setColumn(col);
268 formulaRecord.setRow(row);
269 formulaRecord.setXFIndex(( short ) 0);
270 case CELL_TYPE_BOOLEAN :
271 record = new BoolErrRecord();
272 (( BoolErrRecord ) record).setColumn(col);
273 (( BoolErrRecord ) record).setRow(row);
274 (( BoolErrRecord ) record).setXFIndex(( short ) 0);
275 (( BoolErrRecord ) record).setValue(false);
276 break;
277
278 case CELL_TYPE_ERROR :
279 record = new BoolErrRecord();
280 (( BoolErrRecord ) record).setColumn(col);
281 (( BoolErrRecord ) record).setRow(row);
282 (( BoolErrRecord ) record).setXFIndex(( short ) 0);
283 (( BoolErrRecord ) record).setValue(( byte ) 0);
284 break;
285 }
286 ExtendedFormatRecord xf = book.getExFormatAt(0xf);
287
288 setCellStyle(new HSSFCellStyle(( short ) 0xf, xf));
289 }
290
291
299
300
301 protected HSSFCell(Workbook book, Sheet sheet, int row,
302 CellValueRecordInterface cval)
303 {
304 cellNum = cval.getColumn();
305 record = cval;
306 this.row = row;
307 cellType = determineType(cval);
308 cellStyle = null;
309 stringValue = null;
310 this.book = book;
311 this.sheet = sheet;
312 switch (cellType)
313 {
314
315 case CELL_TYPE_NUMERIC :
316 cellValue = (( NumberRecord ) cval).getValue();
317 break;
318
319 case CELL_TYPE_STRING :
320 stringValue =
321 book.getSSTString( ( (LabelSSTRecord ) cval).getSSTIndex());
322 break;
323
324 case CELL_TYPE_BLANK :
325 break;
326
327 case CELL_TYPE_FORMULA :
328 cellValue = (( FormulaRecordAggregate ) cval).getFormulaRecord().getValue();
329 break;
330
331 case CELL_TYPE_BOOLEAN :
332 booleanValue = (( BoolErrRecord ) cval).getBooleanValue();
333 break;
334
335 case CELL_TYPE_ERROR :
336 errorValue = (( BoolErrRecord ) cval).getErrorValue();
337 break;
338 }
339 ExtendedFormatRecord xf = book.getExFormatAt(cval.getXFIndex());
340
341 setCellStyle(new HSSFCellStyle(( short ) cval.getXFIndex(), xf));
342 }
343
344
347 private HSSFCell()
348 {
349 }
350
351
354 private int determineType(CellValueRecordInterface cval)
355 {
356 Record record = ( Record ) cval;
357 int sid = record.getSid();
358 int retval = 0;
359
360 switch (sid)
361 {
362
363 case NumberRecord.sid :
364 retval = HSSFCell.CELL_TYPE_NUMERIC;
365 break;
366
367 case BlankRecord.sid :
368 retval = HSSFCell.CELL_TYPE_BLANK;
369 break;
370
371 case LabelSSTRecord.sid :
372 retval = HSSFCell.CELL_TYPE_STRING;
373 break;
374
375 case FormulaRecordAggregate.sid :
376 retval = HSSFCell.CELL_TYPE_FORMULA;
377 break;
378
379 case BoolErrRecord.sid :
380 BoolErrRecord boolErrRecord = ( BoolErrRecord ) record;
381
382 retval = (boolErrRecord.isBoolean())
383 ? HSSFCell.CELL_TYPE_BOOLEAN
384 : HSSFCell.CELL_TYPE_ERROR;
385 break;
386 }
387 return retval;
388 }
389
390
394
395 public void setCellNum(short num)
396 {
397 cellNum = num;
398 record.setColumn(num);
399 }
400
401
405
406 public short getCellNum()
407 {
408 return cellNum;
409 }
410
411
421
422 public void setCellType(int cellType)
423 {
424 setCellType(cellType, true);
425 }
426
427
435
436 private void setCellType(int cellType, boolean setValue)
437 {
438
439
440
441
442
443
444 if (cellType > CELL_TYPE_ERROR)
445 {
446 throw new RuntimeException("I have no idea what type that is!");
447 }
448 switch (cellType)
449 {
450
451 case CELL_TYPE_FORMULA :
452 FormulaRecordAggregate frec = null;
453
454 if (cellType != this.cellType)
455 {
456 frec = new FormulaRecordAggregate(new FormulaRecord(),null);
457 }
458 else
459 {
460 frec = ( FormulaRecordAggregate ) record;
461 }
462 frec.setColumn(getCellNum());
463 if (setValue)
464 {
465 frec.getFormulaRecord().setValue(getNumericCellValue());
466 }
467 frec.setXFIndex(( short ) cellStyle.getIndex());
468 frec.setRow(row);
469 record = frec;
470 break;
471
472 case CELL_TYPE_NUMERIC :
473 NumberRecord nrec = null;
474
475 if (cellType != this.cellType)
476 {
477 nrec = new NumberRecord();
478 }
479 else
480 {
481 nrec = ( NumberRecord ) record;
482 }
483 nrec.setColumn(getCellNum());
484 if (setValue)
485 {
486 nrec.setValue(getNumericCellValue());
487 }
488 nrec.setXFIndex(( short ) cellStyle.getIndex());
489 nrec.setRow(row);
490 record = nrec;
491 break;
492
493 case CELL_TYPE_STRING :
494 LabelSSTRecord lrec = null;
495
496 if (cellType != this.cellType)
497 {
498 lrec = new LabelSSTRecord();
499 }
500 else
501 {
502 lrec = ( LabelSSTRecord ) record;
503 }
504 lrec.setColumn(getCellNum());
505 lrec.setRow(row);
506 lrec.setXFIndex(( short ) cellStyle.getIndex());
507 if (setValue)
508 {
509 if ((getStringCellValue() != null)
510 && (!getStringCellValue().equals("")))
511 {
512 int sst = 0;
513
514 if (encoding == ENCODING_COMPRESSED_UNICODE)
515 {
516 sst = book.addSSTString(getStringCellValue());
517 }
518 if (encoding == ENCODING_UTF_16)
519 {
520 sst = book.addSSTString(getStringCellValue(),
521 true);
522 }
523 lrec.setSSTIndex(sst);
524 }
525 }
526 record = lrec;
527 break;
528
529 case CELL_TYPE_BLANK :
530 BlankRecord brec = null;
531
532 if (cellType != this.cellType)
533 {
534 brec = new BlankRecord();
535 }
536 else
537 {
538 brec = ( BlankRecord ) record;
539 }
540 brec.setColumn(getCellNum());
541
542
543 if (cellStyle != null)
544 {
545 brec.setXFIndex(( short ) cellStyle.getIndex());
546 }
547 else
548 {
549 brec.setXFIndex(( short ) 0);
550 }
551 brec.setRow(row);
552 record = brec;
553 break;
554
555 case CELL_TYPE_BOOLEAN :
556 BoolErrRecord boolRec = null;
557
558 if (cellType != this.cellType)
559 {
560 boolRec = new BoolErrRecord();
561 }
562 else
563 {
564 boolRec = ( BoolErrRecord ) record;
565 }
566 boolRec.setColumn(getCellNum());
567 if (setValue)
568 {
569 boolRec.setValue(getBooleanCellValue());
570 }
571 boolRec.setXFIndex(( short ) cellStyle.getIndex());
572 boolRec.setRow(row);
573 record = boolRec;
574 break;
575
576 case CELL_TYPE_ERROR :
577 BoolErrRecord errRec = null;
578
579 if (cellType != this.cellType)
580 {
581 errRec = new BoolErrRecord();
582 }
583 else
584 {
585 errRec = ( BoolErrRecord ) record;
586 }
587 errRec.setColumn(getCellNum());
588 if (setValue)
589 {
590 errRec.setValue(getErrorCellValue());
591 }
592 errRec.setXFIndex(( short ) cellStyle.getIndex());
593 errRec.setRow(row);
594 record = errRec;
595 break;
596 }
597 if (cellType != this.cellType)
598 {
599 int loc = sheet.getLoc();
600
601 sheet.replaceValueRecord(record);
602 sheet.setLoc(loc);
603 }
604 this.cellType = cellType;
605 }
606
607
615
616 public int getCellType()
617 {
618 return cellType;
619 }
620
621
628 public void setCellValue(double value)
629 {
630 if ((cellType != CELL_TYPE_NUMERIC) && (cellType != CELL_TYPE_FORMULA))
631 {
632 setCellType(CELL_TYPE_NUMERIC, false);
633 }
634 (( NumberRecord ) record).setValue(value);
635 cellValue = value;
636 }
637
638
646 public void setCellValue(Date value)
647 {
648 setCellValue(HSSFDateUtil.getExcelDate(value));
649 }
650
651
659 public void setCellValue(Calendar value)
660 {
661 setCellValue(value.getTime());
662 }
663
664
673
674 public void setCellValue(String value)
675 {
676 if (value == null)
677 {
678 setCellType(CELL_TYPE_BLANK, false);
679 }
680 else
681 {
682 if ((cellType != CELL_TYPE_STRING ) && ( cellType != CELL_TYPE_FORMULA))
683 {
684 setCellType(CELL_TYPE_STRING, false);
685 }
686 int index = 0;
687
688 if (encoding == ENCODING_COMPRESSED_UNICODE)
689 {
690 index = book.addSSTString(value);
691 }
692 if (encoding == ENCODING_UTF_16)
693 {
694 index = book.addSSTString(value, true);
695 }
696 (( LabelSSTRecord ) record).setSSTIndex(index);
697 stringValue = value;
698 }
699 }
700
701 public void setCellFormula(String formula) {
702
703 if (formula==null) {
704 setCellType(CELL_TYPE_BLANK,false);
705 } else {
706 setCellType(CELL_TYPE_FORMULA,false);
707 FormulaRecordAggregate rec = (FormulaRecordAggregate) record;
708 rec.getFormulaRecord().setOptions(( short ) 2);
709 rec.getFormulaRecord().setValue(0);
710
711
712 if (rec.getXFIndex() == (short)0) rec.setXFIndex(( short ) 0x0f);
713 FormulaParser fp = new FormulaParser(formula+";",book);
714 fp.parse();
715 Ptg[] ptg = fp.getRPNPtg();
716 int size = 0;
717
718 for (int k = 0; k < ptg.length; k++) {
719 size += ptg[ k ].getSize();
720 rec.getFormulaRecord().pushExpressionToken(ptg[ k ]);
721 }
722 rec.getFormulaRecord().setExpressionLength(( short ) size);
723
724 }
725 }
726
727 public String getCellFormula() {
728
729 SheetReferences refs = book.getSheetReferences();
730 String retval = FormulaParser.toFormulaString(refs, ((FormulaRecordAggregate)record).getFormulaRecord().getParsedExpression());
731
732 return retval;
733 }
734
735
736
740
741 public double getNumericCellValue()
742 {
743 if (cellType == CELL_TYPE_BLANK)
744 {
745 return 0;
746 }
747 if (cellType == CELL_TYPE_STRING)
748 {
749 throw new NumberFormatException(
750 "You cannot get a numeric value from a String based cell");
751 }
752 if (cellType == CELL_TYPE_BOOLEAN)
753 {
754 throw new NumberFormatException(
755 "You cannot get a numeric value from a boolean cell");
756 }
757 if (cellType == CELL_TYPE_ERROR)
758 {
759 throw new NumberFormatException(
760 "You cannot get a numeric value from an error cell");
761 }
762 return cellValue;
763 }
764
765
769 public Date getDateCellValue()
770 {
771 if (cellType == CELL_TYPE_BLANK)
772 {
773 return null;
774 }
775 if (cellType == CELL_TYPE_STRING)
776 {
777 throw new NumberFormatException(
778 "You cannot get a date value from a String based cell");
779 }
780 if (cellType == CELL_TYPE_BOOLEAN)
781 {
782 throw new NumberFormatException(
783 "You cannot get a date value from a boolean cell");
784 }
785 if (cellType == CELL_TYPE_ERROR)
786 {
787 throw new NumberFormatException(
788 "You cannot get a date value from an error cell");
789 }
790 if (book.isUsing1904DateWindowing()) {
791 return HSSFDateUtil.getJavaDate(cellValue,true);
792 }
793 else {
794 return HSSFDateUtil.getJavaDate(cellValue,false);
795 }
796 }
797
798
802
803 public String getStringCellValue()
804 {
805 if (cellType == CELL_TYPE_BLANK)
806 {
807 return "";
808 }
809 if (cellType == CELL_TYPE_NUMERIC)
810 {
811 throw new NumberFormatException(
812 "You cannot get a string value from a numeric cell");
813 }
814 if (cellType == CELL_TYPE_BOOLEAN)
815 {
816 throw new NumberFormatException(
817 "You cannot get a string value from a boolean cell");
818 }
819 if (cellType == CELL_TYPE_ERROR)
820 {
821 throw new NumberFormatException(
822 "You cannot get a string value from an error cell");
823 }
824 return stringValue;
825 }
826
827
834
835 public void setCellValue(boolean value)
836 {
837 if ((cellType != CELL_TYPE_BOOLEAN ) && ( cellType != CELL_TYPE_FORMULA))
838 {
839 setCellType(CELL_TYPE_BOOLEAN, false);
840 }
841 (( BoolErrRecord ) record).setValue(value);
842 booleanValue = value;
843 }
844
845
853
854 public void setCellErrorValue(byte value)
855 {
856 if ((cellType != CELL_TYPE_ERROR) && (cellType != CELL_TYPE_FORMULA))
857 {
858 setCellType(CELL_TYPE_ERROR, false);
859 }
860 (( BoolErrRecord ) record).setValue(value);
861 errorValue = value;
862 }
863
864
868
869 public boolean getBooleanCellValue()
870 {
871 if (cellType == CELL_TYPE_BOOLEAN)
872 {
873 return booleanValue;
874 }
875 if (cellType == CELL_TYPE_BLANK)
876 {
877 return false;
878 }
879 throw new NumberFormatException(
880 "You cannot get a boolean value from a non-boolean cell");
881 }
882
883
887
888 public byte getErrorCellValue()
889 {
890 if (cellType == CELL_TYPE_ERROR)
891 {
892 return errorValue;
893 }
894 if (cellType == CELL_TYPE_BLANK)
895 {
896 return ( byte ) 0;
897 }
898 throw new NumberFormatException(
899 "You cannot get an error value from a non-error cell");
900 }
901
902
910
911 public void setCellStyle(HSSFCellStyle style)
912 {
913 cellStyle = style;
914 record.setXFIndex(style.getIndex());
915 }
916
917
922
923 public HSSFCellStyle getCellStyle()
924 {
925 return cellStyle;
926 }
927
928
936
937 public short getEncoding()
938 {
939 return encoding;
940 }
941
942
950
951 public void setEncoding(short encoding)
952 {
953 this.encoding = encoding;
954 }
955
956
961
962 protected CellValueRecordInterface getCellValueRecord()
963 {
964 return record;
965 }
966
967
970 private void checkBounds(int cellNum) {
971 if (cellNum > 255) {
972 throw new RuntimeException("You cannot have more than 255 columns "+
973 "in a given row (IV). Because Excel can't handle it");
974 }
975 else if (cellNum < 0) {
976 throw new RuntimeException("You cannot reference columns with an index of less then 0.");
977 }
978 }
979
980
983 public void setAsActiveCell()
984 {
985 this.sheet.setActiveCellRow(this.row);
986 this.sheet.setActiveCellCol(this.cellNum);
987 }
988 }
989