diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs index b34401e..95fa0da 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs @@ -172,5 +172,10 @@ IXLColumn AddVerticalPageBreak(); IXLColumn SetDataType(XLCellValues dataType); + + IXLColumn ColumnLeft(); + IXLColumn ColumnLeft(Int32 step); + IXLColumn ColumnRight(); + IXLColumn ColumnRight(Int32 step); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index ae07e55..c57c915 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -677,5 +677,48 @@ { CopyTo(target); } + + #region XLColumn Left + public XLColumn ColumnLeft() + { + return ColumnLeft(1); + } + IXLColumn IXLColumn.ColumnLeft() + { + return ColumnLeft(); + } + public XLColumn ColumnLeft(Int32 step) + { + return ColumnShift(step * -1); + } + IXLColumn IXLColumn.ColumnLeft(Int32 step) + { + return ColumnLeft(step); + } + #endregion + + #region XLColumn Right + public XLColumn ColumnRight() + { + return ColumnRight(1); + } + IXLColumn IXLColumn.ColumnRight() + { + return ColumnRight(); + } + public XLColumn ColumnRight(Int32 step) + { + return ColumnShift(step); + } + IXLColumn IXLColumn.ColumnRight(Int32 step) + { + return ColumnRight(step); + } + #endregion + + private XLColumn ColumnShift(Int32 columnsToShift) + { + return Worksheet.Column(ColumnNumber() + columnsToShift); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs index cf7172e..0038217 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs @@ -90,6 +90,11 @@ IXLRangeColumns Columns(String columns); IXLRangeColumn SetDataType(XLCellValues dataType); + + IXLRangeColumn ColumnLeft(); + IXLRangeColumn ColumnLeft(Int32 step); + IXLRangeColumn ColumnRight(); + IXLRangeColumn ColumnRight(Int32 step); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs index db3fdc1..2806506 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs @@ -98,6 +98,11 @@ IXLRangeRows Rows(String rows); IXLRangeRow SetDataType(XLCellValues dataType); + + IXLRangeRow RowAbove(); + IXLRangeRow RowAbove(Int32 step); + IXLRangeRow RowBelow(); + IXLRangeRow RowBelow(Int32 step); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs index 3dc6bc3..542e825 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -262,5 +262,53 @@ return 0; } + + #region XLRangeColumn Left + public XLRangeColumn ColumnLeft() + { + return ColumnLeft(1); + } + IXLRangeColumn IXLRangeColumn.ColumnLeft() + { + return ColumnLeft(); + } + public XLRangeColumn ColumnLeft(Int32 step) + { + return ColumnShift(step * -1); + } + IXLRangeColumn IXLRangeColumn.ColumnLeft(Int32 step) + { + return ColumnLeft(step); + } + #endregion + + #region XLRangeColumn Right + public XLRangeColumn ColumnRight() + { + return ColumnRight(1); + } + IXLRangeColumn IXLRangeColumn.ColumnRight() + { + return ColumnRight(); + } + public XLRangeColumn ColumnRight(Int32 step) + { + return ColumnShift(step); + } + IXLRangeColumn IXLRangeColumn.ColumnRight(Int32 step) + { + return ColumnRight(step); + } + #endregion + + private XLRangeColumn ColumnShift(Int32 columnsToShift) + { + Int32 columnNumber = ColumnNumber() + columnsToShift; + return Worksheet.Range( + RangeAddress.FirstAddress.RowNumber, + columnNumber, + RangeAddress.LastAddress.RowNumber, + columnNumber).FirstColumn(); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs index ddb1ab3..f219d09 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -270,5 +270,53 @@ return 0; } + + #region XLRangeRow Above + public XLRangeRow RowAbove() + { + return RowAbove(1); + } + IXLRangeRow IXLRangeRow.RowAbove() + { + return RowAbove(); + } + public XLRangeRow RowAbove(Int32 step) + { + return RowShift(step * -1); + } + IXLRangeRow IXLRangeRow.RowAbove(Int32 step) + { + return RowAbove(step); + } + #endregion + + #region XLRangeRow Below + public XLRangeRow RowBelow() + { + return RowBelow(1); + } + IXLRangeRow IXLRangeRow.RowBelow() + { + return RowBelow(); + } + public XLRangeRow RowBelow(Int32 step) + { + return RowShift(step); + } + IXLRangeRow IXLRangeRow.RowBelow(Int32 step) + { + return RowBelow(step); + } + #endregion + + private XLRangeRow RowShift(Int32 rowsToShift) + { + Int32 rowNum = RowNumber() + rowsToShift; + return Worksheet.Range( + rowNum, + RangeAddress.FirstAddress.ColumnNumber, + rowNum, + RangeAddress.LastAddress.ColumnNumber).FirstRow(); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs index b3a68e8..7063ff4 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs @@ -175,5 +175,10 @@ IXLRow AddHorizontalPageBreak(); IXLRow SetDataType(XLCellValues dataType); + + IXLRow RowAbove(); + IXLRow RowAbove(Int32 step); + IXLRow RowBelow(); + IXLRow RowBelow(Int32 step); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index 26aba04..69d8481 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -612,5 +612,48 @@ { CopyTo(target); } + + #region XLRow Above + public XLRow RowAbove() + { + return RowAbove(1); + } + IXLRow IXLRow.RowAbove() + { + return RowAbove(); + } + public XLRow RowAbove(Int32 step) + { + return RowShift(step * -1); + } + IXLRow IXLRow.RowAbove(Int32 step) + { + return RowAbove(step); + } + #endregion + + #region XLRow Below + public XLRow RowBelow() + { + return RowBelow(1); + } + IXLRow IXLRow.RowBelow() + { + return RowBelow(); + } + public XLRow RowBelow(Int32 step) + { + return RowShift(step); + } + IXLRow IXLRow.RowBelow(Int32 step) + { + return RowBelow(step); + } + #endregion + + private XLRow RowShift(Int32 rowsToShift) + { + return Worksheet.Row(RowNumber() + rowsToShift); + } } } \ 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 2a37cd8..a51a50e 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -148,7 +148,7 @@ - + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index 6a88fb8..e80d76f 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -80,7 +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"); + new WalkingRanges().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 deleted file mode 100644 index fc61d2a..0000000 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/CellMoves.cs +++ /dev/null @@ -1,40 +0,0 @@ -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_Examples/Ranges/WalkingRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/WalkingRanges.cs new file mode 100644 index 0000000..e891fbc --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/WalkingRanges.cs @@ -0,0 +1,68 @@ +using System; +using System.Linq; +using ClosedXML.Excel; + + +namespace ClosedXML_Examples.Ranges +{ + public class WalkingRanges : IXLExample + { + #region Methods + + // Public + public void Create(String filePath) + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Walking Cells"); + + 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); + + var wsWalkRows = wb.Worksheets.Add("Walking rows"); + + var row = wsWalkRows.Row(3); + row.RowAbove().Style.Fill.SetBackgroundColor(XLColor.Salmon); + row.RowAbove(2).Style.Fill.SetBackgroundColor(XLColor.LightSalmon); + row.RowBelow().Style.Fill.SetBackgroundColor(XLColor.Blue); + row.RowBelow(2).Style.Fill.SetBackgroundColor(XLColor.BlueBell); + + var rangeRow = wsWalkRows.Range("B8:D12").Row(3); + rangeRow.RowAbove().Style.Fill.SetBackgroundColor(XLColor.Salmon); + rangeRow.RowAbove(2).Style.Fill.SetBackgroundColor(XLColor.LightSalmon); + rangeRow.RowBelow().Style.Fill.SetBackgroundColor(XLColor.Blue); + rangeRow.RowBelow(2).Style.Fill.SetBackgroundColor(XLColor.BlueBell); + + var wsWalkColumns = wb.Worksheets.Add("Walking columns"); + + var column = wsWalkColumns.Column(3); + column.ColumnLeft().Style.Fill.SetBackgroundColor(XLColor.Salmon); + column.ColumnLeft(2).Style.Fill.SetBackgroundColor(XLColor.LightSalmon); + column.ColumnRight().Style.Fill.SetBackgroundColor(XLColor.Blue); + column.ColumnRight(2).Style.Fill.SetBackgroundColor(XLColor.BlueBell); + + var rangeColumn = wsWalkColumns.Range("H2:L4").Column(3); + rangeColumn.ColumnLeft().Style.Fill.SetBackgroundColor(XLColor.Salmon); + rangeColumn.ColumnLeft(2).Style.Fill.SetBackgroundColor(XLColor.LightSalmon); + rangeColumn.ColumnRight().Style.Fill.SetBackgroundColor(XLColor.Blue); + rangeColumn.ColumnRight(2).Style.Fill.SetBackgroundColor(XLColor.BlueBell); + + wb.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj index 2b50265..06d2e79 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -170,7 +170,7 @@ - +