source: branches/WebJobManager/HeuristicLab.ExtLibs/HeuristicLab.EPPlus/4.0.3/EPPlus-4.0.3/ExcelPackage.cs @ 13656

/*******************************************************************************
 * You may amend and distribute as you like, but don't remove this header!
 *
 * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
 * See http://www.codeplex.com/EPPlus for details.
 *
 * Copyright (C) 2011  Jan Källman
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.

 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
 * See the GNU Lesser General Public License for more details.
 *
 * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
 *
 * All code and executables are provided "as is" with no warranty either express or implied. 
 * The author accepts no liability for any damage or loss of business that this product may cause.
 *
 * Code change notes:
 * 
 * Author             Change            Date
 * ******************************************************************************
 * Jan Källman                    Initial Release           2009-10-01
 * Starnuto Di Topo & Jan Källman   Added stream constructors 
 *                                  and Load method Save as 
 *                                  stream                      2010-03-14
 * Jan Källman    License changed GPL-->LGPL 2011-12-27
 *******************************************************************************/
using System;
using System.Xml;
using System.IO;
using System.Collections.Generic;
using System.Security.Cryptography;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Utils;
using OfficeOpenXml.Packaging.Ionic.Zlib;
using OfficeOpenXml.FormulaParsing;
using OfficeOpenXml.Encryption;
namespace OfficeOpenXml
{
    /// <summary>
    /// Maps to DotNetZips CompressionLevel enum
    /// </summary>
    public enum CompressionLevel
    {
        Level0 = 0,
        None = 0,
        Level1 = 1,
        BestSpeed = 1,
        Level2 = 2,
        Level3 = 3,
        Level4 = 4,
        Level5 = 5,
        Level6 = 6,
        Default = 6,
        Level7 = 7,
        Level8 = 8,
        BestCompression = 9,
        Level9 = 9,
    }
    /// <summary>
    /// Represents an Excel 2007/2010 XLSX file package.  
    /// This is the top-level object to access all parts of the document.
    /// <code>
  ///     FileInfo newFile = new FileInfo(outputDir.FullName + @"\sample1.xlsx");
  ///   if (newFile.Exists)
  ///   {
  ///     newFile.Delete();  // ensures we create a new workbook
  ///     newFile = new FileInfo(outputDir.FullName + @"\sample1.xlsx");
  ///   }
  ///   using (ExcelPackage package = new ExcelPackage(newFile))
    ///     {
    ///         // add a new worksheet to the empty workbook
    ///         ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Inventory");
    ///         //Add the headers
    ///         worksheet.Cells[1, 1].Value = "ID";
    ///         worksheet.Cells[1, 2].Value = "Product";
    ///         worksheet.Cells[1, 3].Value = "Quantity";
    ///         worksheet.Cells[1, 4].Value = "Price";
    ///         worksheet.Cells[1, 5].Value = "Value";
    ///
    ///         //Add some items...
    ///         worksheet.Cells["A2"].Value = "12001";
    ///         worksheet.Cells["B2"].Value = "Nails";
    ///         worksheet.Cells["C2"].Value = 37;
    ///         worksheet.Cells["D2"].Value = 3.99;
    ///
    ///         worksheet.Cells["A3"].Value = "12002";
    ///         worksheet.Cells["B3"].Value = "Hammer";
    ///         worksheet.Cells["C3"].Value = 5;
    ///         worksheet.Cells["D3"].Value = 12.10;
    ///
    ///         worksheet.Cells["A4"].Value = "12003";
    ///         worksheet.Cells["B4"].Value = "Saw";
    ///         worksheet.Cells["C4"].Value = 12;
    ///         worksheet.Cells["D4"].Value = 15.37;
    ///
    ///         //Add a formula for the value-column
    ///         worksheet.Cells["E2:E4"].Formula = "C2*D2";
    ///
    ///            //Ok now format the values;
    ///         using (var range = worksheet.Cells[1, 1, 1, 5]) 
    ///          {
    ///             range.Style.Font.Bold = true;
    ///             range.Style.Fill.PatternType = ExcelFillStyle.Solid;
    ///             range.Style.Fill.BackgroundColor.SetColor(Color.DarkBlue);
    ///             range.Style.Font.Color.SetColor(Color.White);
    ///         }
    ///
    ///         worksheet.Cells["A5:E5"].Style.Border.Top.Style = ExcelBorderStyle.Thin;
    ///         worksheet.Cells["A5:E5"].Style.Font.Bold = true;
    ///
    ///         worksheet.Cells[5, 3, 5, 5].Formula = string.Format("SUBTOTAL(9,{0})", new ExcelAddress(2,3,4,3).Address);
    ///         worksheet.Cells["C2:C5"].Style.Numberformat.Format = "#,##0";
    ///         worksheet.Cells["D2:E5"].Style.Numberformat.Format = "#,##0.00";
    ///
    ///         //Create an autofilter for the range
    ///         worksheet.Cells["A1:E4"].AutoFilter = true;
    ///
    ///         worksheet.Cells["A1:E5"].AutoFitColumns(0);
    ///
    ///         // lets set the header text 
    ///         worksheet.HeaderFooter.oddHeader.CenteredText = "&amp;24&amp;U&amp;\"Arial,Regular Bold\" Inventory";
    ///         // add the page number to the footer plus the total number of pages
    ///         worksheet.HeaderFooter.oddFooter.RightAlignedText =
    ///         string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
    ///         // add the sheet name to the footer
    ///         worksheet.HeaderFooter.oddFooter.CenteredText = ExcelHeaderFooter.SheetName;
    ///         // add the file path to the footer
    ///         worksheet.HeaderFooter.oddFooter.LeftAlignedText = ExcelHeaderFooter.FilePath + ExcelHeaderFooter.FileName;
    ///
    ///         worksheet.PrinterSettings.RepeatRows = worksheet.Cells["1:2"];
    ///         worksheet.PrinterSettings.RepeatColumns = worksheet.Cells["A:G"];
    ///
    ///          // Change the sheet view to show it in page layout mode
    ///           worksheet.View.PageLayoutView = true;
    ///
    ///         // set some document properties
    ///         package.Workbook.Properties.Title = "Invertory";
    ///         package.Workbook.Properties.Author = "Jan Källman";
    ///         package.Workbook.Properties.Comments = "This sample demonstrates how to create an Excel 2007 workbook using EPPlus";
    ///
    ///         // set some extended property values
    ///         package.Workbook.Properties.Company = "AdventureWorks Inc.";
    ///
    ///         // set some custom property values
    ///         package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Jan Källman");
    ///         package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");
    ///
    ///         // save our new workbook and we are done!
    ///         package.Save();
    ///
    ///       }
    ///
    ///       return newFile.FullName;
    /// </code>
    /// More samples can be found at  <a href="http://epplus.codeplex.com/">http://epplus.codeplex.com/</a>
    /// </summary>
  public sealed class ExcelPackage : IDisposable
  {
        internal const bool preserveWhitespace=false;
        Stream _stream = null;
        private bool _isExternalStream=false;
        internal class ImageInfo
        {
            internal string Hash { get; set; }
            internal Uri Uri{get;set;}
            internal int RefCount { get; set; }
            internal Packaging.ZipPackagePart Part { get; set; }
        }
        internal Dictionary<string, ImageInfo> _images = new Dictionary<string, ImageInfo>();
    #region Properties
    /// <summary>
    /// Extention Schema types
    /// </summary>
        internal const string schemaXmlExtension = "application/xml";
        internal const string schemaRelsExtension = "application/vnd.openxmlformats-package.relationships+xml";
        /// <summary>
    /// Main Xml schema name
    /// </summary>
    internal const string schemaMain = @"http://schemas.openxmlformats.org/spreadsheetml/2006/main";
    /// <summary>
    /// Relationship schema name
    /// </summary>
    internal const string schemaRelationships = @"http://schemas.openxmlformats.org/officeDocument/2006/relationships";
                                                                              
