sheetreader.java

一个非常有用的操作MCRSOFT EXCEL文件的工具。可以用JAVA方便的新建

          {
            drawingData = new DrawingData();
          }

          Comment comment = new Comment(msoRecord, 
                                        objRecord, 
                                        drawingData,
                                        workbook.getDrawingGroup(),
                                        workbookSettings);

          Record r2 = excelFile.next();
          if (r2.getType() == Type.MSODRAWING)
          {
            MsoDrawingRecord mso = new MsoDrawingRecord(r2);
            comment.addMso(mso);
            r2 = excelFile.next();
          }
          Assert.verify(r2.getType() == Type.TXO);
          TextObjectRecord txo = new TextObjectRecord(r2);
          comment.setTextObject(txo);

          r2 = excelFile.next();
          Assert.verify(r2.getType() == Type.CONTINUE);
          ContinueRecord text = new ContinueRecord(r2);
          comment.setText(text);

          r2 = excelFile.next();
          if (r2.getType() == Type.CONTINUE)
          {
            ContinueRecord formatting = new ContinueRecord(r2);
            comment.setFormatting(formatting);
          }

          comments.put(new Integer(comment.getObjectId()), comment);

          msoRecord = null;
          objRecord = null;
        }
        else if (objRecord.getType() != ObjRecord.CHART &&
                 !workbookSettings.getDrawingsDisabled())
        {
          logger.warn(objRecord.getType() + " on sheet \"" + 
                      sheet.getName() + 
                      "\" not supported - omitting");
          msoRecord = null;
          objRecord = null;

          if (workbook.getDrawingGroup() != null) // can be null for Excel 95
          {
            workbook.getDrawingGroup().setDrawingsOmitted(msoRecord, 
                                                          objRecord);
          }
        }
      }
      else if (r.getType() == Type.MSODRAWING)
      {
        if (!workbookSettings.getDrawingsDisabled())
        {
          msoRecord = new MsoDrawingRecord(r);
        }
      }
      else if (r.getType() == Type.BUTTONPROPERTYSET)
      {
        buttonPropertySet = new ButtonPropertySetRecord(r);
      }
      else if (r.getType() == Type.BOF)
      {
        BOFRecord br = new BOFRecord(r);
        Assert.verify(!br.isWorksheet());

        int startpos = excelFile.getPos() - r.getLength() - 4;

        // Skip to the end of the nested bof
        // Thanks to Rohit for spotting this
        Record r2 = excelFile.next();
        while (r2.getCode() != Type.EOF.value)
        {
          r2 = excelFile.next();
        }

        if (br.isChart())
        {
          if (drawingData == null)
          {
            drawingData = new DrawingData();
          }

          //  Assert.verify(msoRecord != null);
          Chart chart = new Chart(msoRecord, objRecord, drawingData,
                                  startpos, excelFile.getPos(),
                                  excelFile, workbookSettings);
          charts.add(chart);

          if (workbook.getDrawingGroup() != null)
          {
            workbook.getDrawingGroup().add(chart);
          }

          // Reset the drawing records
          msoRecord = null;
          objRecord = null;
        }

        // If this worksheet is just a chart, then the EOF reached
        // represents the end of the sheet as well as the end of the chart
        if (sheetBof.isChart())
        {
          cont = false;
        }
      }
      else if (r.getType() == Type.EOF)
      {
        cont = false;
      }
    }

    // Restore the file to its accurate position
    excelFile.restorePos();

    // Add all the shared formulas to the sheet as individual formulas
    Iterator i = sharedFormulas.iterator();

    while (i.hasNext())
    {
      SharedFormulaRecord sfr = (SharedFormulaRecord) i.next();

      Cell[] sfnr = sfr.getFormulas(formattingRecords, nineteenFour);

      for (int sf = 0; sf < sfnr.length; sf++)
      {
        addCell(sfnr[sf]);
      }
    }

    // If the last base shared formula wasn't added to the sheet, then
    // revert it to an ordinary formula and add it
    if (!sharedFormulaAdded && sharedFormula != null)
    {
      addCell(revertSharedFormula(sharedFormula));
    }

    // If there is a stray msoDrawing record, then flag to the drawing group
    // that one has been omitted
    if (msoRecord != null && workbook.getDrawingGroup() != null)
    {
      workbook.getDrawingGroup().setDrawingsOmitted(msoRecord, objRecord);
    }

    // Check that the comments hash is empty
    if (!comments.isEmpty())
    {
      logger.warn("note all comments have a corresponding Note record");
    }
  }

  /**
   * Sees if the shared formula belongs to any of the shared formula
   * groups
   *
   * @param fr the candidate shared formula
   * @return TRUE if the formula was added, FALSE otherwise
   */
  private boolean addToSharedFormulas(BaseSharedFormulaRecord fr)
  {
    Iterator i = sharedFormulas.iterator();
    boolean added = false;
    SharedFormulaRecord sfr = null;

    while (i.hasNext() && !added)
    {
      sfr = (SharedFormulaRecord) i.next();
      added = sfr.add(fr);
    }

    return added;
  }

  /**
   * Reverts the shared formula passed in to an ordinary formula and adds
   * it to the list
   *
   * @param f the formula
   * @return the new formula
   */
  private Cell revertSharedFormula(BaseSharedFormulaRecord f)
  {
    // String formulas look for a STRING record soon after the formula
    // occurred.  Temporarily the position in the excel file back
    // to the point immediately after the formula record
    int pos = excelFile.getPos();
    excelFile.setPos(f.getFilePos());

    FormulaRecord fr = new FormulaRecord(f.getRecord(),
                                         excelFile,
                                         formattingRecords,
                                         workbook,
                                         workbook,
                                         FormulaRecord.ignoreSharedFormula,
                                         sheet,
                                         workbookSettings);

    Cell cell = fr.getFormula();

    // See if the formula evaluates to date
    if (fr.getFormula().getType() == CellType.NUMBER_FORMULA)
    {
      NumberFormulaRecord nfr = (NumberFormulaRecord) fr.getFormula();
      if (formattingRecords.isDate(fr.getXFIndex()))
      {
        cell = new DateFormulaRecord(nfr,
                                     formattingRecords,
                                     workbook,
                                     workbook,
                                     nineteenFour,
                                     sheet);
      }
    }

    excelFile.setPos(pos);
    return cell;
  }


  /**
   * Accessor
   *
   * @return the number of rows
   */
  final int getNumRows()
  {
    return numRows;
  }

  /**
   * Accessor
   *
   * @return the number of columns
   */
  final int getNumCols()
  {
    return numCols;
  }

  /**
   * Accessor
   *
   * @return the cells
   */
  final Cell[][] getCells()
  {
    return cells;
  }

  /**
   * Accessor
   *
   * @return the row properties
   */
  final ArrayList    getRowProperties()
  {
    return rowProperties;
  }

  /**
   * Accessor
   *
   * @return the column information
   */
  final ArrayList getColumnInfosArray()
  {
    return columnInfosArray;
  }

  /**
   * Accessor
   *
   * @return the hyperlinks
   */
  final ArrayList getHyperlinks()
  {
    return hyperlinks;
  }

  /**
   * Accessor
   *
   * @return the charts
   */
  final ArrayList getCharts()
  {
    return charts;
  }

  /**
   * Accessor
   *
   * @return the drawings
   */
  final ArrayList getDrawings()
  {
    return drawings;
  }

  /**
   * Accessor
   * 
   * @return the data validations
   */
  final DataValidation getDataValidation()
  {
    return dataValidation;
  }

  /**
   * Accessor
   *
   * @return the ranges
   */
  final Range[] getMergedCells()
  {
    return mergedCells;
  }

  /**
   * Accessor
   *
   * @return the sheet settings
   */
  final SheetSettings getSettings()
  {
    return settings;
  }

  /**
   * Accessor
   *
   * @return the row breaks
   */
  final int[] getRowBreaks()
  {
    return rowBreaks;
  }

  /**
   * Accessor
   *
   * @return the workspace options
   */
  final WorkspaceInformationRecord getWorkspaceOptions()
  {
    return workspaceOptions;
  }

  /**
   * Accessor
   *
   * @return the environment specific print record
   */
  final PLSRecord getPLS()
  {
    return plsRecord;
  }

  /**
   * Accessor for the button property set, used during copying
   *
   * @return the button property set
   */
  final ButtonPropertySetRecord getButtonPropertySet()
  {
    return buttonPropertySet;
  }
  
  /**
   * Adds a cell comment to a cell just read in
   */
  private void addCellComment(int col, int row, String text)
  {
    Cell c = cells[row][col];
    if (c == null)
    {
      StringBuffer sb = new StringBuffer();
      logger.warn("Cell at " + CellReferenceHelper.getCellReference(col, row) +
                  " not present - adding a blank");
      MulBlankCell mbc = new MulBlankCell(row, 
                                          col, 
                                          0,
                                          formattingRecords,
                                          sheet);
      CellFeatures cf = new CellFeatures();
      cf.setReadComment(text);
      mbc.setCellFeatures(cf);
      addCell(mbc);
      
      return;
    }

    if (c instanceof CellValue)
    {
      CellValue cv = (CellValue) c;
      CellFeatures cf = cv.getCellFeatures();

      if (cf == null)
      {
        cf = new CellFeatures();
        cv.setCellFeatures(cf);
      }

      cf.setReadComment(text);
    }
    else
    {
      logger.warn("Not able to add comment to cell type " + 
                  c.getClass().getName() + 
                  " at " + CellReferenceHelper.getCellReference(col, row));
    }
  }
}