Free cookie consent management tool by TermsFeed Policy Generator

source: trunk/sources/HeuristicLab.ExtLibs/HeuristicLab.EPPlus/4.0.3/EPPlus-4.0.3/ExcelWorkbook.cs @ 12074

Last change on this file since 12074 was 12074, checked in by sraggl, 9 years ago

#2341: Added EPPlus-4.0.3 to ExtLibs

File size: 37.2 KB
Line 
1/*******************************************************************************
2 * You may amend and distribute as you like, but don't remove this header!
3 *
4 * EPPlus provides server-side generation of Excel 2007/2010 spreadsheets.
5 * See http://www.codeplex.com/EPPlus for details.
6 *
7 * Copyright (C) 2011  Jan Källman
8 *
9 * This library is free software; you can redistribute it and/or
10 * modify it under the terms of the GNU Lesser General Public
11 * License as published by the Free Software Foundation; either
12 * version 2.1 of the License, or (at your option) any later version.
13
14 * This library is distributed in the hope that it will be useful,
15 * but WITHOUT ANY WARRANTY; without even the implied warranty of
16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
17 * See the GNU Lesser General Public License for more details.
18 *
19 * The GNU Lesser General Public License can be viewed at http://www.opensource.org/licenses/lgpl-license.php
20 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
21 *
22 * All code and executables are provided "as is" with no warranty either express or implied.
23 * The author accepts no liability for any damage or loss of business that this product may cause.
24 *
25 * Code change notes:
26 *
27 * Author             Change            Date
28 * ******************************************************************************
29 * Jan Källman        Initial Release          2011-01-01
30 * Jan Källman        License changed GPL-->LGPL 2011-12-27
31 * Richard Tallent    Fix escaping of quotes          2012-10-31
32 *******************************************************************************/
33using System;
34using System.Xml;
35using System.IO;
36using System.Collections.Generic;
37using System.Text;
38using System.Security;
39using System.Globalization;
40using System.Text.RegularExpressions;
41using OfficeOpenXml.VBA;
42using System.Drawing;
43using OfficeOpenXml.Utils;
44using System.Windows.Media;
45using System.Windows;
46using Ionic.Zip;
47using OfficeOpenXml.FormulaParsing;
48using OfficeOpenXml.FormulaParsing.Excel.Functions;
49using OfficeOpenXml.FormulaParsing.LexicalAnalysis;
50using OfficeOpenXml.Packaging.Ionic.Zip;
51namespace OfficeOpenXml
52{
53  #region Public Enum ExcelCalcMode
54  /// <summary>
55  /// How the application should calculate formulas in the workbook
56  /// </summary>
57  public enum ExcelCalcMode
58  {
59    /// <summary>
60    /// Indicates that calculations in the workbook are performed automatically when cell values change.
61    /// The application recalculates those cells that are dependent on other cells that contain changed values.
62    /// This mode of calculation helps to avoid unnecessary calculations.
63    /// </summary>
64    Automatic,
65    /// <summary>
66    /// Indicates tables be excluded during automatic calculation
67    /// </summary>
68    AutomaticNoTable,
69    /// <summary>
70    /// Indicates that calculations in the workbook be triggered manually by the user.
71    /// </summary>
72    Manual
73  }
74  #endregion
75
76  /// <summary>
77  /// Represents the Excel workbook and provides access to all the
78  /// document properties and worksheets within the workbook.
79  /// </summary>
80  public sealed class ExcelWorkbook : XmlHelper, IDisposable
81  {
82    internal class SharedStringItem
83    {
84      internal int pos;
85      internal string Text;
86      internal bool isRichText = false;
87    }
88    #region Private Properties
89    internal ExcelPackage _package;
90    private ExcelWorksheets _worksheets;
91    private OfficeProperties _properties;
92
93    private ExcelStyles _styles;
94    #endregion
95
96    #region ExcelWorkbook Constructor
97    /// <summary>
98    /// Creates a new instance of the ExcelWorkbook class.
99    /// </summary>
100    /// <param name="package">The parent package</param>
101    /// <param name="namespaceManager">NamespaceManager</param>
102    internal ExcelWorkbook(ExcelPackage package, XmlNamespaceManager namespaceManager) :
103      base(namespaceManager)
104    {
105      _package = package;
106      WorkbookUri = new Uri("/xl/workbook.xml", UriKind.Relative);
107      SharedStringsUri = new Uri("/xl/sharedStrings.xml", UriKind.Relative);
108      StylesUri = new Uri("/xl/styles.xml", UriKind.Relative);
109
110      _names = new ExcelNamedRangeCollection(this);
111      _namespaceManager = namespaceManager;
112      TopNode = WorkbookXml.DocumentElement;
113      SchemaNodeOrder = new string[] { "fileVersion", "fileSharing", "workbookPr", "workbookProtection", "bookViews", "sheets", "functionGroups", "functionPrototypes", "externalReferences", "definedNames", "calcPr", "oleSize", "customWorkbookViews", "pivotCaches", "smartTagPr", "smartTagTypes", "webPublishing", "fileRecoveryPr", };
114        FullCalcOnLoad = true;  //Full calculation on load by default, for both new workbooks and templates.
115      GetSharedStrings();
116    }
117    #endregion
118
119    internal Dictionary<string, SharedStringItem> _sharedStrings = new Dictionary<string, SharedStringItem>(); //Used when reading cells.
120    internal List<SharedStringItem> _sharedStringsList = new List<SharedStringItem>(); //Used when reading cells.
121    internal ExcelNamedRangeCollection _names;
122    internal int _nextDrawingID = 0;
123    internal int _nextTableID = 1;
124    internal int _nextPivotTableID = 1;
125    internal XmlNamespaceManager _namespaceManager;
126        internal FormulaParser _formulaParser = null;
127      internal FormulaParserManager _parserManager;
128        internal CellStore<List<Token>> _formulaTokens;
129    /// <summary>
130    /// Read shared strings to list
131    /// </summary>
132    private void GetSharedStrings()
133    {
134      if (_package.Package.PartExists(SharedStringsUri))
135      {
136        var xml = _package.GetXmlFromUri(SharedStringsUri);
137        XmlNodeList nl = xml.SelectNodes("//d:sst/d:si", NameSpaceManager);
138        _sharedStringsList = new List<SharedStringItem>();
139        if (nl != null)
140        {
141          foreach (XmlNode node in nl)
142          {
143            XmlNode n = node.SelectSingleNode("d:t", NameSpaceManager);
144            if (n != null)
145            {
146                            _sharedStringsList.Add(new SharedStringItem() { Text = ConvertUtil.ExcelDecodeString(n.InnerText) });
147            }
148            else
149            {
150              _sharedStringsList.Add(new SharedStringItem() { Text = node.InnerXml, isRichText = true });
151            }
152          }
153        }
154                //Delete the shared string part, it will be recreated when the package is saved.
155                foreach (var rel in Part.GetRelationships())
156                {
157                    if (rel.TargetUri.OriginalString.EndsWith("sharedstrings.xml", StringComparison.InvariantCultureIgnoreCase))
158                    {
159                        Part.DeleteRelationship(rel.Id);
160                        break;
161                    }
162                }               
163                _package.Package.DeletePart(SharedStringsUri); //Remove the part, it is recreated when saved.
164      }
165    }
166    internal void GetDefinedNames()
167    {
168      XmlNodeList nl = WorkbookXml.SelectNodes("//d:definedNames/d:definedName", NameSpaceManager);
169      if (nl != null)
170      {
171        foreach (XmlElement elem in nl)
172        {
173          string fullAddress = elem.InnerText;
174
175          int localSheetID;
176          ExcelWorksheet nameWorksheet;
177          if(!int.TryParse(elem.GetAttribute("localSheetId"), out localSheetID))
178          {
179            localSheetID = -1;
180            nameWorksheet=null;
181          }
182          else
183          {
184            nameWorksheet=Worksheets[localSheetID + 1];
185          }
186          var addressType = ExcelAddressBase.IsValid(fullAddress);
187          ExcelRangeBase range;
188          ExcelNamedRange namedRange;
189
190          if (fullAddress.IndexOf("[") == 0)
191          {
192            int start = fullAddress.IndexOf("[");
193            int end = fullAddress.IndexOf("]", start);
194            if (start >= 0 && end >= 0)
195            {
196
197              string externalIndex = fullAddress.Substring(start + 1, end - start - 1);
198              int index;
199              if (int.TryParse(externalIndex, out index))
200              {
201                if (index > 0 && index <= _externalReferences.Count)
202                {
203                  fullAddress = fullAddress.Substring(0, start) + "[" + _externalReferences[index - 1] + "]" + fullAddress.Substring(end + 1);
204                }
205              }
206            }
207          }
208
209          if (addressType == ExcelAddressBase.AddressType.Invalid || addressType == ExcelAddressBase.AddressType.InternalName || addressType == ExcelAddressBase.AddressType.ExternalName || addressType==ExcelAddressBase.AddressType.Formula || addressType==ExcelAddressBase.AddressType.ExternalAddress)    //A value or a formula
210          {
211            double value;
212            range = new ExcelRangeBase(this, nameWorksheet, elem.GetAttribute("name"), true);
213            if (nameWorksheet == null)
214            {
215              namedRange = _names.Add(elem.GetAttribute("name"), range);
216            }
217            else
218            {
219              namedRange = nameWorksheet.Names.Add(elem.GetAttribute("name"), range);
220            }
221           
222            if (fullAddress.StartsWith("\"")) //String value
223            {
224              namedRange.NameValue = fullAddress.Substring(1,fullAddress.Length-2);
225            }
226            else if (double.TryParse(fullAddress, NumberStyles.Any, CultureInfo.InvariantCulture, out value))
227            {
228              namedRange.NameValue = value;
229            }
230            else
231            {
232                            //if (addressType == ExcelAddressBase.AddressType.ExternalAddress || addressType == ExcelAddressBase.AddressType.ExternalName)
233                            //{
234                            //    var r = new ExcelAddress(fullAddress);
235                            //    namedRange.NameFormula = '\'[' + r._wb
236                            //}
237                            //else
238                            //{
239                                namedRange.NameFormula = fullAddress;
240                            //}
241            }
242          }
243          else
244          {
245            ExcelAddress addr = new ExcelAddress(fullAddress, _package, null);
246            if (localSheetID > -1)
247            {
248              if (string.IsNullOrEmpty(addr._ws))
249              {
250                namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[localSheetID + 1], fullAddress, false));
251              }
252              else
253              {
254                namedRange = Worksheets[localSheetID + 1].Names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, Worksheets[addr._ws], fullAddress, false));
255              }
256            }
257            else
258            {
259              var ws = Worksheets[addr._ws];
260              namedRange = _names.Add(elem.GetAttribute("name"), new ExcelRangeBase(this, ws, fullAddress, false));
261            }
262          }
263          if (elem.GetAttribute("hidden") == "1" && namedRange != null) namedRange.IsNameHidden = true;
264          if(!string.IsNullOrEmpty(elem.GetAttribute("comment"))) namedRange.NameComment=elem.GetAttribute("comment");
265        }
266      }
267    }
268    #region Worksheets
269    /// <summary>
270    /// Provides access to all the worksheets in the workbook.
271    /// </summary>
272    public ExcelWorksheets Worksheets
273    {
274      get
275      {
276        if (_worksheets == null)
277        {
278          var sheetsNode = _workbookXml.DocumentElement.SelectSingleNode("d:sheets", _namespaceManager);
279          if (sheetsNode == null)
280          {
281            sheetsNode = CreateNode("d:sheets");
282          }
283         
284          _worksheets = new ExcelWorksheets(_package, _namespaceManager, sheetsNode);
285        }
286        return (_worksheets);
287      }
288    }
289    #endregion
290
291    /// <summary>
292    /// Provides access to named ranges
293    /// </summary>
294    public ExcelNamedRangeCollection Names
295    {
296      get
297      {
298        return _names;
299      }
300    }
301    #region Workbook Properties
302    decimal _standardFontWidth = decimal.MinValue;
303        string _fontID = "";
304        internal FormulaParser FormulaParser
305        {
306            get
307            {
308                if (_formulaParser == null)
309                {
310                    _formulaParser = new FormulaParser(new EpplusExcelDataProvider(_package));
311                }
312                return _formulaParser;
313            }
314        }
315
316      public FormulaParserManager FormulaParserManager
317      {
318          get
319          {
320              if (_parserManager == null)
321              {
322                  _parserManager = new FormulaParserManager(FormulaParser);
323              }
324              return _parserManager;
325          }
326      }
327
328        /// <summary>
329    /// Max font width for the workbook
330        /// <remarks>This method uses GDI. If you use Asure or another environment that does not support GDI, you have to set this value manually if you don't use the standard Calibri font</remarks>
331    /// </summary>
332    public decimal MaxFontWidth
333    {
334      get
335      {
336                if (_standardFontWidth == decimal.MinValue || _fontID != Styles.Fonts[0].Id)
337        {
338          var font = Styles.Fonts[0];
339                    try
340                    {
341                        //Font f = new Font(font.Name, font.Size);
342                        _standardFontWidth = 0;
343                        _fontID = font.Id;                       
344                        Typeface tf = new Typeface(new System.Windows.Media.FontFamily(font.Name),
345                                                     (font.Italic) ? FontStyles.Normal : FontStyles.Italic,
346                                                     (font.Bold) ? FontWeights.Bold : FontWeights.Normal,
347                                                     FontStretches.Normal);
348                        for(int i=0;i<10;i++)
349                        {
350                            var ft = new System.Windows.Media.FormattedText("0123456789".Substring(i,1), CultureInfo.InvariantCulture, System.Windows.FlowDirection.LeftToRight, tf, font.Size * (96D / 72D), System.Windows.Media.Brushes.Black);
351                            var width=(int)Math.Round(ft.Width,0);   
352                            if(width>_standardFontWidth)
353                            {
354                                _standardFontWidth = width;
355                            }
356                        }
357                         
358                        //var size = new System.Windows.Size { Width = ft.WidthIncludingTrailingWhitespace, Height = ft.Height };
359
360                        //using (Bitmap b = new Bitmap(1, 1))
361                        //{
362                        //    using (Graphics g = Graphics.FromImage(b))
363                        //    {
364                        //        _standardFontWidth = (decimal)Math.Truncate(g.MeasureString("00", f).Width - g.MeasureString("0", f).Width);
365                        //    }
366                        //}
367                        if (_standardFontWidth <= 0) //No GDI?
368                        {
369                            _standardFontWidth = (int)(font.Size * (2D / 3D)); //Aprox. for Calibri.
370                        }
371                    }
372                    catch   //Error, set default value
373                    {
374                        _standardFontWidth = (int)(font.Size * (2D / 3D)); //Aprox for Calibri.
375                    }
376        }
377        return _standardFontWidth;
378      }
379            set
380            {
381                _standardFontWidth = value;
382            }
383    }
384    ExcelProtection _protection = null;
385    /// <summary>
386    /// Access properties to protect or unprotect a workbook
387    /// </summary>
388    public ExcelProtection Protection
389    {
390      get
391      {
392        if (_protection == null)
393        {
394          _protection = new ExcelProtection(NameSpaceManager, TopNode, this);
395          _protection.SchemaNodeOrder = SchemaNodeOrder;
396        }
397        return _protection;
398      }
399    }       
400    ExcelWorkbookView _view = null;
401    /// <summary>
402    /// Access to workbook view properties
403    /// </summary>
404    public ExcelWorkbookView View
405    {
406      get
407      {
408        if (_view == null)
409        {
410          _view = new ExcelWorkbookView(NameSpaceManager, TopNode, this);
411        }
412        return _view;
413      }
414    }
415        ExcelVbaProject _vba = null;
416        /// <summary>
417        /// A reference to the VBA project.
418        /// Null if no project exists.
419        /// Use Workbook.CreateVBAProject to create a new VBA-Project
420        /// </summary>
421        public ExcelVbaProject VbaProject
422        {
423            get
424            {
425                if (_vba == null)
426                {
427                    if(_package.Package.PartExists(new Uri(ExcelVbaProject.PartUri, UriKind.Relative)))
428                    {
429                        _vba = new ExcelVbaProject(this);
430                    }
431                }
432                return _vba;
433            }
434        }
435
436        /// <summary>
437        /// Create an empty VBA project.
438        /// </summary>
439        public void CreateVBAProject()
440        {
441#if !MONO
442            if (_vba != null || _package.Package.PartExists(new Uri(ExcelVbaProject.PartUri, UriKind.Relative)))
443            {
444                throw (new InvalidOperationException("VBA project already exists."));
445            }
446                       
447            _vba = new ExcelVbaProject(this);
448            _vba.Create();
449#endif
450#if MONO
451            throw new NotSupportedException("Creating a VBA project is not supported under Mono.");
452#endif
453        }
454    /// <summary>
455    /// URI to the workbook inside the package
456    /// </summary>
457    internal Uri WorkbookUri { get; private set; }
458    /// <summary>
459        /// URI to the styles inside the package
460    /// </summary>
461    internal Uri StylesUri { get; private set; }
462    /// <summary>
463        /// URI to the shared strings inside the package
464    /// </summary>
465    internal Uri SharedStringsUri { get; private set; }
466    /// <summary>
467    /// Returns a reference to the workbook's part within the package
468    /// </summary>
469    internal Packaging.ZipPackagePart Part { get { return (_package.Package.GetPart(WorkbookUri)); } }
470   
471    #region WorkbookXml
472    private XmlDocument _workbookXml;
473    /// <summary>
474    /// Provides access to the XML data representing the workbook in the package.
475    /// </summary>
476    public XmlDocument WorkbookXml
477    {
478      get
479      {
480        if (_workbookXml == null)
481        {
482          CreateWorkbookXml(_namespaceManager);
483        }
484        return (_workbookXml);
485      }
486    }
487        const string codeModuleNamePath = "d:workbookPr/@codeName";
488        internal string CodeModuleName
489        {
490            get
491            {
492                return GetXmlNodeString(codeModuleNamePath);
493            }
494            set
495            {
496                SetXmlNodeString(codeModuleNamePath,value);
497            }
498        }
499        internal void CodeNameChange(string value)
500        {
501            CodeModuleName = value;
502        }
503        public VBA.ExcelVBAModule CodeModule
504        {
505            get
506            {
507                if (VbaProject != null)
508                {
509                    return VbaProject.Modules[CodeModuleName];
510                }
511                else
512                {
513                    return null;
514                }
515            }
516        }
517
518        const string date1904Path = "d:workbookPr/@date1904";
519        internal const double date1904Offset = 365.5 * 4;  // offset to fix 1900 and 1904 differences, 4 OLE years
520        /// <summary>
521        /// The date systems used by Microsoft Excel can be based on one of two different dates. By default, a serial number of 1 in Microsoft Excel represents January 1, 1900.
522        /// The default for the serial number 1 can be changed to represent January 2, 1904.
523        /// This option was included in Microsoft Excel for Windows to make it compatible with Excel for the Macintosh, which defaults to January 2, 1904.
524        /// </summary>
525        public bool Date1904
526        {
527            get
528            {
529                return GetXmlNodeBool(date1904Path, false);
530               
531            }
532            set
533            {
534                if (Date1904 != value)
535                {
536                    // Like Excel when the option it's changed update it all cells with Date format
537                    foreach (var item in Worksheets)
538                    {
539                        item.UpdateCellsWithDate1904Setting();
540                    }
541                }
542
543                SetXmlNodeBool(date1904Path, value, false);
544            }
545        }
546     
547       
548    /// <summary>
549    /// Create or read the XML for the workbook.
550    /// </summary>
551    private void CreateWorkbookXml(XmlNamespaceManager namespaceManager)
552    {
553      if (_package.Package.PartExists(WorkbookUri))
554        _workbookXml = _package.GetXmlFromUri(WorkbookUri);
555      else
556      {
557        // create a new workbook part and add to the package
558        Packaging.ZipPackagePart partWorkbook = _package.Package.CreatePart(WorkbookUri, @"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml", _package.Compression);
559
560        // create the workbook
561        _workbookXml = new XmlDocument(namespaceManager.NameTable);               
562       
563        _workbookXml.PreserveWhitespace = ExcelPackage.preserveWhitespace;
564        // create the workbook element
565        XmlElement wbElem = _workbookXml.CreateElement("workbook", ExcelPackage.schemaMain);
566
567        // Add the relationships namespace
568        wbElem.SetAttribute("xmlns:r", ExcelPackage.schemaRelationships);
569
570        _workbookXml.AppendChild(wbElem);
571
572        // create the bookViews and workbooks element
573        XmlElement bookViews = _workbookXml.CreateElement("bookViews", ExcelPackage.schemaMain);
574        wbElem.AppendChild(bookViews);
575        XmlElement workbookView = _workbookXml.CreateElement("workbookView", ExcelPackage.schemaMain);
576        bookViews.AppendChild(workbookView);
577
578        // save it to the package
579        StreamWriter stream = new StreamWriter(partWorkbook.GetStream(FileMode.Create, FileAccess.Write));
580        _workbookXml.Save(stream);
581        //stream.Close();
582        _package.Package.Flush();
583      }
584    }
585    #endregion
586    #region StylesXml
587    private XmlDocument _stylesXml;
588    /// <summary>
589    /// Provides access to the XML data representing the styles in the package.
590    /// </summary>
591    public XmlDocument StylesXml
592    {
593      get
594      {
595        if (_stylesXml == null)
596        {
597          if (_package.Package.PartExists(StylesUri))
598            _stylesXml = _package.GetXmlFromUri(StylesUri);
599          else
600          {
601            // create a new styles part and add to the package
602            Packaging.ZipPackagePart part = _package.Package.CreatePart(StylesUri, @"application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml", _package.Compression);
603            // create the style sheet
604
605            StringBuilder xml = new StringBuilder("<styleSheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">");
606            xml.Append("<numFmts />");
607            xml.Append("<fonts count=\"1\"><font><sz val=\"11\" /><name val=\"Calibri\" /></font></fonts>");
608            xml.Append("<fills><fill><patternFill patternType=\"none\" /></fill><fill><patternFill patternType=\"gray125\" /></fill></fills>");
609            xml.Append("<borders><border><left /><right /><top /><bottom /><diagonal /></border></borders>");
610            xml.Append("<cellStyleXfs count=\"1\"><xf numFmtId=\"0\" fontId=\"0\" /></cellStyleXfs>");
611            xml.Append("<cellXfs count=\"1\"><xf numFmtId=\"0\" fontId=\"0\" xfId=\"0\" /></cellXfs>");
612            xml.Append("<cellStyles><cellStyle name=\"Normal\" xfId=\"0\" builtinId=\"0\" /></cellStyles>");
613                        xml.Append("<dxfs count=\"0\" />");
614                        xml.Append("</styleSheet>");
615           
616            _stylesXml = new XmlDocument();
617            _stylesXml.LoadXml(xml.ToString());
618           
619            //Save it to the package
620            StreamWriter stream = new StreamWriter(part.GetStream(FileMode.Create, FileAccess.Write));
621
622            _stylesXml.Save(stream);
623            //stream.Close();
624            _package.Package.Flush();
625
626            // create the relationship between the workbook and the new shared strings part
627            _package.Workbook.Part.CreateRelationship(UriHelper.GetRelativeUri(WorkbookUri, StylesUri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/styles");
628            _package.Package.Flush();
629          }
630        }
631        return (_stylesXml);
632      }
633      set
634      {
635        _stylesXml = value;
636      }
637    }
638    /// <summary>
639    /// Package styles collection. Used internally to access style data.
640    /// </summary>
641    public ExcelStyles Styles
642    {
643      get
644      {
645        if (_styles == null)
646        {
647          _styles = new ExcelStyles(NameSpaceManager, StylesXml, this);
648        }
649        return _styles;
650      }
651    }
652    #endregion
653
654    #region Office Document Properties
655    /// <summary>
656    /// The office document properties
657    /// </summary>
658    public OfficeProperties Properties
659    {
660      get
661      {
662        if (_properties == null)
663        {
664          //  Create a NamespaceManager to handle the default namespace,
665          //  and create a prefix for the default namespace:                   
666          _properties = new OfficeProperties(_package, NameSpaceManager);
667        }
668        return _properties;
669      }
670    }
671    #endregion
672
673    #region CalcMode
674    private string CALC_MODE_PATH = "d:calcPr/@calcMode";
675    /// <summary>
676    /// Calculation mode for the workbook.
677    /// </summary>
678    public ExcelCalcMode CalcMode
679    {
680      get
681      {
682        string calcMode = GetXmlNodeString(CALC_MODE_PATH);
683        switch (calcMode)
684        {
685          case "autoNoTable":
686            return ExcelCalcMode.AutomaticNoTable;
687          case "manual":
688            return ExcelCalcMode.Manual;
689          default:
690            return ExcelCalcMode.Automatic;
691                       
692        }
693      }
694      set
695      {
696        switch (value)
697        {
698          case ExcelCalcMode.AutomaticNoTable:
699            SetXmlNodeString(CALC_MODE_PATH, "autoNoTable") ;
700            break;
701          case ExcelCalcMode.Manual:
702            SetXmlNodeString(CALC_MODE_PATH, "manual");
703            break;
704          default:
705            SetXmlNodeString(CALC_MODE_PATH, "auto");
706            break;
707
708        }
709      }
710      #endregion
711    }
712
713        private const string FULL_CALC_ON_LOAD_PATH = "d:calcPr/@fullCalcOnLoad";
714        /// <summary>
715        /// Should Excel do a full calculation after the workbook has been loaded?
716        /// <remarks>This property is always true for both new workbooks and loaded templates(on load). If this is not the wanted behavior set this property to false.</remarks>
717        /// </summary>
718        public bool FullCalcOnLoad
719      {
720          get
721          {
722                return GetXmlNodeBool(FULL_CALC_ON_LOAD_PATH);   
723          }
724          set
725          {
726                SetXmlNodeBool(FULL_CALC_ON_LOAD_PATH, value);
727          }
728      }
729    #endregion
730    #region Workbook Private Methods
731     
732    #region Save // Workbook Save
733    /// <summary>
734    /// Saves the workbook and all its components to the package.
735    /// For internal use only!
736    /// </summary>
737    internal void Save()  // Workbook Save
738    {
739      if (Worksheets.Count == 0)
740        throw new InvalidOperationException("The workbook must contain at least one worksheet");
741
742      DeleteCalcChain();
743
744            if (_vba == null && !_package.Package.PartExists(new Uri(ExcelVbaProject.PartUri, UriKind.Relative)))
745            {
746                if (Part.ContentType != ExcelPackage.contentTypeWorkbookDefault)
747                {
748                    Part.ContentType = ExcelPackage.contentTypeWorkbookDefault;
749                }
750            }
751            else
752            {
753                if (Part.ContentType != ExcelPackage.contentTypeWorkbookMacroEnabled)
754                {
755                    Part.ContentType = ExcelPackage.contentTypeWorkbookMacroEnabled;
756                }
757            }
758     
759            UpdateDefinedNamesXml();
760
761      // save the workbook
762      if (_workbookXml != null)
763      {
764        _package.SavePart(WorkbookUri, _workbookXml);
765      }
766
767      // save the properties of the workbook
768      if (_properties != null)
769      {
770        _properties.Save();
771      }
772
773      // save the style sheet
774      Styles.UpdateXml();
775      _package.SavePart(StylesUri, _stylesXml);
776
777      // save all the open worksheets
778      var isProtected = Protection.LockWindows || Protection.LockStructure;
779      foreach (ExcelWorksheet worksheet in Worksheets)
780      {
781        if (isProtected && Protection.LockWindows)
782        {
783          worksheet.View.WindowProtection = true;
784        }
785        worksheet.Save();
786                worksheet.Part.SaveHandler = worksheet.SaveHandler;
787      }
788
789            var part = _package.Package.CreatePart(SharedStringsUri, ExcelPackage.contentTypeSharedString, _package.Compression);
790            part.SaveHandler = SaveSharedStringHandler;
791            Part.CreateRelationship(UriHelper.GetRelativeUri(WorkbookUri, SharedStringsUri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/sharedStrings");
792            //UpdateSharedStringsXml();
793     
794      // Data validation
795      ValidateDataValidations();
796
797            //VBA
798            if (_vba!=null)
799            {
800#if !MONO
801                VbaProject.Save();
802#endif
803            }
804
805    }
806    private void DeleteCalcChain()
807    {
808      //Remove the calc chain if it exists.
809      Uri uriCalcChain = new Uri("/xl/calcChain.xml", UriKind.Relative);
810      if (_package.Package.PartExists(uriCalcChain))
811      {
812        Uri calcChain = new Uri("calcChain.xml", UriKind.Relative);
813        foreach (var relationship in _package.Workbook.Part.GetRelationships())
814        {
815          if (relationship.TargetUri == calcChain)
816          {
817            _package.Workbook.Part.DeleteRelationship(relationship.Id);
818            break;
819          }
820        }
821        // delete the calcChain part
822        _package.Package.DeletePart(uriCalcChain);
823      }
824    }
825
826    private void ValidateDataValidations()
827    {
828      foreach (var sheet in _package.Workbook.Worksheets)
829      {
830                if (!(sheet is ExcelChartsheet))
831                {
832                    sheet.DataValidations.ValidateAll();
833                }
834      }
835    }
836
837        private void SaveSharedStringHandler(ZipOutputStream stream, CompressionLevel compressionLevel, string fileName)
838    {
839            //Packaging.ZipPackagePart stringPart;
840            //if (_package.Package.PartExists(SharedStringsUri))
841            //{
842            //    stringPart=_package.Package.GetPart(SharedStringsUri);
843            //}
844            //else
845            //{
846            //    stringPart = _package.Package.CreatePart(SharedStringsUri, @"application/vnd.openxmlformats-officedocument.spreadsheetml.sharedStrings+xml", _package.Compression);
847                  //Part.CreateRelationship(UriHelper.GetRelativeUri(WorkbookUri, SharedStringsUri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/sharedStrings");
848            //}
849
850      //StreamWriter sw = new StreamWriter(stringPart.GetStream(FileMode.Create, FileAccess.Write));
851            //Init Zip
852            stream.CompressionLevel = (OfficeOpenXml.Packaging.Ionic.Zlib.CompressionLevel)compressionLevel;
853            stream.PutNextEntry(fileName);
854
855            var cache = new StringBuilder();           
856            var sw = new StreamWriter(stream);
857            cache.AppendFormat("<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\" ?><sst xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" count=\"{0}\" uniqueCount=\"{0}\">", _sharedStrings.Count);
858      foreach (string t in _sharedStrings.Keys)
859      {
860
861        SharedStringItem ssi = _sharedStrings[t];
862        if (ssi.isRichText)
863        {
864                    cache.Append("<si>");
865                    ConvertUtil.ExcelEncodeString(cache, t);
866                    cache.Append("</si>");
867        }
868        else
869        {
870          if (t.Length>0 && (t[0] == ' ' || t[t.Length-1] == ' ' || t.Contains("  ") || t.Contains("\t")))
871          {
872                        cache.Append("<si><t xml:space=\"preserve\">");
873          }
874          else
875          {
876                        cache.Append("<si><t>");
877          }
878                    ConvertUtil.ExcelEncodeString(cache, ConvertUtil.ExcelEscapeString(t));
879                    cache.Append("</t></si>");
880        }
881                if (cache.Length > 0x600000)
882                {
883                    sw.Write(cache.ToString());
884                    cache = new StringBuilder();           
885                }
886      }
887            cache.Append("</sst>");
888            sw.Write(cache.ToString());
889            sw.Flush();
890            Part.CreateRelationship(UriHelper.GetRelativeUri(WorkbookUri, SharedStringsUri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/sharedStrings");
891    }
892    private void UpdateDefinedNamesXml()
893    {
894      try
895      {
896                XmlNode top = WorkbookXml.SelectSingleNode("//d:definedNames", NameSpaceManager);
897        if (!ExistsNames())
898        {
899          if (top != null) TopNode.RemoveChild(top);
900          return;
901        }
902        else
903        {
904          if (top == null)
905          {
906            CreateNode("d:definedNames");
907            top = WorkbookXml.SelectSingleNode("//d:definedNames", NameSpaceManager);
908          }
909          else
910          {
911            top.RemoveAll();
912          }
913          foreach (ExcelNamedRange name in _names)
914          {
915
916            XmlElement elem = WorkbookXml.CreateElement("definedName", ExcelPackage.schemaMain);
917            top.AppendChild(elem);
918            elem.SetAttribute("name", name.Name);
919            if (name.IsNameHidden) elem.SetAttribute("hidden", "1");
920            if (!string.IsNullOrEmpty(name.NameComment)) elem.SetAttribute("comment", name.NameComment);
921            SetNameElement(name, elem);
922          }
923        }
924        foreach (ExcelWorksheet ws in _worksheets)
925        {
926                    if (!(ws is ExcelChartsheet))
927                    {
928                        foreach (ExcelNamedRange name in ws.Names)
929                        {
930                            XmlElement elem = WorkbookXml.CreateElement("definedName", ExcelPackage.schemaMain);
931                            top.AppendChild(elem);
932                            elem.SetAttribute("name", name.Name);
933                            elem.SetAttribute("localSheetId", name.LocalSheetId.ToString());
934                            if (name.IsNameHidden) elem.SetAttribute("hidden", "1");
935                            if (!string.IsNullOrEmpty(name.NameComment)) elem.SetAttribute("comment", name.NameComment);
936                            SetNameElement(name, elem);
937                        }
938                    }
939        }
940      }
941      catch (Exception ex)
942      {
943        throw new Exception("Internal error updating named ranges ",ex);
944      }
945    }
946
947    private void SetNameElement(ExcelNamedRange name, XmlElement elem)
948    {
949      if (name.IsName)
950      {
951        if (string.IsNullOrEmpty(name.NameFormula))
952        {
953          if ((name.NameValue.GetType().IsPrimitive || name.NameValue is double || name.NameValue is decimal))
954          {
955            elem.InnerText = Convert.ToDouble(name.NameValue, CultureInfo.InvariantCulture).ToString("R15", CultureInfo.InvariantCulture);
956          }
957          else if (name.NameValue is DateTime)
958          {
959            elem.InnerText = ((DateTime)name.NameValue).ToOADate().ToString(CultureInfo.InvariantCulture);
960          }
961          else
962          {
963            elem.InnerText = "\"" + name.NameValue.ToString() + "\"";
964          }                               
965        }
966        else
967        {
968          elem.InnerText = name.NameFormula;
969        }
970      }
971      else
972      {
973                elem.InnerText = name.FullAddressAbsolute;
974      }
975    }
976    /// <summary>
977    /// Is their any names in the workbook or in the sheets.
978    /// </summary>
979    /// <returns>?</returns>
980    private bool ExistsNames()
981    {
982      if (_names.Count == 0)
983      {
984        foreach (ExcelWorksheet ws in Worksheets)
985        {
986                    if (ws is ExcelChartsheet) continue;
987                    if(ws.Names.Count>0)
988          {
989            return true;
990          }
991        }
992      }
993      else
994      {
995        return true;
996      }
997      return false;
998    }       
999    #endregion
1000
1001    #endregion
1002    internal bool ExistsTableName(string Name)
1003    {
1004      foreach (var ws in Worksheets)
1005      {
1006        if(ws.Tables._tableNames.ContainsKey(Name))
1007        {
1008          return true;
1009        }
1010      }
1011      return false;
1012    }
1013    internal bool ExistsPivotTableName(string Name)
1014    {
1015      foreach (var ws in Worksheets)
1016      {
1017        if (ws.PivotTables._pivotTableNames.ContainsKey(Name))
1018        {
1019          return true;
1020        }
1021      }
1022      return false;
1023    }
1024    internal void AddPivotTable(string cacheID, Uri defUri)
1025    {
1026      CreateNode("d:pivotCaches");
1027
1028      XmlElement item = WorkbookXml.CreateElement("pivotCache", ExcelPackage.schemaMain);
1029      item.SetAttribute("cacheId", cacheID);
1030      var rel = Part.CreateRelationship(UriHelper.ResolvePartUri(WorkbookUri, defUri), Packaging.TargetMode.Internal, ExcelPackage.schemaRelationships + "/pivotCacheDefinition");
1031      item.SetAttribute("id", ExcelPackage.schemaRelationships, rel.Id);
1032
1033      var pivotCaches = WorkbookXml.SelectSingleNode("//d:pivotCaches", NameSpaceManager);
1034      pivotCaches.AppendChild(item);
1035    }
1036    internal List<string> _externalReferences = new List<string>();
1037        //internal bool _isCalculated=false;
1038    internal void GetExternalReferences()
1039    {
1040      XmlNodeList nl = WorkbookXml.SelectNodes("//d:externalReferences/d:externalReference", NameSpaceManager);
1041      if (nl != null)
1042      {
1043        foreach (XmlElement elem in nl)
1044        {
1045          string rID = elem.GetAttribute("r:id");
1046          var rel = Part.GetRelationship(rID);
1047          var part = _package.Package.GetPart(UriHelper.ResolvePartUri(rel.SourceUri, rel.TargetUri));
1048          XmlDocument xmlExtRef = new XmlDocument();
1049                    LoadXmlSafe(xmlExtRef, part.GetStream());
1050
1051          XmlElement book=xmlExtRef.SelectSingleNode("//d:externalBook", NameSpaceManager) as XmlElement;
1052          if(book!=null)
1053          {
1054            string rId_ExtRef = book.GetAttribute("r:id");
1055            var rel_extRef = part.GetRelationship(rId_ExtRef);
1056            if (rel_extRef != null)
1057            {
1058              _externalReferences.Add(rel_extRef.TargetUri.OriginalString);
1059            }
1060
1061          }
1062        }
1063      }
1064    }
1065
1066        public void Dispose()
1067        {
1068            _sharedStrings.Clear();
1069            _sharedStringsList.Clear();
1070           
1071            _sharedStrings = null;
1072            _sharedStringsList = null;
1073            _vba = null;
1074            if (_worksheets != null)
1075            {
1076                _worksheets.Dispose();
1077                _worksheets = null;
1078            }
1079            _package = null;
1080            _properties = null;
1081            if (_formulaParser != null)
1082            {
1083                _formulaParser.Dispose();
1084                _formulaParser = null;
1085            }   
1086        }
1087    } // end Workbook
1088}
Note: See TracBrowser for help on using the repository browser.