/*******************************************************************************
* 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
{
///
/// Maps to DotNetZips CompressionLevel enum
///
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,
}
///
/// Represents an Excel 2007/2010 XLSX file package.
/// This is the top-level object to access all parts of the document.
///
/// 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 = "&24&U&\"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;
///
/// More samples can be found at http://epplus.codeplex.com/
///
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 _images = new Dictionary();
#region Properties
///
/// Extention Schema types
///
internal const string schemaXmlExtension = "application/xml";
internal const string schemaRelsExtension = "application/vnd.openxmlformats-package.relationships+xml";
///
/// Main Xml schema name
///
internal const string schemaMain = @"http://schemas.openxmlformats.org/spreadsheetml/2006/main";
///
/// Relationship schema name
///
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;
///
/// Maximum number of columns in a worksheet (16384).
///
public const int MaxColumns = 16384;
///
/// Maximum number of rows in a worksheet (1048576).
///
public const int MaxRows = 1048576;
#endregion
#region ExcelPackage Constructors
///
/// Create a new instance of the ExcelPackage. Output is accessed through the Stream property.
///
public ExcelPackage()
{
Init();
ConstructNewFile(null);
}
///
/// Create a new instance of the ExcelPackage class based on a existing file or creates a new file.
///
/// If newFile exists, it is opened. Otherwise it is created from scratch.
public ExcelPackage(FileInfo newFile)
{
Init();
File = newFile;
ConstructNewFile(null);
}
///
/// Create a new instance of the ExcelPackage class based on a existing file or creates a new file.
///
/// If newFile exists, it is opened. Otherwise it is created from scratch.
/// Password for an encrypted package
public ExcelPackage(FileInfo newFile, string password)
{
Init();
File = newFile;
ConstructNewFile(password);
}
///
/// 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
///
/// The name of the Excel file to be created
/// The name of the Excel template to use as the basis of the new Excel file
public ExcelPackage(FileInfo newFile, FileInfo template)
{
Init();
File = newFile;
CreateFromTemplate(template, null);
}
///
/// 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
///
/// The name of the Excel file to be created
/// The name of the Excel template to use as the basis of the new Excel file
/// Password to decrypted the template
public ExcelPackage(FileInfo newFile, FileInfo template, string password)
{
Init();
File = newFile;
CreateFromTemplate(template, password);
}
///
/// Create a new instance of the ExcelPackage class based on a existing template.
///
/// The name of the Excel template to use as the basis of the new Excel file
/// if true use a stream. If false create a file in the temp dir with a random name
public ExcelPackage(FileInfo template, bool useStream)
{
Init();
CreateFromTemplate(template, null);
if (useStream == false)
{
File = new FileInfo(Path.GetTempPath() + Guid.NewGuid().ToString() + ".xlsx");
}
}
///
/// Create a new instance of the ExcelPackage class based on a existing template.
///
/// The name of the Excel template to use as the basis of the new Excel file
/// if true use a stream. If false create a file in the temp dir with a random name
/// Password to decrypted the template
public ExcelPackage(FileInfo template, bool useStream, string password)
{
Init();
CreateFromTemplate(template, password);
if (useStream == false)
{
File = new FileInfo(Path.GetTempPath() + Guid.NewGuid().ToString() + ".xlsx");
}
}
///
/// Create a new instance of the ExcelPackage class based on a stream
///
/// The stream object can be empty or contain a package. The stream must be Read/Write
public ExcelPackage(Stream newStream)
{
Init();
if (newStream.Length == 0)
{
_stream = newStream;
_isExternalStream = true;
ConstructNewFile(null);
}
else
{
Load(newStream);
}
}
///
/// Create a new instance of the ExcelPackage class based on a stream
///
/// The stream object can be empty or contain a package. The stream must be Read/Write
/// The password to decrypt the document
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();
}
}
///
/// Create a new instance of the ExcelPackage class based on a stream
///
/// The output stream. Must be an empty read/write stream.
/// This stream is copied to the output stream at load
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);
}
///
/// Create a new instance of the ExcelPackage class based on a stream
///
/// The output stream. Must be an empty read/write stream.
/// This stream is copied to the output stream at load
/// Password to decrypted the template
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;
}
///
/// Init values here
///
private void Init()
{
DoAdjustDrawings = true;
}
///
/// Create a new file from a template
///
/// An existing xlsx file to use as a template
/// The password to decrypt the package.
///
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");
}
///
/// Returns a reference to the package
///
public Packaging.ZipPackage Package { get { return (_package); } }
ExcelEncryption _encryption=null;
///
/// Information how and if the package is encrypted
///
public ExcelEncryption Encryption
{
get
{
if (_encryption == null)
{
_encryption = new ExcelEncryption();
}
return _encryption;
}
}
///
/// Returns a reference to the workbook component within the package.
/// All worksheets and cells can be accessed through the workbook.
///
public ExcelWorkbook Workbook
{
get
{
if (_workbook == null)
{
var nsm = CreateDefaultNSM();
_workbook = new ExcelWorkbook(this, nsm);
_workbook.GetExternalReferences();
_workbook.GetDefinedNames();
}
return (_workbook);
}
}
///
/// Automaticlly adjust drawing size when column width/row height are adjusted, depending on the drawings editBy property.
/// Default True
///
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
///
/// Saves the XmlDocument into the package at the specified Uri.
///
/// The Uri of the component
/// The XmlDocument to save
internal void SavePart(Uri uri, XmlDocument xmlDoc)
{
Packaging.ZipPackagePart part = _package.GetPart(uri);
xmlDoc.Save(part.GetStream(FileMode.Create, FileAccess.Write));
}
///
/// Saves the XmlDocument into the package at the specified Uri.
///
/// The Uri of the component
/// The XmlDocument to save
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
///
/// Closes the package.
///
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
///
/// 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.
///
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));
}
}
}
///
/// 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.
///
/// This parameter overrides the Workbook.Encryption.Password.
public void Save(string password)
{
Encryption.Password = password;
Save();
}
///
/// Saves the workbook to a new file
/// The package is closed after it has been saved
///
/// The file location
public void SaveAs(FileInfo file)
{
File = file;
Save();
}
///
/// Saves the workbook to a new file
/// The package is closed after it has been saved
///
/// The file
/// The password to encrypt the workbook with.
/// This parameter overrides the Encryption.Password.
public void SaveAs(FileInfo file, string password)
{
File = file;
Encryption.Password = password;
Save();
}
///
/// Copies the Package to the Outstream
/// The package is closed after it has been saved
///
/// The stream to copy the package to
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);
}
}
}
///
/// Copies the Package to the Outstream
/// The package is closed after it has been saved
///
/// The stream to copy the package to
/// The password to encrypt the workbook with.
/// This parameter overrides the Encryption.Password.
public void SaveAs(Stream OutputStream, string password)
{
Encryption.Password = password;
SaveAs(OutputStream);
}
FileInfo _file = null;
///
/// The output file. Null if no file is used
///
public FileInfo File
{
get
{
return _file;
}
set
{
_file = value;
}
}
///
/// The output stream. This stream is the not the encrypted package.
/// To get the encrypted package use the SaveAs(stream) method.
///
public Stream Stream
{
get
{
return _stream;
}
}
#endregion
///
/// Compression option for the package
///
public CompressionLevel Compression
{
get
{
return Package.Compression;
}
set
{
Package.Compression = value;
}
}
#region GetXmlFromUri
///
/// Get the XmlDocument from an URI
///
/// The Uri to the part
/// The XmlDocument
internal XmlDocument GetXmlFromUri(Uri uri)
{
XmlDocument xml = new XmlDocument();
Packaging.ZipPackagePart part = _package.GetPart(uri);
XmlHelper.LoadXmlSafe(xml, part.GetStream());
return (xml);
}
#endregion
///
/// Saves and returns the Excel files as a bytearray.
/// Note that the package is closed upon save
///
///
/// Example how to return a document from a Webserver...
///
/// 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);
///
///
///
public byte[] GetAsByteArray()
{
return GetAsByteArray(true);
}
///
/// Saves and returns the Excel files as a bytearray
/// Note that the package is closed upon save
///
///
/// Example how to return a document from a Webserver...
///
/// 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);
///
///
/// The password to encrypt the workbook with.
/// This parameter overrides the Encryption.Password.
///
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;
}
///
/// Loads the specified package data from a stream.
///
/// The input.
public void Load(Stream input)
{
Load(input, new MemoryStream(), null);
}
///
/// Loads the specified package data from a stream.
///
/// The input.
/// The password to decrypt the document
public void Load(Stream input, string Password)
{
Load(input, new MemoryStream(), Password);
}
///
///
///
///
///
///
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();
///
/// Copies the input stream to the output stream.
///
/// The input stream.
/// The output stream.
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();
}
}
}
}