sheetreader.java~

java直接操作execl文件,读写建立新文件

    }

    // 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("Not 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
   * @exception FormulaException
   */
  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);

    try
    {
    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;
    }
    catch (FormulaException e)
    {
      // Something has gone wrong trying to read the formula data eg. it
      // might be unsupported biff7 data
      logger.warn
        (CellReferenceHelper.getCellReference(fr.getColumn(), fr.getRow()) + 
         " " + e.getMessage());

      return null;
    }
  }


  /**
   * 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
   *
   * @param col the column for the comment
   * @param row the row for the comment
   * @param text the comment text
   * @param width the width of the comment text box
   * @param height the height of the comment text box
   */
  private void addCellComment(int col, int row, String text, 
                              double width,
                              double height)
  {
    Cell c = cells[row][col];
    if (c == null)
    {
      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, width, height);
      mbc.setCellFeatures(cf);
      addCell(mbc);

      return;
    }

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

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

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

  /**
   * Reads in the object record
   *
   * @param objRecord the obj record
   * @param msoRecord the mso drawing record read in earlier
   * @param comments the hash map of comments
   */
  private void handleObjectRecord(ObjRecord objRecord,
                                  MsoDrawingRecord msoRecord,
                                  HashMap comments)
  {
    if (msoRecord == null)
    {
      logger.warn("Object record is not associated with a drawing " +
                  " record - ignoring");
      return;
    }

    // Handle images
    if (objRecord.getType() == ObjRecord.PICTURE)
    {
      if (drawingData == null)
      {
        drawingData = new DrawingData();
      }

      Drawing drawing = new Drawing(msoRecord,
                                    objRecord,
                                    drawingData,
                                    workbook.getDrawingGroup());
      drawings.add(drawing);
      return;
    }

    // Handle comments
    if (objRecord.getType() == ObjRecord.EXCELNOTE)
    {
      if (drawingData == null)
      {
        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);
      return;
    }

    // Handle form buttons
    if (objRecord.getType() == ObjRecord.BUTTON)
    {
      if (drawingData == null)
      {
        drawingData = new DrawingData();
      }

      Button button = new Button(msoRecord,
                                 objRecord,
                                 drawingData,
                                 workbook.getDrawingGroup(),
                                 workbookSettings);

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

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

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

      drawings.add(button);

      return;
    }

    // Handle charts
    if (objRecord.getType() != ObjRecord.CHART)
    {
      logger.warn(objRecord.getType() + " on sheet \"" +
                  sheet.getName() +
                  "\" not supported - omitting");
      if (drawingData == null)
      {
        drawingData = new DrawingData();
      }

      drawingData.addData(msoRecord.getData());

      if (workbook.getDrawingGroup() != null) // can be null for Excel 95
      {
        workbook.getDrawingGroup().setDrawingsOmitted(msoRecord,
                                                      objRecord);
      }

      return;
    }
  }
}