sheetwriter.java

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

/*********************************************************************
*
*      Copyright (C) 2002 Andrew Khan
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
***************************************************************************/

package jxl.write.biff;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.TreeSet;

import common.Assert;
import common.Logger;

import jxl.Cell;
import jxl.Range;
import jxl.SheetSettings;
import jxl.WorkbookSettings;
import jxl.write.WritableWorkbook;
import jxl.write.WritableCell;
import jxl.write.WritableHyperlink;
import jxl.write.WriteException;
import jxl.write.Blank;
import jxl.format.CellFormat;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.biff.XFRecord;
import jxl.biff.WorkspaceInformationRecord;
import jxl.biff.drawing.Chart;
import jxl.biff.drawing.SheetDrawingWriter;

/**
 * Contains the functionality necessary for writing out a sheet.  Originally
 * this was incorporated in WritableSheetImpl, but was moved out into
 * a dedicated class in order to reduce the over bloated nature of that
 * class
 */
final class SheetWriter
{
  /**
   * The logger
   */
  private static Logger logger = Logger.getLogger(SheetWriter.class);
    
  /**
   * A handle to the output file which the binary data is written to
   */
  private File outputFile;

  /**
   * The rows within this sheet
   */
  private RowRecord[] rows;

  /**
   * A number of rows.  This is a count of the maximum row number + 1
   */
  private int numRows;

  /**
   * The number of columns.  This is a count of the maximum column number + 1
   */
  private int numCols;

  /**
   * The page header
   */
  private HeaderRecord header;
  /**
   * The page footer
   */
  private FooterRecord footer;
  /**
   * The settings for the sheet
   */
  private SheetSettings settings;
  /**
   * The settings for the workbook
   */
  private WorkbookSettings workbookSettings;
  /**
   * Array of row page breaks
   */
  private ArrayList rowBreaks;
  /**
   * Array of hyperlinks
   */
  private ArrayList hyperlinks;
  /**
   * The data validation validations
   */
  private DataValidation dataValidation;

  /**
   * The list of merged ranges
   */
  private MergedCells mergedCells;

  /**
   * The environment specific print record
   */
  private PLSRecord plsRecord;

  /**
   * The button property ste
   */
  private ButtonPropertySetRecord buttonPropertySet;

  /**
   * The workspace options
   */
  private WorkspaceInformationRecord workspaceOptions;
  /** 
   * The column format overrides
   */
  private TreeSet columnFormats;

  /**
   * The list of drawings
   */
  private SheetDrawingWriter drawingWriter;

  /**
   * Flag indicates that this sheet contains just a chart, and nothing
   * else
   */
  private boolean chartOnly;

  /**
   * A handle back to the writable sheet, in order for this class
   * to invoke the get accessor methods
   */
  private WritableSheetImpl sheet;


  /**
   * Creates a new <code>SheetWriter</code> instance.
   *
   * @param of the output file
   */
  public SheetWriter(File of,
                     WritableSheetImpl wsi,
                     WorkbookSettings ws)
  {
    outputFile = of;
    sheet = wsi;
    workspaceOptions = new WorkspaceInformationRecord();
    workbookSettings = ws;
    chartOnly = false;
    drawingWriter = new SheetDrawingWriter(ws);
  }

  /**
   * 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;
    }

    int bofpos = outputFile.getPos();

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

    CalcModeRecord cmr = new CalcModeRecord(CalcModeRecord.automatic);
    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(true);
    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();
    outputFile.write(gutr);

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

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

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

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

    HorizontalCentreRecord hcr = new HorizontalCentreRecord(false);
    outputFile.write(hcr);

    VerticalCentreRecord vcr = new VerticalCentreRecord(false);
    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);
    
    // Write out all the column formats
    ColumnInfoRecord cir = null;
    int tmpi = 0;
    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 != WritableWorkbook.NORMAL_STYLE && 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() == WritableWorkbook.NORMAL_STYLE ||
               cells[i].getCellFormat() == DateRecord.defaultDateFormat))
          {
            // The cell has no overriding format specified, so
            // set it to the column default
            ((WritableCell) cells[i]).setCellFormat(xfr);
          }
        }
      }
    }

    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);
    }
    
    // Write out the data validations
    if (dataValidation != null)
    {
      dataValidation.write(outputFile);
    }

    // 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;