lyfz_repos/铂金版源码/Biff8/Excel/ExcelWorkbook.cs

using System;
using System.Collections.Generic;
using System.Text;
using Biff8Excel.Interfaces;
using System.IO;
using System.Runtime.InteropServices;
using System.Diagnostics;
using Biff8Excel.Formulas;
using Microsoft.Win32;
using System.Collections;

namespace Biff8Excel.Excel
{
    public class ExcelWorkbook: IDisposable 
    {
        string m_fname;                         // file name
        string m_password ="";                  // password protect the sheet contents
        //int m_WorkbookSizeOnDisk;               // Size in bytes of the saved workbook
        bool bFontsCreated;
        bool bActiveSheetSet;

        List<BoundSheet> mColBoundSheets;       // One boundsheet record for each worksheet
        WorkSheetCollection mColSheets;                  // Collection or worksheets

        List<ExcelFont> mColFonts;              // Collection of Font    Objects
        List<ExcelFormat> mColFormats;          // Collection of Format  Objects
        List<ExcelCellStyle> mColCellStyls;     // Collection of workbook cell styles

        // records
        ExcelSst recSST;
        Helper recHelper;
        ExternSheet recExternSheet;
        Records.BOF recBOF;
        Records.Windows1 recWindow1;
        Records.SupBook recSupBook;
        ExcelBinaryWriter bw;

        //---------------------------------------------------------------------------------------
        // Procedure : Function Calculate
        //           : calculates any Formula cells, required if the workbook is to be viewed
        //           : using the free Excel 97 viewer, as this program does not calculate formulae,
        //           : it only reads the result of a formula value stored on disk by the full version of Excel.
        //---------------------------------------------------------------------------------------
        internal bool Calculate()
        {
            ExcelCell oCell;
            ushort oldFailCount;
            ushort failCount =0;
            ExcelCellDictionary ColFormulaCells;                                 // Collection of formula cells in the worksheet
            ExcelCellDictionary ColNumberFormulaCells;                           // Collection of all number and formula cells in the worksheet

            //  build a collection of number and formula cells
            ColFormulaCells = new ExcelCellDictionary();
            ColNumberFormulaCells = new ExcelCellDictionary();

            foreach (ExcelWorksheet sheet in mColSheets)
            {
                if (sheet.TotalRows > 0)
                {
                    //arrRows = sheet.Rows;

                    foreach (KeyValuePair<ushort, ExcelCellLinkDictionary> kv in sheet.Rows)
                    {
                        foreach (KeyValuePair<ushort, ExcelCell> kvc in kv.Value)
                        {
                            oCell = kvc.Value ;

                            if (oCell.CellType == Globals.CELL_TYPE_FORMULA)
                            {
                                ColFormulaCells.Add(sheet.sheetNumber.ToString("000") + "!" + oCell.CellReference, oCell);
                                ColNumberFormulaCells.Add(sheet.sheetNumber.ToString("000") + "!" + oCell.CellReference, oCell);
                            }
                            else if (oCell.CellType == Globals.CELL_TYPE_NUMBER)
                            {
                                ColNumberFormulaCells.Add(sheet.sheetNumber.ToString("000") + "!" + oCell.CellReference, oCell);
                            }
                        }
                    }
                }       // end if
            }
            //  Loop through the Formula cells and calculate each in turn
            //  If a formula contains a cell reference and that cell has a formula then
            //  it may be that it cannot be resolved until the nested formula is resolved so
            //  we may need to loop more than once through the formula cells to resolve all formulas
            do
            {
                oldFailCount = failCount;
                failCount = 0;

                foreach (DictionaryEntry oCells in ColFormulaCells)
                {
                    if ( !((ExcelCell)oCells.Value).CalculationResolved)
                    {
                        ((ExcelCell)oCells.Value).Calculate(ColNumberFormulaCells);
                        if (((ExcelCell)oCells.Value).CalculationResolved == false)
                            failCount++;
                    }
                }

                // (failCount should always be less than or equal to oldFailCount)
                // if failcount and OldFailCount match then
                //  we cannot resolve any more formulas so exit
            } while (oldFailCount != failCount);

            return (failCount == 0);
        }

        public ExcelCell CreateCell()
        {
            ExcelCell ec = new ExcelCell(this);
            ec.pWorkbook = this;
            return ec;
        }