        internal const string schemaDrawings = @"http://schemas.openxmlformats.org/drawingml/2006/main";
        internal const string schemaSheetDrawings = @"http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing";
        internal const string schemaMarkupCompatibility = @"http://schemas.openxmlformats.org/markup-compatibility/2006";

        internal const string schemaMicrosoftVml = @"urn:schemas-microsoft-com:vml";
        internal const string schemaMicrosoftOffice = "urn:schemas-microsoft-com:office:office";
        internal const string schemaMicrosoftExcel = "urn:schemas-microsoft-com:office:excel";

        internal const string schemaChart = @"http://schemas.openxmlformats.org/drawingml/2006/chart";                                                        
        internal const string schemaHyperlink = @"http://schemas.openxmlformats.org/officeDocument/2006/relationships/hyperlink";
        internal const string schemaComment = @"http://schemas.openxmlformats.org/officeDocument/2006/relationships/comments";
        internal const string schemaImage = @"http://schemas.openxmlformats.org/officeDocument/2006/relationships/image";
        //Office properties
        internal const string schemaCore = @"http://schemas.openxmlformats.org/package/2006/metadata/core-properties";
        internal const string schemaExtended = @"http://schemas.openxmlformats.org/officeDocument/2006/extended-properties";
        internal const string schemaCustom = @"http://schemas.openxmlformats.org/officeDocument/2006/custom-properties";
        internal const string schemaDc = @"http://purl.org/dc/elements/1.1/";
        internal const string schemaDcTerms = @"http://purl.org/dc/terms/";
        internal const string schemaDcmiType = @"http://purl.org/dc/dcmitype/";
        internal const string schemaXsi = @"http://www.w3.org/2001/XMLSchema-instance";
        internal const string schemaVt = @"http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes";

        //Pivottables
        internal const string schemaPivotTable = @"application/vnd.openxmlformats-officedocument.spreadsheetml.pivotTable+xml";
        internal const string schemaPivotCacheDefinition = @"application/vnd.openxmlformats-officedocument.spreadsheetml.pivotCacheDefinition+xml";
        internal const string schemaPivotCacheRecords = @"application/vnd.openxmlformats-officedocument.spreadsheetml.pivotCacheRecords+xml";

