diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs index e232ae8..dd18368 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLAddress.cs @@ -12,7 +12,7 @@ String ColumnLetter { get; } } - public static class XLAddressMethods + public static class IXLAddressMethods { } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLColumn.cs index f869cba..11dcbe1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLColumn.cs @@ -8,5 +8,6 @@ public interface IXLColumn: IXLRange { Double Width { get; set; } + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs index acfe46e..6119b65 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRange.cs @@ -16,9 +16,12 @@ IXLRange Row(Int32 row); IXLRange Column(Int32 column); IXLRange Column(String column); + Int32 RowNumber { get; } + Int32 ColumnNumber { get; } + String ColumnLetter { get; } } - public static class XLRangeMethods + public static class IXLRangeMethods { public static IXLCell FirstCell(this IXLRange range) { @@ -76,15 +79,26 @@ } public static IXLRange Range(this IXLRange range, IXLAddress firstCellAddress, IXLAddress lastCellAddress) { - return new XLRange( - new XLRangeParameters() - { - FirstCellAddress = (XLAddress)firstCellAddress + (XLAddress)range.FirstCellAddress - 1, - LastCellAddress = (XLAddress)lastCellAddress + (XLAddress)range.FirstCellAddress - 1, - CellsCollection = range.CellsCollection, - MergedCells = range.MergedCells - } - ); + + var xlRangeParameters = new XLRangeParameters() + { + FirstCellAddress = (XLAddress)firstCellAddress + (XLAddress)range.FirstCellAddress - 1, + LastCellAddress = (XLAddress)lastCellAddress + (XLAddress)range.FirstCellAddress - 1, + CellsCollection = range.CellsCollection, + MergedCells = range.MergedCells, + DefaultStyle = range.Style + }; + if ( + xlRangeParameters.FirstCellAddress.Row < range.FirstCellAddress.Row + || xlRangeParameters.FirstCellAddress.Row > range.LastCellAddress.Row + || xlRangeParameters.LastCellAddress.Row > range.LastCellAddress.Row + || xlRangeParameters.FirstCellAddress.Column < range.FirstCellAddress.Column + || xlRangeParameters.FirstCellAddress.Column > range.LastCellAddress.Column + || xlRangeParameters.LastCellAddress.Column > range.LastCellAddress.Column + ) + throw new ArgumentOutOfRangeException(); + + return new XLRange(xlRangeParameters); } public static IXLRange Range(this IXLRange range, IXLCell firstCell, IXLCell lastCell) { @@ -127,6 +141,105 @@ { return range.Row(range.RowCount()); } + + public static void InsertRowsBelow(this IXLRange range, Int32 numberOfRows) + { + var cellsToInsert = new Dictionary(); + var cellsToDelete = new List(); + var lastRow = range.LastRow().RowNumber; + var firstColumn = range.FirstColumn().ColumnNumber; + var lastColumn = range.LastColumn().ColumnNumber; + foreach (var c in range.CellsCollection + .Where(c => + c.Key.Row > lastRow + && c.Key.Column >= firstColumn + && c.Key.Column <= lastColumn + )) + { + var newRow = c.Key.Row + numberOfRows; + var newKey = new XLAddress(newRow, c.Key.Column); + var newCell = new XLCell(newKey, c.Value.Style); + newCell.Value = c.Value.Value; + cellsToInsert.Add(newKey, newCell); + cellsToDelete.Add(c.Key); + } + cellsToDelete.ForEach(c => range.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => range.CellsCollection.Add(c.Key, c.Value)); + } + public static void InsertRowsAbove(this IXLRange range, Int32 numberOfRows) + { + var cellsToInsert = new Dictionary(); + var cellsToDelete = new List(); + var firstRow = range.FirstRow().RowNumber; + var firstColumn = range.FirstColumn().ColumnNumber; + var lastColumn = range.LastColumn().ColumnNumber; + foreach (var c in range.CellsCollection + .Where(c => + c.Key.Row >= firstRow + && c.Key.Column >= firstColumn + && c.Key.Column <= lastColumn + )) + { + var newRow = c.Key.Row + numberOfRows; + var newKey = new XLAddress(newRow, c.Key.Column); + var newCell = new XLCell(newKey, c.Value.Style); + newCell.Value = c.Value.Value; + cellsToInsert.Add(newKey, newCell); + cellsToDelete.Add(c.Key); + } + cellsToDelete.ForEach(c => range.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => range.CellsCollection.Add(c.Key, c.Value)); + } + + public static void InsertColumnsAfter(this IXLRange range, Int32 numberOfColumns) + { + var cellsToInsert = new Dictionary(); + var cellsToDelete = new List(); + var firstRow = range.FirstRow().RowNumber; + var lastRow = range.LastRow().RowNumber; + var lastColumn = range.LastColumn().ColumnNumber; + foreach (var c in range.CellsCollection + .Where(c => + c.Key.Column > lastColumn + && c.Key.Row >= firstRow + && c.Key.Row <= lastRow + )) + { + var newColumn = c.Key.Column + numberOfColumns; + var newKey = new XLAddress(c.Key.Row, newColumn); + var newCell = new XLCell(newKey, c.Value.Style); + newCell.Value = c.Value.Value; + cellsToInsert.Add(newKey, newCell); + cellsToDelete.Add(c.Key); + } + cellsToDelete.ForEach(c => range.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => range.CellsCollection.Add(c.Key, c.Value)); + } + + public static void InsertColumnsBefore(this IXLRange range, Int32 numberOfColumns) + { + var cellsToInsert = new Dictionary(); + var cellsToDelete = new List(); + var firstRow = range.FirstRow().RowNumber; + var lastRow = range.LastRow().RowNumber; + var firstColumn = range.FirstColumn().ColumnNumber; + foreach (var c in range.CellsCollection + .Where(c => + c.Key.Column >= firstColumn + && c.Key.Row >= firstRow + && c.Key.Row <= lastRow + )) + { + var newColumn = c.Key.Column + numberOfColumns; + var newKey = new XLAddress(c.Key.Row, newColumn); + var newCell = new XLCell(newKey, c.Value.Style); + newCell.Value = c.Value.Value; + cellsToInsert.Add(newKey, newCell); + cellsToDelete.Add(c.Key); + } + cellsToDelete.ForEach(c => range.CellsCollection.Remove(c)); + cellsToInsert.ForEach(c => range.CellsCollection.Add(c.Key, c.Value)); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRow.cs index 30fb541..2f6a053 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLRow.cs @@ -8,5 +8,11 @@ public interface IXLRow: IXLRange { Double Height { get; set; } + + } + + public static class IXLRowMethods + { + } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs index d1edce1..2076edb 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLColumn.cs @@ -12,12 +12,18 @@ { FirstCellAddress = new XLAddress(1, column); LastCellAddress = new XLAddress(XLWorksheet.MaxNumberOfRows, column); + RowNumber = 1; + ColumnNumber = column; + ColumnLetter = XLAddress.GetColumnLetterFromNumber(column); CellsCollection = cellsCollection; this.style = new XLStyle(this, defaultStyle); this.Width = XLWorkbook.DefaultColumnWidth; } public Double Width { get; set; } + public Int32 RowNumber { get; private set; } + public Int32 ColumnNumber { get; private set; } + public String ColumnLetter { get; private set; } #region IXLRange Members @@ -65,7 +71,7 @@ public IXLRange Row(int row) { - var address = new XLAddress(row, FirstCellAddress.Column); + var address = new XLAddress(row, 1); return this.Range(address, address); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRange.cs index b35f164..a26c65a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRange.cs @@ -16,7 +16,10 @@ LastCellAddress = xlRangeParameters.LastCellAddress; CellsCollection = xlRangeParameters.CellsCollection; MergedCells = xlRangeParameters.MergedCells; - this.defaultStyle = new XLStyle(this, this.FirstCell().Style); + RowNumber = FirstCellAddress.Row; + ColumnNumber = FirstCellAddress.Column; + ColumnLetter = FirstCellAddress.ColumnLetter; + this.defaultStyle = new XLStyle(this, xlRangeParameters.DefaultStyle); } #region IXLRange Members @@ -47,6 +50,10 @@ return this.Range(firstCellAddress, lastCellAddress); } + public Int32 RowNumber { get; private set; } + public Int32 ColumnNumber { get; private set; } + public String ColumnLetter { get; private set; } + #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRangeParameters.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRangeParameters.cs index fb03cee..0bc8e4a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRangeParameters.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRangeParameters.cs @@ -2,6 +2,7 @@ using System.Collections.Generic; using System.Linq; using System.Text; +using ClosedXML.Excel.Style; namespace ClosedXML.Excel { @@ -23,6 +24,7 @@ public IXLAddress LastCellAddress { get; set; } public Dictionary CellsCollection { get; set; } public List MergedCells { get; set; } + public IXLStyle DefaultStyle { get; set; } // Private diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs index 15a27d0..8bf6982 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLRow.cs @@ -11,13 +11,19 @@ public XLRow(Int32 row, Dictionary cellsCollection, IXLStyle defaultStyle) { FirstCellAddress = new XLAddress(row, 1); - LastCellAddress = new XLAddress(row, XLWorksheet.MaxNumberOfColumns); + LastCellAddress = new XLAddress(row, XLWorksheet.MaxNumberOfColumns); + RowNumber = row; + ColumnNumber = 1; + ColumnLetter = "A"; CellsCollection = cellsCollection; this.style = new XLStyle(this, defaultStyle); this.Height = XLWorkbook.DefaultRowHeight; } public Double Height { get; set; } + public Int32 RowNumber { get; private set; } + public Int32 ColumnNumber { get; private set; } + public String ColumnLetter { get; private set; } #region IXLRange Members @@ -70,7 +76,7 @@ public IXLRange Column(int column) { - var address = new XLAddress(FirstCellAddress.Row, column); + var address = new XLAddress(1, column); return this.Range(address, address); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 31a21cd..a11dc9a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -25,6 +25,9 @@ DefaultCell = new XLCell(defaultAddress, XLWorkbook.DefaultStyle); cellsCollection.Add(defaultAddress, DefaultCell); MergedCells = new List(); + RowNumber = 1; + ColumnNumber = 1; + ColumnLetter = "A"; var tmp = this.Cell(1, 1).Value; this.Name = sheetName; } @@ -55,6 +58,9 @@ } private IXLCell DefaultCell { get; set; } + public Int32 RowNumber { get; private set; } + public Int32 ColumnNumber { get; private set; } + public String ColumnLetter { get; private set; } #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index f2ca730..8bf9770 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -50,10 +50,12 @@ + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/InsertColumns.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/InsertColumns.cs new file mode 100644 index 0000000..4a71a32 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Columns/InsertColumns.cs @@ -0,0 +1,76 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using ClosedXML.Excel.Style; +using System.Drawing; + +namespace ClosedXML_Examples.Columns +{ + public class InsertColumns + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.Worksheets.Add("Insert Columns"); + + foreach (var r in Enumerable.Range(1, 5)) + { + foreach (var c in Enumerable.Range(1, 5)) + { + ws.Cell(r, c).Value = "X"; + ws.Cell(r, c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + } + } + + ws.Column(3).InsertColumnsAfter(2); + ws.Column(1).InsertColumnsBefore(2); + ws.Range("D3:E4").InsertColumnsAfter(2); + ws.Range("B3:C5").InsertColumnsBefore(2); + + workbook.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Program.cs index 03d0f85..f511ee6 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Program.cs @@ -19,6 +19,8 @@ new ColumnSettings().Create(@"c:\ColumnSettings.xlsx"); new RowSettings().Create(@"c:\RowSettings.xlsx"); new MergeCells().Create(@"c:\MergedCells.xlsx"); + new InsertRows().Create(@"c:\InsertRows.xlsx"); + new InsertColumns().Create(@"c:\InsertColumns.xlsx"); } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/InsertRows.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/InsertRows.cs new file mode 100644 index 0000000..349394c --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Rows/InsertRows.cs @@ -0,0 +1,76 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; +using ClosedXML.Excel.Style; +using System.Drawing; + +namespace ClosedXML_Examples.Rows +{ + public class InsertRows + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.Worksheets.Add("Insert Rows"); + + foreach (var r in Enumerable.Range(1, 5)) + { + foreach (var c in Enumerable.Range(1, 5)) + { + ws.Cell(r, c).Value = "X"; + ws.Cell(r, c).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + } + } + + ws.Row(3).InsertRowsBelow(2); + ws.Row(1).InsertRowsAbove(2); + ws.Range("D3:E4").InsertRowsBelow(2); + ws.Range("B3:C5").InsertRowsAbove(2); + + workbook.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleFont.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleFont.cs index 1e157df..d76638c 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleFont.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleFont.cs @@ -17,7 +17,6 @@ var co = 2; var ro = 1; - ws.Cell(++ro, co).Value = "Bold"; ws.Cell(ro, co).Style.Font.Bold = true; @@ -28,9 +27,27 @@ ws.Cell(++ro, co).Value = "FontFamilyNumbering - Script"; ws.Cell(ro, co).Style.Font.FontFamilyNumbering = XLFontFamilyNumberingValues.Script; - ws.Cell(++ro, co).Value = "FontName - Arial"; - ws.Cell(ro, co).Style.Font.FontName = "Arial"; + ws.Cell(++ro, co).Value = "FontName - Stencil"; + ws.Cell(ro, co).Style.Font.FontName = "Stencil"; + ws.Cell(++ro, co).Value = "FontSize - 15"; + ws.Cell(ro, co).Style.Font.FontSize = 15; + + ws.Cell(++ro, co).Value = "Italic - true"; + ws.Cell(ro, co).Style.Font.Italic = true; + + ws.Cell(++ro, co).Value = "Shadow - true"; + ws.Cell(ro, co).Style.Font.Shadow = true; + + ws.Cell(++ro, co).Value = "Strikethrough - true"; + ws.Cell(ro, co).Style.Font.Strikethrough = true; + + ws.Cell(++ro, co).Value = "Underline - Double"; + ws.Cell(ro, co).Style.Font.Underline = XLFontUnderlineValues.Double; + + ws.Cell(++ro, co).Value = "VerticalAlignment - Superscript"; + ws.Cell(ro, co).Style.Font.VerticalAlignment = XLFontVerticalTextAlignmentValues.Superscript; + workbook.SaveAs(filePath); } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleNumberFormat.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleNumberFormat.cs index bb31c2b..e89c3e1 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleNumberFormat.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Styles/StyleNumberFormat.cs @@ -68,7 +68,7 @@ ws.Cell(ro, co).Style.NumberFormat.Format = "$ #,##0.00"; ws.Cell(++ro, co).Value = "12.345"; - ws.Cell(ro, co).Style.NumberFormat.Format = "$ #,##0.00"; + ws.Cell(ro, co).Style.NumberFormat.Format = "0000"; ws.Cell(++ro, co).Value = "12.345"; ws.Cell(ro, co).Style.NumberFormat.NumberFormatId = 3;