/// <summary>
/// Sets a password for the sheet.
/// </summary>
/// <param name="Password"></param>
public void SetPassword(string Password)
{
if (IsProtected == false)
{
IsProtected = true;
}
Password = Password.Trim();
if (Password == "")
{
string passwordPath = "d:sheetProtection";
XmlNode node = TopNode.SelectSingleNode(passwordPath, NameSpaceManager);
if (node != null)
{
TopNode.RemoveChild(node);
//node.OwnerDocument.Attributes.Remove(node as XmlAttribute);
//(node as XmlAttribute).OwnerElement.Attributes.Remove(node as XmlAttribute);
}
return;
}
int hash = EncryptedPackageHandler.CalculatePasswordHash(Password);
SetXmlNodeString(_passwordPath, ((int)hash).ToString("x"));
}
internal async Task <byte[]> GetAsByteArrayAsync(bool save, CancellationToken cancellationToken)
{
if (save)
{
Workbook.Save();
_zipPackage.Close();
if (_stream is MemoryStream && _stream.Length > 0)
{
_stream.Close();
#if Standard21
await _stream.DisposeAsync();
#else
_stream.Dispose();
#endif
_stream = new MemoryStream();
}
_zipPackage.Save(_stream);
}
var byRet = new byte[Stream.Length];
var pos = Stream.Position;
Stream.Seek(0, SeekOrigin.Begin);
await Stream.ReadAsync(byRet, 0, (int)Stream.Length, cancellationToken).ConfigureAwait(false);
//Encrypt Workbook?
if (Encryption.IsEncrypted)
{
var eph = new EncryptedPackageHandler();
var ms = eph.EncryptPackage(byRet, Encryption);
byRet = ms.ToArray();
}
Stream.Seek(pos, SeekOrigin.Begin);
Stream.Close();
return(byRet);
}
/// <summary>
/// Sets a password for the sheet.
/// </summary>
/// <param name="Password"></param>
public void SetPassword(string Password)
{
#if CORECLR
throw new NotSupportedException();
#else
if (IsProtected == false)
{
IsProtected = true;
}
Password = Password.Trim();
if (Password == "")
{
var node = TopNode.SelectSingleNode(_passwordPath, NameSpaceManager);
if (node != null)
{
(node as XmlAttribute).OwnerElement.Attributes.Remove(node as XmlAttribute);
}
return;
}
int hash = EncryptedPackageHandler.CalculatePasswordHash(Password);
SetXmlNodeString(_passwordPath, ((int)hash).ToString("x"));
#endif
}
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 && !CORECLR
EncryptedPackageHandler eph = new EncryptedPackageHandler();
var ms = eph.EncryptPackage(byRet, Encryption);
byRet = ms.ToArray();
#else
throw new NotSupportedException("Encryption is not supported");
#endif
}
Stream.Seek(pos, SeekOrigin.Begin);
Stream.Close();
return(byRet);
}
/// <summary>
/// Copies the Package to the Outstream
/// The package is closed after it has been saved
/// </summary>
/// <param name="OutputStream">The stream to copy the package to</param>
public void SaveAs(Stream OutputStream)
{
File = null;
Save();
if (OutputStream != _stream)
{
if (Encryption.IsEncrypted)
{
#if !MONO && !CORECLR
//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);
#else
throw new NotSupportedException("Encryption is not supported under Mono.");
#endif
}
else
{
CopyStream(_stream, ref OutputStream);
}
}
}
/// <summary>
/// Saves all the components back into the package.
/// This method recursively calls the Save method on all sub-components.
/// We close the package after the save is done.
/// </summary>
public void Save()
{
try
{
Workbook.Save();
if (File == null)
{
_package.Flush();
}
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));
}
}
if (Stream is MemoryStream)
{
_package.Flush();
var fi = new FileStream(File.FullName, FileMode.Create);
//EncryptPackage
if (Encryption.IsEncrypted)
{
byte[] file = ((MemoryStream)Stream).ToArray();
EncryptedPackageHandler eph = new EncryptedPackageHandler();
var ms = eph.EncryptPackage(file, Encryption);
fi.Write(ms.GetBuffer(), 0, (int)ms.Length);
}
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 (ex);
}
else
{
throw (new InvalidOperationException(string.Format("Error saving file {0}", File.FullName), ex));
}
}
}
/// <summary>
/// Sets a password for the workbook. This does not encrypt the workbook.
/// </summary>
/// <param name="Password">The password. </param>
public void SetPassword_Kevin(string Password)
{
if (string.IsNullOrEmpty(Password))
{
string passwordPath = "d:fileSharing";
DeleteNode(passwordPath);
}
else
{
SetXmlNodeString(workbookPasswordPath_Kevin, ((int)EncryptedPackageHandler.CalculatePasswordHash(Password)).ToString("x"));
}
}
/// <summary>
///
/// </summary>
/// <param name="input"></param>
/// <param name="output"></param>
/// <param name="Password"></param>
/// <param name="cancellationToken"></param>
private async Task LoadAsync(Stream input, Stream output, string Password, CancellationToken cancellationToken)
{
ReleaseResources();
if (input.CanSeek && input.Length == 0) // Template is blank, Construct new
{
_stream = output;
await ConstructNewFileAsync(Password, cancellationToken).ConfigureAwait(false);
}
else
{
Stream ms;
_stream = output;
if (Password != null)
{
using (var encrStream = RecyclableMemory.GetStream())
{
await CopyStreamAsync(input, encrStream, cancellationToken).ConfigureAwait(false);
var eph = new EncryptedPackageHandler();
Encryption.Password = Password;
ms = eph.DecryptPackage(encrStream, Encryption);
}
}
else
{
ms = RecyclableMemory.GetStream();
await CopyStreamAsync(input, ms, cancellationToken).ConfigureAwait(false);
}
try
{
_zipPackage = new Packaging.ZipPackage(ms);
}
catch (Exception ex)
{
if (Password == null && await CompoundDocumentFile.IsCompoundDocumentAsync((MemoryStream)_stream, cancellationToken).ConfigureAwait(false))
{
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);
}
throw;
}
finally
{
ms.Dispose();
}
}
//Clear the workbook so that it gets reinitialized next time
this._workbook = null;
}
/// <summary>
/// Sets a password for the workbook. This does not encrypt the workbook.
/// </summary>
/// <param name="Password">The password. </param>
public void SetPassword(string Password)
{
if (string.IsNullOrEmpty(Password))
{
DeleteNode(workbookPasswordPath);
}
else
{
#if CORECLR
throw new NotSupportedException();
#else
SetXmlNodeString(workbookPasswordPath, ((int)EncryptedPackageHandler.CalculatePasswordHash(Password)).ToString("x"));
#endif
}
}
private async Task ConstructNewFileAsync(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)
{
var encrHandler = new EncryptedPackageHandler();
Encryption.IsEncrypted = true;
Encryption.Password = password;
ms = encrHandler.DecryptPackage(File, Encryption);
encrHandler = null;
}
else
{
await WriteFileToStreamAsync(File.FullName, ms);
}
try
{
_package = new Packaging.ZipPackage(ms);
}
catch (Exception ex)
{
if (password == null && await CompoundDocumentFile.IsCompoundDocumentAsync(File).ConfigureAwait(false))
{
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;
}
}
}
else
{
_package = new Packaging.ZipPackage(ms);
CreateBlankWb();
}
}
/// <summary>
/// Writes protectes the workbook with a password.
/// EPPlus uses SHA-512 as hash algorithm with a spin count of 100000.
/// </summary>
/// <param name="userName">The name of the person enforcing the write protection</param>
/// <param name="password">The password. Setting the password to null or empty will remove the read-only mode.</param>
public void SetReadOnly(string userName, string password)
{
UserName = userName;
if (string.IsNullOrEmpty(password?.Trim()))
{
RemovePasswordAttributes();
return;
}
HashAlgorithm = eHashAlgorithm.SHA512;
var s = new byte[16];
var rnd = RandomNumberGenerator.Create();
rnd.GetBytes(s);
SaltValue = s;
SpinCount = 100000;
HashValue = EncryptedPackageHandler.GetPasswordHashSpinAppending(SHA512.Create(), SaltValue, password, SpinCount, 64);
}
private void ConstructNewFile(Stream stream, string password)
{
_stream = stream;
if (File != null)
{
File.Refresh();
}
if (File != null && File.Exists)
{
if (password != null)
{
var encrHandler = new EncryptedPackageHandler();
Encryption.IsEncrypted = true;
Encryption.Password = password;
_stream = encrHandler.DecryptPackage(File, Encryption);
encrHandler = null;
}
else
{
ReadFile();
}
try
{
_package = Package.Open(_stream, FileMode.Open, FileAccess.ReadWrite);
}
catch (Exception ex)
{
if (password == null && EncryptedPackageHandler.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 (ex);
}
}
}
else
{
_package = Package.Open(_stream, FileMode.Create, FileAccess.ReadWrite);
CreateBlankWb();
}
}
/// <summary>
/// Copies the Package to the Outstream
/// The package is closed after it has been saved
/// </summary>
/// <param name="OutputStream">The stream to copy the package to</param>
public void SaveAs(Stream OutputStream)
{
File = null;
Save();
if (Encryption.IsEncrypted)
{
//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);
}
else
{
CopyStream(_stream, ref OutputStream);
}
}
/// <summary>
/// Saves all the components back into the package.
/// This method recursively calls the Save method on all sub-components.
/// We close the package after the save is done.
/// </summary>
public void Save()
{
try
{
Workbook.Save();
if (File == null)
{
if (Encryption.IsEncrypted)
{
#if !MONO
var ms = new MemoryStream();
_package.Save(ms);
byte[] file = ms.ToArray();
EncryptedPackageHandler eph = new EncryptedPackageHandler();
var msEnc = eph.EncryptPackage(file, Encryption);
CopyStream(msEnc, ref _stream);
#endif
#if MONO
throw new NotSupportedException("Encryption is not supported under Mono.");
#endif
}
else
{
_package.Save(_stream);
}
_stream.Flush();
_package.Close();
}
else
{
if (System.IO.File.Exists(File.FullName))
{
try
{
System.IO.File.Delete(File.FullName);
}
catch (Exception ex)
{
throw (new Exception(string.Format("Error overwriting file {0}", File.FullName), ex));
}
}
_package.Save(_stream);
_package.Close();
if (Stream is MemoryStream)
{
var fi = new FileStream(File.FullName, FileMode.Create);
//EncryptPackage
if (Encryption.IsEncrypted)
{
#if !MONO
byte[] file = ((MemoryStream)Stream).ToArray();
EncryptedPackageHandler eph = new EncryptedPackageHandler();
var ms = eph.EncryptPackage(file, Encryption);
fi.Write(ms.GetBuffer(), 0, (int)ms.Length);
#endif
#if MONO
throw new NotSupportedException("Encryption is not supported under Mono.");
#endif
}
else
{
fi.Write(((MemoryStream)Stream).GetBuffer(), 0, (int)Stream.Length);
}
fi.Close();
}
else
{
System.IO.File.WriteAllBytes(File.FullName, GetAsByteArray(false));
}
}
}
catch (Exception ex)
{
if (File == null)
{
throw;
}
else
{
throw (new InvalidOperationException(string.Format("Error saving file {0}", File.FullName), ex));
}
}
}
/// <summary>
/// Saves all the components back into the package.
/// This method recursively calls the Save method on all sub-components.
/// We close the package after the save is done.
/// </summary>
public void Save()
{
if (_stream is MemoryStream && _stream.Length > 0)
{
//Close any open memorystream and "renew" then. This can occure if the package is saved twice.
//The stream is left open on save to enable the user to read the stream-property.
//Non-memorystream streams will leave the closing to the user before saving a second time.
CloseStream();
}
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))
{
System.IO.File.Delete(File.FullName);
}
_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.Dispose();
}
else
{
System.IO.File.WriteAllBytes(File.FullName, GetAsByteArray(false));
}
}
}
/// <summary>
///
/// </summary>
/// <param name="input"></param>
/// <param name="output"></param>
/// <param name="Password"></param>
private void Load(Stream input, Stream output, string Password)
{
//Release some resources:
if (this._package != null)
{
this._package.Flush();
this._package = null;
}
if (this._stream != null)
{
this._stream.Close();
this._stream.Dispose();
this._stream = null;
}
if (Password != null)
{
Stream encrStream = new MemoryStream();
CopyStream(input, ref encrStream);
EncryptedPackageHandler eph=new EncryptedPackageHandler();
Encryption.Password = Password;
this._stream = eph.DecryptPackage((MemoryStream)encrStream, Encryption);
}
else
{
this._stream = output;
CopyStream(input, ref this._stream);
}
try
{
this._package = Package.Open(this._stream, FileMode.Open, FileAccess.ReadWrite);
}
catch (Exception ex)
{
EncryptedPackageHandler eph = new EncryptedPackageHandler();
if (Password == null && EncryptedPackageHandler.IsStorageILockBytes(eph.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 (ex);
}
}
//Clear the workbook so that it gets reinitialized next time
this._workbook = null;
}
/// <summary>
/// Create a new file from a template
/// </summary>
/// <param name="template">An existing xlsx file to use as a template</param>
/// <param name="password">The password to decrypt the package.</param>
/// <returns></returns>
private void CreateFromTemplate(FileInfo template, string password)
{
if (template != null) template.Refresh();
if (template.Exists)
{
_stream = new MemoryStream();
if (password != null)
{
Encryption.IsEncrypted = true;
Encryption.Password = password;
var encrHandler = new EncryptedPackageHandler();
_stream = encrHandler.DecryptPackage(template, Encryption);
encrHandler = null;
//throw (new NotImplementedException("No support for Encrypted packages in this version"));
}
else
{
byte[] b = System.IO.File.ReadAllBytes(template.FullName);
_stream.Write(b, 0, b.Length);
}
try
{
_package = Package.Open(_stream, FileMode.Open, FileAccess.ReadWrite);
}
catch (Exception ex)
{
if (password == null && EncryptedPackageHandler.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 (ex);
}
}
}
else
throw new Exception("Passed invalid TemplatePath to Excel Template");
//return newFile;
}
private void ConstructNewFile(Stream stream, string password)
{
_stream = stream;
if (File != null) File.Refresh();
if (File != null && File.Exists)
{
if (password != null)
{
var encrHandler = new EncryptedPackageHandler();
Encryption.IsEncrypted = true;
Encryption.Password = password;
_stream = encrHandler.DecryptPackage(File, Encryption);
encrHandler = null;
}
else
{
ReadFile();
}
try
{
_package = Package.Open(_stream, FileMode.Open, FileAccess.ReadWrite);
}
catch (Exception ex)
{
if (password == null && EncryptedPackageHandler.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 (ex);
}
}
}
else
{
_package = Package.Open(_stream, FileMode.Create, FileAccess.ReadWrite);
CreateBlankWb();
}
}
internal byte[] GetAsByteArray(bool save)
{
if(save) Workbook.Save();
_package.Flush();
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)
{
EncryptedPackageHandler eph=new EncryptedPackageHandler();
var ms = eph.EncryptPackage(byRet, Encryption);
byRet = ms.ToArray();
}
Stream.Seek(pos, SeekOrigin.Begin);
Stream.Close();
return byRet;
}
/// <summary>
/// Copies the Package to the Outstream
/// Package is closed after it has been saved
/// </summary>
/// <param name="OutputStream">The stream to copy the package to</param>
public void SaveAs(Stream OutputStream)
{
File = null;
Save();
if (Encryption.IsEncrypted)
{
//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);
}
else
{
CopyStream(_stream, ref OutputStream);
}
}
/// <summary>
/// Saves all the components back into the package.
/// This method recursively calls the Save method on all sub-components.
/// We close the package after the save is done.
/// </summary>
public void Save()
{
try
{
Workbook.Save();
if (File == null)
{
_package.Flush();
}
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));
}
}
if (Stream is MemoryStream)
{
_package.Flush();
var fi = new FileStream(File.FullName, FileMode.Create);
//EncryptPackage
if (Encryption.IsEncrypted)
{
byte[] file = ((MemoryStream)Stream).ToArray();
EncryptedPackageHandler eph = new EncryptedPackageHandler();
var ms = eph.EncryptPackage(file, Encryption);
fi.Write(ms.GetBuffer(), 0, (int)ms.Length);
}
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 (ex);
}
else
{
throw (new InvalidOperationException(string.Format("Error saving file {0}", File.FullName), ex));
}
}
}
/// <summary>
/// Saves all the components back into the package.
/// This method recursively calls the Save method on all sub-components.
/// The package is closed after it has ben saved
/// d to encrypt the workbook with.
/// </summary>
/// <returns></returns>
public async Task SaveAsync(CancellationToken cancellationToken = default)
{
try
{
if (_stream is MemoryStream && _stream.Length > 0)
{
//Close any open memorystream and "renew" then. This can occure if the package is saved twice.
//The stream is left open on save to enable the user to read the stream-property.
//Non-memorystream streams will leave the closing to the user before saving a second time.
CloseStream();
}
//Invoke before save delegates
foreach (var action in BeforeSave)
{
action.Invoke();
}
Workbook.Save();
if (File == null)
{
if (Encryption.IsEncrypted)
{
using (var ms = RecyclableMemory.GetStream())
{
_zipPackage.Save(ms);
var file = ms.ToArray();
var eph = new EncryptedPackageHandler();
using (var msEnc = eph.EncryptPackage(file, Encryption))
{
await CopyStreamAsync(msEnc, _stream, cancellationToken).ConfigureAwait(false);
}
}
}
else
{
_zipPackage.Save(_stream);
}
await _stream.FlushAsync(cancellationToken);
_zipPackage.Close();
}
else
{
if (System.IO.File.Exists(File.FullName))
{
try
{
System.IO.File.Delete(File.FullName);
}
catch (Exception ex)
{
throw new Exception($"Error overwriting file {File.FullName}", ex);
}
}
_zipPackage.Save(_stream);
_zipPackage.Close();
if (Stream is MemoryStream stream)
{
#if NETSTANDARD2_1
await using (var fi = new FileStream(File.FullName, FileMode.Create))
#else
using (var fi = new FileStream(File.FullName, FileMode.Create))
#endif
{
//EncryptPackage
if (Encryption.IsEncrypted)
{
var file = stream.ToArray();
var eph = new EncryptedPackageHandler();
using (var ms = eph.EncryptPackage(file, Encryption))
{
await fi.WriteAsync(ms.ToArray(), 0, (int)ms.Length, cancellationToken).ConfigureAwait(false);
}
}
else
{
await fi.WriteAsync(stream.ToArray(), 0, (int)Stream.Length, cancellationToken).ConfigureAwait(false);
}
}
}
else
{
#if NETSTANDARD2_1
await using (var fs = new FileStream(File.FullName, FileMode.Create))
#else
using (var fs = new FileStream(File.FullName, FileMode.Create))
#endif
{
var b = await GetAsByteArrayAsync(false, cancellationToken).ConfigureAwait(false);
await fs.WriteAsync(b, 0, b.Length, cancellationToken).ConfigureAwait(false);
}
}
}
}
catch (Exception ex)
{
if (File == null)
{
throw;
}
throw new InvalidOperationException($"Error saving file {File.FullName}", ex);
}
}
