diff --git a/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/Excel/Cells/IXLCell.cs index 8df2759..5a10fc3 100644 --- a/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/Excel/Cells/IXLCell.cs @@ -7,6 +7,8 @@ { public enum XLCellValues { Text, Number, Boolean, DateTime, TimeSpan } + public enum XLTableCellType { None, Header, Data, Total } + public enum XLClearOptions { ContentsAndFormats, @@ -253,6 +255,7 @@ /// IXLTable InsertTable(DataTable data, String tableName, Boolean createTable); + XLTableCellType TableCellType(); XLHyperlink Hyperlink { get; set; } IXLWorksheet Worksheet { get; } diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 1c654d9..fa5e7db 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -16,7 +16,6 @@ using ClosedXML.Extensions; [DebuggerDisplay("{Address}")] - internal class XLCell : IXLCell, IXLStylized { public static readonly DateTime BaseDate = new DateTime(1899, 12, 30); @@ -100,11 +99,8 @@ _worksheet = worksheet; } - - #endregion Constructor - public XLWorksheet Worksheet { get { return _worksheet; } @@ -203,11 +199,23 @@ public IXLCell SetValue(T value) { + return SetValue(value, true); + } + + internal IXLCell SetValue(T value, bool setTableHeader) + { if (value == null) return this.Clear(XLClearOptions.Contents); FormulaA1 = String.Empty; _richText = null; + + if (setTableHeader) + { + if (SetTableHeaderValue(value)) return this; + if (SetTableTotalsRowLabel(value)) return this; + } + var style = GetStyleForRead(); if (value is String || value is char) { @@ -422,7 +430,7 @@ if (value is XLCells) throw new ArgumentException("Cannot assign IXLCells object to the cell value."); - if (SetTableHeader(value)) return; + if (SetTableHeaderValue(value)) return; if (SetRangeRows(value)) return; @@ -456,6 +464,9 @@ public IXLTable InsertTable(IEnumerable data, string tableName, bool createTable) { + if (createTable && this.Worksheet.Tables.Any(t => t.Contains(this))) + throw new InvalidOperationException(String.Format("This cell '{0}' is already part of a table.", this.Address.ToString())); + if (data != null && !(data is String)) { var ro = Address.RowNumber + 1; @@ -485,12 +496,12 @@ if (String.IsNullOrWhiteSpace(fieldName)) fieldName = itemType.Name; - SetValue(fieldName, fRo, co); + _worksheet.SetValue(fieldName, fRo, co); hasTitles = true; co = Address.ColumnNumber; } - SetValue(o, ro, co); + _worksheet.SetValue(o, ro, co); co++; if (co > maxCo) @@ -547,7 +558,7 @@ { foreach (var item in (m as Array)) { - SetValue(item, ro, co); + _worksheet.SetValue(item, ro, co); co++; } } @@ -564,7 +575,7 @@ ? column.ColumnName : column.Caption) { - SetValue(fieldName, fRo, co); + _worksheet.SetValue(fieldName, fRo, co); co++; } @@ -574,7 +585,7 @@ foreach (var item in row.ItemArray) { - SetValue(item, ro, co); + _worksheet.SetValue(item, ro, co); co++; } } @@ -590,7 +601,7 @@ { for (var i = 0; i < fieldCount; i++) { - SetValue(record.GetName(i), fRo, co); + _worksheet.SetValue(record.GetName(i), fRo, co); co++; } @@ -600,7 +611,7 @@ for (var i = 0; i < fieldCount; i++) { - SetValue(record[i], ro, co); + _worksheet.SetValue(record[i], ro, co); co++; } } @@ -616,7 +627,7 @@ if (String.IsNullOrWhiteSpace(fieldName)) fieldName = mi.Name; - SetValue(fieldName, fRo, co); + _worksheet.SetValue(fieldName, fRo, co); } co++; @@ -629,11 +640,11 @@ foreach (var mi in members) { if (mi.MemberType == MemberTypes.Property && (mi as PropertyInfo).GetGetMethod().IsStatic) - SetValue((mi as PropertyInfo).GetValue(null), ro, co); + _worksheet.SetValue((mi as PropertyInfo).GetValue(null), ro, co); else if (mi.MemberType == MemberTypes.Field && (mi as FieldInfo).IsStatic) - SetValue((mi as FieldInfo).GetValue(null), ro, co); + _worksheet.SetValue((mi as FieldInfo).GetValue(null), ro, co); else - SetValue(accessor[m, mi.Name], ro, co); + _worksheet.SetValue(accessor[m, mi.Name], ro, co); co++; } @@ -680,6 +691,9 @@ { if (data == null) return null; + if (createTable && this.Worksheet.Tables.Any(t => t.Contains(this))) + throw new InvalidOperationException(String.Format("This cell '{0}' is already part of a table.", this.Address.ToString())); + if (data.Rows.Count > 0) return InsertTable(data.AsEnumerable(), tableName, createTable); var ro = Address.RowNumber; @@ -687,7 +701,7 @@ foreach (DataColumn col in data.Columns) { - SetValue(col.ColumnName, ro, co); + _worksheet.SetValue(col.ColumnName, ro, co); co++; } @@ -703,6 +717,17 @@ return tableName == null ? range.AsTable() : range.AsTable(tableName); } + public XLTableCellType TableCellType() + { + var table = this.Worksheet.Tables.FirstOrDefault(t => t.AsRange().Contains(this)); + if (table == null) return XLTableCellType.None; + + if (table.ShowHeaderRow && table.HeadersRow().RowNumber().Equals(this.Address.RowNumber)) return XLTableCellType.Header; + if (table.ShowTotalsRow && table.TotalsRow().RowNumber().Equals(this.Address.RowNumber)) return XLTableCellType.Total; + + return XLTableCellType.Data; + } + public IXLRange InsertData(IEnumerable data) { return InsertData(data, false); @@ -735,10 +760,9 @@ else columnNumber = Address.ColumnNumber; - if (itemType.IsPrimitive || itemType == typeof(String) || itemType == typeof(DateTime) || itemType.IsNumber()) { - SetValue(m, rowNumber, columnNumber); + _worksheet.SetValue(m, rowNumber, columnNumber); if (transpose) rowNumber++; @@ -749,7 +773,7 @@ { foreach (var item in (Array)m) { - SetValue(item, rowNumber, columnNumber); + _worksheet.SetValue(item, rowNumber, columnNumber); if (transpose) rowNumber++; @@ -764,7 +788,7 @@ foreach (var item in (m as DataRow).ItemArray) { - SetValue(item, rowNumber, columnNumber); + _worksheet.SetValue(item, rowNumber, columnNumber); if (transpose) rowNumber++; @@ -782,7 +806,7 @@ var fieldCount = record.FieldCount; for (var i = 0; i < fieldCount; i++) { - SetValue(record[i], rowNumber, columnNumber); + _worksheet.SetValue(record[i], rowNumber, columnNumber); if (transpose) rowNumber++; @@ -811,12 +835,12 @@ foreach (var mi in members) { if (mi.MemberType == MemberTypes.Property && (mi as PropertyInfo).GetGetMethod().IsStatic) - SetValue((mi as PropertyInfo).GetValue(null), rowNumber, columnNumber); + _worksheet.SetValue((mi as PropertyInfo).GetValue(null), rowNumber, columnNumber); else if (mi.MemberType == MemberTypes.Field && (mi as FieldInfo).IsStatic) - SetValue((mi as FieldInfo).GetValue(null), rowNumber, columnNumber); + _worksheet.SetValue((mi as FieldInfo).GetValue(null), rowNumber, columnNumber); else + _worksheet.SetValue(accessor[m, mi.Name], rowNumber, columnNumber); - SetValue(accessor[m, mi.Name], rowNumber, columnNumber); if (transpose) rowNumber++; else @@ -1492,7 +1516,7 @@ #endregion IXLStylized Members - private bool SetTableHeader(object value) + private Boolean SetTableHeaderValue(object value) { foreach (var table in Worksheet.Tables.Where(t => t.ShowHeaderRow)) { @@ -1509,6 +1533,26 @@ return false; } + private Boolean SetTableTotalsRowLabel(object value) + { + foreach (var table in Worksheet.Tables.Where(t => t.ShowTotalsRow)) + { + var cells = table.TotalsRow().Cells(c => c.Address.Equals(this.Address)); + if (cells.Any()) + { + var cell = cells.First(); + var field = table.Fields.First(f => f.Column.ColumnNumber() == cell.WorksheetColumn().ColumnNumber()); + field.TotalsRowFunction = XLTotalsRowFunction.None; + field.TotalsRowLabel = value.ToString(); + this._cellValue = value.ToString(); + this.DataType = XLCellValues.Text; + return true; + } + } + + return false; + } + private bool SetRangeColumns(object value) { var columns = value as XLRangeColumns; @@ -1575,6 +1619,7 @@ } #region Styles + private IXLStyle GetStyle() { if (_style != null) @@ -1615,15 +1660,14 @@ if (!_styleCacheId.HasValue) _styleCacheId = Worksheet.GetStyleId(); return _styleCacheId.Value; - } public Boolean IsDefaultWorksheetStyle() { return !_styleCacheId.HasValue && !StyleChanged || GetStyleId() == Worksheet.GetStyleId(); } - #endregion Styles + #endregion Styles public void DeleteComment() { @@ -1737,16 +1781,6 @@ mergeToDelete.ForEach(m => Worksheet.Internals.MergedRanges.Remove(m)); } - private void SetValue(T value, int ro, int co) where T : class - { - if (value == null) - _worksheet.Cell(ro, co).SetValue(String.Empty); - else if (value is IConvertible) - _worksheet.Cell(ro, co).SetValue((T)Convert.ChangeType(value, typeof(T))); - else - _worksheet.Cell(ro, co).SetValue(value); - } - private void SetValue(object value) { FormulaA1 = String.Empty; @@ -1824,6 +1858,10 @@ } } if (val.Length > 32767) throw new ArgumentException("Cells can only hold 32,767 characters."); + + if (SetTableHeaderValue(val)) return; + if (SetTableTotalsRowLabel(val)) return; + _cellValue = val; } diff --git a/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/Excel/Tables/IXLTable.cs index 386c669..1030c02 100644 --- a/ClosedXML/Excel/Tables/IXLTable.cs +++ b/ClosedXML/Excel/Tables/IXLTable.cs @@ -1,31 +1,22 @@ using System; using System.Collections.Generic; + namespace ClosedXML.Excel { public interface IXLTable : IXLRange { - string Name { get; set; } + IXLBaseAutoFilter AutoFilter { get; } + IXLTableRange DataRange { get; } Boolean EmphasizeFirstColumn { get; set; } Boolean EmphasizeLastColumn { get; set; } - Boolean ShowRowStripes { get; set; } - Boolean ShowColumnStripes { get; set; } - Boolean ShowTotalsRow { get; set; } - Boolean ShowAutoFilter { get; set; } - XLTableTheme Theme { get; set; } - IXLRangeRow HeadersRow(); - IXLRangeRow TotalsRow(); - IXLTableField Field(string fieldName); - IXLTableField Field(int fieldIndex); IEnumerable Fields { get; } - - - - IXLTable SetEmphasizeFirstColumn(); IXLTable SetEmphasizeFirstColumn(Boolean value); - IXLTable SetEmphasizeLastColumn(); IXLTable SetEmphasizeLastColumn(Boolean value); - IXLTable SetShowRowStripes(); IXLTable SetShowRowStripes(Boolean value); - IXLTable SetShowColumnStripes(); IXLTable SetShowColumnStripes(Boolean value); - IXLTable SetShowTotalsRow(); IXLTable SetShowTotalsRow(Boolean value); - IXLTable SetShowAutoFilter(); IXLTable SetShowAutoFilter(Boolean value); + string Name { get; set; } + Boolean ShowAutoFilter { get; set; } + Boolean ShowColumnStripes { get; set; } + Boolean ShowHeaderRow { get; set; } + Boolean ShowRowStripes { get; set; } + Boolean ShowTotalsRow { get; set; } + XLTableTheme Theme { get; set; } /// /// Clears the contents of this table. @@ -33,13 +24,97 @@ /// Specify what you want to clear. new IXLTable Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); - IXLBaseAutoFilter AutoFilter { get; } + IXLTableField Field(string fieldName); + + IXLTableField Field(int fieldIndex); + + IXLRangeRow HeadersRow(); + + /// + /// Resizes the table to the specified range. + /// + /// The new table range. + /// + IXLTable Resize(IXLRange range); + + /// + /// Resizes the table to the specified range address. + /// + /// The range boundaries. + /// + IXLTable Resize(IXLRangeAddress rangeAddress); + + /// + /// Resizes the table to the specified range address. + /// + /// The range boundaries. + /// + IXLTable Resize(string rangeAddress); + + /// + /// Resizes the table to the specified range. + /// + /// The first cell in the range. + /// The last cell in the range. + /// + IXLTable Resize(IXLCell firstCell, IXLCell lastCell); + + /// + /// Resizes the table to the specified range. + /// + /// The first cell address in the worksheet. + /// The last cell address in the worksheet. + /// + IXLTable Resize(string firstCellAddress, string lastCellAddress); + + /// + /// Resizes the table to the specified range. + /// + /// The first cell address in the worksheet. + /// The last cell address in the worksheet. + /// + IXLTable Resize(IXLAddress firstCellAddress, IXLAddress lastCellAddress); + + /// + /// Resizes the table to 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. + /// + IXLTable Resize(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn); new IXLBaseAutoFilter SetAutoFilter(); - Boolean ShowHeaderRow { get; set; } - IXLTable SetShowHeaderRow(); IXLTable SetShowHeaderRow(Boolean value); + IXLTable SetEmphasizeFirstColumn(); - IXLTableRange DataRange { get; } + IXLTable SetEmphasizeFirstColumn(Boolean value); + + IXLTable SetEmphasizeLastColumn(); + + IXLTable SetEmphasizeLastColumn(Boolean value); + + IXLTable SetShowAutoFilter(); + + IXLTable SetShowAutoFilter(Boolean value); + + IXLTable SetShowColumnStripes(); + + IXLTable SetShowColumnStripes(Boolean value); + + IXLTable SetShowHeaderRow(); + + IXLTable SetShowHeaderRow(Boolean value); + + IXLTable SetShowRowStripes(); + + IXLTable SetShowRowStripes(Boolean value); + + IXLTable SetShowTotalsRow(); + + IXLTable SetShowTotalsRow(Boolean value); + + IXLRangeRow TotalsRow(); } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index 6048e33..c468d76 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -54,43 +54,76 @@ { get { - if (_fieldNames != null && _lastRangeAddress != null && _lastRangeAddress.Equals(RangeAddress)) return _fieldNames; + if (_fieldNames != null && _lastRangeAddress != null && _lastRangeAddress.Equals(RangeAddress)) + return _fieldNames; - _fieldNames = new Dictionary(); - _lastRangeAddress = RangeAddress; - - if (ShowHeaderRow) + if (_fieldNames == null) { - var headersRow = HeadersRow(); - Int32 cellPos = 0; - foreach (var cell in headersRow.Cells()) - { - var name = cell.GetString(); - if (String.IsNullOrWhiteSpace(name)) - { - name = "Column" + (cellPos + 1); - cell.SetValue(name); - } - 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 = new Dictionary(); + _lastRangeAddress = RangeAddress; + HeadersRow(); } else { - Int32 colCount = ColumnCount(); - for (Int32 i = 1; i <= colCount; i++) - { - if (!_fieldNames.Values.Any(f => f.Index == i - 1)) - { - var name = "Column" + i; + HeadersRow(false); + } - _fieldNames.Add(name, new XLTableField(this, name) { Index = i - 1 }); - } + RescanFieldNames(); + + _lastRangeAddress = RangeAddress; + + return _fieldNames; + } + } + + private void RescanFieldNames() + { + if (ShowHeaderRow) + { + var detectedFieldNames = new Dictionary(); + var headersRow = HeadersRow(false); + Int32 cellPos = 0; + foreach (var cell in headersRow.Cells()) + { + var name = cell.GetString(); + if (_fieldNames.ContainsKey(name) && _fieldNames[name].Column.ColumnNumber() == cell.Address.ColumnNumber) + { + (_fieldNames[name] as XLTableField).Index = cellPos; + detectedFieldNames.Add(name, _fieldNames[name]); + cellPos++; + continue; + } + + if (String.IsNullOrWhiteSpace(name)) + { + name = GetUniqueName("Column", cellPos + 1, true); + cell.SetValue(name); + cell.DataType = XLCellValues.Text; + } + 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++ }); + detectedFieldNames.Add(name, _fieldNames[name]); + } + + _fieldNames.Keys + .Where(key => !detectedFieldNames.ContainsKey(key)) + .ToArray() + .ForEach(key => _fieldNames.Remove(key)); + } + else + { + Int32 colCount = ColumnCount(); + for (Int32 i = 1; i <= colCount; i++) + { + if (!_fieldNames.Values.Any(f => f.Index == i - 1)) + { + var name = "Column" + i; + + _fieldNames.Add(name, new XLTableField(this, name) { Index = i - 1 }); } } - return _fieldNames; } } @@ -122,9 +155,6 @@ get { XLRange range; - //var ws = Worksheet; - //var tracking = ws.EventTrackingEnabled; - //ws.EventTrackingEnabled = false; if (_showHeaderRow) { @@ -138,7 +168,7 @@ ? Range(1, 1, RowCount() - 1, ColumnCount()) : Range(1, 1, RowCount(), ColumnCount()); } - //ws.EventTrackingEnabled = tracking; + return new XLTableRange(range, this); } } @@ -222,9 +252,18 @@ public IXLRangeRow HeadersRow() { + return HeadersRow(true); + } + + internal IXLRangeRow HeadersRow(Boolean scanForNewFieldsNames) + { if (!ShowHeaderRow) return null; - var m = FieldNames; + if (scanForNewFieldsNames) + { + var tempResult = FieldNames; + } + return FirstRow(); } @@ -253,6 +292,124 @@ } } + public IXLTable Resize(IXLRangeAddress rangeAddress) + { + return Resize(Worksheet.Range(RangeAddress)); + } + + public IXLTable Resize(string rangeAddress) + { + return Resize(Worksheet.Range(RangeAddress)); + } + + public IXLTable Resize(IXLCell firstCell, IXLCell lastCell) + { + return Resize(Worksheet.Range(firstCell, lastCell)); + } + + public IXLTable Resize(string firstCellAddress, string lastCellAddress) + { + return Resize(Worksheet.Range(firstCellAddress, lastCellAddress)); + } + + public IXLTable Resize(IXLAddress firstCellAddress, IXLAddress lastCellAddress) + { + return Resize(Worksheet.Range(firstCellAddress, lastCellAddress)); + } + + public IXLTable Resize(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn) + { + return Resize(Worksheet.Range(firstCellRow, firstCellColumn, lastCellRow, lastCellColumn)); + } + + public IXLTable Resize(IXLRange range) + { + if (!this.ShowHeaderRow) + throw new NotImplementedException("Resizing of tables with no headers not supported yet."); + + if (this.Worksheet != range.Worksheet) + throw new InvalidOperationException("You cannot resize a table to a range on a different sheet."); + + var totalsRowChanged = this.ShowTotalsRow ? range.LastRow().RowNumber() - this.TotalsRow().RowNumber() : 0; + var oldTotalsRowNumber = this.ShowTotalsRow ? this.TotalsRow().RowNumber() : -1; + + var existingHeaders = this.FieldNames.Keys; + var newHeaders = new HashSet(); + var tempArray = this.Fields.Select(f => f.Column).ToArray(); + + var firstRow = range.Row(1); + if (!firstRow.FirstCell().Address.Equals(this.HeadersRow().FirstCell().Address) + || !firstRow.LastCell().Address.Equals(this.HeadersRow().LastCell().Address)) + { + _uniqueNames.Clear(); + var co = 1; + foreach (var c in firstRow.Cells()) + { + if (String.IsNullOrWhiteSpace(((XLCell)c).InnerText)) + c.Value = GetUniqueName("Column", co, true); + + var header = c.GetString(); + _uniqueNames.Add(header); + + if (!existingHeaders.Contains(header)) + newHeaders.Add(header); + + co++; + } + } + + if (totalsRowChanged < 0) + { + range.Rows(r => r.RowNumber().Equals(this.TotalsRow().RowNumber() + totalsRowChanged)).Single().InsertRowsAbove(1); + range = Worksheet.Range(range.FirstCell(), range.LastCell().CellAbove()); + oldTotalsRowNumber++; + } + else if (totalsRowChanged > 0) + { + this.TotalsRow().RowBelow(totalsRowChanged + 1).InsertRowsAbove(1); + this.TotalsRow().AsRange().Delete(XLShiftDeletedCells.ShiftCellsUp); + } + + this.RangeAddress = range.RangeAddress as XLRangeAddress; + RescanFieldNames(); + + if (this.ShowTotalsRow) + { + foreach (var f in this._fieldNames.Values) + { + var c = this.TotalsRow().Cell(f.Index + 1); + if (!c.IsEmpty() && newHeaders.Contains(f.Name)) + { + f.TotalsRowLabel = c.GetFormattedString(); + c.DataType = XLCellValues.Text; + } + } + + if (totalsRowChanged != 0) + { + foreach (var f in this._fieldNames.Values.Cast()) + { + f.UpdateUnderlyingCellFormula(); + var c = this.TotalsRow().Cell(f.Index + 1); + if (!String.IsNullOrWhiteSpace(f.TotalsRowLabel)) + { + c.DataType = XLCellValues.Text; + + //Remove previous row's label + var oldTotalsCell = this.Worksheet.Cell(oldTotalsRowNumber, f.Column.ColumnNumber()); + if (oldTotalsCell.Value.ToString() == f.TotalsRowLabel) + oldTotalsCell.Value = null; + } + + if (f.TotalsRowFunction != XLTotalsRowFunction.None) + c.DataType = XLCellValues.Number; + } + } + } + + return this; + } + public IXLTable SetEmphasizeFirstColumn() { EmphasizeFirstColumn = true; @@ -386,7 +543,7 @@ if (setAutofilter) InitializeAutoFilter(); - HeadersRow().DataType = XLCellValues.Text; + AsRange().Row(1).DataType = XLCellValues.Text; if (RowCount() == 1) InsertRowsBelow(1); @@ -406,19 +563,19 @@ foreach (IXLCell c in range.Row(1).Cells()) { if (String.IsNullOrWhiteSpace(((XLCell)c).InnerText)) - c.Value = GetUniqueName("Column" + co.ToInvariantString()); + c.Value = GetUniqueName("Column", co, true); _uniqueNames.Add(c.GetString()); co++; } Worksheet.Tables.Add(this); } - private String GetUniqueName(String originalName) + private String GetUniqueName(String originalName, Int32 initialOffset, Boolean enforceOffset) { - String name = originalName; - if (_uniqueNames.Contains(name)) + String name = String.Concat(originalName, enforceOffset ? initialOffset.ToInvariantString() : string.Empty); + if (_uniqueNames?.Contains(name) ?? false) { - Int32 i = 1; + Int32 i = initialOffset; name = originalName + i.ToInvariantString(); while (_uniqueNames.Contains(name)) { @@ -427,7 +584,6 @@ } } - _uniqueNames.Add(name); return name; } @@ -460,7 +616,7 @@ foreach (IXLCell c in headersRow.Cells()) { if (String.IsNullOrWhiteSpace(((XLCell)c).InnerText)) - c.Value = GetUniqueName("Column" + co.ToInvariantString()); + c.Value = GetUniqueName("Column", co, true); _uniqueNames.Add(c.GetString()); co++; } diff --git a/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/Excel/Tables/XLTableField.cs index 528d7d2..c737adc 100644 --- a/ClosedXML/Excel/Tables/XLTableField.cs +++ b/ClosedXML/Excel/Tables/XLTableField.cs @@ -19,12 +19,25 @@ this.name = name; } + private IXLRangeColumn _column; + public IXLRangeColumn Column { - get { return table.Column(this.Index); } + get { return _column ?? (_column = table.HeadersRow(false).Cell(this.Index + 1).AsRange().Columns().Single()); } } - public Int32 Index { get; internal set; } + private Int32 index; + + public Int32 Index + { + get { return index; } + internal set + { + if (index == value) return; + index = value; + _column = null; + } + } public String Name { @@ -35,7 +48,7 @@ set { if (table.ShowHeaderRow) - table.HeadersRow().Cell(Index + 1).SetValue(value); + (table.HeadersRow(false).Cell(Index + 1) as XLCell).SetValue(value, false); table.RenameField(name, value); name = value; @@ -67,31 +80,36 @@ get { return totalsRowFunction; } set { - if (value != XLTotalsRowFunction.None && value != XLTotalsRowFunction.Custom) - { - var cell = table.TotalsRow().Cell(Index + 1); - String formula = String.Empty; - switch (value) - { - case XLTotalsRowFunction.Sum: formula = "109"; break; - case XLTotalsRowFunction.Minimum: formula = "105"; break; - case XLTotalsRowFunction.Maximum: formula = "104"; break; - case XLTotalsRowFunction.Average: formula = "101"; break; - case XLTotalsRowFunction.Count: formula = "103"; break; - case XLTotalsRowFunction.CountNumbers: formula = "102"; break; - case XLTotalsRowFunction.StandardDeviation: formula = "107"; break; - case XLTotalsRowFunction.Variance: formula = "110"; break; - } - - cell.FormulaA1 = "SUBTOTAL(" + formula + ",[" + Name + "])"; - var lastCell = table.LastRow().Cell(Index + 1); - if (lastCell.DataType != XLCellValues.Text) - { - cell.DataType = lastCell.DataType; - cell.Style.NumberFormat = lastCell.Style.NumberFormat; - } - } totalsRowFunction = value; + UpdateUnderlyingCellFormula(); + } + } + + internal void UpdateUnderlyingCellFormula() + { + if (TotalsRowFunction != XLTotalsRowFunction.None && TotalsRowFunction != XLTotalsRowFunction.Custom) + { + var cell = table.TotalsRow().Cell(Index + 1); + String formula = String.Empty; + switch (TotalsRowFunction) + { + case XLTotalsRowFunction.Sum: formula = "109"; break; + case XLTotalsRowFunction.Minimum: formula = "105"; break; + case XLTotalsRowFunction.Maximum: formula = "104"; break; + case XLTotalsRowFunction.Average: formula = "101"; break; + case XLTotalsRowFunction.Count: formula = "103"; break; + case XLTotalsRowFunction.CountNumbers: formula = "102"; break; + case XLTotalsRowFunction.StandardDeviation: formula = "107"; break; + case XLTotalsRowFunction.Variance: formula = "110"; break; + } + + cell.FormulaA1 = "SUBTOTAL(" + formula + ",[" + Name + "])"; + var lastCell = table.LastRow().Cell(Index + 1); + if (lastCell.DataType != XLCellValues.Text) + { + cell.DataType = lastCell.DataType; + cell.Style.NumberFormat = lastCell.Style.NumberFormat; + } } } @@ -101,7 +119,7 @@ set { totalsRowFunction = XLTotalsRowFunction.None; - table.TotalsRow().Cell(Index + 1).SetValue(value); + (table.TotalsRow().Cell(Index + 1) as XLCell).SetValue(value, false); totalsRowLabel = value; } } @@ -113,12 +131,16 @@ 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); + var fields = table.Fields.Cast().ToArray(); if (deleteUnderlyingRangeColumn) - (this.Column as XLRangeColumn).Delete(false); + { + table.AsRange().ColumnQuick(this.Index + 1).Delete(); + // (this.Column as XLRangeColumn).Delete(false); + } + + fields.Where(f => f.Index > this.Index).ForEach(f => f.Index--); + table.FieldNames.Remove(this.Name); } } -} +} \ No newline at end of file diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index b43a2a7..aa9a77d 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -4225,7 +4225,6 @@ if (xlWorksheet.Internals.CellsCollection.RowsCollection.ContainsKey(distinctRow)) { - var isNewRow = !row.Elements().Any(); lastCell = 0; var mRows = row.Elements().ToDictionary(c => XLHelper.GetColumnNumberFromAddress(c.CellReference == null @@ -4234,6 +4233,8 @@ .OrderBy(c => c.Address.ColumnNumber) .Select(c => c)) { + XLTableField field = null; + var styleId = context.SharedStyles[xlCell.GetStyleId()].StyleId; var cellReference = (xlCell.Address).GetTrimmedAddress(); @@ -4305,14 +4306,29 @@ cell.CellValue = null; } + else if (xlCell.TableCellType() == XLTableCellType.Total) + { + var table = xlWorksheet.Tables.First(t => t.AsRange().Contains(xlCell)); + field = table.Fields.First(f => f.Column.ColumnNumber() == xlCell.Address.ColumnNumber) as XLTableField; + + if (!String.IsNullOrWhiteSpace(field.TotalsRowLabel)) + { + cell.DataType = XLWorkbook.CvSharedString; + } + else + { + cell.DataType = null; + } + cell.CellFormula = null; + } else { cell.CellFormula = null; cell.DataType = xlCell.DataType == XLCellValues.DateTime ? null : GetCellValueType(xlCell); } - if (!xlCell.HasFormula || evaluateFormulae) - SetCellValue(xlCell, cell); + if (evaluateFormulae || field != null || !xlCell.HasFormula) + SetCellValue(xlCell, field, cell); } } xlWorksheet.Internals.CellsCollection.deleted.Remove(distinctRow); @@ -4352,7 +4368,6 @@ } } - foreach ( var r in xlWorksheet.Internals.CellsCollection.deleted.Keys.Where( @@ -4864,8 +4879,25 @@ #endregion LegacyDrawingHeaderFooter } - private static void SetCellValue(XLCell xlCell, Cell openXmlCell) + private static void SetCellValue(XLCell xlCell, XLTableField field, Cell openXmlCell) { + if (field != null) + { + if (!String.IsNullOrWhiteSpace(field.TotalsRowLabel)) + { + var cellValue = new CellValue(); + cellValue.Text = xlCell.SharedStringId.ToString(); + openXmlCell.DataType = CvSharedString; + openXmlCell.CellValue = cellValue; + } + else if (field.TotalsRowFunction == XLTotalsRowFunction.None) + { + openXmlCell.DataType = CvSharedString; + openXmlCell.CellValue = null; + } + return; + } + if (xlCell.HasFormula) { var cellValue = new CellValue(); diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index c40ad1b..6b5348a 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -1595,5 +1595,16 @@ { return true; } + + internal void SetValue(T value, int ro, int co) where T : class + { + if (value == null) + this.Cell(ro, co).SetValue(String.Empty); + else if (value is IConvertible) + this.Cell(ro, co).SetValue((T)Convert.ChangeType(value, typeof(T))); + else + this.Cell(ro, co).SetValue(value); + } + } } diff --git a/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML_Examples/ClosedXML_Examples.csproj index 9f4871b..a870a50 100644 --- a/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -105,7 +105,6 @@ - @@ -118,7 +117,6 @@ - @@ -169,6 +167,9 @@ + + + diff --git a/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML_Examples/Creating/CreateFiles.cs index 1e639dd..8f1978f 100644 --- a/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML_Examples/Creating/CreateFiles.cs @@ -4,6 +4,7 @@ using ClosedXML_Examples.Ranges; using ClosedXML_Examples.Rows; using ClosedXML_Examples.Styles; +using ClosedXML_Examples.Tables; using System.IO; namespace ClosedXML_Examples @@ -67,6 +68,7 @@ new RowCells().Create(Path.Combine(path, "RowCells.xlsx")); new FreezePanes().Create(Path.Combine(path, "FreezePanes.xlsx")); new UsingTables().Create(Path.Combine(path, "UsingTables.xlsx")); + new ResizingTables().Create(Path.Combine(path, "ResizingTables.xlsx")); new AddingRowToTables().Create(Path.Combine(path, "AddingRowToTables.xlsx")); new RightToLeft().Create(Path.Combine(path, "RightToLeft.xlsx")); new ShowCase().Create(Path.Combine(path, "ShowCase.xlsx")); diff --git a/ClosedXML_Examples/Misc/CopyingWorksheets.cs b/ClosedXML_Examples/Misc/CopyingWorksheets.cs index b0843eb..e06cd66 100644 --- a/ClosedXML_Examples/Misc/CopyingWorksheets.cs +++ b/ClosedXML_Examples/Misc/CopyingWorksheets.cs @@ -1,6 +1,6 @@ -using System.IO; using ClosedXML.Excel; -using ClosedXML_Examples.Ranges; +using ClosedXML_Examples.Tables; +using System.IO; namespace ClosedXML_Examples.Misc { @@ -18,7 +18,7 @@ var wsSource = wb.Worksheet(1); // Copy the worksheet to a new sheet in this workbook wsSource.CopyTo("Copy"); - + // We're going to open another workbook to show that you can // copy a sheet from one workbook to another: new BasicTable().Create(tempFile2); @@ -40,6 +40,5 @@ } } } - } } diff --git a/ClosedXML_Examples/Misc/InsertingTables.cs b/ClosedXML_Examples/Misc/InsertingTables.cs deleted file mode 100644 index 80a42df..0000000 --- a/ClosedXML_Examples/Misc/InsertingTables.cs +++ /dev/null @@ -1,111 +0,0 @@ -using ClosedXML.Attributes; -using ClosedXML.Excel; -using System; -using System.Collections.Generic; -using System.Data; -using System.Linq; - -namespace ClosedXML_Examples.Misc -{ - public class InsertingTables : IXLExample - { - #region Methods - - // Public - public void Create(String filePath) - { - using (var wb = new XLWorkbook()) - { - var ws = wb.Worksheets.Add("Inserting Tables"); - - // From a list of strings - var listOfStrings = new List(); - listOfStrings.Add("House"); - listOfStrings.Add("Car"); - ws.Cell(1, 1).Value = "From Strings"; - ws.Cell(1, 1).AsRange().AddToNamed("Titles"); - ws.Cell(2, 1).InsertTable(listOfStrings); - - // From a list of arrays - var listOfArr = new List(); - listOfArr.Add(new Int32[] { 1, 2, 3 }); - listOfArr.Add(new Int32[] { 1 }); - listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 }); - ws.Cell(1, 3).Value = "From Arrays"; - ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles"); - ws.Cell(2, 3).InsertTable(listOfArr); - - // From a DataTable - var dataTable = GetTable(); - ws.Cell(7, 1).Value = "From DataTable"; - ws.Range(7, 1, 7, 4).Merge().AddToNamed("Titles"); - ws.Cell(8, 1).InsertTable(dataTable.AsEnumerable()); - - // From a query - var list = new List(); - list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St." }); - list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St." }); - list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St." }); - list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." }); - - var people = from p in list - where p.Age >= 21 - select p; - - ws.Cell(7, 6).Value = "From Query"; - ws.Range(7, 6, 7, 9).Merge().AddToNamed("Titles"); - ws.Cell(8, 6).InsertTable(people.AsEnumerable()); - - ws.Cell(15, 6).Value = "From List"; - ws.Range(15, 6, 15, 9).Merge().AddToNamed("Titles"); - ws.Cell(16, 6).InsertTable(people); - - // Prepare the style for the titles - var titlesStyle = wb.Style; - titlesStyle.Font.Bold = true; - titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; - titlesStyle.Fill.BackgroundColor = XLColor.Cyan; - - // Format all titles in one shot - wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle; - - ws.Columns().AdjustToContents(); - - wb.SaveAs(filePath); - } - } - - private class Person - { - [XLColumn(Header = "House Street")] - public String House { get; set; } - - public String Name { get; set; } - public Int32 Age { get; set; } - - [XLColumn(Header = "Class Type")] - public static String ClassType { get { return nameof(Person); } } - } - - // Private - private DataTable GetTable() - { - DataTable table = new DataTable(); - table.Columns.Add("Dosage", typeof(int)); - table.Columns.Add("Drug", typeof(string)); - table.Columns.Add("Patient", typeof(string)); - table.Columns.Add("Date", typeof(DateTime)); - - table.Rows.Add(25, "Indocin", "David", new DateTime(2000, 1, 1)); - table.Rows.Add(50, "Enebrel", "Sam", new DateTime(2000, 1, 2)); - table.Rows.Add(10, "Hydralazine", "Christoff", new DateTime(2000, 1, 3)); - table.Rows.Add(21, "Combivent", "Janet", new DateTime(2000, 1, 4)); - table.Rows.Add(100, "Dilantin", "Melanie", new DateTime(2000, 1, 5)); - return table; - } - - // Override - - #endregion Methods - } -} diff --git a/ClosedXML_Examples/Ranges/UsingTables.cs b/ClosedXML_Examples/Ranges/UsingTables.cs deleted file mode 100644 index e5c24b2..0000000 --- a/ClosedXML_Examples/Ranges/UsingTables.cs +++ /dev/null @@ -1,106 +0,0 @@ -using System; -using System.IO; -using ClosedXML.Excel; -using System.Linq; - - -namespace ClosedXML_Examples.Ranges -{ - public class UsingTables : IXLExample - { - #region Methods - - // Public - public void Create(String filePath) - { - string tempFile = ExampleHelper.GetTempFilePath(filePath); - try - { - new BasicTable().Create(tempFile); - var wb = new XLWorkbook(tempFile); - var ws = wb.Worksheet(1); - ws.Name = "Contacts Table"; - var firstCell = ws.FirstCellUsed(); - var lastCell = ws.LastCellUsed(); - var range = ws.Range(firstCell.Address, lastCell.Address); - range.FirstRow().Delete(); // Deleting the "Contacts" header (we don't need it for our purposes) - - // We want to use a theme for table, not the hard coded format of the BasicTable - range.Clear(XLClearOptions.Formats); - // Put back the date and number formats - range.Column(4).Style.NumberFormat.NumberFormatId = 15; - range.Column(5).Style.NumberFormat.Format = "$ #,##0"; - - var table = range.CreateTable(); // You can also use range.AsTable() if you want to - // manipulate the range as a table but don't want - // to create the table in the worksheet. - - // Let's activate the Totals row and add the sum of Income - table.ShowTotalsRow = true; - table.Field("Income").TotalsRowFunction = XLTotalsRowFunction.Sum; - // Just for fun let's add the text "Sum Of Income" to the totals row - table.Field(0).TotalsRowLabel = "Sum Of Income"; - - // Copy all the headers - Int32 columnWithHeaders = lastCell.Address.ColumnNumber + 2; - Int32 currentRow = table.RangeAddress.FirstAddress.RowNumber; - ws.Cell(currentRow, columnWithHeaders).Value = "Table Headers"; - foreach (var cell in table.HeadersRow().Cells()) - { - currentRow++; - ws.Cell(currentRow, columnWithHeaders).Value = cell.Value; - } - - // Format the headers as a table with a different style and no autofilters - var htFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithHeaders); - var htLastCell = ws.Cell(currentRow, columnWithHeaders); - var headersTable = ws.Range(htFirstCell, htLastCell).CreateTable("Headers"); - headersTable.Theme = XLTableTheme.TableStyleLight10; - headersTable.ShowAutoFilter = false; - - // Add a custom formula to the headersTable - headersTable.ShowTotalsRow = true; - headersTable.Field(0).TotalsRowFormulaA1 = "CONCATENATE(\"Count: \", CountA(Headers[Table Headers]))"; - - // Copy the names - Int32 columnWithNames = columnWithHeaders + 2; - currentRow = table.RangeAddress.FirstAddress.RowNumber; // reset the currentRow - ws.Cell(currentRow, columnWithNames).Value = "Names"; - foreach (var row in table.DataRange.Rows()) - { - currentRow++; - var fName = row.Field("FName").GetString(); // Notice how we're calling the cell by field name - var lName = row.Field("LName").GetString(); // Notice how we're calling the cell by field name - var name = String.Format("{0} {1}", fName, lName); - ws.Cell(currentRow, columnWithNames).Value = name; - } - - // Format the names as a table with a different style and no autofilters - var ntFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithNames); - var ntLastCell = ws.Cell(currentRow, columnWithNames); - var namesTable = ws.Range(ntFirstCell, ntLastCell).CreateTable(); - namesTable.Theme = XLTableTheme.TableStyleLight12; - namesTable.ShowAutoFilter = false; - - ws.Columns().AdjustToContents(); - ws.Columns("A,G,I").Width = 3; - - wb.SaveAs(filePath); - } - finally - { - if (File.Exists(tempFile)) - { - File.Delete(tempFile); - } - } - } - - // Private - - // Override - - - #endregion - } -} diff --git a/ClosedXML_Examples/Tables/InsertingTables.cs b/ClosedXML_Examples/Tables/InsertingTables.cs new file mode 100644 index 0000000..5c86a6e --- /dev/null +++ b/ClosedXML_Examples/Tables/InsertingTables.cs @@ -0,0 +1,111 @@ +using ClosedXML.Attributes; +using ClosedXML.Excel; +using System; +using System.Collections.Generic; +using System.Data; +using System.Linq; + +namespace ClosedXML_Examples.Tables +{ + public class InsertingTables : IXLExample + { + #region Methods + + // Public + public void Create(String filePath) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Inserting Tables"); + + // From a list of strings + var listOfStrings = new List(); + listOfStrings.Add("House"); + listOfStrings.Add("Car"); + ws.Cell(1, 1).Value = "From Strings"; + ws.Cell(1, 1).AsRange().AddToNamed("Titles"); + ws.Cell(2, 1).InsertTable(listOfStrings); + + // From a list of arrays + var listOfArr = new List(); + listOfArr.Add(new Int32[] { 1, 2, 3 }); + listOfArr.Add(new Int32[] { 1 }); + listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 }); + ws.Cell(1, 3).Value = "From Arrays"; + ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles"); + ws.Cell(2, 3).InsertTable(listOfArr); + + // From a DataTable + var dataTable = GetTable(); + ws.Cell(7, 1).Value = "From DataTable"; + ws.Range(7, 1, 7, 4).Merge().AddToNamed("Titles"); + ws.Cell(8, 1).InsertTable(dataTable.AsEnumerable()); + + // From a query + var list = new List(); + list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St." }); + list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St." }); + list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St." }); + list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." }); + + var people = from p in list + where p.Age >= 21 + select p; + + ws.Cell(7, 6).Value = "From Query"; + ws.Range(7, 6, 7, 9).Merge().AddToNamed("Titles"); + ws.Cell(8, 6).InsertTable(people.AsEnumerable()); + + ws.Cell(15, 6).Value = "From List"; + ws.Range(15, 6, 15, 9).Merge().AddToNamed("Titles"); + ws.Cell(16, 6).InsertTable(people); + + // Prepare the style for the titles + var titlesStyle = wb.Style; + titlesStyle.Font.Bold = true; + titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + titlesStyle.Fill.BackgroundColor = XLColor.Cyan; + + // Format all titles in one shot + wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle; + + ws.Columns().AdjustToContents(); + + wb.SaveAs(filePath); + } + } + + private class Person + { + [XLColumn(Header = "House Street")] + public String House { get; set; } + + public String Name { get; set; } + public Int32 Age { get; set; } + + [XLColumn(Header = "Class Type")] + public static String ClassType { get { return nameof(Person); } } + } + + // Private + private DataTable GetTable() + { + DataTable table = new DataTable(); + table.Columns.Add("Dosage", typeof(int)); + table.Columns.Add("Drug", typeof(string)); + table.Columns.Add("Patient", typeof(string)); + table.Columns.Add("Date", typeof(DateTime)); + + table.Rows.Add(25, "Indocin", "David", new DateTime(2000, 1, 1)); + table.Rows.Add(50, "Enebrel", "Sam", new DateTime(2000, 1, 2)); + table.Rows.Add(10, "Hydralazine", "Christoff", new DateTime(2000, 1, 3)); + table.Rows.Add(21, "Combivent", "Janet", new DateTime(2000, 1, 4)); + table.Rows.Add(100, "Dilantin", "Melanie", new DateTime(2000, 1, 5)); + return table; + } + + // Override + + #endregion Methods + } +} diff --git a/ClosedXML_Examples/Tables/ResizingTables.cs b/ClosedXML_Examples/Tables/ResizingTables.cs new file mode 100644 index 0000000..64ad34e --- /dev/null +++ b/ClosedXML_Examples/Tables/ResizingTables.cs @@ -0,0 +1,47 @@ +using ClosedXML.Excel; +using System; +using System.Linq; + +// TODO: Add example to Wiki + +namespace ClosedXML_Examples.Tables +{ + public class ResizingTables : IXLExample + { + public void Create(string filePath) + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.AddWorksheet("Sheet1"); + + var data1 = Enumerable.Range(1, 10) + .Select(i => + new + { + Index = i, + Character = Convert.ToChar(64 + i), + String = new String('a', i), + Integer = 64 + i + }); + + var table1 = ws1.Cell("B2").InsertTable(data1, true) + .SetShowHeaderRow() + .SetShowTotalsRow(); + + table1.Fields.First().TotalsRowLabel = "Sum of Integer"; + table1.Fields.Last().TotalsRowFunction = XLTotalsRowFunction.Sum; + + var ws2 = ws1.CopyTo("Sheet2"); + var table2 = ws2.Tables.First(); + table2.Resize(table2.FirstCell(), table2.LastCell().CellLeft().CellAbove(3)); + + var ws3 = ws2.CopyTo("Sheet3"); + var table3 = ws3.Tables.First(); + table3.Resize(table3.FirstCell().CellLeft(), table3.LastCell().CellRight().CellBelow(1)); + + wb.Worksheets.ForEach(ws => ws.Columns().AdjustToContents()); + wb.SaveAs(filePath); + } + } + } +} diff --git a/ClosedXML_Examples/Tables/UsingTables.cs b/ClosedXML_Examples/Tables/UsingTables.cs new file mode 100644 index 0000000..639a0a0 --- /dev/null +++ b/ClosedXML_Examples/Tables/UsingTables.cs @@ -0,0 +1,105 @@ +using ClosedXML.Excel; +using System; +using System.IO; + +namespace ClosedXML_Examples.Tables +{ + public class UsingTables : IXLExample + { + #region Methods + + // Public + public void Create(String filePath) + { + string tempFile = ExampleHelper.GetTempFilePath(filePath); + try + { + new BasicTable().Create(tempFile); + using (var wb = new XLWorkbook(tempFile)) + { + var ws = wb.Worksheet(1); + ws.Name = "Contacts Table"; + var firstCell = ws.FirstCellUsed(); + var lastCell = ws.LastCellUsed(); + var range = ws.Range(firstCell.Address, lastCell.Address); + range.FirstRow().Delete(); // Deleting the "Contacts" header (we don't need it for our purposes) + + // We want to use a theme for table, not the hard coded format of the BasicTable + range.Clear(XLClearOptions.Formats); + // Put back the date and number formats + range.Column(4).Style.NumberFormat.NumberFormatId = 15; + range.Column(5).Style.NumberFormat.Format = "$ #,##0"; + + var table = range.CreateTable(); // You can also use range.AsTable() if you want to + // manipulate the range as a table but don't want + // to create the table in the worksheet. + + // Let's activate the Totals row and add the sum of Income + table.ShowTotalsRow = true; + table.Field("Income").TotalsRowFunction = XLTotalsRowFunction.Sum; + // Just for fun let's add the text "Sum Of Income" to the totals row + table.Field(0).TotalsRowLabel = "Sum Of Income"; + + // Copy all the headers + Int32 columnWithHeaders = lastCell.Address.ColumnNumber + 2; + Int32 currentRow = table.RangeAddress.FirstAddress.RowNumber; + ws.Cell(currentRow, columnWithHeaders).Value = "Table Headers"; + foreach (var cell in table.HeadersRow().Cells()) + { + currentRow++; + ws.Cell(currentRow, columnWithHeaders).Value = cell.Value; + } + + // Format the headers as a table with a different style and no autofilters + var htFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithHeaders); + var htLastCell = ws.Cell(currentRow, columnWithHeaders); + var headersTable = ws.Range(htFirstCell, htLastCell).CreateTable("Headers"); + headersTable.Theme = XLTableTheme.TableStyleLight10; + headersTable.ShowAutoFilter = false; + + // Add a custom formula to the headersTable + headersTable.ShowTotalsRow = true; + headersTable.Field(0).TotalsRowFormulaA1 = "CONCATENATE(\"Count: \", CountA(Headers[Table Headers]))"; + + // Copy the names + Int32 columnWithNames = columnWithHeaders + 2; + currentRow = table.RangeAddress.FirstAddress.RowNumber; // reset the currentRow + ws.Cell(currentRow, columnWithNames).Value = "Names"; + foreach (var row in table.DataRange.Rows()) + { + currentRow++; + var fName = row.Field("FName").GetString(); // Notice how we're calling the cell by field name + var lName = row.Field("LName").GetString(); // Notice how we're calling the cell by field name + var name = String.Format("{0} {1}", fName, lName); + ws.Cell(currentRow, columnWithNames).Value = name; + } + + // Format the names as a table with a different style and no autofilters + var ntFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithNames); + var ntLastCell = ws.Cell(currentRow, columnWithNames); + var namesTable = ws.Range(ntFirstCell, ntLastCell).CreateTable(); + namesTable.Theme = XLTableTheme.TableStyleLight12; + namesTable.ShowAutoFilter = false; + + ws.Columns().AdjustToContents(); + ws.Columns("A,G,I").Width = 3; + + wb.SaveAs(filePath); + } + } + finally + { + if (File.Exists(tempFile)) + { + File.Delete(tempFile); + } + } + } + + // Private + + // Override + + #endregion Methods + } +} diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index cca56e5..28a64f7 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -76,6 +76,7 @@ + @@ -174,7 +175,6 @@ - @@ -224,7 +224,6 @@ - @@ -294,6 +293,9 @@ + + + diff --git a/ClosedXML_Tests/Examples/MiscTests.cs b/ClosedXML_Tests/Examples/MiscTests.cs index 4f14453..d74a9fc 100644 --- a/ClosedXML_Tests/Examples/MiscTests.cs +++ b/ClosedXML_Tests/Examples/MiscTests.cs @@ -133,11 +133,6 @@ TestHelper.RunTestExample(@"Misc\InsertingData.xlsx"); } - [Test] - public void InsertingTables() - { - TestHelper.RunTestExample(@"Misc\InsertingTables.xlsx"); - } [Test] public void LambdaExpressions() @@ -204,11 +199,11 @@ { TestHelper.RunTestExample(@"Misc\WorkbookProperties.xlsx"); } - + [Test] public void WorkbookProtection() { TestHelper.RunTestExample(@"Misc\WorkbookProtection.xlsx"); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Examples/RangesTests.cs b/ClosedXML_Tests/Examples/RangesTests.cs index fe3e1e2..59cd62b 100644 --- a/ClosedXML_Tests/Examples/RangesTests.cs +++ b/ClosedXML_Tests/Examples/RangesTests.cs @@ -99,12 +99,6 @@ } [Test] - public void UsingTables() - { - TestHelper.RunTestExample(@"Ranges\UsingTables.xlsx"); - } - - [Test] public void AddingRowToTables() { TestHelper.RunTestExample(@"Ranges\AddingRowToTables.xlsx"); @@ -116,4 +110,4 @@ TestHelper.RunTestExample(@"Ranges\WalkingRanges.xlsx"); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Examples/TablesTests.cs b/ClosedXML_Tests/Examples/TablesTests.cs new file mode 100644 index 0000000..934bf65 --- /dev/null +++ b/ClosedXML_Tests/Examples/TablesTests.cs @@ -0,0 +1,27 @@ +using ClosedXML_Examples.Tables; +using NUnit.Framework; + +namespace ClosedXML_Tests.Examples +{ + [TestFixture] + public class TablesTests + { + [Test] + public void InsertingTables() + { + TestHelper.RunTestExample(@"Tables\InsertingTables.xlsx"); + } + + [Test] + public void ResizingTables() + { + TestHelper.RunTestExample(@"Tables\ResizingTables.xlsx"); + } + + [Test] + public void UsingTables() + { + TestHelper.RunTestExample(@"Tables\UsingTables.xlsx"); + } + } +} diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index 1a35952..b3ff96e 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -451,5 +451,134 @@ Assert.AreEqual(2, table.Fields.Last().Index); } } + + [Test] + public void OverlappingTablesThrowsException() + { + var dt = new DataTable("sheet1"); + dt.Columns.Add("col1", typeof(string)); + dt.Columns.Add("col2", typeof(double)); + + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(dt, true); + Assert.Throws(() => ws.FirstCell().CellRight().InsertTable(dt, true)); + } + } + + [Test] + public void OverwritingTableTotalsRow() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + var data1 = Enumerable.Range(1, 10) + .Select(i => + new + { + Index = i, + Character = Convert.ToChar(64 + i), + String = new String('a', i) + }); + + var table = ws.FirstCell().InsertTable(data1, true) + .SetShowHeaderRow() + .SetShowTotalsRow(); + table.Fields.First().TotalsRowFunction = XLTotalsRowFunction.Sum; + + var data2 = Enumerable.Range(1, 20) + .Select(i => + new + { + Index = i, + Character = Convert.ToChar(64 + i), + String = new String('b', i), + Int = 64 + i + }); + + ws.FirstCell().CellBelow().InsertData(data2); + + table.Fields.ForEach(f => Assert.AreEqual(XLTotalsRowFunction.None, f.TotalsRowFunction)); + + Assert.AreEqual("11", table.Field(0).TotalsRowLabel); + Assert.AreEqual("K", table.Field(1).TotalsRowLabel); + Assert.AreEqual("bbbbbbbbbbb", table.Field(2).TotalsRowLabel); + } + } + + [Test] + public void CanResizeTable() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + var data1 = Enumerable.Range(1, 10) + .Select(i => + new + { + Index = i, + Character = Convert.ToChar(64 + i), + String = new String('a', i) + }); + + var table = ws.FirstCell().InsertTable(data1, true) + .SetShowHeaderRow() + .SetShowTotalsRow(); + table.Fields.First().TotalsRowFunction = XLTotalsRowFunction.Sum; + + var data2 = Enumerable.Range(1, 10) + .Select(i => + new + { + Index = i, + Character = Convert.ToChar(64 + i), + String = new String('b', i), + Integer = 64 + i + }); + + ws.FirstCell().CellBelow().InsertData(data2); + table.Resize(table.FirstCell().Address, table.AsRange().LastCell().CellRight().Address); + + Assert.AreEqual(4, table.Fields.Count()); + + Assert.AreEqual("Column4", table.Field(3).Name); + + ws.Cell("D1").Value = "Integer"; + Assert.AreEqual("Integer", table.Field(3).Name); + } + } + + [Test] + public void TestTableCellTypes() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + var data1 = Enumerable.Range(1, 10) + .Select(i => + new + { + Index = i, + Character = Convert.ToChar(64 + i), + String = new String('a', i) + }); + + var table = ws.FirstCell().InsertTable(data1, true) + .SetShowHeaderRow() + .SetShowTotalsRow(); + table.Fields.First().TotalsRowFunction = XLTotalsRowFunction.Sum; + + Assert.AreEqual(XLTableCellType.Header, table.HeadersRow().Cell(1).TableCellType()); + Assert.AreEqual(XLTableCellType.Data, table.HeadersRow().Cell(1).CellBelow().TableCellType()); + Assert.AreEqual(XLTableCellType.Total, table.TotalsRow().Cell(1).TableCellType()); + Assert.AreEqual(XLTableCellType.None, ws.Cell("Z100").TableCellType()); + } + } + + //TODO: Delete table (not underlying range) } -} +} \ No newline at end of file diff --git a/ClosedXML_Tests/Resource/Examples/Misc/InsertingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/InsertingTables.xlsx deleted file mode 100644 index 89de679..0000000 --- a/ClosedXML_Tests/Resource/Examples/Misc/InsertingTables.xlsx +++ /dev/null Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx deleted file mode 100644 index 288d1fe..0000000 --- a/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx +++ /dev/null Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx new file mode 100644 index 0000000..89de679 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx new file mode 100644 index 0000000..dad3954 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx new file mode 100644 index 0000000..288d1fe --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx Binary files differ