using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.IO;
using Biff8Excel.COM;

namespace Biff8Excel.Excel
{
    public class ExcelWorksheet : IDisposable 
    {
        ExcelWorkbook pWorkbook;                     // pointer to the parent Workbook (soft reference)

        // This is the Disk representtion of the worksheet
        // it is passed to the workbook when saving
        byte[] m_diskbuffer;
        uint m_diskbufferSize;

        // Row Variables
        ExcelRowLinkDictionary marrRows;                        //  single link of all the rows, not empty
        ushort mTotalRowBlocks;
        ushort mFirstUsedRow;
        ushort mLastUsedRow;

        //bool bFirstUsedRow;
        //bool bLastUsedRow;

        // records
        ExcelPrintSetup recPrintSetup;
        ExcelHeader recHeader;
        ExcelFooter recFooter;

        Index recIndex;
        DefColWidth recDefCol;
        DBCell[] recDBCells;
        MergedCells recMergedCells;
        ColInfo[] recColumns;
        SheetProtection recPassword;
        Records.Dimensions recDimensions;
        Records.Windows2 recWindow2;
        Records.Calcmode recCalcmode;

        ushort mSheetNumber;                        // Sheet Number (index)
        string mSheetName;                          // sheet name;

        EnumWindow2Options rec2WindowOptions;
        EnumColours mGridlineColour;                // grid line colour;

        string m_password="";                       // sheet password

        ExcelBinaryWriter bw;                       // Writes the sheet to a byte stream;

        internal ushort TotalRows
        {
            get { return (ushort)marrRows.Count; }
        }

        //internal ushort LastUsedRow
        //{
        //    get { return mLastUsedRow; }
        //}

        internal ExcelRowLinkDictionary Rows
        {
            get { return marrRows; }
        }

        //private void WriteRowBlocks(COM.Stream bbw)
        private void WriteRowBlocks()
        {
            DBCell dbCell;
            ExcelCell oCell;
            byte[] buffer;
            int[] DBCellOffsets = new int[0];
            //List<int> DBCellOffsets = new List<int>();
            uint firstRowOffset;
            int usedrows = 0;

            ExcelBinaryWriter cellsbuffer = new ExcelBinaryWriter();

            if (mTotalRowBlocks == 0)
                return;

            Array.Resize<DBCell>(ref recDBCells, mTotalRowBlocks);

            int preRowsKey = -1;

            int lRowBlock = 0;
            int currentBlock = 0;

            firstRowOffset = (uint)bw.Position;

            // Loop through each ExcelRowsLink
            foreach (KeyValuePair<ushort, ExcelCellLinkDictionary> rows in marrRows)
            {
                usedrows++;

                lRowBlock = (rows.Key) / 32;
                if (currentBlock < lRowBlock)
                {
                    preRowsKey = rows.Key;

                    bw.Write(cellsbuffer.Stream);

                    // Write the DB Cell record for the Block
                    dbCell = new DBCell();
                    dbCell.RowOffset = (uint)(bw.Position - firstRowOffset);                  //  relative offset to the DBCell offset
                    dbCell.StreamOffset = (int)bw.Position;

                    // 前32行中有数据
                    if (cellsbuffer.Position > 0)
                    {
                        Array.Resize<int>(ref DBCellOffsets, usedrows);
                        dbCell.CellOffsets = DBCellOffsets;
                        buffer = dbCell.WriteRecord();
                        bw.Write(buffer);
                        usedrows = 0;
                    }

                    recDBCells[currentBlock] = dbCell;

                    //First Pass, Write any Used Rows in the Block
                    firstRowOffset = (uint)bw.Position;

                    cellsbuffer = new ExcelBinaryWriter();
                    currentBlock++;

                    //如还是小说明下一区段的数据为空，循环下去
                    while (currentBlock < lRowBlock)
                    {
                        preRowsKey = rows.Key;

                        //// Write the DB Cell record for the Block
                        dbCell = new DBCell();
                        dbCell.RowOffset = (uint)(bw.Position - firstRowOffset);                  //  relative offset to the DBCell offset
                        dbCell.StreamOffset = (int)bw.Position;

                        recDBCells[currentBlock] = dbCell;

                        //First Pass, Write any Used Rows in the Block
                        firstRowOffset = (uint)bw.Position;

                        currentBlock++;
                    }
                }

                if (rows.Value.Style.TopLineStyle == EnumLineStyle.Double || rows.Value.Style.TopLineStyle == EnumLineStyle.Thick)
                    rows.Value.AdditionalSpaceAbove = true;
                if (rows.Value.Style.BottomLineStyle == EnumLineStyle.Double || rows.Value.Style.BottomLineStyle == EnumLineStyle.Thick)
                    rows.Value.AdditionalSpaceBelow = true;

                //rows.Value.StreamPosition = (int)bw.Position;
                //rows.Value.RowBlock = (ushort)(lRowBlock + 1);

                foreach (KeyValuePair<ushort, ExcelCell> cells in rows.Value)
                {
                    oCell = cells.Value;
                    if (oCell.HasThickBorderAbove)
                        rows.Value.AdditionalSpaceAbove = true;
                    if (oCell.HasThickBorderBelow)
                        rows.Value.AdditionalSpaceBelow = true;

                    // If this is a Label,
                    // get the index into the Shared String Table for this Labels
                    if (oCell.CellType == Globals.CELL_TYPE_LABEL)
                        oCell.SSTindex = this.GetSSTIndex(oCell);

                    if ( oCell.StyleIndex < 0)
                    {
                        // row style takes precedence over the columns
                        if (rows.Value.StyleIndex == 0xF)                                         // default index
                            oCell.StyleIndex = this.GetColumnStyleIndex(cells.Key);               // get the columns index (may be &HF as well)
                        else
                            oCell.StyleIndex = rows.Value.StyleIndex;                             // uses the rows style
                    }
                    // Write the Cell to the stream
                    buffer = oCell.WriteCell();
                    cellsbuffer.Write(buffer);
                    //bw.WriteBytes(buffer);
                }
                buffer = rows.Value.WriteRecord();
                bw.Write(buffer);
            }

            // 循环结束有未处理的行数据则把这些行数据加在最后
            if ( preRowsKey <= marrRows.LastUsedRow )
            //if ( preRowsKey <= marrRows.tail.Index )
            {
                //First Pass, Write any Used Rows in the Block
                bw.Write(cellsbuffer.Stream);

                // Write the DB Cell record for the Block
                dbCell = new DBCell();
                dbCell.RowOffset = (uint)(bw.Position - firstRowOffset);                  //  relative offset to the DBCell offset
                //dbCell.StreamOffset = (int)bw.Position;

                // calculete the relative position from the db cell record
                // to the first cell record of each row ??????
                // 前32行中有数据
                if (cellsbuffer.Position > 0)
                {
                    Array.Resize<int>(ref DBCellOffsets, usedrows);
                    dbCell.CellOffsets = DBCellOffsets;
                    buffer = dbCell.WriteRecord();
                    bw.Write(buffer);
                }
                //cellsbuffer.clear();

                recDBCells[currentBlock] = dbCell;
            }
        }

        /// <summary>
        /// 以byte数组形式生成Sheet信息
        /// </summary>
        internal void CreateSheetBuffer()
        {
            byte[] buffer;
            Records.BOF obj;                 // using object for the records, so we only need to dim oen variable
            string s;
            int y;
            
            // Bof Record
            obj = new Biff8Excel.Records.BOF();
            obj.DataType = Biff8Excel.Records.EnumBofTypes.bofWorkSheet;
            buffer = obj.GetByte();
            bw.Write(buffer);
            //  ''''''''''''''''''''''''''''''''''''''''
            //  ''' Index Record
            //  ''''''''''''''''''''''''''''''''''''''''
            //  ''' This record stores the Absolute Stream Offsets of the DB Records
            //  ''' As they are not written until the later in the Stream we need to
            //  ''' reserve space in the stream for these offsets which we will add later
            //  ''''''''''''''''''''''''''''''''''''''''
            uint[] DBCellOffsets;
            y = this.CalculateRowBlocks();
            if (y > 0)
            {
                DBCellOffsets = new uint[y];

                recIndex = new Index();
                recIndex.StreamOffset = (uint)bw.Position;        // Save the Position of this record in the stream
                recIndex.FirstRowUsed = mFirstUsedRow;
                recIndex.LastRowUsed = mLastUsedRow;
                recIndex.DefColumnRecordOffset = 0;
                recIndex.DBCellOffsets = DBCellOffsets;
                buffer = recIndex.WriteRecord();
                bw.Write(buffer);
            }

            //  Calc Mode (always set to Automatic)
            buffer = recCalcmode.GetByte();
            bw.Write(buffer);

            // Print Headers
            buffer = recPrintSetup.WriteRecord_PRINTHEADER();
            bw.Write(buffer);
            
            // Print Grid
            buffer = recPrintSetup.WriteRecord_PRINTGRID();
            bw.Write(buffer);

            // WS BOOL
            buffer = recPrintSetup.WriteRecord_WSBOOL();
            bw.Write(buffer);

            // Header
            buffer = recHeader.WriteRecord();
            bw.Write(buffer);

            // Footer;
            buffer = recFooter.WriteRecord();
            bw.Write(buffer);

            // HCenter
            buffer = recPrintSetup.WriteRecord_HCENTER();
            bw.Write(buffer);

            // VCenter
            buffer = recPrintSetup.Writerecord_VCENTER();
            bw.Write(buffer);
            
            // Left Margin
            buffer = recPrintSetup.Writerecord_LMARGIN();
            bw.Write(buffer);

            // Right Margin
            buffer = recPrintSetup.Writerecord_RMARGIN();
            bw.Write(buffer);

            // Top Margin
            buffer = recPrintSetup.Writerecord_TMARGIN();
            bw.Write(buffer);

            // Bottom Margin
            buffer = recPrintSetup.Writerecord_BMARGIN();
            bw.Write(buffer);

            // Print Setup
            buffer = recPrintSetup.WriteRecord_SETUP();
            bw.Write(buffer);
            
            // Password Protection 
            if (m_password.Length > 0)
            {
                recPassword.ProtectSheet(m_password);
                buffer = recPassword.WriteRecord();
                bw.Write(buffer);
            }
            // DefColWidth
            recDefCol = new DefColWidth();
            recDefCol.StreamOffset = (int)bw.Position;
            buffer = recDefCol.WriteRecord();
            bw.Write(buffer);

            // ColInfos
            if (recColumns != null)
            {
                y = recColumns.Length;
                for (int i = 0; i < y; i++)
                {
                    buffer = recColumns[i].WriteRecord();
                    bw.Write(buffer);
                }
            }
            
            // Dimensions
            recDimensions.FirstRow = mFirstUsedRow;
            recDimensions.LastRow = (uint)(mLastUsedRow +1);
            buffer = recDimensions.GetByte();
            bw.Write(buffer);

            // Row Block (ouch);
            // Rows, Cells and DBCells
            //this.WriteRowBlocks(bw);
            this.WriteRowBlocks();

            // Window2
            recWindow2.OptionalFlags = (ushort)rec2WindowOptions;
            buffer = recWindow2.GetByte();
            bw.Write(buffer);

            // Merged Cells
            if (recMergedCells.Total > 0)
            {
                buffer = recMergedCells.WriteRecord();
                bw.Write(buffer);
            }

            // EOF
            s = "0A000000";
            bw.Write(Globals.WriteBytes(s)); 
            m_diskbufferSize = (uint)bw.Length;
        }

        private uint GetSSTIndex(Biff8Excel.Interfaces.ICell newCell)
        {
            ExcelSst sst;
            try
            {
                sst = Workbook.SharedStringTable;

                return sst.AddString(newCell.ResolvedValue.ToString());
            }
            finally
            {
                sst = null;
            }
        }

        internal void UpdateAbsoluteOffsets(uint lOffset)
        {
            try
            {
                //byte[] streambuffer = bw.Stream;
                uint[] dbCells = null;

                if (recIndex == null)
                {
                    //workBookbw.WriteBytes(streambuffer);
                    m_diskbuffer = bw.Stream;
                    return;
                }

                recIndex.DefColumnRecordOffset = (uint)(recDefCol.StreamOffset + lOffset);

                Array.Resize<uint>(ref dbCells, recIndex.DBCellOffsets.Length);

                for (int i = 0; i < recIndex.DBCellOffsets.Length; i++)
                {
                    dbCells[i] = (uint)recDBCells[i].StreamOffset + lOffset;
                }

                recIndex.DBCellOffsets = dbCells;

                byte[] buffer = recIndex.WriteRecord();

                bw.Position = (int)recIndex.StreamOffset;
                bw.Write(buffer);

                m_diskbuffer = bw.Stream;

                //buffer.CopyTo(streambuffer, recIndex.StreamOffset);
                //workBookbw.WriteBytes(streambuffer);
            }
            finally
            {
                //bw.clear();
                bw = null;
            }
        }

        public ExcelCellLinkDictionary GetRow(ushort rowNumber)
        {
             //zero based row numbers;
            rowNumber--;
            if (rowNumber < 0) rowNumber = 0;

            ExcelCellLinkDictionary oRow;

            if (marrRows.TryGetValue(rowNumber, out oRow) == true)
                return oRow;

            oRow = new ExcelCellLinkDictionary(pWorkbook);
            oRow.rowNumber = rowNumber;
            marrRows.Add(rowNumber, oRow);
            //oRow.sheetNumber = mSheetNumber;

            return oRow;
        }

        public void SetColumnStyle(ushort iFromColumn, ushort iToColumn)
        {
            SetColumnStyle(iFromColumn, iToColumn, 64, null);
        }

        public void SetColumnStyle(ushort iFromColumn, ushort iToColumn, ushort iPixelWidth)
        {
            SetColumnStyle(iFromColumn, iToColumn, iPixelWidth, null);
        }

        public void SetColumnStyle(ushort iFromColumn, ushort iToColumn, ushort iPixelWidth, ExcelCellStyle oStyle)
        {
            int ub;
            ushort idx;
            ColInfo cInfo;
            try
            {
                if (recColumns != null)
                {
                    ub = (int)recColumns.Length + 1;
                    Array.Resize<ColInfo>(ref recColumns, ub);
                }
                else
                {
                    recColumns = new ColInfo[1];
                }

                if (oStyle != null)
                    //idx = this.GetXFIndex(oStyle);
                    idx = (ushort)oStyle.GetXFIndex();
                else
                    idx = 0xA;              // default cell style;

                cInfo = new ColInfo();
                cInfo.SetColumnStyle(iFromColumn, iToColumn, iPixelWidth, idx);
                recColumns[recColumns.Length - 1] = cInfo;
            }
            catch
            {
                throw;
            }
        }

        internal ushort GetColumnStyleIndex(ushort iColumn)
        {
            ushort result = 0xF;        // default style

            //int ub = 0;
            //try
            //{
            //    if ( recColumns != null)
            //        ub = (int)(recColumns.Length - 1);                
            //}
            //catch
            //{
            //    return 0;
            //}

            if (recColumns == null)
                return 0;

            for (int i = 0; i < recColumns.Length; i++)
            {
                if (iColumn >= recColumns[i].FromColumn && iColumn <= recColumns[i].ToColumn)
                    return recColumns[i].StyleIndex;
            }
            return result;
        }

        /// <summary>
        /// 合并单元格    
        /// </summary>
        /// <param name="iFirstColumn">启始列号</param>
        /// <param name="iFirstRow">启始行号</param>
        /// <param name="iLastColumn">终止列号</param>
        /// <param name="iLastRow">终止行号</param>
        public void MergeCels(ushort iFirstColumn, ushort iFirstRow, ushort iLastColumn, ushort iLastRow)
        {
            recMergedCells.AddMergedCells(iFirstRow, iLastRow, iFirstColumn, iLastColumn);
        }

        private ushort CalculateRowBlocks()
        {
            mTotalRowBlocks = 0;
            if ( marrRows.Count != 0)
            {
                mTotalRowBlocks = (ushort)((marrRows.LastUsedRow + 1) / 32);
                if ( ( marrRows.LastUsedRow + 1) % 32 > 0)
                    mTotalRowBlocks++;
            }
            return mTotalRowBlocks; 
        }

        public string SheetName
        {
            set { mSheetName = value; }
            get { return mSheetName; }
        }

        public EnumColours GridlineColour
        {
            set 
            { 
                mGridlineColour = value;
                recWindow2.GridLineColour = (ushort)value;
            }
        }

        public bool ShowFormulas
        {
            set
            {
                if (value)
                    rec2WindowOptions = rec2WindowOptions | EnumWindow2Options.ShowFormulas;
                else
                    rec2WindowOptions = rec2WindowOptions & (~EnumWindow2Options.ShowFormulas);
            }
        }

        public bool Visible
        {
            set
            {
                if (value)
                    rec2WindowOptions = rec2WindowOptions | EnumWindow2Options.SheetVisible;
                else
                    rec2WindowOptions = rec2WindowOptions & (~EnumWindow2Options.SheetVisible);
            }
        }

        public bool GridLines
        {
            set
            {
                if (value)
                    rec2WindowOptions = rec2WindowOptions | EnumWindow2Options.ShowGridLines;
                else
                    rec2WindowOptions = rec2WindowOptions & (~EnumWindow2Options.ShowGridLines);
            }
        }

        public bool SheetHeaders
        {
            set
            {
                if (value)
                    rec2WindowOptions = rec2WindowOptions | EnumWindow2Options.ShowSheetHeader;
                else
                    rec2WindowOptions = rec2WindowOptions & (~EnumWindow2Options.ShowSheetHeader);
            }
        }

        public bool Selected
        {
            set
            {
                if (value)
                    rec2WindowOptions = rec2WindowOptions | EnumWindow2Options.SheetSelected;
                else
                    rec2WindowOptions = rec2WindowOptions & (~EnumWindow2Options.SheetSelected);
            }
        }

        
        public bool ShowZeroValues
        {
            set
            {
                if (value)
                    rec2WindowOptions = rec2WindowOptions | EnumWindow2Options.ShowZeroValues;
                else
                    rec2WindowOptions = rec2WindowOptions & (~EnumWindow2Options.ShowZeroValues);
            }
        }

        public ExcelHeader Header
        {
            get { return recHeader; }
            set { recHeader = value; }
        }

        public ExcelFooter Footer
        {
            get { return recFooter; }
            set { recFooter = value; }
        }

        public ExcelPrintSetup PrintSetup
        {
            get { return recPrintSetup; }
            set { recPrintSetup = value; }
        }

        internal string password
        {
            set { m_password = value; }
        }

        internal uint SizeOnDisk
        {
            get { return m_diskbufferSize; }
        }

        internal byte[] DiskBuffer
        {
            get { return m_diskbuffer; }
        }

        internal ushort sheetNumber
        {
            set { mSheetNumber = value; }
            get { return mSheetNumber; }
        }

        public void SumRows(ushort columnFrom, ushort columnTo, ushort sumFrom, ushort sumTo, ushort rowNumber)
        {
            SumRows(columnFrom, columnTo, sumFrom, sumTo, rowNumber, null);
        }

        public void SumRows(ushort columnFrom, ushort columnTo, ushort sumFrom, ushort sumTo, ushort rowNumber, ExcelCellStyle oCellStyle)
        {
            AddSummaryRow("SUM", columnFrom, columnTo, sumFrom, sumTo, rowNumber, oCellStyle);
        }

        public void AverageRows(ushort columnFrom, ushort columnTo, ushort sumFrom, ushort sumTo, ushort rowNumber)
        {
            SumRows(columnFrom, columnTo, sumFrom, sumTo, rowNumber, null);
        }

        public void AverageRows(ushort columnFrom, ushort columnTo, ushort sumFrom, ushort sumTo, ushort rowNumber, ExcelCellStyle oCellStyle)
        {
            AddSummaryRow("AVERAGE", columnFrom, columnTo, sumFrom, sumTo, rowNumber, oCellStyle);
        }

        public void CountRows(ushort columnFrom, ushort columnTo, ushort sumFrom, ushort sumTo, ushort rowNumber)
        {
            SumRows(columnFrom, columnTo, sumFrom, sumTo, rowNumber, null);
        }

        public void CountRows(ushort columnFrom, ushort columnTo, ushort sumFrom, ushort sumTo, ushort rowNumber, ExcelCellStyle oCellStyle)
        {
            AddSummaryRow("COUNT", columnFrom, columnTo, sumFrom, sumTo, rowNumber, oCellStyle);
        }

        private void AddSummaryRow(string sType, ushort columnFrom, ushort columnTo, ushort sumFrom, ushort sumTo, ushort rowNumber)
        {
            AddSummaryRow(sType, columnFrom, columnTo, sumFrom, sumTo, rowNumber, null);
        }
          
        private void AddSummaryRow(string sType, ushort columnFrom, ushort columnTo, ushort sumFrom, 
            ushort sumTo, ushort rowNumber, ExcelCellStyle oCellStyle)
        {
            ExcelCell oCell;
            //ushort Row;
            string colName;
            string s;

            for (ushort column = columnFrom; column <= columnTo; column++)
            {
                colName = Globals.ConvertColNumToString(column);
                s = "=" + sType + "(" + colName + sumFrom.ToString() + ":" + colName + sumTo.ToString() + ")";
                oCell = AddCell(column, rowNumber, s, oCellStyle);
                oCell.Workbook = Workbook;
            }
            oCell = null;
        }

        /// <summary>
        /// 在Worksheet中加入单元格
        /// </summary>
        /// <param name="columnNumber">列号</param>
        /// <param name="rowNumber">行号</param>
        /// <returns>加入的单元格</returns>
        public ExcelCell AddCell(ushort columnNumber, ushort rowNumber)
        {
            return AddCell(columnNumber, rowNumber, null, null);
        }

        /// <summary>
        /// 在Worksheet中加入单元格
        /// </summary>
        /// <param name="columnNumber">列号</param>
        /// <param name="rowNumber">行号</param>
        /// <param name="vValue">单元格的值</param>
        /// <returns>加入的单元格</returns>
        public ExcelCell AddCell(ushort columnNumber, ushort rowNumber, object vValue)
        {
            return AddCell(columnNumber, rowNumber, vValue, null);
        }

        //public ExcelCell AddCell(ushort columnNumber, ushort rowNumber, ExcelCellStyle oCellStyle)
        //{
        //    return AddCell(columnNumber, rowNumber, null, oCellStyle);
        //}

        /// <summary>
        /// 在Worksheet中加入单元格
        /// </summary>
        /// <param name="columnNumber">列号</param>
        /// <param name="rowNumber">行号</param>
        /// <param name="vValue">单元格的值</param>
        /// <param name="oCellStyle">单元格的样式</param>
        /// <returns>加入的单元格</returns>
        public ExcelCell AddCell(ushort columnNumber, ushort rowNumber, object vValue, ExcelCellStyle oCellStyle)
        {
            //ExcelRow oRow;
            ExcelCell oCell;

            if (columnNumber > 256)
                throw new Biff8ExcelException("超出列的最大值");

            // Row and Columns are zero based
            rowNumber--;
            columnNumber--;

            if (rowNumber < 0) rowNumber = 0;
            if (columnNumber < 0) columnNumber = 0;

            //// create the new cell
            //oCell = new ExcelCell(pWorkbook);
            oCell = pWorkbook.CreateCell();
            oCell.Workbook = Workbook;
            oCell.sheetNumber = mSheetNumber;
            oCell.Row = rowNumber;
            oCell.Column = columnNumber;

            if (vValue != null)
                oCell.Value = vValue;

            if (oCellStyle != null)
            {
                oCell.Style = oCellStyle;
                oCell.StyleIndex = oCellStyle.GetXFIndex();
            }

            marrRows.AddCell(rowNumber, columnNumber, oCell);
            return oCell;
        }

        /// <summary>
        /// 加入多个单元格(一行内)
        /// </summary>
        /// <param name="columnFrom">起始列号</param>
        /// <param name="columnTo">终止列号</param>
        /// <param name="rowNumber">行号</param>
        /// <param name="oCellStyle">样式</param>
        /// <param name="vValues">值数组(数组长度为 columnFrom - columnTo 多于部分被忽略)</param>
        public void AddCells(ushort columnFrom, ushort columnTo, ushort rowNumber, ExcelCellStyle oCellStyle, params object[] vValues)
        {
            ushort Count = 0;
            object vValue;

            try
            {
                for (ushort columnNumber = columnFrom; columnNumber <= columnTo; columnNumber++)
                {
                    if (vValues.Length > Count)
                        vValue = vValues[Count];
                    else
                        vValue = null;

                    AddCell(columnNumber, rowNumber, vValue, oCellStyle);
                    Count++;
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 得到已存在的单元格(如单元格不存在则新增)
        /// </summary>
        /// <param name="columnNumber">列号</param>
        /// <param name="rowNumber">行号</param>
        /// <returns>得到的单元格</returns>
        public ExcelCell GetCell(ushort columnNumber, ushort rowNumber)
        {
            // Row and Columns are zero based;
            rowNumber--;
            columnNumber--;

            if (rowNumber < 0) rowNumber = 0;
            if (columnNumber < 0) columnNumber = 0;


            ExcelCellLinkDictionary cl;
            ExcelCell oCell;

            // get the containing row object
            if ( !marrRows.TryGetValue(rowNumber, out cl))
            {
                cl = new ExcelCellLinkDictionary(pWorkbook);
            }

            // get the containing cell object
            if (!cl.TryGetValue(columnNumber, out oCell))
            {
                //oCell = new ExcelCell(pWorkbook);
                oCell = pWorkbook.CreateCell();
                oCell.Workbook = Workbook;
                oCell.sheetNumber = mSheetNumber;
                oCell.Row = rowNumber;
                oCell.Column = columnNumber;
                cl.Add(columnNumber, oCell);
            }

            marrRows.AddCell(rowNumber, columnNumber, oCell);
            return oCell;
        }

        internal ExcelWorkbook Workbook
        {
            get{return pWorkbook; }
            set{pWorkbook = value;}
        }

        internal ExcelWorksheet(ExcelWorkbook pWorkbook)
        {
            marrRows = new ExcelRowLinkDictionary(pWorkbook);
            this.pWorkbook = pWorkbook;

            recWindow2 = new Biff8Excel.Records.Windows2();
            recDimensions = new Biff8Excel.Records.Dimensions();
            recCalcmode = new Biff8Excel.Records.Calcmode();
            recMergedCells = new MergedCells();
            recPrintSetup = new ExcelPrintSetup();

            recHeader = new ExcelHeader();
            recFooter = new ExcelFooter();
            recPassword = new SheetProtection();

            bw = new ExcelBinaryWriter();

            // default options for each sheet
            this.GridLines = true;
            SheetHeaders = true;
            Visible = true;

            mGridlineColour = EnumColours.SystemBorder;
            rec2WindowOptions = EnumWindow2Options.ShowGridLines | EnumWindow2Options.ShowZeroValues | EnumWindow2Options.ShowSheetHeader;

            recWindow2.OptionalFlags = (ushort)rec2WindowOptions;
            recWindow2.GridLineColour = (ushort)mGridlineColour;
        }

        #region IDisposable 成员

        public void Dispose()
        {
            recIndex.Dispose();
            recIndex = null;
            recWindow2 = null;
            recCalcmode = null;
            recDimensions = null;
            recMergedCells.Dispose();
            recMergedCells = null;
            recPrintSetup.Dispose();
            recPrintSetup = null;
            recPassword.Dispose();
            recPassword = null;
            recHeader.Dispose();
            recHeader = null;
            recFooter = null;
            bw.Close();
            bw = null;
            marrRows = null;
            recDefCol.Dispose();
            recDefCol = null;
            recFooter.Dispose();
            recFooter = null;
        }

        #endregion
    }
}