        //VBA
        internal const string schemaVBA = @"application/vnd.ms-office.vbaProject";
        internal const string schemaVBASignature = @"application/vnd.ms-office.vbaProjectSignature";

        internal const string contentTypeWorkbookDefault = @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";
        internal const string contentTypeWorkbookMacroEnabled = "application/vnd.ms-excel.sheet.macroEnabled.main+xml";
        internal const string contentTypeSharedString = @"application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml";
        //Package reference
        private Packaging.ZipPackage _package;
    internal ExcelWorkbook _workbook;
        /// <summary>
        /// Maximum number of columns in a worksheet (16384). 
        /// </summary>
        public const int MaxColumns = 16384;
        /// <summary>
        /// Maximum number of rows in a worksheet (1048576). 
        /// </summary>
        public const int MaxRows = 1048576;
    #endregion

    #region ExcelPackage Constructors
        /// <summary>
        /// Create a new instance of the ExcelPackage. Output is accessed through the Stream property.
        /// </summary>
        public ExcelPackage()
        {
            Init();
            ConstructNewFile(null);
        }
        /// <summary>
    /// Create a new instance of the ExcelPackage class based on a existing file or creates a new file. 
    /// </summary>
    /// <param name="newFile">If newFile exists, it is opened.  Otherwise it is created from scratch.</param>
        public ExcelPackage(FileInfo newFile)
    {
            Init();
            File = newFile;
            ConstructNewFile(null);
        }
        /// <summary>
        /// Create a new instance of the ExcelPackage class based on a existing file or creates a new file. 
        /// </summary>
        /// <param name="newFile">If newFile exists, it is opened.  Otherwise it is created from scratch.</param>
        /// <param name="password">Password for an encrypted package</param>
        public ExcelPackage(FileInfo newFile, string password)
        {
            Init();
            File = newFile;
            ConstructNewFile(password);
        }
    /// <summary>
    /// Create a new instance of the ExcelPackage class based on a existing template.
    /// If newFile exists, it will be overwritten when the Save method is called
    /// </summary>
    /// <param name="newFile">The name of the Excel file to be created</param>
    /// <param name="template">The name of the Excel template to use as the basis of the new Excel file</param>
    public ExcelPackage(FileInfo newFile, FileInfo template)
    {
            Init();
            File = newFile;
            CreateFromTemplate(template, null);
    }
        /// <summary>
        /// Create a new instance of the ExcelPackage class based on a existing template.
        /// If newFile exists, it will be overwritten when the Save method is called
        /// </summary>
        /// <param name="newFile">The name of the Excel file to be created</param>
        /// <param name="template">The name of the Excel template to use as the basis of the new Excel file</param>
        /// <param name="password">Password to decrypted the template</param>
        public ExcelPackage(FileInfo newFile, FileInfo template, string password)
        {
            Init();
            File = newFile;
            CreateFromTemplate(template, password);
        }
        /// <summary>
        /// Create a new instance of the ExcelPackage class based on a existing template.
        /// </summary>
        /// <param name="template">The name of the Excel template to use as the basis of the new Excel file</param>
        /// <param name="useStream">if true use a stream. If false create a file in the temp dir with a random name</param>
        public ExcelPackage(FileInfo template, bool useStream)
        {
            Init();
            CreateFromTemplate(template, null);
            if (useStream == false)
            {
                File = new FileInfo(Path.GetTempPath() + Guid.NewGuid().ToString() + ".xlsx");
            }
        }
        /// <summary>
        /// Create a new instance of the ExcelPackage class based on a existing template.
        /// </summary>
        /// <param name="template">The name of the Excel template to use as the basis of the new Excel file</param>
        /// <param name="useStream">if true use a stream. If false create a file in the temp dir with a random name</param>
        /// <param name="password">Password to decrypted the template</param>
        public ExcelPackage(FileInfo template, bool useStream, string password)
        {
            Init();
            CreateFromTemplate(template, password);
            if (useStream == false)
            {
                File = new FileInfo(Path.GetTempPath() + Guid.NewGuid().ToString() + ".xlsx");
            }
        }
        /// <summary>
        /// Create a new instance of the ExcelPackage class based on a stream
        /// </summary>
        /// <param name="newStream">The stream object can be empty or contain a package. The stream must be Read/Write</param>
        public ExcelPackage(Stream newStream) 
        {
            Init();
            if (newStream.Length == 0)
            {
                _stream = newStream;
                _isExternalStream = true;
                ConstructNewFile(null);
            }
            else
            {                
                Load(newStream);
            }
        }
        /// <summary>
        /// Create a new instance of the ExcelPackage class based on a stream
        /// </summary>
        /// <param name="newStream">The stream object can be empty or contain a package. The stream must be Read/Write</param>
        /// <param name="Password">The password to decrypt the document</param>
        public ExcelPackage(Stream newStream, string Password)
        {
            if (!(newStream.CanRead && newStream.CanWrite))
            {
                throw new Exception("The stream must be read/write");
            }

            Init();
            if (newStream.Length > 0)
            {
                Load(newStream,Password);
            }
            else
            {
                _stream = newStream;
                _isExternalStream = true;
                //_package = Package.Open(_stream, FileMode.Create, FileAccess.ReadWrite); TODO:Remove
                _package = new Packaging.ZipPackage(_stream);
                CreateBlankWb();
            }
        }
        /// <summary>
        /// Create a new instance of the ExcelPackage class based on a stream
        /// </summary>
        /// <param name="newStream">The output stream. Must be an empty read/write stream.</param>
        /// <param name="templateStream">This stream is copied to the output stream at load</param>
        public ExcelPackage(Stream newStream, Stream templateStream)
        {
            if (newStream.Length > 0)
            {
                throw(new Exception("The output stream must be empty. Length > 0"));
            }
            else if (!(newStream.CanRead && newStream.CanWrite))
            {
                throw new Exception("The stream must be read/write");
            }
            Init();
            Load(templateStream, newStream, null);
        }
        /// <summary>
        /// Create a new instance of the ExcelPackage class based on a stream
        /// </summary>
        /// <param name="newStream">The output stream. Must be an empty read/write stream.</param>
        /// <param name="templateStream">This stream is copied to the output stream at load</param>
        /// <param name="Password">Password to decrypted the template</param>
        public ExcelPackage(Stream newStream, Stream templateStream, string Password)
        {
            if (newStream.Length > 0)
            {
                throw (new Exception("The output stream must be empty. Length > 0"));
            }
            else if (!(newStream.CanRead && newStream.CanWrite))
            {
                throw new Exception("The stream must be read/write");
            }
            Init();
            Load(templateStream, newStream, Password);
        }
        #endregion
        internal ImageInfo AddImage(byte[] image)
        {
            return AddImage(image, null, "");
        }
        internal ImageInfo AddImage(byte[] image, Uri uri, string contentType)
        {
            var hashProvider = new SHA1CryptoServiceProvider();
            var hash = BitConverter.ToString(hashProvider.ComputeHash(image)).Replace("-","");
            lock (_images)
            {
                if (_images.ContainsKey(hash))
                {
                    _images[hash].RefCount++;
                }
                else
                {
                    Packaging.ZipPackagePart imagePart;
                    if (uri == null)
                    {
                        uri = GetNewUri(Package, "/xl/media/image{0}.jpg");
                        imagePart = Package.CreatePart(uri, "image/jpeg", CompressionLevel.None);
                    }
                    else
                    {
                        imagePart = Package.CreatePart(uri, contentType, CompressionLevel.None);
                    }
                    var stream = imagePart.GetStream(FileMode.Create, FileAccess.Write);
                    stream.Write(image, 0, image.GetLength(0));

                    _images.Add(hash, new ImageInfo() { Uri = uri, RefCount = 1, Hash = hash, Part = imagePart });
                }
            }
            return _images[hash];
        }
        internal ImageInfo LoadImage(byte[] image, Uri uri, Packaging.ZipPackagePart imagePart)
        {
            var hashProvider = new SHA1CryptoServiceProvider();
            var hash = BitConverter.ToString(hashProvider.ComputeHash(image)).Replace("-", "");
            if (_images.ContainsKey(hash))
            {
                _images[hash].RefCount++;
            }
            else
            {
                _images.Add(hash, new ImageInfo() { Uri = uri, RefCount = 1, Hash = hash, Part = imagePart });
            }
            return _images[hash];
        }
        internal void RemoveImage(string hash)
        {
            lock (_images)
            {
                if (_images.ContainsKey(hash))
                {
                    var ii = _images[hash];
                    ii.RefCount--;
                    if (ii.RefCount == 0)
                    {
                        Package.DeletePart(ii.Uri);
                        _images.Remove(hash);
                    }
                }
            }
        }
        internal ImageInfo GetImageInfo(byte[] image)
        {
            var hashProvider = new SHA1CryptoServiceProvider();
            var hash = BitConverter.ToString(hashProvider.ComputeHash(image)).Replace("-","");

            if (_images.ContainsKey(hash))
            {
                return _images[hash];
            }
            else
            {
                return null;
            }
        }
        private Uri GetNewUri(Packaging.ZipPackage package, string sUri)
        {
            int id = 1;
            Uri uri;
            do
            {
                uri = new Uri(string.Format(sUri, id++), UriKind.Relative);
            }
            while (package.PartExists(uri));
            return uri;
        }
        /// <summary>
        /// Init values here
        /// </summary>
        private void Init()
        {
            DoAdjustDrawings = true;
        }
        /// <summary>
        /// Create a new file from a template
        /// </summary>
        /// <param name="template">An existing xlsx file to use as a template</param>
        /// <param name="password">The password to decrypt the package.</param>
        /// <returns></returns>
        private void CreateFromTemplate(FileInfo template, string password)
        {
            if (template != null) template.Refresh();
            if (template.Exists)
            {
                if(_stream==null) _stream=new MemoryStream();
                var ms = new MemoryStream();
                if (password != null)
                {
#if !MONO
                    Encryption.IsEncrypted = true;
                    Encryption.Password = password;
                    var encrHandler = new EncryptedPackageHandler();
                    ms = encrHandler.DecryptPackage(template, Encryption);
                    encrHandler = null;
#endif
#if MONO
                  throw (new NotImplementedException("No support for Encrypted packages in Mono"));
#endif
                  //throw (new NotImplementedException("No support for Encrypted packages in this version"));
                }
                else
                {
                    byte[] b = System.IO.File.ReadAllBytes(template.FullName);
                    ms.Write(b, 0, b.Length);
                }
                try
                {
                    //_package = Package.Open(_stream, FileMode.Open, FileAccess.ReadWrite);
                    _package = new Packaging.ZipPackage(ms);
                }
                catch (Exception ex)
                {
#if !MONO
                    if (password == null && CompoundDocument.IsStorageFile(template.FullName)==0)
                    {
                        throw new Exception("Can not open the package. Package is an OLE compound document. If this is an encrypted package, please supply the password", ex);
                    }
                    else
                    {
                        throw;
                    }
#endif
#if MONO
                    throw;
#endif
                }
            }
            else
                throw new Exception("Passed invalid TemplatePath to Excel Template");
            //return newFile;
        }
        private void ConstructNewFile(string password)
        {
            var ms = new MemoryStream();
            if (_stream == null) _stream = new MemoryStream();
            if (File != null) File.Refresh();
            if (File != null && File.Exists)
            {
                if (password != null)
                {
#if !MONO
                    var encrHandler = new EncryptedPackageHandler();
                    Encryption.IsEncrypted = true;
                    Encryption.Password = password;
                    ms = encrHandler.DecryptPackage(File, Encryption);
                    encrHandler = null;
#endif
#if MONO
                    throw new NotImplementedException("No support for Encrypted packages in Mono");
#endif
                }
                else
                {
                    byte[] b = System.IO.File.ReadAllBytes(File.FullName);
                    ms.Write(b, 0, b.Length);
                }
                try
                {
                    //_package = Package.Open(_stream, FileMode.Open, FileAccess.ReadWrite);
                    _package = new Packaging.ZipPackage(ms);
                }
                catch (Exception ex)
               {
#if !MONO
                    if (password == null && CompoundDocument.IsStorageFile(File.FullName)==0)
                    {
                        throw new Exception("Can not open the package. Package is an OLE compound document. If this is an encrypted package, please supply the password", ex);
                    }
                    else
                    {
                        throw;
                    }
#endif
#if MONO
                    throw;
#endif
                }
            }
            else
            {
                //_package = Package.Open(_stream, FileMode.Create, FileAccess.ReadWrite);
                _package = new Packaging.ZipPackage(ms);
                CreateBlankWb();
            }
        }