        //private void CreateDefaultSheets()
        //{
        //    BoundSheet bs;
        //    ExcelWorksheet ws;
        //    for (int i = 1; i < 4; i++)
        //    {
        //        bs = new BoundSheet();
        //        bs.SheetName = "Sheet" + i.ToString();
        //        ws = new ExcelWorksheet(this);
        //        mColBoundSheets.Add(bs);
        //        mColSheets.Add(ws);
        //    }
        //}

        private void CreateDefaultsCellStyles()
        {
            ExcelCellStyle cs;
            for (int i = 1; i < 22; i++)
            {
                //cs = new ExcelCellStyle(this);
                cs = this.CreateStyle();

                // Same across all styles
                cs.VerticalAlignment = EnumVerticalAlignment.Bottom;
                cs.PatternForeColour = EnumColours.SystemBorder;
                cs.PatternBackColour = EnumColours.SystemBackground;
                cs.CellIsLocked = true;

                if (i == 1)
                    cs.StyleType = EnumType.Style;
                else if (i >= 2 & i <= 3)
                {
                    cs.StyleType = EnumType.Style;
                    cs.FontIndex = 1;
                    cs.UseParentStyleOptions = 0xF4;
                }
                if (i >= 4 && i <= 5)
                {
                    cs.StyleType = EnumType.Style;
                    cs.FontIndex = 2;
                    cs.UseParentStyleOptions = 0xF4;
                }
                if (i >= 6 && i <= 15)
                {
                    cs.StyleType = EnumType.Style;
                    cs.UseParentStyleOptions = 0xF4;
                }
                if (i == 16)
                {
                    cs.StyleType = EnumType.Cell;
                }
                if (i >= 17 && i <= 21)
                {
                    cs.StyleType = EnumType.Style;
                    cs.FontIndex = 1;
                    cs.UseParentStyleOptions = 0xF8;

                    if (i == 17)                        // 43
                        cs.FormatIndex = 0x2B;
                    else if (i == 18)
                        cs.FormatIndex = 0x29;          // 41                            
                    else if (i == 19)
                        cs.FormatIndex = 0x2C;          // 44                    
                    else if (i == 20)
                        cs.FormatIndex = 0x2A;          // 42                        
                    else if (i == 21)
                        cs.FormatIndex = 0x9;           // 9

                }
                mColCellStyls.Add(cs);
            }
        }

        internal ushort CreateNewFont(ExcelFont fnt)
        {
            if (fnt.Name.Length == 0)
                throw new Biff8ExcelException("no font name");

            mColFonts.Add(fnt);
            return (ushort)mColFonts.Count;         // one based ???
        }

        internal ushort CreateNewFormat(ExcelFormat fmt)
        {
            if (fmt.FormatString.Length == 0)
                return 0;

            mColFormats.Add(fmt);

            fmt.index = (ushort)(mColFormats.Count + 163);        // zero based userdefined start at index 164
            return fmt.index;
        }

        internal ushort CreateNewStyle(ExcelCellStyle cs)
        {
            mColCellStyls.Add(cs);
            return (ushort)(mColCellStyls.Count - 1);         // zero based
        }

        /// <summary>
        /// 新建样式
        /// </summary>
        /// <returns></returns>
        public ExcelCellStyle CreateStyle()
        {
            ExcelCellStyle cs = new ExcelCellStyle();
            cs.pWorkbook = this;
            return cs;
        }

        /// <summary>
        /// 新建Sheet
        /// </summary>
        /// <param name="sSheetName">显示的名称</param>
        /// <returns></returns>
        public ExcelWorksheet CreateSheet(string sSheetName)
        {
            return CreateSheet(sSheetName, true);
        }

        /// <summary>
        /// 新建Sheet
        /// </summary>
        /// <param name="sSheetName">显示的名称</param>
        /// <param name="bVisible">是否显示</param>
        /// <returns></returns>
        public ExcelWorksheet CreateSheet(string sSheetName, bool bVisible)
        {
            ExcelWorksheet ws;
            BoundSheet bs;

            // create the new worksheet;    
            ws = new ExcelWorksheet(this);              // create a new sheet
            //ws.Workbook = this;                     // pass in a reference to the parent workbook object
            ws.SheetName = sSheetName;
            ws.sheetNumber = (ushort)mColSheets.Count;      // zero index

            mColSheets.Add(ws);                     // add it to the sheets collection

            // Add a Bound Sheet record
            bs = new BoundSheet();
            bs.SheetName = sSheetName;
            bs.Visible = (bVisible == true ? Visibility.Visible : Visibility.Hidden);
            mColBoundSheets.Add(bs);

            return ws;
        }

