diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 1ee2e88..3f57037 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -105,7 +105,6 @@ - diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs index c90afad..e956a44 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/IXLCell.cs @@ -160,6 +160,7 @@ /// If the named range exists, it will add this range to that named range. /// Name of the range. /// The scope for the named range. + /// IXLCell AddToNamed(String rangeName, XLScope scope); /// @@ -168,6 +169,7 @@ /// Name of the range. /// The scope for the named range. /// The comments for the named range. + /// IXLCell AddToNamed(String rangeName, XLScope scope, String comment); //IXLCell CopyFrom(IXLCell otherCell); @@ -182,5 +184,14 @@ Boolean IsMerged(); Boolean IsUsed(); Boolean IsUsed(Boolean includeFormats); + + IXLCell CellAbove(); + IXLCell CellAbove(Int32 step); + IXLCell CellBelow(); + IXLCell CellBelow(Int32 step); + IXLCell CellLeft(); + IXLCell CellLeft(Int32 step); + IXLCell CellRight(); + IXLCell CellRight(Int32 step); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index b7e20fe..142b25b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -14,12 +14,12 @@ public static readonly DateTime BaseDate = new DateTime(1899, 12, 30); private static Dictionary _formatCodes; - private static readonly Regex _a1Regex = new Regex( + 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 - private static readonly Regex _a1SimpleRegex = new Regex( + private static readonly Regex A1SimpleRegex = new Regex( @"(?<=\W)" // Start with non word + @"(" // Start Group to pick + @"(" // Start Sheet Name, optional @@ -41,15 +41,15 @@ + @"(?=\W)" // End with non word ); - private static readonly Regex a1RowRegex = new Regex( + private static readonly Regex A1RowRegex = new Regex( @"(\d{1,7}:\d{1,7})" // 1:1 ); - private static readonly Regex a1ColumnRegex = new Regex( + private static readonly Regex A1ColumnRegex = new Regex( @"([a-zA-Z]{1,3}:[a-zA-Z]{1,3})" // A:A ); - private static readonly Regex r1c1Regex = new Regex( + private static readonly Regex R1C1Regex = new Regex( @"(?<=\W)([Rr]\[?-?\d{0,7}\]?[Cc]\[?-?\d{0,7}\]?)(?=\W)" // R1C1 + @"|(?<=\W)([Rr]\[?-?\d{0,7}\]?:[Rr]\[?-?\d{0,7}\]?)(?=\W)" // R:R + @"|(?<=\W)([Cc]\[?-?\d{0,5}\]?:[Cc]\[?-?\d{0,5}\]?)(?=\W)"); // C:C @@ -365,7 +365,7 @@ { int co = Address.ColumnNumber; - if (m.GetType().IsPrimitive || m.GetType() == typeof(String) || m.GetType() == typeof(DateTime)) + if (m.GetType().IsPrimitive || m is string || m is DateTime) { if (!hasTitles) { @@ -497,7 +497,7 @@ { int co = Address.ColumnNumber; - if (m.GetType().IsPrimitive || m.GetType() == typeof(String) || m.GetType() == typeof(DateTime)) + if (m.GetType().IsPrimitive || m is string || m is DateTime) SetValue(m, ro, co); else if (m.GetType().IsArray) { @@ -1120,7 +1120,7 @@ string value = ">" + strValue + "<"; - var regex = conversionType == FormulaConversionType.A1ToR1C1 ? _a1Regex : r1c1Regex; + var regex = conversionType == FormulaConversionType.A1ToR1C1 ? A1Regex : R1C1Regex; var sb = new StringBuilder(); int lastIndex = 0; @@ -1340,7 +1340,7 @@ { string value = ">" + _formulaA1 + "<"; - var regex = _a1SimpleRegex; + var regex = A1SimpleRegex; var sb = new StringBuilder(); int lastIndex = 0; @@ -1368,7 +1368,7 @@ if (sheetName.ToLower().Equals(shiftedRange.Worksheet.Name.ToLower())) { string rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); - if (!a1ColumnRegex.IsMatch(rangeAddress)) + if (!A1ColumnRegex.IsMatch(rangeAddress)) { var matchRange = _worksheet.Workbook.Worksheet(sheetName).Range(rangeAddress); if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= @@ -1380,7 +1380,7 @@ shiftedRange.RangeAddress.LastAddress.ColumnNumber >= matchRange.RangeAddress.LastAddress.ColumnNumber) { - if (a1RowRegex.IsMatch(rangeAddress)) + if (A1RowRegex.IsMatch(rangeAddress)) { var rows = rangeAddress.Split(':'); string row1String = rows[0]; @@ -1558,7 +1558,7 @@ { string value = ">" + _formulaA1 + "<"; - var regex = _a1SimpleRegex; + var regex = A1SimpleRegex; var sb = new StringBuilder(); int lastIndex = 0; @@ -1586,7 +1586,7 @@ if (sheetName.ToLower().Equals(shiftedRange.Worksheet.Name.ToLower())) { string rangeAddress = matchString.Substring(matchString.IndexOf('!') + 1); - if (!a1RowRegex.IsMatch(rangeAddress)) + if (!A1RowRegex.IsMatch(rangeAddress)) { var matchRange = _worksheet.Workbook.Worksheet(sheetName).Range(rangeAddress); if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= @@ -1598,7 +1598,7 @@ shiftedRange.RangeAddress.LastAddress.RowNumber >= matchRange.RangeAddress.LastAddress.RowNumber) { - if (a1ColumnRegex.IsMatch(rangeAddress)) + if (A1ColumnRegex.IsMatch(rangeAddress)) { var columns = rangeAddress.Split(':'); string column1String = columns[0]; @@ -1796,5 +1796,86 @@ } ; #endregion + + #region XLCell Above + public XLCell CellAbove() + { + return CellAbove(1); + } + IXLCell IXLCell.CellAbove() + { + return CellAbove(); + } + public XLCell CellAbove(Int32 step) + { + return CellShift(step * -1, 0); + } + IXLCell IXLCell.CellAbove(Int32 step) + { + return CellAbove(step); + } + #endregion + + #region XLCell Below + public XLCell CellBelow() + { + return CellBelow(1); + } + IXLCell IXLCell.CellBelow() + { + return CellBelow(); + } + public XLCell CellBelow(Int32 step) + { + return CellShift(step, 0); + } + IXLCell IXLCell.CellBelow(Int32 step) + { + return CellBelow(step); + } + #endregion + + #region XLCell Left + public XLCell CellLeft() + { + return CellLeft(1); + } + IXLCell IXLCell.CellLeft() + { + return CellLeft(); + } + public XLCell CellLeft(Int32 step) + { + return CellShift(0, step * -1); + } + IXLCell IXLCell.CellLeft(Int32 step) + { + return CellLeft(step); + } + #endregion + + #region XLCell Right + public XLCell CellRight() + { + return CellRight(1); + } + IXLCell IXLCell.CellRight() + { + return CellRight(); + } + public XLCell CellRight(Int32 step) + { + return CellShift(0, step); + } + IXLCell IXLCell.CellRight(Int32 step) + { + return CellRight(step); + } + #endregion + + private XLCell CellShift(Int32 rowsToShift, Int32 columnsToShift) + { + return Worksheet.Cell(Address.RowNumber + rowsToShift, Address.ColumnNumber + columnsToShift); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellsCollection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellsCollection.cs index 52582b9..5b720b9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellsCollection.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellsCollection.cs @@ -5,12 +5,9 @@ { internal class XLCellsCollection { - private const Int32 InitialRowCapacity = 5; - private const Int32 InitialColumnCapacity = 10; - //private XLCell[,] _cells; + private readonly Dictionary _cellsDictionary = new Dictionary(); - private Int32 _columnCapacity = InitialColumnCapacity; - private Int32 _rowCapacity = InitialRowCapacity; + public Int32 MaxColumnUsed; public Int32 MaxRowUsed; @@ -101,24 +98,9 @@ RowsUsed.Clear(); ColumnsUsed.Clear(); - //for (int ro = 1; ro <= MaxRowUsed; ro++) - //{ - // for (int co = 1; co <= MaxColumnUsed; co++) - // { - // if (_cells[ro, co] != null) - // { - // var sp = new XLSheetPoint(ro, co); - // if (!Deleted.Contains(sp)) - // Deleted.Add(sp); - // } - // } - //} _cellsDictionary.Clear(); - _rowCapacity = InitialRowCapacity; - _columnCapacity = InitialColumnCapacity; MaxRowUsed = 0; MaxColumnUsed = 0; - //_cells = new XLCell[_rowCapacity,_columnCapacity]; } public void Remove(XLSheetPoint sheetPoint) diff --git a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs index b9118f7..0087778 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs +++ b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs @@ -2,7 +2,6 @@ using System.Globalization; using System.Text; using ClosedXML.Excel; -using Microsoft.VisualBasic.CompilerServices; namespace ClosedXML { @@ -184,26 +183,6 @@ return GetColumnNumberFromLetter(cellAddressString.Substring(startPos, rowPos)); } - public static T[,] ResizeArrayX(T[,] original, int rows, int cols) - { - var newArray = new T[rows,cols]; - Array.Copy(original, newArray, original.Length); - return newArray; - } - public static T[,] ResizeArray(T[,] original, int rows, int cols) - { - var newArray = new T[rows,cols]; - Utils.CopyArray(original, newArray); - //int originalSize = original.Length; - //int xLength = original.GetLength(0); - //int yLength = original.GetLength(1); - - //for (int x = 0; x < xLength; x++) - //{ - // Array.Copy(original, x * originalSize, newArray, 0, yLength); - //} - return newArray; - } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 8fb3b66..2a37cd8 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -148,6 +148,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index 60f52e0..6a88fb8 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -80,6 +80,7 @@ new CopyingRowsAndColumns().Create(@"C:\Excel Files\Created\CopyingRowsAndColumns.xlsx"); new UsingRichText().Create(@"C:\Excel Files\Created\UsingRichText.xlsx"); new UsingPhonetics().Create(@"C:\Excel Files\Created\UsingPhonetics.xlsx"); + new CellMoves().Create(@"C:\Excel Files\Created\CellMoves.xlsx"); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/CellMoves.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/CellMoves.cs new file mode 100644 index 0000000..fc61d2a --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/CellMoves.cs @@ -0,0 +1,40 @@ +using System; +using System.Linq; +using ClosedXML.Excel; + + +namespace ClosedXML_Examples.Ranges +{ + public class CellMoves : IXLExample + { + #region Methods + + // Public + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.Worksheets.Add("Cell Moves"); + + var cell = ws.Cell(5, 5).SetValue("(5,5)"); + + cell.CellAbove().SetValue("(4,5)").Style.Fill.SetBackgroundColor(XLColor.LightSalmon); + cell.CellAbove(2).SetValue("(3,5)").Style.Fill.SetBackgroundColor(XLColor.LightSalmon); + cell.CellBelow().SetValue("(6,5)").Style.Fill.SetBackgroundColor(XLColor.Salmon); + cell.CellBelow(2).SetValue("(7,5)").Style.Fill.SetBackgroundColor(XLColor.Salmon); + + cell.CellLeft().SetValue("(5,4)").Style.Fill.SetBackgroundColor(XLColor.LightBlue); + cell.CellLeft(2).SetValue("(5,3)").Style.Fill.SetBackgroundColor(XLColor.LightBlue); + cell.CellRight().SetValue("(5,6)").Style.Fill.SetBackgroundColor(XLColor.BlueBell); + cell.CellRight(2).SetValue("(5,7)").Style.Fill.SetBackgroundColor(XLColor.BlueBell); + + workbook.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index 3807aef..f876d13 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -104,7 +104,6 @@ - diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj index fc132ed..2b50265 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -169,6 +169,9 @@ + + +