diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 261e775..95700b7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -18,37 +18,37 @@ private static readonly Regex A1Regex = new Regex( @"(?<=\W)(\$?[a-zA-Z]{1,3}\$?\d{1,7})(?=\W)" // A1 - + @"|(?<=\W)(\d{1,7}:\d{1,7})(?=\W)" // 1:1 - + @"|(?<=\W)([a-zA-Z]{1,3}:[a-zA-Z]{1,3})(?=\W)"); // A:A + + @"|(?<=\W)(\$?\d{1,7}:\$?\d{1,7})(?=\W)" // 1:1 + + @"|(?<=\W)(\$?[a-zA-Z]{1,3}:\$?[a-zA-Z]{1,3})(?=\W)"); // A:A - private static readonly Regex A1SimpleRegex = new Regex( - @"(?<=\W)" // Start with non word - + @"(" // Start Group to pick - + @"(" // Start Sheet Name, optional + public static readonly Regex A1SimpleRegex = new Regex( + // @"(?<=\W)" // Start with non word + @"(?" // Start Group to pick + + @"(?" // Start Sheet Name, optional + @"(" - + @"\'[^\[\]\*/\\\?:]+\'" // Sheet name with special characters, surrounding apostrophes are required + + @"\'([^\[\]\*/\\\?:\']+|\'\')\'" // Sheet name with special characters, surrounding apostrophes are required + @"|" + @"\'?\w+\'?" // Sheet name with letters and numbers, surrounding apostrophes are optional + @")" + @"!)?" // End Sheet Name, optional - + @"(" // Start range + + @"(?" // Start range + @"\$?[a-zA-Z]{1,3}\$?\d{1,7}" // A1 Address 1 - + @"(:\$?[a-zA-Z]{1,3}\$?\d{1,7})?" // A1 Address 2, optional + + @"(?:\$?[a-zA-Z]{1,3}\$?\d{1,7})?" // A1 Address 2, optional + @"|" - + @"(\d{1,7}:\d{1,7})" // 1:1 + + @"(?\$?\d{1,7}:\$?\d{1,7})" // 1:1 + @"|" - + @"([a-zA-Z]{1,3}:[a-zA-Z]{1,3})" // A:A + + @"(?\$?[a-zA-Z]{1,3}:\$?[a-zA-Z]{1,3})" // A:A + @")" // End Range + @")" // End Group to pick - + @"(?=\W)" // End with non word + //+ @"(?=\W)" // End with non word ); private static readonly Regex A1RowRegex = new Regex( - @"(\d{1,7}:\d{1,7})" // 1:1 + @"(\$?\d{1,7}:\$?\d{1,7})" // 1:1 ); private static readonly Regex A1ColumnRegex = new Regex( - @"([a-zA-Z]{1,3}:[a-zA-Z]{1,3})" // A:A + @"(\$?[a-zA-Z]{1,3}:\$?[a-zA-Z]{1,3})" // A:A ); private static readonly Regex R1C1Regex = new Regex( @@ -1404,12 +1404,16 @@ return this; } - internal void ShiftFormulaRows(XLRange shiftedRange, int rowsShifted) { - if (StringExtensions.IsNullOrWhiteSpace(FormulaA1)) return; + _formulaA1 = ShiftFormulaRows(FormulaA1, Worksheet, shiftedRange, rowsShifted); + } - string value = ">" + _formulaA1 + "<"; + internal static String ShiftFormulaRows(String formulaA1, XLWorksheet worksheetInAction, XLRange shiftedRange, int rowsShifted) + { + if (StringExtensions.IsNullOrWhiteSpace(formulaA1)) return String.Empty; + + string value = formulaA1;// ">" + formulaA1 + "<"; var regex = A1SimpleRegex; @@ -1423,7 +1427,7 @@ int matchIndex = match.Index; if (value.Substring(0, matchIndex).CharCount('"') % 2 == 0) { -// Check that the match is not between quotes + // Check that the match is not between quotes sb.Append(value.Substring(lastIndex, matchIndex - lastIndex)); string sheetName; bool useSheetName = false; @@ -1435,14 +1439,14 @@ useSheetName = true; } else - sheetName = _worksheet.Name; + sheetName = worksheetInAction.Name; - if (String.Compare(sheetName, shiftedWsName, true)==0) + if (String.Compare(sheetName, shiftedWsName, true) == 0) { string rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); if (!A1ColumnRegex.IsMatch(rangeAddress)) { - var matchRange = _worksheet.Workbook.Worksheet(sheetName).Range(rangeAddress); + var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(rangeAddress); if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= matchRange.RangeAddress.LastAddress.RowNumber && @@ -1488,7 +1492,7 @@ { sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. FirstAddress.RowNumber + rowsShifted, @@ -1498,7 +1502,7 @@ FirstAddress.FixedRow, matchRange.RangeAddress. FirstAddress.FixedColumn), - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. LastAddress.RowNumber + rowsShifted, @@ -1512,7 +1516,7 @@ else { sb.Append(String.Format("{0}:{1}", - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. FirstAddress.RowNumber + rowsShifted, @@ -1522,7 +1526,7 @@ FirstAddress.FixedRow, matchRange.RangeAddress. FirstAddress.FixedColumn), - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. LastAddress.RowNumber + rowsShifted, @@ -1540,7 +1544,7 @@ { sb.Append(String.Format("'{0}'!{1}", sheetName, - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. FirstAddress.RowNumber + rowsShifted, @@ -1554,7 +1558,7 @@ else { sb.Append(String.Format("{0}", - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. FirstAddress.RowNumber + rowsShifted, @@ -1574,7 +1578,7 @@ sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, matchRange.RangeAddress.FirstAddress, - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. LastAddress.RowNumber + rowsShifted, @@ -1589,7 +1593,7 @@ { sb.Append(String.Format("{0}:{1}", matchRange.RangeAddress.FirstAddress, - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. LastAddress.RowNumber + rowsShifted, @@ -1619,15 +1623,22 @@ if (lastIndex < value.Length) sb.Append(value.Substring(lastIndex)); - string retVal = sb.ToString(); - _formulaA1 = retVal.Substring(1, retVal.Length - 2); + return sb.ToString(); + + //string retVal = sb.ToString(); + //return retVal.Substring(1, retVal.Length - 2); } internal void ShiftFormulaColumns(XLRange shiftedRange, int columnsShifted) { - if (StringExtensions.IsNullOrWhiteSpace(FormulaA1)) return; + _formulaA1 = ShiftFormulaColumns(FormulaA1, Worksheet, shiftedRange, columnsShifted); + } - string value = ">" + _formulaA1 + "<"; + internal static String ShiftFormulaColumns(String formulaA1, XLWorksheet worksheetInAction, XLRange shiftedRange, int columnsShifted) + { + if (StringExtensions.IsNullOrWhiteSpace(formulaA1)) return String.Empty; + + string value = formulaA1; // ">" + formulaA1 + "<"; var regex = A1SimpleRegex; @@ -1652,14 +1663,14 @@ useSheetName = true; } else - sheetName = _worksheet.Name; + sheetName = worksheetInAction.Name; if (String.Compare(sheetName, shiftedRange.Worksheet.Name, true) == 0) { string rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); if (!A1RowRegex.IsMatch(rangeAddress)) { - var matchRange = _worksheet.Workbook.Worksheet(sheetName).Range(rangeAddress); + var matchRange = worksheetInAction.Workbook.Worksheet(sheetName).Range(rangeAddress); if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.LastAddress.ColumnNumber && @@ -1719,7 +1730,7 @@ { sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. FirstAddress.RowNumber, matchRange.RangeAddress. @@ -1729,7 +1740,7 @@ FirstAddress.FixedRow, matchRange.RangeAddress. FirstAddress.FixedColumn), - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. LastAddress.RowNumber, matchRange.RangeAddress. @@ -1743,7 +1754,7 @@ else { sb.Append(String.Format("{0}:{1}", - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. FirstAddress.RowNumber, matchRange.RangeAddress. @@ -1753,7 +1764,7 @@ FirstAddress.FixedRow, matchRange.RangeAddress. FirstAddress.FixedColumn), - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. LastAddress.RowNumber, matchRange.RangeAddress. @@ -1771,7 +1782,7 @@ { sb.Append(String.Format("'{0}'!{1}", sheetName, - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. FirstAddress.RowNumber, matchRange.RangeAddress. @@ -1785,7 +1796,7 @@ else { sb.Append(String.Format("{0}", - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. FirstAddress.RowNumber, matchRange.RangeAddress. @@ -1805,7 +1816,7 @@ sb.Append(String.Format("'{0}'!{1}:{2}", sheetName, matchRange.RangeAddress.FirstAddress, - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. LastAddress.RowNumber, matchRange.RangeAddress. @@ -1820,7 +1831,7 @@ { sb.Append(String.Format("{0}:{1}", matchRange.RangeAddress.FirstAddress, - new XLAddress(_worksheet, + new XLAddress(worksheetInAction, matchRange.RangeAddress. LastAddress.RowNumber, matchRange.RangeAddress. @@ -1850,9 +1861,10 @@ if (lastIndex < value.Length) sb.Append(value.Substring(lastIndex)); - string retVal = sb.ToString(); + return sb.ToString(); - _formulaA1 = retVal.Substring(1, retVal.Length - 2); + //string retVal = sb.ToString(); + //return retVal.Substring(1, retVal.Length - 2); } // -- diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs index c12d2a4..52a92d6 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs @@ -13,7 +13,7 @@ @"^('?(?[^'!]+)'?!(?.+))|((?[^\[]+)\[(?[^\]]+)\])$", RegexOptions.Compiled | RegexOptions.CultureInvariant | RegexOptions.ExplicitCapture ); - private readonly List _rangeList = new List(); + private List _rangeList = new List(); private readonly XLNamedRanges _namedRanges; public XLNamedRange(XLNamedRanges namedRanges , String rangeName, String range, String comment = null) { @@ -38,7 +38,7 @@ { var ranges = new XLRanges(); foreach (var rangeToAdd in - from rangeAddress in _rangeList.SelectMany(c=>c.Split(',')) + from rangeAddress in _rangeList.SelectMany(c=>c.Split(',')).Where(s=>s[0] != '"') let match = _namedRangeReferenceRegex.Match(rangeAddress) select match.Groups["Sheet"].Success @@ -105,5 +105,11 @@ } public String RefersTo { get { return ToString(); } } + + internal List RangeList + { + get { return _rangeList; } + set { _rangeList = value; } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs index bfba263..a3e8296 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -31,23 +31,41 @@ ? rangeAddress.Substring(rangeAddress.IndexOf("!") + 1) : rangeAddress; - XLAddress firstAddress; - XLAddress lastAddress; + string firstPart; + string secondPart; if (addressToUse.Contains(':')) { var arrRange = addressToUse.Split(':'); - string firstPart = arrRange[0]; - string secondPart = arrRange[1]; - firstAddress = XLAddress.Create(worksheet, firstPart); - lastAddress = XLAddress.Create(worksheet, secondPart); + firstPart = arrRange[0]; + secondPart = arrRange[1]; } else { - firstAddress = XLAddress.Create(worksheet, addressToUse); - lastAddress = XLAddress.Create(worksheet, addressToUse); + firstPart = addressToUse; + secondPart = addressToUse; } - FirstAddress = firstAddress; - LastAddress = lastAddress; + + if (ExcelHelper.IsValidA1Address(firstPart)) + { + FirstAddress = XLAddress.Create(worksheet, firstPart); + LastAddress = XLAddress.Create(worksheet, secondPart); + } + else + { + firstPart = firstPart.Replace("$", String.Empty); + secondPart = secondPart.Replace("$", String.Empty); + if (char.IsDigit(firstPart[0])) + { + FirstAddress = XLAddress.Create(worksheet, "A" + firstPart); + LastAddress = XLAddress.Create(worksheet, ExcelHelper.MaxColumnLetter + secondPart); + } + else + { + FirstAddress = XLAddress.Create(worksheet, firstPart + "1"); + LastAddress = XLAddress.Create(worksheet, secondPart + ExcelHelper.MaxRowNumber.ToStringLookup()); + } + } + Worksheet = worksheet; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index ddc5be8..66c7071 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -4,6 +4,8 @@ namespace ClosedXML.Excel { + using System.Text.RegularExpressions; + internal delegate void RangeShiftedRowsDelegate(XLRange range, Int32 rowsShifted); internal delegate void RangeShiftedColumnsDelegate(XLRange range, Int32 columnsShifted); @@ -1069,6 +1071,9 @@ newMerge.Add(rngMerged); } Internals.MergedRanges = newMerge; + + Workbook.Worksheets.ForEach(ws => MoveNamedRangesColumns(range, columnsShifted, ws.NamedRanges)); + MoveNamedRangesColumns(range, columnsShifted, Workbook.NamedRanges); } private void XLWorksheetRangeShiftedRows(XLRange range, int rowsShifted) @@ -1094,6 +1099,32 @@ newMerge.Add(rngMerged); } Internals.MergedRanges = newMerge; + + Workbook.Worksheets.ForEach(ws=> MoveNamedRangesRows(range, rowsShifted, ws.NamedRanges)); + MoveNamedRangesRows(range, rowsShifted, Workbook.NamedRanges); + + } + + private void MoveNamedRangesRows(XLRange range, int rowsShifted, IXLNamedRanges namedRanges) + { + foreach (XLNamedRange nr in namedRanges) + { + var newRangeList = + nr.RangeList.Select(r => XLCell.ShiftFormulaRows(r, this, range, rowsShifted)).Where( + newReference => newReference.Length > 0).ToList(); + nr.RangeList = newRangeList; + } + } + + private void MoveNamedRangesColumns(XLRange range, int columnsShifted, IXLNamedRanges namedRanges) + { + foreach (XLNamedRange nr in namedRanges) + { + var newRangeList = + nr.RangeList.Select(r => XLCell.ShiftFormulaColumns(r, this, range, columnsShifted)).Where( + newReference => newReference.Length > 0).ToList(); + nr.RangeList = newRangeList; + } } public void NotifyRangeShiftedRows(XLRange range, Int32 rowsShifted) diff --git a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs index afd8d1f..452f134 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs +++ b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs @@ -6,6 +6,7 @@ namespace ClosedXML.Excel { using System.Linq; + using System.Text.RegularExpressions; /// /// Common methods @@ -16,6 +17,7 @@ public const int MinColumnNumber = 1; public const int MaxRowNumber = 1048576; public const int MaxColumnNumber = 16384; + public const String MaxColumnLetter = "XFD"; private const Int32 TwoT26 = 26 * 26; internal static readonly NumberFormatInfo NumberFormatForParse = CultureInfo.InvariantCulture.NumberFormat; @@ -111,24 +113,47 @@ && IsValidColumn(address.Substring(0, rowPos)); } + public static readonly Regex A1SimpleRegex = new Regex( + @"\A" + + @"(?" // Start Group to pick + + @"(?" // Start Sheet Name, optional + + @"(" + + @"\'([^\[\]\*/\\\?:\']+|\'\')\'" // Sheet name with special characters, surrounding apostrophes are required + + @"|" + + @"\'?\w+\'?" // Sheet name with letters and numbers, surrounding apostrophes are optional + + @")" + + @"!)?" // End Sheet Name, optional + + @"(?" // Start range + + @"\$?[a-zA-Z]{1,3}\$?\d{1,7}" // A1 Address 1 + + @"(?:\$?[a-zA-Z]{1,3}\$?\d{1,7})?" // A1 Address 2, optional + + @"|" + + @"(?\$?\d{1,7}:\$?\d{1,7})" // 1:1 + + @"|" + + @"(?\$?[a-zA-Z]{1,3}:\$?[a-zA-Z]{1,3})" // A:A + + @")" // End Range + + @")" // End Group to pick + + @"\Z" + ); + public static Boolean IsValidRangeAddress(String rangeAddress) { - if (StringExtensions.IsNullOrWhiteSpace(rangeAddress)) - return false; - - string addressToUse = rangeAddress.Contains("!") - ? rangeAddress.Substring(rangeAddress.IndexOf("!") + 1) - : rangeAddress; + return A1SimpleRegex.IsMatch(rangeAddress); + //if (StringExtensions.IsNullOrWhiteSpace(rangeAddress)) + // return false; - if (addressToUse.Contains(':')) - { - var arrRange = addressToUse.Split(':'); - string firstPart = arrRange[0]; - string secondPart = arrRange[1]; - return IsValidA1Address(firstPart) && IsValidA1Address(secondPart); - } + //string addressToUse = rangeAddress.Contains("!") + // ? rangeAddress.Substring(rangeAddress.IndexOf("!") + 1) + // : rangeAddress; - return IsValidA1Address(addressToUse); + //if (addressToUse.Contains(':')) + //{ + // var arrRange = addressToUse.Split(':'); + // string firstPart = arrRange[0]; + // string secondPart = arrRange[1]; + // return IsValidA1Address(firstPart) && IsValidA1Address(secondPart); + //} + + //return IsValidA1Address(addressToUse); } public static int GetRowFromAddress1(string cellAddressString) diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj index 9f6bf64..e42c7cc 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -74,6 +74,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs new file mode 100644 index 0000000..938a8b5 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/NamedRanges/NamedRangesTests.cs @@ -0,0 +1,40 @@ +using ClosedXML.Excel; +using System.Linq; +using Microsoft.VisualStudio.TestTools.UnitTesting; + +namespace ClosedXML_Tests.Excel +{ + /// + /// Summary description for UnitTest1 + /// + [TestClass] + public class NamedRangesTests + { + + [TestMethod] + public void MovingRanges() + { + var wb = new XLWorkbook(); + + var sheet1 = wb.Worksheets.Add("Sheet1"); + var sheet2 = wb.Worksheets.Add("Sheet2"); + + wb.NamedRanges.Add("wbNamedRange", + "Sheet1!$B$2,Sheet1!$B$3:$C$3,Sheet2!$D$3:$D$4,Sheet1!$6:$7,Sheet1!$F:$G"); + sheet1.NamedRanges.Add("sheet1NamedRange", + "Sheet1!$B$2,Sheet1!$B$3:$C$3,Sheet2!$D$3:$D$4,Sheet1!$6:$7,Sheet1!$F:$G"); + sheet2.NamedRanges.Add("sheet2NamedRange", "Sheet1!A1,Sheet2!A1"); + + sheet1.Row(1).InsertRowsAbove(2); + sheet1.Row(1).Delete(); + sheet1.Column(1).InsertColumnsBefore(2); + sheet1.Column(1).Delete(); + + + Assert.AreEqual("'Sheet1'!$C$3,'Sheet1'!$C$4:$D$4,Sheet2!$D$3:$D$4,'Sheet1'!$7:$8,'Sheet1'!$G:$H", wb.NamedRanges.First().RefersTo); + Assert.AreEqual("'Sheet1'!$C$3,'Sheet1'!$C$4:$D$4,Sheet2!$D$3:$D$4,'Sheet1'!$7:$8,'Sheet1'!$G:$H", sheet1.NamedRanges.First().RefersTo); + Assert.AreEqual("'Sheet1'!B2,Sheet2!A1", sheet2.NamedRanges.First().RefersTo); + } + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx index 8f9b202..14ea176 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx Binary files differ