        internal Interfaces.IExternSheet ExterSheet
        {
            get { return recExternSheet; }
        }

        public ExcelWorkbook()
        {
            mColFonts = new List<ExcelFont>();
            mColFormats = new List<ExcelFormat>();
            mColCellStyls = new List<ExcelCellStyle>();
            mColSheets = new WorkSheetCollection();
            mColBoundSheets = new List<BoundSheet>();
            recSST = new ExcelSst();
            recSupBook = new Biff8Excel.Records.SupBook();
            recExternSheet = new ExternSheet();
            recHelper = new Helper();
            recWindow1 = new Biff8Excel.Records.Windows1();
            recWindow1.ActivewWorksheet = 1;

            recHelper = new Helper();
            recWindow1 = new Biff8Excel.Records.Windows1();

            recWindow1.ActivewWorksheet = 1;

            recHelper.ExcelWorkSheets = mColSheets;
            recExternSheet.Workbook = this;

            CreateDefaultsCellStyles();
            bw = new ExcelBinaryWriter();
            //  'Call CreateDefaultSheets
        }

        internal List<ExcelFont> Fonts
        {
            get { return mColFonts; }
        }
        
        internal List<ExcelFormat> Formats
        {
            get { return mColFormats; }
        }

        /// <summary>
        /// 得到已在的Sheet
        /// </summary>
        /// <param name="sheet">Sheet名称或索引号</param>
        /// <returns></returns>
        public ExcelWorksheet GetSheet(object sheet)
        {
            if (sheet is string)
                return mColSheets[sheet.ToString()];
            else if (sheet is int)
                return mColSheets[Convert.ToInt32(sheet)];

            throw new Biff8ExcelException("no type sheet");
            //if (sheet.ToString() == "0") sheet = 1;
            //return mColSheets[sheet.ToString()];
        }

        public Helper Helper
        {
            get { return recHelper; }
        }

        public string Password
        {
            set { m_password = value; }
                    }

        internal List<ExcelCellStyle> Styles
        {
            get { return mColCellStyls; }
        }

        internal ExcelSst SharedStringTable
        {
            get { return recSST; }
        }

        /// <summary>
        /// 设定活动的Sheet(只能有一个活动的sheet)
        /// </summary>
        ///// <param name="sheet">Sheet名称或索引号</param>
        public object SetActiveSheet
        {
            set
            {
                ExcelWorksheet oSheet;
                int tmp;

                if (bActiveSheetSet)
                    return;

                bActiveSheetSet = true;

                if (int.TryParse(value.ToString(), out tmp))
                {
                    recWindow1.ActivewWorksheet = (ushort)(tmp - 1);          //zero based
                    oSheet = mColSheets[tmp];
                    if (oSheet != null)
                        oSheet.Selected = true;
                }
                else
                {
                    mColSheets[value.ToString()].Selected = true;

                    recWindow1.ActivewWorksheet = mColSheets[value.ToString()].sheetNumber;
                }
            }
        }

        internal ushort SheetIndexFromName(string sSheetName)
        {
            ushort count = 0;
            bool bfound = false ;

            foreach (BoundSheet bs in mColBoundSheets)
            {
                if (bs.SheetName.ToUpper() == sSheetName.ToUpper())
                {
                    bfound = true;
                    break;
                }
                count++;
            }

            //return (bfound ? count : (ushort)-1);

            if (bfound)
                return count;
            else
                throw new Biff8ExcelException("Invalid Sheet Name");
        }

