writablesheetimpl.java

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

  /**
   * Sets the height of the specified row, as well as its collapse status
   *
   * @param row the row to be formatted
   * @param height the row height in points
   * @param collapsed indicates whether the row is collapsed
   * @param zeroHeight indicates that the row has zero height
   * @exception RowsExceededException
   */
  public void setRowView(int row, int height, 
                         boolean collapsed)
                         throws RowsExceededException
  {
    RowRecord rowrec = getRowRecord(row);
    rowrec.setRowHeight(height);
    rowrec.setCollapsed(collapsed);
  }

  /**
   * Writes out this sheet.  This functionality is delegated off to the 
   * SheetWriter class in order to reduce the bloated nature of this source
   * file
   *
   * @exception IOException 
   */
  public void write() throws IOException
  {
    boolean dmod = drawingsModified;
    if (workbook.getDrawingGroup() != null)
    {
      dmod |= workbook.getDrawingGroup().hasDrawingsOmitted();
    }


    sheetWriter.setWriteData(rows, 
                             rowBreaks, 
                             hyperlinks, 
                             mergedCells, 
                             columnFormats );
    sheetWriter.setDimensions(getRows(), getColumns());
    sheetWriter.setSettings(settings);
    sheetWriter.setPLS(plsRecord);
    sheetWriter.setDrawings(drawings, dmod);
    sheetWriter.setButtonPropertySet(buttonPropertySet);
    sheetWriter.setDataValidation(dataValidation);
    
    sheetWriter.write();
  }

  /** 
   * Copies the cell contents from the specified sheet into this one
   *
   * @param s the sheet to copy
   */
  private void copyCells(Sheet s)
  {
    // Copy the cells
    int cells = s.getRows();
    Cell[] row = null;
    Cell cell = null;
    for (int i = 0;  i < cells; i++)
    {
      row = s.getRow(i);

      for (int j = 0; j < row.length; j++)
      {
        cell = row[j];
        CellType ct = cell.getType();

        // Encase the calls to addCell in a try-catch block
        // These should not generate any errors, because we are
        // copying from an existing spreadsheet.  In the event of
        // errors, catch the exception and then bomb out with an
        // assertion
        try
        {
          if (ct == CellType.LABEL)
          {
            Label l = new Label((LabelCell) cell);
            addCell(l);
          }
          else if (ct == CellType.NUMBER)
          {
            Number n = new Number((NumberCell) cell);
            addCell(n);
          }
          else if (ct == CellType.DATE)
          {
            DateTime dt = new DateTime((DateCell) cell);
            addCell(dt);
          }
          else if (ct == CellType.BOOLEAN)
          {
            Boolean b = new Boolean((BooleanCell) cell);
            addCell(b);
          }
          else if (ct == CellType.NUMBER_FORMULA)
          {
            ReadNumberFormulaRecord fr = new ReadNumberFormulaRecord
              ((FormulaData) cell);
            addCell(fr);
          }
          else if (ct == CellType.STRING_FORMULA)
          {
            ReadStringFormulaRecord fr = new ReadStringFormulaRecord
              ((FormulaData) cell);
            addCell(fr);
          }
          else if( ct == CellType.BOOLEAN_FORMULA)
          {
            ReadBooleanFormulaRecord fr = new ReadBooleanFormulaRecord
              ((FormulaData) cell);
            addCell(fr);
          }
          else if (ct == CellType.DATE_FORMULA)
          {
            ReadDateFormulaRecord fr = new ReadDateFormulaRecord
              ((FormulaData) cell);
            addCell(fr);
          }
          else if(ct == CellType.FORMULA_ERROR)
          {
            ReadErrorFormulaRecord fr = new ReadErrorFormulaRecord
              ((FormulaData) cell);
            addCell(fr);
          }
          else if (ct == CellType.EMPTY)
          {
            if (cell.getCellFormat() != null)
            {
              // It is a blank cell, rather than an empty cell, so
              // it may have formatting information, so
              // it must be copied
              Blank b = new Blank(cell);
              addCell(b);
            }
          }
        }
        catch (WriteException e)
        {
          Assert.verify(false);
        }
      }
    }
  }

  /**
   * Copies the specified sheet, row by row and cell by cell
   * 
   * @param s the sheet to copy
   */
  void copy(Sheet s)
  {
    // Copy the settings
    settings = new SheetSettings(s.getSettings());

    copyCells(s);

    // Copy the column info records
    jxl.read.biff.SheetImpl si = (jxl.read.biff.SheetImpl) s;
    jxl.read.biff.ColumnInfoRecord[] readCirs = si.getColumnInfos();

    for (int i = 0 ; i < readCirs.length; i++)
    {
      jxl.read.biff.ColumnInfoRecord rcir = readCirs[i];
      for (int j = rcir.getStartColumn(); j <= rcir.getEndColumn() ; j++) 
      {
        ColumnInfoRecord cir = new ColumnInfoRecord(rcir, j, 
                                                    formatRecords);
        cir.setHidden(rcir.getHidden());
        columnFormats.add(cir);
      }
    }

    // Copy the hyperlinks
    Hyperlink[] hls = s.getHyperlinks();
    for (int i = 0 ; i < hls.length; i++)
    {
      WritableHyperlink hr = new WritableHyperlink
        (hls[i], this);
      hyperlinks.add(hr);
    }

    // Copy the merged cells
    Range[] merged = s.getMergedCells();

    for (int i = 0; i < merged.length; i++)
    {
      mergedCells.add(new SheetRangeImpl((SheetRangeImpl)merged[i], this));
    }

    // Copy the row properties
    try
    {
      jxl.read.biff.RowRecord[] rowprops  = si.getRowProperties();

      for (int i = 0; i < rowprops.length; i++)
      {
        RowRecord rr = getRowRecord(rowprops[i].getRowNumber());
        XFRecord format = rowprops[i].hasDefaultFormat() ? 
          formatRecords.getXFRecord(rowprops[i].getXFIndex()) : null;
        rr.setRowDetails(rowprops[i].getRowHeight(), 
                         rowprops[i].matchesDefaultFontHeight(),
                         rowprops[i].isCollapsed(),
                         format);
      }
    }
    catch (RowsExceededException e)
    {
      // Handle the rows exceeded exception - this cannot occur since
      // the sheet we are copying from will have a valid number of rows
      Assert.verify(false);
    }

    // Copy the headers and footers
    //    sheetWriter.setHeader(new HeaderRecord(si.getHeader()));
    //    sheetWriter.setFooter(new FooterRecord(si.getFooter()));

    // Copy the page breaks
    int[] rowbreaks = si.getRowPageBreaks();

    if (rowbreaks != null)
    {
      for (int i = 0; i < rowbreaks.length; i++)
      {
        rowBreaks.add(new Integer(rowbreaks[i]));
      }
    }

    // Copy the data validations
    jxl.read.biff.DataValidation rdv = si.getDataValidation();
    if (rdv != null)
    {
      dataValidation = new DataValidation(rdv, workbook, workbookSettings);
    }

    // Copy the charts
    sheetWriter.setCharts(si.getCharts());

    // Copy the drawings
    DrawingGroupObject[] dr = si.getDrawings();
    for (int i = 0 ; i < dr.length ; i++)
    {
      if (dr[i] instanceof jxl.biff.drawing.Drawing)
      {
        WritableImage wi = new WritableImage(dr[i], 
                                             workbook.getDrawingGroup());
        drawings.add(wi);
        images.add(wi);
      }
      else if (dr[i] instanceof jxl.biff.drawing.Comment)
      {
        jxl.biff.drawing.Comment c = 
          new jxl.biff.drawing.Comment(dr[i], 
                                       workbook.getDrawingGroup(),
                                       workbookSettings);
        drawings.add(c);
        
        // Set up the reference on the cell value
        CellValue cv = (CellValue) getWritableCell(c.getColumn(), c.getRow());
        Assert.verify(cv.getCellFeatures() != null);
        cv.getWritableCellFeatures().setCommentDrawing(c);
      }
    }

    // Copy the workspace options
    sheetWriter.setWorkspaceOptions(si.getWorkspaceOptions());

    // Set a flag to indicate if it contains a chart only
    if (si.getSheetBof().isChart())
    {
      chartOnly = true;
      sheetWriter.setChartOnly();
    }

    // Copy the environment specific print record
    if (si.getPLS() != null)
    {
      plsRecord = new PLSRecord(si.getPLS());
    }

    // Copy the button property set
    if (si.getButtonPropertySet() != null)
    {
      buttonPropertySet = new ButtonPropertySetRecord
        (si.getButtonPropertySet());
    }
  }

  /**
   * Copies the specified sheet, row by row and cell by cell
   * 
   * @param s the sheet to copy
   */
  void copy(WritableSheet s)
  {
    settings = new SheetSettings(s.getSettings());

    copyCells(s);

    // Copy the column formats
    columnFormats = ( (WritableSheetImpl) s).columnFormats;

    // Copy the merged cells
    Range[] merged = s.getMergedCells();

    for (int i = 0; i < merged.length; i++)
    {
      mergedCells.add(new SheetRangeImpl((SheetRangeImpl)merged[i], this));
    }

    // Copy the row properties
    try
    {
      RowRecord[] copyRows = ( (WritableSheetImpl) s).rows;
      RowRecord row = null;
      for (int i = 0; i < copyRows.length ; i++)
      {
        row = copyRows[i];
        
        if (row != null &&
            (!row.isDefaultHeight() ||
             row.isCollapsed()))
        {
          RowRecord rr = getRowRecord(i);
          rr.setRowDetails(row.getRowHeight(), 
                           row.matchesDefaultFontHeight(),
                           row.isCollapsed(), 
                           row.getStyle());
        }
      }
    }
    catch (RowsExceededException e)
    {
      // Handle the rows exceeded exception - this cannot occur since
      // the sheet we are copying from will have a valid number of rows
      Assert.verify(false);
    }

    // Copy the headers and footers
    WritableSheetImpl si = (WritableSheetImpl) s;

    //    sheetWriter.setHeader(si.getHeader());
    //    sheetWriter.setFooter(si.getFooter());

    // Copy the horizontal page breaks
    rowBreaks = new ArrayList(si.rowBreaks);

    // Copy the data validations
    DataValidation rdv = si.dataValidation;
    if (rdv != null)
    {
      dataValidation = new DataValidation(rdv, 
                                          workbook, 
                                          workbookSettings);
    }

    // Copy the charts 
    sheetWriter.setCharts(si.getCharts());

    // Copy the drawings
    DrawingGroupObject[] dr = si.getDrawings();
    for (int i = 0 ; i < dr.length ; i++)
    {
      if (dr[i] instanceof jxl.biff.drawing.Drawing)
      {
        WritableImage wi = new WritableImage(dr[i], 
                                             workbook.getDrawingGroup());
        drawings.add(wi);
        images.add(wi);
      }
      else if (dr[i] instanceof jxl.biff.drawing.Comment)
      {
        jxl.biff.drawing.Comment c = new jxl.biff.drawing.Comment
                                     (dr[i], 
                                      workbook.getDrawingGroup(),
                                      workbookSettings);
        drawings.add(c);
      }
    }

    // Copy the workspace options
    sheetWriter.setWorkspaceOptions(si.getWorkspaceOptions());

    // Copy the environment specific print record
    if (si.plsRecord != null)
    {
      plsRecord = new PLSRecord(si.plsRecord);
    }

    // Copy the button property set
    if (si.buttonPropertySet != null)
    {
      buttonPropertySet = new ButtonPropertySetRecord(si.buttonPropertySet);
    }
  }

  /**
   * Gets the header.  Called when copying sheets
   *
   * @return the page header
   */
  final HeaderRecord getHeader()
  {
    return sheetWriter.getHeader();
  }

  /**
   * Gets the footer.  Called when copying sheets
   *
   * @return the page footer
   */
  final FooterRecord getFooter()
  {
    return sheetWriter.getFooter();
  }
  /**
   * Determines whether the sheet is protected
   *
   * @return whether or not the sheet is protected
   * @deprecated in favour of getSettings() api
   */
  public boolean isProtected()
  {
    return settings.isProtected();
  }

  /**
   * Gets the hyperlinks on this sheet
   *
   * @return an array of hyperlinks
   */
  public Hyperlink[] getHyperlinks()
  {
    Hyperlink[] hl = new Hyperlink[hyperlinks.size()];

    for (int i = 0; i < hyperlinks.size(); i++)
    {
      hl[i] = (Hyperlink) hyperlinks.get(i);
    }

    return hl;
  }

  /**
   * Gets the cells which have been merged on this sheet
   *
   * @return an array of range objects
   */
  public Range[] getMergedCells()
  {
    return mergedCells.getMergedCells();
  }

  /**
   * Gets the writable  hyperlinks on this sheet
   *
   * @return an array of hyperlinks
   */
  public WritableHyperlink[] getWritableHyperlinks()
  {
    WritableHyperlink[] hl = new WritableHyperlink[hyperlinks.size()];

    for (int i = 0; i < hyperlinks.size(); i++)
    {
      hl[i] = (WritableHyperlink) hyperlinks.get(i);
    }

    return hl;
  }

  /**
   * Removes the specified hyperlink.  Note that if you merely set the
   * cell contents to be an Empty cell, then the cells containing the 
   * hyperlink will still be active.  The contents of the cell which
   * activate the hyperlink are removed.
   * The hyperlink passed in must be a hyperlink retrieved using the 
   * getHyperlinks method
   *
   * @param h the hyperlink to remove.
   * @param preserveLabel if TRUE preserves the label contents, if FALSE
   * removes them
   */
  public void removeHyperlink(WritableHyperlink h)
  {
    removeHyperlink(h, false);
  }

  /**
   * Removes the specified hyperlink.  Note that if you merely set the
   * cell contents to be an Empty cell, then the cells containing the 
   * hyperlink will still be active.
   * If the preserveLabel field is set, the cell contents of the 
   * hyperlink are preserved, although the hyperlink is deactivated.  If
   * this value is FALSE, the cell contents are removed
   * The hyperlink passed in must be a hyperlink retrieved using the 
   * getHyperlinks method
   *
   * @param h the hyperlink to remove.
   * @param preserveLabel if TRUE preserves the label contents, if FALSE
   * removes them
   */
  public void removeHyperlink(WritableHyperlink h, boolean preserveLabel)
  {
    // Remove the hyperlink
    hyperlinks.remove(hyperlinks.indexOf(h));

    if (!preserveLabel)
    {
      // Set the cell contents for the hyperlink - including any formatting
      // information - to be empty
      Assert.verify(rows.length > h.getRow() && rows[h.getRow()] != null);
      rows[h.getRow()].removeCell(h.getColumn());
    }
  }

  /**
   * Adds the specified hyperlink
   * 
   * @param the hyperlink
   * @exception WriteException
   * @exception RowsExceededException
   */
  public void addHyperlink(WritableHyperlink h) 
    throws WriteException, RowsExceededException
  {
    // First set the label on the sheet
    Cell c = getCell(h.getColumn(), h.getRow());

    String contents = null;
    if (h.isFile() || h.isUNC())
    {
      String cnts = ( (HyperlinkRecord) h).getContents();
      if (cnts == null)
      {
        contents = h.getFile().getPath();
      }
      else
      {
        contents = cnts;
      }
    }
    else if (h.isURL())
    {
      String cnts = ( (HyperlinkRecord) h).getContents();
      if (cnts == null)
      {
        contents = h.getURL().toString();
      }
      else
      {
        contents=cnts;
      }
    }