EPPlus/ExcelPackage.cs

/*******************************************************************************
 * 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.IO;
using System.Xml;
using OfficeOpenXml.Packaging;
using OfficeOpenXml.Utils;

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 XLSX file package.
/// This is the top-level object to access all parts of the document.\
/// </summary>
public sealed class ExcelPackage {
  internal const bool _preserveWhitespace = false;

  /// <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 _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";

  //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";

  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";

  /// <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;

  /// <summary>
  /// Create a new, empty package.
  /// </summary>
  public ExcelPackage() {
    Package = new();
    Workbook = CreateWorkbook();
    _ = Workbook.WorkbookXml;
    // create the relationship to the main part
    Package.CreateRelationship(
        UriHelper.GetRelativeUri(new("/xl", UriKind.Relative), ExcelWorkbook.WorkbookUri),
        TargetMode.Internal,
        _schemaRelationships + "/officeDocument");
  }

  /// <summary>
  /// Create a new instance of the ExcelPackage class based on an existing file.
  /// </summary>
  public ExcelPackage(FileInfo newFile) {
    using var inputStream = newFile.OpenRead();
    Package = new(inputStream);
    Workbook = CreateWorkbook();
  }

  /// <summary>
  /// Create a new instance of the ExcelPackage class based on a stream.
  /// </summary>
  public ExcelPackage(Stream newStream) {
    Package = new(newStream);
    Workbook = CreateWorkbook();
  }

  private ExcelWorkbook CreateWorkbook() {
    ExcelWorkbook workbook = new(this, CreateDefaultNsm());
    workbook.GetExternalReferences();
    workbook.GetDefinedNames();
    return workbook;
  }

  internal ZipPackage Package { get; }

  /// <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; }

  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, _schemaMain);
    ns.AddNamespace("d", _schemaMain);
    ns.AddNamespace("r", _schemaRelationships);
    ns.AddNamespace("c", _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;
  }

  /// <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) {
    ZipPackagePart part = Package.GetPart(uri);
    xmlDoc.Save(part.GetStream(FileMode.Create, FileAccess.Write));
  }

  /// <summary>
  /// Compression option for the package
  /// </summary>
  public CompressionLevel Compression {
    get => Package.Compression;
    set => Package.Compression = value;
  }

  /// <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();
    ZipPackagePart part = Package.GetPart(uri);
    XmlHelper.LoadXmlSafe(xml, part.GetStream());
    return (xml);
  }

  /// <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() {
    var result = new MemoryStream();
    Workbook.Save();
    Package.Save(result);
    return result.ToArray();
  }
}