diff --git a/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/Excel/Ranges/IXLRange.cs index fbbe958..992ebf9 100644 --- a/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/Excel/Ranges/IXLRange.cs @@ -1,12 +1,14 @@ using System; - namespace ClosedXML.Excel { public enum XLShiftDeletedCells { ShiftCellsUp, ShiftCellsLeft } + public enum XLTransposeOptions { MoveCells, ReplaceCells } + public enum XLSearchContents { Values, Formulas, ValuesAndFormulas } - public interface IXLRange: IXLRangeBase + + public interface IXLRange : IXLRangeBase { /// /// Gets the cell at the specified row and column. @@ -28,6 +30,7 @@ /// The cell's row. /// The cell's column. IXLCell Cell(int row, string column); + /// Gets the cell at the specified address. /// The cell address is relative to the parent range. /// The cell address in the parent range. @@ -36,79 +39,100 @@ /// /// Gets the specified column of the range. /// - /// The range column. - IXLRangeColumn Column(int column); + /// The column number. + /// + IXLRangeColumn Column(int columnNumber); + /// /// Gets the specified column of the range. /// - /// The range column. - IXLRangeColumn Column(string column); + /// Column letter. + IXLRangeColumn Column(string columnLetter); + /// /// Gets the first column of the range. /// IXLRangeColumn FirstColumn(Func predicate = null); + /// /// Gets the first column of the range that contains a cell with a value. /// IXLRangeColumn FirstColumnUsed(Boolean includeFormats, Func predicate = null); + IXLRangeColumn FirstColumnUsed(Func predicate = null); + /// /// Gets the last column of the range. /// IXLRangeColumn LastColumn(Func predicate = null); + /// /// Gets the last column of the range that contains a cell with a value. /// IXLRangeColumn LastColumnUsed(Boolean includeFormats, Func predicate = null); + IXLRangeColumn LastColumnUsed(Func predicate = null); + /// /// Gets a collection of all columns in this range. /// IXLRangeColumns Columns(Func predicate = null); + /// /// Gets a collection of the specified columns in this range. /// /// The first column to return. /// The last column to return. IXLRangeColumns Columns(int firstColumn, int lastColumn); + /// /// Gets a collection of the specified columns in this range. /// /// The first column to return. /// The last column to return. IXLRangeColumns Columns(string firstColumn, string lastColumn); + /// /// Gets a collection of the specified columns in this range, separated by commas. /// e.g. Columns("G:H"), Columns("10:11,13:14"), Columns("P:Q,S:T"), Columns("V") /// /// The columns to return. IXLRangeColumns Columns(string columns); + /// /// Returns the first row that matches the given predicate /// IXLRangeColumn FindColumn(Func predicate); + /// /// Returns the first row that matches the given predicate /// IXLRangeRow FindRow(Func predicate); + /// /// Gets the first row of the range. /// IXLRangeRow FirstRow(Func predicate = null); + /// /// Gets the first row of the range that contains a cell with a value. /// IXLRangeRow FirstRowUsed(Boolean includeFormats, Func predicate = null); + IXLRangeRow FirstRowUsed(Func predicate = null); + /// /// Gets the last row of the range. /// IXLRangeRow LastRow(Func predicate = null); + /// /// Gets the last row of the range that contains a cell with a value. /// IXLRangeRow LastRowUsed(Boolean includeFormats, Func predicate = null); + IXLRangeRow LastRowUsed(Func predicate = null); + /// /// Gets the specified row of the range. /// @@ -124,6 +148,7 @@ /// The last row to return. /// IXLRangeRows Rows(int firstRow, int lastRow); + /// /// Gets a collection of the specified rows in this range, separated by commas. /// e.g. Rows("4:5"), Rows("7:8,10:11"), Rows("13") @@ -182,27 +207,34 @@ /// /// Number of columns to insert. IXLRangeColumns InsertColumnsAfter(int numberOfColumns); + IXLRangeColumns InsertColumnsAfter(int numberOfColumns, Boolean expandRange); + /// /// Inserts X number of columns to the left of this range. /// This range and all cells to the right of this range will be shifted X number of columns. /// /// Number of columns to insert. IXLRangeColumns InsertColumnsBefore(int numberOfColumns); + IXLRangeColumns InsertColumnsBefore(int numberOfColumns, Boolean expandRange); + /// /// Inserts X number of rows on top of this range. /// This range and all cells below this range will be shifted X number of rows. /// /// Number of rows to insert. IXLRangeRows InsertRowsAbove(int numberOfRows); + IXLRangeRows InsertRowsAbove(int numberOfRows, Boolean expandRange); + /// /// Inserts X number of rows below this range. /// All cells below this range will be shifted X number of rows. /// /// Number of rows to insert. IXLRangeRows InsertRowsBelow(int numberOfRows); + IXLRangeRows InsertRowsBelow(int numberOfRows, Boolean expandRange); /// @@ -218,13 +250,17 @@ void Transpose(XLTransposeOptions transposeOption); IXLTable AsTable(); + IXLTable AsTable(String name); + IXLTable CreateTable(); + IXLTable CreateTable(String name); IXLRange RangeUsed(); IXLRange CopyTo(IXLCell target); + IXLRange CopyTo(IXLRangeBase target); IXLSortElements SortRows { get; } @@ -233,9 +269,10 @@ IXLRange Sort(); IXLRange Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + IXLRange Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + IXLRange SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); - IXLRange SetDataType(XLCellValues dataType); @@ -246,9 +283,11 @@ new IXLRange Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); IXLRangeRows RowsUsed(Boolean includeFormats, Func predicate = null); + IXLRangeRows RowsUsed(Func predicate = null); + IXLRangeColumns ColumnsUsed(Boolean includeFormats, Func predicate = null); + IXLRangeColumns ColumnsUsed(Func predicate = null); } } - diff --git a/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/Excel/Ranges/XLRange.cs index 94d53e3..1991c89 100644 --- a/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/Excel/Ranges/XLRange.cs @@ -2,7 +2,6 @@ using System.Collections.Generic; using System.Linq; - namespace ClosedXML.Excel { internal class XLRange : XLRangeBase, IXLRange @@ -23,29 +22,28 @@ SetStyle(xlRangeParameters.DefaultStyle); } - #endregion + #endregion Constructor public XLRangeParameters RangeParameters { get; private set; } #region IXLRange Members - IXLRangeRow IXLRange.Row(Int32 row) { return Row(row); } - IXLRangeColumn IXLRange.Column(Int32 column) + IXLRangeColumn IXLRange.Column(Int32 columnNumber) { - return Column(column); + return Column(columnNumber); } - IXLRangeColumn IXLRange.Column(String column) + IXLRangeColumn IXLRange.Column(String columnLetter) { - return Column(column); + return Column(columnLetter); } - public IXLRangeColumns Columns(Func predicate = null) + public virtual IXLRangeColumns Columns(Func predicate = null) { var retVal = new XLRangeColumns(); Int32 columnCount = ColumnCount(); @@ -69,13 +67,13 @@ return retVal; } - public IXLRangeColumns Columns(String firstColumn, String lastColumn) + public virtual IXLRangeColumns Columns(String firstColumn, String lastColumn) { return Columns(XLHelper.GetColumnNumberFromLetter(firstColumn), XLHelper.GetColumnNumberFromLetter(lastColumn)); } - public IXLRangeColumns Columns(String columns) + public virtual IXLRangeColumns Columns(String columns) { var retVal = new XLRangeColumns(); var columnPairs = columns.Split(','); @@ -274,6 +272,7 @@ { return CreateTable(); } + public XLTable CreateTable() { return new XLTable(this, true, true); @@ -283,6 +282,7 @@ { return CreateTable(name); } + public XLTable CreateTable(String name) { return new XLTable(this, name, true, true); @@ -333,27 +333,27 @@ return this; } - public new IXLRange Sort() { return base.Sort().AsRange(); } - + public new IXLRange Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { return base.Sort(columnsToSortBy, sortOrder, matchCase, ignoreBlanks).AsRange(); } + public new IXLRange Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { return base.Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks).AsRange(); } + public new IXLRange SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { return base.SortLeftToRight(sortOrder, matchCase, ignoreBlanks).AsRange(); } - - #endregion + #endregion IXLRange Members private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { @@ -362,13 +362,14 @@ private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) { - ShiftRows(RangeAddress, range, rowsShifted); + ShiftRows(RangeAddress, range, rowsShifted); } IXLRangeColumn IXLRange.FirstColumn(Func predicate) { return FirstColumn(predicate); } + public XLRangeColumn FirstColumn(Func predicate = null) { if (predicate == null) @@ -390,6 +391,7 @@ { return LastColumn(predicate); } + public XLRangeColumn LastColumn(Func predicate = null) { Int32 columnCount = ColumnCount(); @@ -407,10 +409,11 @@ return null; } - IXLRangeColumn IXLRange.FirstColumnUsed(Func predicate ) + IXLRangeColumn IXLRange.FirstColumnUsed(Func predicate) { return FirstColumnUsed(false, predicate); } + public XLRangeColumn FirstColumnUsed(Func predicate = null) { return FirstColumnUsed(false, predicate); @@ -420,6 +423,7 @@ { return FirstColumnUsed(includeFormats, predicate); } + public XLRangeColumn FirstColumnUsed(Boolean includeFormats, Func predicate = null) { if (predicate == null) @@ -450,6 +454,7 @@ { return LastColumnUsed(false, predicate); } + public XLRangeColumn LastColumnUsed(Func predicate = null) { return LastColumnUsed(false, predicate); @@ -459,6 +464,7 @@ { return LastColumnUsed(includeFormats, predicate); } + public XLRangeColumn LastColumnUsed(Boolean includeFormats, Func predicate = null) { if (predicate == null) @@ -489,6 +495,7 @@ { return FirstRow(predicate); } + public XLRangeRow FirstRow(Func predicate = null) { if (predicate == null) @@ -510,6 +517,7 @@ { return LastRow(predicate); } + public XLRangeRow LastRow(Func predicate = null) { Int32 rowCount = RowCount(); @@ -531,6 +539,7 @@ { return FirstRowUsed(false, predicate); } + public XLRangeRow FirstRowUsed(Func predicate = null) { return FirstRowUsed(false, predicate); @@ -540,6 +549,7 @@ { return FirstRowUsed(includeFormats, predicate); } + public XLRangeRow FirstRowUsed(Boolean includeFormats, Func predicate = null) { if (predicate == null) @@ -572,6 +582,7 @@ { return LastRowUsed(false, predicate); } + public XLRangeRow LastRowUsed(Func predicate = null) { return LastRowUsed(false, predicate); @@ -581,6 +592,7 @@ { return LastRowUsed(includeFormats, predicate); } + public XLRangeRow LastRowUsed(Boolean includeFormats, Func predicate = null) { if (predicate == null) @@ -607,11 +619,11 @@ return null; } - IXLRangeRows IXLRange.RowsUsed(Boolean includeFormats, Func predicate) { return RowsUsed(includeFormats, predicate); } + public XLRangeRows RowsUsed(Boolean includeFormats, Func predicate = null) { XLRangeRows rows = new XLRangeRows(); @@ -627,19 +639,23 @@ } return rows; } + IXLRangeRows IXLRange.RowsUsed(Func predicate) { return RowsUsed(predicate); } + public XLRangeRows RowsUsed(Func predicate = null) { return RowsUsed(false, predicate); } + IXLRangeColumns IXLRange.ColumnsUsed(Boolean includeFormats, Func predicate) { return ColumnsUsed(includeFormats, predicate); } - public XLRangeColumns ColumnsUsed(Boolean includeFormats, Func predicate = null) + + public virtual XLRangeColumns ColumnsUsed(Boolean includeFormats, Func predicate = null) { XLRangeColumns columns = new XLRangeColumns(); Int32 columnCount = ColumnCount(); @@ -654,11 +670,13 @@ } return columns; } + IXLRangeColumns IXLRange.ColumnsUsed(Func predicate) { return ColumnsUsed(predicate); } - public XLRangeColumns ColumnsUsed(Func predicate = null) + + public virtual XLRangeColumns ColumnsUsed(Func predicate = null) { return ColumnsUsed(false, predicate); } @@ -682,34 +700,30 @@ new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), false); } - - - public XLRangeColumn Column(Int32 column) + public virtual XLRangeColumn Column(Int32 columnNumber) { - if (column <= 0 || column > XLHelper.MaxColumnNumber) + if (columnNumber <= 0 || columnNumber > XLHelper.MaxColumnNumber) throw new IndexOutOfRangeException(String.Format("Column number must be between 1 and {0}", XLHelper.MaxColumnNumber)); var firstCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber + column - 1, + RangeAddress.FirstAddress.ColumnNumber + columnNumber - 1, false, false); var lastCellAddress = new XLAddress(Worksheet, RangeAddress.LastAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber + column - 1, + RangeAddress.FirstAddress.ColumnNumber + columnNumber - 1, false, false); return new XLRangeColumn( new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), false); } - public XLRangeColumn Column(String column) + public virtual XLRangeColumn Column(String columnLetter) { - return Column(XLHelper.GetColumnNumberFromLetter(column)); + return Column(XLHelper.GetColumnNumberFromLetter(columnLetter)); } - - private void TransposeRange(int squareSide) { var cellsToInsert = new Dictionary(); @@ -728,7 +742,7 @@ { var oldCell = rngToTranspose.Cell(ro, co); var newKey = rngToTranspose.Cell(co, ro).Address; - // new XLAddress(Worksheet, c.Address.ColumnNumber, c.Address.RowNumber); + // new XLAddress(Worksheet, c.Address.ColumnNumber, c.Address.RowNumber); var newCell = new XLCell(Worksheet, newKey, oldCell.GetStyleId()); newCell.CopyFrom(oldCell, true); cellsToInsert.Add(new XLSheetPoint(newKey.RowNumber, newKey.ColumnNumber), newCell); diff --git a/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/Excel/Ranges/XLRangeColumn.cs index 7449d3c..34710ea 100644 --- a/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -1,9 +1,8 @@ +using System; +using System.Linq; + namespace ClosedXML.Excel { - using System; - using System.Linq; - - internal class XLRangeColumn : XLRangeBase, IXLRangeColumn { #region Constructor @@ -13,18 +12,24 @@ { if (quickLoad) return; - SubscribeToShiftedRows((range, rowsShifted) => this.WorksheetRangeShiftedRows(range, rowsShifted)); - SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); + SubscribeToShiftedRows((range, rowsShifted) => this.WorksheetRangeShiftedRows(range, rowsShifted)); + SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); SetStyle(rangeParameters.DefaultStyle); } - #endregion + public XLRangeColumn(XLRangeParameters rangeParameters, bool quickLoad, IXLTable table) + : this(rangeParameters, quickLoad) + { + this.Table = table; + } + + #endregion Constructor #region IXLRangeColumn Members - IXLCell IXLRangeColumn.Cell(int row) + IXLCell IXLRangeColumn.Cell(int rowNumber) { - return Cell(row); + return Cell(rowNumber); } public new IXLCells Cells(string cellsInColumn) @@ -43,6 +48,22 @@ public void Delete() { + Delete(true); + } + + internal void Delete(Boolean deleteTableField) + { + if (deleteTableField && IsTableColumn()) + { + var table = Table as XLTable; + var firstCellValue = Cell(1).Value.ToString(); + if (!table.FieldNames.ContainsKey(firstCellValue)) + throw new ArgumentException(string.Format("Field {0} not found.", firstCellValue)); + + var field = table.Fields.Cast().Single(f => f.Name == firstCellValue); + field.Delete(false); + } + Delete(XLShiftDeletedCells.ShiftCellsLeft); } @@ -77,7 +98,6 @@ return this; } - public new IXLRangeColumn CopyTo(IXLCell target) { base.CopyTo(target); @@ -166,7 +186,7 @@ return Worksheet.Column(RangeAddress.FirstAddress.ColumnNumber); } - #endregion + #endregion IXLRangeColumn Members public XLCell Cell(int row) { @@ -289,7 +309,7 @@ return ColumnShift(step * -1); } - #endregion + #endregion XLRangeColumn Left #region XLRangeColumn Right @@ -313,29 +333,40 @@ return ColumnShift(step); } - #endregion - + #endregion XLRangeColumn Right public IXLTable AsTable() { + if (IsTableColumn()) + throw new InvalidOperationException("This column is already part of a table."); + using (var asRange = AsRange()) - return asRange.AsTable(); + return asRange.AsTable(); } public IXLTable AsTable(string name) { + if (IsTableColumn()) + throw new InvalidOperationException("This column is already part of a table."); + using (var asRange = AsRange()) return asRange.AsTable(name); } public IXLTable CreateTable() { + if (IsTableColumn()) + throw new InvalidOperationException("This column is already part of a table."); + using (var asRange = AsRange()) return asRange.CreateTable(); } public IXLTable CreateTable(string name) { + if (IsTableColumn()) + throw new InvalidOperationException("This column is already part of a table."); + using (var asRange = AsRange()) return asRange.CreateTable(name); } @@ -351,5 +382,11 @@ return Column(FirstCellUsed(includeFormats), LastCellUsed(includeFormats)); } + internal IXLTable Table { get; set; } + + public Boolean IsTableColumn() + { + return Table != null; + } } } diff --git a/ClosedXML/Excel/Tables/IXLTableField.cs b/ClosedXML/Excel/Tables/IXLTableField.cs index 5bc695c..91ba4e8 100644 --- a/ClosedXML/Excel/Tables/IXLTableField.cs +++ b/ClosedXML/Excel/Tables/IXLTableField.cs @@ -18,11 +18,14 @@ public interface IXLTableField { + IXLRangeColumn Column { get; } Int32 Index { get; } String Name { get; set; } - String TotalsRowLabel { get; set; } String TotalsRowFormulaA1 { get; set; } String TotalsRowFormulaR1C1 { get; set; } XLTotalsRowFunction TotalsRowFunction { get; set; } + String TotalsRowLabel { get; set; } + + void Delete(); } } diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index 076a2a8..6048e33 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -13,12 +13,12 @@ internal bool _showTotalsRow; internal HashSet _uniqueNames; - #endregion + #endregion Private fields #region Constructor public XLTable(XLRange range, Boolean addToTables, Boolean setAutofilter = true) - : base(new XLRangeParameters(range.RangeAddress, range.Style )) + : base(new XLRangeParameters(range.RangeAddress, range.Style)) { InitializeValues(setAutofilter); @@ -45,10 +45,11 @@ AddToTables(range, addToTables); } - #endregion + #endregion Constructor private IXLRangeAddress _lastRangeAddress; private Dictionary _fieldNames = null; + public Dictionary FieldNames { get @@ -73,13 +74,11 @@ if (_fieldNames.ContainsKey(name)) throw new ArgumentException("The header row contains more than one field name '" + name + "'."); - _fieldNames.Add(name, new XLTableField(this, name) {Index = cellPos++ }); + _fieldNames.Add(name, new XLTableField(this, name) { Index = cellPos++ }); } } else { - if (_fieldNames == null) _fieldNames = new Dictionary(); - Int32 colCount = ColumnCount(); for (Int32 i = 1; i <= colCount; i++) { @@ -87,7 +86,7 @@ { var name = "Column" + i; - _fieldNames.Add(name, new XLTableField(this, name) {Index = i - 1 }); + _fieldNames.Add(name, new XLTableField(this, name) { Index = i - 1 }); } } } @@ -100,7 +99,7 @@ _fieldNames = new Dictionary(); Int32 cellPos = 0; - foreach(var name in fieldNames) + foreach (var name in fieldNames) { _fieldNames.Add(name, new XLTableField(this, name) { Index = cellPos++ }); } @@ -116,7 +115,6 @@ _fieldNames.Add(newName, field); } - internal String RelId { get; set; } public IXLTableRange DataRange @@ -131,7 +129,7 @@ if (_showHeaderRow) { range = _showTotalsRow - ? Range(2, 1,RowCount() - 1,ColumnCount()) + ? Range(2, 1, RowCount() - 1, ColumnCount()) : Range(2, 1, RowCount(), ColumnCount()); } else @@ -146,6 +144,7 @@ } private XLAutoFilter _autoFilter; + public XLAutoFilter AutoFilter { get @@ -174,10 +173,13 @@ public Boolean ShowColumnStripes { get; set; } private Boolean _showAutoFilter; - public Boolean ShowAutoFilter { + + public Boolean ShowAutoFilter + { get { return _showHeaderRow && _showAutoFilter; } set { _showAutoFilter = value; } - } + } + public XLTableTheme Theme { get; set; } public String Name @@ -374,9 +376,7 @@ base.Dispose(); } - #endregion - - + #endregion IXLTable Members private void InitializeValues(Boolean setAutofilter) { @@ -413,7 +413,6 @@ Worksheet.Tables.Add(this); } - private String GetUniqueName(String originalName) { String name = originalName; @@ -445,6 +444,7 @@ } internal Boolean _showHeaderRow; + public Boolean ShowHeaderRow { get { return _showHeaderRow; } @@ -475,59 +475,59 @@ } else { - using(var asRange = Worksheet.Range( - RangeAddress.FirstAddress.RowNumber - 1 , + using (var asRange = Worksheet.Range( + RangeAddress.FirstAddress.RowNumber - 1, RangeAddress.FirstAddress.ColumnNumber, RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber )) - using (var firstRow = asRange.FirstRow()) - { - IXLRangeRow rangeRow; - if (firstRow.IsEmpty(true)) - { - rangeRow = firstRow; - RangeAddress.FirstAddress = new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber - 1, - RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.FirstAddress.FixedRow, - RangeAddress.FirstAddress.FixedColumn); - } - else - { - var fAddress = RangeAddress.FirstAddress; - var lAddress = RangeAddress.LastAddress; + using (var firstRow = asRange.FirstRow()) + { + IXLRangeRow rangeRow; + if (firstRow.IsEmpty(true)) + { + rangeRow = firstRow; + RangeAddress.FirstAddress = new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber - 1, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.FirstAddress.FixedRow, + RangeAddress.FirstAddress.FixedColumn); + } + else + { + var fAddress = RangeAddress.FirstAddress; + var lAddress = RangeAddress.LastAddress; - rangeRow = firstRow.InsertRowsBelow(1, false).First(); + rangeRow = firstRow.InsertRowsBelow(1, false).First(); + RangeAddress.FirstAddress = new XLAddress(Worksheet, fAddress.RowNumber, + fAddress.ColumnNumber, + fAddress.FixedRow, + fAddress.FixedColumn); - RangeAddress.FirstAddress = new XLAddress(Worksheet, fAddress.RowNumber, - fAddress.ColumnNumber, - fAddress.FixedRow, - fAddress.FixedColumn); + RangeAddress.LastAddress = new XLAddress(Worksheet, lAddress.RowNumber + 1, + lAddress.ColumnNumber, + lAddress.FixedRow, + lAddress.FixedColumn); + } - RangeAddress.LastAddress = new XLAddress(Worksheet, lAddress.RowNumber + 1, - lAddress.ColumnNumber, - lAddress.FixedRow, - lAddress.FixedColumn); - } - - Int32 co = 1; - foreach (var name in FieldNames.Values.Select(f => f.Name)) - { - rangeRow.Cell(co).SetValue(name); - co++; - } - - } + Int32 co = 1; + foreach (var name in FieldNames.Values.Select(f => f.Name)) + { + rangeRow.Cell(co).SetValue(name); + co++; + } + } } _showHeaderRow = value; } } + public IXLTable SetShowHeaderRow() { return SetShowHeaderRow(true); } + public IXLTable SetShowHeaderRow(Boolean value) { ShowHeaderRow = value; @@ -542,5 +542,60 @@ RangeAddress.LastAddress.FixedColumn); } + public override XLRangeColumn Column(int columnNumber) + { + var column = base.Column(columnNumber); + column.Table = this; + return column; + } + + public override XLRangeColumn Column(string columnName) + { + var column = base.Column(columnName); + column.Table = this; + return column; + } + + public override IXLRangeColumns Columns(int firstColumn, int lastColumn) + { + var columns = base.Columns(firstColumn, lastColumn); + columns.Cast().ForEach(column => column.Table = this); + return columns; + } + + public override IXLRangeColumns Columns(Func predicate = null) + { + var columns = base.Columns(predicate); + columns.Cast().ForEach(column => column.Table = this); + return columns; + } + + public override IXLRangeColumns Columns(string columns) + { + var cols = base.Columns(columns); + cols.Cast().ForEach(column => column.Table = this); + return cols; + } + + public override IXLRangeColumns Columns(string firstColumn, string lastColumn) + { + var columns = base.Columns(firstColumn, lastColumn); + columns.Cast().ForEach(column => column.Table = this); + return columns; + } + + public override XLRangeColumns ColumnsUsed(bool includeFormats, Func predicate = null) + { + var columns = base.ColumnsUsed(includeFormats, predicate); + columns.Cast().ForEach(column => column.Table = this); + return columns; + } + + public override XLRangeColumns ColumnsUsed(Func predicate = null) + { + var columns = base.ColumnsUsed(predicate); + columns.Cast().ForEach(column => column.Table = this); + return columns; + } } } diff --git a/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/Excel/Tables/XLTableField.cs index 89ee2bb..528d7d2 100644 --- a/ClosedXML/Excel/Tables/XLTableField.cs +++ b/ClosedXML/Excel/Tables/XLTableField.cs @@ -1,19 +1,30 @@ using System; +using System.Diagnostics; +using System.Linq; namespace ClosedXML.Excel { - internal class XLTableField: IXLTableField + [DebuggerDisplay("{Name}")] + internal class XLTableField : IXLTableField { - private XLTable table; + internal XLTotalsRowFunction totalsRowFunction; + internal String totalsRowLabel; + private readonly XLTable table; + + private String name; + public XLTableField(XLTable table, String name) { this.table = table; this.name = name; } - public Int32 Index { get; internal set; } + public IXLRangeColumn Column + { + get { return table.Column(this.Index); } + } - private String name; + public Int32 Index { get; internal set; } public String Name { @@ -31,18 +42,6 @@ } } - internal String totalsRowLabel; - public String TotalsRowLabel - { - get { return totalsRowLabel; } - set - { - totalsRowFunction = XLTotalsRowFunction.None; - table.TotalsRow().Cell(Index + 1).SetValue(value); - totalsRowLabel = value; - } - } - public String TotalsRowFormulaA1 { get { return table.TotalsRow().Cell(Index + 1).FormulaA1; } @@ -52,6 +51,7 @@ table.TotalsRow().Cell(Index + 1).FormulaA1 = value; } } + public String TotalsRowFormulaR1C1 { get { return table.TotalsRow().Cell(Index + 1).FormulaR1C1; } @@ -62,7 +62,6 @@ } } - internal XLTotalsRowFunction totalsRowFunction; public XLTotalsRowFunction TotalsRowFunction { get { return totalsRowFunction; } @@ -95,5 +94,31 @@ totalsRowFunction = value; } } + + public String TotalsRowLabel + { + get { return totalsRowLabel; } + set + { + totalsRowFunction = XLTotalsRowFunction.None; + table.TotalsRow().Cell(Index + 1).SetValue(value); + totalsRowLabel = value; + } + } + + public void Delete() + { + Delete(true); + } + + internal void Delete(Boolean deleteUnderlyingRangeColumn) + { + var fields = table.Fields.Cast(); + fields.Where(f => f.Index > this.Index).ForEach(f => f.Index--); + table.FieldNames.Remove(this.Name); + + if (deleteUnderlyingRangeColumn) + (this.Column as XLRangeColumn).Delete(false); + } } } diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index 1190f9e..1a35952 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -42,100 +42,112 @@ dt.Columns.Add("col1", typeof(string)); dt.Columns.Add("col2", typeof(double)); - var wb = new XLWorkbook(); - wb.AddWorksheet(dt); + using (var wb = new XLWorkbook()) + { + wb.AddWorksheet(dt); - using (var ms = new MemoryStream()) - wb.SaveAs(ms, true); + using (var ms = new MemoryStream()) + wb.SaveAs(ms, true); + } } [Test] public void CanSaveTableCreatedFromSingleRow() { - var wb = new XLWorkbook(); - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Title"); - ws.Range("A1").CreateTable(); + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Title"); + ws.Range("A1").CreateTable(); - using (var ms = new MemoryStream()) - wb.SaveAs(ms, true); + using (var ms = new MemoryStream()) + wb.SaveAs(ms, true); + } } [Test] public void CreatingATableFromHeadersPushCellsBelow() { - var wb = new XLWorkbook(); - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Title") - .CellBelow().SetValue("X"); - ws.Range("A1").CreateTable(); + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Title") + .CellBelow().SetValue("X"); + ws.Range("A1").CreateTable(); - Assert.AreEqual(ws.Cell("A2").GetString(), String.Empty); - Assert.AreEqual(ws.Cell("A3").GetString(), "X"); + Assert.AreEqual(ws.Cell("A2").GetString(), String.Empty); + Assert.AreEqual(ws.Cell("A3").GetString(), "X"); + } } [Test] public void Inserting_Column_Sets_Header() { - var wb = new XLWorkbook(); - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Categories") - .CellBelow().SetValue("A") - .CellBelow().SetValue("B") - .CellBelow().SetValue("C"); + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Categories") + .CellBelow().SetValue("A") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C"); - IXLTable table = ws.RangeUsed().CreateTable(); - table.InsertColumnsAfter(1); - Assert.AreEqual("Column2", table.HeadersRow().LastCell().GetString()); + IXLTable table = ws.RangeUsed().CreateTable(); + table.InsertColumnsAfter(1); + Assert.AreEqual("Column2", table.HeadersRow().LastCell().GetString()); + } } [Test] public void SavingLoadingTableWithNewLineInHeader() { - var wb = new XLWorkbook(); - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - string columnName = "Line1" + Environment.NewLine + "Line2"; - ws.FirstCell().SetValue(columnName) - .CellBelow().SetValue("A"); - ws.RangeUsed().CreateTable(); - using (var ms = new MemoryStream()) + using (var wb = new XLWorkbook()) { - wb.SaveAs(ms, true); - var wb2 = new XLWorkbook(ms); - IXLWorksheet ws2 = wb2.Worksheet(1); - IXLTable table2 = ws2.Table(0); - string fieldName = table2.Field(0).Name; - Assert.AreEqual("Line1\nLine2", fieldName); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + string columnName = "Line1" + Environment.NewLine + "Line2"; + ws.FirstCell().SetValue(columnName) + .CellBelow().SetValue("A"); + ws.RangeUsed().CreateTable(); + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + var wb2 = new XLWorkbook(ms); + IXLWorksheet ws2 = wb2.Worksheet(1); + IXLTable table2 = ws2.Table(0); + string fieldName = table2.Field(0).Name; + Assert.AreEqual("Line1\nLine2", fieldName); + } } } [Test] public void SavingLoadingTableWithNewLineInHeader2() { - var wb = new XLWorkbook(); - IXLWorksheet ws = wb.Worksheets.Add("Test"); - - var dt = new DataTable(); - string columnName = "Line1" + Environment.NewLine + "Line2"; - dt.Columns.Add(columnName); - - DataRow dr = dt.NewRow(); - dr[columnName] = "some text"; - dt.Rows.Add(dr); - ws.Cell(1, 1).InsertTable(dt.AsEnumerable()); - - IXLTable table1 = ws.Table(0); - string fieldName1 = table1.Field(0).Name; - Assert.AreEqual(columnName, fieldName1); - - using (var ms = new MemoryStream()) + using (var wb = new XLWorkbook()) { - wb.SaveAs(ms, true); - var wb2 = new XLWorkbook(ms); - IXLWorksheet ws2 = wb2.Worksheet(1); - IXLTable table2 = ws2.Table(0); - string fieldName2 = table2.Field(0).Name; - Assert.AreEqual("Line1\nLine2", fieldName2); + IXLWorksheet ws = wb.Worksheets.Add("Test"); + + var dt = new DataTable(); + string columnName = "Line1" + Environment.NewLine + "Line2"; + dt.Columns.Add(columnName); + + DataRow dr = dt.NewRow(); + dr[columnName] = "some text"; + dt.Rows.Add(dr); + ws.Cell(1, 1).InsertTable(dt.AsEnumerable()); + + IXLTable table1 = ws.Table(0); + string fieldName1 = table1.Field(0).Name; + Assert.AreEqual(columnName, fieldName1); + + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + var wb2 = new XLWorkbook(ms); + IXLWorksheet ws2 = wb2.Worksheet(1); + IXLTable table2 = ws2.Table(0); + string fieldName2 = table2.Field(0).Name; + Assert.AreEqual("Line1\nLine2", fieldName2); + } } } @@ -146,10 +158,12 @@ dt.Columns.Add("col1", typeof(string)); dt.Columns.Add("col2", typeof(double)); - var wb = new XLWorkbook(); - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().InsertTable(dt); - Assert.AreEqual(2, ws.Tables.First().ColumnCount()); + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(dt); + Assert.AreEqual(2, ws.Tables.First().ColumnCount()); + } } [Test] @@ -157,10 +171,12 @@ { var l = new List(); - var wb = new XLWorkbook(); - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().InsertTable(l); - Assert.AreEqual(1, ws.Tables.First().ColumnCount()); + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(l); + Assert.AreEqual(1, ws.Tables.First().ColumnCount()); + } } [Test] @@ -168,10 +184,12 @@ { var l = new List(); - var wb = new XLWorkbook(); - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().InsertTable(l); - Assert.AreEqual(2, ws.Tables.First().ColumnCount()); + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(l); + Assert.AreEqual(2, ws.Tables.First().ColumnCount()); + } } [Test] @@ -183,151 +201,163 @@ new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } }; - var wb = new XLWorkbook(); - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().InsertTable(l); - Assert.AreEqual(4, ws.Tables.First().ColumnCount()); - Assert.AreEqual("FirstColumn", ws.FirstCell().Value); - Assert.AreEqual("SecondColumn", ws.FirstCell().CellRight().Value); - Assert.AreEqual("SomeFieldNotProperty", ws.FirstCell().CellRight().CellRight().Value); - Assert.AreEqual("UnOrderedColumn", ws.FirstCell().CellRight().CellRight().CellRight().Value); + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(l); + Assert.AreEqual(4, ws.Tables.First().ColumnCount()); + Assert.AreEqual("FirstColumn", ws.FirstCell().Value); + Assert.AreEqual("SecondColumn", ws.FirstCell().CellRight().Value); + Assert.AreEqual("SomeFieldNotProperty", ws.FirstCell().CellRight().CellRight().Value); + Assert.AreEqual("UnOrderedColumn", ws.FirstCell().CellRight().CellRight().CellRight().Value); + } } [Test] public void TableInsertAboveFromData() { - var wb = new XLWorkbook(); - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Value"); + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); - IXLTable table = ws.Range("A1:A2").CreateTable(); - table.SetShowTotalsRow() - .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + IXLTable table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; - IXLTableRow row = table.DataRange.FirstRow(); - row.Field("Value").Value = 3; - row = table.DataRange.InsertRowsAbove(1).First(); - row.Field("Value").Value = 2; - row = table.DataRange.InsertRowsAbove(1).First(); - row.Field("Value").Value = 1; + IXLTableRow row = table.DataRange.FirstRow(); + row.Field("Value").Value = 3; + row = table.DataRange.InsertRowsAbove(1).First(); + row.Field("Value").Value = 2; + row = table.DataRange.InsertRowsAbove(1).First(); + row.Field("Value").Value = 1; - Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); - Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); - Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); + Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); + Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + } } [Test] public void TableInsertAboveFromRows() { - var wb = new XLWorkbook(); - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Value"); + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); - IXLTable table = ws.Range("A1:A2").CreateTable(); - table.SetShowTotalsRow() - .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + IXLTable table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; - IXLTableRow row = table.DataRange.FirstRow(); - row.Field("Value").Value = 3; - row = row.InsertRowsAbove(1).First(); - row.Field("Value").Value = 2; - row = row.InsertRowsAbove(1).First(); - row.Field("Value").Value = 1; + IXLTableRow row = table.DataRange.FirstRow(); + row.Field("Value").Value = 3; + row = row.InsertRowsAbove(1).First(); + row.Field("Value").Value = 2; + row = row.InsertRowsAbove(1).First(); + row.Field("Value").Value = 1; - Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); - Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); - Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); + Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); + Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + } } [Test] public void TableInsertBelowFromData() { - var wb = new XLWorkbook(); - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Value"); + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); - IXLTable table = ws.Range("A1:A2").CreateTable(); - table.SetShowTotalsRow() - .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + IXLTable table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; - IXLTableRow row = table.DataRange.FirstRow(); - row.Field("Value").Value = 1; - row = table.DataRange.InsertRowsBelow(1).First(); - row.Field("Value").Value = 2; - row = table.DataRange.InsertRowsBelow(1).First(); - row.Field("Value").Value = 3; + IXLTableRow row = table.DataRange.FirstRow(); + row.Field("Value").Value = 1; + row = table.DataRange.InsertRowsBelow(1).First(); + row.Field("Value").Value = 2; + row = table.DataRange.InsertRowsBelow(1).First(); + row.Field("Value").Value = 3; - Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); - Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); - Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); + Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); + Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + } } [Test] public void TableInsertBelowFromRows() { - var wb = new XLWorkbook(); - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Value"); + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); - IXLTable table = ws.Range("A1:A2").CreateTable(); - table.SetShowTotalsRow() - .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + IXLTable table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; - IXLTableRow row = table.DataRange.FirstRow(); - row.Field("Value").Value = 1; - row = row.InsertRowsBelow(1).First(); - row.Field("Value").Value = 2; - row = row.InsertRowsBelow(1).First(); - row.Field("Value").Value = 3; + IXLTableRow row = table.DataRange.FirstRow(); + row.Field("Value").Value = 1; + row = row.InsertRowsBelow(1).First(); + row.Field("Value").Value = 2; + row = row.InsertRowsBelow(1).First(); + row.Field("Value").Value = 3; - Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); - Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); - Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); + Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); + Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + } } [Test] public void TableShowHeader() { - var wb = new XLWorkbook(); - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Categories") - .CellBelow().SetValue("A") - .CellBelow().SetValue("B") - .CellBelow().SetValue("C"); + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Categories") + .CellBelow().SetValue("A") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C"); - IXLTable table = ws.RangeUsed().CreateTable(); + IXLTable table = ws.RangeUsed().CreateTable(); - Assert.AreEqual("Categories", table.Fields.First().Name); + Assert.AreEqual("Categories", table.Fields.First().Name); - table.SetShowHeaderRow(false); + table.SetShowHeaderRow(false); - Assert.AreEqual("Categories", table.Fields.First().Name); + Assert.AreEqual("Categories", table.Fields.First().Name); - Assert.IsTrue(ws.Cell(1, 1).IsEmpty(true)); - Assert.AreEqual(null, table.HeadersRow()); - Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString()); - Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString()); - Assert.AreEqual("A", table.DataRange.FirstCell().GetString()); - Assert.AreEqual("C", table.DataRange.LastCell().GetString()); + Assert.IsTrue(ws.Cell(1, 1).IsEmpty(true)); + Assert.AreEqual(null, table.HeadersRow()); + Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString()); + Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString()); + Assert.AreEqual("A", table.DataRange.FirstCell().GetString()); + Assert.AreEqual("C", table.DataRange.LastCell().GetString()); - table.SetShowHeaderRow(); - IXLRangeRow headerRow = table.HeadersRow(); - Assert.AreNotEqual(null, headerRow); - Assert.AreEqual("Categories", headerRow.Cell(1).GetString()); + table.SetShowHeaderRow(); + IXLRangeRow headerRow = table.HeadersRow(); + Assert.AreNotEqual(null, headerRow); + Assert.AreEqual("Categories", headerRow.Cell(1).GetString()); - table.SetShowHeaderRow(false); + table.SetShowHeaderRow(false); - ws.FirstCell().SetValue("x"); + ws.FirstCell().SetValue("x"); - table.SetShowHeaderRow(); + table.SetShowHeaderRow(); - Assert.AreEqual("x", ws.FirstCell().GetString()); - Assert.AreEqual("Categories", ws.Cell("A2").GetString()); - Assert.AreNotEqual(null, headerRow); - Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString()); - Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString()); - Assert.AreEqual("A", table.DataRange.FirstCell().GetString()); - Assert.AreEqual("C", table.DataRange.LastCell().GetString()); + Assert.AreEqual("x", ws.FirstCell().GetString()); + Assert.AreEqual("Categories", ws.Cell("A2").GetString()); + Assert.AreNotEqual(null, headerRow); + Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString()); + Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString()); + Assert.AreEqual("A", table.DataRange.FirstCell().GetString()); + Assert.AreEqual("C", table.DataRange.LastCell().GetString()); + } } [Test] @@ -369,5 +399,57 @@ Assert.AreEqual("FirstName", tbl.Field(0).Name); } } + + [Test] + public void CanDeleteTableColumn() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } + }; + + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + var table = ws.FirstCell().InsertTable(l); + + table.Column("C").Delete(); + + Assert.AreEqual(3, table.Fields.Count()); + + Assert.AreEqual("FirstColumn", table.Fields.First().Name); + Assert.AreEqual(0, table.Fields.First().Index); + + Assert.AreEqual("UnOrderedColumn", table.Fields.Last().Name); + Assert.AreEqual(2, table.Fields.Last().Index); + } + } + + [Test] + public void CanDeleteTableField() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } + }; + + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + var table = ws.FirstCell().InsertTable(l); + + table.Field("SomeFieldNotProperty").Delete(); + + Assert.AreEqual(3, table.Fields.Count()); + + Assert.AreEqual("FirstColumn", table.Fields.First().Name); + Assert.AreEqual(0, table.Fields.First().Index); + + Assert.AreEqual("UnOrderedColumn", table.Fields.Last().Name); + Assert.AreEqual(2, table.Fields.Last().Index); + } + } } }