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 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 {
  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 instance of the ExcelPackage. Output is accessed through the Stream property.
  /// </summary>
  public ExcelPackage() {
    Package = new();
    Workbook = CreateWorkbook();
    _ = Workbook.WorkbookXml;
    // create the relationship to the main part
    Package.CreateRelationship(
        UriHelper.GetRelativeUri(new("/xl", UriKind.Relative), Workbook.WorkbookUri),
        TargetMode.Internal,
        _schemaRelationships + "/officeDocument");
  }

  /// <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) {
    using var inputStream = newFile.OpenRead();
    Package = new(inputStream);
    Workbook = CreateWorkbook();
  }

  /// <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) {
    Package = new(newStream);
    Workbook = CreateWorkbook();
  }

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

  /// <summary>
  /// Returns a reference to the package
  /// </summary>
  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();
  }
}