diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj
index 893e0dd..455e38c 100644
--- a/ClosedXML/ClosedXML.csproj
+++ b/ClosedXML/ClosedXML.csproj
@@ -81,6 +81,7 @@
+
diff --git a/ClosedXML/Excel/SaveOptions.cs b/ClosedXML/Excel/SaveOptions.cs
new file mode 100644
index 0000000..7dcd9a1
--- /dev/null
+++ b/ClosedXML/Excel/SaveOptions.cs
@@ -0,0 +1,26 @@
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
+using System.Threading.Tasks;
+
+namespace ClosedXML.Excel
+{
+ public sealed class SaveOptions
+ {
+ public SaveOptions()
+ {
+#if DEBUG
+ this.ValidatePackage = true;
+#else
+ this.ValidatePackage = false;
+#endif
+
+ this.EvaluateFormulasBeforeSaving = false;
+ this.GenerateCalculationChain = true;
+ }
+ public Boolean ValidatePackage;
+ public Boolean EvaluateFormulasBeforeSaving;
+ public Boolean GenerateCalculationChain;
+ }
+}
diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs
index 9e86a67..370836e 100644
--- a/ClosedXML/Excel/XLWorkbook.cs
+++ b/ClosedXML/Excel/XLWorkbook.cs
@@ -440,16 +440,26 @@
///
public void Save(Boolean validate, Boolean evaluateFormulae = false)
{
+ Save(new SaveOptions
+ {
+ ValidatePackage = validate,
+ EvaluateFormulasBeforeSaving = evaluateFormulae,
+ GenerateCalculationChain = true
+ });
+ }
+
+ public void Save(SaveOptions options)
+ {
checkForWorksheetsPresent();
if (_loadSource == XLLoadSource.New)
- throw new Exception("This is a new file, please use one of the SaveAs methods.");
+ throw new Exception("This is a new file. Please use one of the 'SaveAs' methods.");
if (_loadSource == XLLoadSource.Stream)
{
- CreatePackage(_originalStream, false, _spreadsheetDocumentType, validate, evaluateFormulae);
+ CreatePackage(_originalStream, false, _spreadsheetDocumentType, options);
}
else
- CreatePackage(_originalFile, _spreadsheetDocumentType, validate, evaluateFormulae);
+ CreatePackage(_originalFile, _spreadsheetDocumentType, options);
}
///
@@ -469,6 +479,16 @@
///
public void SaveAs(String file, Boolean validate, Boolean evaluateFormulae = false)
{
+ SaveAs(file, new SaveOptions
+ {
+ ValidatePackage = validate,
+ EvaluateFormulasBeforeSaving = evaluateFormulae,
+ GenerateCalculationChain = true
+ });
+ }
+
+ public void SaveAs(String file, SaveOptions options)
+ {
checkForWorksheetsPresent();
PathHelper.CreateDirectory(Path.GetDirectoryName(file));
if (_loadSource == XLLoadSource.New)
@@ -476,14 +496,14 @@
if (File.Exists(file))
File.Delete(file);
- CreatePackage(file, GetSpreadsheetDocumentType(file), validate, evaluateFormulae);
+ CreatePackage(file, GetSpreadsheetDocumentType(file), options);
}
else if (_loadSource == XLLoadSource.File)
{
if (String.Compare(_originalFile.Trim(), file.Trim(), true) != 0)
File.Copy(_originalFile, file, true);
- CreatePackage(file, GetSpreadsheetDocumentType(file), validate, evaluateFormulae);
+ CreatePackage(file, GetSpreadsheetDocumentType(file), options);
}
else if (_loadSource == XLLoadSource.Stream)
{
@@ -492,8 +512,7 @@
using (var fileStream = File.Create(file))
{
CopyStream(_originalStream, fileStream);
- //fileStream.Position = 0;
- CreatePackage(fileStream, false, _spreadsheetDocumentType, validate, evaluateFormulae);
+ CreatePackage(fileStream, false, _spreadsheetDocumentType, options);
fileStream.Close();
}
}
@@ -542,6 +561,16 @@
///
public void SaveAs(Stream stream, Boolean validate, Boolean evaluateFormulae = false)
{
+ SaveAs(stream, new SaveOptions
+ {
+ ValidatePackage = validate,
+ EvaluateFormulasBeforeSaving = evaluateFormulae,
+ GenerateCalculationChain = true
+ });
+ }
+
+ public void SaveAs(Stream stream, SaveOptions options)
+ {
checkForWorksheetsPresent();
if (_loadSource == XLLoadSource.New)
{
@@ -552,13 +581,13 @@
if (stream.CanRead && stream.CanSeek && stream.CanWrite)
{
// all is fine the package can be created in a direct way
- CreatePackage(stream, true, _spreadsheetDocumentType, validate, evaluateFormulae);
+ CreatePackage(stream, true, _spreadsheetDocumentType, options);
}
else
{
// the harder way
MemoryStream ms = new MemoryStream();
- CreatePackage(ms, true, _spreadsheetDocumentType, validate, evaluateFormulae);
+ CreatePackage(ms, true, _spreadsheetDocumentType, options);
// not really nessesary, because I changed CopyStream too.
// but for better understanding and if somebody in the future
// provide an changed version of CopyStream
@@ -573,7 +602,7 @@
CopyStream(fileStream, stream);
fileStream.Close();
}
- CreatePackage(stream, false, _spreadsheetDocumentType, validate, evaluateFormulae);
+ CreatePackage(stream, false, _spreadsheetDocumentType, options);
}
else if (_loadSource == XLLoadSource.Stream)
{
@@ -581,7 +610,7 @@
if (_originalStream != stream)
CopyStream(_originalStream, stream);
- CreatePackage(stream, false, _spreadsheetDocumentType, validate, evaluateFormulae);
+ CreatePackage(stream, false, _spreadsheetDocumentType, options);
}
}
@@ -866,7 +895,7 @@
public XLWorkbook SetLockWindows(Boolean value) { LockWindows = value; return this; }
internal HexBinaryValue LockPassword { get; set; }
public Boolean IsPasswordProtected { get { return LockPassword != null; } }
-
+
public void Protect(Boolean lockStructure, Boolean lockWindows, String workbookPassword)
{
if (IsPasswordProtected && workbookPassword == null)
@@ -896,7 +925,7 @@
LockStructure = lockStructure;
LockWindows = lockWindows;
}
-
+
public void Protect()
{
Protect(true);
@@ -927,4 +956,4 @@
Protect(false, false, workbookPassword);
}
}
-}
\ No newline at end of file
+}
diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs
index 26d38d4..3c9b741 100644
--- a/ClosedXML/Excel/XLWorkbook_Save.cs
+++ b/ClosedXML/Excel/XLWorkbook_Save.cs
@@ -111,7 +111,7 @@
return true;
}
- private void CreatePackage(String filePath, SpreadsheetDocumentType spreadsheetDocumentType, bool validate, bool evaluateFormulae)
+ private void CreatePackage(String filePath, SpreadsheetDocumentType spreadsheetDocumentType, SaveOptions options)
{
PathHelper.CreateDirectory(Path.GetDirectoryName(filePath));
var package = File.Exists(filePath)
@@ -120,12 +120,12 @@
using (package)
{
- CreateParts(package, evaluateFormulae);
- if (validate) Validate(package);
+ CreateParts(package, options);
+ if (options.ValidatePackage) Validate(package);
}
}
- private void CreatePackage(Stream stream, bool newStream, SpreadsheetDocumentType spreadsheetDocumentType, bool validate, bool evaluateFormulae)
+ private void CreatePackage(Stream stream, bool newStream, SpreadsheetDocumentType spreadsheetDocumentType, SaveOptions options)
{
var package = newStream
? SpreadsheetDocument.Create(stream, spreadsheetDocumentType)
@@ -133,8 +133,8 @@
using (package)
{
- CreateParts(package, evaluateFormulae);
- if (validate) Validate(package);
+ CreateParts(package, options);
+ if (options.ValidatePackage) Validate(package);
}
}
@@ -218,7 +218,7 @@
}
// Adds child parts and generates content of the specified part.
- private void CreateParts(SpreadsheetDocument document, bool evaluateFormulae)
+ private void CreateParts(SpreadsheetDocument document, SaveOptions options)
{
var context = new SaveContext();
@@ -314,7 +314,7 @@
GenerateVmlDrawingPartContent(vmlDrawingPart, worksheet, context);
}
- GenerateWorksheetPartContent(worksheetPart, worksheet, evaluateFormulae, context);
+ GenerateWorksheetPartContent(worksheetPart, worksheet, options.EvaluateFormulasBeforeSaving, context);
if (worksheet.PivotTables.Any())
{
@@ -333,7 +333,10 @@
if (workbookPart.Workbook.PivotCaches != null && !workbookPart.Workbook.PivotCaches.Any())
workbookPart.Workbook.RemoveChild(workbookPart.Workbook.PivotCaches);
- GenerateCalculationChainPartContent(workbookPart, context);
+ if (options.GenerateCalculationChain)
+ GenerateCalculationChainPartContent(workbookPart, context);
+ else
+ DeleteCalculationChainPartContent(workbookPart, context);
if (workbookPart.ThemePart == null)
{
@@ -590,7 +593,7 @@
workbook.WorkbookProtection = null;
}
- #endregion
+ #endregion WorkbookProtection
if (workbook.BookViews == null)
workbook.BookViews = new BookViews();
@@ -1002,11 +1005,16 @@
return run;
}
+ private void DeleteCalculationChainPartContent(WorkbookPart workbookPart, SaveContext context)
+ {
+ if (workbookPart.CalculationChainPart != null)
+ workbookPart.DeletePart(workbookPart.CalculationChainPart);
+ }
+
private void GenerateCalculationChainPartContent(WorkbookPart workbookPart, SaveContext context)
{
- var thisRelId = context.RelIdGenerator.GetNext(RelType.Workbook);
if (workbookPart.CalculationChainPart == null)
- workbookPart.AddNewPart(thisRelId);
+ workbookPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook));
if (workbookPart.CalculationChainPart.CalculationChain == null)
workbookPart.CalculationChainPart.CalculationChain = new CalculationChain();
@@ -1023,25 +1031,33 @@
cellsWithoutFormulas.Add(c.Address.ToStringRelative());
else
{
- if (c.FormulaA1.StartsWith("{"))
+ if (c.HasArrayFormula)
{
- var cc = new CalculationCell
- {
- CellReference = c.Address.ToString(),
- SheetId = worksheet.SheetId
- };
-
- if (c.FormulaReference == null)
- c.FormulaReference = c.AsRange().RangeAddress;
if (c.FormulaReference.FirstAddress.Equals(c.Address))
{
+ var cc = new CalculationCell
+ {
+ CellReference = c.Address.ToString(),
+ SheetId = worksheet.SheetId
+ };
+
+ if (c.FormulaReference == null)
+ c.FormulaReference = c.AsRange().RangeAddress;
+
cc.Array = true;
calculationChain.AppendChild(cc);
- calculationChain.AppendChild(new CalculationCell { CellReference = c.Address.ToString(), InChildChain = true });
- }
- else
- {
- calculationChain.AppendChild(cc);
+
+ foreach (var childCell in worksheet.Range(c.FormulaReference.ToString()).Cells())
+ {
+ calculationChain.AppendChild(
+ new CalculationCell
+ {
+ CellReference = childCell.Address.ToString(),
+ SheetId = worksheet.SheetId,
+ InChildChain = true
+ }
+ );
+ }
}
}
else
@@ -1055,17 +1071,34 @@
}
}
- //var cCellsToRemove = new List();
- var m = from cc in calculationChain.Elements()
- where !(cc.SheetId != null || cc.InChildChain != null)
- && calculationChain.Elements()
- .Where(c1 => c1.SheetId != null)
- .Select(c1 => c1.CellReference.Value)
- .Contains(cc.CellReference.Value)
- || cellsWithoutFormulas.Contains(cc.CellReference.Value)
- select cc;
- //m.ToList().ForEach(cc => cCellsToRemove.Add(cc));
- m.ToList().ForEach(cc => calculationChain.RemoveChild(cc));
+ // This part shouldn't be necessary anymore, but I'm keeping it in the DEBUG configuration until I'm 100% sure.
+
+#if DEBUG
+ var sheetCellReferences = calculationChain.Elements()
+ .Where(cc1 => cc1.SheetId != null)
+ .Select(cc1 => cc1.CellReference.Value)
+ .ToList();
+
+ // Remove orphaned calc chain cells
+ var cellsToRemove = calculationChain.Elements()
+ .Where(cc =>
+ {
+ return cc.SheetId == worksheet.SheetId
+ && cellsWithoutFormulas.Contains(cc.CellReference.Value)
+ || cc.SheetId == null
+ && cc.InChildChain == null
+ && sheetCellReferences.Contains(cc.CellReference.Value);
+ })
+ .ToArray();
+
+ // This shouldn't happen, because the calc chain should be correctly generated
+ System.Diagnostics.Debug.Assert(!cellsToRemove.Any());
+
+ foreach (var cc in cellsToRemove)
+ {
+ calculationChain.RemoveChild(cc);
+ }
+#endif
}
if (!calculationChain.Any())
@@ -4236,16 +4269,17 @@
}
cell.StyleIndex = styleId;
- var formula = xlCell.FormulaA1;
if (xlCell.HasFormula)
{
- if (formula.StartsWith("{"))
+ var formula = xlCell.FormulaA1;
+ if (xlCell.HasArrayFormula)
{
formula = formula.Substring(1, formula.Length - 2);
var f = new CellFormula { FormulaType = CellFormulaValues.Array };
if (xlCell.FormulaReference == null)
xlCell.FormulaReference = xlCell.AsRange().RangeAddress;
+
if (xlCell.FormulaReference.FirstAddress.Equals(xlCell.Address))
{
f.Text = formula;
@@ -4270,7 +4304,6 @@
if (!xlCell.HasFormula || evaluateFormulae)
SetCellValue(xlCell, cell);
-
}
}
xlWorksheet.Internals.CellsCollection.deleted.Remove(distinctRow);
diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx
index 6d14038..28c4b9b 100644
--- a/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx
Binary files differ
diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx
index 8d4e6a2..2b450d6 100644
--- a/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx
+++ b/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx
Binary files differ