        private void CreateBlankWb()
        {
            XmlDocument workbook = Workbook.WorkbookXml; // this will create the workbook xml in the package
            // create the relationship to the main part
            _package.CreateRelationship(UriHelper.GetRelativeUri(new Uri("/xl", UriKind.Relative), Workbook.WorkbookUri), Packaging.TargetMode.Internal, schemaRelationships + "/officeDocument");
        }

    /// <summary>
    /// Returns a reference to the package
    /// </summary>
    public Packaging.ZipPackage Package { get { return (_package); } }
        ExcelEncryption _encryption=null;
        /// <summary>
        /// Information how and if the package is encrypted
        /// </summary>
        public ExcelEncryption Encryption
        {
            get
            {
                if (_encryption == null)
                {
                    _encryption = new ExcelEncryption();
                }
                return _encryption;
            }
        }
    /// <summary>
    /// Returns a reference to the workbook component within the package.
    /// All worksheets and cells can be accessed through the workbook.
    /// </summary>
    public ExcelWorkbook Workbook
    {
      get
      {
                if (_workbook == null)
                {
                    var nsm = CreateDefaultNSM();

                    _workbook = new ExcelWorkbook(this, nsm);

                    _workbook.GetExternalReferences();
                    _workbook.GetDefinedNames();

                }
                return (_workbook);
      }
    }
        /// <summary>
        /// Automaticlly adjust drawing size when column width/row height are adjusted, depending on the drawings editBy property.
        /// Default True
        /// </summary>
        public bool DoAdjustDrawings
        {
            get;
            set;
        }
        private XmlNamespaceManager CreateDefaultNSM()
        {
            //  Create a NamespaceManager to handle the default namespace, 
            //  and create a prefix for the default namespace:
            NameTable nt = new NameTable();
            var ns = new XmlNamespaceManager(nt);
            ns.AddNamespace(string.Empty, ExcelPackage.schemaMain);
            ns.AddNamespace("d", ExcelPackage.schemaMain);
            ns.AddNamespace("r", ExcelPackage.schemaRelationships);
            ns.AddNamespace("c", ExcelPackage.schemaChart);
            ns.AddNamespace("vt", schemaVt);
            // extended properties (app.xml)
            ns.AddNamespace("xp", schemaExtended);
            // custom properties
            ns.AddNamespace("ctp", schemaCustom);
            // core properties
            ns.AddNamespace("cp", schemaCore);
            // core property namespaces 
            ns.AddNamespace("dc", schemaDc);
            ns.AddNamespace("dcterms", schemaDcTerms);
            ns.AddNamespace("dcmitype", schemaDcmiType);
            ns.AddNamespace("xsi", schemaXsi);
            return ns;
        }
    
