예제 #1
0
  /** Writes out the data validations */
  private void writeDataValidation() throws IOException {
    if (dataValidation != null && validatedCells.size() == 0) {
      // only data validations are those read in
      dataValidation.write(outputFile);
      return;
    }

    if (dataValidation == null && validatedCells.size() > 0) {
      int comboBoxId =
          sheet.getComboBox() != null
              ? sheet.getComboBox().getObjectId()
              : DataValidation.DEFAULT_OBJECT_ID;
      dataValidation =
          new DataValidation(
              comboBoxId, sheet.getWorkbook(), sheet.getWorkbook(), workbookSettings);

      for (Iterator i = validatedCells.iterator(); i.hasNext(); ) {
        CellValue cv = (CellValue) i.next();
        CellFeatures cf = cv.getCellFeatures();
        DataValiditySettingsRecord dvsr = new DataValiditySettingsRecord(cf.getDVParser());
        dataValidation.add(dvsr);
      }
      dataValidation.write(outputFile);
      return;
    }

    // Read and write validations
    for (Iterator i = validatedCells.iterator(); i.hasNext(); ) {
      CellValue cv = (CellValue) i.next();
      CellFeatures cf = cv.getCellFeatures();
      DataValiditySettingsRecord dvsr = new DataValiditySettingsRecord(cf.getDVParser());
      dataValidation.add(dvsr);
    }
    dataValidation.write(outputFile);
    return;
  }
