diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs index 0f4f606..f824b23 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs @@ -12,5 +12,7 @@ String ColumnLetter { get; } Boolean FixedRow { get; } Boolean FixedColumn { get; } + String ToStringRelative(); + String ToStringFixed(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs index 18aea9d..827257f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeAddress.cs @@ -28,5 +28,8 @@ /// true if this instance is invalid; otherwise, false. /// Boolean IsInvalid { get; set; } + + String ToStringRelative(); + String ToStringFixed(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index c17d9b5..6e8fee0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -196,6 +196,8 @@ IXLDataValidation DataValidation { get; } + String ToStringRelative(); + String ToStringFixed(); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs index 75eae2b..bd5f2f4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -81,6 +81,15 @@ return firstAddress.ToString() + ":" + lastAddress.ToString(); } + public String ToStringRelative() + { + return firstAddress.ToStringRelative() + ":" + lastAddress.ToStringRelative(); + } + public String ToStringFixed() + { + return firstAddress.ToStringFixed() + ":" + lastAddress.ToStringFixed(); + } + public override bool Equals(object obj) { var other = (XLRangeAddress)obj; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index e5d28c5..b251ff1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -768,6 +768,16 @@ return String.Format("'{0}'!{1}:{2}", Worksheet.Name, RangeAddress.FirstAddress.ToString(), RangeAddress.LastAddress.ToString()); } + public string ToStringRelative() + { + return String.Format("'{0}'!{1}:{2}", Worksheet.Name, RangeAddress.FirstAddress.ToStringRelative(), RangeAddress.LastAddress.ToStringRelative()); + } + + public string ToStringFixed() + { + return String.Format("'{0}'!{1}:{2}", Worksheet.Name, RangeAddress.FirstAddress.ToStringFixed(), RangeAddress.LastAddress.ToStringFixed()); + } + public String FormulaA1 { set @@ -806,11 +816,11 @@ if (namedRanges.Where(nr => nr.Name.ToLower() == rangeName.ToLower()).Any()) { var namedRange = namedRanges.Where(nr => nr.Name.ToLower() == rangeName.ToLower()).Single(); - namedRange.Add(this.AsRange()); + namedRange.Add(this.ToStringFixed()); } else { - namedRanges.Add(rangeName, this.AsRange(), comment); + namedRanges.Add(rangeName, this.ToStringFixed(), comment); } return AsRange(); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs index c774429..bd4e165 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs @@ -339,6 +339,17 @@ { return ColumnLetter + rowNumber.ToStringLookup(); } + + public string ToStringRelative() + { + return ColumnLetter + rowNumber.ToStringLookup(); + } + + public string ToStringFixed() + { + return "$" + ColumnLetter + "$" + rowNumber.ToStringLookup(); + } + #endregion #region Operator Overloads diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index fbfaab5..d4ba608 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -106,6 +106,11 @@ /// public IXLNamedRanges NamedRanges { get; private set; } + public IXLNamedRange NamedRange(String rangeName) + { + return NamedRanges.NamedRange(rangeName); + } + /// /// Gets the file name of the workbook. /// diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 1ed2f6f..793a197 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -2568,46 +2568,44 @@ workbookPart.CalculationChainPart.CalculationChain = new CalculationChain(); CalculationChain calculationChain = workbookPart.CalculationChainPart.CalculationChain; + calculationChain.RemoveAllChildren(); + //var calculationCells = new Dictionary>(); + //foreach(var calculationCell in calculationChain.Elements().Where(cc => cc.CellReference != null)) + //{ + // String cellReference = calculationCell.CellReference.Value; + // if (!calculationCells.ContainsKey(cellReference)) + // calculationCells.Add(cellReference, new List()); + // calculationCell. + // calculationCells[cellReference].Add(calculationCell); + //} + foreach (var worksheet in Worksheets.Cast()) { - foreach (var c in worksheet.Internals.CellsCollection.Values.Where(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) + var cellsWithoutFormulas = new HashSet(); + foreach (var c in worksheet.Internals.CellsCollection.Values) { - var calculationCells = calculationChain.Elements().Where( - cc => cc.CellReference != null && cc.CellReference == c.Address.ToString()).Select(cc => cc).ToList(); - - calculationCells.ForEach(cc => calculationChain.RemoveChild(cc)); - - - if (c.FormulaA1.StartsWith("{")) + if (StringExtensions.IsNullOrWhiteSpace(c.FormulaA1)) { - calculationChain.Append(new CalculationCell() { CellReference = c.Address.ToString(), SheetId = worksheet.SheetId, Array = true }); - calculationChain.Append(new CalculationCell() { CellReference = c.Address.ToString(), InChildChain = true }); + cellsWithoutFormulas.Add(c.Address.ToStringRelative()); } else { - calculationChain.Append(new CalculationCell() { CellReference = c.Address.ToString(), SheetId = worksheet.SheetId }); + //var calculationCells = calculationChain.Elements().Where( + // cc => cc.CellReference != null && cc.CellReference == c.Address.ToString()).Select(cc => cc).ToList(); + + //calculationCells.ForEach(cc => calculationChain.RemoveChild(cc)); + + + if (c.FormulaA1.StartsWith("{")) + { + calculationChain.Append(new CalculationCell() { CellReference = c.Address.ToString(), SheetId = worksheet.SheetId, Array = true }); + calculationChain.Append(new CalculationCell() { CellReference = c.Address.ToString(), InChildChain = true }); + } + else + { + calculationChain.Append(new CalculationCell() { CellReference = c.Address.ToString(), SheetId = worksheet.SheetId }); + } } - - - //Boolean addNew = true; - //if (calculationCells.FirstOrDefault() != null) - //{ - // calculationCells.Where(cc => cc.SheetId == null).Select(cc => cc).ForEach(cc => calculationChain.RemoveChild(cc)); - // var cCell = calculationCells.Where(cc => cc.SheetId != null).FirstOrDefault(cc => cc.SheetId == worksheet.SheetId); - // if (cCell != null) - // { - // cCell.SheetId = worksheet.SheetId; - // cCell.Array = null; - // addNew = false; - // } - //} - - //if (addNew) - //{ - // CalculationCell calculationCell = new CalculationCell() { CellReference = c.Address.ToString(), SheetId = worksheet.SheetId }; - // calculationChain.Append(calculationCell); - //} - } //var cCellsToRemove = new List(); @@ -2617,7 +2615,7 @@ .Where(c1 => c1.SheetId != null) .Select(c1 => c1.CellReference.Value) .Contains(cc.CellReference.Value) - || worksheet.Internals.CellsCollection.Where(kp=>kp.Key.ToString() == cc.CellReference.Value && StringExtensions.IsNullOrWhiteSpace(kp.Value.FormulaA1)).Any() + || cellsWithoutFormulas.Contains(cc.CellReference.Value) select cc; //m.ToList().ForEach(cc => cCellsToRemove.Add(cc)); m.ToList().ForEach(cc=>calculationChain.RemoveChild(cc)); diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 06e5252..b8f84fd 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -13,14 +13,24 @@ class Program { - static void Main(string[] args) + static void xMain(string[] args) { - var fileName = "Issue_6373"; + var fileName = "Issue_6313"; //var fileName = "Blank"; //var fileName = "Sandbox"; var wb = new XLWorkbook(String.Format(@"c:\Excel Files\ForTesting\{0}.xlsx", fileName)); - var ws = wb.Worksheet(2); - ws.Row(75).InsertRowsBelow(1); + + IXLWorksheet sheet = wb.Worksheets.Add("Query Results"); + + // Add the table to the Excel sheet + var table = sheet.Cell(1, 1).InsertTable(new List()); + // run autofit on all the columns + sheet.Columns().AdjustToContents(); + // Freeze the top row and the first five columns + sheet.SheetView.Freeze(1, 5); + // Mark the first row as BOLD + table.HeadersRow().Style.Font.Bold = true; + //var wb = new XLWorkbook(); //var ws = wb.Worksheets.Add("Shifting Formulas"); //ws.Cell("B2").Value = 5; @@ -120,7 +130,7 @@ wb.Save(); } - static void xMain(string[] args) + static void Main(string[] args) { FillStyles(); List runningSave = new List(); @@ -134,9 +144,9 @@ foreach (var i in Enumerable.Range(1, 1)) { var ws = wb.Worksheets.Add("Sheet" + i); - foreach (var ro in Enumerable.Range(1, 200)) + foreach (var ro in Enumerable.Range(1, 1000)) { - foreach (var co in Enumerable.Range(1, 200)) + foreach (var co in Enumerable.Range(1, 100)) { ws.Cell(ro, co).Style = GetRandomStyle(); //if (rnd.Next(1, 5) == 1) @@ -202,7 +212,7 @@ Console.WriteLine("Avg Save time: {0}", runningSave.Average()); Console.WriteLine("Avg Load time: {0}", runningLoad.Average()); Console.WriteLine("Avg Save Back time: {0}", runningSavedBack.Average()); - //Console.ReadKey(); + Console.ReadKey(); } private static IXLStyle style1;