    #region SavePart
    /// <summary>
    /// Saves the XmlDocument into the package at the specified Uri.
    /// </summary>
    /// <param name="uri">The Uri of the component</param>
    /// <param name="xmlDoc">The XmlDocument to save</param>
    internal void SavePart(Uri uri, XmlDocument xmlDoc)
    {
            Packaging.ZipPackagePart part = _package.GetPart(uri);
      xmlDoc.Save(part.GetStream(FileMode.Create, FileAccess.Write));
    }
        /// <summary>
    /// Saves the XmlDocument into the package at the specified Uri.
    /// </summary>
    /// <param name="uri">The Uri of the component</param>
    /// <param name="xmlDoc">The XmlDocument to save</param>
        internal void SaveWorkbook(Uri uri, XmlDocument xmlDoc)
    {
            Packaging.ZipPackagePart part = _package.GetPart(uri);
            if(Workbook.VbaProject==null)
            {
                if (part.ContentType != contentTypeWorkbookDefault)
                {
                    part = _package.CreatePart(uri, contentTypeWorkbookDefault, Compression);
                }
            }
            else
            {
                if (part.ContentType != contentTypeWorkbookMacroEnabled)
                {
                    var rels = part.GetRelationships();
                    _package.DeletePart(uri);
                    part = Package.CreatePart(uri, contentTypeWorkbookMacroEnabled);
                    foreach (var rel in rels)
                    {
                        Package.DeleteRelationship(rel.Id);
                        part.CreateRelationship(rel.TargetUri, rel.TargetMode, rel.RelationshipType);
                    }
                }
            }
      xmlDoc.Save(part.GetStream(FileMode.Create, FileAccess.Write));
    }

