diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs index 58b0351..affe7bd 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs @@ -7,6 +7,7 @@ namespace ClosedXML.Excel { public enum XLShiftDeletedCells { ShiftCellsUp, ShiftCellsLeft } + public enum XLTransposeOptions { MoveCells, ReplaceCells } public interface IXLRange: IXLRangeBase { IXLCell Cell(int row, int column); @@ -45,6 +46,8 @@ void InsertRowsBelow(int numberOfRows); void Delete(XLShiftDeletedCells shiftDeleteCells); void Clear(); + + void Transpose(XLTransposeOptions transposeOption); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 1dc374c..d878c89 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -286,6 +286,184 @@ return retVal; } + public void Transpose(XLTransposeOptions transposeOption) + { + var rowCount = this.RowCount(); + var columnCount = this.ColumnCount(); + var squareSide = rowCount > columnCount ? rowCount : columnCount; + + var firstCell = FirstCell(); + var lastCell = LastCell(); + var topBorder = firstCell.Style.Border.LeftBorder; + var topBorderColor = firstCell.Style.Border.LeftBorderColor; + var leftBorder = firstCell.Style.Border.TopBorder; + var leftBorderColor = firstCell.Style.Border.TopBorderColor; + var rightBorder = lastCell.Style.Border.BottomBorder; + var rightBorderColor = lastCell.Style.Border.BottomBorderColor; + var bottomBorder = lastCell.Style.Border.RightBorder; + var bottomBorderColor = lastCell.Style.Border.RightBorderColor; + + var insideTopBorder = lastCell.Style.Border.LeftBorder; + var insideTopBorderColor = lastCell.Style.Border.LeftBorderColor; + var insideLeftBorder = lastCell.Style.Border.TopBorder; + var insideLeftBorderColor = lastCell.Style.Border.TopBorderColor; + var insideRightBorder = firstCell.Style.Border.BottomBorder; + var insideRightBorderColor = firstCell.Style.Border.BottomBorderColor; + var insideBottomBorder = firstCell.Style.Border.RightBorder; + var insideBottomBorderColor = firstCell.Style.Border.RightBorderColor; + + MoveOrClearForTranspose(transposeOption, rowCount, columnCount); + TransposeMerged(); + TransposeRange(squareSide); + this.LastAddressInSheet = new XLAddress( + firstCell.Address.RowNumber + columnCount - 1, + firstCell.Address.ColumnNumber + rowCount - 1); + if (rowCount > columnCount) + { + var rng = Worksheet.Range( + this.LastAddressInSheet.RowNumber + 1, + this.FirstAddressInSheet.ColumnNumber, + this.LastAddressInSheet.RowNumber + (rowCount - columnCount), + this.LastAddressInSheet.ColumnNumber); + rng.Delete(XLShiftDeletedCells.ShiftCellsUp); + } + else if (columnCount > rowCount) + { + var rng = Worksheet.Range( + this.FirstAddressInSheet.RowNumber, + this.LastAddressInSheet.ColumnNumber + 1, + this.LastAddressInSheet.RowNumber, + this.LastAddressInSheet.ColumnNumber + (columnCount - rowCount)); + rng.Delete(XLShiftDeletedCells.ShiftCellsLeft); + } + + + foreach (var c in this.Range(1,1,columnCount, rowCount).Cells()) + { + c.Style.Border.TopBorder = insideTopBorder; + c.Style.Border.TopBorderColor = insideTopBorderColor; + c.Style.Border.LeftBorder = insideLeftBorder; + c.Style.Border.LeftBorderColor = insideLeftBorderColor; + c.Style.Border.RightBorder = insideRightBorder; + c.Style.Border.RightBorderColor = insideRightBorderColor; + c.Style.Border.BottomBorder = insideBottomBorder; + c.Style.Border.BottomBorderColor = insideBottomBorderColor; + } + + var firstColumn = this.FirstColumn(); + firstColumn.Style.Border.LeftBorder = leftBorder; + firstColumn.Style.Border.LeftBorderColor = leftBorderColor; + var LastColumn = this.LastColumn(); + LastColumn.Style.Border.RightBorder = rightBorder; + LastColumn.Style.Border.RightBorderColor = rightBorderColor; + var firstRow = this.FirstRow(); + firstRow.Style.Border.TopBorder = topBorder; + firstRow.Style.Border.TopBorderColor = topBorderColor; + var lastRow = this.LastRow(); + lastRow.Style.Border.BottomBorder = bottomBorder; + lastRow.Style.Border.BottomBorderColor = bottomBorderColor; + } + + private void TransposeRange(int squareSide) + { + var cellsToInsert = new Dictionary(); + var cellsToDelete = new List(); + XLRange rngToTranspose = (XLRange)Worksheet.Range( + this.FirstAddressInSheet.RowNumber, + this.FirstAddressInSheet.ColumnNumber, + this.FirstAddressInSheet.RowNumber + squareSide, + this.FirstAddressInSheet.ColumnNumber + squareSide); + + foreach (var c in rngToTranspose.Cells()) + { + var newKey = new XLAddress(c.Address.ColumnNumber, c.Address.RowNumber); + var newCell = new XLCell(newKey, c.Style, Worksheet); + newCell.Value = c.Value; + newCell.DataType = c.DataType; + cellsToInsert.Add(newKey, newCell); + cellsToDelete.Add(c.Address); + } + cellsToDelete.ForEach(c => this.Worksheet.Internals.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => this.Worksheet.Internals.CellsCollection.Add(c.Key, c.Value)); + } + + private void TransposeMerged() + { + List mergeToDelete = new List(); + List mergeToInsert = new List(); + foreach (var merge in Worksheet.Internals.MergedCells) + { + if (this.ContainsRange(merge)) + { + mergeToDelete.Add(merge); + String[] arrRange = merge.Split(':'); + var firstAddress = new XLAddress(arrRange[0]); + var lastAddress = new XLAddress(arrRange[1]); + var newLastAddress = new XLAddress(lastAddress.ColumnNumber, lastAddress.RowNumber); + mergeToInsert.Add(firstAddress.ToString() + ":" + newLastAddress.ToString()); + } + } + mergeToDelete.ForEach(m => this.Worksheet.Internals.MergedCells.Remove(m)); + mergeToInsert.ForEach(m => this.Worksheet.Internals.MergedCells.Add(m)); + } + + private void MoveOrClearForTranspose(XLTransposeOptions transposeOption, int rowCount, int columnCount) + { + if (transposeOption == XLTransposeOptions.MoveCells) + { + if (rowCount > columnCount) + { + this.InsertColumnsAfter(rowCount - columnCount); + } + else if (columnCount > rowCount) + { + this.InsertRowsBelow(columnCount - rowCount); + } + } + else + { + if (rowCount > columnCount) + { + var toMove = columnCount - rowCount; + var rngToClear = Worksheet.Range( + this.FirstAddressInSheet.RowNumber, + columnCount + 1, + this.LastAddressInSheet.RowNumber, + columnCount + toMove); + rngToClear.Clear(); + } + else if (columnCount > rowCount) + { + var toMove = rowCount - columnCount; + var rngToClear = Worksheet.Range( + rowCount + 1, + this.FirstAddressInSheet.ColumnNumber, + rowCount + toMove, + this.LastAddressInSheet.ColumnNumber); + rngToClear.Clear(); + } + } + } + + public Boolean ContainsRange(String rangeAddress) + { + XLAddress firstAddress; + XLAddress lastAddress; + if (rangeAddress.Contains(':')) + { + String[] arrRange = rangeAddress.Split(':'); + firstAddress = new XLAddress(arrRange[0]); + lastAddress = new XLAddress(arrRange[1]); + } + else + { + firstAddress = new XLAddress(rangeAddress); + lastAddress = new XLAddress(rangeAddress); + } + return + firstAddress >= (XLAddress)this.FirstAddressInSheet + && lastAddress <= (XLAddress)this.LastAddressInSheet; + } #endregion } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index a944212..3d902a5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -127,7 +127,7 @@ || newFirstCellAddress.ColumnNumber > this.LastAddressInSheet.ColumnNumber || newLastCellAddress.ColumnNumber > this.LastAddressInSheet.ColumnNumber ) - throw new ArgumentOutOfRangeException(String.Format("The cells {0} and {1} are outside the range '{2}'.", firstCellAddress.ToString(), lastCellAddress.ToString(), this.ToString())); + throw new ArgumentOutOfRangeException(String.Format("The cells {0} and {1} are outside the range '{2}'.", newFirstCellAddress.ToString(), newLastCellAddress.ToString(), this.ToString())); return new XLRange(xlRangeParameters); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs index 37ff377..34f157a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLAddress.cs @@ -196,13 +196,15 @@ public static Boolean operator >(XLAddress xlCellAddressLeft, XLAddress xlCellAddressRight) { return !(xlCellAddressLeft == xlCellAddressRight) - && (xlCellAddressLeft.RowNumber > xlCellAddressRight.RowNumber || xlCellAddressLeft.ColumnNumber > xlCellAddressRight.ColumnNumber); + && (xlCellAddressLeft.RowNumber >= xlCellAddressRight.RowNumber + && xlCellAddressLeft.ColumnNumber >= xlCellAddressRight.ColumnNumber); } public static Boolean operator <(XLAddress xlCellAddressLeft, XLAddress xlCellAddressRight) { return !(xlCellAddressLeft == xlCellAddressRight) - && (xlCellAddressLeft.RowNumber < xlCellAddressRight.RowNumber || xlCellAddressLeft.ColumnNumber < xlCellAddressRight.ColumnNumber); + && (xlCellAddressLeft.RowNumber <= xlCellAddressRight.RowNumber + && xlCellAddressLeft.ColumnNumber <= xlCellAddressRight.ColumnNumber); } public static Boolean operator >=(XLAddress xlCellAddressLeft, XLAddress xlCellAddressRight) diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index cf7ce8d..34d86d5 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -54,6 +54,8 @@ + + @@ -67,7 +69,7 @@ - + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index f28b7f1..2fd1957 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -19,7 +19,7 @@ new BasicTable().Create(@"C:\Excel Files\Created\BasicTable.xlsx"); new StyleExamples().Create(); new ChangingBasicTable().Create(); - new MovingRanges().Create(); + new ShiftingRanges().Create(); new ColumnSettings().Create(@"C:\Excel Files\Created\ColumnSettings.xlsx"); new RowSettings().Create(@"C:\Excel Files\Created\RowSettings.xlsx"); new MergeCells().Create(@"C:\Excel Files\Created\MergedCells.xlsx"); @@ -46,6 +46,8 @@ new CellValues().Create(@"C:\Excel Files\Created\CellValues.xlsx"); new LambdaExpressions().Create(); new DefaultStyles().Create(@"C:\Excel Files\Created\DefaultStyles.xlsx"); + new TransposeRanges().Create(); + new TransposeRangesPlus().Create(); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/MovingRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/MovingRanges.cs deleted file mode 100644 index 6540be5..0000000 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/MovingRanges.cs +++ /dev/null @@ -1,30 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; -using ClosedXML.Excel; -using System.Drawing; - -namespace ClosedXML_Examples -{ - public class MovingRanges - { - public void Create() - { - var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); - var ws = workbook.Worksheets.GetWorksheet(0); - - // Get a range object - var rngHeaders = ws.Range("B3:F3"); - - // Insert some rows/columns before the range - ws.Row(1).InsertRowsAbove(2); - ws.Column(1).InsertColumnsBefore(2); - - // Change the background color of the headers - rngHeaders.Style.Fill.BackgroundColor = Color.LightSalmon; - - workbook.SaveAs(@"C:\Excel Files\Created\MovingRanges.xlsx"); - } - } -} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs new file mode 100644 index 0000000..93bd549 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/ShiftingRanges.cs @@ -0,0 +1,30 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using System.Drawing; + +namespace ClosedXML_Examples +{ + public class ShiftingRanges + { + public void Create() + { + var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); + var ws = workbook.Worksheets.GetWorksheet(0); + + // Get a range object + var rngHeaders = ws.Range("B3:F3"); + + // Insert some rows/columns before the range + ws.Row(1).InsertRowsAbove(2); + ws.Column(1).InsertColumnsBefore(2); + + // Change the background color of the headers + rngHeaders.Style.Fill.BackgroundColor = Color.LightSalmon; + + workbook.SaveAs(@"C:\Excel Files\Created\ShiftingRanges.xlsx"); + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs new file mode 100644 index 0000000..3da6d12 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/TransposeRangesPlus.cs @@ -0,0 +1,36 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using System.Drawing; + + +namespace ClosedXML_Examples +{ + public class TransposeRangesPlus + { + public void Create() + { + var workbook = new XLWorkbook(@"C:\Excel Files\Created\BasicTable.xlsx"); + var ws = workbook.Worksheets.GetWorksheet(0); + + var rngTable = ws.Range("B2:F6"); + + rngTable.Row(rngTable.RowCount() - 1).Delete(XLShiftDeletedCells.ShiftCellsUp); + + // Place some markers + var cellNextRow = ws.Cell(rngTable.LastAddressInSheet.RowNumber + 1, rngTable.LastAddressInSheet.ColumnNumber); + cellNextRow.Value = "Next Row"; + var cellNextColumn = ws.Cell(rngTable.LastAddressInSheet.RowNumber, rngTable.LastAddressInSheet.ColumnNumber + 1); + cellNextColumn.Value = "Next Column"; + + rngTable.Transpose(XLTransposeOptions.MoveCells); + rngTable.Transpose(XLTransposeOptions.MoveCells); + rngTable.Transpose(XLTransposeOptions.ReplaceCells); + rngTable.Transpose(XLTransposeOptions.ReplaceCells); + + workbook.SaveAs(@"C:\Excel Files\Created\TransposeRangesPlus.xlsx"); + } + } +} \ No newline at end of file