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 package org.apache.poi.hssf.usermodel;
19
20 import org.apache.poi.POIDocument;
21 import org.apache.poi.ddf.EscherBSERecord;
22 import org.apache.poi.ddf.EscherBitmapBlip;
23 import org.apache.poi.ddf.EscherRecord;
24 import org.apache.poi.ddf.EscherBlipRecord;
25 import org.apache.poi.hssf.eventmodel.EventRecordFactory;
26 import org.apache.poi.hssf.model.Sheet;
27 import org.apache.poi.hssf.model.Workbook;
28 import org.apache.poi.hssf.record;
29 import org.apache.poi.hssf.record.formula.Area3DPtg;
30 import org.apache.poi.hssf.record.formula.MemFuncPtg;
31 import org.apache.poi.hssf.record.formula.UnionPtg;
32 import org.apache.poi.hssf.usermodel.HSSFRow.MissingCellPolicy;
33 import org.apache.poi.hssf.util.CellReference;
34 import org.apache.poi.hssf.util.SheetReferences;
35 import org.apache.poi.poifs.filesystem;
36 import org.apache.poi.util.POILogFactory;
37 import org.apache.poi.util.POILogger;
38
39 import java.io.ByteArrayInputStream;
40 import java.io.FileNotFoundException;
41 import java.io.IOException;
42 import java.io.InputStream;
43 import java.io.OutputStream;
44 import java.io.PrintWriter;
45 import java.util.ArrayList;
46 import java.util.Iterator;
47 import java.util.List;
48 import java.util.Stack;
49
50 /**
51 * High level representation of a workbook. This is the first object most users
52 * will construct whether they are reading or writing a workbook. It is also the
53 * top level object for creating new sheets/etc.
54 *
55 * @see org.apache.poi.hssf.model.Workbook
56 * @see org.apache.poi.hssf.usermodel.HSSFSheet
57 * @author Andrew C. Oliver (acoliver at apache dot org)
58 * @author Glen Stampoultzis (glens at apache.org)
59 * @author Shawn Laubach (slaubach at apache dot org)
60 * @version 2.0-pre
61 */
62 public class HSSFWorkbook extends POIDocument
63 {
64 private static final int DEBUG = POILogger.DEBUG;
65
66 /**
67 * used for compile-time performance/memory optimization. This determines the
68 * initial capacity for the sheet collection. Its currently set to 3.
69 * Changing it in this release will decrease performance
70 * since you're never allowed to have more or less than three sheets!
71 */
72
73 public final static int INITIAL_CAPACITY = 3;
74
75 /**
76 * this is the reference to the low level Workbook object
77 */
78
79 private Workbook workbook;
80
81 /**
82 * this holds the HSSFSheet objects attached to this workbook
83 */
84
85 protected List _sheets;
86
87 /**
88 * this holds the HSSFName objects attached to this workbook
89 */
90
91 private ArrayList names;
92
93 /**
94 * holds whether or not to preserve other nodes in the POIFS. Used
95 * for macros and embedded objects.
96 */
97 private boolean preserveNodes;
98
99 /**
100 * Used to keep track of the data formatter so that all
101 * createDataFormatter calls return the same one for a given
102 * book. This ensures that updates from one places is visible
103 * someplace else.
104 */
105 private HSSFDataFormat formatter;
106
107 /**
108 * The policy to apply in the event of missing or
109 * blank cells when fetching from a row.
110 * See {@link MissingCellPolicy}
111 */
112 private MissingCellPolicy missingCellPolicy = HSSFRow.RETURN_NULL_AND_BLANK;
113
114
115 /** Extended windows meta file */
116 public static final int PICTURE_TYPE_EMF = 2;
117 /** Windows Meta File */
118 public static final int PICTURE_TYPE_WMF = 3;
119 /** Mac PICT format */
120 public static final int PICTURE_TYPE_PICT = 4;
121 /** JPEG format */
122 public static final int PICTURE_TYPE_JPEG = 5;
123 /** PNG format */
124 public static final int PICTURE_TYPE_PNG = 6;
125 /** Device independant bitmap */
126 public static final int PICTURE_TYPE_DIB = 7;
127
128
129 private static POILogger log = POILogFactory.getLogger(HSSFWorkbook.class);
130
131
132
133 /**
134 * Creates new HSSFWorkbook from scratch (start here!)
135 *
136 */
137 public HSSFWorkbook()
138 {
139 this(Workbook.createWorkbook());
140 }
141
142 protected HSSFWorkbook( Workbook book )
143 {
144 super(null, null);
145 workbook = book;
146 _sheets = new ArrayList( INITIAL_CAPACITY );
147 names = new ArrayList( INITIAL_CAPACITY );
148 }
149
150 public HSSFWorkbook(POIFSFileSystem fs) throws IOException {
151 this(fs,true);
152 }
153
154 /**
155 * given a POI POIFSFileSystem object, read in its Workbook and populate the high and
156 * low level models. If you're reading in a workbook...start here.
157 *
158 * @param fs the POI filesystem that contains the Workbook stream.
159 * @param preserveNodes whether to preseve other nodes, such as
160 * macros. This takes more memory, so only say yes if you
161 * need to. If set, will store all of the POIFSFileSystem
162 * in memory
163 * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
164 * @exception IOException if the stream cannot be read
165 */
166 public HSSFWorkbook(POIFSFileSystem fs, boolean preserveNodes)
167 throws IOException
168 {
169 this(fs.getRoot(), fs, preserveNodes);
170 }
171
172 /**
173 * Normally, the Workbook will be in a POIFS Stream
174 * called "Workbook". However, some weird XLS generators use "WORKBOOK"
175 */
176 private static final String[] WORKBOOK_DIR_ENTRY_NAMES = {
177 "Workbook", // as per BIFF8 spec
178 "WORKBOOK",
179 };
180
181
182 private static String getWorkbookDirEntryName(DirectoryNode directory) {
183
184 String[] potentialNames = WORKBOOK_DIR_ENTRY_NAMES;
185 for (int i = 0; i < potentialNames.length; i++) {
186 String wbName = potentialNames[i];
187 try {
188 directory.getEntry(wbName);
189 return wbName;
190 } catch (FileNotFoundException e) {
191 // continue - to try other options
192 }
193 }
194
195 // check for previous version of file format
196 try {
197 directory.getEntry("Book");
198 throw new IllegalArgumentException("The supplied spreadsheet seems to be Excel 5.0/7.0 (BIFF5) format. "
199 + "POI only supports BIFF8 format (from Excel versions 97/2000/XP/2003)");
200 } catch (FileNotFoundException e) {
201 // fall through
202 }
203
204 throw new IllegalArgumentException("The supplied POIFSFileSystem does not contain a BIFF8 'Workbook' entry. "
205 + "Is it really an excel file?");
206 }
207
208 /**
209 * given a POI POIFSFileSystem object, and a specific directory
210 * within it, read in its Workbook and populate the high and
211 * low level models. If you're reading in a workbook...start here.
212 *
213 * @param directory the POI filesystem directory to process from
214 * @param fs the POI filesystem that contains the Workbook stream.
215 * @param preserveNodes whether to preseve other nodes, such as
216 * macros. This takes more memory, so only say yes if you
217 * need to. If set, will store all of the POIFSFileSystem
218 * in memory
219 * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
220 * @exception IOException if the stream cannot be read
221 */
222 public HSSFWorkbook(DirectoryNode directory, POIFSFileSystem fs, boolean preserveNodes)
223 throws IOException
224 {
225 super(directory, fs);
226 String workbookName = getWorkbookDirEntryName(directory);
227
228 this.preserveNodes = preserveNodes;
229
230 // If we're not preserving nodes, don't track the
231 // POIFS any more
232 if(! preserveNodes) {
233 this.filesystem = null;
234 this.directory = null;
235 }
236
237 _sheets = new ArrayList(INITIAL_CAPACITY);
238 names = new ArrayList(INITIAL_CAPACITY);
239
240 // Grab the data from the workbook stream, however
241 // it happens to be spelled.
242 InputStream stream = directory.createDocumentInputStream(workbookName);
243
244 EventRecordFactory factory = new EventRecordFactory();
245
246 List records = RecordFactory.createRecords(stream);
247
248 workbook = Workbook.createWorkbook(records);
249 setPropertiesFromWorkbook(workbook);
250 int recOffset = workbook.getNumRecords();
251 int sheetNum = 0;
252
253 // convert all LabelRecord records to LabelSSTRecord
254 convertLabelRecords(records, recOffset);
255 while (recOffset < records.size())
256 {
257 Sheet sheet = Sheet.createSheet(records, sheetNum++, recOffset );
258
259 recOffset = sheet.getEofLoc()+1;
260 if (recOffset == 1)
261 {
262 break;
263 }
264
265 HSSFSheet hsheet = new HSSFSheet(this, sheet);
266
267 _sheets.add(hsheet);
268
269 // workbook.setSheetName(sheets.size() -1, "Sheet"+sheets.size());
270 }
271
272 for (int i = 0 ; i < workbook.getNumNames() ; ++i){
273 HSSFName name = new HSSFName(this, workbook.getNameRecord(i));
274 names.add(name);
275 }
276 }
277
278 public HSSFWorkbook(InputStream s) throws IOException {
279 this(s,true);
280 }
281
282 /**
283 * Companion to HSSFWorkbook(POIFSFileSystem), this constructs the POI filesystem around your
284 * inputstream.
285 *
286 * @param s the POI filesystem that contains the Workbook stream.
287 * @param preserveNodes whether to preseve other nodes, such as
288 * macros. This takes more memory, so only say yes if you
289 * need to.
290 * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
291 * @see #HSSFWorkbook(POIFSFileSystem)
292 * @exception IOException if the stream cannot be read
293 */
294
295 public HSSFWorkbook(InputStream s, boolean preserveNodes)
296 throws IOException
297 {
298 this(new POIFSFileSystem(s), preserveNodes);
299 }
300
301 /**
302 * used internally to set the workbook properties.
303 */
304
305 private void setPropertiesFromWorkbook(Workbook book)
306 {
307 this.workbook = book;
308
309 // none currently
310 }
311
312 /**
313 * This is basically a kludge to deal with the now obsolete Label records. If
314 * you have to read in a sheet that contains Label records, be aware that the rest
315 * of the API doesn't deal with them, the low level structure only provides read-only
316 * semi-immutable structures (the sets are there for interface conformance with NO
317 * impelmentation). In short, you need to call this function passing it a reference
318 * to the Workbook object. All labels will be converted to LabelSST records and their
319 * contained strings will be written to the Shared String tabel (SSTRecord) within
320 * the Workbook.
321 *
322 * @param wb sheet's matching low level Workbook structure containing the SSTRecord.
323 * @see org.apache.poi.hssf.record.LabelRecord
324 * @see org.apache.poi.hssf.record.LabelSSTRecord
325 * @see org.apache.poi.hssf.record.SSTRecord
326 */
327
328 private void convertLabelRecords(List records, int offset)
329 {
330 if (log.check( POILogger.DEBUG ))
331 log.log(POILogger.DEBUG, "convertLabelRecords called");
332 for (int k = offset; k < records.size(); k++)
333 {
334 Record rec = ( Record ) records.get(k);
335
336 if (rec.getSid() == LabelRecord.sid)
337 {
338 LabelRecord oldrec = ( LabelRecord ) rec;
339
340 records.remove(k);
341 LabelSSTRecord newrec = new LabelSSTRecord();
342 int stringid =
343 workbook.addSSTString(new UnicodeString(oldrec.getValue()));
344
345 newrec.setRow(oldrec.getRow());
346 newrec.setColumn(oldrec.getColumn());
347 newrec.setXFIndex(oldrec.getXFIndex());
348 newrec.setSSTIndex(stringid);
349 records.add(k, newrec);
350 }
351 }
352 if (log.check( POILogger.DEBUG ))
353 log.log(POILogger.DEBUG, "convertLabelRecords exit");
354 }
355
356 /**
357 * Retrieves the current policy on what to do when
358 * getting missing or blank cells from a row.
359 * The default is to return blank and null cells.
360 * {@link MissingCellPolicy}
361 */
362 public MissingCellPolicy getMissingCellPolicy() {
363 return missingCellPolicy;
364 }
365
366 /**
367 * Sets the policy on what to do when
368 * getting missing or blank cells from a row.
369 * This will then apply to all calls to
370 * {@link HSSFRow.getCell()}. See
371 * {@link MissingCellPolicy}
372 */
373 public void setMissingCellPolicy(MissingCellPolicy missingCellPolicy) {
374 this.missingCellPolicy = missingCellPolicy;
375 }
376
377 /**
378 * sets the order of appearance for a given sheet.
379 *
380 * @param sheetname the name of the sheet to reorder
381 * @param pos the position that we want to insert the sheet into (0 based)
382 */
383
384 public void setSheetOrder(String sheetname, int pos ) {
385 _sheets.add(pos,_sheets.remove(getSheetIndex(sheetname)));
386 workbook.setSheetOrder(sheetname, pos);
387 }
388
389 private void validateSheetIndex(int index) {
390 int lastSheetIx = _sheets.size() - 1;
391 if (index < 0 || index > lastSheetIx) {
392 throw new IllegalArgumentException("Sheet index ("
393 + index +") is out of range (0.." + lastSheetIx + ")");
394 }
395 }
396
397 /**
398 * Selects a single sheet. This may be different to
399 * the 'active' sheet (which is the sheet with focus).
400 */
401 public void setSelectedTab(int index) {
402
403 validateSheetIndex(index);
404 int nSheets = _sheets.size();
405 for (int i=0; i<nSheets; i++) {
406 getSheetAt(i).setSelected(i == index);
407 }
408 workbook.getWindowOne().setNumSelectedTabs((short)1);
409 }
410 /**
411 * deprecated May 2008
412 * @deprecated use setSelectedTab(int)
413 */
414 public void setSelectedTab(short index) {
415 setSelectedTab((int)index);
416 }
417 public void setSelectedTabs(int[] indexes) {
418
419 for (int i = 0; i < indexes.length; i++) {
420 validateSheetIndex(indexes[i]);
421 }
422 int nSheets = _sheets.size();
423 for (int i=0; i<nSheets; i++) {
424 boolean bSelect = false;
425 for (int j = 0; j < indexes.length; j++) {
426 if (indexes[j] == i) {
427 bSelect = true;
428 break;
429 }
430
431 }
432 getSheetAt(i).setSelected(bSelect);
433 }
434 workbook.getWindowOne().setNumSelectedTabs((short)indexes.length);
435 }
436 /**
437 * Convenience method to set the active sheet. The active sheet is is the sheet
438 * which is currently displayed when the workbook is viewed in Excel.
439 * 'Selected' sheet(s) is a distinct concept.
440 */
441 public void setActiveSheet(int index) {
442
443 validateSheetIndex(index);
444 int nSheets = _sheets.size();
445 for (int i=0; i<nSheets; i++) {
446 getSheetAt(i).setActive(i == index);
447 }
448 workbook.getWindowOne().setActiveSheetIndex(index);
449 }
450
451 /**
452 * gets the tab whose data is actually seen when the sheet is opened.
453 * This may be different from the "selected sheet" since excel seems to
454 * allow you to show the data of one sheet when another is seen "selected"
455 * in the tabs (at the bottom).
456 * @see org.apache.poi.hssf.usermodel.HSSFSheet#setSelected(boolean)
457 */
458 public int getActiveSheetIndex() {
459 return workbook.getWindowOne().getActiveSheetIndex();
460 }
461 /**
462 * deprecated May 2008
463 * @deprecated - Misleading name - use getActiveSheetIndex()
464 */
465 public short getSelectedTab() {
466 return (short) getActiveSheetIndex();
467 }
468
469
470 /**
471 * sets the first tab that is displayed in the list of tabs
472 * in excel.
473 * @param index
474 */
475 public void setFirstVisibleTab(int index) {
476 workbook.getWindowOne().setFirstVisibleTab(index);
477 }
478 /**
479 * deprecated May 2008
480 * @deprecated - Misleading name - use setFirstVisibleTab()
481 */
482 public void setDisplayedTab(short index) {
483 setFirstVisibleTab(index);
484 }
485
486 /**
487 * sets the first tab that is displayed in the list of tabs in excel.
488 */
489 public int getFirstVisibleTab() {
490 return workbook.getWindowOne().getFirstVisibleTab();
491 }
492 /**
493 * deprecated May 2008
494 * @deprecated - Misleading name - use getFirstVisibleTab()
495 */
496 public short getDisplayedTab() {
497 return (short) getFirstVisibleTab();
498 }
499
500 /**
501 * @deprecated POI will now properly handle unicode strings without
502 * forceing an encoding
503 */
504 public final static byte ENCODING_COMPRESSED_UNICODE = 0;
505 /**
506 * @deprecated POI will now properly handle unicode strings without
507 * forceing an encoding
508 */
509 public final static byte ENCODING_UTF_16 = 1;
510
511
512 /**
513 * set the sheet name.
514 * Will throw IllegalArgumentException if the name is greater than 31 chars
515 * or contains /\?*[]
516 * @param sheetIx number (0 based)
517 */
518 public void setSheetName(int sheetIx, String name)
519 {
520 if (workbook.doesContainsSheetName( name, sheetIx )) {
521 throw new IllegalArgumentException( "The workbook already contains a sheet with this name" );
522 }
523 validateSheetIndex(sheetIx);
524 workbook.setSheetName(sheetIx, name);
525 }
526
527
528 /**
529 * set the sheet name forcing the encoding. Forcing the encoding IS A BAD IDEA!!!
530 * @deprecated 3-Jan-2006 POI now automatically detects unicode and sets the encoding
531 * appropriately. Simply use setSheetName(int sheet, String encoding)
532 * @throws IllegalArgumentException if the name is greater than 31 chars
533 * or contains /\?*[]
534 * @param sheet number (0 based)
535 */
536 public void setSheetName(int sheetIx, String name, short encoding)
537 {
538 if (workbook.doesContainsSheetName( name, sheetIx )) {
539 throw new IllegalArgumentException( "The workbook already contains a sheet with this name" );
540 }
541 validateSheetIndex(sheetIx);
542
543 switch ( encoding ) {
544 case ENCODING_COMPRESSED_UNICODE:
545 case ENCODING_UTF_16:
546 break;
547
548 default:
549 // TODO java.io.UnsupportedEncodingException
550 throw new RuntimeException( "Unsupported encoding" );
551 }
552
553 workbook.setSheetName( sheetIx, name, encoding );
554 }
555
556 /**
557 * get the sheet name
558 * @param sheetIx Number
559 * @return Sheet name
560 */
561 public String getSheetName(int sheetIx)
562 {
563 validateSheetIndex(sheetIx);
564 return workbook.getSheetName(sheetIx);
565 }
566
567 /**
568 * check whether a sheet is hidden
569 * @param sheetIx Number
570 * @return True if sheet is hidden
571 */
572 public boolean isSheetHidden(int sheetIx) {
573 validateSheetIndex(sheetIx);
574 return workbook.isSheetHidden(sheetIx);
575 }
576
577 /**
578 * Hide or unhide a sheet
579 *
580 * @param sheetIx The sheet index
581 * @param hidden True to mark the sheet as hidden, false otherwise
582 */
583 public void setSheetHidden(int sheetIx, boolean hidden) {
584 validateSheetIndex(sheetIx);
585 workbook.setSheetHidden(sheetIx, hidden);
586 }
587
588 /*
589 * get the sheet's index
590 * @param name sheet name
591 * @return sheet index or -1 if it was not found.
592 */
593
594 /** Returns the index of the sheet by his name
595 * @param name the sheet name
596 * @return index of the sheet (0 based)
597 */
598 public int getSheetIndex(String name)
599 {
600 int retval = workbook.getSheetIndex(name);
601
602 return retval;
603 }
604
605 /** Returns the index of the given sheet
606 * @param sheet the sheet to look up
607 * @return index of the sheet (0 based). <tt>-1</tt> if not found
608 */
609 public int getSheetIndex(HSSFSheet sheet)
610 {
611 for(int i=0; i<_sheets.size(); i++) {
612 if(_sheets.get(i) == sheet) {
613 return i;
614 }
615 }
616 return -1;
617 }
618
619 /**
620 * Returns the external sheet index of the sheet
621 * with the given internal index, creating one
622 * if needed.
623 * Used by some of the more obscure formula and
624 * named range things.
625 */
626 public short getExternalSheetIndex(int internalSheetIndex) {
627 return workbook.checkExternSheet(internalSheetIndex);
628 }
629
630 /**
631 * create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and returns
632 * the high level representation. Use this to create new sheets.
633 *
634 * @return HSSFSheet representing the new sheet.
635 */
636
637 public HSSFSheet createSheet()
638 {
639 HSSFSheet sheet = new HSSFSheet(this);
640
641 _sheets.add(sheet);
642 workbook.setSheetName(_sheets.size() - 1, "Sheet" + (_sheets.size() - 1));
643 boolean isOnlySheet = _sheets.size() == 1;
644 sheet.setSelected(isOnlySheet);
645 sheet.setActive(isOnlySheet);
646 return sheet;
647 }
648
649 /**
650 * create an HSSFSheet from an existing sheet in the HSSFWorkbook.
651 *
652 * @return HSSFSheet representing the cloned sheet.
653 */
654
655 public HSSFSheet cloneSheet(int sheetNum) {
656 validateSheetIndex(sheetNum);
657 HSSFSheet srcSheet = (HSSFSheet) _sheets.get(sheetNum);
658 String srcName = workbook.getSheetName(sheetNum);
659 HSSFSheet clonedSheet = srcSheet.cloneSheet(this);
660 clonedSheet.setSelected(false);
661 clonedSheet.setActive(false);
662
663 _sheets.add(clonedSheet);
664 int i = 1;
665 while (true) {
666 // Try and find the next sheet name that is unique
667 String name = srcName;
668 String index = Integer.toString(i++);
669 if (name.length() + index.length() + 2 < 31) {
670 name = name + "(" + index + ")";
671 } else {
672 name = name.substring(0, 31 - index.length() - 2) + "(" + index + ")";
673 }
674
675 //If the sheet name is unique, then set it otherwise move on to the next number.
676 if (workbook.getSheetIndex(name) == -1) {
677 workbook.setSheetName(_sheets.size()-1, name);
678 break;
679 }
680 }
681 return clonedSheet;
682 }
683
684 /**
685 * create an HSSFSheet for this HSSFWorkbook, adds it to the sheets and
686 * returns the high level representation. Use this to create new sheets.
687 *
688 * @param sheetname
689 * sheetname to set for the sheet.
690 * @return HSSFSheet representing the new sheet.
691 * @throws IllegalArgumentException
692 * if there is already a sheet present with a case-insensitive
693 * match for the specified name.
694 */
695
696 public HSSFSheet createSheet(String sheetname)
697 {
698 if (workbook.doesContainsSheetName( sheetname, _sheets.size() ))
699 throw new IllegalArgumentException( "The workbook already contains a sheet of this name" );
700
701 HSSFSheet sheet = new HSSFSheet(this);
702
703 _sheets.add(sheet);
704 workbook.setSheetName(_sheets.size() - 1, sheetname);
705 boolean isOnlySheet = _sheets.size() == 1;
706 sheet.setSelected(isOnlySheet);
707 sheet.setActive(isOnlySheet);
708 return sheet;
709 }
710
711 /**
712 * get the number of spreadsheets in the workbook (this will be three after serialization)
713 * @return number of sheets
714 */
715
716 public int getNumberOfSheets()
717 {
718 return _sheets.size();
719 }
720
721 private HSSFSheet[] getSheets() {
722 HSSFSheet[] result = new HSSFSheet[_sheets.size()];
723 _sheets.toArray(result);
724 return result;
725 }
726 /**
727 * Get the HSSFSheet object at the given index.
728 * @param index of the sheet number (0-based physical & logical)
729 * @return HSSFSheet at the provided index
730 */
731
732 public HSSFSheet getSheetAt(int index)
733 {
734 return (HSSFSheet) _sheets.get(index);
735 }
736
737 /**
738 * Get sheet with the given name (case insensitive match)
739 * @param name of the sheet
740 * @return HSSFSheet with the name provided or <code>null</code> if it does not exist
741 */
742
743 public HSSFSheet getSheet(String name)
744 {
745 HSSFSheet retval = null;
746
747 for (int k = 0; k < _sheets.size(); k++)
748 {
749 String sheetname = workbook.getSheetName(k);
750
751 if (sheetname.equalsIgnoreCase(name))
752 {
753 retval = (HSSFSheet) _sheets.get(k);
754 }
755 }
756 return retval;
757 }
758
759 public SheetReferences getSheetReferences() {
760 return workbook.getSheetReferences();
761 }
762
763 /**
764 * Removes sheet at the given index.<p/>
765 *
766 * Care must be taken if the removed sheet is the currently active or only selected sheet in
767 * the workbook. There are a few situations when Excel must have a selection and/or active
768 * sheet. (For example when printing - see Bug 40414).<br/>
769 *
770 * This method makes sure that if the removed sheet was active, another sheet will become
771 * active in its place. Furthermore, if the removed sheet was the only selected sheet, another
772 * sheet will become selected. The newly active/selected sheet will have the same index, or
773 * one less if the removed sheet was the last in the workbook.
774 *
775 * @param index of the sheet (0-based)
776 */
777 public void removeSheetAt(int index) {
778 validateSheetIndex(index);
779 boolean wasActive = getSheetAt(index).isActive();
780 boolean wasSelected = getSheetAt(index).isSelected();
781
782 _sheets.remove(index);
783 workbook.removeSheet(index);
784
785 // set the remaining active/selected sheet
786 int nSheets = _sheets.size();
787 if (nSheets < 1) {
788 // nothing more to do if there are no sheets left
789 return;
790 }
791 // the index of the closest remaining sheet to the one just deleted
792 int newSheetIndex = index;
793 if (newSheetIndex >= nSheets) {
794 newSheetIndex = nSheets-1;
795 }
796 if (wasActive) {
797 setActiveSheet(newSheetIndex);
798 }
799
800 if (wasSelected) {
801 boolean someOtherSheetIsStillSelected = false;
802 for (int i =0; i < nSheets; i++) {
803 if (getSheetAt(i).isSelected()) {
804 someOtherSheetIsStillSelected = true;
805 break;
806 }
807 }
808 if (!someOtherSheetIsStillSelected) {
809 setSelectedTab(newSheetIndex);
810 }
811 }
812 }
813
814 /**
815 * determine whether the Excel GUI will backup the workbook when saving.
816 *
817 * @param backupValue true to indicate a backup will be performed.
818 */
819
820 public void setBackupFlag(boolean backupValue)
821 {
822 BackupRecord backupRecord = workbook.getBackupRecord();
823
824 backupRecord.setBackup(backupValue ? (short) 1
825 : (short) 0);
826 }
827
828 /**
829 * determine whether the Excel GUI will backup the workbook when saving.
830 *
831 * @return the current setting for backups.
832 */
833
834 public boolean getBackupFlag()
835 {
836 BackupRecord backupRecord = workbook.getBackupRecord();
837
838 return (backupRecord.getBackup() == 0) ? false
839 : true;
840 }
841
842 /**
843 * Sets the repeating rows and columns for a sheet (as found in
844 * File->PageSetup->Sheet). This is function is included in the workbook
845 * because it creates/modifies name records which are stored at the
846 * workbook level.
847 * <p>
848 * To set just repeating columns:
849 * <pre>
850 * workbook.setRepeatingRowsAndColumns(0,0,1,-1-1);
851 * </pre>
852 * To set just repeating rows:
853 * <pre>
854 * workbook.setRepeatingRowsAndColumns(0,-1,-1,0,4);
855 * </pre>
856 * To remove all repeating rows and columns for a sheet.
857 * <pre>
858 * workbook.setRepeatingRowsAndColumns(0,-1,-1,-1,-1);
859 * </pre>
860 *
861 * @param sheetIndex 0 based index to sheet.
862 * @param startColumn 0 based start of repeating columns.
863 * @param endColumn 0 based end of repeating columns.
864 * @param startRow 0 based start of repeating rows.
865 * @param endRow 0 based end of repeating rows.
866 */
867 public void setRepeatingRowsAndColumns(int sheetIndex,
868 int startColumn, int endColumn,
869 int startRow, int endRow)
870 {
871 // Check arguments
872 if (startColumn == -1 && endColumn != -1) throw new IllegalArgumentException("Invalid column range specification");
873 if (startRow == -1 && endRow != -1) throw new IllegalArgumentException("Invalid row range specification");
874 if (startColumn < -1 || startColumn >= 0xFF) throw new IllegalArgumentException("Invalid column range specification");
875 if (endColumn < -1 || endColumn >= 0xFF) throw new IllegalArgumentException("Invalid column range specification");
876 if (startRow < -1 || startRow > 65535) throw new IllegalArgumentException("Invalid row range specification");
877 if (endRow < -1 || endRow > 65535) throw new IllegalArgumentException("Invalid row range specification");
878 if (startColumn > endColumn) throw new IllegalArgumentException("Invalid column range specification");
879 if (startRow > endRow) throw new IllegalArgumentException("Invalid row range specification");
880
881 HSSFSheet sheet = getSheetAt(sheetIndex);
882 short externSheetIndex = getWorkbook().checkExternSheet(sheetIndex);
883
884 boolean settingRowAndColumn =
885 startColumn != -1 && endColumn != -1 && startRow != -1 && endRow != -1;
886 boolean removingRange =
887 startColumn == -1 && endColumn == -1 && startRow == -1 && endRow == -1;
888
889 boolean isNewRecord = false;
890 NameRecord nameRecord;
891 nameRecord = findExistingRowColHeaderNameRecord(sheetIndex);
892 if (removingRange )
893 {
894 if (nameRecord != null)
895 workbook.removeName(findExistingRowColHeaderNameRecordIdx(sheetIndex+1));
896 return;
897 }
898 if ( nameRecord == null )
899 {
900 nameRecord = workbook.createBuiltInName(NameRecord.BUILTIN_PRINT_TITLE, sheetIndex+1);
901 //does a lot of the house keeping for builtin records, like setting lengths to zero etc
902 isNewRecord = true;
903 }
904
905 short definitionTextLength = settingRowAndColumn ? (short)0x001a : (short)0x000b;
906 nameRecord.setDefinitionTextLength(definitionTextLength);
907
908 Stack ptgs = new Stack();
909
910 if (settingRowAndColumn)
911 {
912 ptgs.add(new MemFuncPtg(23)); // TODO - where did constant '23' come from?
913 }
914 if (startColumn >= 0)
915 {
916 Area3DPtg area3DPtg1 = new Area3DPtg();
917 area3DPtg1.setExternSheetIndex(externSheetIndex);
918 area3DPtg1.setFirstColumn((short)startColumn);
919 area3DPtg1.setLastColumn((short)endColumn);
920 area3DPtg1.setFirstRow((short)0);
921 area3DPtg1.setLastRow((short)0xFFFF);
922 ptgs.add(area3DPtg1);
923 }
924 if (startRow >= 0)
925 {
926 Area3DPtg area3DPtg2 = new Area3DPtg();
927 area3DPtg2.setExternSheetIndex(externSheetIndex);
928 area3DPtg2.setFirstColumn((short)0);
929 area3DPtg2.setLastColumn((short)0x00FF);
930 area3DPtg2.setFirstRow((short)startRow);
931 area3DPtg2.setLastRow((short)endRow);
932 ptgs.add(area3DPtg2);
933 }
934 if (settingRowAndColumn)
935 {
936 ptgs.add(UnionPtg.instance);
937 }
938 nameRecord.setNameDefinition(ptgs);
939
940 if (isNewRecord)
941 {
942 HSSFName newName = new HSSFName(this, nameRecord);
943 names.add(newName);
944 }
945
946 HSSFPrintSetup printSetup = sheet.getPrintSetup();
947 printSetup.setValidSettings(false);
948
949 sheet.setActive(true);
950 }
951
952 private NameRecord findExistingRowColHeaderNameRecord( int sheetIndex )
953 {
954 int index = findExistingRowColHeaderNameRecordIdx(sheetIndex);
955 if (index == -1)
956 return null;
957 else
958 return (NameRecord)workbook.findNextRecordBySid(NameRecord.sid, index);
959 }
960
961 private int findExistingRowColHeaderNameRecordIdx( int sheetIndex )
962 {
963 int index = 0;
964 NameRecord r = null;
965 while ((r = (NameRecord) workbook.findNextRecordBySid(NameRecord.sid, index)) != null)
966 {
967 int indexToSheet = r.getEqualsToIndexToSheet() -1;
968 if(indexToSheet > -1) { //ignore "GLOBAL" name records
969 int nameRecordSheetIndex = workbook.getSheetIndexFromExternSheetIndex(indexToSheet);
970 if (isRowColHeaderRecord( r ) && nameRecordSheetIndex == sheetIndex)
971 {
972 return index;
973 }
974 }
975 index++;
976 }
977
978 return -1;
979 }
980
981 private boolean isRowColHeaderRecord( NameRecord r )
982 {
983 return r.getOptionFlag() == 0x20 && ("" + ((char)7)).equals(r.getNameText());
984 }
985
986 /**
987 * create a new Font and add it to the workbook's font table
988 * @return new font object
989 */
990
991 public HSSFFont createFont()
992 {
993 FontRecord font = workbook.createNewFont();
994 short fontindex = (short) (getNumberOfFonts() - 1);
995
996 if (fontindex > 3)
997 {
998 fontindex++; // THERE IS NO FOUR!!
999 }
1000 if(fontindex == Short.MAX_VALUE){
1001 throw new IllegalArgumentException("Maximum number of fonts was exceeded");
1002 }
1003 HSSFFont retval = new HSSFFont(fontindex, font);
1004
1005 return retval;
1006 }
1007
1008 /**
1009 * Finds a font that matches the one with the supplied attributes
1010 */
1011 public HSSFFont findFont(short boldWeight, short color, short fontHeight,
1012 String name, boolean italic, boolean strikeout,
1013 short typeOffset, byte underline)
1014 {
1015 // System.out.println( boldWeight + ", " + color + ", " + fontHeight + ", " + name + ", " + italic + ", " + strikeout + ", " + typeOffset + ", " + underline );
1016 for (short i = 0; i < workbook.getNumberOfFontRecords(); i++)
1017 {
1018 if (i == 4)
1019 continue;
1020
1021 FontRecord font = workbook.getFontRecordAt(i);
1022 HSSFFont hssfFont = new HSSFFont(i, font);
1023 // System.out.println( hssfFont.getBoldweight() + ", " + hssfFont.getColor() + ", " + hssfFont.getFontHeight() + ", " + hssfFont.getFontName() + ", " + hssfFont.getItalic() + ", " + hssfFont.getStrikeout() + ", " + hssfFont.getTypeOffset() + ", " + hssfFont.getUnderline() );
1024 if (hssfFont.getBoldweight() == boldWeight
1025 && hssfFont.getColor() == color
1026 && hssfFont.getFontHeight() == fontHeight
1027 && hssfFont.getFontName().equals(name)
1028 && hssfFont.getItalic() == italic
1029 && hssfFont.getStrikeout() == strikeout
1030 && hssfFont.getTypeOffset() == typeOffset
1031 && hssfFont.getUnderline() == underline)
1032 {
1033 // System.out.println( "Found font" );
1034 return hssfFont;
1035 }
1036 }
1037
1038 // System.out.println( "No font found" );
1039 return null;
1040 }
1041
1042 /**
1043 * get the number of fonts in the font table
1044 * @return number of fonts
1045 */
1046
1047 public short getNumberOfFonts()
1048 {
1049 return (short) workbook.getNumberOfFontRecords();
1050 }
1051
1052 /**
1053 * get the font at the given index number
1054 * @param idx index number
1055 * @return HSSFFont at the index
1056 */
1057
1058 public HSSFFont getFontAt(short idx)
1059 {
1060 FontRecord font = workbook.getFontRecordAt(idx);
1061 HSSFFont retval = new HSSFFont(idx, font);
1062
1063 return retval;
1064 }
1065
1066 /**
1067 * create a new Cell style and add it to the workbook's style table
1068 * @return the new Cell Style object
1069 */
1070
1071 public HSSFCellStyle createCellStyle()
1072 {
1073 ExtendedFormatRecord xfr = workbook.createCellXF();
1074 short index = (short) (getNumCellStyles() - 1);
1075 HSSFCellStyle style = new HSSFCellStyle(index, xfr, this);
1076
1077 return style;
1078 }
1079
1080 /**
1081 * get the number of styles the workbook contains
1082 * @return count of cell styles
1083 */
1084
1085 public short getNumCellStyles()
1086 {
1087 return (short) workbook.getNumExFormats();
1088 }
1089
1090 /**
1091 * get the cell style object at the given index
1092 * @param idx index within the set of styles
1093 * @return HSSFCellStyle object at the index
1094 */
1095
1096 public HSSFCellStyle getCellStyleAt(short idx)
1097 {
1098 ExtendedFormatRecord xfr = workbook.getExFormatAt(idx);
1099 HSSFCellStyle style = new HSSFCellStyle(idx, xfr, this);
1100
1101 return style;
1102 }
1103
1104 /**
1105 * Method write - write out this workbook to an Outputstream. Constructs
1106 * a new POI POIFSFileSystem, passes in the workbook binary representation and
1107 * writes it out.
1108 *
1109 * @param stream - the java OutputStream you wish to write the XLS to
1110 *
1111 * @exception IOException if anything can't be written.
1112 * @see org.apache.poi.poifs.filesystem.POIFSFileSystem
1113 */
1114
1115 public void write(OutputStream stream)
1116 throws IOException
1117 {
1118 byte[] bytes = getBytes();
1119 POIFSFileSystem fs = new POIFSFileSystem();
1120
1121 // For tracking what we've written out, used if we're
1122 // going to be preserving nodes
1123 List excepts = new ArrayList(1);
1124
1125 // Write out the Workbook stream
1126 fs.createDocument(new ByteArrayInputStream(bytes), "Workbook");
1127
1128 // Write out our HPFS properties, if we have them
1129 writeProperties(fs, excepts);
1130
1131 if (preserveNodes) {
1132 // Don't write out the old Workbook, we'll be doing our new one
1133 excepts.add("Workbook");
1134 // If the file had WORKBOOK instead of Workbook, we'll write it
1135 // out correctly shortly, so don't include the old one
1136 excepts.add("WORKBOOK");
1137
1138 // Copy over all the other nodes to our new poifs
1139 copyNodes(this.filesystem,fs,excepts);
1140 }
1141 fs.writeFilesystem(stream);
1142 //poifs.writeFilesystem(stream);
1143 }
1144
1145 /**
1146 * Method getBytes - get the bytes of just the HSSF portions of the XLS file.
1147 * Use this to construct a POI POIFSFileSystem yourself.
1148 *
1149 *
1150 * @return byte[] array containing the binary representation of this workbook and all contained
1151 * sheets, rows, cells, etc.
1152 *
1153 * @see org.apache.poi.hssf.model.Workbook
1154 * @see org.apache.poi.hssf.model.Sheet
1155 */
1156
1157 public byte[] getBytes()
1158 {
1159 if (log.check( POILogger.DEBUG )) {
1160 log.log(DEBUG, "HSSFWorkbook.getBytes()");
1161 }
1162
1163 HSSFSheet[] sheets = getSheets();
1164 int nSheets = sheets.length;
1165
1166 // before getting the workbook size we must tell the sheets that
1167 // serialization is about to occur.
1168 for (int i = 0; i < nSheets; i++) {
1169 sheets[i].getSheet().preSerialize();
1170 }
1171
1172 int totalsize = workbook.getSize();
1173
1174 // pre-calculate all the sheet sizes and set BOF indexes
1175 int[] estimatedSheetSizes = new int[nSheets];
1176 for (int k = 0; k < nSheets; k++) {
1177 workbook.setSheetBof(k, totalsize);
1178 int sheetSize = sheets[k].getSheet().getSize();
1179 estimatedSheetSizes[k] = sheetSize;
1180 totalsize += sheetSize;
1181 }
1182
1183
1184 byte[] retval = new byte[totalsize];
1185 int pos = workbook.serialize(0, retval);
1186
1187 for (int k = 0; k < nSheets; k++) {
1188 int serializedSize = sheets[k].getSheet().serialize(pos, retval);
1189 if (serializedSize != estimatedSheetSizes[k]) {
1190 // Wrong offset values have been passed in the call to setSheetBof() above.
1191 // For books with more than one sheet, this discrepancy would cause excel
1192 // to report errors and loose data while reading the workbook
1193 throw new IllegalStateException("Actual serialized sheet size (" + serializedSize
1194 + ") differs from pre-calculated size (" + estimatedSheetSizes[k]
1195 + ") for sheet (" + k + ")");
1196 // TODO - add similar sanity check to ensure that Sheet.serializeIndexRecord() does not write mis-aligned offsets either
1197 }
1198 pos += serializedSize;
1199 }
1200 return retval;
1201 }
1202
1203 /** @deprecated Do not call this method from your applications. Use the methods
1204 * available in the HSSFRow to add string HSSFCells
1205 */
1206 public int addSSTString(String string)
1207 {
1208 return workbook.addSSTString(new UnicodeString(string));
1209 }
1210
1211 /** @deprecated Do not call this method from your applications. Use the methods
1212 * available in the HSSFRow to get string HSSFCells
1213 */
1214 public String getSSTString(int index)
1215 {
1216 return workbook.getSSTString(index).getString();
1217 }
1218
1219 protected Workbook getWorkbook()
1220 {
1221 return workbook;
1222 }
1223
1224 /** gets the total number of named ranges in the workboko
1225 * @return number of named ranges
1226 */
1227 public int getNumberOfNames(){
1228 int result = names.size();
1229 return result;
1230 }
1231
1232 /** gets the Named range
1233 * @param index position of the named range
1234 * @return named range high level
1235 */
1236 public HSSFName getNameAt(int index){
1237 HSSFName result = (HSSFName) names.get(index);
1238
1239 return result;
1240 }
1241
1242 /** gets the named range name
1243 * @param index the named range index (0 based)
1244 * @return named range name
1245 */
1246 public String getNameName(int index){
1247 String result = getNameAt(index).getNameName();
1248
1249 return result;
1250 }
1251
1252 /**
1253 * TODO - make this less cryptic / move elsewhere
1254 * @param refIndex Index to REF entry in EXTERNSHEET record in the Link Table
1255 * @param definedNameIndex zero-based to DEFINEDNAME or EXTERNALNAME record
1256 * @return the string representation of the defined or external name
1257 */
1258 public String resolveNameXText(int refIndex, int definedNameIndex) {
1259 return workbook.resolveNameXText(refIndex, definedNameIndex);
1260 }
1261
1262
1263 /**
1264 * Sets the printarea for the sheet provided
1265 * <p>
1266 * i.e. Reference = $A$1:$B$2
1267 * @param sheetIndex Zero-based sheet index (0 Represents the first sheet to keep consistent with java)
1268 * @param reference Valid name Reference for the Print Area
1269 */
1270 public void setPrintArea(int sheetIndex, String reference)
1271 {
1272 NameRecord name = workbook.getSpecificBuiltinRecord(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1);
1273
1274
1275 if (name == null)
1276 name = workbook.createBuiltInName(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1);
1277 //adding one here because 0 indicates a global named region; doesnt make sense for print areas
1278
1279 short externSheetIndex = getWorkbook().checkExternSheet(sheetIndex);
1280 name.setExternSheetNumber(externSheetIndex);
1281 name.setAreaReference(reference);
1282
1283
1284 }
1285
1286 /**
1287 * For the Convenience of Java Programmers maintaining pointers.
1288 * @see #setPrintArea(int, String)
1289 * @param sheetIndex Zero-based sheet index (0 = First Sheet)
1290 * @param startColumn Column to begin printarea
1291 * @param endColumn Column to end the printarea
1292 * @param startRow Row to begin the printarea
1293 * @param endRow Row to end the printarea
1294 */
1295 public void setPrintArea(int sheetIndex, int startColumn, int endColumn,
1296 int startRow, int endRow) {
1297
1298 //using absolute references because they don't get copied and pasted anyway
1299 CellReference cell = new CellReference(startRow, startColumn, true, true);
1300 String reference = cell.formatAsString();
1301
1302 cell = new CellReference(endRow, endColumn, true, true);
1303 reference = reference+":"+cell.formatAsString();
1304
1305 setPrintArea(sheetIndex, reference);
1306 }
1307
1308
1309 /**
1310 * Retrieves the reference for the printarea of the specified sheet, the sheet name is appended to the reference even if it was not specified.
1311 * @param sheetIndex Zero-based sheet index (0 Represents the first sheet to keep consistent with java)
1312 * @return String Null if no print area has been defined
1313 */
1314 public String getPrintArea(int sheetIndex)
1315 {
1316 NameRecord name = workbook.getSpecificBuiltinRecord(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1);
1317 if (name == null) return null;
1318 //adding one here because 0 indicates a global named region; doesnt make sense for print areas
1319
1320 return name.getAreaReference(this);
1321 }
1322
1323 /**
1324 * Delete the printarea for the sheet specified
1325 * @param sheetIndex Zero-based sheet index (0 = First Sheet)
1326 */
1327 public void removePrintArea(int sheetIndex) {
1328 getWorkbook().removeBuiltinRecord(NameRecord.BUILTIN_PRINT_AREA, sheetIndex+1);
1329 }
1330
1331 /** creates a new named range and add it to the model
1332 * @return named range high level
1333 */
1334 public HSSFName createName(){
1335 NameRecord nameRecord = workbook.createName();
1336
1337 HSSFName newName = new HSSFName(this, nameRecord);
1338
1339 names.add(newName);
1340
1341 return newName;
1342 }
1343
1344 /** gets the named range index by his name
1345 * <i>Note:</i>Excel named ranges are case-insensitive and
1346 * this method performs a case-insensitive search.
1347 *
1348 * @param name named range name
1349 * @return named range index
1350 */
1351 public int getNameIndex(String name)
1352 {
1353 int retval = -1;
1354
1355 for (int k = 0; k < names.size(); k++)
1356 {
1357 String nameName = getNameName(k);
1358
1359 if (nameName.equalsIgnoreCase(name))
1360 {
1361 retval = k;
1362 break;
1363 }
1364 }
1365 return retval;
1366 }
1367
1368
1369 /** remove the named range by his index
1370 * @param index named range index (0 based)
1371 */
1372 public void removeName(int index){
1373 names.remove(index);
1374 workbook.removeName(index);
1375 }
1376
1377 /**
1378 * Returns the instance of HSSFDataFormat for this workbook.
1379 * @return the HSSFDataFormat object
1380 * @see org.apache.poi.hssf.record.FormatRecord
1381 * @see org.apache.poi.hssf.record.Record
1382 */
1383 public HSSFDataFormat createDataFormat() {
1384 if (formatter == null)
1385 formatter = new HSSFDataFormat(workbook);
1386 return formatter;
1387 }
1388
1389 /** remove the named range by his name
1390 * @param name named range name
1391 */
1392 public void removeName(String name){
1393 int index = getNameIndex(name);
1394
1395 removeName(index);
1396
1397 }
1398
1399 public HSSFPalette getCustomPalette()
1400 {
1401 return new HSSFPalette(workbook.getCustomPalette());
1402 }
1403
1404 /** Test only. Do not use */
1405 public void insertChartRecord()
1406 {
1407 int loc = workbook.findFirstRecordLocBySid(SSTRecord.sid);
1408 byte[] data = {
1409 (byte)0x0F, (byte)0x00, (byte)0x00, (byte)0xF0, (byte)0x52,
1410 (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x00,
1411 (byte)0x06, (byte)0xF0, (byte)0x18, (byte)0x00, (byte)0x00,
1412 (byte)0x00, (byte)0x01, (byte)0x08, (byte)0x00, (byte)0x00,
1413 (byte)0x02, (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x02,
1414 (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x01, (byte)0x00,
1415 (byte)0x00, (byte)0x00, (byte)0x01, (byte)0x00, (byte)0x00,
1416 (byte)0x00, (byte)0x03, (byte)0x00, (byte)0x00, (byte)0x00,
1417 (byte)0x33, (byte)0x00, (byte)0x0B, (byte)0xF0, (byte)0x12,
1418 (byte)0x00, (byte)0x00, (byte)0x00, (byte)0xBF, (byte)0x00,
1419 (byte)0x08, (byte)0x00, (byte)0x08, (byte)0x00, (byte)0x81,
1420 (byte)0x01, (byte)0x09, (byte)0x00, (byte)0x00, (byte)0x08,
1421 (byte)0xC0, (byte)0x01, (byte)0x40, (byte)0x00, (byte)0x00,
1422 (byte)0x08, (byte)0x40, (byte)0x00, (byte)0x1E, (byte)0xF1,
1423 (byte)0x10, (byte)0x00, (byte)0x00, (byte)0x00, (byte)0x0D,
1424 (byte)0x00, (byte)0x00, (byte)0x08, (byte)0x0C, (byte)0x00,
1425 (byte)0x00, (byte)0x08, (byte)0x17, (byte)0x00, (byte)0x00,
1426 (byte)0x08, (byte)0xF7, (byte)0x00, (byte)0x00, (byte)0x10,
1427 };
1428 UnknownRecord r = new UnknownRecord((short)0x00EB, data);
1429 workbook.getRecords().add(loc, r);
1430 }
1431
1432 /**
1433 * Spits out a list of all the drawing records in the workbook.
1434 */
1435 public void dumpDrawingGroupRecords(boolean fat)
1436 {
1437 DrawingGroupRecord r = (DrawingGroupRecord) workbook.findFirstRecordBySid( DrawingGroupRecord.sid );
1438 r.decode();
1439 List escherRecords = r.getEscherRecords();
1440 PrintWriter w = new PrintWriter(System.out);
1441 for ( Iterator iterator = escherRecords.iterator(); iterator.hasNext(); )
1442 {
1443 EscherRecord escherRecord = (EscherRecord) iterator.next();
1444 if (fat)
1445 System.out.println(escherRecord.toString());
1446 else
1447 escherRecord.display(w, 0);
1448 }
1449 w.flush();
1450 }
1451
1452 /**
1453 * Adds a picture to the workbook.
1454 *
1455 * @param pictureData The bytes of the picture
1456 * @param format The format of the picture. One of <code>PICTURE_TYPE_*</code>
1457 *
1458 * @return the index to this picture (1 based).
1459 */
1460 public int addPicture(byte[] pictureData, int format)
1461 {
1462 byte[] uid = newUID();
1463 EscherBitmapBlip blipRecord = new EscherBitmapBlip();
1464 blipRecord.setRecordId( (short) ( EscherBitmapBlip.RECORD_ID_START + format ) );
1465 switch (format)
1466 {
1467 case PICTURE_TYPE_EMF:
1468 blipRecord.setOptions(HSSFPictureData.MSOBI_EMF);
1469 break;
1470 case PICTURE_TYPE_WMF:
1471 blipRecord.setOptions(HSSFPictureData.MSOBI_WMF);
1472 break;
1473 case PICTURE_TYPE_PICT:
1474 blipRecord.setOptions(HSSFPictureData.MSOBI_PICT);
1475 break;
1476 case PICTURE_TYPE_PNG:
1477 blipRecord.setOptions(HSSFPictureData.MSOBI_PNG);
1478 break;
1479 case HSSFWorkbook.PICTURE_TYPE_JPEG:
1480 blipRecord.setOptions(HSSFPictureData.MSOBI_JPEG);
1481 break;
1482 case HSSFWorkbook.PICTURE_TYPE_DIB:
1483 blipRecord.setOptions(HSSFPictureData.MSOBI_DIB);
1484 break;
1485 }
1486
1487 blipRecord.setUID( uid );
1488 blipRecord.setMarker( (byte) 0xFF );
1489 blipRecord.setPictureData( pictureData );
1490
1491 EscherBSERecord r = new EscherBSERecord();
1492 r.setRecordId( EscherBSERecord.RECORD_ID );
1493 r.setOptions( (short) ( 0x0002 | ( format << 4 ) ) );
1494 r.setBlipTypeMacOS( (byte) format );
1495 r.setBlipTypeWin32( (byte) format );
1496 r.setUid( uid );
1497 r.setTag( (short) 0xFF );
1498 r.setSize( pictureData.length + 25 );
1499 r.setRef( 1 );
1500 r.setOffset( 0 );
1501 r.setBlipRecord( blipRecord );
1502
1503 return workbook.addBSERecord( r );
1504 }
1505
1506 /**
1507 * Gets all pictures from the Workbook.
1508 *
1509 * @return the list of pictures (a list of {@link HSSFPictureData} objects.)
1510 */
1511 public List getAllPictures()
1512 {
1513 // The drawing group record always exists at the top level, so we won't need to do this recursively.
1514 List pictures = new ArrayList();
1515 Iterator recordIter = workbook.getRecords().iterator();
1516 while (recordIter.hasNext())
1517 {
1518 Object obj = recordIter.next();
1519 if (obj instanceof AbstractEscherHolderRecord)
1520 {
1521 ((AbstractEscherHolderRecord) obj).decode();
1522 List escherRecords = ((AbstractEscherHolderRecord) obj).getEscherRecords();
1523 searchForPictures(escherRecords, pictures);
1524 }
1525 }
1526 return pictures;
1527 }
1528
1529 /**
1530 * Performs a recursive search for pictures in the given list of escher records.
1531 *
1532 * @param escherRecords the escher records.
1533 * @param pictures the list to populate with the pictures.
1534 */
1535 private void searchForPictures(List escherRecords, List pictures)
1536 {
1537 Iterator recordIter = escherRecords.iterator();
1538 while (recordIter.hasNext())
1539 {
1540 Object obj = recordIter.next();
1541 if (obj instanceof EscherRecord)
1542 {
1543 EscherRecord escherRecord = (EscherRecord) obj;
1544
1545 if (escherRecord instanceof EscherBSERecord)
1546 {
1547 EscherBlipRecord blip = ((EscherBSERecord) escherRecord).getBlipRecord();
1548 if (blip != null)
1549 {
1550 // TODO: Some kind of structure.
1551 pictures.add(new HSSFPictureData(blip));
1552 }
1553 }
1554
1555 // Recursive call.
1556 searchForPictures(escherRecord.getChildRecords(), pictures);
1557 }
1558 }
1559 }
1560
1561 /**
1562 * Is the workbook protected with a password (not encrypted)?
1563 */
1564 public boolean isWriteProtected() {
1565 return this.workbook.isWriteProtected();
1566 }
1567
1568 /**
1569 * protect a workbook with a password (not encypted, just sets writeprotect
1570 * flags and the password.
1571 * @param password to set
1572 */
1573 public void writeProtectWorkbook( String password, String username ) {
1574 this.workbook.writeProtectWorkbook(password, username);
1575 }
1576
1577 /**
1578 * removes the write protect flag
1579 */
1580 public void unwriteProtectWorkbook() {
1581 this.workbook.unwriteProtectWorkbook();
1582 }
1583
1584 /**
1585 * Gets all embedded OLE2 objects from the Workbook.
1586 *
1587 * @return the list of embedded objects (a list of {@link HSSFObjectData} objects.)
1588 */
1589 public List getAllEmbeddedObjects()
1590 {
1591 List objects = new ArrayList();
1592 for (int i = 0; i < getNumberOfSheets(); i++)
1593 {
1594 getAllEmbeddedObjects(getSheetAt(i).getSheet().getRecords(), objects);
1595 }
1596 return objects;
1597 }
1598
1599 /**
1600 * Gets all embedded OLE2 objects from the Workbook.
1601 *
1602 * @param records the list of records to search.
1603 * @param objects the list of embedded objects to populate.
1604 */
1605 private void getAllEmbeddedObjects(List records, List objects)
1606 {
1607 Iterator recordIter = records.iterator();
1608 while (recordIter.hasNext())
1609 {
1610 Object obj = recordIter.next();
1611 if (obj instanceof ObjRecord)
1612 {
1613 // TODO: More convenient way of determining if there is stored binary.
1614 // TODO: Link to the data stored in the other stream.
1615 Iterator subRecordIter = ((ObjRecord) obj).getSubRecords().iterator();
1616 while (subRecordIter.hasNext())
1617 {
1618 Object sub = subRecordIter.next();
1619 if (sub instanceof EmbeddedObjectRefSubRecord)
1620 {
1621 objects.add(new HSSFObjectData((ObjRecord) obj, filesystem));
1622 }
1623 }
1624 }
1625 }
1626 }
1627
1628 private byte[] newUID()
1629 {
1630 byte[] bytes = new byte[16];
1631 return bytes;
1632 }
1633 }
Save This Page