        #endregion

    #region Dispose
    /// <summary>
    /// Closes the package.
    /// </summary>
    public void Dispose()
    {
            if(_package != null)
            {
                if (_isExternalStream==false && Stream != null && (Stream.CanRead || Stream.CanWrite))
                {
                    Stream.Close();
                }
                _package.Close();
                if(_isExternalStream==false) ((IDisposable)_stream).Dispose();
                if(_workbook != null)
                {
                    _workbook.Dispose();
                }
                _package = null;
                _images = null;
                _file = null;
                _workbook = null;
                _stream = null;
                _workbook = null;
            }
    }
    #endregion

    #region Save  // ExcelPackage save
        /// <summary>
        /// Saves all the components back into the package.
        /// This method recursively calls the Save method on all sub-components.
        /// We close the package after the save is done.
        /// </summary>
        public void Save()
        {
            try
            {
                Workbook.Save();
                if (File == null)
                {
                    if(Encryption.IsEncrypted)
                    {
#if !MONO
                        var ms = new MemoryStream();
                        _package.Save(ms);
                        byte[] file = ms.ToArray();
                        EncryptedPackageHandler eph = new EncryptedPackageHandler();
                        var msEnc = eph.EncryptPackage(file, Encryption);
                        CopyStream(msEnc, ref _stream);
#endif
#if MONO
                        throw new NotSupportedException("Encryption is not supported under Mono.");
#endif
                    }
                    else
                    {
                        _package.Save(_stream);
                    }
                    _stream.Flush();
                    _package.Close();
                }
                else
                {
                    if (System.IO.File.Exists(File.FullName))
                    {
                        try
                        {
                            System.IO.File.Delete(File.FullName);
                        }
                        catch (Exception ex)
                        {
                            throw (new Exception(string.Format("Error overwriting file {0}", File.FullName), ex));
                        }
                    }

                    _package.Save(_stream);
                    _package.Close();
                    if (Stream is MemoryStream)
                    {
                        var fi = new FileStream(File.FullName, FileMode.Create);
                        //EncryptPackage
                        if (Encryption.IsEncrypted)
                        {
#if !MONO
                            byte[] file = ((MemoryStream)Stream).ToArray();
                            EncryptedPackageHandler eph = new EncryptedPackageHandler();
                            var ms = eph.EncryptPackage(file, Encryption);

                            fi.Write(ms.GetBuffer(), 0, (int)ms.Length);
#endif
#if MONO
                            throw new NotSupportedException("Encryption is not supported under Mono.");
#endif
                        }
                        else
                        {                            
                            fi.Write(((MemoryStream)Stream).GetBuffer(), 0, (int)Stream.Length);
                        }
                        fi.Close();
                    }
                    else
                    {
                        System.IO.File.WriteAllBytes(File.FullName, GetAsByteArray(false));
                    }
                }
            }
            catch (Exception ex)
            {
                if (File == null)
                {
                    throw;
                }
                else
                {
                    throw (new InvalidOperationException(string.Format("Error saving file {0}", File.FullName), ex));
                }
            }
        }
        /// <summary>
        /// Saves all the components back into the package.
        /// This method recursively calls the Save method on all sub-components.
        /// The package is closed after it ha
        /// d to encrypt the workbook with. 
        /// </summary>
        /// <param name="password">This parameter overrides the Workbook.Encryption.Password.</param>
        public void Save(string password)
    {
            Encryption.Password = password;
            Save();
        }
        /// <summary>
        /// Saves the workbook to a new file
        /// The package is closed after it has been saved        
        /// </summary>
        /// <param name="file">The file location</param>
        public void SaveAs(FileInfo file)
        {
            File = file;
            Save();
        }
        /// <summary>
        /// Saves the workbook to a new file
        /// The package is closed after it has been saved
        /// </summary>
        /// <param name="file">The file</param>
        /// <param name="password">The password to encrypt the workbook with. 
        /// This parameter overrides the Encryption.Password.</param>
        public void SaveAs(FileInfo file, string password)
        {
            File = file;
            Encryption.Password = password;
            Save();
        }
        /// <summary>
        /// Copies the Package to the Outstream
        /// The package is closed after it has been saved
        /// </summary>
        /// <param name="OutputStream">The stream to copy the package to</param>
        public void SaveAs(Stream OutputStream)
        {
            File = null;
            Save();

            if (OutputStream != _stream)
            {
                if (Encryption.IsEncrypted)
                {
#if !MONO
                    //Encrypt Workbook
                    Byte[] file = new byte[Stream.Length];
                    long pos = Stream.Position;
                    Stream.Seek(0, SeekOrigin.Begin);
                    Stream.Read(file, 0, (int) Stream.Length);
                    EncryptedPackageHandler eph = new EncryptedPackageHandler();
                    var ms = eph.EncryptPackage(file, Encryption);
                    CopyStream(ms, ref OutputStream);
#endif
#if MONO
                throw new NotSupportedException("Encryption is not supported under Mono.");
#endif
                }
                else
                {
                    CopyStream(_stream, ref OutputStream);
                }
            }
        }
        /// <summary>
        /// Copies the Package to the Outstream
        /// The package is closed after it has been saved
        /// </summary>
        /// <param name="OutputStream">The stream to copy the package to</param>
        /// <param name="password">The password to encrypt the workbook with. 
        /// This parameter overrides the Encryption.Password.</param>
        public void SaveAs(Stream OutputStream, string password)
        {
            Encryption.Password = password;
            SaveAs(OutputStream);
        }
        FileInfo _file = null;