        public void Save(string fname)
        {
            byte[] buffer;
            int count;
            uint offset;
            uint endOfWorkbook;              // position of the end of the workbook stream
            string s;

            m_fname = fname;

            if (this.Calculate() == false)
            {
                //    If MsgBox("Some Formula cells could not be calculated, do you want to create the sheet anyway?" & vbCrLf & vbCrLf & _
                //      "This will only cause a problem if you intent the workbook to be opened using the Microsoft Excel 97 Viewer " & _
                //      "as this program does not recalculate formulae but reads the value from disk" & vbCrLf & vbCrLf & _
                //      "Create anyway?", vbInformation + vbYesNo, "Warning") = vbNo Then
            }

            //  This will create the Sheets for saving to disk,
            //  this is required as any cell styles, fonts, formats etc will only be created
            //  during the saving of a sheet, as we need to no the offsets in the stream for each sheet
            //  we need to precalculate the offsets before the workbook records are written
            foreach (ExcelWorksheet sheet in mColSheets)
            {
                // set the sheet password if supplied
                if (m_password.Length > 0)
                {
                    sheet.password = m_password;
                }
                sheet.CreateSheetBuffer();
            }

            //ExcelBinaryWriter bw = new ExcelBinaryWriter();

            recBOF = new Biff8Excel.Records.BOF();
            recBOF.DataType = Biff8Excel.Records.EnumBofTypes.bofWorkbook;
            buffer = recBOF.GetByte();
            bw.Write(buffer);                // 20

            // Not implemented client implementations of these options
            if (mColSheets.Count > 0)
                this.SetActiveSheet = 1;
                //this.SetActiveSheet(1);

            recWindow1.Width = 15360;               // twips 1024 x
            recWindow1.Height = 11520;              // twips 768
            recWindow1.VerticalScrollBar = true;
            recWindow1.HorizontalScrollBar = true;
            recWindow1.DisplayTabsAtBottom = true;
            recWindow1.SelectedWorksheets = 1;
            recWindow1.TabWidth = 600;              // twips
            buffer = recWindow1.GetByte();
            bw.Write(buffer);
            
            // Default font
            //this.SetDefaultFont("Arial", 10);     // sets default font if not already set
            this.SetDefaultFont("宋体", 12);
            foreach (ExcelFont fnt in mColFonts)
            {
                buffer = fnt.WriteRecord();
                bw.Write(buffer);
            }
            //Default Formats
            this.WriteDefaultFormats();         

            // formats
            foreach (ExcelFormat fmt in mColFormats)
            {
                buffer = fmt.WriteRecord();
                bw.Write(buffer);
            }
            // Cell Styles;
            foreach (ExcelCellStyle cs in mColCellStyls)
            {
                buffer = cs.WriteRecord();
                bw.Write(buffer);
            }
            // Styles
            this.WriteDefaultStyles();          

            // Bound Sheets
            // The bound sheet record for a worksheet holds the absolute byte
            // array position of the BOF record of a worksheet
            // we don't know this yet so we will have to write this record
            // again at the end of the stream write, this is just a place
            // holder in the stream for these records
            foreach (BoundSheet bs in mColBoundSheets)
            {
                bs.StreamOffset = (uint)bw.Position;          // save this records position in the stream
                //bs.StreamOffset = offsets;
                buffer = bs.WriteRecord;
                bw.Write(buffer);
            }
            // Sup Book Record (only using 1)
            recSupBook.TotalSheets = (ushort)mColBoundSheets.Count;
            buffer = recSupBook.GetByte();
            bw.Write(buffer);

            // ExternSheet, Holds indexes to the Sheets used in formulas
            buffer = recExternSheet.WriteRecord();
            bw.Write(buffer);

            //recSST.AbsolutOffset = (uint)bw.Position;
             
            //Shared String Table
            buffer = recSST.WriteRecord();
            bw.Write(buffer);

            // EOF
            s = "0A000000";
            bw.Write(Globals.WriteBytes(s));

            // re-write the bound sheet record with the
            // calculated sheet stream positions
            //offset = bw.Position;          
            offset = (uint)bw.Position;     //  end of workbook stream
            //offset = offsets;
            endOfWorkbook = offset;          // save end of workbook position for moving back later on
            count = 1;
            foreach (BoundSheet bs in mColBoundSheets)
            {
                bw.Position = (int)bs.StreamOffset;
                bs.SheetOffset = offset;
                buffer = bs.WriteRecord;
                bw.Write(buffer);

                offset += mColSheets[count].SizeOnDisk;
                count++;
            }
            bw.Position = (int)endOfWorkbook;        // restore the stream pointer;

            // Write each Sheet
            foreach (ExcelWorksheet ws in mColSheets)
            {
                ws.UpdateAbsoluteOffsets((uint)bw.Position);
                bw.Write(ws.DiskBuffer);
            }

            // Com的结构化保存(Biff8的要求)
            COM.Storage file;
            COM.COMStream Data;

            if (File.Exists(m_fname))
            {
                File.SetAttributes(m_fname, FileAttributes.Normal);
                File.Delete(m_fname);
            }

            file = COM.Storage.CreateDocFile(m_fname, COM.StorageMode.ReadWrite | COM.StorageMode.ShareExclusive);
            Data = file.CreateStream("workbook");
            Data.WriteBytes(bw.Stream);

            //FileStream fs;
            //fs = new FileStream(m_fname, FileMode.OpenOrCreate);
            //fs.Write(bw.Stream, 0, bw.Stream.Length);
            //fs.Close();

            file.Commit(0);
            file.Dispose();
            Data = null;
        }