예제 #2
0
  /**
   * 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.TOP));
              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());
        }
      }
    }
  }
예제 #3
0
  /**
   * Writes out this sheet. First writes out the standard sheet information then writes out each row
   * in turn. Once all the rows have been written out, it retrospectively adjusts the offset
   * references in the file
   *
   * @exception IOException
   */
  public void write() throws IOException {
    Assert.verify(rows != null);

    // This worksheet consists of just one chart, so write it and return
    if (chartOnly) {
      drawingWriter.write(outputFile);
      return;
    }

    BOFRecord bof = new BOFRecord(BOFRecord.sheet);
    outputFile.write(bof);

    // Compute the number of blocks of 32 rows that will be needed
    int numBlocks = numRows / 32;
    if (numRows - numBlocks * 32 != 0) {
      numBlocks++;
    }

    int indexPos = outputFile.getPos();

    // Write the index record out now in order to serve as a place holder
    // The bof passed in is the bof of the workbook, not this sheet
    IndexRecord indexRecord = new IndexRecord(0, numRows, numBlocks);
    outputFile.write(indexRecord);

    if (settings.getAutomaticFormulaCalculation()) {
      CalcModeRecord cmr = new CalcModeRecord(CalcModeRecord.automatic);
      outputFile.write(cmr);
    } else {
      CalcModeRecord cmr = new CalcModeRecord(CalcModeRecord.manual);
      outputFile.write(cmr);
    }

    CalcCountRecord ccr = new CalcCountRecord(0x64);
    outputFile.write(ccr);

    RefModeRecord rmr = new RefModeRecord();
    outputFile.write(rmr);

    IterationRecord itr = new IterationRecord(false);
    outputFile.write(itr);

    DeltaRecord dtr = new DeltaRecord(0.001);
    outputFile.write(dtr);

    SaveRecalcRecord srr = new SaveRecalcRecord(settings.getRecalculateFormulasBeforeSave());
    outputFile.write(srr);

    PrintHeadersRecord phr = new PrintHeadersRecord(settings.getPrintHeaders());
    outputFile.write(phr);

    PrintGridLinesRecord pglr = new PrintGridLinesRecord(settings.getPrintGridLines());
    outputFile.write(pglr);

    GridSetRecord gsr = new GridSetRecord(true);
    outputFile.write(gsr);

    GuttersRecord gutr = new GuttersRecord();
    gutr.setMaxColumnOutline(maxColumnOutlineLevel + 1);
    gutr.setMaxRowOutline(maxRowOutlineLevel + 1);

    outputFile.write(gutr);

    DefaultRowHeightRecord drhr =
        new DefaultRowHeightRecord(
            settings.getDefaultRowHeight(),
            settings.getDefaultRowHeight() != SheetSettings.DEFAULT_DEFAULT_ROW_HEIGHT);
    outputFile.write(drhr);

    if (maxRowOutlineLevel > 0) {
      workspaceOptions.setRowOutlines(true);
    }

    if (maxColumnOutlineLevel > 0) {
      workspaceOptions.setColumnOutlines(true);
    }

    workspaceOptions.setFitToPages(settings.getFitToPages());
    outputFile.write(workspaceOptions);

    if (rowBreaks.size() > 0) {
      int[] rb = new int[rowBreaks.size()];

      for (int i = 0; i < rb.length; i++) {
        rb[i] = ((Integer) rowBreaks.get(i)).intValue();
      }

      HorizontalPageBreaksRecord hpbr = new HorizontalPageBreaksRecord(rb);
      outputFile.write(hpbr);
    }

    if (columnBreaks.size() > 0) {
      int[] rb = new int[columnBreaks.size()];

      for (int i = 0; i < rb.length; i++) {
        rb[i] = ((Integer) columnBreaks.get(i)).intValue();
      }

      VerticalPageBreaksRecord hpbr = new VerticalPageBreaksRecord(rb);
      outputFile.write(hpbr);
    }

    HeaderRecord header = new HeaderRecord(settings.getHeader().toString());
    outputFile.write(header);

    FooterRecord footer = new FooterRecord(settings.getFooter().toString());
    outputFile.write(footer);

    HorizontalCentreRecord hcr = new HorizontalCentreRecord(settings.isHorizontalCentre());
    outputFile.write(hcr);

    VerticalCentreRecord vcr = new VerticalCentreRecord(settings.isVerticalCentre());
    outputFile.write(vcr);

    // Write out the margins if they don't equal the default
    if (settings.getLeftMargin() != settings.getDefaultWidthMargin()) {
      MarginRecord mr = new LeftMarginRecord(settings.getLeftMargin());
      outputFile.write(mr);
    }

    if (settings.getRightMargin() != settings.getDefaultWidthMargin()) {
      MarginRecord mr = new RightMarginRecord(settings.getRightMargin());
      outputFile.write(mr);
    }

    if (settings.getTopMargin() != settings.getDefaultHeightMargin()) {
      MarginRecord mr = new TopMarginRecord(settings.getTopMargin());
      outputFile.write(mr);
    }

    if (settings.getBottomMargin() != settings.getDefaultHeightMargin()) {
      MarginRecord mr = new BottomMarginRecord(settings.getBottomMargin());
      outputFile.write(mr);
    }

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

    SetupRecord setup = new SetupRecord(settings);
    outputFile.write(setup);

    if (settings.isProtected()) {
      ProtectRecord pr = new ProtectRecord(settings.isProtected());
      outputFile.write(pr);

      ScenarioProtectRecord spr = new ScenarioProtectRecord(settings.isProtected());
      outputFile.write(spr);

      ObjectProtectRecord opr = new ObjectProtectRecord(settings.isProtected());
      outputFile.write(opr);

      if (settings.getPassword() != null) {
        PasswordRecord pw = new PasswordRecord(settings.getPassword());
        outputFile.write(pw);
      } else if (settings.getPasswordHash() != 0) {
        PasswordRecord pw = new PasswordRecord(settings.getPasswordHash());
        outputFile.write(pw);
      }
    }

    indexRecord.setDataStartPosition(outputFile.getPos());
    DefaultColumnWidth dcw = new DefaultColumnWidth(settings.getDefaultColumnWidth());
    outputFile.write(dcw);

    // Get a handle to the normal styles
    WritableCellFormat normalStyle = sheet.getWorkbook().getStyles().getNormalStyle();
    WritableCellFormat defaultDateFormat = sheet.getWorkbook().getStyles().getDefaultDateFormat();

    // Write out all the column formats
    ColumnInfoRecord cir = null;
    for (Iterator colit = columnFormats.iterator(); colit.hasNext(); ) {
      cir = (ColumnInfoRecord) colit.next();

      // Writing out the column info with index 0x100 causes excel to crash
      if (cir.getColumn() < 0x100) {
        outputFile.write(cir);
      }

      XFRecord xfr = cir.getCellFormat();

      if (xfr != normalStyle && cir.getColumn() < 0x100) {
        // Make this the format for every cell in the column
        Cell[] cells = getColumn(cir.getColumn());

        for (int i = 0; i < cells.length; i++) {
          if (cells[i] != null
              && (cells[i].getCellFormat() == normalStyle
                  || cells[i].getCellFormat() == defaultDateFormat)) {
            // The cell has no overriding format specified, so
            // set it to the column default
            ((WritableCell) cells[i]).setCellFormat(xfr);
          }
        }
      }
    }

    // Write out the auto filter
    if (autoFilter != null) {
      autoFilter.write(outputFile);
    }

    DimensionRecord dr = new DimensionRecord(numRows, numCols);
    outputFile.write(dr);

    // Write out all the rows, in blocks of 32
    for (int block = 0; block < numBlocks; block++) {
      DBCellRecord dbcell = new DBCellRecord(outputFile.getPos());

      int blockRows = Math.min(32, numRows - block * 32);
      boolean firstRow = true;

      // First write out all the row records
      for (int i = block * 32; i < block * 32 + blockRows; i++) {
        if (rows[i] != null) {
          rows[i].write(outputFile);
          if (firstRow) {
            dbcell.setCellOffset(outputFile.getPos());
            firstRow = false;
          }
        }
      }

      // Now write out all the cells
      for (int i = block * 32; i < block * 32 + blockRows; i++) {
        if (rows[i] != null) {
          dbcell.addCellRowPosition(outputFile.getPos());
          rows[i].writeCells(outputFile);
        }
      }

      // Now set the current file position in the index record
      indexRecord.addBlockPosition(outputFile.getPos());

      // Set the position of the file pointer and write out the DBCell
      // record
      dbcell.setPosition(outputFile.getPos());
      outputFile.write(dbcell);
    }

    // Do the drawings and charts if enabled
    if (!workbookSettings.getDrawingsDisabled()) {
      drawingWriter.write(outputFile);
    }

    Window2Record w2r = new Window2Record(settings);
    outputFile.write(w2r);

    // Handle the frozen panes
    if (settings.getHorizontalFreeze() != 0 || settings.getVerticalFreeze() != 0) {
      PaneRecord pr = new PaneRecord(settings.getHorizontalFreeze(), settings.getVerticalFreeze());
      outputFile.write(pr);

      // Handle the selection record.  First, there will always be a top left
      SelectionRecord sr = new SelectionRecord(SelectionRecord.upperLeft, 0, 0);
      outputFile.write(sr);

      // Top right
      if (settings.getHorizontalFreeze() != 0) {
        sr = new SelectionRecord(SelectionRecord.upperRight, settings.getHorizontalFreeze(), 0);
        outputFile.write(sr);
      }

      // Bottom left
      if (settings.getVerticalFreeze() != 0) {
        sr = new SelectionRecord(SelectionRecord.lowerLeft, 0, settings.getVerticalFreeze());
        outputFile.write(sr);
      }

      // Bottom right
      if (settings.getHorizontalFreeze() != 0 && settings.getVerticalFreeze() != 0) {
        sr =
            new SelectionRecord(
                SelectionRecord.lowerRight,
                settings.getHorizontalFreeze(),
                settings.getVerticalFreeze());
        outputFile.write(sr);
      }

      Weird1Record w1r = new Weird1Record();
      outputFile.write(w1r);
    } else {
      // No frozen panes - just write out the selection record for the
      // whole sheet
      SelectionRecord sr = new SelectionRecord(SelectionRecord.upperLeft, 0, 0);
      outputFile.write(sr);
    }

    // Handle the zoom factor
    if (settings.getZoomFactor() != 100) {
      SCLRecord sclr = new SCLRecord(settings.getZoomFactor());
      outputFile.write(sclr);
    }

    // Now write out all the merged cells
    mergedCells.write(outputFile);

    // Write out all the hyperlinks
    Iterator hi = hyperlinks.iterator();
    WritableHyperlink hlr = null;
    while (hi.hasNext()) {
      hlr = (WritableHyperlink) hi.next();
      outputFile.write(hlr);
    }

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

    // Write out the data validations
    if (dataValidation != null || validatedCells.size() > 0) {
      writeDataValidation();
    }

    // Write out the conditional formats
    if (conditionalFormats != null && conditionalFormats.size() > 0) {
      for (Iterator i = conditionalFormats.iterator(); i.hasNext(); ) {
        ConditionalFormat cf = (ConditionalFormat) i.next();
        cf.write(outputFile);
      }
    }

    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);
  }