        /// <summary>
        /// The output file. Null if no file is used
        /// </summary>
        public FileInfo File
        {
            get
            {
                return _file;
            }
            set
            {
                _file = value;
            }
        }
        /// <summary>
        /// The output stream. This stream is the not the encrypted package.
        /// To get the encrypted package use the SaveAs(stream) method.
        /// </summary>
        public Stream Stream
        {
            get
            {
                return _stream;
            }
        }
    #endregion
        /// <summary>
        /// Compression option for the package
        /// </summary>        
        public CompressionLevel Compression 
        { 
            get
            {
                return Package.Compression;
            }
            set
            {
                Package.Compression = value;
            }
        }
    #region GetXmlFromUri
    /// <summary>
    /// Get the XmlDocument from an URI
    /// </summary>
    /// <param name="uri">The Uri to the part</param>
    /// <returns>The XmlDocument</returns>
    internal XmlDocument GetXmlFromUri(Uri uri)
    {
      XmlDocument xml = new XmlDocument();
      Packaging.ZipPackagePart part = _package.GetPart(uri);
            XmlHelper.LoadXmlSafe(xml, part.GetStream()); 
      return (xml);
    }
    #endregion

        /// <summary>
        /// Saves and returns the Excel files as a bytearray.
        /// Note that the package is closed upon save
        /// </summary>
        /// <example>      
        /// Example how to return a document from a Webserver...
        /// <code> 
        ///  ExcelPackage package=new ExcelPackage();
        ///  /**** ... Create the document ****/
        ///  Byte[] bin = package.GetAsByteArray();
        ///  Response.ContentType = "Application/vnd.ms-Excel";
        ///  Response.AddHeader("content-disposition", "attachment;  filename=TheFile.xlsx");
    ///  Response.BinaryWrite(bin);
        /// </code>
        /// </example>
        /// <returns></returns>
        public byte[] GetAsByteArray()
        {
           return GetAsByteArray(true);
        }
        /// <summary>
        /// Saves and returns the Excel files as a bytearray
        /// Note that the package is closed upon save
        /// </summary>
        /// <example>      
        /// Example how to return a document from a Webserver...
        /// <code> 
        ///  ExcelPackage package=new ExcelPackage();
        ///  /**** ... Create the document ****/
        ///  Byte[] bin = package.GetAsByteArray();
        ///  Response.ContentType = "Application/vnd.ms-Excel";
        ///  Response.AddHeader("content-disposition", "attachment;  filename=TheFile.xlsx");
        ///  Response.BinaryWrite(bin);
        /// </code>
        /// </example>
        /// <param name="password">The password to encrypt the workbook with. 
        /// This parameter overrides the Encryption.Password.</param>
        /// <returns></returns>
        public byte[] GetAsByteArray(string password)
        {
            if (password != null)
            {
                Encryption.Password = password;
            }
            return GetAsByteArray(true);
        }
        internal byte[] GetAsByteArray(bool save)
        {
            if (save)
            {
                Workbook.Save();
                _package.Close();
                _package.Save(_stream);
            }
            Byte[] byRet = new byte[Stream.Length];
            long pos = Stream.Position;            
            Stream.Seek(0, SeekOrigin.Begin);
            Stream.Read(byRet, 0, (int)Stream.Length);

            //Encrypt Workbook?
            if (Encryption.IsEncrypted)
            {
#if !MONO
                EncryptedPackageHandler eph=new EncryptedPackageHandler();
                var ms = eph.EncryptPackage(byRet, Encryption);
                byRet = ms.ToArray();
#endif
            }

            Stream.Seek(pos, SeekOrigin.Begin);
            Stream.Close();
            return byRet;
        }
        /// <summary>
        /// Loads the specified package data from a stream.
        /// </summary>
        /// <param name="input">The input.</param>
        public void Load(Stream input)
        {
            Load(input, new MemoryStream(), null);
        }
        /// <summary>
        /// Loads the specified package data from a stream.
        /// </summary>
        /// <param name="input">The input.</param>
        /// <param name="Password">The password to decrypt the document</param>
        public void Load(Stream input, string Password)
        {
            Load(input, new MemoryStream(), Password);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="input"></param>
        /// <param name="output"></param>
        /// <param name="Password"></param>
        private void Load(Stream input, Stream output, string Password)
        {
            //Release some resources:
            if (this._package != null)
            {
                this._package.Close();
                this._package = null;
            }
            if (this._stream != null)
            {
                this._stream.Close();
                this._stream.Dispose();
                this._stream = null;
            }
            _isExternalStream = true;
            if (input.Length == 0) // Template is blank, Construct new
            {
                _stream = output;
                ConstructNewFile(Password);
            }
            else
            {
                Stream ms;
                this._stream = output;
                if (Password != null)
                {
#if !MONO
                    Stream encrStream = new MemoryStream();
                    CopyStream(input, ref encrStream);
                    EncryptedPackageHandler eph = new EncryptedPackageHandler();
                    Encryption.Password = Password;
                    ms = eph.DecryptPackage((MemoryStream)encrStream, Encryption);
#endif
#if MONO
                    throw new NotSupportedException("Encryption is not supported under Mono.");
#endif
                }
                else
                {
                    ms = new MemoryStream();
                    CopyStream(input, ref ms);
                }

                try
                {
                    //this._package = Package.Open(this._stream, FileMode.Open, FileAccess.ReadWrite);
                    _package = new Packaging.ZipPackage(ms);
                }
                catch (Exception ex)
                {
#if !MONO
                    EncryptedPackageHandler eph = new EncryptedPackageHandler();
                    if (Password == null && CompoundDocument.IsStorageILockBytes(CompoundDocument.GetLockbyte((MemoryStream)_stream)) == 0)
                    {
                        throw new Exception("Can not open the package. Package is an OLE compound document. If this is an encrypted package, please supply the password", ex);
                    }
                    else
                    {
                        throw;
                    }
#endif
#if MONO
                    throw;
#endif
                }
            }            
            //Clear the workbook so that it gets reinitialized next time
            this._workbook = null;
        }
        static object _lock=new object();
        /// <summary>
        /// Copies the input stream to the output stream.
        /// </summary>
        /// <param name="inputStream">The input stream.</param>
        /// <param name="outputStream">The output stream.</param>
        internal static void CopyStream(Stream inputStream, ref Stream outputStream)
        {
            if (!inputStream.CanRead)
            {
                throw (new Exception("Can not read from inputstream"));
            }
            if (!outputStream.CanWrite)
            {
                throw (new Exception("Can not write to outputstream"));
            }
            if (inputStream.CanSeek)
            {
                inputStream.Seek(0, SeekOrigin.Begin);
            }

                const int bufferLength = 8096;
                var buffer = new Byte[bufferLength];
                lock (_lock)
                {
                    int bytesRead = inputStream.Read(buffer, 0, bufferLength);
                    // write the required bytes
                    while (bytesRead > 0)
                    {
                        outputStream.Write(buffer, 0, bytesRead);
                        bytesRead = inputStream.Read(buffer, 0, bufferLength);
                    }
                    outputStream.Flush();
            }
        }
    }
}