sheetcopier.java

java读写Excel文档的API

    // Copy the data validations
    DataValidation rdv = fromSheet.getDataValidation();
    if (rdv != null)
    {
      dataValidation = new DataValidation(rdv, 
                                          toSheet.getWorkbook(), 
                                          toSheet.getWorkbook(),
                                          workbookSettings);
      int objid = dataValidation.getComboBoxObjectId();
      if (objid != 0)
      {
        comboBox = (ComboBox) drawings.get(objid);
      }
    }

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

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

    // Copy the environment specific print record
    if (fromSheet.getPLS() != null)
    {
      if (fromSheet.getWorkbookBof().isBiff7())
      {
        logger.warn("Cannot copy Biff7 print settings record - ignoring");
      }
      else
      {
        plsRecord = new PLSRecord(fromSheet.getPLS());
      }
    }

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

    importNames();

    // Copy the outline levels
    maxRowOutlineLevel = fromSheet.getMaxRowOutlineLevel();
    maxColumnOutlineLevel = fromSheet.getMaxColumnOutlineLevel();
  }

  /**
   * Performs a shallow copy of the specified cell
   */
  private WritableCell shallowCopyCell(Cell cell)
  {
    CellType ct = cell.getType();
    WritableCell newCell = null;

    if (ct == CellType.LABEL)
    {
      newCell = new Label((LabelCell) cell);
    }
    else if (ct == CellType.NUMBER)
    {
      newCell = new Number((NumberCell) cell);
    }
    else if (ct == CellType.DATE)
    {
      newCell = new DateTime((DateCell) cell);
    }
    else if (ct == CellType.BOOLEAN)
    {
      newCell = new Boolean((BooleanCell) cell);
    }
    else if (ct == CellType.NUMBER_FORMULA)
    {
      newCell = new ReadNumberFormulaRecord((FormulaData) cell);
    }
    else if (ct == CellType.STRING_FORMULA)
    {
      newCell = new ReadStringFormulaRecord((FormulaData) cell);
    }
    else if( ct == CellType.BOOLEAN_FORMULA)
    {
      newCell = new ReadBooleanFormulaRecord((FormulaData) cell);
    }
    else if (ct == CellType.DATE_FORMULA)
    {
      newCell = new ReadDateFormulaRecord((FormulaData) cell);
    }
    else if(ct == CellType.FORMULA_ERROR)
    {
      newCell = new ReadErrorFormulaRecord((FormulaData) cell);
    }
    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
        newCell = new Blank(cell);
      }
    }
    
    return newCell;
  }

  /** 
   * Performs a deep copy of the specified cell, handling the cell format
   * 
   * @param cell the cell to copy
   */
  private WritableCell deepCopyCell(Cell cell)
  {
    WritableCell c = shallowCopyCell(cell);

    if (c == null)
    {
      return c;
    }

    if (c instanceof ReadFormulaRecord)
    {
      ReadFormulaRecord rfr = (ReadFormulaRecord) c;
      boolean crossSheetReference = !rfr.handleImportedCellReferences
        (fromSheet.getWorkbook(),
         fromSheet.getWorkbook(),
         workbookSettings);
      
      if (crossSheetReference)
      {
        try
        {
        logger.warn("Formula " + rfr.getFormula() +
                    " in cell " + 
                    CellReferenceHelper.getCellReference(cell.getColumn(),
                                                         cell.getRow()) +
                    " cannot be imported because it references another " +
                    " sheet from the source workbook");
        }
        catch (FormulaException e)
        {
          logger.warn("Formula  in cell " + 
                      CellReferenceHelper.getCellReference(cell.getColumn(),
                                                           cell.getRow()) +
                      " cannot be imported:  " + e.getMessage());
        }
        
        // Create a new error formula and add it instead
        c = new Formula(cell.getColumn(), cell.getRow(), "\"ERROR\"");
      }
    }

    // Copy the cell format
    CellFormat cf = c.getCellFormat();
    int index = ( (XFRecord) cf).getXFIndex();
    WritableCellFormat wcf = (WritableCellFormat) 
      xfRecords.get(new Integer(index));

    if (wcf == null)
    {
      wcf = copyCellFormat(cf);
    }

    c.setCellFormat(wcf);

    return c;
  }

  /** 
   * Perform a shallow copy of the cells from the specified sheet into this one
   */
  void shallowCopyCells()
  {
    // Copy the cells
    int cells = fromSheet.getRows();
    Cell[] row = null;
    Cell cell = null;
    for (int i = 0;  i < cells; i++)
    {
      row = fromSheet.getRow(i);

      for (int j = 0; j < row.length; j++)
      {
        cell = row[j];
        WritableCell c = shallowCopyCell(cell);

        // 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 (c != null)
          {
            toSheet.addCell(c);
          }
        }
        catch (WriteException e)
        {
          Assert.verify(false);
        }
      }
    }
    numRows = toSheet.getRows();
  }

  /** 
   * Perform a deep copy of the cells from the specified sheet into this one
   */
  void deepCopyCells()
  {
    // Copy the cells
    int cells = fromSheet.getRows();
    Cell[] row = null;
    Cell cell = null;
    for (int i = 0;  i < cells; i++)
    {
      row = fromSheet.getRow(i);

      for (int j = 0; j < row.length; j++)
      {
        cell = row[j];
        WritableCell c = deepCopyCell(cell);

        // 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 (c != null)
          {
            toSheet.addCell(c);
          }
        }
        catch (WriteException e)
        {
          Assert.verify(false);
        }
      }
    }
  }

  /**
   * Returns an initialized copy of the cell format
   *
   * @param cf the cell format to copy
   * @return a deep copy of the cell format
   */
  private WritableCellFormat copyCellFormat(CellFormat cf)
  {
    try
    {
      // just do a deep copy of the cell format for now.  This will create
      // a copy of the format and font also - in the future this may
      // need to be sorted out
      XFRecord xfr = (XFRecord) cf;
      WritableCellFormat f = new WritableCellFormat(xfr);
      formatRecords.addStyle(f);

      // Maintain the local list of formats
      int xfIndex = xfr.getXFIndex();
      xfRecords.put(new Integer(xfIndex), f);

      int fontIndex = xfr.getFontIndex();
      fonts.put(new Integer(fontIndex), new Integer(f.getFontIndex()));

      int formatIndex = xfr.getFormatRecord();
      formats.put(new Integer(formatIndex), new Integer(f.getFormatRecord()));

      return f;
    }
    catch (NumFormatRecordsException e)
    {
      logger.warn("Maximum number of format records exceeded.  Using " +
                  "default format.");

      return WritableWorkbook.NORMAL_STYLE;
    }
  }

  /**
   * Imports any names defined on the source sheet to the destination workbook
   */
  private void importNames()
  {
    WorkbookParser fromWorkbook = (WorkbookParser) fromSheet.getWorkbook();
    WritableWorkbook toWorkbook = toSheet.getWorkbook();
    int fromSheetIndex = fromWorkbook.getIndex(fromSheet);
    NameRecord[] nameRecords = fromWorkbook.getNameRecords();
    String[] names = toWorkbook.getRangeNames();

    for (int i = 0 ; i < nameRecords.length ;i++)
    {
      NameRecord.NameRange[] nameRanges = nameRecords[i].getRanges();
      
      for (int j = 0; j < nameRanges.length; j++)
      {
        int nameSheetIndex = fromWorkbook.getExternalSheetIndex
          (nameRanges[j].getExternalSheet());

        if (fromSheetIndex == nameSheetIndex)
        {
          String name = nameRecords[i].getName();
          if (Arrays.binarySearch(names, name) < 0)
          {
            toWorkbook.addNameArea(name,
                                   toSheet,
                                   nameRanges[j].getFirstColumn(),
                                   nameRanges[j].getFirstRow(),
                                   nameRanges[j].getLastColumn(),
                                   nameRanges[j].getLastRow());
          }
          else
          {
            logger.warn("Named range " + name + 
                        " is already present in the destination workbook");
          }
                                 
        }
      }
    }
  }

  /**
   * Gets the number of rows - allows for the case where formatting has
   * been applied to rows, even though the row has no data
   *
   * @return the number of rows
   */
  int getRows()
  {
    return numRows;
  }

  /** 
   * Accessor for the maximum column outline level
   *
   * @return the maximum column outline level, or 0 if no outlines/groups
   */
  public int getMaxColumnOutlineLevel() 
  {
    return maxColumnOutlineLevel;
  }

  /** 
   * Accessor for the maximum row outline level
   *
   * @return the maximum row outline level, or 0 if no outlines/groups
   */
  public int getMaxRowOutlineLevel() 
  {
    return maxRowOutlineLevel;
  }
}