writablesheetimpl.java

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

    else if (h.isLocation())
    {
      contents = ( (HyperlinkRecord) h).getContents();
    }

    if (c.getType() == CellType.LABEL)
    {
      Label l = (Label) c;
      l.setString(contents);
      l.setCellFormat(WritableWorkbook.HYPERLINK_STYLE);
    }
    else
    {
      Label l = new Label(h.getColumn(), h.getRow(), contents, 
                          WritableWorkbook.HYPERLINK_STYLE);
      addCell(l);
    }
    
    // Set all other cells within range to be empty
    for (int i = h.getRow(); i <= h.getLastRow(); i++)
    {
      for (int j = h.getColumn(); j <= h.getLastColumn(); j++)
      {
        if (i != h.getRow() && j != h.getColumn())
        {
          // Set the cell to be empty
          if (rows[i] != null)
          {
            rows[i].removeCell(j);
          }
        }
      }
    }

    ((HyperlinkRecord) h).initialize(this);
    hyperlinks.add(h);
  }

  /**
   * Merges the specified cells.  Any clashes or intersections between 
   * merged cells are resolved when the spreadsheet is written out
   *
   * @param col1 the column number of the top left cell
   * @param row1 the row number of the top left cell
   * @param col2 the column number of the bottom right cell
   * @param row2 the row number of the bottom right cell
   * @return the Range object representing the merged cells
   * @exception jxl.write..WriteException
   * @exception jxl.write.biff.RowsExceededException
   */
  public Range mergeCells(int col1, int row1, int col2, int row2)
    throws WriteException, RowsExceededException
  {
    // First check that the cells make sense
    if (col2 < col1 || row2 < row1)
    {
      logger.warn("Cannot merge cells - top left and bottom right "+
                  "incorrectly specified");
    }

    // Make sure the spreadsheet is up to size
    if (col2 >= numColumns || row2 >= numRows)
    {
      addCell(new Blank(col2, row2));
    }

    SheetRangeImpl range = new SheetRangeImpl(this, col1, row1, col2, row2);
    mergedCells.add(range);

    return range;
  }

  /**
   * Unmerges the specified cells.  The Range passed in should be one that
   * has been previously returned as a result of the getMergedCells method
   *
   * @param r the range of cells to unmerge
   */
  public void unmergeCells(Range r)
  {
    mergedCells.unmergeCells(r);
  }

  /**
   * Sets the header for this page
   *
   * @param l the print header to print on the left side
   * @param c the print header to print in the centre
   * @param r the print header to print on the right hand side
   * @deprecated use the sheet settings beant
   */
  public void setHeader(String l, String c, String r)
  {
    HeaderFooter header = new HeaderFooter();
    header.getLeft().append(l);
    header.getCentre().append(c);
    header.getRight().append(r);
    settings.setHeader(header);
  }

  /**
   * Sets the footer for this page
   *
   * @param l the print header to print on the left side
   * @param c the print header to print in the centre
   * @param r the print header to print on the right hand side
   * @deprecated use the sheet settings bean
   */
  public void setFooter(String l, String c, String r)
  {
    HeaderFooter footer = new HeaderFooter();
    footer.getLeft().append(l);
    footer.getCentre().append(c);
    footer.getRight().append(r);
    settings.setFooter(footer);
  }

  /**
   * Sets the page setup details
   *
   * @param p  the page orientation
   * @deprecated use the SheetSettings bean
   */
  public void setPageSetup(PageOrientation p)
  {
    settings.setOrientation(p);
  }

  /**
   * Sets the page setup details
   *
   * @param p  the page orientation
   * @param hm the header margin, in inches
   * @param fm the footer margin, in inches
   * @deprecated use the SheetSettings bean
   */
  public void setPageSetup(PageOrientation p, double hm, double fm)
  {
    settings.setOrientation(p);
    settings.setHeaderMargin(hm);
    settings.setFooterMargin(fm);
  }

  /**
   * Sets the page setup details
   *
   * @param p  the page orientation
   * @param ps the paper size
   * @param hm the header margin, in inches
   * @param fm the footer margin, in inches
   * @deprecated use the SheetSettings bean
   */
  public void setPageSetup(PageOrientation p, PaperSize ps, 
                           double hm, double fm)
  {
    settings.setPaperSize(ps);
    settings.setOrientation(p);
    settings.setHeaderMargin(hm);
    settings.setFooterMargin(fm);
  }

  /** 
   * Gets the settings for this sheet
   *
   * @return the page settings bean
   */
  public SheetSettings getSettings()
  {
    return settings;
  }

  /**
   * Gets the workbook settings
   */
  WorkbookSettings getWorkbookSettings()
  {
    return workbookSettings;
  }

  /**
   * Forces a page break at the specified row
   * 
   * @param row the row to break at
   */
  public void addRowPageBreak(int row)
  {
    // First check that the row is not already present
    Iterator i = rowBreaks.iterator();
    boolean found = false;

    while (i.hasNext() && !found)
    {
      if (( (Integer) i.next()).intValue() == row)
      {
        found = true;
      }
    }

    if (!found)
    {
      rowBreaks.add(new Integer(row));
    }
  }

  /**
   * Accessor for the charts.  Used when copying
   *
   * @return the charts on this sheet
   */
  private Chart[] getCharts()
  {
    return sheetWriter.getCharts();
  }

  /**
   * Accessor for the drawings.  Used when copying
   *
   * @return the drawings on this sheet
   */
  private DrawingGroupObject[] getDrawings()
  {
    DrawingGroupObject[] dr = new DrawingGroupObject[drawings.size()];
    System.arraycopy(drawings.toArray(), 0, dr, 0, dr.length);
    return dr;
  }

  /**
   * Check all the merged cells for borders.  Although in an OO sense the
   * logic should belong in this class, in order to reduce the bloated 
   * nature of the source code for this object this logic has been delegated
   * to the SheetWriter
   */
  void checkMergedBorders()
  {
    sheetWriter.setWriteData(rows, 
                             rowBreaks, 
                             hyperlinks, 
                             mergedCells, 
                             columnFormats );
    sheetWriter.setDimensions(getRows(), getColumns());
    sheetWriter.checkMergedBorders();
  }

  /**
   * Accessor for the workspace options
   *
   * @return the workspace options
   */
  private WorkspaceInformationRecord getWorkspaceOptions()
  {
    return sheetWriter.getWorkspaceOptions();
  }

  /**
   * Rationalizes the sheets xf index mapping
   * @param xfMapping the index mapping for XFRecords
   * @param fontMapping the index mapping for fonts
   * @param formatMapping the index mapping for formats
   */
  void rationalize(IndexMapping xfMapping, 
                   IndexMapping fontMapping, 
                   IndexMapping formatMapping)
  {
    // Rationalize the column formats
    for (Iterator i = columnFormats.iterator() ; i.hasNext() ;)
    {
      ColumnInfoRecord cir = (ColumnInfoRecord) i.next();
      cir.rationalize(xfMapping);
    }

    // Rationalize the row formats
    for (int i = 0; i < rows.length ; i++)
    {
      if (rows[i] != null)
      {
        rows[i].rationalize(xfMapping);
      }
    }

    // Rationalize any data that appears on the charts
    Chart[] charts = getCharts();
    for (int c = 0; c < charts.length; c++)
    {
      charts[c].rationalize(xfMapping, fontMapping, formatMapping);
    }    
  }

  /**
   * Accessor for the workbook
   * @return the workbook
   */
  WritableWorkbookImpl getWorkbook()
  {
    return workbook;
  }

  /**
   * Gets the column format for the specified column
   *
   * @param col the column number
   * @return the column format, or NULL if the column has no specific format
   * @deprecated use getColumnView instead
   */
  public CellFormat getColumnFormat(int col)
  {
    return getColumnView(col).getFormat();
  }

  /**
   * Gets the column width for the specified column
   *
   * @param col the column number
   * @return the column width, or the default width if the column has no
   *         specified format
   * @deprecated use getColumnView instead
   */
  public int getColumnWidth(int col)
  {
    return getColumnView(col).getDimension();
  }

  /**
   * Gets the column width for the specified column
   *
   * @param row the column number
   * @return the row height, or the default height if the column has no
   *         specified format
   * @deprecated use getRowView instead
   */
  public int getRowHeight(int row)
  {
    return getRowView(row).getDimension();
  }

  /**
   * Accessor for the chart only method
   * 
   * @return TRUE if this is a chart only, FALSE otherwise
   */
  boolean isChartOnly()
  {
    return chartOnly;
  }

  /**
   * Gets the row view for the specified row
   *
   * @param col the row number
   * @return the row format, or the default format if no override is
             specified
   */
  public CellView getRowView(int row)
  {
    CellView cv = new CellView();

    try
    {
      RowRecord rr = getRowRecord(row);

      if (rr == null || rr.isDefaultHeight())
      {
        cv.setDimension(settings.getDefaultRowHeight());
        cv.setSize(settings.getDefaultRowHeight());
      }
      else if (rr.isCollapsed())
      {
        cv.setHidden(true);
      }
      else
      {
        cv.setDimension(rr.getRowHeight());
        cv.setSize(rr.getRowHeight());
      }
      return cv;
    }
    catch (RowsExceededException e)
    {
      // Simple return the default
      cv.setDimension(settings.getDefaultRowHeight());
      cv.setSize(settings.getDefaultRowHeight());
      return cv;
    }
  }

  /**
   * Gets the column width for the specified column
   *
   * @param col the column number
   * @return the column format, or the default format if no override is
             specified
   */
  public CellView getColumnView(int col)
  {
    ColumnInfoRecord cir = getColumnInfo(col);
    CellView cv = new CellView();

    if (cir != null)
    {
      cv.setDimension(cir.getWidth()/256);
      cv.setSize(cir.getWidth());
      cv.setHidden(cir.getHidden());
      cv.setFormat(cir.getCellFormat());
    }
    else
    {
      cv.setDimension(settings.getDefaultColumnWidth()/256);
      cv.setSize(settings.getDefaultColumnWidth());
    }

    return cv;
  }

  /**
   * Adds an image to this sheet
   *
   * @param image the image to add
   */
  public void addImage(WritableImage image)
  {
    boolean supported = false;
    java.io.File imageFile = image.getImageFile();
    String fileType = "?";

    if (imageFile != null)
    {
    
      String fileName = imageFile.getName();
      int fileTypeIndex = fileName.lastIndexOf('.');
      fileType = fileTypeIndex != -1 ? 
        fileName.substring(fileTypeIndex+1) : "";
      
      for (int i = 0 ; i < imageTypes.length && !supported ; i++)
      {
        if (fileType.equalsIgnoreCase(imageTypes[i]))
        {
          supported = true;
        }
      }
    }
    else
    {
      supported = true;
    }

    if (supported)
    {
      workbook.addDrawing(image);
      drawings.add(image);
      images.add(image);
    }
    else
    {
      StringBuffer message = new StringBuffer("Image type ");
      message.append(fileType);
      message.append(" not supported.  Supported types are ");
      message.append(imageTypes[0]);
      for (int i = 1 ; i < imageTypes.length ; i++)
      {
        message.append(", ");
        message.append(imageTypes[i]);
      }
      logger.warn(message.toString());
    }
  }

  /**
   * Gets the number of images on this sheet
   *
   * @return the number of images on this sheet
   */
  public int getNumberOfImages()
  {
    return images.size();
  }

  /**
   * Accessor for a particular image on this sheet
   *
   * @param i the 0-based image index number
   * @return the image with the specified index number
   */
  public WritableImage getImage(int i)
  {
    return (WritableImage) images.get(i);
  }

  /**
   * Accessor for a particular image on this sheet
   *
   * @param i the 0-based image index number
   * @return the image with the specified index number
   */
  public Image getDrawing(int i)
  {
    return (Image) images.get(i);
  }

  /**
   * Removes the specified image from this sheet.  The image passed in
   * must be the same instance as that retrieved from a getImage call
   *
   * @param wi the image to remove
   */
  public void removeImage(WritableImage wi)
  {
    boolean removed = drawings.remove(wi);
    images.remove(wi);
    drawingsModified = true;
    workbook.removeDrawing(wi);
  }

  /**
   * Validates the sheet name
   */
  private String validateName(String n)
  {
    if (n.length() > maxSheetNameLength)
    {
      logger.warn("Sheet name " + n + " too long - truncating");
      n = n.substring(0, maxSheetNameLength);
    }

    if (n.charAt(0) == '\'')
    {
      logger.warn("Sheet naming cannot start with \' - removing");
      n = n.substring(1);
    }

    for (int i = 0 ; i < illegalSheetNameCharacters.length ; i++)
    {
      String newname = n.replace(illegalSheetNameCharacters[i], '@');
      if (n != newname)
      {
        logger.warn(illegalSheetNameCharacters[i] + 
        " is not a valid character within a sheet name - replacing");
      }
      n = newname;
    }

    return n;
  }

  /**
   * Adds a drawing to the list - typically used for comments
   *
   * @param the drawing to add
   */
  void addDrawing(DrawingGroupObject o)
  {
    drawings.add(o);
    Assert.verify(!(o instanceof Drawing));
  }
}