diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index 85f38cd..539fd1e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -198,6 +198,8 @@ + + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index 4da6705..51d7e4c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -67,6 +67,7 @@ private readonly XLWorksheet _worksheet; internal string _cellValue = String.Empty; + private XLComment _comment; internal XLCellValues _dataType; private XLHyperlink _hyperlink; @@ -864,7 +865,9 @@ if (clearOptions == XLClearOptions.Formats || clearOptions == XLClearOptions.ContentsAndFormats) { - DataValidation.Clear(); + if(HasDataValidation) + DataValidation.Clear(); + SetStyle(Worksheet.Style); } @@ -2189,7 +2192,7 @@ get { using(var asRange = AsRange()) - return Worksheet.DataValidations.Any(dv => dv.Ranges.Contains(asRange)); + return Worksheet.DataValidations.Any(dv => dv.Ranges.Contains(asRange) && dv.IsDirty()); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs index 3bcc6f8..a99e010 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs @@ -43,7 +43,7 @@ select match.Groups["Sheet"].Success ? _namedRanges.Workbook.WorksheetsInternal.Worksheet(match.Groups["Sheet"].Value).Range(match.Groups["Range"].Value) as IXLRangeBase - : _namedRanges.Workbook.Worksheets.SelectMany(sheet => sheet.Tables).Single(table => table.Name == match.Groups["Table"].Value).Column(match.Groups["Column"].Value) ) + : _namedRanges.Workbook.Worksheets.SelectMany(sheet => sheet.Tables).Single(table => table.Name == match.Groups["Table"].Value).DataRange.Column(match.Groups["Column"].Value) ) { ranges.Add(rangeToAdd); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 16322ee..84de779 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -269,14 +269,27 @@ return new XLTable(this, name, false); } - public IXLTable CreateTable() + IXLTable IXLRange.CreateTable() { - return new XLTable(this, true); + return CreateTable(); + } + public XLTable CreateTable() + { + return new XLTable(this, true, true); } - public IXLTable CreateTable(String name) + IXLTable IXLRange.CreateTable(String name) { - return new XLTable(this, name, true); + return CreateTable(name); + } + public XLTable CreateTable(String name) + { + return new XLTable(this, name, true, true); + } + + public IXLTable CreateTable(String name, Boolean setAutofilter) + { + return new XLTable(this, name, true, setAutofilter); } public new IXLRange CopyTo(IXLCell target) @@ -417,7 +430,7 @@ RangeAddress.LastAddress.ColumnNumber, includeFormats); - return firstColumnUsed == 0 ? null : Column(firstColumnUsed); + return firstColumnUsed == 0 ? null : Column(firstColumnUsed - RangeAddress.FirstAddress.ColumnNumber + 1); } Int32 columnCount = ColumnCount(); @@ -456,7 +469,7 @@ RangeAddress.LastAddress.ColumnNumber, includeFormats); - return lastColumnUsed == 0 ? null : Column(lastColumnUsed); + return lastColumnUsed == 0 ? null : Column(lastColumnUsed - RangeAddress.FirstAddress.ColumnNumber + 1); } Int32 columnCount = ColumnCount(); @@ -539,7 +552,7 @@ //Int32 rowFromRows = Worksheet.Internals.RowsCollection.FirstRowUsed(includeFormats); - return rowFromCells == 0 ? null : Row(rowFromCells); + return rowFromCells == 0 ? null : Row(rowFromCells - RangeAddress.FirstAddress.RowNumber + 1); } Int32 rowCount = RowCount(); @@ -578,7 +591,7 @@ RangeAddress.LastAddress.ColumnNumber, includeFormats); - return lastRowUsed == 0 ? null : Row(lastRowUsed); + return lastRowUsed == 0 ? null : Row(lastRowUsed - RangeAddress.FirstAddress.RowNumber + 1); } Int32 rowCount = RowCount(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 2cc1369..82b008e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -53,7 +53,7 @@ private Boolean _subscribedToShiftedRows; protected void SubscribeToShiftedRows(RangeShiftedRowsDelegate worksheetRangeShiftedRows) { - if (!Worksheet.EventTrackingEnabled) return; + if (Worksheet == null || !Worksheet.EventTrackingEnabled) return; WorksheetRangeShiftedRows = worksheetRangeShiftedRows; RangeAddress.Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; @@ -63,7 +63,7 @@ private Boolean _subscribedToShiftedColumns; protected void SubscribeToShiftedColumns(RangeShiftedColumnsDelegate worksheetRangeShiftedColumns) { - if (!Worksheet.EventTrackingEnabled) return; + if (Worksheet == null || !Worksheet.EventTrackingEnabled) return; WorksheetRangeShiftedColumns = worksheetRangeShiftedColumns; RangeAddress.Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; @@ -1336,7 +1336,7 @@ } } } - + public Int32 Marker; protected void ShiftRows(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int rowsShifted) { if (thisRangeAddress.IsInvalid || shiftedRange.RangeAddress.IsInvalid) return; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs index dfd954e..9954520 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs @@ -66,7 +66,7 @@ TableStyleDark1 } - public interface IXLTable : IXLRangeBase + public interface IXLTable : IXLRange { string Name { get; set; } Boolean EmphasizeFirstColumn { get; set; } @@ -76,240 +76,13 @@ Boolean ShowTotalsRow { get; set; } Boolean ShowAutoFilter { get; set; } XLTableTheme Theme { get; set; } - IXLSortElements SortRows { get; } - IXLSortElements SortColumns { get; } IXLRangeRow HeadersRow(); IXLRangeRow TotalsRow(); IXLTableField Field(string fieldName); IXLTableField Field(int fieldIndex); IEnumerable Fields { get; } - IXLTableRow FirstRow(Func predicate = null); - IXLTableRow FirstRowUsed(Boolean includeFormats, Func predicate = null); - IXLTableRow FirstRowUsed(Func predicate = null); - IXLTableRow LastRow(Func predicate = null); - IXLTableRow LastRowUsed(Boolean includeFormats, Func predicate = null); - IXLTableRow LastRowUsed(Func predicate = null); - - /// - /// Gets the specified row of the table data. - /// - /// The table row. - IXLTableRow Row(int row); - - IXLTableRows Rows(Func predicate = null); - - /// - /// Gets a collection of the specified data rows in this table. - /// - /// The first row to return. - /// The last row to return. - /// - IXLTableRows Rows(int firstRow, int lastRow); - - /// - /// Gets a collection of the specified data rows in this table, separated by commas. - /// e.g. Rows("4:5"), Rows("7:8,10:11"), Rows("13") - /// - /// The rows to return. - IXLTableRows Rows(string rows); - - 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); - - /// - /// Gets the cell at the specified row and column. - /// The cell address is relative to the parent range. - /// - /// The cell's row. - /// The cell's column. - IXLCell Cell(int row, int column); - - /// - /// Gets the cell at the specified address. - /// - /// The cell address is relative to the parent range. - /// The cell address in the parent range. - IXLCell Cell(string cellAddressInRange); - - /// - /// Gets the cell at the specified row and column. - /// The cell address is relative to the parent range. - /// - /// 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. - IXLCell Cell(IXLAddress cellAddressInRange); - - /// - /// Gets the specified column of the range. - /// - /// The range column. - IXLRangeColumn Column(int column); - - /// - /// Gets the specified column of the range. - /// - /// The range column. - IXLRangeColumn Column(string column); - - IXLRangeColumn FirstColumn(Func predicate = null); - IXLRangeColumn FirstColumnUsed(Boolean includeFormats, Func predicate = null); - IXLRangeColumn FirstColumnUsed(Func predicate = null); - IXLRangeColumn LastColumn(Func predicate = null); - IXLRangeColumn LastColumnUsed(Boolean includeFormats, Func predicate = null); - IXLRangeColumn LastColumnUsed(Func predicate = null); - - 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 specified range. - /// - /// The range boundaries. - IXLRange Range(IXLRangeAddress rangeAddress); - - /// - /// Returns the specified range. - /// - /// e.g. Range("A1"), Range("A1:C2") - /// The range boundaries. - IXLRange Range(string rangeAddress); - - /// - /// Returns the specified range. - /// - /// The first cell in the range. - /// The last cell in the range. - IXLRange Range(IXLCell firstCell, IXLCell lastCell); - - /// - /// Returns the specified range. - /// - /// The first cell address in the range. - /// The last cell address in the range. - IXLRange Range(string firstCellAddress, string lastCellAddress); - - /// - /// Returns the specified range. - /// - /// The first cell address in the range. - /// The last cell address in the range. - IXLRange Range(IXLAddress firstCellAddress, IXLAddress lastCellAddress); - - /// - /// Returns a collection of ranges, separated by commas. - /// - /// e.g. Ranges("A1"), Ranges("A1:C2"), Ranges("A1:B2,D1:D4") - /// The ranges to return. - IXLRanges Ranges(string ranges); - - /// - /// Returns the specified range. - /// - /// The first cell's row of the range to return. - /// The first cell's column of the range to return. - /// The last cell's row of the range to return. - /// The last cell's column of the range to return. - /// . - IXLRange Range(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn); - - /// - /// Gets the number of rows in this range. - /// - int RowCount(); - - /// - /// Gets the number of columns in this range. - /// - int ColumnCount(); - - /// - /// Inserts X number of columns to the right of this range. - /// All cells to the right of this range will be shifted X number of columns. - /// - /// Number of columns to insert. - IXLRangeColumns InsertColumnsAfter(int numberOfColumns); - - IXLRangeColumns InsertColumnsAfter(int numberOfColumns, bool 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, bool 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, bool 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, bool expandRange); - - /// - /// Deletes this range and shifts the surrounding cells accordingly. - /// - /// How to shift the surrounding cells. - void Delete(XLShiftDeletedCells shiftDeleteCells); - - /// - /// Transposes the contents and styles of all cells in this range. - /// - /// How to handle the surrounding cells when transposing the range. - void Transpose(XLTransposeOptions transposeOption); - - IXLTable AsTable(); - IXLTable AsTable(string name); - IXLTable CreateTable(); - IXLTable CreateTable(string name); - - IXLRange RangeUsed(); - - void CopyTo(IXLCell target); - void CopyTo(IXLRangeBase target); + IXLTable SetEmphasizeFirstColumn(); IXLTable SetEmphasizeFirstColumn(Boolean value); IXLTable SetEmphasizeLastColumn(); IXLTable SetEmphasizeLastColumn(Boolean value); @@ -326,9 +99,10 @@ IXLBaseAutoFilter AutoFilter { get; } - IXLTableRows RowsUsed(Boolean includeFormats, Func predicate = null); - IXLTableRows RowsUsed(Func predicate = null); - IXLRangeColumns ColumnsUsed(Boolean includeFormats, Func predicate = null); - IXLRangeColumns ColumnsUsed(Func predicate = null); + + Boolean ShowHeaderRow { get; set; } + IXLTable SetShowHeaderRow(); IXLTable SetShowHeaderRow(Boolean value); + + IXLTableRange DataRange { get; } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRange.cs new file mode 100644 index 0000000..4c21627 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRange.cs @@ -0,0 +1,27 @@ +using System; +using System.Collections.Generic; +namespace ClosedXML.Excel +{ + + public interface IXLTableRange : IXLRange + { + IXLTableRow FirstRow(Func predicate = null); + IXLTableRow FirstRowUsed(Boolean includeFormats, Func predicate = null); + IXLTableRow FirstRowUsed(Func predicate = null); + IXLTableRow LastRow(Func predicate = null); + IXLTableRow LastRowUsed(Boolean includeFormats, Func predicate = null); + IXLTableRow LastRowUsed(Func predicate = null); + + new IXLTableRow Row(int row); + IXLTableRows Rows(Func predicate = null); + new IXLTableRows Rows(int firstRow, int lastRow); + new IXLTableRows Rows(string rows); + IXLTableRows RowsUsed(Boolean includeFormats, Func predicate = null); + IXLTableRows RowsUsed(Func predicate = null); + + IXLTable Table { get; } + + new IXLTableRows InsertRowsAbove(int numberOfRows); + new IXLTableRows InsertRowsBelow(int numberOfRows); + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs index 83eca6a..ac3ce9c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs @@ -20,5 +20,8 @@ /// /// Specify what you want to clear. new IXLTableRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + + new IXLTableRows InsertRowsAbove(int numberOfRows); + new IXLTableRows InsertRowsBelow(int numberOfRows); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs index 937a201..9b354df 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs @@ -9,7 +9,7 @@ { #region Private fields - private readonly Dictionary _fieldNames = new Dictionary(); + public readonly Dictionary FieldNames = new Dictionary(); private readonly Dictionary _fields = new Dictionary(); private string _name; internal bool _showTotalsRow; @@ -19,10 +19,10 @@ #region Constructor - public XLTable(XLRange range, Boolean addToTables) + public XLTable(XLRange range, Boolean addToTables, Boolean setAutofilter = true) : base(range.RangeParameters) { - InitializeValues(); + InitializeValues(setAutofilter); Int32 id = 1; while (true) @@ -38,10 +38,10 @@ } } - public XLTable(XLRange range, String name, Boolean addToTables) + public XLTable(XLRange range, String name, Boolean addToTables, Boolean setAutofilter = true) : base(range.RangeParameters) { - InitializeValues(); + InitializeValues(setAutofilter); Name = name; AddToTables(range, addToTables); @@ -51,13 +51,14 @@ public String RelId { get; set; } - public IXLRange DataRange + public IXLTableRange DataRange { get { - return _showTotalsRow + var range = _showTotalsRow ? Range(2, 1, RowCount() - 1, ColumnCount()) : Range(2, 1, RowCount(), ColumnCount()); + return new XLTableRange(range, this); } } @@ -69,7 +70,12 @@ public Boolean EmphasizeLastColumn { get; set; } public Boolean ShowRowStripes { get; set; } public Boolean ShowColumnStripes { get; set; } - public Boolean ShowAutoFilter { get; set; } + + private Boolean _showAutoFilter; + public Boolean ShowAutoFilter { + get { return _showHeaderRow && _showAutoFilter; } + set { _showAutoFilter = value; } + } public XLTableTheme Theme { get; set; } public String Name @@ -112,281 +118,12 @@ public IXLRangeRow HeadersRow() { - return new XLTableRow(this, base.FirstRow()); + return ShowHeaderRow ? FirstRow() : null; } public IXLRangeRow TotalsRow() { - if (ShowTotalsRow) - return new XLTableRow(this, base.LastRow()); - - throw new InvalidOperationException("Cannot access TotalsRow if ShowTotals property is false"); - } - - IXLTableRow IXLTable.FirstRow(Func predicate) - { - return FirstRow(predicate); - } - public XLTableRow FirstRow(Func predicate = null) - { - using (var dataRange = DataRange) - { - if (predicate == null) - return new XLTableRow(this, (dataRange.FirstRow() as XLRangeRow)); - - Int32 rowCount = dataRange.RowCount(); - - for (Int32 ro = 1; ro <= rowCount; ro++) - { - var row = new XLTableRow(this, (dataRange.Row(ro) as XLRangeRow)); - if (predicate(row)) return row; - - row.Dispose(); - } - } - return null; - } - - IXLTableRow IXLTable.FirstRowUsed(Func predicate) - { - return FirstRowUsed(false, predicate); - } - public XLTableRow FirstRowUsed(Func predicate = null) - { - return FirstRowUsed(false, predicate); - } - - IXLTableRow IXLTable.FirstRowUsed(Boolean includeFormats, Func predicate) - { - return FirstRowUsed(includeFormats, predicate); - } - public XLTableRow FirstRowUsed(Boolean includeFormats, Func predicate = null) - { - using (var dataRange = DataRange) - { - if (predicate == null) - return new XLTableRow(this, (dataRange.FirstRowUsed(includeFormats) as XLRangeRow)); - - Int32 rowCount = dataRange.RowCount(); - - for (Int32 ro = 1; ro <= rowCount; ro++) - { - var row = new XLTableRow(this, (dataRange.Row(ro) as XLRangeRow)); - - if (!row.IsEmpty(includeFormats) && predicate(row)) - return row; - row.Dispose(); - } - } - - return null; - } - - - - - IXLTableRow IXLTable.LastRow(Func predicate) - { - return LastRow(predicate); - } - public XLTableRow LastRow(Func predicate = null) - { - using (var dataRange = DataRange) - { - if (predicate == null) - return new XLTableRow(this, (dataRange.LastRow() as XLRangeRow)); - - Int32 rowCount = dataRange.RowCount(); - - for (Int32 ro = rowCount; ro >= 1; ro--) - { - var row = new XLTableRow(this, (dataRange.Row(ro) as XLRangeRow)); - if (predicate(row)) return row; - - row.Dispose(); - } - } - return null; - } - - IXLTableRow IXLTable.LastRowUsed(Func predicate) - { - return LastRowUsed(false, predicate); - } - public XLTableRow LastRowUsed(Func predicate = null) - { - return LastRowUsed(false, predicate); - } - - IXLTableRow IXLTable.LastRowUsed(Boolean includeFormats, Func predicate) - { - return LastRowUsed(includeFormats, predicate); - } - public XLTableRow LastRowUsed(Boolean includeFormats, Func predicate = null) - { - using (var dataRange = DataRange) - { - if (predicate == null) - return new XLTableRow(this, (dataRange.LastRowUsed(includeFormats) as XLRangeRow)); - - Int32 rowCount = dataRange.RowCount(); - - for (Int32 ro = rowCount; ro >= 1; ro--) - { - var row = new XLTableRow(this, (dataRange.Row(ro) as XLRangeRow)); - - if (!row.IsEmpty(includeFormats) && predicate(row)) - return row; - row.Dispose(); - } - } - - return null; - } - - IXLTableRow IXLTable.Row(int row) - { - return Row(row); - } - - public new IXLTableRows Rows(Func predicate = null) - { - var retVal = new XLTableRows(Worksheet.Style); - using (var dataRange = DataRange) - { - Int32 rowCount = dataRange.RowCount(); - - for (int r = 1; r <= rowCount; r++) - { - var row = Row(r); - if (predicate == null || predicate(row)) - retVal.Add(row); - else - row.Dispose(); - } - } - return retVal; - } - - public new IXLTableRows Rows(int firstRow, int lastRow) - { - var retVal = new XLTableRows(Worksheet.Style); - - for (int ro = firstRow; ro <= lastRow; ro++) - retVal.Add(Row(ro)); - return retVal; - } - - public new IXLTableRows Rows(string rows) - { - var retVal = new XLTableRows(Worksheet.Style); - var rowPairs = rows.Split(','); - foreach (string tPair in rowPairs.Select(pair => pair.Trim())) - { - String firstRow; - String lastRow; - if (tPair.Contains(':') || tPair.Contains('-')) - { - var rowRange = ExcelHelper.SplitRange(tPair); - - firstRow = rowRange[0]; - lastRow = rowRange[1]; - } - else - { - firstRow = tPair; - lastRow = tPair; - } - foreach (IXLTableRow row in Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))) - retVal.Add(row); - } - return retVal; - } - - public new IXLRangeColumn Column(Int32 column) - { - return DataRange.Column(column); - } - - public new IXLRangeColumn Column(String column) - { - if (ExcelHelper.IsValidColumn(column)) - { - Int32 coNum = ExcelHelper.GetColumnNumberFromLetter(column); - return coNum > ColumnCount() ? DataRange.Column(GetFieldIndex(column) + 1) : DataRange.Column(coNum); - } - - return DataRange.Column(GetFieldIndex(column) + 1); - } - - public new IXLRangeColumns Columns(Func predicate = null) - { - return DataRange.Columns(); - } - - public new IXLRangeColumns Columns(Int32 firstColumn, Int32 lastColumn) - { - return DataRange.Columns(firstColumn, lastColumn); - } - - public new IXLRangeColumns Columns(String firstColumn, String lastColumn) - { - return DataRange.Columns(firstColumn, lastColumn); - } - - public new IXLRangeColumns Columns(String columns) - { - return DataRange.Columns(columns); - } - - IXLCell IXLTable.Cell(int row, int column) - { - return Cell(row, column); - } - - IXLCell IXLTable.Cell(string cellAddressInRange) - { - return Cell(cellAddressInRange); - } - - IXLCell IXLTable.Cell(int row, string column) - { - return Cell(row, column); - } - - IXLCell IXLTable.Cell(IXLAddress cellAddressInRange) - { - return Cell(cellAddressInRange); - } - - IXLRange IXLTable.Range(IXLRangeAddress rangeAddress) - { - return Range(rangeAddress); - } - - IXLRange IXLTable.Range(string rangeAddress) - { - return Range(rangeAddress); - } - - IXLRange IXLTable.Range(IXLCell firstCell, IXLCell lastCell) - { - return Range(firstCell, lastCell); - } - - IXLRange IXLTable.Range(string firstCellAddress, string lastCellAddress) - { - return Range(firstCellAddress, lastCellAddress); - } - - IXLRange IXLTable.Range(IXLAddress firstCellAddress, IXLAddress lastCellAddress) - { - return Range(firstCellAddress, lastCellAddress); - } - - IXLRange IXLTable.Range(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn) - { - return Range(firstCellRow, firstCellColumn, lastCellRow, lastCellColumn); + return ShowTotalsRow ? LastRow() : null; } public IXLTableField Field(String fieldName) @@ -491,37 +228,6 @@ return this; } - - IXLRangeColumn IXLTable.FirstColumn(Func predicate) - { - return FirstColumn(predicate); - } - - IXLRangeColumn IXLTable.LastColumn(Func predicate) - { - return LastColumn(predicate); - } - - IXLRangeColumn IXLTable.FirstColumnUsed(Func predicate) - { - return FirstColumnUsed(false, predicate); - } - - IXLRangeColumn IXLTable.FirstColumnUsed(Boolean includeFormats, Func predicate) - { - return FirstColumnUsed(includeFormats, predicate); - } - - IXLRangeColumn IXLTable.LastColumnUsed(Func predicate) - { - return LastColumnUsed(false, predicate); - } - - IXLRangeColumn IXLTable.LastColumnUsed(Boolean includeFormats, Func predicate) - { - return LastColumnUsed(includeFormats, predicate); - } - public new IXLRange Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { @@ -575,23 +281,21 @@ #endregion - public new XLTableRow Row(int row) - { - if (row <= 0 || row > ExcelHelper.MaxRowNumber) - { - throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", - ExcelHelper.MaxRowNumber)); - } - return new XLTableRow(this, base.Row(row + 1)); - } - private void InitializeValues() + private void InitializeValues(Boolean setAutofilter) { ShowRowStripes = true; - ShowAutoFilter = true; + _showHeaderRow = true; Theme = XLTableTheme.TableStyleLight9; - AutoFilter = new XLAutoFilter {Range = AsRange()}; + if (setAutofilter) + InitializeAutoFilter(); + } + + public void InitializeAutoFilter() + { + AutoFilter = new XLAutoFilter { Range = AsRange() }; + ShowAutoFilter = true; } private void AddToTables(XLRange range, Boolean addToTables) @@ -630,8 +334,8 @@ public Int32 GetFieldIndex(String name) { - if (_fieldNames.ContainsKey(name)) - return _fieldNames[name].Index; + if (FieldNames.ContainsKey(name)) + return FieldNames[name].Index; var headersRow = HeadersRow(); Int32 cellCount = headersRow.CellCount(); @@ -639,62 +343,106 @@ { if (!headersRow.Cell(cellPos).GetString().Equals(name)) continue; - if (_fieldNames.ContainsKey(name)) + if (FieldNames.ContainsKey(name)) { throw new ArgumentException("The header row contains more than one field name '" + name + "'."); } - _fieldNames.Add(name, Field(cellPos - 1)); + FieldNames.Add(name, Field(cellPos - 1)); } - if (_fieldNames.ContainsKey(name)) - return _fieldNames[name].Index; + if (FieldNames.ContainsKey(name)) + return FieldNames[name].Index; throw new ArgumentOutOfRangeException("The header row doesn't contain field name '" + name + "'."); } - IXLRangeColumns IXLTable.ColumnsUsed(Boolean includeFormats, Func predicate) + internal Boolean _showHeaderRow; + public Boolean ShowHeaderRow { - return ColumnsUsed(includeFormats, predicate); - } - - IXLRangeColumns IXLTable.ColumnsUsed(Func predicate) - { - return ColumnsUsed(predicate); - } - - IXLTableRows IXLTable.RowsUsed(Boolean includeFormats, Func predicate) - { - return RowsUsed(includeFormats, predicate); - } - public IXLTableRows RowsUsed(Boolean includeFormats, Func predicate = null) - { - var rows = new XLTableRows(Worksheet.Style); - Int32 rowCount; - if (_showTotalsRow) - rowCount = RowCount() - 1; - else - rowCount = RowCount(); - - for (Int32 ro = 1; ro <= rowCount; ro++) + get { return _showHeaderRow; } + set { - var row = Row(ro); + if (_showHeaderRow == value) return; - if (!row.IsEmpty(includeFormats) && (predicate == null || predicate(row))) - rows.Add(row); + if (_showHeaderRow) + { + var headersRow = HeadersRow(); + _uniqueNames = new HashSet(); + Int32 co = 1; + foreach (IXLCell c in headersRow.Cells()) + { + if (StringExtensions.IsNullOrWhiteSpace(((XLCell)c).InnerText)) + c.Value = GetUniqueName("Column" + co.ToStringLookup()); + _uniqueNames.Add(c.GetString()); + co++; + } + _uniqueNames.ForEach(n=>AddField(n)); + headersRow.Clear(); + + } else - row.Dispose(); - } - return rows; - } - + { + using(var asRange = AsRange()) + using (var firstRow = asRange.FirstRow()) + { + IXLRangeRow rangeRow; + if (firstRow.IsEmpty(true)) + rangeRow = firstRow; + else + { + rangeRow = firstRow.InsertRowsBelow(1).First(); + + RangeAddress.FirstAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber + 1, + RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.FirstAddress.FixedRow, + RangeAddress.FirstAddress.FixedColumn); - IXLTableRows IXLTable.RowsUsed(Func predicate) - { - return RowsUsed(predicate); + RangeAddress.LastAddress = new XLAddress(Worksheet, RangeAddress.LastAddress.RowNumber, + RangeAddress.LastAddress.ColumnNumber, + RangeAddress.LastAddress.FixedRow, + RangeAddress.LastAddress.FixedColumn); + } + + Int32 co = 1; + foreach (var name in FieldNames.Keys) + { + rangeRow.Cell(co).SetValue(name); + co++; + } + + } + } + _showHeaderRow = value; + } } - public IXLTableRows RowsUsed(Func predicate = null) + public IXLTable SetShowHeaderRow() { - return RowsUsed(false, predicate); + return SetShowHeaderRow(true); + } + public IXLTable SetShowHeaderRow(Boolean value) + { + ShowHeaderRow = value; + return this; + } + + public XLTable AddField(String name) + { + var field = new XLTableField(this) {Index = _fields.Count, Name = name}; + if (!_fields.ContainsKey(_fields.Count)) + _fields.Add(_fields.Count, field); + + if (!FieldNames.ContainsKey(name)) + FieldNames.Add(name, field); + + return this; + } + + public void ExpandTableRows(Int32 rows) + { + RangeAddress.LastAddress = new XLAddress(Worksheet, RangeAddress.LastAddress.RowNumber + rows, + RangeAddress.LastAddress.ColumnNumber, + RangeAddress.LastAddress.FixedRow, + RangeAddress.LastAddress.FixedColumn); } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRange.cs new file mode 100644 index 0000000..5c638f7 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRange.cs @@ -0,0 +1,252 @@ +using System; + +namespace ClosedXML.Excel +{ + using System.Linq; + + internal class XLTableRange : XLRange, IXLTableRange + { + private readonly XLTable _table; + private readonly XLRange _range; + public XLTableRange(XLRange range, XLTable table):base(range.RangeParameters) + { + _table = table; + _range = range; + } + + IXLTableRow IXLTableRange.FirstRow(Func predicate) + { + return FirstRow(predicate); + } + public XLTableRow FirstRow(Func predicate = null) + { + if (predicate == null) + return new XLTableRow(this, (_range.FirstRow())); + + Int32 rowCount = _range.RowCount(); + + for (Int32 ro = 1; ro <= rowCount; ro++) + { + var row = new XLTableRow(this, (_range.Row(ro))); + if (predicate(row)) return row; + + row.Dispose(); + } + + return null; + } + + IXLTableRow IXLTableRange.FirstRowUsed(Func predicate) + { + return FirstRowUsed(false, predicate); + } + public XLTableRow FirstRowUsed(Func predicate = null) + { + return FirstRowUsed(false, predicate); + } + + IXLTableRow IXLTableRange.FirstRowUsed(Boolean includeFormats, Func predicate) + { + return FirstRowUsed(includeFormats, predicate); + } + public XLTableRow FirstRowUsed(Boolean includeFormats, Func predicate = null) + { + if (predicate == null) + return new XLTableRow(this, (_range.FirstRowUsed(includeFormats))); + + Int32 rowCount = _range.RowCount(); + + for (Int32 ro = 1; ro <= rowCount; ro++) + { + var row = new XLTableRow(this, (_range.Row(ro))); + + if (!row.IsEmpty(includeFormats) && predicate(row)) + return row; + row.Dispose(); + } + + return null; + } + + + IXLTableRow IXLTableRange.LastRow(Func predicate) + { + return LastRow(predicate); + } + public XLTableRow LastRow(Func predicate = null) + { + if (predicate == null) + return new XLTableRow(this, (_range.LastRow())); + + Int32 rowCount = _range.RowCount(); + + for (Int32 ro = rowCount; ro >= 1; ro--) + { + var row = new XLTableRow(this, (_range.Row(ro))); + if (predicate(row)) return row; + + row.Dispose(); + } + return null; + } + + IXLTableRow IXLTableRange.LastRowUsed(Func predicate) + { + return LastRowUsed(false, predicate); + } + public XLTableRow LastRowUsed(Func predicate = null) + { + return LastRowUsed(false, predicate); + } + + IXLTableRow IXLTableRange.LastRowUsed(Boolean includeFormats, Func predicate) + { + return LastRowUsed(includeFormats, predicate); + } + public XLTableRow LastRowUsed(Boolean includeFormats, Func predicate = null) + { + if (predicate == null) + return new XLTableRow(this, (_range.LastRowUsed(includeFormats))); + + Int32 rowCount = _range.RowCount(); + + for (Int32 ro = rowCount; ro >= 1; ro--) + { + var row = new XLTableRow(this, (_range.Row(ro))); + + if (!row.IsEmpty(includeFormats) && predicate(row)) + return row; + row.Dispose(); + } + + return null; + } + + IXLTableRow IXLTableRange.Row(int row) + { + return Row(row); + } + public new XLTableRow Row(int row) + { + if (row <= 0 || row > ExcelHelper.MaxRowNumber) + { + throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", + ExcelHelper.MaxRowNumber)); + } + + return new XLTableRow(this, base.Row(row)); + } + + public IXLTableRows Rows(Func predicate = null) + { + var retVal = new XLTableRows(Worksheet.Style); + Int32 rowCount = _range.RowCount(); + + for (int r = 1; r <= rowCount; r++) + { + var row = Row(r); + if (predicate == null || predicate(row)) + retVal.Add(row); + else + row.Dispose(); + } + return retVal; + } + + public new IXLTableRows Rows(int firstRow, int lastRow) + { + var retVal = new XLTableRows(Worksheet.Style); + + for (int ro = firstRow; ro <= lastRow; ro++) + retVal.Add(Row(ro)); + return retVal; + } + + public new IXLTableRows Rows(string rows) + { + var retVal = new XLTableRows(Worksheet.Style); + var rowPairs = rows.Split(','); + foreach (string tPair in rowPairs.Select(pair => pair.Trim())) + { + String firstRow; + String lastRow; + if (tPair.Contains(':') || tPair.Contains('-')) + { + var rowRange = ExcelHelper.SplitRange(tPair); + + firstRow = rowRange[0]; + lastRow = rowRange[1]; + } + else + { + firstRow = tPair; + lastRow = tPair; + } + foreach (IXLTableRow row in Rows(Int32.Parse(firstRow), Int32.Parse(lastRow))) + retVal.Add(row); + } + return retVal; + } + + IXLTableRows IXLTableRange.RowsUsed(Boolean includeFormats, Func predicate) + { + return RowsUsed(includeFormats, predicate); + } + public IXLTableRows RowsUsed(Boolean includeFormats, Func predicate = null) + { + var rows = new XLTableRows(Worksheet.Style); + Int32 rowCount = RowCount(); + + for (Int32 ro = 1; ro <= rowCount; ro++) + { + var row = Row(ro); + + if (!row.IsEmpty(includeFormats) && (predicate == null || predicate(row))) + rows.Add(row); + else + row.Dispose(); + } + return rows; + } + + IXLTableRows IXLTableRange.RowsUsed(Func predicate) + { + return RowsUsed(predicate); + } + public IXLTableRows RowsUsed(Func predicate = null) + { + return RowsUsed(false, predicate); + } + + IXLTable IXLTableRange.Table { get { return _table; } } + public XLTable Table { get { return _table; } } + + public new IXLTableRows InsertRowsAbove(int numberOfRows) + { + var rows = new XLTableRows(Worksheet.Style); + var inserted = base.InsertRowsAbove(numberOfRows); + inserted.ForEach(r => rows.Add(new XLTableRow(this, r as XLRangeRow))); + _table.ExpandTableRows(numberOfRows); + return rows; + } + public new IXLTableRows InsertRowsBelow(int numberOfRows) + { + var rows = new XLTableRows(Worksheet.Style); + var inserted = base.InsertRowsBelow(numberOfRows); + inserted.ForEach(r => rows.Add(new XLTableRow(this, r as XLRangeRow))); + _table.ExpandTableRows(numberOfRows); + return rows; + } + + public new IXLRangeColumn Column(String column) + { + if (ExcelHelper.IsValidColumn(column)) + { + Int32 coNum = ExcelHelper.GetColumnNumberFromLetter(column); + return coNum > ColumnCount() ? Column(_table.GetFieldIndex(column) + 1) : Column(coNum); + } + + return Column(_table.GetFieldIndex(column) + 1); + } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs index 17b332c..7c82944 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs @@ -4,12 +4,13 @@ { internal class XLTableRow : XLRangeRow, IXLTableRow { - private readonly XLTable _table; + private readonly XLTableRange _tableRange; - public XLTableRow(XLTable table, XLRangeRow rangeRow) + public XLTableRow(XLTableRange tableRange, XLRangeRow rangeRow) : base(rangeRow.RangeParameters, false) { - _table = table; + Dispose(); + _tableRange = tableRange; } #region IXLTableRow Members @@ -21,7 +22,7 @@ public IXLCell Field(String name) { - Int32 fieldIndex = _table.GetFieldIndex(name); + Int32 fieldIndex = _tableRange.Table.GetFieldIndex(name); return Cell(fieldIndex + 1); } @@ -40,7 +41,7 @@ private XLTableRow RowShift(Int32 rowsToShift) { - return _table.Row(RowNumber() + rowsToShift); + return _tableRange.Row(RowNumber() + rowsToShift); } #region XLTableRow Above @@ -96,5 +97,28 @@ base.Clear(clearOptions); return this; } + + public new IXLTableRows InsertRowsAbove(int numberOfRows) + { + var rows = new XLTableRows(Worksheet.Style); + var inserted = base.InsertRowsAbove(numberOfRows); + inserted.ForEach(r => rows.Add(new XLTableRow(_tableRange, r as XLRangeRow))); + _tableRange.Table.ExpandTableRows(numberOfRows); + return rows; + } + public new IXLTableRows InsertRowsBelow(int numberOfRows) + { + var rows = new XLTableRows(Worksheet.Style); + var inserted = base.InsertRowsBelow(numberOfRows); + inserted.ForEach(r => rows.Add(new XLTableRow(_tableRange, r as XLRangeRow))); + _tableRange.Table.ExpandTableRows(numberOfRows); + return rows; + } + + public new void Delete() + { + Delete(XLShiftDeletedCells.ShiftCellsUp); + _tableRange.Table.ExpandTableRows(-1); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 961e9d4..9445601 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -212,15 +212,17 @@ { var dTable = tablePart.Table; string reference = dTable.Reference.Value; - IXLTable xlTable; - //if (dTable.HeaderRowCount != null && dTable.HeaderRowCount == 0) - //{ - - //} - //else - //{ - xlTable = ws.Range(reference).CreateTable(dTable.Name); - //} + XLTable xlTable = ws.Range(reference).CreateTable(dTable.Name, false) as XLTable; + if (dTable.HeaderRowCount != null && dTable.HeaderRowCount == 0) + { + xlTable._showHeaderRow = false; + foreach (var tableColumn in dTable.TableColumns.Cast()) + xlTable.AddField(tableColumn.Name); + } + else + { + xlTable.InitializeAutoFilter(); + } if (dTable.TotalsRowCount != null && dTable.TotalsRowCount.Value > 0) ((XLTable) xlTable)._showTotalsRow = true; @@ -265,13 +267,13 @@ if (tableColumn.TotalsRowLabel != null) xlTable.Field(tableColumn.Name.Value).TotalsRowLabel = tableColumn.TotalsRowLabel.Value; } - - xlTable.AutoFilter.Range = xlTable.Worksheet.Range( + if (xlTable.AutoFilter != null) + xlTable.AutoFilter.Range = xlTable.Worksheet.Range( xlTable.RangeAddress.FirstAddress.RowNumber, xlTable.RangeAddress.FirstAddress.ColumnNumber, xlTable.RangeAddress.LastAddress.RowNumber - 1, xlTable.RangeAddress.LastAddress.ColumnNumber); } - else - xlTable.AutoFilter.Range = xlTable.Worksheet.Range(xlTable.RangeAddress); + else if (xlTable.AutoFilter != null) + xlTable.AutoFilter.Range = xlTable.Worksheet.Range(xlTable.RangeAddress); } #endregion diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index b7e07b3..7aa6233 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -684,7 +684,9 @@ var newRichStrings = new Dictionary(); foreach (XLCell c in Worksheets.Cast().SelectMany(w => w.Internals.CellsCollection.GetCells().Where(c => c.DataType == XLCellValues.Text && c.ShareString - && c.InnerText.Length > 0))) + && c.InnerText.Length > 0 + && StringExtensions.IsNullOrWhiteSpace(c.FormulaA1) + ))) { if (c.HasRichText) { @@ -1593,17 +1595,25 @@ Reference = reference }; + if (!xlTable.ShowHeaderRow) + table.HeaderRowCount = 0; + if (xlTable.ShowTotalsRow) table.TotalsRowCount = 1; else table.TotalsRowShown = false; var tableColumns1 = new TableColumns {Count = (UInt32)xlTable.ColumnCount()}; + IEnumerable names; + if (xlTable.ShowHeaderRow) + names = xlTable.HeadersRow().Cells().Select(c => c.GetString()); + else + names = xlTable.FieldNames.Keys; + UInt32 columnId = 0; - foreach (IXLCell cell in xlTable.HeadersRow().Cells()) + foreach (var fieldName in names) { columnId++; - String fieldName = cell.GetString(); var xlField = xlTable.Field(fieldName); var tableColumn1 = new TableColumn { diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs index 4935de4..97084ef 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/UsingTables.cs @@ -66,7 +66,7 @@ Int32 columnWithNames = columnWithHeaders + 2; currentRow = table.RangeAddress.FirstAddress.RowNumber; // reset the currentRow ws.Cell(currentRow, columnWithNames).Value = "Names"; - foreach (var row in table.Rows()) + foreach (var row in table.DataRange.Rows()) { currentRow++; var fName = row.Field("FName").GetString(); // Notice how we're calling the cell by field name diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index 94930e5..5aef33d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -646,6 +646,9 @@ Excel\Tables\IXLTableField.cs + + Excel\Tables\IXLTableRange.cs + Excel\Tables\IXLTableRow.cs @@ -661,6 +664,9 @@ Excel\Tables\XLTableField.cs + + Excel\Tables\XLTableRange.cs + Excel\Tables\XLTableRow.cs diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj index 3f1f0f6..27ee61b 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -86,6 +86,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Tables/TablesTests.cs new file mode 100644 index 0000000..3b27e00 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -0,0 +1,160 @@ +using ClosedXML.Excel; +using Microsoft.VisualStudio.TestTools.UnitTesting; +using System; +using System.Linq; + +namespace ClosedXML_Tests.Excel +{ + /// + /// Summary description for UnitTest1 + /// + [TestClass] + public class TablesTests + { + + [TestMethod] + public void TableShowHeader() + { + var wb = new XLWorkbook(); + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Categories") + .CellBelow().SetValue("A") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C"); + + ws.RangeUsed().CreateTable().SetShowHeaderRow(false); + + var table = ws.Tables.First(); + + 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(); + var headerRow = table.HeadersRow(); + Assert.AreNotEqual(null, headerRow); + Assert.AreEqual("Categories", headerRow.Cell(1).GetString()); + + table.SetShowHeaderRow(false); + ws.FirstCell().SetValue("x"); + 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()); + + } + + + [TestMethod] + public void TableInsertBelowFromRows() + { + var wb = new XLWorkbook(); + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); + + var table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + + var 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()); + + //wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); + + } + + [TestMethod] + public void TableInsertBelowFromData() + { + var wb = new XLWorkbook(); + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); + + var table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + + var 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()); + + //wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); + + } + + [TestMethod] + public void TableInsertAboveFromRows() + { + var wb = new XLWorkbook(); + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); + + var table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + + var 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()); + + //wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); + + } + + [TestMethod] + public void TableInsertAboveFromData() + { + var wb = new XLWorkbook(); + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); + + var table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + + var 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()); + + //wb.SaveAs(@"C:\Excel Files\ForTesting\Sandbox.xlsx"); + + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx index c26390b..e300ad5 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx index 62c037b..9891641 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/Formulas.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx index 46a0c72..15c911c 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx index 36db2b4..47d59fa 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/NamedRanges.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx index e1a46bc..f3e164f 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx Binary files differ