SheetReader


1   /*********************************************************************
2   *
3   *      Copyright (C) 2002 Andrew Khan
4   *
5   * This library is free software; you can redistribute it and/or
6   * modify it under the terms of the GNU Lesser General Public
7   * License as published by the Free Software Foundation; either
8   * version 2.1 of the License, or (at your option) any later version.
9   *
10  * This library is distributed in the hope that it will be useful,
11  * but WITHOUT ANY WARRANTY; without even the implied warranty of
12  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
13  * Lesser General Public License for more details.
14  *
15  * You should have received a copy of the GNU Lesser General Public
16  * License along with this library; if not, write to the Free Software
17  * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
18  ***************************************************************************/
19  
20  package jxl.read.biff;
21  
22  import java.util.ArrayList  ;
23  import java.util.Iterator  ;
24  import java.util.HashMap  ;
25  
26  import common.Assert;
27  import common.Logger;
28  
29  import jxl.Cell;
30  import jxl.DateCell;
31  import jxl.CellType;
32  import jxl.Range;
33  import jxl.SheetSettings;
34  import jxl.WorkbookSettings;
35  import jxl.CellReferenceHelper;
36  import jxl.HeaderFooter;
37  import jxl.CellFeatures;
38  import jxl.biff.Type;
39  import jxl.biff.FormattingRecords;
40  import jxl.biff.ContinueRecord;
41  import jxl.biff.WorkspaceInformationRecord;
42  import jxl.biff.drawing.Chart;
43  import jxl.format.PaperSize;
44  import jxl.format.PageOrientation;
45  import jxl.biff.formula.FormulaException;
46  import jxl.biff.drawing.ObjRecord;
47  import jxl.biff.drawing.MsoDrawingRecord;
48  import jxl.biff.drawing.Drawing;
49  import jxl.biff.drawing.Comment;
50  import jxl.biff.drawing.Button;
51  import jxl.biff.drawing.DrawingData;
52  import jxl.biff.drawing.TextObjectRecord;
53  import jxl.biff.drawing.NoteRecord;
54  
55  /**
56   * Reads the sheet.  This functionality was originally part of the
57   * SheetImpl class, but was separated out in order to simplify the former
58   * class
59   */
60  final class SheetReader
61  {
62    /**
63     * The logger
64     */
65    private static Logger logger = Logger.getLogger(SheetReader.class);
66  
67    /**
68     * The excel file
69     */
70    private File excelFile;
71  
72    /**
73     * A handle to the shared string table
74     */
75    private SSTRecord sharedStrings;
76  
77    /**
78     * A handle to the sheet BOF record, which indicates the stream type
79     */
80    private BOFRecord sheetBof;
81  
82    /**
83     * A handle to the workbook BOF record, which indicates the stream type
84     */
85    private BOFRecord workbookBof;
86  
87    /**
88     * A handle to the formatting records
89     */
90    private FormattingRecords formattingRecords;
91  
92    /**
93     * The  number of rows
94     */
95    private int numRows;
96  
97    /**
98     * The number of columns
99     */
100   private int numCols;
101 
102   /**
103    * The cells
104    */
105   private Cell[][] cells;
106 
107   /**
108    * The start position in the stream of this sheet
109    */
110   private int startPosition;
111 
112   /**
113    * The list of non-default row properties
114    */
115   private ArrayList   rowProperties;
116 
117   /**
118    * An array of column info records.  They are held this way before
119    * they are transferred to the more convenient array
120    */
121   private ArrayList   columnInfosArray;
122 
123   /**
124    * A list of shared formula groups
125    */
126   private ArrayList   sharedFormulas;
127 
128   /**
129    * A list of hyperlinks on this page
130    */
131   private ArrayList   hyperlinks;
132 
133   /**
134    * A list of merged cells on this page
135    */
136   private Range[] mergedCells;
137 
138   /**
139    * The list of data validations on this page
140    */
141   private DataValidation dataValidation;
142 
143   /**
144    * The list of charts on this page
145    */
146   private ArrayList   charts;
147 
148   /**
149    * The list of drawings on this page
150    */
151   private ArrayList   drawings;
152 
153   /**
154    * The drawing data for the drawings
155    */
156   private DrawingData drawingData;
157 
158   /**
159    * Indicates whether or not the dates are based around the 1904 date system
160    */
161   private boolean nineteenFour;
162 
163   /**
164    * The PLS print record
165    */
166   private PLSRecord plsRecord;
167 
168   /**
169    * The property set record associated with this workbook
170    */
171   private ButtonPropertySetRecord buttonPropertySet;
172 
173   /**
174    * The workspace options
175    */
176   private WorkspaceInformationRecord workspaceOptions;
177 
178   /**
179    * The horizontal page breaks contained on this sheet
180    */
181   private int[] rowBreaks;
182 
183   /**
184    * The sheet settings
185    */
186   private SheetSettings settings;
187 
188   /**
189    * The workbook settings
190    */
191   private WorkbookSettings workbookSettings;
192 
193   /**
194    * A handle to the workbook which contains this sheet.  Some of the records
195    * need this in order to reference external sheets
196    */
197   private WorkbookParser workbook;
198 
199   /**
200    * A handle to the sheet
201    */
202   private SheetImpl sheet;
203 
204   /**
205    * Constructor
206    *
207    * @param fr the formatting records
208    * @param sst the shared string table
209    * @param f the excel file
210    * @param sb the bof record which indicates the start of the sheet
211    * @param wb the bof record which indicates the start of the sheet
212    * @param wp the workbook which this sheet belongs to
213    * @param sp the start position of the sheet bof in the excel file
214    * @param sh the sheet
215    * @param nf 1904 date record flag
216    * @exception BiffException
217    */
218   SheetReader(File f,
219               SSTRecord sst,
220               FormattingRecords fr,
221               BOFRecord sb,
222               BOFRecord wb,
223               boolean nf,
224               WorkbookParser wp,
225               int sp,
226               SheetImpl sh)
227   {
228     excelFile = f;
229     sharedStrings = sst;
230     formattingRecords = fr;
231     sheetBof = sb;
232     workbookBof = wb;
233     columnInfosArray = new ArrayList  ();
234     sharedFormulas = new ArrayList  ();
235     hyperlinks = new ArrayList  ();
236     rowProperties = new ArrayList  (10);
237     charts = new ArrayList  ();
238     drawings = new ArrayList  ();
239     nineteenFour = nf;
240     workbook = wp;
241     startPosition = sp;
242     sheet = sh;
243     settings = new SheetSettings();
244     workbookSettings = workbook.getSettings();
245   }
246 
247   /**
248    * Adds the cell to the array
249    *
250    * @param cell the cell to add
251    */
252   private void addCell(Cell cell)
253   {
254     // Sometimes multiple cells (eg. MULBLANK) can exceed the
255     // column/row boundaries.  Ignore these
256 
257     if (cell.getRow() < numRows && cell.getColumn() < numCols)
258     {
259       if (cells[cell.getRow()][cell.getColumn()] != null)
260       {
261         StringBuffer   sb = new StringBuffer  ();
262         CellReferenceHelper.getCellReference
263           (cell.getColumn(), cell.getRow(), sb);
264         logger.warn("Cell " + sb.toString() +
265                       " already contains data");
266       }
267       cells[cell.getRow()][cell.getColumn()] = cell;
268     }
269     else
270     {
271       logger.warn("Cell " +
272                   CellReferenceHelper.getCellReference
273                                    (cell.getColumn(), cell.getRow()) +
274                   " exceeds defined cell boundaries in Dimension record " +
275                   "(" + numCols + "x" + numRows + ")");
276     }
277   }
278 
279   /**
280    * Reads in the contents of this sheet
281    */
282   final void read()
283   {
284     Record r = null;
285     BaseSharedFormulaRecord sharedFormula = null;
286     boolean sharedFormulaAdded = false;
287 
288     boolean cont = true;
289 
290     // Set the position within the file
291     excelFile.setPos(startPosition);
292 
293     // Handles to the last drawing and obj records
294     MsoDrawingRecord msoRecord = null;
295     ObjRecord objRecord = null;
296     boolean firstMsoRecord = true;
297 
298     // A handle to window2 record
299     Window2Record window2Record = null;
300 
301     // A handle to printgridlines record
302     PrintGridLinesRecord printGridLinesRecord = null;
303 
304     // A handle to printheaders record
305     PrintHeadersRecord printHeadersRecord = null;
306 
307     // Hash map of comments, indexed on objectId.  As each corresponding
308     // note record is encountered, these are removed from the array
309     HashMap   comments = new HashMap  ();
310 
311     while (cont)
312     {
313       r = excelFile.next();
314 
315       if (r.getType() == Type.UNKNOWN && r.getCode() == 0)
316       {
317         logger.warn("Biff code zero found");
318 
319         // Try a dimension record
320         if (r.getLength() == 0xa)
321         {
322           logger.warn("Biff code zero found - trying a dimension record.");
323           r.setType(Type.DIMENSION);
324         }
325         else
326         {
327           logger.warn("Biff code zero found - Ignoring.");
328         }
329       }
330 
331       if (r.getType() == Type.DIMENSION)
332       {
333         DimensionRecord dr = null;
334 
335         if (workbookBof.isBiff8())
336         {
337           dr = new DimensionRecord(r);
338         }
339         else
340         {
341           dr = new DimensionRecord(r, DimensionRecord.biff7);
342         }
343         numRows = dr.getNumberOfRows();
344         numCols = dr.getNumberOfColumns();
345         cells = new Cell[numRows][numCols];
346       }
347       else if (r.getType() == Type.LABELSST)
348       {
349         LabelSSTRecord label = new LabelSSTRecord(r,
350                                                   sharedStrings,
351                                                   formattingRecords,
352                                                   sheet);
353         addCell(label);
354       }
355       else if (r.getType() == Type.RK || r.getType() == Type.RK2)
356       {
357         RKRecord rkr = new RKRecord(r, formattingRecords, sheet);
358 
359         if (formattingRecords.isDate(rkr.getXFIndex()))
360         {
361           DateCell dc = new DateRecord
362             (rkr, rkr.getXFIndex(), formattingRecords, nineteenFour, sheet);
363           addCell(dc);
364         }
365         else
366         {
367           addCell(rkr);
368         }
369       }
370       else if (r.getType() == Type.HLINK)
371       {
372         HyperlinkRecord hr = new HyperlinkRecord(r, sheet, workbookSettings);
373         hyperlinks.add(hr);
374       }
375       else if (r.getType() == Type.MERGEDCELLS)
376       {
377         MergedCellsRecord  mc = new MergedCellsRecord(r, sheet);
378         if (mergedCells == null)
379         {
380           mergedCells = mc.getRanges();
381         }
382         else
383         {
384           Range[] newMergedCells =
385             new Range[mergedCells.length + mc.getRanges().length];
386           System.arraycopy(mergedCells, 0, newMergedCells, 0,
387                            mergedCells.length);
388           System.arraycopy(mc.getRanges(),
389                            0,
390                            newMergedCells, mergedCells.length,
391                            mc.getRanges().length);
392           mergedCells = newMergedCells;
393         }
394       }
395       else if (r.getType() == Type.MULRK)
396       {
397         MulRKRecord mulrk = new MulRKRecord(r);
398 
399         // Get the individual cell records from the multiple record
400         int num = mulrk.getNumberOfColumns();
401         int ixf = 0;
402         for (int i = 0; i < num; i++)
403         {
404           ixf = mulrk.getXFIndex(i);
405 
406           NumberValue nv = new NumberValue
407             (mulrk.getRow(),
408              mulrk.getFirstColumn() + i,
409              RKHelper.getDouble(mulrk.getRKNumber(i)),
410              ixf,
411              formattingRecords,
412              sheet);
413 
414 
415           if (formattingRecords.isDate(ixf))
416           {
417             DateCell dc = new DateRecord(nv, 
418                                          ixf, 
419                                          formattingRecords,
420                                          nineteenFour, 
421                                          sheet);
422             addCell(dc);
423           }
424           else
425           {
426             nv.setNumberFormat(formattingRecords.getNumberFormat(ixf));
427             addCell(nv);
428           }
429         }
430       }
431       else if (r.getType() == Type.NUMBER)
432       {
433         NumberRecord nr = new NumberRecord(r, formattingRecords, sheet);
434 
435         if (formattingRecords.isDate(nr.getXFIndex()))
436         {
437           DateCell dc = new DateRecord(nr,
438                                        nr.getXFIndex(),
439                                        formattingRecords,
440                                        nineteenFour, sheet);
441           addCell(dc);
442         }
443         else
444         {
445           addCell(nr);
446         }
447       }
448       else if (r.getType() == Type.BOOLERR)
449       {
450         BooleanRecord br = new BooleanRecord(r, formattingRecords, sheet);
451 
452         if (br.isError())
453         {
454           ErrorRecord er = new ErrorRecord(br.getRecord(), formattingRecords,
455                                            sheet);
456           addCell(er);
457         }
458         else
459         {
460           addCell(br);
461         }
462       }
463       else if (r.getType() == Type.PRINTGRIDLINES)
464       {
465         printGridLinesRecord = new PrintGridLinesRecord(r);
466         settings.setPrintGridLines(printGridLinesRecord.getPrintGridLines());
467       }
468       else if (r.getType() == Type.PRINTHEADERS)
469       {
470         printHeadersRecord = new PrintHeadersRecord(r);
471         settings.setPrintHeaders(printHeadersRecord.getPrintHeaders());
472       }
473       else if (r.getType() == Type.WINDOW2)
474       {
475         window2Record = new Window2Record(r);
476 
477         settings.setShowGridLines(window2Record.getShowGridLines());
478         settings.setDisplayZeroValues(window2Record.getDisplayZeroValues());
479         settings.setSelected(true);
480       }
481       else if (r.getType() == Type.PANE)
482       {
483         PaneRecord pr = new PaneRecord(r);
484 
485         if (window2Record != null &&
486             window2Record.getFrozen())
487         {
488           settings.setVerticalFreeze(pr.getRowsVisible());
489           settings.setHorizontalFreeze(pr.getColumnsVisible());
490         }
491       }
492       else if (r.getType() == Type.CONTINUE)
493       {
494         ;
495       }
496       else if (r.getType() == Type.NOTE)
497       {
498         if (!workbookSettings.getDrawingsDisabled())
499         {
500           NoteRecord nr = new NoteRecord(r);
501 
502           // Get the comment for the object id
503           Comment comment = (Comment) comments.remove
504             (new Integer  (nr.getObjectId()));
505 
506           if (comment == null)
507           {
508             logger.warn(" cannot find comment for note id " +
509                         nr.getObjectId() + "...ignoring");
510           }
511           else
512           {
513             comment.setNote(nr);
514 
515             drawings.add(comment);
516 
517             addCellComment(comment.getColumn(),
518                            comment.getRow(),
519                            comment.getText(),
520                            comment.getWidth(),
521                            comment.getHeight());
522           }
523         }
524       }
525       else if (r.getType() == Type.ARRAY)
526       {
527         ;
528       }
529       else if (r.getType() == Type.PROTECT)
530       {
531         ProtectRecord pr = new ProtectRecord(r);
532         settings.setProtected(pr.isProtected());
533       }
534       else if (r.getType() == Type.SHAREDFORMULA)
535       {
536         if (sharedFormula == null)
537         {
538           logger.warn("Shared template formula is null - " +
539                       "trying most recent formula template");
540           SharedFormulaRecord lastSharedFormula =
541             (SharedFormulaRecord) sharedFormulas.get(sharedFormulas.size() - 1);
542 
543           if (lastSharedFormula != null)
544           {
545             sharedFormula = lastSharedFormula.getTemplateFormula();
546           }
547         }
548 
549         SharedFormulaRecord sfr = new SharedFormulaRecord
550           (r, sharedFormula, workbook, workbook, sheet);
551         sharedFormulas.add(sfr);
552         sharedFormula = null;
553       }
554       else if (r.getType() == Type.FORMULA || r.getType() == Type.FORMULA2)
555       {
556         FormulaRecord fr = new FormulaRecord(r,
557                                              excelFile,
558                                              formattingRecords,
559                                              workbook,
560                                              workbook,
561                                              sheet,
562                                              workbookSettings);
563 
564         if (fr.isShared())
565         {
566           BaseSharedFormulaRecord prevSharedFormula = sharedFormula;
567           sharedFormula = (BaseSharedFormulaRecord) fr.getFormula();
568 
569           // See if it fits in any of the shared formulas
570           sharedFormulaAdded = addToSharedFormulas(sharedFormula);
571 
572           if (sharedFormulaAdded)
573           {
574             sharedFormula = prevSharedFormula;
575           }
576 
577           // If we still haven't added the previous base shared formula,
578           // revert it to an ordinary formula and add it to the cell
579           if (!sharedFormulaAdded && prevSharedFormula != null)
580           {
581             // Do nothing.  It's possible for the biff file to contain the
582             // record sequence
583             // FORMULA-SHRFMLA-FORMULA-SHRFMLA-FORMULA-FORMULA-FORMULA
584             // ie. it first lists all the formula templates, then it
585             // lists all the individual formulas
586             addCell(revertSharedFormula(prevSharedFormula));
587           }
588         }
589         else
590         {
591           Cell cell = fr.getFormula();
592           try
593           {
594             // See if the formula evaluates to date
595             if (fr.getFormula().getType() == CellType.NUMBER_FORMULA)
596             {
597               NumberFormulaRecord nfr = (NumberFormulaRecord) fr.getFormula();
598               if (formattingRecords.isDate(nfr.getXFIndex()))
599               {
600                 cell = new DateFormulaRecord(nfr,
601                                              formattingRecords,
602                                              workbook,
603                                              workbook,
604                                              nineteenFour,
605                                              sheet);
606               }
607             }
608             
609             addCell(cell);
610           }
611           catch (FormulaException e)
612           {
613             // Something has gone wrong trying to read the formula data eg. it
614             // might be unsupported biff7 data
615             logger.warn
616               (CellReferenceHelper.getCellReference
617                (cell.getColumn(), cell.getRow()) + " " + e.getMessage());
618           }
619         }
620       }
621       else if (r.getType() == Type.LABEL)
622       {
623         LabelRecord lr = null;
624 
625         if (workbookBof.isBiff8())
626         {
627           lr = new LabelRecord(r, formattingRecords, sheet, workbookSettings);
628         }
629         else
630         {
631           lr = new LabelRecord(r, formattingRecords, sheet, workbookSettings,
632                                LabelRecord.biff7);
633         }
634         addCell(lr);
635       }
636       else if (r.getType() == Type.RSTRING)
637       {
638         RStringRecord lr = null;
639 
640         // RString records are obsolete in biff 8
641         Assert.verify(!workbookBof.isBiff8());
642         lr = new RStringRecord(r, formattingRecords,
643                                sheet, workbookSettings,
644                                RStringRecord.biff7);
645         addCell(lr);
646       }
647       else if (r.getType() == Type.NAME)
648       {
649         ;
650       }
651       else if (r.getType() == Type.PASSWORD)
652       {
653         PasswordRecord pr = new PasswordRecord(r);
654         settings.setPasswordHash(pr.getPasswordHash());
655       }
656       else if (r.getType() == Type.ROW)
657       {
658         RowRecord rr = new RowRecord(r);
659 
660         // See if the row has anything funny about it
661         if (!rr.isDefaultHeight() ||
662             !rr.matchesDefaultFontHeight() ||
663             rr.isCollapsed() ||
664             rr.hasDefaultFormat())
665         {
666           rowProperties.add(rr);
667         }
668       }
669       else if (r.getType() == Type.BLANK)
670       {
671         if (!workbookSettings.getIgnoreBlanks())
672         {
673           BlankCell bc = new BlankCell(r, formattingRecords, sheet);
674           addCell(bc);
675         }
676       }
677       else if (r.getType() == Type.MULBLANK)
678       {
679         if (!workbookSettings.getIgnoreBlanks())
680         {
681           MulBlankRecord mulblank = new MulBlankRecord(r);
682 
683           // Get the individual cell records from the multiple record
684           int num = mulblank.getNumberOfColumns();
685 
686           for (int i = 0; i < num; i++)
687           {
688             int ixf = mulblank.getXFIndex(i);
689 
690             MulBlankCell mbc = new MulBlankCell
691               (mulblank.getRow(),
692                mulblank.getFirstColumn() + i,
693                ixf,
694                formattingRecords,
695                sheet);
696             
697             addCell(mbc);
698           }
699         }
700       }
701       else if (r.getType() == Type.SCL)
702       {
703         SCLRecord scl = new SCLRecord(r);
704         settings.setZoomFactor(scl.getZoomFactor());
705       }
706       else if (r.getType() == Type.COLINFO)
707       {
708         ColumnInfoRecord cir = new ColumnInfoRecord(r);
709         columnInfosArray.add(cir);
710       }
711       else if (r.getType() == Type.HEADER)
712       {
713         HeaderRecord hr = null;
714         if (workbookBof.isBiff8())
715         {
716           hr = new HeaderRecord(r, workbookSettings);
717         }
718         else
719         {
720           hr = new HeaderRecord(r, workbookSettings, HeaderRecord.biff7);
721         }
722 
723         HeaderFooter header = new HeaderFooter(hr.getHeader());
724         settings.setHeader(header);
725       }
726       else if (r.getType() == Type.FOOTER)
727       {
728         FooterRecord fr = null;
729         if (workbookBof.isBiff8())
730         {
731           fr = new FooterRecord(r, workbookSettings);
732         }
733         else
734         {
735           fr = new FooterRecord(r, workbookSettings, FooterRecord.biff7);
736         }
737 
738         HeaderFooter footer = new HeaderFooter(fr.getFooter());
739         settings.setFooter(footer);
740       }
741       else if (r.getType() == Type.SETUP)
742       {
743         SetupRecord sr = new SetupRecord(r);
744         if (sr.isPortrait())
745         {
746           settings.setOrientation(PageOrientation.PORTRAIT);
747         }
748         else
749         {
750           settings.setOrientation(PageOrientation.LANDSCAPE);
751         }
752         settings.setPaperSize(PaperSize.getPaperSize(sr.getPaperSize()));
753         settings.setHeaderMargin(sr.getHeaderMargin());
754         settings.setFooterMargin(sr.getFooterMargin());
755         settings.setScaleFactor(sr.getScaleFactor());
756         settings.setPageStart(sr.getPageStart());
757         settings.setFitWidth(sr.getFitWidth());
758         settings.setFitHeight(sr.getFitHeight());
759         settings.setHorizontalPrintResolution
760           (sr.getHorizontalPrintResolution());
761         settings.setVerticalPrintResolution(sr.getVerticalPrintResolution());
762         settings.setCopies(sr.getCopies());
763 
764         if (workspaceOptions != null)
765         {
766           settings.setFitToPages(workspaceOptions.getFitToPages());
767         }
768       }
769       else if (r.getType() == Type.WSBOOL)
770       {
771         workspaceOptions = new WorkspaceInformationRecord(r);
772       }
773       else if (r.getType() == Type.DEFCOLWIDTH)
774       {
775         DefaultColumnWidthRecord dcwr = new DefaultColumnWidthRecord(r);
776         settings.setDefaultColumnWidth(dcwr.getWidth());
777       }
778       else if (r.getType() == Type.DEFAULTROWHEIGHT)
779       {
780         DefaultRowHeightRecord drhr = new DefaultRowHeightRecord(r);
781         if (drhr.getHeight() != 0)
782         {
783           settings.setDefaultRowHeight(drhr.getHeight());
784         }
785       }
786       else if (r.getType() == Type.LEFTMARGIN)
787       {
788         MarginRecord m = new LeftMarginRecord(r);
789         settings.setLeftMargin(m.getMargin());
790       }
791       else if (r.getType() == Type.RIGHTMARGIN)
792       {
793         MarginRecord m = new RightMarginRecord(r);
794         settings.setRightMargin(m.getMargin());
795       }
796       else if (r.getType() == Type.TOPMARGIN)
797       {
798         MarginRecord m = new TopMarginRecord(r);
799         settings.setTopMargin(m.getMargin());
800       }
801       else if (r.getType() == Type.BOTTOMMARGIN)
802       {
803         MarginRecord m = new BottomMarginRecord(r);
804         settings.setBottomMargin(m.getMargin());
805       }
806       else if (r.getType() == Type.HORIZONTALPAGEBREAKS)
807       {
808         HorizontalPageBreaksRecord dr = null;
809 
810         if (workbookBof.isBiff8())
811         {
812           dr = new HorizontalPageBreaksRecord(r);
813         }
814         else
815         {
816           dr = new HorizontalPageBreaksRecord
817             (r, HorizontalPageBreaksRecord.biff7);
818         }
819         rowBreaks = dr.getRowBreaks();
820       }
821       else if (r.getType() == Type.PLS)
822       {
823         plsRecord = new PLSRecord(r);
824       }
825       else if (r.getType() == Type.DVAL)
826       {
827         // Leave these until another day.  They need to be wrapped inside
828         // an implementation of DrawingGroupObject
829         //        DataValidityListRecord dvlr = new DataValidityListRecord(r);
830         //        dataValidation = new DataValidation(dvlr);
831       }
832       else if (r.getType() == Type.DV)
833       {
834         // Leave these until another day.  They need to be wrapped inside
835         // an implementation of DrawingGroupObject
836         //        DataValiditySettingsRecord dvsr = new DataValiditySettingsRecord(r);
837         //        dataValidation.add(dvsr);
838       }
839       else if (r.getType() == Type.OBJ)
840       {
841         objRecord = new ObjRecord(r);
842 
843         if (!workbookSettings.getDrawingsDisabled())
844         {
845           handleObjectRecord(objRecord, msoRecord, comments);
846         }
847 
848         // Save chart handling until the chart BOF record appears
849         if (objRecord.getType() != ObjRecord.CHART)
850         {
851           objRecord = null;
852           msoRecord = null;
853         }
854       }
855       else if (r.getType() == Type.MSODRAWING)
856       {
857         if (!workbookSettings.getDrawingsDisabled())
858         {
859           if (msoRecord != null)
860           {
861             // For form controls, a rogue MSODRAWING record can crop up
862             // after the main one.  Add these into the drawing data
863             drawingData.addRawData(msoRecord.getData());
864           }
865           msoRecord = new MsoDrawingRecord(r);
866 
867           if (firstMsoRecord)
868           {
869             msoRecord.setFirst();
870             firstMsoRecord = false;
871           }
872         }
873       }
874       else if (r.getType() == Type.BUTTONPROPERTYSET)
875       {
876         buttonPropertySet = new ButtonPropertySetRecord(r);
877       }
878       else if (r.getType() == Type.BOF)
879       {
880         BOFRecord br = new BOFRecord(r);
881         Assert.verify(!br.isWorksheet());
882 
883         int startpos = excelFile.getPos() - r.getLength() - 4;
884 
885         // Skip to the end of the nested bof
886         // Thanks to Rohit for spotting this
887         Record r2 = excelFile.next();
888         while (r2.getCode() != Type.EOF.value)
889         {
890           r2 = excelFile.next();
891         }
892 
893         if (br.isChart())
894         {
895           if (drawingData == null)
896           {
897             drawingData = new DrawingData();
898           }
899           
900           if (!workbookSettings.getDrawingsDisabled())
901           {
902             Chart chart = new Chart(msoRecord, objRecord, drawingData,
903                                     startpos, excelFile.getPos(),
904                                     excelFile, workbookSettings);
905             charts.add(chart);
906 
907             if (workbook.getDrawingGroup() != null)
908             {
909               workbook.getDrawingGroup().add(chart);
910             }
911           }
912 
913           // Reset the drawing records
914           msoRecord = null;
915           objRecord = null;
916         }
917 
918         // If this worksheet is just a chart, then the EOF reached
919         // represents the end of the sheet as well as the end of the chart
920         if (sheetBof.isChart())
921         {
922           cont = false;
923         }
924       }
925       else if (r.getType() == Type.EOF)
926       {
927         cont = false;
928       }
929     }
930 
931     // Restore the file to its accurate position
932     excelFile.restorePos();
933 
934     // Add all the shared formulas to the sheet as individual formulas
935     Iterator   i = sharedFormulas.iterator();
936 
937     while (i.hasNext())
938     {
939       SharedFormulaRecord sfr = (SharedFormulaRecord) i.next();
940 
941       Cell[] sfnr = sfr.getFormulas(formattingRecords, nineteenFour);
942 
943       for (int sf = 0; sf < sfnr.length; sf++)
944       {
945         addCell(sfnr[sf]);
946       }
947     }
948 
949     // If the last base shared formula wasn't added to the sheet, then
950     // revert it to an ordinary formula and add it
951     if (!sharedFormulaAdded && sharedFormula != null)
952     {
953       addCell(revertSharedFormula(sharedFormula));
954     }
955 
956     // If there is a stray msoDrawing record, then flag to the drawing group
957     // that one has been omitted
958     if (msoRecord != null && workbook.getDrawingGroup() != null)
959     {
960       workbook.getDrawingGroup().setDrawingsOmitted(msoRecord, objRecord);
961     }
962 
963     // Check that the comments hash is empty
964     if (!comments.isEmpty())
965     {
966       logger.warn("Not all comments have a corresponding Note record");
967     }
968   }
969 
970   /**
971    * Sees if the shared formula belongs to any of the shared formula
972    * groups
973    *
974    * @param fr the candidate shared formula
975    * @return TRUE if the formula was added, FALSE otherwise
976    */
977   private boolean addToSharedFormulas(BaseSharedFormulaRecord fr)
978   {
979     Iterator   i = sharedFormulas.iterator();
980     boolean added = false;
981     SharedFormulaRecord sfr = null;
982 
983     while (i.hasNext() && !added)
984     {
985       sfr = (SharedFormulaRecord) i.next();
986       added = sfr.add(fr);
987     }
988 
989     return added;
990   }
991 
992   /**
993    * Reverts the shared formula passed in to an ordinary formula and adds
994    * it to the list
995    *
996    * @param f the formula
997    * @return the new formula
998    * @exception FormulaException
999    */
1000  private Cell revertSharedFormula(BaseSharedFormulaRecord f) 
1001  {
1002    // String formulas look for a STRING record soon after the formula
1003    // occurred.  Temporarily the position in the excel file back
1004    // to the point immediately after the formula record
1005    int pos = excelFile.getPos();
1006    excelFile.setPos(f.getFilePos());
1007
1008    FormulaRecord fr = new FormulaRecord(f.getRecord(),
1009                                         excelFile,
1010                                         formattingRecords,
1011                                         workbook,
1012                                         workbook,
1013                                         FormulaRecord.ignoreSharedFormula,
1014                                         sheet,
1015                                         workbookSettings);
1016
1017    try
1018    {
1019    Cell cell = fr.getFormula();
1020
1021    // See if the formula evaluates to date
1022    if (fr.getFormula().getType() == CellType.NUMBER_FORMULA)
1023    {
1024      NumberFormulaRecord nfr = (NumberFormulaRecord) fr.getFormula();
1025      if (formattingRecords.isDate(fr.getXFIndex()))
1026      {
1027        cell = new DateFormulaRecord(nfr,
1028                                     formattingRecords,
1029                                     workbook,
1030                                     workbook,
1031                                     nineteenFour,
1032                                     sheet);
1033      }
1034    }
1035
1036    excelFile.setPos(pos);
1037    return cell;
1038    }
1039    catch (FormulaException e)
1040    {
1041      // Something has gone wrong trying to read the formula data eg. it
1042      // might be unsupported biff7 data
1043      logger.warn
1044        (CellReferenceHelper.getCellReference(fr.getColumn(), fr.getRow()) + 
1045         " " + e.getMessage());
1046
1047      return null;
1048    }
1049  }
1050
1051
1052  /**
1053   * Accessor
1054   *
1055   * @return the number of rows
1056   */
1057  final int getNumRows()
1058  {
1059    return numRows;
1060  }
1061
1062  /**
1063   * Accessor
1064   *
1065   * @return the number of columns
1066   */
1067  final int getNumCols()
1068  {
1069    return numCols;
1070  }
1071
1072  /**
1073   * Accessor
1074   *
1075   * @return the cells
1076   */
1077  final Cell[][] getCells()
1078  {
1079    return cells;
1080  }
1081
1082  /**
1083   * Accessor
1084   *
1085   * @return the row properties
1086   */
1087  final ArrayList      getRowProperties()
1088  {
1089    return rowProperties;
1090  }
1091
1092  /**
1093   * Accessor
1094   *
1095   * @return the column information
1096   */
1097  final ArrayList   getColumnInfosArray()
1098  {
1099    return columnInfosArray;
1100  }
1101
1102  /**
1103   * Accessor
1104   *
1105   * @return the hyperlinks
1106   */
1107  final ArrayList   getHyperlinks()
1108  {
1109    return hyperlinks;
1110  }
1111
1112  /**
1113   * Accessor
1114   *
1115   * @return the charts
1116   */
1117  final ArrayList   getCharts()
1118  {
1119    return charts;
1120  }
1121
1122  /**
1123   * Accessor
1124   *
1125   * @return the drawings
1126   */
1127  final ArrayList   getDrawings()
1128  {
1129    return drawings;
1130  }
1131
1132  /**
1133   * Accessor
1134   *
1135   * @return the data validations
1136   */
1137  final DataValidation getDataValidation()
1138  {
1139    return dataValidation;
1140  }
1141
1142  /**
1143   * Accessor
1144   *
1145   * @return the ranges
1146   */
1147  final Range[] getMergedCells()
1148  {
1149    return mergedCells;
1150  }
1151
1152  /**
1153   * Accessor
1154   *
1155   * @return the sheet settings
1156   */
1157  final SheetSettings getSettings()
1158  {
1159    return settings;
1160  }
1161
1162  /**
1163   * Accessor
1164   *
1165   * @return the row breaks
1166   */
1167  final int[] getRowBreaks()
1168  {
1169    return rowBreaks;
1170  }
1171
1172  /**
1173   * Accessor
1174   *
1175   * @return the workspace options
1176   */
1177  final WorkspaceInformationRecord getWorkspaceOptions()
1178  {
1179    return workspaceOptions;
1180  }
1181
1182  /**
1183   * Accessor
1184   *
1185   * @return the environment specific print record
1186   */
1187  final PLSRecord getPLS()
1188  {
1189    return plsRecord;
1190  }
1191
1192  /**
1193   * Accessor for the button property set, used during copying
1194   *
1195   * @return the button property set
1196   */
1197  final ButtonPropertySetRecord getButtonPropertySet()
1198  {
1199    return buttonPropertySet;
1200  }
1201
1202  /**
1203   * Adds a cell comment to a cell just read in
1204   *
1205   * @param col the column for the comment
1206   * @param row the row for the comment
1207   * @param text the comment text
1208   * @param width the width of the comment text box
1209   * @param height the height of the comment text box
1210   */
1211  private void addCellComment(int col, int row, String   text, 
1212                              double width,
1213                              double height)
1214  {
1215    Cell c = cells[row][col];
1216    if (c == null)
1217    {
1218      logger.warn("Cell at " + CellReferenceHelper.getCellReference(col, row) +
1219                  " not present - adding a blank");
1220      MulBlankCell mbc = new MulBlankCell(row,
1221                                          col,
1222                                          0,
1223                                          formattingRecords,
1224                                          sheet);
1225      CellFeatures cf = new CellFeatures();
1226      cf.setReadComment(text, width, height);
1227      mbc.setCellFeatures(cf);
1228      addCell(mbc);
1229
1230      return;
1231    }
1232
1233    if (c instanceof CellFeaturesAccessor)
1234    {
1235      CellFeaturesAccessor cv = (CellFeaturesAccessor) c;
1236      CellFeatures cf = cv.getCellFeatures();
1237
1238      if (cf == null)
1239      {
1240        cf = new CellFeatures();
1241        cv.setCellFeatures(cf);
1242      }
1243
1244      cf.setReadComment(text, width ,height);
1245    }
1246    else
1247    {
1248      logger.warn("Not able to add comment to cell type " +
1249                  c.getClass().getName() +
1250                  " at " + CellReferenceHelper.getCellReference(col, row));
1251    }
1252  }
1253
1254  /**
1255   * Reads in the object record
1256   *
1257   * @param objRecord the obj record
1258   * @param msoRecord the mso drawing record read in earlier
1259   * @param comments the hash map of comments
1260   */
1261  private void handleObjectRecord(ObjRecord objRecord,
1262                                  MsoDrawingRecord msoRecord,
1263                                  HashMap   comments)
1264  {
1265    if (msoRecord == null)
1266    {
1267      logger.warn("Object record is not associated with a drawing " +
1268                  " record - ignoring");
1269      return;
1270    }
1271
1272    // Handle images
1273    if (objRecord.getType() == ObjRecord.PICTURE ||
1274        objRecord.getType() == ObjRecord.MSOFFICEDRAWING)
1275    {
1276      if (drawingData == null)
1277      {
1278        drawingData = new DrawingData();
1279      }
1280
1281      Drawing drawing = new Drawing(msoRecord,
1282                                    objRecord,
1283                                    drawingData,
1284                                    workbook.getDrawingGroup());
1285      drawings.add(drawing);
1286      return;
1287    }
1288
1289    // Handle comments
1290    if (objRecord.getType() == ObjRecord.EXCELNOTE)
1291    {
1292      if (drawingData == null)
1293      {
1294        drawingData = new DrawingData();
1295      }
1296
1297      Comment comment = new Comment(msoRecord,
1298                                    objRecord,
1299                                    drawingData,
1300                                    workbook.getDrawingGroup(),
1301                                    workbookSettings);
1302
1303      Record r2 = excelFile.next();
1304      if (r2.getType() == Type.MSODRAWING)
1305      {
1306        MsoDrawingRecord mso = new MsoDrawingRecord(r2);
1307        comment.addMso(mso);
1308        r2 = excelFile.next();
1309      }
1310      Assert.verify(r2.getType() == Type.TXO);
1311      TextObjectRecord txo = new TextObjectRecord(r2);
1312      comment.setTextObject(txo);
1313
1314      r2 = excelFile.next();
1315      Assert.verify(r2.getType() == Type.CONTINUE);
1316      ContinueRecord text = new ContinueRecord(r2);
1317      comment.setText(text);
1318
1319      r2 = excelFile.next();
1320      if (r2.getType() == Type.CONTINUE)
1321      {
1322        ContinueRecord formatting = new ContinueRecord(r2);
1323        comment.setFormatting(formatting);
1324      }
1325
1326      comments.put(new Integer  (comment.getObjectId()), comment);
1327      return;
1328    }
1329
1330    // Handle form buttons
1331    if (objRecord.getType() == ObjRecord.BUTTON)
1332    {
1333      if (drawingData == null)
1334      {
1335        drawingData = new DrawingData();
1336      }
1337
1338      Button button = new Button(msoRecord,
1339                                 objRecord,
1340                                 drawingData,
1341                                 workbook.getDrawingGroup(),
1342                                 workbookSettings);
1343
1344      Record r2 = excelFile.next();
1345      if (r2.getType() == Type.MSODRAWING)
1346      {
1347        MsoDrawingRecord mso = new MsoDrawingRecord(r2);
1348        button.addMso(mso);
1349        r2 = excelFile.next();
1350      }
1351      Assert.verify(r2.getType() == Type.TXO);
1352      TextObjectRecord txo = new TextObjectRecord(r2);
1353      button.setTextObject(txo);
1354
1355      r2 = excelFile.next();
1356      Assert.verify(r2.getType() == Type.CONTINUE);
1357      ContinueRecord text = new ContinueRecord(r2);
1358      button.setText(text);
1359
1360      r2 = excelFile.next();
1361      if (r2.getType() == Type.CONTINUE)
1362      {
1363        ContinueRecord formatting = new ContinueRecord(r2);
1364        button.setFormatting(formatting);
1365      }
1366
1367      drawings.add(button);
1368
1369      return;
1370    }
1371
1372    // Handle charts
1373    if (objRecord.getType() != ObjRecord.CHART)
1374    {
1375      logger.warn(objRecord.getType() + " on sheet \"" +
1376                  sheet.getName() +
1377                  "\" not supported - omitting");
1378      if (drawingData == null)
1379      {
1380        drawingData = new DrawingData();
1381      }
1382
1383      drawingData.addData(msoRecord.getData());
1384
1385      if (workbook.getDrawingGroup() != null) // can be null for Excel 95
1386      {
1387        workbook.getDrawingGroup().setDrawingsOmitted(msoRecord,
1388                                                      objRecord);
1389      }
1390
1391      return;
1392    }
1393  }
1394}
1395
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags