How to Read and Write .xlsx (Excel 2007) File - Part II

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Xml;
using System.IO;
using Ionic.Zip;
using System.Data;
using System.Runtime.Serialization.Formatters.Binary;

namespace Excel2007
{
    public class StringWriter : System.IO.StringWriter
    {
        Encoding encoding;

        public StringWriter(StringBuilder builder, Encoding encoding)
            : base(builder)
        {
            this.encoding = encoding;
        }

        public override Encoding Encoding
        {
            get { return encoding; }
        }
    }

    public class WorkBook
    {

        WorkSheetCollection _listWorkSheet;
        #region Public Block
        public WorkSheetCollection WorkSheets
        {
            get
            {
                if (_listWorkSheet == null)
                    _listWorkSheet = new WorkSheetCollection();
                return _listWorkSheet;

            }
        }
        public void Save(Stream outputStream)
        {
            using (Ionic.Zip.ZipFile zipFile = new Ionic.Zip.ZipFile())
            {
                zipFile.AddEntry("[Content_Types].xml", (name, stream) => writeContentXml(stream));
                zipFile.AddEntry("xl\\workbook.xml", (name, stream) => writeWorkBook(stream));
                createWorkSheetsEntries(zipFile);
                zipFile.AddEntry("xl\\sharedStrings.xml", (name, stream) => writeSharedString(stream));
                zipFile.AddEntry("_rels\\.rels", (name, stream) => writeRelations(stream));
                zipFile.AddEntry("docProps\\app.xml", (name, stream) => writeApp(stream));
                zipFile.AddEntry("docProps\\core.xml", (name, stream) => writeCore(stream));
                zipFile.AddEntry("xl\\styles.xml", (name, stream) => writeStyleSheet(stream));
                zipFile.AddEntry("xl\\_rels\\workbook.xml.rels", (name, stream) => writeWorkBookRelations(stream));
                zipFile.AddEntry("xl\\theme\\theme1.xml", (name, stream) => writeTheme(stream));
                zipFile.Save(outputStream);
            }
        }
        #endregion

        #region Private Block
        void createWorkSheetsEntries(ZipFile zipFile)
        {
            sharedStringValues = new List<string>();
            int index = 1;
            foreach (WorkSheet workSheet in WorkSheets)
            {
                foreach (Cell cell in workSheet.Cells)
                {
                    if (cell.Value is string && !sharedStringValues.Contains((string)cell.Value))
                        sharedStringValues.Add((string)cell.Value);
                }
                using (Excel2007.StringWriter sw = new Excel2007.StringWriter(new StringBuilder(), Encoding.UTF8))
                {
                    using (XmlTextWriter writer = new XmlTextWriter(sw))
                    {
                        writeWorkSheet(writer, workSheet);
                    }
                    zipFile.AddEntry("xl\\worksheets\\sheet" + index + ".xml",sw.ToString());
                }
                
                //zipFile.AddEntry("xl\\worksheets\\sheet" + index + ".xml", delegate(string name, Stream stream) {
                //    writeWorkSheet(workSheet.Clone(), stream); });
                index++;
            }
        }

        #region Xlsx Write Methods

        List<string> sharedStringValues;
        void writeApp(Stream stream)
        {
            using (StreamWriter writer = new StreamWriter(stream))
            {
                writer.Write(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes"" ?> 

<Properties xmlns=""http://schemas.openxmlformats.org/officeDocument/2006/extended-properties"" xmlns:vt=""http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"">
  <Application>Microsoft Excel</Application> 
  <DocSecurity>0</DocSecurity> 
  <ScaleCrop>false</ScaleCrop> 
 <HeadingPairs>
 <vt:vector size=""2"" baseType=""variant"">
 <vt:variant>
  <vt:lpstr>Worksheets</vt:lpstr> 
  </vt:variant>
 <vt:variant>
<vt:i4>"); writer.Write(WorkSheets.Count); writer.Write("</vt:i4>");
                writer.Write(@"</vt:variant>
  </vt:vector>
  </HeadingPairs>
 <TitlesOfParts>
 <vt:vector size="""); writer.Write(this.WorkSheets.Count); writer.Write(@""" baseType=""lpstr"">");
                foreach (WorkSheet sheet in WorkSheets)
                {
                    writer.Write("<vt:lpstr>");
                    writer.Write(sheet.Name);
                    writer.Write("</vt:lpstr>");

                }
                writer.Write(@"
   </vt:vector>
  </TitlesOfParts>
  <LinksUpToDate>false</LinksUpToDate> 
  <SharedDoc>false</SharedDoc> 
  <HyperlinksChanged>false</HyperlinksChanged> 
  <AppVersion>14.0300</AppVersion> 
  </Properties>");
            }
        }
        void writeCore(Stream stream)
        {
            using (StreamWriter writer = new StreamWriter(stream))
            {
                writer.Write(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes"" ?> 
 <cp:coreProperties xmlns:cp=""http://schemas.openxmlformats.org/package/2006/metadata/core-properties"" xmlns:dc=""http://purl.org/dc/elements/1.1/"" xmlns:dcterms=""http://purl.org/dc/terms/"" xmlns:dcmitype=""http://purl.org/dc/dcmitype/"" xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"">
  <dc:creator>Administrator</dc:creator> 
  <cp:lastModifiedBy>Windows User</cp:lastModifiedBy> 
  <dcterms:created xsi:type=""dcterms:W3CDTF"">2011-06-16T08:14:40Z</dcterms:created> 
  <dcterms:modified xsi:type=""dcterms:W3CDTF"">2011-06-16T08:14:40Z</dcterms:modified> 
  </cp:coreProperties>");
            }
        }
        void writeStyleSheet(Stream stream)
        {
            using (StreamWriter writer = new StreamWriter(stream))
            {
                writer.Write(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<styleSheet xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" xmlns:mc=""http://schemas.openxmlformats.org/markup-compatibility/2006"" mc:Ignorable=""x14ac"" xmlns:x14ac=""http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac""><fonts count=""18"" x14ac:knownFonts=""1""><font><sz val=""11""/><color theme=""1""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><sz val=""11""/><color theme=""1""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><b/><sz val=""18""/><color theme=""3""/><name val=""Cambria""/><family val=""2""/><scheme val=""major""/></font><font><b/><sz val=""15""/><color theme=""3""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><b/><sz val=""13""/><color theme=""3""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><b/><sz val=""11""/><color theme=""3""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><sz val=""11""/><color rgb=""FF006100""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><sz val=""11""/><color rgb=""FF9C0006""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><sz val=""11""/><color rgb=""FF9C6500""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><sz val=""11""/><color rgb=""FF3F3F76""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><b/><sz val=""11""/><color rgb=""FF3F3F3F""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><b/><sz val=""11""/><color rgb=""FFFA7D00""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><sz val=""11""/><color rgb=""FFFA7D00""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><b/><sz val=""11""/><color theme=""0""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><sz val=""11""/><color rgb=""FFFF0000""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><i/><sz val=""11""/><color rgb=""FF7F7F7F""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><b/><sz val=""11""/><color theme=""1""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font><font><sz val=""11""/><color theme=""0""/><name val=""Calibri""/><family val=""2""/><scheme val=""minor""/></font></fonts><fills count=""33""><fill><patternFill patternType=""none""/></fill><fill><patternFill patternType=""gray125""/></fill><fill><patternFill patternType=""solid""><fgColor rgb=""FFC6EFCE""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor rgb=""FFFFC7CE""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor rgb=""FFFFEB9C""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor rgb=""FFFFCC99""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor rgb=""FFF2F2F2""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor rgb=""FFA5A5A5""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor rgb=""FFFFFFCC""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""4""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""4"" tint=""0.79998168889431442""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""4"" tint=""0.59999389629810485""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""4"" tint=""0.39997558519241921""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""5""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""5"" tint=""0.79998168889431442""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""5"" tint=""0.59999389629810485""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""5"" tint=""0.39997558519241921""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""6""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""6"" tint=""0.79998168889431442""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""6"" tint=""0.59999389629810485""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""6"" tint=""0.39997558519241921""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""7""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""7"" tint=""0.79998168889431442""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""7"" tint=""0.59999389629810485""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""7"" tint=""0.39997558519241921""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""8""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""8"" tint=""0.79998168889431442""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""8"" tint=""0.59999389629810485""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""8"" tint=""0.39997558519241921""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""9""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""9"" tint=""0.79998168889431442""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""9"" tint=""0.59999389629810485""/><bgColor indexed=""65""/></patternFill></fill><fill><patternFill patternType=""solid""><fgColor theme=""9"" tint=""0.39997558519241921""/><bgColor indexed=""65""/></patternFill></fill></fills><borders count=""10""><border><left/><right/><top/><bottom/><diagonal/></border><border><left/><right/><top/><bottom style=""thick""><color theme=""4""/></bottom><diagonal/></border><border><left/><right/><top/><bottom style=""thick""><color theme=""4"" tint=""0.499984740745262""/></bottom><diagonal/></border><border><left/><right/><top/><bottom style=""medium""><color theme=""4"" tint=""0.39997558519241921""/></bottom><diagonal/></border><border><left style=""thin""><color rgb=""FF7F7F7F""/></left><right style=""thin""><color rgb=""FF7F7F7F""/></right><top style=""thin""><color rgb=""FF7F7F7F""/></top><bottom style=""thin""><color rgb=""FF7F7F7F""/></bottom><diagonal/></border><border><left style=""thin""><color rgb=""FF3F3F3F""/></left><right style=""thin""><color rgb=""FF3F3F3F""/></right><top style=""thin""><color rgb=""FF3F3F3F""/></top><bottom style=""thin""><color rgb=""FF3F3F3F""/></bottom><diagonal/></border><border><left/><right/><top/><bottom style=""double""><color rgb=""FFFF8001""/></bottom><diagonal/></border><border><left style=""double""><color rgb=""FF3F3F3F""/></left><right style=""double""><color rgb=""FF3F3F3F""/></right><top style=""double""><color rgb=""FF3F3F3F""/></top><bottom style=""double""><color rgb=""FF3F3F3F""/></bottom><diagonal/></border><border><left style=""thin""><color rgb=""FFB2B2B2""/></left><right style=""thin""><color rgb=""FFB2B2B2""/></right><top style=""thin""><color rgb=""FFB2B2B2""/></top><bottom style=""thin""><color rgb=""FFB2B2B2""/></bottom><diagonal/></border><border><left/><right/><top style=""thin""><color theme=""4""/></top><bottom style=""double""><color theme=""4""/></bottom><diagonal/></border></borders><cellStyleXfs count=""42""><xf numFmtId=""0"" fontId=""0"" fillId=""0"" borderId=""0""/><xf numFmtId=""0"" fontId=""2"" fillId=""0"" borderId=""0"" applyNumberFormat=""0"" applyFill=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""3"" fillId=""0"" borderId=""1"" applyNumberFormat=""0"" applyFill=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""4"" fillId=""0"" borderId=""2"" applyNumberFormat=""0"" applyFill=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""5"" fillId=""0"" borderId=""3"" applyNumberFormat=""0"" applyFill=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""5"" fillId=""0"" borderId=""0"" applyNumberFormat=""0"" applyFill=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""6"" fillId=""2"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""7"" fillId=""3"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""8"" fillId=""4"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""9"" fillId=""5"" borderId=""4"" applyNumberFormat=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""10"" fillId=""6"" borderId=""5"" applyNumberFormat=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""11"" fillId=""6"" borderId=""4"" applyNumberFormat=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""12"" fillId=""0"" borderId=""6"" applyNumberFormat=""0"" applyFill=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""13"" fillId=""7"" borderId=""7"" applyNumberFormat=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""14"" fillId=""0"" borderId=""0"" applyNumberFormat=""0"" applyFill=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""1"" fillId=""8"" borderId=""8"" applyNumberFormat=""0"" applyFont=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""15"" fillId=""0"" borderId=""0"" applyNumberFormat=""0"" applyFill=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""16"" fillId=""0"" borderId=""9"" applyNumberFormat=""0"" applyFill=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""17"" fillId=""9"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""1"" fillId=""10"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""1"" fillId=""11"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""17"" fillId=""12"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""17"" fillId=""13"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""1"" fillId=""14"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""1"" fillId=""15"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""17"" fillId=""16"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""17"" fillId=""17"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""1"" fillId=""18"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""1"" fillId=""19"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""17"" fillId=""20"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""17"" fillId=""21"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""1"" fillId=""22"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""1"" fillId=""23"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""17"" fillId=""24"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""17"" fillId=""25"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""1"" fillId=""26"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""1"" fillId=""27"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""17"" fillId=""28"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""17"" fillId=""29"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""1"" fillId=""30"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""1"" fillId=""31"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/><xf numFmtId=""0"" fontId=""17"" fillId=""32"" borderId=""0"" applyNumberFormat=""0"" applyBorder=""0"" applyAlignment=""0"" applyProtection=""0""/></cellStyleXfs><cellXfs count=""1""><xf numFmtId=""0"" fontId=""0"" fillId=""0"" borderId=""0"" xfId=""0""/></cellXfs><cellStyles count=""42""><cellStyle name=""20% - Accent1"" xfId=""19"" builtinId=""30"" customBuiltin=""1""/><cellStyle name=""20% - Accent2"" xfId=""23"" builtinId=""34"" customBuiltin=""1""/><cellStyle name=""20% - Accent3"" xfId=""27"" builtinId=""38"" customBuiltin=""1""/><cellStyle name=""20% - Accent4"" xfId=""31"" builtinId=""42"" customBuiltin=""1""/><cellStyle name=""20% - Accent5"" xfId=""35"" builtinId=""46"" customBuiltin=""1""/><cellStyle name=""20% - Accent6"" xfId=""39"" builtinId=""50"" customBuiltin=""1""/><cellStyle name=""40% - Accent1"" xfId=""20"" builtinId=""31"" customBuiltin=""1""/><cellStyle name=""40% - Accent2"" xfId=""24"" builtinId=""35"" customBuiltin=""1""/><cellStyle name=""40% - Accent3"" xfId=""28"" builtinId=""39"" customBuiltin=""1""/><cellStyle name=""40% - Accent4"" xfId=""32"" builtinId=""43"" customBuiltin=""1""/><cellStyle name=""40% - Accent5"" xfId=""36"" builtinId=""47"" customBuiltin=""1""/><cellStyle name=""40% - Accent6"" xfId=""40"" builtinId=""51"" customBuiltin=""1""/><cellStyle name=""60% - Accent1"" xfId=""21"" builtinId=""32"" customBuiltin=""1""/><cellStyle name=""60% - Accent2"" xfId=""25"" builtinId=""36"" customBuiltin=""1""/><cellStyle name=""60% - Accent3"" xfId=""29"" builtinId=""40"" customBuiltin=""1""/><cellStyle name=""60% - Accent4"" xfId=""33"" builtinId=""44"" customBuiltin=""1""/><cellStyle name=""60% - Accent5"" xfId=""37"" builtinId=""48"" customBuiltin=""1""/><cellStyle name=""60% - Accent6"" xfId=""41"" builtinId=""52"" customBuiltin=""1""/><cellStyle name=""Accent1"" xfId=""18"" builtinId=""29"" customBuiltin=""1""/><cellStyle name=""Accent2"" xfId=""22"" builtinId=""33"" customBuiltin=""1""/><cellStyle name=""Accent3"" xfId=""26"" builtinId=""37"" customBuiltin=""1""/><cellStyle name=""Accent4"" xfId=""30"" builtinId=""41"" customBuiltin=""1""/><cellStyle name=""Accent5"" xfId=""34"" builtinId=""45"" customBuiltin=""1""/><cellStyle name=""Accent6"" xfId=""38"" builtinId=""49"" customBuiltin=""1""/><cellStyle name=""Bad"" xfId=""7"" builtinId=""27"" customBuiltin=""1""/><cellStyle name=""Calculation"" xfId=""11"" builtinId=""22"" customBuiltin=""1""/><cellStyle name=""Check Cell"" xfId=""13"" builtinId=""23"" customBuiltin=""1""/><cellStyle name=""Explanatory Text"" xfId=""16"" builtinId=""53"" customBuiltin=""1""/><cellStyle name=""Good"" xfId=""6"" builtinId=""26"" customBuiltin=""1""/><cellStyle name=""Heading 1"" xfId=""2"" builtinId=""16"" customBuiltin=""1""/><cellStyle name=""Heading 2"" xfId=""3"" builtinId=""17"" customBuiltin=""1""/><cellStyle name=""Heading 3"" xfId=""4"" builtinId=""18"" customBuiltin=""1""/><cellStyle name=""Heading 4"" xfId=""5"" builtinId=""19"" customBuiltin=""1""/><cellStyle name=""Input"" xfId=""9"" builtinId=""20"" customBuiltin=""1""/><cellStyle name=""Linked Cell"" xfId=""12"" builtinId=""24"" customBuiltin=""1""/><cellStyle name=""Neutral"" xfId=""8"" builtinId=""28"" customBuiltin=""1""/><cellStyle name=""Normal"" xfId=""0"" builtinId=""0""/><cellStyle name=""Note"" xfId=""15"" builtinId=""10"" customBuiltin=""1""/><cellStyle name=""Output"" xfId=""10"" builtinId=""21"" customBuiltin=""1""/><cellStyle name=""Title"" xfId=""1"" builtinId=""15"" customBuiltin=""1""/><cellStyle name=""Total"" xfId=""17"" builtinId=""25"" customBuiltin=""1""/><cellStyle name=""Warning Text"" xfId=""14"" builtinId=""11"" customBuiltin=""1""/></cellStyles><dxfs count=""0""/><tableStyles count=""0"" defaultTableStyle=""TableStyleMedium2"" defaultPivotStyle=""PivotStyleLight16""/><extLst><ext uri=""{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"" xmlns:x14=""http://schemas.microsoft.com/office/spreadsheetml/2009/9/main""><x14:slicerStyles defaultSlicerStyle=""SlicerStyleLight1""/></ext></extLst></styleSheet>
");
            }
        }
        void writeRelations(Stream stream)
        {
            using (StreamWriter writer = new StreamWriter(stream))
            {
                writer.Write(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes"" ?> 
 <Relationships xmlns=""http://schemas.openxmlformats.org/package/2006/relationships"">
  <Relationship Id=""rId3"" Type=""http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties"" Target=""docProps/app.xml"" /> 
  <Relationship Id=""rId2"" Type=""http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties"" Target=""docProps/core.xml"" /> 
  <Relationship Id=""rId1"" Type=""http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument"" Target=""xl/workbook.xml"" /> 
  </Relationships>");
            }
        }
        void writeWorkBookRelations(Stream stream)
        {
            using (StreamWriter writer = new StreamWriter(stream))
            {
                writer.Write(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes"" ?> 
 <Relationships xmlns=""http://schemas.openxmlformats.org/package/2006/relationships"">");
                int index = 1;
                foreach (WorkSheet sheet in WorkSheets)
                {
                    writer.Write(@"<Relationship Id=""rId"); writer.Write(index);
                    writer.Write(@""" Type=""http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet"" Target=""worksheets/sheet"); writer.Write(index); writer.Write(@".xml"" /> ");
                    index++;
                }
                writer.Write(@"<Relationship Id=""rId"); writer.Write(index + 1); writer.Write(@""" Type=""http://schemas.openxmlformats.org/officeDocument/2006/relationships/theme"" Target=""theme/theme1.xml"" /> ");
                writer.Write(@"<Relationship Id=""rId"); writer.Write(index + 2); writer.Write(@""" Type=""http://schemas.openxmlformats.org/officeDocument/2006/relationships/styles"" Target=""styles.xml"" />");
                writer.Write(@"<Relationship Id=""rId"); writer.Write(index + 3); writer.Write(@""" Type=""http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings"" Target=""sharedStrings.xml"" />");
                writer.Write("</Relationships>");
            }
        }
        void writeTheme(Stream stream)
        {
            using (StreamWriter writer = new StreamWriter(stream))
            {
                writer.Write(@" <?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<a:theme xmlns:a=""http://schemas.openxmlformats.org/drawingml/2006/main"" name=""Office Theme""><a:themeElements><a:clrScheme name=""Office""><a:dk1><a:sysClr val=""windowText"" lastClr=""000000""/></a:dk1><a:lt1><a:sysClr val=""window"" lastClr=""FFFFFF""/></a:lt1><a:dk2><a:srgbClr val=""1F497D""/></a:dk2><a:lt2><a:srgbClr val=""EEECE1""/></a:lt2><a:accent1><a:srgbClr val=""4F81BD""/></a:accent1><a:accent2><a:srgbClr val=""C0504D""/></a:accent2><a:accent3><a:srgbClr val=""9BBB59""/></a:accent3><a:accent4><a:srgbClr val=""8064A2""/></a:accent4><a:accent5><a:srgbClr val=""4BACC6""/></a:accent5><a:accent6><a:srgbClr val=""F79646""/></a:accent6><a:hlink><a:srgbClr val=""0000FF""/></a:hlink><a:folHlink><a:srgbClr val=""800080""/></a:folHlink></a:clrScheme><a:fontScheme name=""Office""><a:majorFont><a:latin typeface=""Cambria""/><a:ea typeface=""""/><a:cs typeface=""""/><a:font script=""Jpan"" typeface=""ＭＳ Ｐゴシック""/><a:font script=""Hang"" typeface=""맑은 고딕""/><a:font script=""Hans"" typeface=""宋体""/><a:font script=""Hant"" typeface=""新細明體""/><a:font script=""Arab"" typeface=""Times New Roman""/><a:font script=""Hebr"" typeface=""Times New Roman""/><a:font script=""Thai"" typeface=""Tahoma""/><a:font script=""Ethi"" typeface=""Nyala""/><a:font script=""Beng"" typeface=""Vrinda""/><a:font script=""Gujr"" typeface=""Shruti""/><a:font script=""Khmr"" typeface=""MoolBoran""/><a:font script=""Knda"" typeface=""Tunga""/><a:font script=""Guru"" typeface=""Raavi""/><a:font script=""Cans"" typeface=""Euphemia""/><a:font script=""Cher"" typeface=""Plantagenet Cherokee""/><a:font script=""Yiii"" typeface=""Microsoft Yi Baiti""/><a:font script=""Tibt"" typeface=""Microsoft Himalaya""/><a:font script=""Thaa"" typeface=""MV Boli""/><a:font script=""Deva"" typeface=""Mangal""/><a:font script=""Telu"" typeface=""Gautami""/><a:font script=""Taml"" typeface=""Latha""/><a:font script=""Syrc"" typeface=""Estrangelo Edessa""/><a:font script=""Orya"" typeface=""Kalinga""/><a:font script=""Mlym"" typeface=""Kartika""/><a:font script=""Laoo"" typeface=""DokChampa""/><a:font script=""Sinh"" typeface=""Iskoola Pota""/><a:font script=""Mong"" typeface=""Mongolian Baiti""/><a:font script=""Viet"" typeface=""Times New Roman""/><a:font script=""Uigh"" typeface=""Microsoft Uighur""/><a:font script=""Geor"" typeface=""Sylfaen""/></a:majorFont><a:minorFont><a:latin typeface=""Calibri""/><a:ea typeface=""""/><a:cs typeface=""""/><a:font script=""Jpan"" typeface=""ＭＳ Ｐゴシック""/><a:font script=""Hang"" typeface=""맑은 고딕""/><a:font script=""Hans"" typeface=""宋体""/><a:font script=""Hant"" typeface=""新細明體""/><a:font script=""Arab"" typeface=""Arial""/><a:font script=""Hebr"" typeface=""Arial""/><a:font script=""Thai"" typeface=""Tahoma""/><a:font script=""Ethi"" typeface=""Nyala""/><a:font script=""Beng"" typeface=""Vrinda""/><a:font script=""Gujr"" typeface=""Shruti""/><a:font script=""Khmr"" typeface=""DaunPenh""/><a:font script=""Knda"" typeface=""Tunga""/><a:font script=""Guru"" typeface=""Raavi""/><a:font script=""Cans"" typeface=""Euphemia""/><a:font script=""Cher"" typeface=""Plantagenet Cherokee""/><a:font script=""Yiii"" typeface=""Microsoft Yi Baiti""/><a:font script=""Tibt"" typeface=""Microsoft Himalaya""/><a:font script=""Thaa"" typeface=""MV Boli""/><a:font script=""Deva"" typeface=""Mangal""/><a:font script=""Telu"" typeface=""Gautami""/><a:font script=""Taml"" typeface=""Latha""/><a:font script=""Syrc"" typeface=""Estrangelo Edessa""/><a:font script=""Orya"" typeface=""Kalinga""/><a:font script=""Mlym"" typeface=""Kartika""/><a:font script=""Laoo"" typeface=""DokChampa""/><a:font script=""Sinh"" typeface=""Iskoola Pota""/><a:font script=""Mong"" typeface=""Mongolian Baiti""/><a:font script=""Viet"" typeface=""Arial""/><a:font script=""Uigh"" typeface=""Microsoft Uighur""/><a:font script=""Geor"" typeface=""Sylfaen""/></a:minorFont></a:fontScheme><a:fmtScheme name=""Office""><a:fillStyleLst><a:solidFill><a:schemeClr val=""phClr""/></a:solidFill><a:gradFill rotWithShape=""1""><a:gsLst><a:gs pos=""0""><a:schemeClr val=""phClr""><a:tint val=""50000""/><a:satMod val=""300000""/></a:schemeClr></a:gs><a:gs pos=""35000""><a:schemeClr val=""phClr""><a:tint val=""37000""/><a:satMod val=""300000""/></a:schemeClr></a:gs><a:gs pos=""100000""><a:schemeClr val=""phClr""><a:tint val=""15000""/><a:satMod val=""350000""/></a:schemeClr></a:gs></a:gsLst><a:lin ang=""16200000"" scaled=""1""/></a:gradFill><a:gradFill rotWithShape=""1""><a:gsLst><a:gs pos=""0""><a:schemeClr val=""phClr""><a:shade val=""51000""/><a:satMod val=""130000""/></a:schemeClr></a:gs><a:gs pos=""80000""><a:schemeClr val=""phClr""><a:shade val=""93000""/><a:satMod val=""130000""/></a:schemeClr></a:gs><a:gs pos=""100000""><a:schemeClr val=""phClr""><a:shade val=""94000""/><a:satMod val=""135000""/></a:schemeClr></a:gs></a:gsLst><a:lin ang=""16200000"" scaled=""0""/></a:gradFill></a:fillStyleLst><a:lnStyleLst><a:ln w=""9525"" cap=""flat"" cmpd=""sng"" algn=""ctr""><a:solidFill><a:schemeClr val=""phClr""><a:shade val=""95000""/><a:satMod val=""105000""/></a:schemeClr></a:solidFill><a:prstDash val=""solid""/></a:ln><a:ln w=""25400"" cap=""flat"" cmpd=""sng"" algn=""ctr""><a:solidFill><a:schemeClr val=""phClr""/></a:solidFill><a:prstDash val=""solid""/></a:ln><a:ln w=""38100"" cap=""flat"" cmpd=""sng"" algn=""ctr""><a:solidFill><a:schemeClr val=""phClr""/></a:solidFill><a:prstDash val=""solid""/></a:ln></a:lnStyleLst><a:effectStyleLst><a:effectStyle><a:effectLst><a:outerShdw blurRad=""40000"" dist=""20000"" dir=""5400000"" rotWithShape=""0""><a:srgbClr val=""000000""><a:alpha val=""38000""/></a:srgbClr></a:outerShdw></a:effectLst></a:effectStyle><a:effectStyle><a:effectLst><a:outerShdw blurRad=""40000"" dist=""23000"" dir=""5400000"" rotWithShape=""0""><a:srgbClr val=""000000""><a:alpha val=""35000""/></a:srgbClr></a:outerShdw></a:effectLst></a:effectStyle><a:effectStyle><a:effectLst><a:outerShdw blurRad=""40000"" dist=""23000"" dir=""5400000"" rotWithShape=""0""><a:srgbClr val=""000000""><a:alpha val=""35000""/></a:srgbClr></a:outerShdw></a:effectLst><a:scene3d><a:camera prst=""orthographicFront""><a:rot lat=""0"" lon=""0"" rev=""0""/></a:camera><a:lightRig rig=""threePt"" dir=""t""><a:rot lat=""0"" lon=""0"" rev=""1200000""/></a:lightRig></a:scene3d><a:sp3d><a:bevelT w=""63500"" h=""25400""/></a:sp3d></a:effectStyle></a:effectStyleLst><a:bgFillStyleLst><a:solidFill><a:schemeClr val=""phClr""/></a:solidFill><a:gradFill rotWithShape=""1""><a:gsLst><a:gs pos=""0""><a:schemeClr val=""phClr""><a:tint val=""40000""/><a:satMod val=""350000""/></a:schemeClr></a:gs><a:gs pos=""40000""><a:schemeClr val=""phClr""><a:tint val=""45000""/><a:shade val=""99000""/><a:satMod val=""350000""/></a:schemeClr></a:gs><a:gs pos=""100000""><a:schemeClr val=""phClr""><a:shade val=""20000""/><a:satMod val=""255000""/></a:schemeClr></a:gs></a:gsLst><a:path path=""circle""><a:fillToRect l=""50000"" t=""-80000"" r=""50000"" b=""180000""/></a:path></a:gradFill><a:gradFill rotWithShape=""1""><a:gsLst><a:gs pos=""0""><a:schemeClr val=""phClr""><a:tint val=""80000""/><a:satMod val=""300000""/></a:schemeClr></a:gs><a:gs pos=""100000""><a:schemeClr val=""phClr""><a:shade val=""30000""/><a:satMod val=""200000""/></a:schemeClr></a:gs></a:gsLst><a:path path=""circle""><a:fillToRect l=""50000"" t=""50000"" r=""50000"" b=""50000""/></a:path></a:gradFill></a:bgFillStyleLst></a:fmtScheme></a:themeElements><a:objectDefaults/><a:extraClrSchemeLst/></a:theme>");
            }
        }
        void writeWorkBook(Stream stream)
        {
            using (StreamWriter writer = new StreamWriter(stream))
            {
                writer.Write(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes""?>
<workbook xmlns=""http://schemas.openxmlformats.org/spreadsheetml/2006/main"" xmlns:r=""http://schemas.openxmlformats.org/officeDocument/2006/relationships""><fileVersion appName=""xl"" lastEdited=""4"" lowestEdited=""4"" rupBuild=""4505""/><workbookPr defaultThemeVersion=""124226""/><bookViews><workbookView xWindow=""480"" yWindow=""30"" windowWidth=""10320"" windowHeight=""6225""/></bookViews><sheets>
");
                int index = 1;
                foreach (WorkSheet sheet in WorkSheets)
                {
                    writer.Write("<sheet name=\"");
                    writer.Write(sheet.Name);
                    writer.Write("\" sheetId=\"");
                    writer.Write(index);
                    writer.Write("\" r:id=\"rId");
                    writer.Write(index);
                    writer.Write("\" />");
                    index++;
                }

                writer.Write(@"</sheets><calcPr calcId=""124519""/></workbook>");
            }
        }

        void writeContentXml(Stream stream)
        {
            using (StreamWriter writer = new StreamWriter(stream))
            {
                writer.Write(@"<?xml version=""1.0"" encoding=""UTF-8"" standalone=""yes"" ?> 
  <Types xmlns=""http://schemas.openxmlformats.org/package/2006/content-types"">
  <Override PartName=""/xl/theme/theme1.xml"" ContentType=""application/vnd.openxmlformats-officedocument.theme+xml"" /> 
  <Override PartName=""/xl/styles.xml"" ContentType=""application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml"" /> 
  <Default Extension=""rels"" ContentType=""application/vnd.openxmlformats-package.relationships+xml"" /> 
  <Default Extension=""xml"" ContentType=""application/xml"" /> 
  <Override PartName=""/xl/workbook.xml"" ContentType=""application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"" /> 
  <Override PartName=""/docProps/app.xml"" ContentType=""application/vnd.openxmlformats-officedocument.extended-properties+xml"" /> 
");
                for (int index = 0; index < WorkSheets.Count; index++)
                {
                    writer.Write(@"<Override PartName=""/xl/worksheets/sheet");
                    writer.Write(index + 1);
                    writer.Write(@".xml"" ContentType=""application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"" /> ");
                }

                writer.Write(@"<Override PartName=""/xl/sharedStrings.xml"" ContentType=""application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml"" /> 
  <Override PartName=""/docProps/core.xml"" ContentType=""application/vnd.openxmlformats-package.core-properties+xml"" /> 
  </Types>");

            }
        }

        private void writeWorkSheet(XmlTextWriter writer, WorkSheet workSheet)
        {
                writer.WriteStartDocument(true);

                writer.WriteStartElement("worksheet");
                writer.WriteAttributeString("xmlns", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                writer.WriteAttributeString("xmlns:r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

                writer.WriteStartElement("dimension");

                writer.WriteAttributeString("ref", "A1:" + workSheet.Cells.GetLastCell().Name);
                writer.WriteEndElement();

                writer.WriteStartElement("sheetViews");
                writer.WriteStartElement("sheetView");
                writer.WriteAttributeString("tabSelected", "1");
                writer.WriteAttributeString("workbookViewId", "0");
                writer.WriteEndElement();
                writer.WriteEndElement();

                writer.WriteStartElement("sheetFormatPr");
                writer.WriteAttributeString("defaultRowHeight", "15");
                writer.WriteEndElement();

                writer.WriteStartElement("sheetData");

                writeWorksheetData(writer, workSheet, sharedStringValues);
                //ExcelRW.WriteWorksheetData(writer, data, lookupTable);
                writer.WriteEndElement();

                writer.WriteStartElement("pageMargins");
                writer.WriteAttributeString("left", "0.7");
                writer.WriteAttributeString("right", "0.7");
                writer.WriteAttributeString("top", "0.75");
                writer.WriteAttributeString("bottom", "0.75");
                writer.WriteAttributeString("header", "0.3");
                writer.WriteAttributeString("footer", "0.3");
                writer.WriteEndElement();

                writer.WriteEndElement();
            
        }
        private void writeWorkSheet(WorkSheet workSheet, Stream workSheetStream)
        {
            using (XmlTextWriter writer = new XmlTextWriter(workSheetStream, Encoding.UTF8))
            {
                //writer.Flush();
                
                writer.WriteStartDocument(true);

                writer.WriteStartElement("worksheet");
                writer.WriteAttributeString("xmlns", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                writer.WriteAttributeString("xmlns:r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");

                writer.WriteStartElement("dimension");

                writer.WriteAttributeString("ref", "A1:" + workSheet.Cells.GetLastCell().Name);
                writer.WriteEndElement();

                writer.WriteStartElement("sheetViews");
                writer.WriteStartElement("sheetView");
                writer.WriteAttributeString("tabSelected", "1");
                writer.WriteAttributeString("workbookViewId", "0");
                writer.WriteEndElement();
                writer.WriteEndElement();

                writer.WriteStartElement("sheetFormatPr");
                writer.WriteAttributeString("defaultRowHeight", "15");
                writer.WriteEndElement();

                writer.WriteStartElement("sheetData");

                writeWorksheetData(writer, workSheet, sharedStringValues);
                //ExcelRW.WriteWorksheetData(writer, data, lookupTable);
                writer.WriteEndElement();

                writer.WriteStartElement("pageMargins");
                writer.WriteAttributeString("left", "0.7");
                writer.WriteAttributeString("right", "0.7");
                writer.WriteAttributeString("top", "0.75");
                writer.WriteAttributeString("bottom", "0.75");
                writer.WriteAttributeString("header", "0.3");
                writer.WriteAttributeString("footer", "0.3");
                writer.WriteEndElement();

                writer.WriteEndElement();
            }
        }
        void writeWorksheetData(XmlTextWriter writer, WorkSheet workSheet, List<string> sharedStringValues)
        {
            int rowIndex = -1;
            bool isNotFirst = false;
            int totalCellCount = 0;
            foreach (Cell cell in workSheet.Cells)
            {
                if (rowIndex != cell.RowIndex)
                {

                    if (isNotFirst)
                        writer.WriteEndElement();

                    writer.WriteStartElement("row");
                    writer.WriteAttributeString("r", (cell.RowIndex + 1).ToString());
                    writer.WriteAttributeString("spans", "1:" + workSheet.Cells.GetCellCount(cell.RowIndex));

                    rowIndex = cell.RowIndex;
                }

                //writing column Info
                writer.WriteStartElement("c");
                writer.WriteAttributeString("r", cell.Name);

                string val = "";
                if (cell.Value is string)
                {
                    writer.WriteAttributeString("t", "s");
                    val = sharedStringValues.IndexOf(cell.Value.ToString()).ToString();
                }

                writer.WriteElementString("v", val.ToString());
                writer.WriteEndElement();
                isNotFirst = true;
                totalCellCount++;
            }
            if (isNotFirst)
                writer.WriteEndElement();
        }

        private void writeSharedString(Stream sharedStringStream)
        {
            using (XmlTextWriter sharedStringWriter = new XmlTextWriter(sharedStringStream, Encoding.UTF8))
            {
                sharedStringWriter.WriteStartDocument(true);

                sharedStringWriter.WriteStartElement("sst");
                sharedStringWriter.WriteAttributeString("xmlns", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                sharedStringWriter.WriteAttributeString("count", sharedStringValues.Count.ToString());
                sharedStringWriter.WriteAttributeString("uniqueCount", sharedStringValues.Count.ToString());

                foreach (string str in sharedStringValues)
                {
                    sharedStringWriter.WriteStartElement("si");
                    sharedStringWriter.WriteElementString("t", str);
                    sharedStringWriter.WriteEndElement();
                }

                sharedStringWriter.WriteEndElement();
            }
        }

        #endregion

        #endregion
    }

    
    public class WorkSheetCollection : IEnumerable
    {
        private Dictionary<string, WorkSheet> _dicWorkSheets;
        Dictionary<string, WorkSheet> WorkSheetsDictionary
        {
            get
            {
                if (_dicWorkSheets == null)
                    _dicWorkSheets = new Dictionary<string, WorkSheet>();
                return _dicWorkSheets;
            }
        }
        public int Count
        {
            get
            {
                return _dicWorkSheets.Count;
            }
        }
        public WorkSheet this[string sheetName]
        {
            get
            {
                return _dicWorkSheets[sheetName];
            }
        }
        public WorkSheet Get(string SheetName)
        {
            return WorkSheetsDictionary[SheetName];
        }
        public void Add(string SheetName)
        {
            WorkSheetsDictionary.Add(SheetName, new WorkSheet(SheetName));
        }
        public void Remove(string SheetName)
        {
            WorkSheetsDictionary.Remove(SheetName);
        }

        #region IEnumerable Members

        public IEnumerator GetEnumerator()
        {
            return WorkSheetsDictionary.Values.GetEnumerator();
        }

        #endregion

        
    }

    [Serializable]
    public class WorkSheet
    {
        
        private WorkSheet() { }
        private string _name;
        public WorkSheet(string Name)
        {
            _name = Name;
        }

        CellCollection _listCell;
        public string Name
        {
            get
            {
                return _name;
            }
        }
        public CellCollection Cells
        {
            get
            {
                if (_listCell == null)
                    _listCell = new CellCollection();
                return _listCell;
            }
        }
        public WorkSheet Clone()
        {
            using (var ms = new MemoryStream())
            {
                var formatter = new BinaryFormatter();
                formatter.Serialize(ms, this);
                ms.Position = 0;
                return (WorkSheet) formatter.Deserialize(ms);
            }
        }
    }

    public class CellCollection : IEnumerable
    {
        private SortedList<int, SortedList<int, Cell>> _dicRows;
        //private SortedList<int, Cell> _dicCells;



        SortedList<int, SortedList<int, Cell>> RowsDictionary
        {
            get
            {
                if (_dicRows == null)
                    _dicRows = new SortedList<int, SortedList<int, Cell>>();
                return _dicRows;
            }
        }

        public int RowCount
        {
            get
            {
                return RowsDictionary.Count;
            }
        }

        public Cell this[int row, int col]
        {
            get
            {
                string columnName = Cell.getColumnName(col);
                if (!RowsDictionary.ContainsKey(row))
                    RowsDictionary.Add(row, new SortedList<int, Cell>());

                if (!RowsDictionary[row].ContainsKey(col))
                    RowsDictionary[row].Add(col, new Cell(row, col));

                return RowsDictionary[row][col];
            }
        }

        public void Remove(int row, int col)
        {
            RowsDictionary[row].Remove(col);
            //CellsDictionary.Remove(row + ":" + Cell.getColumnName(col));
        }

        public Cell GetLastCell()
        {
            //string LastKey = "";

            SortedList<int, Cell> cellsList = RowsDictionary[RowsDictionary.Keys.Max()];
            return cellsList[cellsList.Keys.Max()];

            //foreach (string key in CellsDictionary.Keys)
            //{
            //    LastKey = key;
            //}
            //return CellsDictionary[LastKey];
        }
        public int GetCellCount(int rowIndex)
        {
            return RowsDictionary[rowIndex].Count();
        }

        #region IEnumerable Members

        public IEnumerator GetEnumerator()
        {
            return (IEnumerator)new CellCollectionEnumerator(this);
        }

        #endregion

        #region IEnumerator Members
        private class CellCollectionEnumerator : IEnumerator
        {
            int enumeratorRowIndex;
            int enumeratorColIndex;
            private CellCollection _cellCollection;
            public CellCollectionEnumerator(CellCollection cellCollection)
            {
                enumeratorColIndex = -1;
                enumeratorRowIndex = -1;
                _cellCollection = cellCollection;
            }

            #region IEnumerator Members
            public object Current
            {
                get
                {
                    return _cellCollection.RowsDictionary.ElementAt(enumeratorRowIndex).Value.ElementAt(enumeratorColIndex).Value;
                }
            }

            public bool MoveNext()
            {
                if (enumeratorRowIndex == -1)
                {
                    enumeratorRowIndex++;
                    if (enumeratorRowIndex >= _cellCollection.RowsDictionary.Count)
                        return false;
                }

                enumeratorColIndex++;
                if (enumeratorColIndex >= _cellCollection.RowsDictionary.ElementAt(enumeratorRowIndex).Value.Count)
                {
                    enumeratorRowIndex++;
                    enumeratorColIndex = 0;
                    if (enumeratorRowIndex >= _cellCollection.RowsDictionary.Count)
                        return false;
                    else
                        return true;
                }

                return true;

                //Cell cell = .ElementAt(enumeratorColIndex).Value;
                //if(cell == null)
                //{
                //    enumeratorRowIndex++;
                //    if(RowsDictionary.ElementAt(enumeratorRowIndex) != null)
                //        cell = RowsDictionary.ElementAt(enumeratorRowIndex).Value.ElementAt(enumeratorColIndex).Value;
                //}

                //cell = RowsDictionary.ElementAt(enumeratorRowIndex).Value.ElementAt(enumeratorColIndex).Value;
                //RowsDictionar
                //throw new NotImplementedException();
            }

            public void Reset()
            {
                enumeratorRowIndex = -1;
                enumeratorColIndex = -1;
            }

            #endregion
        }


        #endregion
    }
    public class Cell
    {
        private int _row, _col; object _value;
        private Cell() { }
        public Cell(int row, int col, object value)
        {
            _row = row;
            _col = col;
            _value = value;
        }
        public Cell(int row, int col)
        {
            _row = row;
            _col = col;
            _value = "";
        }
        public int RowIndex
        {
            get { return _row; }
        }
        public int ColumnIndex
        {
            get { return _col; }
        }
        public object Value
        {
            get { return _value; }
            set
            {
                _value = value;
            }
        }
        public string Name
        {
            get
            {
                return getCellName(RowIndex, ColumnIndex);
            }
        }
        private string getCellName(int rowIndex, int columnIndex)
        {
            return getColumnName(columnIndex) + (rowIndex + 1);
        }
        internal static string getColumnName(int columnIndex)
        {
            string colName = ((char)(((int)'A') + columnIndex % 26)).ToString();

            columnIndex = columnIndex / 26;

            if (columnIndex != 0)
                colName = ((char)(((int)'A') - 1 + columnIndex)).ToString() + colName;

            return colName.ToString();
        }


    }
}