        /// <summary>
        /// 设定默认字体
        /// </summary>
        /// <param name="Name">字体名</param>
        /// <param name="Size">字体尺寸</param>
        public void SetDefaultFont(string name, ushort size)
        {
            if (bFontsCreated)
                return;

            ExcelFont f;

            bFontsCreated = true;

            // Create 4 Fonts (All the Same???)
            for (int i = 1; i < 5; i++)
            {
                f = new ExcelFont();
                f.Name = name;
                f.Size = size;
                f.Colour = EnumColours.SystemText;
                f.Bold = false;
                mColFonts.Add(f);
            }
        }

        internal string[] SheetNames
        {
            get
            {
                if (mColSheets.Count == 0)
                    return null;

                ushort x = 0;

                string[] s = new string[mColSheets.Count];

                foreach (ExcelWorksheet ws in mColSheets)
                {
                    s[x] = ws.SheetName;
                    x++;
                }
                return s;
            }
        }

        private void WriteDefaultStyles()
        {
            string s;
            s = "93020400108003FF"
              + "93020400118006FF"
              + "93020400128004FF"
              + "93020400138007FF"
              + "93020400008000FF"
              + "93020400148005FF";

            bw.Write(Globals.WriteBytes(s));
        }

        private void WriteDefaultFormats()
        {
            string s;
            s = "1E041800050013000022A322232C2323303B5C2D22A322232C232330"
              + "1E041D00060018000022A322232C2323303B5B5265645D5C2D22A322232C232330"
              + "1E041E00070019000022A322232C2323302E30303B5C2D22A322232C2323302E3030"
              + "1E04230008001E000022A322232C2323302E30303B5B5265645D5C2D22A322232C2323302E3030"
              + "1E0435002A003000005F2D22A3222A20232C2323305F2D3B5C2D22A3222A20232C2323305F2D3B5F2D22A3222A20222D225F2D3B5F2D405F2D"
              + "1E042C0029002700005F2D2A20232C2323305F2D3B5C2D2A20232C2323305F2D3B5F2D2A20222D225F2D3B5F2D405F2D"
              + "1E043D002C003800005F2D22A3222A20232C2323302E30305F2D3B5C2D22A3222A20232C2323302E30305F2D3B5F2D22A3222A20222D223F3F5F2D3B5F2D405F2D"
              + "1E0434002B002F00005F2D2A20232C2323302E30305F2D3B5C2D2A20232C2323302E30305F2D3B5F2D2A20222D223F3F5F2D3B5F2D405F2D";

            bw.Write(Globals.WriteBytes(s));
        }

        //internal int WorkbookSizeOnDisk
        //{
        //    get { return m_WorkbookSizeOnDisk; }
        //}

        #region IDisposable 成员

        public void Dispose()
        {
            mColFonts = null;
            mColFormats = null;
            mColCellStyls = null;
            mColSheets.Dispose();
            mColSheets = null;
            mColBoundSheets = null;
            recSST.Dispose();
            recSST = null;
            recSupBook = null;
            recExternSheet.Dispose();
            recExternSheet = null;
            recHelper.Dispose();
            recHelper = null;
            recWindow1 = null;
        }

        #endregion
    }
}