sheetwriter.java

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

    while (hi.hasNext())
    {
      hlr = (WritableHyperlink) hi.next();
      outputFile.write(hlr);
    }

    if (buttonPropertySet != null)
    {
      outputFile.write(buttonPropertySet);
    }


    EOFRecord eof = new EOFRecord();
    outputFile.write(eof);

    // Now the various cross reference offsets have been calculated,
    // retrospectively set the values in the output file
    outputFile.setData(indexRecord.getData(), indexPos+4);
  }

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

  /**
   * Gets the footer.  Called when copying sheets
   *
   * @return the page footer
   */
  final FooterRecord getFooter()
  {
    return footer;
  }

  /**
   * Sets the data necessary for writing out the sheet.  This method must
   *  be called immediately prior to writing
   *
   * @param rws the rows in the spreadsheet
   */
  void setWriteData(RowRecord[] rws, 
                    ArrayList   rb,
                    ArrayList   hl,
                    MergedCells mc,
                    TreeSet     cf)
  {
    rows = rws;
    rowBreaks = rb;
    hyperlinks = hl;
    mergedCells = mc;
    columnFormats = cf;
  }

  /**
   * Sets the dimensions of this spreadsheet.  This method must be called 
   * immediately prior to writing
   *
   * @param rws the number of rows
   * @param cls the number of columns
   */
  void setDimensions(int rws, int cls)
  {
    numRows = rws;
    numCols = cls;
  }

  /**
   * Sets the sheet settings for this particular sheet.  Must be
   * called immediately prior to writing
   * 
   * @param sr the sheet settings
   */
  void setSettings(SheetSettings sr)
  {
    settings = sr;
  }

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

  /**
   * Accessor for the workspace options
   *
   * @param wo the workspace options
   */
  void setWorkspaceOptions(WorkspaceInformationRecord wo)
  {
    workspaceOptions = wo;
  }


  /**
   * Sets the charts for this sheet
   *
   * @param ch the charts
   */
  void setCharts(Chart[] ch)
  {
    drawingWriter.setCharts(ch);
  }

  /**
   * Sets the drawings on this sheet
   *
   * @param dr the list of drawings
   * @param mod a modified flag
   */
  void setDrawings(ArrayList dr, boolean mod)
  {
    drawingWriter.setDrawings(dr, mod);
  }

  /**
   * Accessor for the charts on this sheet
   *
   * @return the charts
   */
  Chart[] getCharts()
  {
    return  drawingWriter.getCharts();
  }

  /**
   * Check all the merged cells for borders.  If the merge record has
   * borders, then we need to rejig the cell formats to take account of this.
   * This is called by the write method of the WritableWorkbookImpl, so that
   * any new XFRecords that are created may be written out with the others
   */
  void checkMergedBorders()
  {
    Range[] mcells = mergedCells.getMergedCells();
    ArrayList borderFormats = new ArrayList();
    for (int mci = 0 ; mci < mcells.length ; mci++)
    {
      Range range = mcells[mci];
      Cell topLeft = range.getTopLeft();
      XFRecord tlformat = (XFRecord) topLeft.getCellFormat();

      if (tlformat != null && 
          tlformat.hasBorders() == true && 
          !tlformat.isRead())
      {
        try
        {
          CellXFRecord cf1 = new CellXFRecord(tlformat);
          Cell bottomRight = range.getBottomRight();

          cf1.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
          cf1.setBorder(Border.LEFT, 
                        tlformat.getBorderLine(Border.LEFT), 
                        tlformat.getBorderColour(Border.LEFT));
          cf1.setBorder(Border.TOP,  
                        tlformat.getBorderLine(Border.TOP),
                        tlformat.getBorderColour(Border.TOP));

          if (topLeft.getRow() == bottomRight.getRow())
          {
            cf1.setBorder(Border.BOTTOM, 
                          tlformat.getBorderLine(Border.BOTTOM),
                          tlformat.getBorderColour(Border.BOTTOM));
          }

          if (topLeft.getColumn() == bottomRight.getColumn())
          {
            cf1.setBorder(Border.RIGHT, 
                          tlformat.getBorderLine(Border.RIGHT),
                          tlformat.getBorderColour(Border.RIGHT));
          }

          int index = borderFormats.indexOf(cf1);
          if (index != -1)
          {
            cf1 = (CellXFRecord) borderFormats.get(index);
          }
          else
          {
            borderFormats.add(cf1);
          }
          ( (WritableCell) topLeft).setCellFormat(cf1);

          // Handle the bottom left corner
          if (bottomRight.getRow() > topLeft.getRow())
          {
            // Handle the corner cell
            if (bottomRight.getColumn() != topLeft.getColumn())
            {
              CellXFRecord cf2 = new CellXFRecord(tlformat);
              cf2.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf2.setBorder(Border.LEFT, 
                            tlformat.getBorderLine(Border.LEFT),
                            tlformat.getBorderColour(Border.LEFT));
              cf2.setBorder(Border.BOTTOM,  
                            tlformat.getBorderLine(Border.BOTTOM),
                            tlformat.getBorderColour(Border.BOTTOM));
            
              index = borderFormats.indexOf(cf2);
              if (index != -1)
              {
                cf2 = (CellXFRecord) borderFormats.get(index);
              }
              else
              {
                borderFormats.add(cf2);
              }

              sheet.addCell(new Blank(topLeft.getColumn(), 
                                      bottomRight.getRow(), cf2));
            }

            // Handle the cells down the left hand side (and along the
            // right too, if necessary)
            for (int i = topLeft.getRow() + 1; i < bottomRight.getRow() ;i++)
            {
              CellXFRecord cf3 = new CellXFRecord(tlformat);
              cf3.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf3.setBorder(Border.LEFT, 
                            tlformat.getBorderLine(Border.LEFT),
                            tlformat.getBorderColour(Border.LEFT));

              if (topLeft.getColumn() == bottomRight.getColumn())
              {
                cf3.setBorder(Border.RIGHT, 
                              tlformat.getBorderLine(Border.RIGHT),
                              tlformat.getBorderColour(Border.RIGHT));
              }

              index = borderFormats.indexOf(cf3);
              if (index != -1)
              {
                cf3 = (CellXFRecord) borderFormats.get(index);
              }
              else
              {
                borderFormats.add(cf3);
              }

              sheet.addCell(new Blank(topLeft.getColumn(), i, cf3));
            }
          }

          // Handle the top right corner
          if (bottomRight.getColumn() > topLeft.getColumn())
          {
            if (bottomRight.getRow() != topLeft.getRow())
            {
              // Handle the corner cell
              CellXFRecord cf6 = new CellXFRecord(tlformat);
              cf6.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf6.setBorder(Border.RIGHT, 
                            tlformat.getBorderLine(Border.RIGHT),
                            tlformat.getBorderColour(Border.RIGHT));
              cf6.setBorder(Border.TOP,  
                            tlformat.getBorderLine(Border.TOP),
                            tlformat.getBorderColour(Border.RIGHT));
              index = borderFormats.indexOf(cf6);
              if (index != -1)
              {
                cf6 = (CellXFRecord) borderFormats.get(index);
              }
              else
              {
                borderFormats.add(cf6);
              }
              
              sheet.addCell(new Blank(bottomRight.getColumn(), 
                                      topLeft.getRow(), cf6));
            }

            // Handle the cells along the right
            for (int i = topLeft.getRow() + 1; 
                     i < bottomRight.getRow() ;i++)
            {
              CellXFRecord cf7 = new CellXFRecord(tlformat);
              cf7.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf7.setBorder(Border.RIGHT, 
                            tlformat.getBorderLine(Border.RIGHT),
                            tlformat.getBorderColour(Border.RIGHT));

              index = borderFormats.indexOf(cf7);
              if (index != -1)
              {
                cf7 = (CellXFRecord) borderFormats.get(index);
              }
              else
              {
                borderFormats.add(cf7);
              }
              
              sheet.addCell(new Blank(bottomRight.getColumn(), i, cf7));
            }

            // Handle the cells along the top, and along the bottom too
            for (int i = topLeft.getColumn() + 1; 
                     i < bottomRight.getColumn() ;i++)
            {
              CellXFRecord cf8 = new CellXFRecord(tlformat);
              cf8.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf8.setBorder(Border.TOP, 
                            tlformat.getBorderLine(Border.TOP),
                            tlformat.getBorderColour(Border.TOP));
              
              if (topLeft.getRow() == bottomRight.getRow())
              {
                cf8.setBorder(Border.BOTTOM, 
                              tlformat.getBorderLine(Border.BOTTOM),
                              tlformat.getBorderColour(Border.BOTTOM));
              }

              index = borderFormats.indexOf(cf8);
              if (index != -1)
              {
                cf8 = (CellXFRecord) borderFormats.get(index);
              }
              else
              {
                borderFormats.add(cf8);
              }
              
              sheet.addCell(new Blank(i, topLeft.getRow(), cf8));
            }
          }

          // Handle the bottom right corner
          if (bottomRight.getColumn() > topLeft.getColumn() ||
              bottomRight.getRow() > topLeft.getRow())
          {
            // Handle the corner cell
            CellXFRecord cf4 = new CellXFRecord(tlformat);
            cf4.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
            cf4.setBorder(Border.RIGHT,  
                          tlformat.getBorderLine(Border.RIGHT),
                          tlformat.getBorderColour(Border.RIGHT));
            cf4.setBorder(Border.BOTTOM, 
                          tlformat.getBorderLine(Border.BOTTOM),
                          tlformat.getBorderColour(Border.BOTTOM));

            if (bottomRight.getRow() == topLeft.getRow())
            {
              cf4.setBorder(Border.TOP, 
                            tlformat.getBorderLine(Border.TOP),
                            tlformat.getBorderColour(Border.TOP));
            }

            if (bottomRight.getColumn() == topLeft.getColumn())
            {
              cf4.setBorder(Border.LEFT, 
                            tlformat.getBorderLine(Border.LEFT),
                            tlformat.getBorderColour(Border.LEFT));
            }

            index = borderFormats.indexOf(cf4);
            if (index != -1)
            {
              cf4 = (CellXFRecord) borderFormats.get(index);
            }
            else
            {
              borderFormats.add(cf4);
            }

            sheet.addCell(new Blank(bottomRight.getColumn(), 
                                    bottomRight.getRow(), cf4));

            // Handle the cells along the bottom (and along the top
            // as well, if appropriate)
            for (int i = topLeft.getColumn() + 1; 
                     i < bottomRight.getColumn() ;i++)
            {
              CellXFRecord cf5 = new CellXFRecord(tlformat);
              cf5.setBorder(Border.ALL, BorderLineStyle.NONE, Colour.BLACK);
              cf5.setBorder(Border.BOTTOM, 
                            tlformat.getBorderLine(Border.BOTTOM),
                            tlformat.getBorderColour(Border.BOTTOM));

              if (topLeft.getRow() == bottomRight.getRow())
              {
                cf5.setBorder(Border.TOP, 
                              tlformat.getBorderLine(Border.TOP),
                              tlformat.getBorderColour(Border.TOP));
              }

              index = borderFormats.indexOf(cf5);
              if (index != -1)
              {
                cf5 = (CellXFRecord) borderFormats.get(index);
              }
              else
              {
                borderFormats.add(cf5);
              }
              
              sheet.addCell(new Blank(i, bottomRight.getRow(), cf5));
            }
          }
        }
        catch (WriteException e)
        {
          // just log e.toString(), not the whole stack trace
          logger.warn(e.toString());  
        }
      }
    }
  }

  /**
   * Get the cells in the column.  Don't use the interface method
   * getColumn for this as this will create loads of empty cells,
   * and we could do without that overhead
   */
  private Cell[] getColumn(int col)
  {
    // Find the last non-null cell
    boolean found = false;
    int row = numRows - 1;

    while (row >= 0 && !found)
    {
      if (rows[row] != null &&
          rows[row].getCell(col) != null)
      {
        found = true;
      }
      else
      {
        row--;
      }
    }

    // Only create entries for non-empty cells
    Cell[] cells = new Cell[row+1];

    for (int i = 0; i <= row; i++)
    {
      cells[i] = rows[i] != null ? rows[i].getCell(col) : null;
    }

    return cells;
  }

  /**
   * Sets a flag to indicate that this sheet contains a chart only
   */
  void setChartOnly()
  {
    chartOnly = true;
  }

  /**
   * Sets the environment specific print record
   *
   * @param pls the print record
   */
  void setPLS(PLSRecord pls)
  {
    plsRecord = pls;
  }

  /**
   * Sets the button property set record
   *
   * @param bps the button property set
   */
  void setButtonPropertySet(ButtonPropertySetRecord bps)
  {
    buttonPropertySet = bps;
  }

  /**
   * Sets the data validations
   *
   * @param dv the data validations
   */
  void setDataValidation(DataValidation dv)
  {
    dataValidation = dv;
  }
}