1
54
55
60 package org.apache.poi.hssf.usermodel;
61
62 import org.apache.poi.hssf.model.Sheet;
63 import org.apache.poi.hssf.model.Workbook;
64 import org.apache.poi.hssf.record.*;
65 import org.apache.poi.hssf.util.Region;
66 import org.apache.poi.util.POILogFactory;
67 import org.apache.poi.util.POILogger;
68
69 import java.util.Iterator;
70 import java.util.TreeMap;
71 import java.util.List;
72
73
80
81 public class HSSFSheet
82 {
83 private static final int DEBUG = POILogger.DEBUG;
84
85
86 public static final short LeftMargin = Sheet.LeftMargin;
87 public static final short RightMargin = Sheet.RightMargin;
88 public static final short TopMargin = Sheet.TopMargin;
89 public static final short BottomMargin = Sheet.BottomMargin;
90
91 public static final byte PANE_LOWER_RIGHT = (byte)0;
92 public static final byte PANE_UPPER_RIGHT = (byte)1;
93 public static final byte PANE_LOWER_LEFT = (byte)2;
94 public static final byte PANE_UPPER_LEFT = (byte)3;
95
96
97
102
103 public final static int INITIAL_CAPACITY = 20;
104
105
108
109 private Sheet sheet;
110 private TreeMap rows;
111 private Workbook book;
112 private int firstrow;
113 private int lastrow;
114 private static POILogger log = POILogFactory.getLogger(HSSFSheet.class);
115
116
123
124 protected HSSFSheet(Workbook book)
125 {
126 sheet = Sheet.createSheet();
127 rows = new TreeMap();
128 this.book = book;
129 }
130
131
139
140 protected HSSFSheet(Workbook book, Sheet sheet)
141 {
142 this.sheet = sheet;
143 rows = new TreeMap();
144 this.book = book;
145 setPropertiesFromSheet(sheet);
146 }
147
148 HSSFSheet cloneSheet(Workbook book) {
149 return new HSSFSheet(book, sheet.cloneSheet());
150 }
151
152
153
156
157 private void setPropertiesFromSheet(Sheet sheet)
158 {
159 int sloc = sheet.getLoc();
160 RowRecord row = sheet.getNextRow();
161
162 while (row != null)
163 {
164 createRowFromRecord(row);
165
166 row = sheet.getNextRow();
167 }
168 sheet.setLoc(sloc);
169 CellValueRecordInterface cval = sheet.getNextValueRecord();
170 long timestart = System.currentTimeMillis();
171
172 log.log(DEBUG, "Time at start of cell creating in HSSF sheet = ",
173 new Long(timestart));
174 HSSFRow lastrow = null;
175
176 while (cval != null)
177 {
178 long cellstart = System.currentTimeMillis();
179 HSSFRow hrow = lastrow;
180
181 if ( ( lastrow == null ) || ( lastrow.getRowNum() != cval.getRow() ) )
182 {
183 hrow = getRow( cval.getRow() );
184 }
185 if ( hrow != null )
186 {
187 lastrow = hrow;
188 log.log( DEBUG, "record id = " + Integer.toHexString( ( (Record) cval ).getSid() ) );
189 hrow.createCellFromRecord( cval );
190 cval = sheet.getNextValueRecord();
191 log.log( DEBUG, "record took ",
192 new Long( System.currentTimeMillis() - cellstart ) );
193 }
194 else
195 {
196 cval = null;
197 }
198 }
199 log.log(DEBUG, "total sheet cell creation took ",
200 new Long(System.currentTimeMillis() - timestart));
201 }
202
203
211
212
213 public HSSFRow createRow(int rownum)
214 {
215 HSSFRow row = new HSSFRow(book, sheet, rownum);
216
217 addRow(row, true);
218 return row;
219 }
220
221
227
228 private HSSFRow createRowFromRecord(RowRecord row)
229 {
230 HSSFRow hrow = new HSSFRow(book, sheet, row);
231
232 addRow(hrow, false);
233 return hrow;
234 }
235
236
241
242 public void removeRow(HSSFRow row)
243 {
244 sheet.setLoc(sheet.getDimsLoc());
245 if (rows.size() > 0)
246 {
247 rows.remove(row);
248 if (row.getRowNum() == getLastRowNum())
249 {
250 lastrow = findLastRow(lastrow);
251 }
252 if (row.getRowNum() == getFirstRowNum())
253 {
254 firstrow = findFirstRow(firstrow);
255 }
256 Iterator iter = row.cellIterator();
257
258 while (iter.hasNext())
259 {
260 HSSFCell cell = (HSSFCell) iter.next();
261
262 sheet.removeValueRecord(row.getRowNum(),
263 cell.getCellValueRecord());
264 }
265 sheet.removeRow(row.getRowRecord());
266 }
267 }
268
269
272
273 private int findLastRow(int lastrow)
274 {
275 int rownum = lastrow - 1;
276 HSSFRow r = getRow(rownum);
277
278 while (r == null && rownum >= 0)
279 {
280 r = getRow(--rownum);
281 }
282 return rownum;
283 }
284
285
288
289 private int findFirstRow(int firstrow)
290 {
291 int rownum = firstrow + 1;
292 HSSFRow r = getRow(rownum);
293
294 while (r == null && rownum <= getLastRowNum())
295 {
296 r = getRow(++rownum);
297 }
298
299 if (rownum > getLastRowNum())
300 return -1;
301
302 return rownum;
303 }
304
305
310
311 private void addRow(HSSFRow row, boolean addLow)
312 {
313 rows.put(row, row);
314 if (addLow)
315 {
316 sheet.addRow(row.getRowRecord());
317 }
318 if (row.getRowNum() > getLastRowNum())
319 {
320 lastrow = row.getRowNum();
321 }
322 if (row.getRowNum() < getFirstRowNum())
323 {
324 firstrow = row.getRowNum();
325 }
326 }
327
328
334
335 public HSSFRow getRow(int rownum)
336 {
337 HSSFRow row = new HSSFRow();
338
339
340 row.setRowNum( rownum);
341 return (HSSFRow) rows.get(row);
342 }
343
344
347
348 public int getPhysicalNumberOfRows()
349 {
350 return rows.size();
351 }
352
353
357
358 public int getFirstRowNum()
359 {
360 return firstrow;
361 }
362
363
367
368 public int getLastRowNum()
369 {
370 return lastrow;
371 }
372
373
378
379 public void setColumnWidth(short column, short width)
380 {
381 sheet.setColumnWidth(column, width);
382 }
383
384
389
390 public short getColumnWidth(short column)
391 {
392 return sheet.getColumnWidth(column);
393 }
394
395
400
401 public short getDefaultColumnWidth()
402 {
403 return sheet.getDefaultColumnWidth();
404 }
405
406
411
412 public short getDefaultRowHeight()
413 {
414 return sheet.getDefaultRowHeight();
415 }
416
417
422
423 public float getDefaultRowHeightInPoints()
424 {
425 return (sheet.getDefaultRowHeight() / 20);
426 }
427
428
433
434 public void setDefaultColumnWidth(short width)
435 {
436 sheet.setDefaultColumnWidth(width);
437 }
438
439
444
445 public void setDefaultRowHeight(short height)
446 {
447 sheet.setDefaultRowHeight(height);
448 }
449
450
455
456 public void setDefaultRowHeightInPoints(float height)
457 {
458 sheet.setDefaultRowHeight((short) (height * 20));
459 }
460
461
465
466 public boolean isGridsPrinted()
467 {
468 return sheet.isGridsPrinted();
469 }
470
471
475
476 public void setGridsPrinted(boolean value)
477 {
478 sheet.setGridsPrinted(value);
479 }
480
481
486
487 public int addMergedRegion(Region region)
488 {
489
490 return sheet.addMergedRegion( region.getRowFrom(),
491 region.getColumnFrom(),
492
493 region.getRowTo(),
494 region.getColumnTo());
495 }
496
497
501
502 public void setVerticallyCenter(boolean value)
503 {
504 VCenterRecord record =
505 (VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
506
507 record.setVCenter(value);
508 }
509
510
513
514 public boolean getVerticallyCenter(boolean value)
515 {
516 VCenterRecord record =
517 (VCenterRecord) sheet.findFirstRecordBySid(VCenterRecord.sid);
518
519 return record.getVCenter();
520 }
521
522
526
527 public void setHorizontallyCenter(boolean value)
528 {
529 HCenterRecord record =
530 (HCenterRecord) sheet.findFirstRecordBySid(HCenterRecord.sid);
531
532 record.setHCenter(value);
533 }
534
535
538
539 public boolean getHorizontallyCenter()
540 {
541 HCenterRecord record =
542 (HCenterRecord) sheet.findFirstRecordBySid(HCenterRecord.sid);
543
544 return record.getHCenter();
545 }
546
547
548
549
553
554 public void removeMergedRegion(int index)
555 {
556 sheet.removeMergedRegion(index);
557 }
558
559
563
564 public int getNumMergedRegions()
565 {
566 return sheet.getNumMergedRegions();
567 }
568
569
574
575 public Region getMergedRegionAt(int index)
576 {
577 return new Region(sheet.getMergedRegionAt(index));
578 }
579
580
584
585 public Iterator rowIterator()
586 {
587 return rows.values().iterator();
588 }
589
590
595
596 protected Sheet getSheet()
597 {
598 return sheet;
599 }
600
601
605
606 public void setAlternativeExpression(boolean b)
607 {
608 WSBoolRecord record =
609 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
610
611 record.setAlternateExpression(b);
612 }
613
614
618
619 public void setAlternativeFormula(boolean b)
620 {
621 WSBoolRecord record =
622 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
623
624 record.setAlternateFormula(b);
625 }
626
627
631
632 public void setAutobreaks(boolean b)
633 {
634 WSBoolRecord record =
635 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
636
637 record.setAutobreaks(b);
638 }
639
640
644
645 public void setDialog(boolean b)
646 {
647 WSBoolRecord record =
648 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
649
650 record.setDialog(b);
651 }
652
653
658
659 public void setDisplayGuts(boolean b)
660 {
661 WSBoolRecord record =
662 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
663
664 record.setDisplayGuts(b);
665 }
666
667
671
672 public void setFitToPage(boolean b)
673 {
674 WSBoolRecord record =
675 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
676
677 record.setFitToPage(b);
678 }
679
680
684
685 public void setRowSumsBelow(boolean b)
686 {
687 WSBoolRecord record =
688 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
689
690 record.setRowSumsBelow(b);
691 }
692
693
697
698 public void setRowSumsRight(boolean b)
699 {
700 WSBoolRecord record =
701 (WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid);
702
703 record.setRowSumsRight(b);
704 }
705
706
710
711 public boolean getAlternateExpression()
712 {
713 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
714 .getAlternateExpression();
715 }
716
717
721
722 public boolean getAlternateFormula()
723 {
724 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
725 .getAlternateFormula();
726 }
727
728
732
733 public boolean getAutobreaks()
734 {
735 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
736 .getAutobreaks();
737 }
738
739
743
744 public boolean getDialog()
745 {
746 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
747 .getDialog();
748 }
749
750
755
756 public boolean getDisplayGuts()
757 {
758 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
759 .getDisplayGuts();
760 }
761
762
766
767 public boolean getFitToPage()
768 {
769 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
770 .getFitToPage();
771 }
772
773
777
778 public boolean getRowSumsBelow()
779 {
780 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
781 .getRowSumsBelow();
782 }
783
784
788
789 public boolean getRowSumsRight()
790 {
791 return ((WSBoolRecord) sheet.findFirstRecordBySid(WSBoolRecord.sid))
792 .getRowSumsRight();
793 }
794
795
799 public boolean isPrintGridlines() {
800 return getSheet().getPrintGridlines().getPrintGridlines();
801 }
802
803
808 public void setPrintGridlines( boolean newPrintGridlines )
809 {
810 getSheet().getPrintGridlines().setPrintGridlines( newPrintGridlines );
811 }
812
813
817 public HSSFPrintSetup getPrintSetup()
818 {
819 return new HSSFPrintSetup( getSheet().getPrintSetup() );
820 }
821
822
826 public HSSFHeader getHeader()
827 {
828 return new HSSFHeader( getSheet().getHeader() );
829 }
830
831
835 public HSSFFooter getFooter()
836 {
837 return new HSSFFooter( getSheet().getFooter() );
838 }
839
840
844 public void setSelected( boolean sel )
845 {
846 getSheet().setSelected( sel );
847 }
848
849
854 public double getMargin( short margin )
855 {
856 return getSheet().getMargin( margin );
857 }
858
859
864 public void setMargin( short margin, double size )
865 {
866 getSheet().setMargin( margin, size );
867 }
868
869
877 public void setZoom( int numerator, int denominator)
878 {
879 if (numerator < 1 || numerator > 65535)
880 throw new IllegalArgumentException("Numerator must be greater than 1 and less than 65536");
881 if (denominator < 1 || denominator > 65535)
882 throw new IllegalArgumentException("Denominator must be greater than 1 and less than 65536");
883
884 SCLRecord sclRecord = new SCLRecord();
885 sclRecord.setNumerator((short)numerator);
886 sclRecord.setDenominator((short)denominator);
887 getSheet().setSCLRecord(sclRecord);
888 }
889
890
899 public void shiftRows( int startRow, int endRow, int n )
900 {
901 int s, e, inc;
902 if ( n < 0 )
903 {
904 s = startRow;
905 e = endRow;
906 inc = 1;
907 }
908 else
909 {
910 s = endRow;
911 e = startRow;
912 inc = -1;
913 }
914 for ( int rowNum = s; rowNum >= startRow && rowNum <= endRow && rowNum >= 0 && rowNum < 65536; rowNum += inc )
915 {
916 HSSFRow row = getRow( rowNum );
917 HSSFRow row2Replace = getRow( rowNum + n );
918 if ( row2Replace == null )
919 row2Replace = createRow( rowNum + n );
920
921 HSSFCell cell;
922 for ( short col = row2Replace.getFirstCellNum(); col <= row2Replace.getLastCellNum(); col++ )
923 {
924 cell = row2Replace.getCell( col );
925 if ( cell != null )
926 row2Replace.removeCell( cell );
927 }
928 if (row == null) continue;
929 for ( short col = row.getFirstCellNum(); col <= row.getLastCellNum(); col++ )
930 {
931 cell = row.getCell( col );
932 if ( cell != null )
933 {
934 row.removeCell( cell );
935 CellValueRecordInterface cellRecord = cell.getCellValueRecord();
936 cellRecord.setRow( rowNum + n );
937 row2Replace.createCellFromRecord( cellRecord );
938 sheet.addValueRecord( rowNum + n, cellRecord );
939 }
940 }
941 }
942 if ( endRow == lastrow || endRow + n > lastrow ) lastrow = Math.min( endRow + n, 65535 );
943 if ( startRow == firstrow || startRow + n < firstrow ) firstrow = Math.max( startRow + n, 0 );
944 }
945
946 protected void insertChartRecords( List records )
947 {
948 int window2Loc = sheet.findFirstRecordLocBySid( WindowTwoRecord.sid );
949 sheet.getRecords().addAll( window2Loc, records );
950 }
951
952
959 public void createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow )
960 {
961 if (colSplit < 0 || colSplit > 255) throw new IllegalArgumentException("Column must be between 0 and 255");
962 if (rowSplit < 0 || rowSplit > 65535) throw new IllegalArgumentException("Row must be between 0 and 65535");
963 if (leftmostColumn < colSplit) throw new IllegalArgumentException("leftmostColumn parameter must not be less than colSplit parameter");
964 if (topRow < rowSplit) throw new IllegalArgumentException("topRow parameter must not be less than leftmostColumn parameter");
965 getSheet().createFreezePane( colSplit, rowSplit, topRow, leftmostColumn );
966 }
967
968
973 public void createFreezePane( int colSplit, int rowSplit )
974 {
975 createFreezePane( colSplit, rowSplit, colSplit, rowSplit );
976 }
977
978
991 public void createSplitPane(int xSplitPos, int ySplitPos, int leftmostColumn, int topRow, int activePane )
992 {
993 getSheet().createSplitPane( xSplitPos, ySplitPos, topRow, leftmostColumn, activePane );
994 }
995
996
997 }
998