diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index 8d97801..538ae9a 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -5,16 +5,18 @@ 7.2 ClosedXML ClosedXML - 0.93.0 - Manuel de Leon, Amir Ghezelbash, Francois Botha, Aleksei Pankratev + 0.94.0 + Francois Botha, Aleksei Pankratev, Manuel de Leon, Amir Ghezelbash + Francois Botha, Aleksei Pankratev ClosedXML See https://github.com/ClosedXML/ClosedXML/releases/tag/$(productVersion) - ClosedXML makes it easier for developers to create Excel 2007+ (.xlsx, .xlsm, etc) files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and VisualBasic.NET. + ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API. MIT https://github.com/ClosedXML/ClosedXML/blob/master/LICENSE https://github.com/ClosedXML/ClosedXML https://github.com/ClosedXML/ClosedXML + https://raw.githubusercontent.com/ClosedXML/ClosedXML/develop/resources/logo/nuget-logo.png true $(NoWarn);NU1605;CS1591 true @@ -22,6 +24,7 @@ + ClosedXML.Signed bin\Release.Signed\ true true @@ -59,10 +62,6 @@ - - - - diff --git a/ClosedXML/ClosedXML.nuspec b/ClosedXML/ClosedXML.nuspec index 07006ea..f065911 100644 --- a/ClosedXML/ClosedXML.nuspec +++ b/ClosedXML/ClosedXML.nuspec @@ -6,7 +6,7 @@ $title$ $author$ $author$ - ClosedXML makes it easier for developers to create Excel 2007+ (.xlsx, .xlsm, etc) files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and VisualBasic.NET. + ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying OpenXML API. https://github.com/ClosedXML/ClosedXML/blob/master/LICENSE https://github.com/ClosedXML/ClosedXML false diff --git a/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs b/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs index 99445b7..b848f47 100644 --- a/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs +++ b/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs @@ -1,16 +1,33 @@ +// Keep this file CodeMaid organised and cleaned using System; +using System.Collections.Generic; + namespace ClosedXML.Excel { - using System.Collections.Generic; + public enum XLFilterDynamicType { AboveAverage, BelowAverage } + + public enum XLFilterType { Regular, Custom, TopBottom, Dynamic, DateTimeGrouping } + + public enum XLTopBottomPart { Top, Bottom } public interface IXLAutoFilter { - IXLFilterColumn Column(String column); - IXLFilterColumn Column(Int32 column); - - IXLAutoFilter Sort(Int32 columnToSortBy = 1, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + Boolean Enabled { get; set; } + IEnumerable HiddenRows { get; } + IXLRange Range { get; set; } + Int32 SortColumn { get; set; } Boolean Sorted { get; set; } XLSortOrder SortOrder { get; set; } - Int32 SortColumn { get; set; } + IEnumerable VisibleRows { get; } + + IXLAutoFilter Clear(); + + IXLFilterColumn Column(String column); + + IXLFilterColumn Column(Int32 column); + + IXLAutoFilter Reapply(); + + IXLAutoFilter Sort(Int32 columnToSortBy = 1, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); } } diff --git a/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs b/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs deleted file mode 100644 index 4f2851d..0000000 --- a/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs +++ /dev/null @@ -1,30 +0,0 @@ -using System; - -namespace ClosedXML.Excel -{ - public enum XLFilterType { Regular, Custom, TopBottom, Dynamic, DateTimeGrouping } - - public enum XLFilterDynamicType { AboveAverage, BelowAverage } - - public enum XLTopBottomPart { Top, Bottom } - - public interface IXLBaseAutoFilter - { - Boolean Enabled { get; set; } - IXLRange Range { get; set; } - - IXLBaseAutoFilter Set(IXLRangeBase range); - - IXLBaseAutoFilter Clear(); - - IXLFilterColumn Column(String column); - - IXLFilterColumn Column(Int32 column); - - IXLBaseAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); - - Boolean Sorted { get; set; } - XLSortOrder SortOrder { get; set; } - Int32 SortColumn { get; set; } - } -} diff --git a/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs b/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs index 84a54a8..f994fff 100644 --- a/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs +++ b/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs @@ -1,3 +1,4 @@ +// Keep this file CodeMaid organised and cleaned using System; using System.Linq; @@ -5,7 +6,7 @@ { using System.Collections.Generic; - internal class XLAutoFilter : IXLBaseAutoFilter, IXLAutoFilter + internal class XLAutoFilter : IXLAutoFilter { private readonly Dictionary _columns = new Dictionary(); @@ -18,39 +19,19 @@ #region IXLAutoFilter Members - IXLAutoFilter IXLAutoFilter.Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, - Boolean ignoreBlanks) - { - return Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks); - } - - #endregion - - #region IXLBaseAutoFilter Members - public Boolean Enabled { get; set; } + public IEnumerable HiddenRows { get => Range.Rows(r => r.WorksheetRow().IsHidden); } public IXLRange Range { get; set; } + public Int32 SortColumn { get; set; } + public Boolean Sorted { get; set; } + public XLSortOrder SortOrder { get; set; } + public IEnumerable VisibleRows { get => Range.Rows(r => !r.WorksheetRow().IsHidden); } - IXLBaseAutoFilter IXLBaseAutoFilter.Clear() + IXLAutoFilter IXLAutoFilter.Clear() { return Clear(); } - IXLBaseAutoFilter IXLBaseAutoFilter.Set(IXLRangeBase range) - { - return Set(range); - } - - IXLBaseAutoFilter IXLBaseAutoFilter.Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, - Boolean ignoreBlanks) - { - return Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks); - } - - public Boolean Sorted { get; set; } - public XLSortOrder SortOrder { get; set; } - public Int32 SortColumn { get; set; } - public IXLFilterColumn Column(String column) { var columnNumber = XLHelper.GetColumnNumberFromLetter(column); @@ -74,39 +55,10 @@ return filterColumn; } - #endregion - - public XLAutoFilter Set(IXLRangeBase range) + public IXLAutoFilter Reapply() { - Range = range.AsRange(); - Enabled = true; - return this; - } - - public XLAutoFilter Clear() - { - if (!Enabled) return this; - - Enabled = false; - Filters.Clear(); - foreach (IXLRangeRow row in Range.Rows().Where(r => r.RowNumber() > 1)) - row.WorksheetRow().Unhide(); - return this; - } - - public XLAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, Boolean ignoreBlanks) - { - if (!Enabled) - throw new InvalidOperationException("Filter has not been enabled."); - var ws = Range.Worksheet as XLWorksheet; ws.SuspendEvents(); - Range.Range(Range.FirstCell().CellBelow(), Range.LastCell()).Sort(columnToSortBy, sortOrder, matchCase, - ignoreBlanks); - - Sorted = true; - SortOrder = sortOrder; - SortColumn = columnToSortBy; // Recalculate shown / hidden rows var rows = Range.Rows(2, Range.RowCount()); @@ -167,5 +119,52 @@ ws.ResumeEvents(); return this; } + + IXLAutoFilter IXLAutoFilter.Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, + Boolean ignoreBlanks) + { + return Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks); + } + + #endregion IXLAutoFilter Members + + public XLAutoFilter Clear() + { + if (!Enabled) return this; + + Enabled = false; + Filters.Clear(); + foreach (IXLRangeRow row in Range.Rows().Where(r => r.RowNumber() > 1)) + row.WorksheetRow().Unhide(); + return this; + } + + public XLAutoFilter Set(IXLRangeBase range) + { + Range = range.AsRange(); + Enabled = true; + return this; + } + + public XLAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, Boolean ignoreBlanks) + { + if (!Enabled) + throw new InvalidOperationException("Filter has not been enabled."); + + var ws = Range.Worksheet as XLWorksheet; + ws.SuspendEvents(); + Range.Range(Range.FirstCell().CellBelow(), Range.LastCell()).Sort(columnToSortBy, sortOrder, matchCase, + ignoreBlanks); + + Sorted = true; + SortOrder = sortOrder; + SortColumn = columnToSortBy; + + ws.ResumeEvents(); + + Reapply(); + + return this; + } } } diff --git a/ClosedXML/Excel/AutoFilters/XLFilter.cs b/ClosedXML/Excel/AutoFilters/XLFilter.cs index e62c552..42ac112 100644 --- a/ClosedXML/Excel/AutoFilters/XLFilter.cs +++ b/ClosedXML/Excel/AutoFilters/XLFilter.cs @@ -1,12 +1,12 @@ +// Keep this file CodeMaid organised and cleaned using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { internal enum XLConnector { And, Or } + internal enum XLFilterOperator { Equal, NotEqual, GreaterThan, LessThan, EqualOrGreaterThan, EqualOrLessThan } + internal class XLFilter { public XLFilter(XLFilterOperator op = XLFilterOperator.Equal) @@ -14,10 +14,10 @@ Operator = op; } + public Func Condition { get; set; } + public XLConnector Connector { get; set; } + public XLDateTimeGrouping DateTimeGrouping { get; set; } public XLFilterOperator Operator { get; set; } public Object Value { get; set; } - public XLConnector Connector { get; set; } - public Func Condition { get; set; } - public XLDateTimeGrouping DateTimeGrouping { get; set; } } } diff --git a/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/Excel/Cells/IXLCell.cs index 5fa8c8e..25929d0 100644 --- a/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/Excel/Cells/IXLCell.cs @@ -17,6 +17,7 @@ /// If the object is an IEnumerable, ClosedXML will copy the collection's data into a table starting from this cell. /// If the object is a range, ClosedXML will copy the range starting from this cell. /// Setting the value to an object (not IEnumerable/range) will call the object's ToString() method. + /// If the value starts with a single quote, ClosedXML will assume the value is a text variable and will prefix the value with a single quote in Excel too. /// /// /// The object containing the value(s) to set. @@ -176,9 +177,9 @@ /// Inserts the IEnumerable data elements and returns the range it occupies. /// /// The IEnumerable data. - /// if set to true the data will be transposed before inserting. + /// if set to true the data will be transposed before inserting. /// - IXLRange InsertData(IEnumerable data, Boolean tranpose); + IXLRange InsertData(IEnumerable data, Boolean transpose); /// /// Inserts the data of a data table. diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index f7996a5..678d282 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -223,21 +223,9 @@ // For SetValue we set the cell value directly to the parameter // as opposed to the other SetValue(object value) where we parse the string and try to decude the value - if (value is String || value is char) - { - parsedValue = value.ToInvariantString(); - _dataType = XLDataType.Text; - if (parsedValue.Contains(Environment.NewLine) && !style.Alignment.WrapText) - Style.Alignment.WrapText = true; - - parsed = true; - } - else - { - var tuple = SetKnownTypedValue(value, style); - parsedValue = tuple.Item1; - parsed = tuple.Item2; - } + var tuple = SetKnownTypedValue(value, style, acceptString: true); + parsedValue = tuple.Item1; + parsed = tuple.Item2; // If parsing was unsuccessful, we throw an ArgumentException // because we are using SetValue (typed). @@ -253,11 +241,20 @@ } // TODO: Replace with (string, bool) ValueTuple later - private Tuple SetKnownTypedValue(T value, XLStyleValue style) + private Tuple SetKnownTypedValue(T value, XLStyleValue style, Boolean acceptString) { string parsedValue; bool parsed; - if (value is DateTime d && d >= BaseDate) + if (value is String && acceptString || value is char || value is Guid || value is Enum) + { + parsedValue = value.ToInvariantString(); + _dataType = XLDataType.Text; + if (parsedValue.Contains(Environment.NewLine) && !style.Alignment.WrapText) + Style.Alignment.WrapText = true; + + parsed = true; + } + else if (value is DateTime d && d >= BaseDate) { parsedValue = d.ToOADate().ToInvariantString(); parsed = true; @@ -284,7 +281,7 @@ { parsedValue = value.ToString(); _dataType = XLDataType.Text; - parsed = parsedValue.Length == 0; + parsed = parsedValue.Length != 0; } else { @@ -295,10 +292,8 @@ } else { - // Here we specifically don't use invariant string, as we want to use the current culture to convert to string - parsedValue = value.ToString(); - _dataType = XLDataType.Text; - parsed = parsedValue.Length == 0; + parsed = false; + parsedValue = null; } return new Tuple(parsedValue, parsed); @@ -306,13 +301,23 @@ private string DeduceCellValueByParsing(string value, XLStyleValue style) { - if (value[0] == '\'') + if (String.IsNullOrEmpty(value)) { + _dataType = XLDataType.Text; + } + else if (value[0] == '\'') + { + // If a user sets a cell value to a value starting with a single quote + // ensure the data type is text + // and that it will be prefixed with a quote in Excel too + value = value.Substring(1, value.Length - 1); _dataType = XLDataType.Text; if (value.Contains(Environment.NewLine) && !style.Alignment.WrapText) Style.Alignment.WrapText = true; + + this.Style.SetIncludeQuotePrefix(); } else if (value.Trim() != "NaN" && Double.TryParse(value, XLHelper.NumberStyle, XLHelper.ParseCulture, out Double _)) _dataType = XLDataType.Number; @@ -655,79 +660,214 @@ return InsertTable(data, tableName, true); } - public IXLTable InsertTable(IEnumerable data, string tableName, bool createTable) + public IXLTable InsertTable(IEnumerable data, String tableName, Boolean createTable) + { + return InsertTable(data, tableName, createTable, addHeadings: true, transpose: false); + } + + public IXLTable InsertTable(IEnumerable data, String tableName, Boolean createTable, Boolean addHeadings, Boolean transpose) { 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 range = InsertDataInternal(data, addHeadings, transpose); + + if (createTable) + // Create a table and save it in the file + return tableName == null ? range.CreateTable() : range.CreateTable(tableName); + else + // Create a table, but keep it in memory. Saved file will contain only "raw" data and column headers + return tableName == null ? range.AsTable() : range.AsTable(tableName); + } + + public IXLTable InsertTable(DataTable data) + { + return InsertTable(data, null, true); + } + + public IXLTable InsertTable(DataTable data, Boolean createTable) + { + return InsertTable(data, null, createTable); + } + + public IXLTable InsertTable(DataTable data, String tableName) + { + return InsertTable(data, tableName, true); + } + + public IXLTable InsertTable(DataTable data, String tableName, Boolean createTable) + { + if (data == null || data.Columns.Count == 0) + 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.Cast().Any()) + return InsertTable(data.Rows.Cast(), tableName, createTable); + + var co = _columnNumber; + + foreach (DataColumn col in data.Columns) { - var ro = _rowNumber + 1; - var fRo = _rowNumber; - var hasTitles = false; - var maxCo = 0; - var isDataTable = false; - var isDataReader = false; - var itemType = data.GetItemType(); + Worksheet.SetValue(col.ColumnName, _rowNumber, co); + co++; + } - if (!data.Any()) + ClearMerged(); + var range = Worksheet.Range( + _rowNumber, + _columnNumber, + _rowNumber, + co - 1); + + if (createTable) + // Create a table and save it in the file + return tableName == null ? range.CreateTable() : range.CreateTable(tableName); + else + // Create a table, but keep it in memory. Saved file will contain only "raw" data and column headers + return tableName == null ? range.AsTable() : range.AsTable(tableName); + } + + internal XLRange InsertDataInternal(IEnumerable data, Boolean addHeadings, Boolean transpose) + { + if (data == null || data is String) + return null; + + var currentRowNumber = _rowNumber; + if (addHeadings && !transpose) currentRowNumber++; + + var currentColumnNumber = _columnNumber; + if (addHeadings && transpose) currentColumnNumber++; + + var firstRowNumber = _rowNumber; + var hasHeadings = false; + var maximumColumnNumber = currentColumnNumber; + var maximumRowNumber = currentRowNumber; + + var itemType = data.GetItemType(); + var isArray = itemType.IsArray; + var isDataTable = itemType == typeof(DataTable); + var isDataReader = itemType == typeof(IDataReader); + + // Inline functions to handle looping with transposing + ////////////////////////////////////////////////////// + void incrementFieldPosition() + { + if (transpose) { - if (itemType.IsPrimitive || itemType == typeof(String) || itemType == typeof(DateTime) || itemType.IsNumber()) - maxCo = _columnNumber + 1; - else - maxCo = _columnNumber + itemType.GetFields().Length + itemType.GetProperties().Length; + maximumRowNumber = Math.Max(maximumRowNumber, currentRowNumber); + currentRowNumber++; } - else if (itemType.IsPrimitive || itemType == typeof(String) || itemType == typeof(DateTime) || itemType.IsNumber()) + else { - foreach (object o in data) - { - var co = _columnNumber; + maximumColumnNumber = Math.Max(maximumColumnNumber, currentColumnNumber); + currentColumnNumber++; + } + } - if (!hasTitles) + void incrementRecordPosition() + { + if (transpose) + { + maximumColumnNumber = Math.Max(maximumColumnNumber, currentColumnNumber); + currentColumnNumber++; + } + else + { + maximumRowNumber = Math.Max(maximumRowNumber, currentRowNumber); + currentRowNumber++; + } + } + + void resetRecordPosition() + { + if (transpose) + currentRowNumber = _rowNumber; + else + currentColumnNumber = _columnNumber; + } + ////////////////////////////////////////////////////// + + if (!data.Any()) + { + if (itemType.IsSimpleType()) + maximumColumnNumber = _columnNumber; + else + maximumColumnNumber = _columnNumber + itemType.GetFields().Length + itemType.GetProperties().Length - 1; + } + else if (itemType.IsSimpleType()) + { + foreach (object o in data) + { + resetRecordPosition(); + + if (addHeadings && !hasHeadings) + { + var fieldName = XLColumnAttribute.GetHeader(itemType); + if (String.IsNullOrWhiteSpace(fieldName)) + fieldName = itemType.Name; + + Worksheet.SetValue(fieldName, firstRowNumber, currentColumnNumber); + hasHeadings = true; + resetRecordPosition(); + } + + Worksheet.SetValue(o, currentRowNumber, currentColumnNumber); + incrementFieldPosition(); + incrementRecordPosition(); + } + } + else + { + const BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static; + var memberCache = new Dictionary>(); + var accessorCache = new Dictionary(); + IEnumerable members = null; + TypeAccessor accessor = null; + bool isPlainObject = itemType == typeof(object); + + if (!isPlainObject) + { + members = itemType.GetFields(bindingFlags).Cast() + .Concat(itemType.GetProperties(bindingFlags)) + .Where(mi => !XLColumnAttribute.IgnoreMember(mi)) + .OrderBy(mi => XLColumnAttribute.GetOrder(mi)); + accessor = TypeAccessor.Create(itemType); + } + + foreach (T m in data) + { + resetRecordPosition(); + + if (m.GetType().IsSimpleType()) + { + if (addHeadings && !hasHeadings) { var fieldName = XLColumnAttribute.GetHeader(itemType); if (String.IsNullOrWhiteSpace(fieldName)) fieldName = itemType.Name; - Worksheet.SetValue(fieldName, fRo, co); - hasTitles = true; - co = _columnNumber; + Worksheet.SetValue(fieldName, firstRowNumber, currentColumnNumber); + hasHeadings = true; + resetRecordPosition(); } - Worksheet.SetValue(o, ro, co); - co++; - - if (co > maxCo) - maxCo = co; - - ro++; + Worksheet.SetValue(m as object, currentRowNumber, currentColumnNumber); + incrementFieldPosition(); } - } - else - { - const BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static; - var memberCache = new Dictionary>(); - var accessorCache = new Dictionary(); - IEnumerable members = null; - TypeAccessor accessor = null; - bool isPlainObject = itemType == typeof(object); - - if (!isPlainObject) - { - members = itemType.GetFields(bindingFlags).Cast() - .Concat(itemType.GetProperties(bindingFlags)) - .Where(mi => !XLColumnAttribute.IgnoreMember(mi)) - .OrderBy(mi => XLColumnAttribute.GetOrder(mi)); - accessor = TypeAccessor.Create(itemType); - } - - foreach (T m in data) + else { if (isPlainObject) { // In this case data is just IEnumerable, which means we have to determine the runtime type of each element // This is very inefficient and we prefer type of T to be a concrete class or struct var type = m.GetType(); + + isArray |= type.IsArray; + isDataTable |= type == typeof(DataRow); + isDataReader |= type == typeof(IDataRecord); + if (!memberCache.ContainsKey(type)) { var _accessor = TypeAccessor.Create(type); @@ -745,14 +885,12 @@ accessor = accessorCache[type]; } - var co = _columnNumber; - - if (itemType.IsArray) + if (isArray) { foreach (var item in (m as Array)) { - Worksheet.SetValue(item, ro, co); - co++; + Worksheet.SetValue(item, currentRowNumber, currentColumnNumber); + incrementFieldPosition(); } } else if (isDataTable || m is DataRow) @@ -761,25 +899,25 @@ if (!isDataTable) isDataTable = true; - if (!hasTitles) + if (addHeadings && !hasHeadings) { foreach (var fieldName in from DataColumn column in row.Table.Columns select String.IsNullOrWhiteSpace(column.Caption) ? column.ColumnName : column.Caption) { - Worksheet.SetValue(fieldName, fRo, co); - co++; + Worksheet.SetValue(fieldName, firstRowNumber, currentColumnNumber); + incrementFieldPosition(); } - co = _columnNumber; - hasTitles = true; + resetRecordPosition(); + hasHeadings = true; } foreach (var item in row.ItemArray) { - Worksheet.SetValue(item, ro, co); - co++; + Worksheet.SetValue(item, currentRowNumber, currentColumnNumber); + incrementFieldPosition(); } } else if (isDataReader || m is IDataRecord) @@ -790,27 +928,27 @@ var record = m as IDataRecord; var fieldCount = record.FieldCount; - if (!hasTitles) + if (addHeadings && !hasHeadings) { for (var i = 0; i < fieldCount; i++) { - Worksheet.SetValue(record.GetName(i), fRo, co); - co++; + Worksheet.SetValue(record.GetName(i), firstRowNumber, currentColumnNumber); + incrementFieldPosition(); } - co = _columnNumber; - hasTitles = true; + resetRecordPosition(); + hasHeadings = true; } for (var i = 0; i < fieldCount; i++) { - Worksheet.SetValue(record[i], ro, co); - co++; + Worksheet.SetValue(record[i], currentRowNumber, currentColumnNumber); + incrementFieldPosition(); } } else { - if (!hasTitles) + if (addHeadings && !hasHeadings) { foreach (var mi in members) { @@ -820,94 +958,43 @@ if (String.IsNullOrWhiteSpace(fieldName)) fieldName = mi.Name; - Worksheet.SetValue(fieldName, fRo, co); + Worksheet.SetValue(fieldName, firstRowNumber, currentColumnNumber); } - co++; + incrementFieldPosition(); } - co = _columnNumber; - hasTitles = true; + resetRecordPosition(); + hasHeadings = true; } foreach (var mi in members) { if (mi.MemberType == MemberTypes.Property && (mi as PropertyInfo).GetGetMethod().IsStatic) - Worksheet.SetValue((mi as PropertyInfo).GetValue(null, null), ro, co); + Worksheet.SetValue((mi as PropertyInfo).GetValue(null, null), currentRowNumber, currentColumnNumber); else if (mi.MemberType == MemberTypes.Field && (mi as FieldInfo).IsStatic) - Worksheet.SetValue((mi as FieldInfo).GetValue(null), ro, co); + Worksheet.SetValue((mi as FieldInfo).GetValue(null), currentRowNumber, currentColumnNumber); else - Worksheet.SetValue(accessor[m, mi.Name], ro, co); + Worksheet.SetValue(accessor[m, mi.Name], currentRowNumber, currentColumnNumber); - co++; + incrementFieldPosition(); } } - - if (co > maxCo) - maxCo = co; - - ro++; } + + incrementRecordPosition(); } - - ClearMerged(); - var range = Worksheet.Range( - _rowNumber, - _columnNumber, - ro - 1, - maxCo - 1); - - if (createTable) - return tableName == null ? range.CreateTable() : range.CreateTable(tableName); - return tableName == null ? range.AsTable() : range.AsTable(tableName); - } - - return null; - } - - public IXLTable InsertTable(DataTable data) - { - return InsertTable(data, null, true); - } - - public IXLTable InsertTable(DataTable data, bool createTable) - { - return InsertTable(data, null, createTable); - } - - public IXLTable InsertTable(DataTable data, string tableName) - { - return InsertTable(data, tableName, true); - } - - public IXLTable InsertTable(DataTable data, string tableName, bool createTable) - { - if (data == null || data.Columns.Count == 0) - 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.Cast().Any()) return InsertTable(data.Rows.Cast(), tableName, createTable); - var ro = _rowNumber; - var co = _columnNumber; - - foreach (DataColumn col in data.Columns) - { - Worksheet.SetValue(col.ColumnName, ro, co); - co++; } ClearMerged(); + var range = Worksheet.Range( _rowNumber, _columnNumber, - ro, - co - 1); + maximumRowNumber, + maximumColumnNumber); - if (createTable) return tableName == null ? range.CreateTable() : range.CreateTable(tableName); - - return tableName == null ? range.AsTable() : range.AsTable(tableName); + return range; } public XLTableCellType TableCellType() @@ -923,150 +1010,26 @@ public IXLRange InsertData(IEnumerable data) { - return InsertData(data, false); + if (data == null || data is String) + return null; + + return InsertDataInternal(data?.Cast(), addHeadings: false, transpose: false); } public IXLRange InsertData(IEnumerable data, Boolean transpose) { - if (data != null && !(data is String)) - { - var rowNumber = _rowNumber; - var columnNumber = _columnNumber; + if (data == null || data is String) + return null; - var maxColumnNumber = 0; - var maxRowNumber = 0; - var isDataTable = false; - var isDataReader = false; - - const BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static; - var memberCache = new Dictionary>(); - var accessorCache = new Dictionary(); - IEnumerable members = null; - TypeAccessor accessor = null; - - foreach (var m in data) - { - var itemType = m.GetType(); - - if (transpose) - rowNumber = _rowNumber; - else - columnNumber = _columnNumber; - - if (itemType.IsPrimitive || itemType == typeof(String) || itemType == typeof(DateTime) || itemType.IsNumber()) - { - Worksheet.SetValue(m, rowNumber, columnNumber); - - if (transpose) - rowNumber++; - else - columnNumber++; - } - else if (itemType.IsArray) - { - foreach (var item in (Array)m) - { - Worksheet.SetValue(item, rowNumber, columnNumber); - - if (transpose) - rowNumber++; - else - columnNumber++; - } - } - else if (isDataTable || m is DataRow) - { - if (!isDataTable) - isDataTable = true; - - foreach (var item in (m as DataRow).ItemArray) - { - Worksheet.SetValue(item, rowNumber, columnNumber); - - if (transpose) - rowNumber++; - else - columnNumber++; - } - } - else if (isDataReader || m is IDataRecord) - { - if (!isDataReader) - isDataReader = true; - - var record = m as IDataRecord; - - var fieldCount = record.FieldCount; - for (var i = 0; i < fieldCount; i++) - { - Worksheet.SetValue(record[i], rowNumber, columnNumber); - - if (transpose) - rowNumber++; - else - columnNumber++; - } - } - else - { - if (!memberCache.ContainsKey(itemType)) - { - var _accessor = TypeAccessor.Create(itemType); - - var _members = itemType.GetFields(bindingFlags).Cast() - .Concat(itemType.GetProperties(bindingFlags)) - .Where(mi => !XLColumnAttribute.IgnoreMember(mi)) - .OrderBy(mi => XLColumnAttribute.GetOrder(mi)); - - memberCache.Add(itemType, _members); - accessorCache.Add(itemType, _accessor); - } - - accessor = accessorCache[itemType]; - members = memberCache[itemType]; - - foreach (var mi in members) - { - if (mi.MemberType == MemberTypes.Property && (mi as PropertyInfo).GetGetMethod().IsStatic) - Worksheet.SetValue((mi as PropertyInfo).GetValue(null, null), rowNumber, columnNumber); - else if (mi.MemberType == MemberTypes.Field && (mi as FieldInfo).IsStatic) - Worksheet.SetValue((mi as FieldInfo).GetValue(null), rowNumber, columnNumber); - else - Worksheet.SetValue(accessor[m, mi.Name], rowNumber, columnNumber); - - if (transpose) - rowNumber++; - else - columnNumber++; - } - } - - if (transpose) - columnNumber++; - else - rowNumber++; - - if (columnNumber > maxColumnNumber) - maxColumnNumber = columnNumber; - - if (rowNumber > maxRowNumber) - maxRowNumber = rowNumber; - } - - ClearMerged(); - return Worksheet.Range( - _rowNumber, - _columnNumber, - maxRowNumber - 1, - maxColumnNumber - 1); - } - - return null; + return InsertDataInternal(data?.Cast(), addHeadings: false, transpose: transpose); } public IXLRange InsertData(DataTable dataTable) { - return InsertData(dataTable.Rows); + if (dataTable == null) + return null; + + return InsertDataInternal(dataTable?.Rows?.Cast(), addHeadings: false, transpose: false); } public IXLCell SetDataType(XLDataType dataType) @@ -1610,11 +1573,21 @@ public Boolean TryGetValue(out T value) { - var currValue = Value; + Object currValue; + try + { + currValue = Value; + } + catch + { + // May fail for formula evaluation + value = default; + return false; + } if (currValue == null) { - value = default(T); + value = default; return true; } @@ -2126,7 +2099,8 @@ } else { - var tuple = SetKnownTypedValue(value, style); + // Don't accept strings, because we're going to try to parse them later + var tuple = SetKnownTypedValue(value, style, acceptString: false); parsedValue = tuple.Item1; parsed = tuple.Item2; } @@ -2135,7 +2109,7 @@ if (!parsed) { // We'll have to parse it slowly :-( - parsedValue = DeduceCellValueByParsing(parsedValue.ToString(), style); + parsedValue = DeduceCellValueByParsing(value.ToString(), style); } if (SetTableHeaderValue(parsedValue)) return; diff --git a/ClosedXML/Excel/ContentManagers/XLBaseContentManager.cs b/ClosedXML/Excel/ContentManagers/XLBaseContentManager.cs new file mode 100644 index 0000000..6d031b5 --- /dev/null +++ b/ClosedXML/Excel/ContentManagers/XLBaseContentManager.cs @@ -0,0 +1,38 @@ +using DocumentFormat.OpenXml; +using System; +using System.Collections.Generic; +using System.Linq; + +namespace ClosedXML.Excel.ContentManagers +{ + internal abstract class XLBaseContentManager + { + + } + + internal abstract class XLBaseContentManager : XLBaseContentManager + where T : struct, IConvertible + + { + protected readonly IDictionary contents = new Dictionary(); + + public OpenXmlElement GetPreviousElementFor(T content) + { + var i = content.CastTo(); + + var previousElements = contents.Keys + .Where(key => key.CastTo() < i && contents[key] != null) + .OrderBy(key => key.CastTo()); + + if (previousElements.Any()) + return contents[previousElements.Last()]; + else + return null; + } + + public void SetElement(T content, OpenXmlElement element) + { + contents[content] = element; + } + } +} diff --git a/ClosedXML/Excel/ContentManagers/XLSheetViewContentManager.cs b/ClosedXML/Excel/ContentManagers/XLSheetViewContentManager.cs new file mode 100644 index 0000000..12c74ad --- /dev/null +++ b/ClosedXML/Excel/ContentManagers/XLSheetViewContentManager.cs @@ -0,0 +1,24 @@ +using DocumentFormat.OpenXml.Spreadsheet; +using System.Linq; + +namespace ClosedXML.Excel.ContentManagers +{ + internal enum XLSheetViewContents + { + Pane, + Selection, + PivotSelection, + ExtensionList + } + + internal class XLSheetViewContentManager : XLBaseContentManager + { + public XLSheetViewContentManager(SheetView sheetView) + { + contents.Add(XLSheetViewContents.Pane, sheetView.Elements().LastOrDefault()); + contents.Add(XLSheetViewContents.Selection, sheetView.Elements().LastOrDefault()); + contents.Add(XLSheetViewContents.PivotSelection, sheetView.Elements().LastOrDefault()); + contents.Add(XLSheetViewContents.ExtensionList, sheetView.Elements().LastOrDefault()); + } + } +} diff --git a/ClosedXML/Excel/ContentManagers/XLWorksheetContentManager.cs b/ClosedXML/Excel/ContentManagers/XLWorksheetContentManager.cs new file mode 100644 index 0000000..d3f90c0 --- /dev/null +++ b/ClosedXML/Excel/ContentManagers/XLWorksheetContentManager.cs @@ -0,0 +1,97 @@ +using DocumentFormat.OpenXml; +using DocumentFormat.OpenXml.Spreadsheet; +using System.Linq; + +namespace ClosedXML.Excel.ContentManagers +{ + internal enum XLWorksheetContents + { + SheetProperties = 1, + SheetDimension = 2, + SheetViews = 3, + SheetFormatProperties = 4, + Columns = 5, + SheetData = 6, + SheetCalculationProperties = 7, + SheetProtection = 8, + ProtectedRanges = 9, + Scenarios = 10, + AutoFilter = 11, + SortState = 12, + DataConsolidate = 13, + CustomSheetViews = 14, + MergeCells = 15, + PhoneticProperties = 16, + ConditionalFormatting = 17, + DataValidations = 18, + Hyperlinks = 19, + PrintOptions = 20, + PageMargins = 21, + PageSetup = 22, + HeaderFooter = 23, + RowBreaks = 24, + ColumnBreaks = 25, + CustomProperties = 26, + CellWatches = 27, + IgnoredErrors = 28, + SmartTags = 29, + Drawing = 30, + LegacyDrawing = 31, + LegacyDrawingHeaderFooter = 32, + DrawingHeaderFooter = 33, + Picture = 34, + OleObjects = 35, + Controls = 36, + AlternateContent = 37, + WebPublishItems = 38, + TableParts = 39, + WorksheetExtensionList = 40 + } + + internal class XLWorksheetContentManager : XLBaseContentManager + { + public XLWorksheetContentManager(Worksheet opWorksheet) + { + contents.Add(XLWorksheetContents.SheetProperties, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.SheetDimension, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.SheetViews, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.SheetFormatProperties, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.Columns, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.SheetData, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.SheetCalculationProperties, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.SheetProtection, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.ProtectedRanges, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.Scenarios, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.AutoFilter, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.SortState, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.DataConsolidate, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.CustomSheetViews, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.MergeCells, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.PhoneticProperties, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.ConditionalFormatting, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.DataValidations, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.Hyperlinks, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.PrintOptions, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.PageMargins, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.PageSetup, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.HeaderFooter, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.RowBreaks, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.ColumnBreaks, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.CustomProperties, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.CellWatches, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.IgnoredErrors, opWorksheet.Elements().LastOrDefault()); + //contents.Add(XLWSContents.SmartTags, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.Drawing, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.LegacyDrawing, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.LegacyDrawingHeaderFooter, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.DrawingHeaderFooter, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.Picture, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.OleObjects, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.Controls, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.AlternateContent, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.WebPublishItems, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.TableParts, opWorksheet.Elements().LastOrDefault()); + contents.Add(XLWorksheetContents.WorksheetExtensionList, opWorksheet.Elements().LastOrDefault()); + } + } +} diff --git a/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs b/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs index d814355..a1d4d19 100644 --- a/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs +++ b/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs @@ -21,6 +21,8 @@ get { return name; } set { + if (name == value) return; + if (_workbook.CustomProperties.Any(t => t.Name == value)) throw new ArgumentException( String.Format("This workbook already contains a custom property named '{0}'", value)); diff --git a/ClosedXML/Excel/Drawings/IXLMarker.cs b/ClosedXML/Excel/Drawings/IXLMarker.cs deleted file mode 100644 index d5c2ef4..0000000 --- a/ClosedXML/Excel/Drawings/IXLMarker.cs +++ /dev/null @@ -1,10 +0,0 @@ -using System.Drawing; - -namespace ClosedXML.Excel.Drawings -{ - internal interface IXLMarker - { - IXLAddress Address { get; set; } - Point Offset { get; set; } - } -} diff --git a/ClosedXML/Excel/Drawings/IXLPicture.cs b/ClosedXML/Excel/Drawings/IXLPicture.cs index cf2245d..2036470 100644 --- a/ClosedXML/Excel/Drawings/IXLPicture.cs +++ b/ClosedXML/Excel/Drawings/IXLPicture.cs @@ -1,3 +1,4 @@ +// Keep this file CodeMaid organised and cleaned using System; using System.Drawing; using System.IO; @@ -6,20 +7,7 @@ { public interface IXLPicture : IDisposable { - IXLAddress BottomRightCellAddress { get; } - - /// - /// Create a copy of the picture on a different worksheet. - /// - /// The worksheet to which the picture will be copied. - /// A created copy of the picture. - IXLPicture CopyTo(IXLWorksheet targetSheet); - - /// - /// Create a copy of the picture on the same worksheet. - /// - /// A created copy of the picture. - IXLPicture Duplicate(); + IXLCell BottomRightCell { get; } /// /// Type of image. The supported formats are defined by OpenXML's ImagePartType. @@ -45,32 +33,45 @@ Int32 Top { get; set; } - IXLAddress TopLeftCellAddress { get; } + IXLCell TopLeftCell { get; } Int32 Width { get; set; } IXLWorksheet Worksheet { get; } /// + /// Create a copy of the picture on a different worksheet. + /// + /// The worksheet to which the picture will be copied. + /// A created copy of the picture. + IXLPicture CopyTo(IXLWorksheet targetSheet); + + /// /// Deletes this picture. /// void Delete(); + /// + /// Create a copy of the picture on the same worksheet. + /// + /// A created copy of the picture. + IXLPicture Duplicate(); + Point GetOffset(XLMarkerPosition position); IXLPicture MoveTo(Int32 left, Int32 top); - IXLPicture MoveTo(IXLAddress cell); + IXLPicture MoveTo(IXLCell cell); - IXLPicture MoveTo(IXLAddress cell, Int32 xOffset, Int32 yOffset); + IXLPicture MoveTo(IXLCell cell, Int32 xOffset, Int32 yOffset); - IXLPicture MoveTo(IXLAddress cell, Point offset); + IXLPicture MoveTo(IXLCell cell, Point offset); - IXLPicture MoveTo(IXLAddress fromCell, IXLAddress toCell); + IXLPicture MoveTo(IXLCell fromCell, IXLCell toCell); - IXLPicture MoveTo(IXLAddress fromCell, Int32 fromCellXOffset, Int32 fromCellYOffset, IXLAddress toCell, Int32 toCellXOffset, Int32 toCellYOffset); + IXLPicture MoveTo(IXLCell fromCell, Int32 fromCellXOffset, Int32 fromCellYOffset, IXLCell toCell, Int32 toCellXOffset, Int32 toCellYOffset); - IXLPicture MoveTo(IXLAddress fromCell, Point fromOffset, IXLAddress toCell, Point toOffset); + IXLPicture MoveTo(IXLCell fromCell, Point fromOffset, IXLCell toCell, Point toOffset); IXLPicture Scale(Double factor, Boolean relativeToOriginal = false); diff --git a/ClosedXML/Excel/Drawings/XLMarker.cs b/ClosedXML/Excel/Drawings/XLMarker.cs index f48bf5c..57d496a 100644 --- a/ClosedXML/Excel/Drawings/XLMarker.cs +++ b/ClosedXML/Excel/Drawings/XLMarker.cs @@ -1,23 +1,37 @@ +// Keep this file CodeMaid organised and cleaned +using System; using System.Diagnostics; using System.Drawing; namespace ClosedXML.Excel.Drawings { [DebuggerDisplay("{Address} {Offset}")] - internal class XLMarker : IXLMarker + internal class XLMarker { - internal XLMarker(IXLAddress address) - : this(address, new Point(0, 0)) + // Using a range to store the location so that it gets added to the range repository + // and hence will be adjusted when there are insertions / deletions + private readonly IXLRange rangeCell; + + internal XLMarker(IXLCell cell) + : this(cell.AsRange(), new Point(0, 0)) { } - internal XLMarker(IXLAddress address, Point offset) + internal XLMarker(IXLCell cell, Point offset) + : this(cell.AsRange(), offset) + { } + + private XLMarker(IXLRange rangeCell, Point offset) { - this.Address = address; + if (rangeCell.RowCount() != 1 || rangeCell.ColumnCount() != 1) + throw new ArgumentException("Range should contain only one cell.", nameof(rangeCell)); + + this.rangeCell = rangeCell; this.Offset = offset; } - public IXLAddress Address { get; set; } - + public IXLCell Cell { get => rangeCell.FirstCell(); } + public Int32 ColumnNumber { get => rangeCell.RangeAddress.FirstAddress.ColumnNumber; } public Point Offset { get; set; } + public Int32 RowNumber { get => rangeCell.RangeAddress.FirstAddress.RowNumber; } } } diff --git a/ClosedXML/Excel/Drawings/XLPicture.cs b/ClosedXML/Excel/Drawings/XLPicture.cs index 4ec84de..3a766e3 100644 --- a/ClosedXML/Excel/Drawings/XLPicture.cs +++ b/ClosedXML/Excel/Drawings/XLPicture.cs @@ -1,3 +1,4 @@ +// Keep this file CodeMaid organised and cleaned using System; using System.Collections.Generic; using System.Diagnostics; @@ -96,7 +97,7 @@ { this.Worksheet = worksheet ?? throw new ArgumentNullException(nameof(worksheet)); this.Placement = XLPicturePlacement.MoveAndSize; - this.Markers = new Dictionary() + this.Markers = new Dictionary() { [XLMarkerPosition.TopLeft] = null, [XLMarkerPosition.BottomRight] = null @@ -110,17 +111,18 @@ this.id = 1; } - public IXLAddress BottomRightCellAddress + public IXLCell BottomRightCell { get { - return Markers[XLMarkerPosition.BottomRight].Address; + return Markers[XLMarkerPosition.BottomRight].Cell; } private set { if (!value.Worksheet.Equals(this.Worksheet)) - throw new ArgumentOutOfRangeException(nameof(value.Worksheet)); + throw new InvalidOperationException("A picture and its anchor cells must be on the same worksheet"); + this.Markers[XLMarkerPosition.BottomRight] = new XLMarker(value); } } @@ -160,7 +162,7 @@ if (this.Placement != XLPicturePlacement.FreeFloating) throw new ArgumentException("To set the left-hand offset, the placement should be FreeFloating"); - Markers[XLMarkerPosition.TopLeft] = new XLMarker(Worksheet.Cell(1, 1).Address, new Point(value, this.Top)); + Markers[XLMarkerPosition.TopLeft] = new XLMarker(Worksheet.Cell(1, 1), new Point(value, this.Top)); } } @@ -192,21 +194,21 @@ if (this.Placement != XLPicturePlacement.FreeFloating) throw new ArgumentException("To set the top offset, the placement should be FreeFloating"); - Markers[XLMarkerPosition.TopLeft] = new XLMarker(Worksheet.Cell(1, 1).Address, new Point(this.Left, value)); + Markers[XLMarkerPosition.TopLeft] = new XLMarker(Worksheet.Cell(1, 1), new Point(this.Left, value)); } } - public IXLAddress TopLeftCellAddress + public IXLCell TopLeftCell { get { - return Markers[XLMarkerPosition.TopLeft].Address; + return Markers[XLMarkerPosition.TopLeft].Cell; } private set { if (!value.Worksheet.Equals(this.Worksheet)) - throw new ArgumentOutOfRangeException(nameof(value.Worksheet)); + throw new InvalidOperationException("A picture and its anchor cells must be on the same worksheet"); this.Markers[XLMarkerPosition.TopLeft] = new XLMarker(value); } @@ -225,10 +227,20 @@ public IXLWorksheet Worksheet { get; } - internal IDictionary Markers { get; private set; } + internal IDictionary Markers { get; private set; } internal String RelId { get; set; } + /// + /// Create a copy of the picture on a different worksheet. + /// + /// The worksheet to which the picture will be copied. + /// A created copy of the picture. + public IXLPicture CopyTo(IXLWorksheet targetSheet) + { + return CopyTo((XLWorksheet)targetSheet); + } + public void Delete() { Worksheet.Pictures.Delete(this.Name); @@ -239,6 +251,15 @@ this.ImageStream.Dispose(); } + /// + /// Create a copy of the picture on the same worksheet. + /// + /// A created copy of the picture. + public IXLPicture Duplicate() + { + return CopyTo(Worksheet); + } + public Point GetOffset(XLMarkerPosition position) { return Markers[position].Offset; @@ -252,45 +273,45 @@ return this; } - public IXLPicture MoveTo(IXLAddress cell) + public IXLPicture MoveTo(IXLCell cell) { return MoveTo(cell, 0, 0); } - public IXLPicture MoveTo(IXLAddress cell, Int32 xOffset, Int32 yOffset) + public IXLPicture MoveTo(IXLCell cell, Int32 xOffset, Int32 yOffset) { return MoveTo(cell, new Point(xOffset, yOffset)); } - public IXLPicture MoveTo(IXLAddress cell, Point offset) + public IXLPicture MoveTo(IXLCell cell, Point offset) { if (cell == null) throw new ArgumentNullException(nameof(cell)); this.Placement = XLPicturePlacement.Move; - this.TopLeftCellAddress = cell; + this.TopLeftCell = cell; this.Markers[XLMarkerPosition.TopLeft].Offset = offset; return this; } - public IXLPicture MoveTo(IXLAddress fromCell, IXLAddress toCell) + public IXLPicture MoveTo(IXLCell fromCell, IXLCell toCell) { return MoveTo(fromCell, 0, 0, toCell, 0, 0); } - public IXLPicture MoveTo(IXLAddress fromCell, Int32 fromCellXOffset, Int32 fromCellYOffset, IXLAddress toCell, Int32 toCellXOffset, Int32 toCellYOffset) + public IXLPicture MoveTo(IXLCell fromCell, Int32 fromCellXOffset, Int32 fromCellYOffset, IXLCell toCell, Int32 toCellXOffset, Int32 toCellYOffset) { return MoveTo(fromCell, new Point(fromCellXOffset, fromCellYOffset), toCell, new Point(toCellXOffset, toCellYOffset)); } - public IXLPicture MoveTo(IXLAddress fromCell, Point fromOffset, IXLAddress toCell, Point toOffset) + public IXLPicture MoveTo(IXLCell fromCell, Point fromOffset, IXLCell toCell, Point toOffset) { if (fromCell == null) throw new ArgumentNullException(nameof(fromCell)); if (toCell == null) throw new ArgumentNullException(nameof(toCell)); this.Placement = XLPicturePlacement.MoveAndSize; - this.TopLeftCellAddress = fromCell; + this.TopLeftCell = fromCell; this.Markers[XLMarkerPosition.TopLeft].Offset = fromOffset; - this.BottomRightCellAddress = toCell; + this.BottomRightCell = toCell; this.Markers[XLMarkerPosition.BottomRight].Offset = toOffset; return this; @@ -326,25 +347,6 @@ return this; } - /// - /// Create a copy of the picture on a different worksheet. - /// - /// The worksheet to which the picture will be copied. - /// A created copy of the picture. - public IXLPicture CopyTo(IXLWorksheet targetSheet) - { - return CopyTo((XLWorksheet) targetSheet); - } - - /// - /// Create a copy of the picture on the same worksheet. - /// - /// A created copy of the picture. - public IXLPicture Duplicate() - { - return CopyTo(Worksheet); - } - internal IXLPicture CopyTo(XLWorksheet targetSheet) { if (targetSheet == null) @@ -368,18 +370,11 @@ break; case XLPicturePlacement.Move: - var newAddress = new XLAddress(targetSheet, TopLeftCellAddress.RowNumber, - TopLeftCellAddress.ColumnNumber, false, false); - newPicture.MoveTo(newAddress, GetOffset(XLMarkerPosition.TopLeft)); + newPicture.MoveTo(targetSheet.Cell(TopLeftCell.Address), GetOffset(XLMarkerPosition.TopLeft)); break; case XLPicturePlacement.MoveAndSize: - var newFromAddress = new XLAddress(targetSheet, TopLeftCellAddress.RowNumber, - TopLeftCellAddress.ColumnNumber, false, false); - var newToAddress = new XLAddress(targetSheet, BottomRightCellAddress.RowNumber, - BottomRightCellAddress.ColumnNumber, false, false); - - newPicture.MoveTo(newFromAddress, GetOffset(XLMarkerPosition.TopLeft), newToAddress, + newPicture.MoveTo(targetSheet.Cell(TopLeftCell.Address), GetOffset(XLMarkerPosition.TopLeft), targetSheet.Cell(BottomRightCell.Address), GetOffset(XLMarkerPosition.BottomRight)); break; } diff --git a/ClosedXML/Excel/Exceptions/ClosedXMLException.cs b/ClosedXML/Excel/Exceptions/ClosedXMLException.cs new file mode 100644 index 0000000..3a00816 --- /dev/null +++ b/ClosedXML/Excel/Exceptions/ClosedXMLException.cs @@ -0,0 +1,19 @@ +using System; + +namespace ClosedXML.Excel.Exceptions +{ + public abstract class ClosedXMLException : Exception + { + protected ClosedXMLException() + : base() + { } + + protected ClosedXMLException(String message) + : base(message) + { } + + protected ClosedXMLException(String message, Exception innerException) + : base(message, innerException) + { } + } +} diff --git a/ClosedXML/Excel/Exceptions/EmptyTableException.cs b/ClosedXML/Excel/Exceptions/EmptyTableException.cs new file mode 100644 index 0000000..1f657e2 --- /dev/null +++ b/ClosedXML/Excel/Exceptions/EmptyTableException.cs @@ -0,0 +1,19 @@ +using System; + +namespace ClosedXML.Excel.Exceptions +{ + public class EmptyTableException : ClosedXMLException + { + public EmptyTableException() + : base() + { } + + public EmptyTableException(String message) + : base(message) + { } + + public EmptyTableException(String message, Exception innerException) + : base(message, innerException) + { } + } +} diff --git a/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/Excel/IXLWorksheet.cs index dbad83a..e561706 100644 --- a/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/Excel/IXLWorksheet.cs @@ -412,7 +412,7 @@ IXLWorksheet SetRightToLeft(); IXLWorksheet SetRightToLeft(Boolean value); - IXLBaseAutoFilter AutoFilter { get; } + IXLAutoFilter AutoFilter { get; } IXLRows RowsUsed(Boolean includeFormats = false, Func predicate = null); diff --git a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs index 2afdd8c..6f0241a 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotTable.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotTable.cs @@ -5,6 +5,7 @@ { public enum XLPivotTableTheme { + None, PivotStyleDark1, PivotStyleDark10, PivotStyleDark11, diff --git a/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/Excel/Ranges/IXLRange.cs index 367b83d..62b79cf 100644 --- a/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/Excel/Ranges/IXLRange.cs @@ -39,8 +39,8 @@ /// /// Gets the specified column of the range. /// - /// The column number. - /// + /// 1-based column number relative to the first column of this range. + /// The relevant column IXLRangeColumn Column(int columnNumber); /// @@ -81,8 +81,8 @@ /// /// Gets a collection of the specified columns in this range. /// - /// The first column to return. - /// The last column to return. + /// The first column to return. 1-based column number relative to the first column of this range. + /// The last column to return. 1-based column number relative to the first column of this range. IXLRangeColumns Columns(int firstColumn, int lastColumn); /// @@ -90,6 +90,7 @@ /// /// The first column to return. /// The last column to return. + /// The relevant columns IXLRangeColumns Columns(string firstColumn, string lastColumn); /// @@ -136,7 +137,8 @@ /// /// Gets the specified row of the range. /// - /// The range row. + /// 1-based row number relative to the first row of this range. + /// The relevant row IXLRangeRow Row(int row); IXLRangeRows Rows(Func predicate = null); @@ -144,8 +146,8 @@ /// /// Gets a collection of the specified rows in this range. /// - /// The first row to return. - /// The last row to return. + /// The first row to return. 1-based row number relative to the first row of this range. + /// The last row to return. 1-based row number relative to the first row of this range. /// IXLRangeRows Rows(int firstRow, int lastRow); diff --git a/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/Excel/Ranges/XLRange.cs index 6e8f90c..b9d45ab 100644 --- a/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/Excel/Ranges/XLRange.cs @@ -659,14 +659,15 @@ public XLRangeRow Row(Int32 row) { - if (row <= 0 || row > XLHelper.MaxRowNumber) - throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", XLHelper.MaxRowNumber)); + if (row <= 0 || row > XLHelper.MaxRowNumber + RangeAddress.FirstAddress.RowNumber - 1) + throw new ArgumentOutOfRangeException(nameof(row), String.Format("Row number must be between 1 and {0}", XLHelper.MaxRowNumber + RangeAddress.FirstAddress.RowNumber - 1)); var firstCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.FirstAddress.ColumnNumber, false, false); + var lastCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber + row - 1, RangeAddress.LastAddress.ColumnNumber, @@ -677,8 +678,8 @@ public virtual XLRangeColumn Column(Int32 columnNumber) { - if (columnNumber <= 0 || columnNumber > XLHelper.MaxColumnNumber) - throw new IndexOutOfRangeException(String.Format("Column number must be between 1 and {0}", XLHelper.MaxColumnNumber)); + if (columnNumber <= 0 || columnNumber > XLHelper.MaxColumnNumber + RangeAddress.FirstAddress.ColumnNumber - 1) + throw new ArgumentOutOfRangeException(nameof(columnNumber), String.Format("Column number must be between 1 and {0}", XLHelper.MaxColumnNumber + RangeAddress.FirstAddress.ColumnNumber - 1)); var firstCellAddress = new XLAddress(Worksheet, RangeAddress.FirstAddress.RowNumber, diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 0604aef..af93919 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -635,15 +635,19 @@ if (!cell.IsEmpty(true)) return cell; } } + } - var intersectedRanges = Worksheet.MergedRanges.GetIntersectedRanges(RangeAddress).ToList(); - if (intersectedRanges.Any()) - { - Int32 minRo = intersectedRanges.Min(r => r.RangeAddress.FirstAddress.RowNumber); - Int32 minCo = intersectedRanges.Min(r => r.RangeAddress.FirstAddress.ColumnNumber); + var intersectedRanges = Worksheet.MergedRanges.GetIntersectedRanges(RangeAddress) + .Where(r => predicate?.Invoke(r.FirstCell()) ?? true).ToList(); + if (intersectedRanges.Any()) + { + Int32 minRo = intersectedRanges.Min(r => r.RangeAddress.FirstAddress.RowNumber); + Int32 minCo = intersectedRanges.Min(r => r.RangeAddress.FirstAddress.ColumnNumber); - return Worksheet.Cell(minRo, minCo); - } + if (sp.Row.Between(1, minRo - 1)) minRo = sp.Row; + if (sp.Column.Between(1, minCo - 1)) minCo = sp.Column; + + return Worksheet.Cell(minRo, minCo); } if (sp.Row > 0) @@ -725,15 +729,19 @@ if (!cell.IsEmpty(true)) return cell; } } + } - var intersectedRanges = Worksheet.MergedRanges.GetIntersectedRanges(RangeAddress).ToList(); - if (intersectedRanges.Any()) - { - Int32 minRo = intersectedRanges.Max(r => r.RangeAddress.LastAddress.RowNumber); - Int32 minCo = intersectedRanges.Max(r => r.RangeAddress.LastAddress.ColumnNumber); + var intersectedRanges = Worksheet.MergedRanges.GetIntersectedRanges(RangeAddress) + .Where(r => predicate?.Invoke(r.FirstCell()) ?? true).ToList(); + if (intersectedRanges.Any()) + { + Int32 maxRo = intersectedRanges.Max(r => r.RangeAddress.LastAddress.RowNumber); + Int32 maxCo = intersectedRanges.Max(r => r.RangeAddress.LastAddress.ColumnNumber); - return Worksheet.Cell(minRo, minCo); - } + if (sp.Row > maxRo) maxRo = sp.Row; + if (sp.Column > maxCo) maxCo = sp.Column; + + return Worksheet.Cell(maxRo, maxCo); } if (sp.Row > 0) diff --git a/ClosedXML/Excel/Style/IXLStyle.cs b/ClosedXML/Excel/Style/IXLStyle.cs index 259f568..7634e61 100644 --- a/ClosedXML/Excel/Style/IXLStyle.cs +++ b/ClosedXML/Excel/Style/IXLStyle.cs @@ -1,3 +1,4 @@ +// Keep this file CodeMaid organised and cleaned using System; namespace ClosedXML.Excel @@ -8,14 +9,18 @@ IXLBorder Border { get; set; } + IXLNumberFormat DateFormat { get; } + IXLFill Fill { get; set; } IXLFont Font { get; set; } + Boolean IncludeQuotePrefix { get; set; } + IXLNumberFormat NumberFormat { get; set; } - IXLNumberFormat DateFormat { get; } - IXLProtection Protection { get; set; } + + IXLStyle SetIncludeQuotePrefix(Boolean includeQuotePrefix = true); } } diff --git a/ClosedXML/Excel/Style/XLStyle.cs b/ClosedXML/Excel/Style/XLStyle.cs index 95e63f2..5c5f1ff 100644 --- a/ClosedXML/Excel/Style/XLStyle.cs +++ b/ClosedXML/Excel/Style/XLStyle.cs @@ -126,6 +126,21 @@ } } + public Boolean IncludeQuotePrefix + { + get { return Value.IncludeQuotePrefix; } + set + { + Modify(k => { k.IncludeQuotePrefix = value; return k; }); + } + } + + public IXLStyle SetIncludeQuotePrefix(Boolean includeQuotePrefix = true) + { + IncludeQuotePrefix = includeQuotePrefix; + return this; + } + public IXLNumberFormat NumberFormat { get { return new XLNumberFormat(this, Value.NumberFormat); } diff --git a/ClosedXML/Excel/Style/XLStyleKey.cs b/ClosedXML/Excel/Style/XLStyleKey.cs index 46eea98..e5fc690 100644 --- a/ClosedXML/Excel/Style/XLStyleKey.cs +++ b/ClosedXML/Excel/Style/XLStyleKey.cs @@ -12,6 +12,8 @@ public XLFontKey Font { get; set; } + public Boolean IncludeQuotePrefix { get; set; } + public XLNumberFormatKey NumberFormat { get; set; } public XLProtectionKey Protection { get; set; } @@ -23,6 +25,7 @@ hashCode = hashCode * -1521134295 + Border.GetHashCode(); hashCode = hashCode * -1521134295 + Fill.GetHashCode(); hashCode = hashCode * -1521134295 + Font.GetHashCode(); + hashCode = hashCode * -1521134295 + IncludeQuotePrefix.GetHashCode(); hashCode = hashCode * -1521134295 + NumberFormat.GetHashCode(); hashCode = hashCode * -1521134295 + Protection.GetHashCode(); return hashCode; @@ -34,6 +37,7 @@ Border == other.Border && Fill == other.Fill && Font == other.Font && + IncludeQuotePrefix == other.IncludeQuotePrefix && NumberFormat == other.NumberFormat && Protection == other.Protection; } @@ -42,11 +46,12 @@ { return this == XLStyle.Default.Key ? "Default" : - string.Format("Alignment: {0} Border: {1} Fill: {2} Font: {3} NumberFormat: {4} Protection: {5}", + string.Format("Alignment: {0} Border: {1} Fill: {2} Font: {3} IncludeQuotePrefix: {4} NumberFormat: {5} Protection: {6}", Alignment == XLStyle.Default.Key.Alignment ? "Default" : Alignment.ToString(), Border == XLStyle.Default.Key.Border ? "Default" : Border.ToString(), Fill == XLStyle.Default.Key.Fill ? "Default" : Fill.ToString(), Font == XLStyle.Default.Key.Font ? "Default" : Font.ToString(), + IncludeQuotePrefix == XLStyle.Default.Key.IncludeQuotePrefix ? "Default" : IncludeQuotePrefix.ToString(), NumberFormat == XLStyle.Default.Key.NumberFormat ? "Default" : NumberFormat.ToString(), Protection == XLStyle.Default.Key.Protection ? "Default" : Protection.ToString()); } diff --git a/ClosedXML/Excel/Style/XLStyleValue.cs b/ClosedXML/Excel/Style/XLStyleValue.cs index a2745bf..f046f91 100644 --- a/ClosedXML/Excel/Style/XLStyleValue.cs +++ b/ClosedXML/Excel/Style/XLStyleValue.cs @@ -1,4 +1,5 @@ using ClosedXML.Excel.Caching; +using System; namespace ClosedXML.Excel { @@ -17,6 +18,7 @@ Border = XLBorderValue.Default.Key, Fill = XLFillValue.Default.Key, Font = XLFontValue.Default.Key, + IncludeQuotePrefix = false, NumberFormat = XLNumberFormatValue.Default.Key, Protection = XLProtectionValue.Default.Key }); @@ -31,6 +33,8 @@ public XLFontValue Font { get; private set; } + public Boolean IncludeQuotePrefix { get; private set; } + public XLNumberFormatValue NumberFormat { get; private set; } public XLProtectionValue Protection { get; private set; } @@ -42,6 +46,7 @@ Border = XLBorderValue.FromKey(Key.Border); Fill = XLFillValue.FromKey(Key.Fill); Font = XLFontValue.FromKey(Key.Font); + IncludeQuotePrefix = key.IncludeQuotePrefix; NumberFormat = XLNumberFormatValue.FromKey(Key.NumberFormat); Protection = XLProtectionValue.FromKey(Key.Protection); } diff --git a/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/Excel/Tables/IXLTable.cs index 1b47e86..e6c2912 100644 --- a/ClosedXML/Excel/Tables/IXLTable.cs +++ b/ClosedXML/Excel/Tables/IXLTable.cs @@ -1,4 +1,5 @@ using System; +using System.Collections; using System.Collections.Generic; using System.Data; @@ -6,7 +7,7 @@ { public interface IXLTable : IXLRange { - IXLBaseAutoFilter AutoFilter { get; } + IXLAutoFilter AutoFilter { get; } IXLTableRange DataRange { get; } Boolean EmphasizeFirstColumn { get; set; } Boolean EmphasizeLastColumn { get; set; } @@ -32,7 +33,91 @@ IXLRangeRow HeadersRow(); /// - /// Resizes the table to the specified range. + /// Appends the IEnumerable data elements and returns the range of the new rows. + /// + /// The IEnumerable data. + /// if set to true propagate extra columns' values and formulas. + /// + /// The range of the new rows. + /// + IXLRange AppendData(IEnumerable data, Boolean propagateExtraColumns = false); + + /// + /// Appends the IEnumerable data elements and returns the range of the new rows. + /// + /// The IEnumerable data. + /// if set to true the data will be transposed before inserting. + /// if set to true propagate extra columns' values and formulas. + /// + /// The range of the new rows. + /// + IXLRange AppendData(IEnumerable data, Boolean transpose, Boolean propagateExtraColumns = false); + + /// + /// Appends the data of a data table and returns the range of the new rows. + /// + /// The data table. + /// if set to true propagate extra columns' values and formulas. + /// + /// The range of the new rows. + /// + IXLRange AppendData(DataTable dataTable, Boolean propagateExtraColumns = false); + + /// + /// Appends the IEnumerable data elements and returns the range of the new rows. + /// + /// + /// The table data. + /// if set to true propagate extra columns' values and formulas. + /// + /// The range of the new rows. + /// + IXLRange AppendData(IEnumerable data, Boolean propagateExtraColumns = false); + + /// + /// Replaces the IEnumerable data elements and returns the table's data range. + /// + /// The IEnumerable data. + /// if set to true propagate extra columns' values and formulas. + /// + /// The table's data range. + /// + IXLRange ReplaceData(IEnumerable data, Boolean propagateExtraColumns = false); + + /// + /// Replaces the IEnumerable data elements and returns the table's data range. + /// + /// The IEnumerable data. + /// if set to true the data will be transposed before inserting. + /// if set to true propagate extra columns' values and formulas. + /// + /// The table's data range. + /// + IXLRange ReplaceData(IEnumerable data, Boolean transpose, Boolean propagateExtraColumns = false); + + /// + /// Replaces the data from the records of a data table and returns the table's data range. + /// + /// The data table. + /// if set to true propagate extra columns' values and formulas. + /// + /// The table's data range. + /// + IXLRange ReplaceData(DataTable dataTable, Boolean propagateExtraColumns = false); + + /// + /// Replaces the IEnumerable data elements as a table and the table's data range. + /// + /// + /// The table data. + /// if set to true propagate extra columns' values and formulas. + /// + /// The table's data range. + /// + IXLRange ReplaceData(IEnumerable data, Boolean propagateExtraColumns = false); + + /// + /// Resizes the table to the specified range address. /// /// The new table range. /// @@ -86,7 +171,7 @@ /// IXLTable Resize(int firstCellRow, int firstCellColumn, int lastCellRow, int lastCellColumn); - new IXLBaseAutoFilter SetAutoFilter(); + new IXLAutoFilter SetAutoFilter(); IXLTable SetEmphasizeFirstColumn(); diff --git a/ClosedXML/Excel/Tables/IXLTableField.cs b/ClosedXML/Excel/Tables/IXLTableField.cs index 4c2fb00..c1e9107 100644 --- a/ClosedXML/Excel/Tables/IXLTableField.cs +++ b/ClosedXML/Excel/Tables/IXLTableField.cs @@ -20,6 +20,7 @@ { /// /// Gets the corresponding column for this table field. + /// Includes the header and footer cells /// /// /// The column. @@ -27,6 +28,31 @@ IXLRangeColumn Column { get; } /// + /// Gets the collection of data cells for this field + /// Excludes the header and footer cells + /// + /// + /// The data cells + /// + IXLCells DataCells { get; } + + /// + /// Gets the footer cell for the table field. + /// + /// + /// The footer cell. + /// + IXLCell TotalsCell { get; } + + /// + /// Gets the header cell for the table field. + /// + /// + /// The header cell. + /// + IXLCell HeaderCell { get; } + + /// /// Gets the index of the column (0-based). /// /// diff --git a/ClosedXML/Excel/Tables/IXLTableRange.cs b/ClosedXML/Excel/Tables/IXLTableRange.cs index 3e6f3f9..f57237d 100644 --- a/ClosedXML/Excel/Tables/IXLTableRange.cs +++ b/ClosedXML/Excel/Tables/IXLTableRange.cs @@ -1,27 +1,49 @@ +// Keep this file CodeMaid organised and cleaned 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; } + IXLTableRow FirstRow(Func predicate = null); + + IXLTableRow FirstRowUsed(Boolean includeFormats, Func predicate = null); + + IXLTableRow FirstRowUsed(Func predicate = null); + new IXLTableRows InsertRowsAbove(int numberOfRows); + new IXLTableRows InsertRowsBelow(int numberOfRows); + + IXLTableRow LastRow(Func predicate = null); + + IXLTableRow LastRowUsed(Boolean includeFormats, Func predicate = null); + + IXLTableRow LastRowUsed(Func predicate = null); + + /// + /// Rows the specified row. + /// + /// 1-based row number relative to the first row of this range. + /// + new IXLTableRow Row(int row); + + IXLTableRows Rows(Func predicate = null); + + /// + /// Returns a subset of the rows + /// + /// The first row to return. 1-based row number relative to the first row of this range. + /// The last row to return. 1-based row number relative to the first row of this range. + /// + new IXLTableRows Rows(int firstRow, int lastRow); + + new IXLTableRows Rows(string rows); + + IXLTableRows RowsUsed(Boolean includeFormats, Func predicate = null); + + IXLTableRows RowsUsed(Func predicate = null); } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Tables/IXLTableRows.cs b/ClosedXML/Excel/Tables/IXLTableRows.cs index 5e10d8a..27eda87 100644 --- a/ClosedXML/Excel/Tables/IXLTableRows.cs +++ b/ClosedXML/Excel/Tables/IXLTableRows.cs @@ -1,3 +1,4 @@ +// Keep this file CodeMaid organised and cleaned using System; using System.Collections.Generic; @@ -5,6 +6,8 @@ { public interface IXLTableRows : IEnumerable { + IXLStyle Style { get; set; } + /// /// Adds a table row to this group. /// @@ -27,14 +30,14 @@ /// if set to true will return all cells with a value or a style different than the default. IXLCells CellsUsed(Boolean includeFormats); - IXLStyle Style { get; set; } - /// /// Clears the contents of these rows. /// /// Specify what you want to clear. IXLTableRows Clear(XLClearOptions clearOptions = XLClearOptions.All); + void Delete(); + void Select(); } } diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index b75900d..1739063 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -1,4 +1,5 @@ using System; +using System.Collections; using System.Collections.Generic; using System.Data; using System.Diagnostics; @@ -12,6 +13,7 @@ internal class XLTable : XLRange, IXLTable { #region Private fields + private string _name; internal bool _showTotalsRow; internal HashSet _uniqueNames; @@ -145,18 +147,19 @@ { XLRange range; + var firstDataRowNumber = 1; + var lastDataRowNumber = RowCount(); + if (_showHeaderRow) - { - range = _showTotalsRow - ? Range(2, 1, RowCount() - 1, ColumnCount()) - : Range(2, 1, RowCount(), ColumnCount()); - } - else - { - range = _showTotalsRow - ? Range(1, 1, RowCount() - 1, ColumnCount()) - : Range(1, 1, RowCount(), ColumnCount()); - } + firstDataRowNumber++; + + if (_showTotalsRow) + lastDataRowNumber--; + + if (firstDataRowNumber > lastDataRowNumber) + return null; + + range = Range(firstDataRowNumber, 1, lastDataRowNumber, ColumnCount()); return new XLTableRange(range, this); } @@ -176,7 +179,7 @@ } } - public new IXLBaseAutoFilter SetAutoFilter() + public new IXLAutoFilter SetAutoFilter() { return AutoFilter; } @@ -242,6 +245,9 @@ _showTotalsRow = value; + // Invalidate fields' columns + this.Fields.Cast().ForEach(f => f.Column = null); + if (_showTotalsRow) { AutoFilter.Range = Worksheet.Range( @@ -526,7 +532,7 @@ return this; } - IXLBaseAutoFilter IXLTable.AutoFilter + IXLAutoFilter IXLTable.AutoFilter { get { return AutoFilter; } } @@ -666,6 +672,9 @@ _showHeaderRow = value; + // Invalidate fields' columns + this.Fields.Cast().ForEach(f => f.Column = null); + if (_showHeaderRow) HeadersRow().DataType = XLDataType.Text; } @@ -822,7 +831,7 @@ public IXLTable CopyTo(IXLWorksheet targetSheet) { - return CopyTo((XLWorksheet) targetSheet); + return CopyTo((XLWorksheet)targetSheet); } internal IXLTable CopyTo(XLWorksheet targetSheet, bool copyData = true) @@ -860,5 +869,162 @@ } return newTable; } + + #region Append and replace data + + public IXLRange AppendData(IEnumerable data, Boolean propagateExtraColumns = false) + { + return AppendData(data, transpose: false, propagateExtraColumns: propagateExtraColumns); + } + + public IXLRange AppendData(IEnumerable data, bool transpose, Boolean propagateExtraColumns = false) + { + var castedData = data?.Cast(); + if (!(castedData?.Any() ?? false) || data is String) + return null; + + var numberOfNewRows = castedData.Count(); + + var lastRowOfOldRange = this.DataRange.LastRow(); + lastRowOfOldRange.InsertRowsBelow(numberOfNewRows); + this.Fields.Cast().ForEach(f => f.Column = null); + + var insertedRange = lastRowOfOldRange.RowBelow().FirstCell().InsertData(castedData, transpose); + + PropagateExtraColumns(insertedRange.ColumnCount(), lastRowOfOldRange.RowNumber()); + + return insertedRange; + } + + public IXLRange AppendData(DataTable dataTable, Boolean propagateExtraColumns = false) + { + return AppendData(dataTable.Rows.Cast(), propagateExtraColumns: propagateExtraColumns); + } + + public IXLRange AppendData(IEnumerable data, Boolean propagateExtraColumns = false) + { + if (!(data?.Any() ?? false) || data is String) + return null; + + var numberOfNewRows = data.Count(); + + if (numberOfNewRows == 0) + return null; + + var lastRowOfOldRange = this.DataRange.LastRow(); + lastRowOfOldRange.InsertRowsBelow(numberOfNewRows); + this.Fields.Cast().ForEach(f => f.Column = null); + + var insertedRange = lastRowOfOldRange.RowBelow().FirstCell().InsertData(data); + + PropagateExtraColumns(insertedRange.ColumnCount(), lastRowOfOldRange.RowNumber()); + + return insertedRange; + } + + public IXLRange ReplaceData(IEnumerable data, Boolean propagateExtraColumns = false) + { + return ReplaceData(data, transpose: false, propagateExtraColumns: propagateExtraColumns); + } + + public IXLRange ReplaceData(IEnumerable data, bool transpose, Boolean propagateExtraColumns = false) + { + var castedData = data?.Cast(); + if (!(castedData?.Any() ?? false) || data is String) + throw new InvalidOperationException("Cannot replace table data with empty enumerable."); + + var firstDataRowNumber = this.DataRange.FirstRow().RowNumber(); + var lastDataRowNumber = this.DataRange.LastRow().RowNumber(); + + // Resize table + var sizeDifference = castedData.Count() - this.DataRange.RowCount(); + if (sizeDifference > 0) + this.DataRange.LastRow().InsertRowsBelow(sizeDifference); + else if (sizeDifference < 0) + { + this.DataRange.Rows + ( + lastDataRowNumber + sizeDifference + 1 - firstDataRowNumber + 1, + lastDataRowNumber - firstDataRowNumber + 1 + ) + .Delete(); + + // No propagation needed when reducing the number of rows + propagateExtraColumns = false; + } + + if (sizeDifference != 0) + // Invalidate table fields' columns + this.Fields.Cast().ForEach(f => f.Column = null); + + var replacedRange = this.DataRange.FirstCell().InsertData(castedData, transpose); + + if (propagateExtraColumns) + PropagateExtraColumns(replacedRange.ColumnCount(), lastDataRowNumber); + + return replacedRange; + } + + public IXLRange ReplaceData(DataTable dataTable, Boolean propagateExtraColumns = false) + { + return ReplaceData(dataTable.Rows.Cast(), propagateExtraColumns: propagateExtraColumns); + } + + public IXLRange ReplaceData(IEnumerable data, Boolean propagateExtraColumns = false) + { + if (!(data?.Any() ?? false) || data is String) + throw new InvalidOperationException("Cannot replace table data with empty enumerable."); + + var firstDataRowNumber = this.DataRange.FirstRow().RowNumber(); + var lastDataRowNumber = this.DataRange.LastRow().RowNumber(); + + // Resize table + var sizeDifference = data.Count() - this.DataRange.RowCount(); + if (sizeDifference > 0) + this.DataRange.LastRow().InsertRowsBelow(sizeDifference); + else if (sizeDifference < 0) + { + this.DataRange.Rows + ( + lastDataRowNumber + sizeDifference + 1 - firstDataRowNumber + 1, + lastDataRowNumber - firstDataRowNumber + 1 + ) + .Delete(); + + // No propagation needed when reducing the number of rows + propagateExtraColumns = false; + } + + if (sizeDifference != 0) + // Invalidate table fields' columns + this.Fields.Cast().ForEach(f => f.Column = null); + + var replacedRange = this.DataRange.FirstCell().InsertData(data); + + if (propagateExtraColumns) + PropagateExtraColumns(replacedRange.ColumnCount(), lastDataRowNumber); + + return replacedRange; + } + + private void PropagateExtraColumns(int numberOfNonExtraColumns, int previousLastDataRow) + { + for (var i = numberOfNonExtraColumns; i < this.Fields.Count(); i++) + { + var field = this.Field(i); + + var cell = this.Worksheet.Cell(previousLastDataRow, field.Column.ColumnNumber()); + field.Column.Cells(c => c.Address.RowNumber > previousLastDataRow) + .ForEach(c => + { + if (cell.HasFormula) + c.FormulaR1C1 = cell.FormulaR1C1; + else + c.Value = cell.Value; + }); + } + } + + #endregion Append and replace data } } diff --git a/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/Excel/Tables/XLTableField.cs index 9b6ee39..c074518 100644 --- a/ClosedXML/Excel/Tables/XLTableField.cs +++ b/ClosedXML/Excel/Tables/XLTableField.cs @@ -32,6 +32,36 @@ } return _column; } + internal set + { + _column = value; + } + } + + public IXLCells DataCells + { + get + { + return Column.Cells(c => + { + if (table.ShowHeaderRow && c == HeaderCell) + return false; + if (table.ShowTotalsRow && c == TotalsCell) + return false; + return true; + }); + } + } + + public IXLCell HeaderCell + { + get + { + if (!table.ShowHeaderRow) + return null; + + return Column.FirstCell(); + } } public Int32 Index @@ -53,6 +83,8 @@ } set { + if (name == value) return; + if (table.ShowHeaderRow) (table.HeadersRow(false).Cell(Index + 1) as XLCell).SetValue(value, false); @@ -63,6 +95,17 @@ public IXLTable Table { get { return table; } } + public IXLCell TotalsCell + { + get + { + if (!table.ShowTotalsRow) + return null; + + return Column.LastCell(); + } + } + public String TotalsRowFormulaA1 { get { return table.TotalsRow().Cell(Index + 1).FormulaA1; } diff --git a/ClosedXML/Excel/Tables/XLTableRange.cs b/ClosedXML/Excel/Tables/XLTableRange.cs index 24c30ab..dc17f8b 100644 --- a/ClosedXML/Excel/Tables/XLTableRange.cs +++ b/ClosedXML/Excel/Tables/XLTableRange.cs @@ -123,10 +123,12 @@ } public new XLTableRow Row(int row) { - if (row <= 0 || row > XLHelper.MaxRowNumber) + if (row <= 0 || row > XLHelper.MaxRowNumber + RangeAddress.FirstAddress.RowNumber - 1) { - throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", - XLHelper.MaxRowNumber)); + throw new ArgumentOutOfRangeException( + nameof(row), + String.Format("Row number must be between 1 and {0}", XLHelper.MaxRowNumber + RangeAddress.FirstAddress.RowNumber - 1) + ); } return new XLTableRow(this, base.Row(row)); @@ -150,8 +152,9 @@ { var retVal = new XLTableRows(Worksheet.Style); - for (int ro = firstRow; ro <= lastRow; ro++) - retVal.Add(Row(ro)); + for (int rowNumber = firstRow; rowNumber <= lastRow; rowNumber++) + retVal.Add(Row(rowNumber)); + return retVal; } diff --git a/ClosedXML/Excel/Tables/XLTableRow.cs b/ClosedXML/Excel/Tables/XLTableRow.cs index 3acff51..f863b67 100644 --- a/ClosedXML/Excel/Tables/XLTableRow.cs +++ b/ClosedXML/Excel/Tables/XLTableRow.cs @@ -40,7 +40,7 @@ private XLTableRow RowShift(Int32 rowsToShift) { - return _tableRange.Row(RowNumber() + rowsToShift); + return _tableRange.Row(RowNumber() - _tableRange.FirstRow().RowNumber() + 1 + rowsToShift); } #region XLTableRow Above @@ -110,7 +110,6 @@ public new void Delete() { Delete(XLShiftDeletedCells.ShiftCellsUp); - _tableRange.Table.ExpandTableRows(-1); } } } diff --git a/ClosedXML/Excel/Tables/XLTableRows.cs b/ClosedXML/Excel/Tables/XLTableRows.cs index f02dffa..9676504 100644 --- a/ClosedXML/Excel/Tables/XLTableRows.cs +++ b/ClosedXML/Excel/Tables/XLTableRows.cs @@ -1,19 +1,20 @@ using System; +using System.Collections; using System.Collections.Generic; +using System.Linq; namespace ClosedXML.Excel { - using System.Collections; - internal class XLTableRows : XLStylizedBase, IXLTableRows, IXLStylized { private readonly List _ranges = new List(); - + public XLTableRows(IXLStyle defaultStyle) : base((defaultStyle as XLStyle).Value) { } #region IXLStylized Members + public override IEnumerable Styles { get @@ -63,9 +64,15 @@ return this; } - public void Add(IXLTableRow range) + public void Delete() { - _ranges.Add((XLTableRow)range); + _ranges.OrderByDescending(r => r.RowNumber()).ForEach(r => r.Delete()); + _ranges.Clear(); + } + + public void Add(IXLTableRow tableRow) + { + _ranges.Add((XLTableRow)tableRow); } public IEnumerator GetEnumerator() diff --git a/ClosedXML/Excel/XLAddressLight.cs b/ClosedXML/Excel/XLAddressLight.cs deleted file mode 100644 index 730e475..0000000 --- a/ClosedXML/Excel/XLAddressLight.cs +++ /dev/null @@ -1,18 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; - -namespace ClosedXML.Excel -{ - internal struct XLAddressLight - { - public XLAddressLight(Int32 rowNumber, Int32 columnNumber) - { - RowNumber = rowNumber; - ColumnNumber = columnNumber; - } - public Int32 RowNumber; - public Int32 ColumnNumber; - } -} diff --git a/ClosedXML/Excel/XLWSContentManager.cs b/ClosedXML/Excel/XLWSContentManager.cs deleted file mode 100644 index 0f67b92..0000000 --- a/ClosedXML/Excel/XLWSContentManager.cs +++ /dev/null @@ -1,111 +0,0 @@ -using System; -using System.Collections.Generic; -using System.Linq; -using DocumentFormat.OpenXml; -using DocumentFormat.OpenXml.Spreadsheet; - -namespace ClosedXML.Excel -{ - internal class XLWSContentManager - { - public enum XLWSContents - { - SheetProperties = 1, - SheetDimension = 2, - SheetViews = 3, - SheetFormatProperties = 4, - Columns = 5, - SheetData = 6, - SheetCalculationProperties = 7, - SheetProtection = 8, - ProtectedRanges = 9, - Scenarios = 10, - AutoFilter = 11, - SortState = 12, - DataConsolidate = 13, - CustomSheetViews = 14, - MergeCells = 15, - PhoneticProperties = 16, - ConditionalFormatting = 17, - DataValidations = 18, - Hyperlinks = 19, - PrintOptions = 20, - PageMargins = 21, - PageSetup = 22, - HeaderFooter = 23, - RowBreaks = 24, - ColumnBreaks = 25, - CustomProperties = 26, - CellWatches = 27, - IgnoredErrors = 28, - SmartTags = 29, - Drawing = 30, - LegacyDrawing = 31, - LegacyDrawingHeaderFooter = 32, - DrawingHeaderFooter = 33, - Picture = 34, - OleObjects = 35, - Controls = 36, - AlternateContent = 37, - WebPublishItems = 38, - TableParts = 39, - WorksheetExtensionList = 40 - } - private readonly Dictionary contents = new Dictionary(); - - public XLWSContentManager(Worksheet opWorksheet) - { - contents.Add(XLWSContents.SheetProperties, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.SheetDimension, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.SheetViews, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.SheetFormatProperties, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.Columns, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.SheetData, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.SheetCalculationProperties, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.SheetProtection, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.ProtectedRanges, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.Scenarios, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.AutoFilter, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.SortState, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.DataConsolidate, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.CustomSheetViews, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.MergeCells, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.PhoneticProperties, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.ConditionalFormatting, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.DataValidations, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.Hyperlinks, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.PrintOptions, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.PageMargins, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.PageSetup, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.HeaderFooter, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.RowBreaks, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.ColumnBreaks, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.CustomProperties, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.CellWatches, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.IgnoredErrors, opWorksheet.Elements().LastOrDefault()); - //contents.Add(XLWSContents.SmartTags, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.Drawing, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.LegacyDrawing, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.LegacyDrawingHeaderFooter, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.DrawingHeaderFooter, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.Picture, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.OleObjects, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.Controls, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.AlternateContent, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.WebPublishItems, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.TableParts, opWorksheet.Elements().LastOrDefault()); - contents.Add(XLWSContents.WorksheetExtensionList, opWorksheet.Elements().LastOrDefault()); - } - - public void SetElement(XLWSContents content, OpenXmlElement element) - { - contents[content] = element; - } - - public OpenXmlElement GetPreviousElementFor(XLWSContents content) - { - var max = contents.Where(kp => (Int32)kp.Key < (Int32)content && kp.Value != null).Max(kp => kp.Key); - return contents[max]; - } - } -} diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 6df9e8e..62b96d0 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -452,8 +452,9 @@ var pivotTableDefinition = pivotTablePart.PivotTableDefinition; var target = ws.FirstCell(); - if (pivotTableDefinition.Location != null && pivotTableDefinition.Location.Reference != null && pivotTableDefinition.Location.Reference.HasValue) + if (pivotTableDefinition?.Location?.Reference?.HasValue ?? false) { + ws.Range(pivotTableDefinition.Location.Reference.Value).Clear(XLClearOptions.All); target = ws.Range(pivotTableDefinition.Location.Reference.Value).FirstCell(); } @@ -584,11 +585,15 @@ var pivotTableStyle = pivotTableDefinition.GetFirstChild(); if (pivotTableStyle != null) { - pt.Theme = (XLPivotTableTheme)Enum.Parse(typeof(XLPivotTableTheme), pivotTableStyle.Name); - pt.ShowRowHeaders = pivotTableStyle.ShowRowHeaders; - pt.ShowColumnHeaders = pivotTableStyle.ShowColumnHeaders; - pt.ShowRowStripes = pivotTableStyle.ShowRowStripes; - pt.ShowColumnStripes = pivotTableStyle.ShowColumnStripes; + if (pivotTableStyle.Name != null) + pt.Theme = (XLPivotTableTheme)Enum.Parse(typeof(XLPivotTableTheme), pivotTableStyle.Name); + else + pt.Theme = XLPivotTableTheme.None; + + pt.ShowRowHeaders = OpenXmlHelper.GetBooleanValueAsBool(pivotTableStyle.ShowRowHeaders, false); + pt.ShowColumnHeaders = OpenXmlHelper.GetBooleanValueAsBool(pivotTableStyle.ShowColumnHeaders, false); + pt.ShowRowStripes = OpenXmlHelper.GetBooleanValueAsBool(pivotTableStyle.ShowRowStripes, false); + pt.ShowColumnStripes = OpenXmlHelper.GetBooleanValueAsBool(pivotTableStyle.ShowColumnStripes, false); } // Subtotal configuration @@ -715,7 +720,7 @@ .Distinct().ToList(); pivotValue.BaseField = col.FirstCell().GetValue(); - + if (df.BaseItem?.Value != null) { var bi = (int)df.BaseItem.Value; @@ -732,11 +737,11 @@ { foreach (var pageField in pivotTableDefinition.PageFields.Cast()) { - var pf = pivotTableDefinition.PivotFields.ElementAt((int)pageField.Field.Value) as PivotField; + var pf = pivotTableDefinition.PivotFields.ElementAt(pageField.Field.Value) as PivotField; if (pf == null) continue; - var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt((int)pageField.Field.Value) as CacheField; + var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt(pageField.Field.Value) as CacheField; var filterName = pf.Name?.Value ?? cacheField.Name?.Value; @@ -746,45 +751,51 @@ else rf = pt.ReportFilters.Add(filterName); + var openXmlItems = new List(); if ((pageField.Item?.HasValue ?? false) && pf.Items.Any() && cacheField.SharedItems.Any()) { - var item = pf.Items.ElementAt(Convert.ToInt32(pageField.Item.Value)) as Item; - if (item == null) + if (!(pf.Items.ElementAt(Convert.ToInt32(pageField.Item.Value)) is Item item)) continue; - var sharedItem = cacheField.SharedItems.ElementAt(Convert.ToInt32((uint)item.Index)); - var numberItem = sharedItem as NumberItem; - var stringItem = sharedItem as StringItem; - var dateTimeItem = sharedItem as DateTimeItem; - - if (numberItem != null) - rf.AddSelectedValue(Convert.ToDouble(numberItem.Val.Value)); - else if (dateTimeItem != null) - rf.AddSelectedValue(Convert.ToDateTime(dateTimeItem.Val.Value)); - else if (stringItem != null) - rf.AddSelectedValue(stringItem.Val.Value); - else - throw new NotImplementedException(); + openXmlItems.Add(item); } else if (OpenXmlHelper.GetBooleanValueAsBool(pf.MultipleItemSelectionAllowed, false)) { - foreach (var item in pf.Items.Cast()) - { - if (item.Hidden == null || !BooleanValue.ToBoolean(item.Hidden)) - { - var sharedItem = cacheField.SharedItems.ElementAt(Convert.ToInt32((uint)item.Index)); - var numberItem = sharedItem as NumberItem; - var stringItem = sharedItem as StringItem; - var dateTimeItem = sharedItem as DateTimeItem; + openXmlItems.AddRange(pf.Items.Cast()); + } - if (numberItem != null) + foreach (var item in openXmlItems) + { + if (!OpenXmlHelper.GetBooleanValueAsBool(item.Hidden, false) + && (item.Index?.HasValue ?? false)) + { + var sharedItem = cacheField.SharedItems.ElementAt(Convert.ToInt32((uint)item.Index)); + // https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.shareditems.aspx + switch (sharedItem) + { + case NumberItem numberItem: rf.AddSelectedValue(Convert.ToDouble(numberItem.Val.Value)); - else if (dateTimeItem != null) + break; + + case DateTimeItem dateTimeItem: rf.AddSelectedValue(Convert.ToDateTime(dateTimeItem.Val.Value)); - else if (stringItem != null) + break; + + case BooleanItem booleanItem: + rf.AddSelectedValue(Convert.ToBoolean(booleanItem.Val.Value)); + break; + + case StringItem stringItem: rf.AddSelectedValue(stringItem.Val.Value); - else + break; + + case MissingItem missingItem: + case ErrorItem errorItem: + // Ignore missing and error items + break; + + default: throw new NotImplementedException(); } } @@ -896,7 +907,7 @@ { var oneCellAnchor = anchor as Xdr.OneCellAnchor; var from = LoadMarker(ws, oneCellAnchor.FromMarker); - picture.MoveTo(from.Address, from.Offset); + picture.MoveTo(from.Cell, from.Offset); } else if (anchor is Xdr.TwoCellAnchor) { @@ -906,7 +917,7 @@ if (twoCellAnchor.EditAs == null || !twoCellAnchor.EditAs.HasValue || twoCellAnchor.EditAs.Value == Xdr.EditAsValues.TwoCell) { - picture.MoveTo(from.Address, from.Offset, to.Address, to.Offset); + picture.MoveTo(from.Cell, from.Offset, to.Cell, to.Offset); } else if (twoCellAnchor.EditAs.Value == Xdr.EditAsValues.Absolute) { @@ -921,7 +932,7 @@ } else if (twoCellAnchor.EditAs.Value == Xdr.EditAsValues.OneCell) { - picture.MoveTo(from.Address, from.Offset); + picture.MoveTo(from.Cell, from.Offset); } } } @@ -934,12 +945,12 @@ return Convert.ToInt32(emu * resolution / 914400); } - private static IXLMarker LoadMarker(IXLWorksheet ws, Xdr.MarkerType marker) + private static XLMarker LoadMarker(IXLWorksheet ws, Xdr.MarkerType marker) { var row = Math.Min(XLHelper.MaxRowNumber, Math.Max(1, Convert.ToInt32(marker.RowId.InnerText) + 1)); var column = Math.Min(XLHelper.MaxColumnNumber, Math.Max(1, Convert.ToInt32(marker.ColumnId.InnerText) + 1)); return new XLMarker( - ws.Cell(row, column).Address, + ws.Cell(row, column), new Point( ConvertFromEnglishMetricUnits(Convert.ToInt32(marker.ColumnOffset.InnerText), GraphicsUtils.Graphics.DpiX), ConvertFromEnglishMetricUnits(Convert.ToInt32(marker.RowOffset.InnerText), GraphicsUtils.Graphics.DpiY) @@ -1695,7 +1706,7 @@ if (source.Style != null) setBorder(source.Style.Value.ToClosedXml()); if (source.Color != null) - setColor(GetColor(source.Color)); + setColor(source.Color.ToClosedXMLColor(_colorList)); } } @@ -1720,22 +1731,28 @@ if (differentialFillFormat) { if (openXMLFill.PatternFill.BackgroundColor != null) - closedXMLFill.BackgroundColor = GetColor(openXMLFill.PatternFill.BackgroundColor); + closedXMLFill.BackgroundColor = openXMLFill.PatternFill.BackgroundColor.ToClosedXMLColor(_colorList); + else + closedXMLFill.BackgroundColor = XLColor.FromIndex(64); } else { // yes, source is foreground! if (openXMLFill.PatternFill.ForegroundColor != null) - closedXMLFill.BackgroundColor = GetColor(openXMLFill.PatternFill.ForegroundColor); + closedXMLFill.BackgroundColor = openXMLFill.PatternFill.ForegroundColor.ToClosedXMLColor(_colorList); + else + closedXMLFill.BackgroundColor = XLColor.FromIndex(64); } break; default: if (openXMLFill.PatternFill.ForegroundColor != null) - closedXMLFill.PatternColor = GetColor(openXMLFill.PatternFill.ForegroundColor); + closedXMLFill.PatternColor = openXMLFill.PatternFill.ForegroundColor.ToClosedXMLColor(_colorList); if (openXMLFill.PatternFill.BackgroundColor != null) - closedXMLFill.BackgroundColor = GetColor(openXMLFill.PatternFill.BackgroundColor); + closedXMLFill.BackgroundColor = openXMLFill.PatternFill.BackgroundColor.ToClosedXMLColor(_colorList); + else + closedXMLFill.BackgroundColor = XLColor.FromIndex(64); break; } } @@ -1745,9 +1762,9 @@ if (fontSource == null) return; fontBase.Bold = GetBoolean(fontSource.Elements().FirstOrDefault()); - var fontColor = GetColor(fontSource.Elements().FirstOrDefault()); - if (fontColor.HasValue) - fontBase.FontColor = fontColor; + var fontColor = fontSource.Elements().FirstOrDefault(); + if (fontColor != null) + fontBase.FontColor = fontColor.ToClosedXMLColor(_colorList); var fontFamilyNumbering = fontSource.Elements().FirstOrDefault(); @@ -2153,7 +2170,7 @@ } } - private static void LoadAutoFilterSort(AutoFilter af, XLWorksheet ws, IXLBaseAutoFilter autoFilter) + private static void LoadAutoFilterSort(AutoFilter af, XLWorksheet ws, IXLAutoFilter autoFilter) { var sort = af.Elements().FirstOrDefault(); if (sort != null) @@ -2174,7 +2191,18 @@ if (sp == null) return; if (sp.Sheet != null) ws.Protection.Protected = sp.Sheet.Value; - if (sp.Password != null) ws.Protection.PasswordHash = sp.Password.Value; + + var algorithmName = sp.AlgorithmName?.Value ?? string.Empty; + if (String.IsNullOrEmpty(algorithmName)) + { + ws.Protection.PasswordHash = sp.Password?.Value ?? string.Empty; + } + else + { + var hashValue = sp.HashValue?.Value ?? string.Empty; + var saltValue = sp.SaltValue?.Value ?? string.Empty; + // Continue for now. + } if (sp.FormatCells != null) ws.Protection.FormatCells = !sp.FormatCells.Value; if (sp.FormatColumns != null) ws.Protection.FormatColumns = !sp.FormatColumns.Value; if (sp.FormatRows != null) ws.Protection.FormatRows = !sp.FormatRows.Value; @@ -2343,8 +2371,7 @@ if (xlConditionalFormat != null) { var negativeFillColor = conditionalFormattingRule.Descendants().SingleOrDefault(); - var color = new DocumentFormat.OpenXml.Spreadsheet.Color { Rgb = negativeFillColor.Rgb }; - xlConditionalFormat.Colors.Add(this.GetColor(color)); + xlConditionalFormat.Colors.Add(negativeFillColor.ToClosedXMLColor(_colorList)); } } } @@ -2375,7 +2402,7 @@ } foreach (var c in element.Elements()) { - conditionalFormat.Colors.Add(GetColor(c)); + conditionalFormat.Colors.Add(c.ToClosedXMLColor(_colorList)); } } @@ -2432,7 +2459,7 @@ if (sheetProperty == null) return; if (sheetProperty.TabColor != null) - ws.TabColor = GetColor(sheetProperty.TabColor); + ws.TabColor = sheetProperty.TabColor.ToClosedXMLColor(_colorList); if (sheetProperty.OutlineProperties != null) { @@ -2631,34 +2658,6 @@ Properties.Title = p.Title; } - private XLColor GetColor(ColorType color) - { - XLColor retVal = null; - if (color != null) - { - if (color.Rgb != null) - { - String htmlColor = "#" + color.Rgb.Value; - Color thisColor; - if (!_colorList.ContainsKey(htmlColor)) - { - thisColor = ColorStringParser.ParseFromHtml(htmlColor); - _colorList.Add(htmlColor, thisColor); - } - else - thisColor = _colorList[htmlColor]; - retVal = XLColor.FromColor(thisColor); - } - else if (color.Indexed != null && color.Indexed <= 64) - retVal = XLColor.FromIndex((Int32)color.Indexed.Value); - else if (color.Theme != null) - { - retVal = color.Tint != null ? XLColor.FromTheme((XLThemeColor)color.Theme.Value, color.Tint.Value) : XLColor.FromTheme((XLThemeColor)color.Theme.Value); - } - } - return retVal ?? XLColor.NoColor; - } - private void ApplyStyle(IXLStylized xlStylized, Int32 styleIndex, Stylesheet s, Fills fills, Borders borders, Fonts fonts, NumberingFormats numberingFormats) { @@ -2668,6 +2667,8 @@ var xlStyle = XLStyle.Default.Key; + xlStyle.IncludeQuotePrefix = OpenXmlHelper.GetBooleanValueAsBool(cellFormat.QuotePrefix, false); + if (cellFormat.ApplyProtection != null) { var protection = cellFormat.Protection; @@ -2738,45 +2739,40 @@ if (bottomBorder.Style != null) xlBorder.BottomBorder = bottomBorder.Style.Value.ToClosedXml(); - var bottomBorderColor = GetColor(bottomBorder.Color); - if (bottomBorderColor.HasValue) - xlBorder.BottomBorderColor = bottomBorderColor.Key; + if (bottomBorder.Color != null) + xlBorder.BottomBorderColor = bottomBorder.Color.ToClosedXMLColor(_colorList).Key; } var topBorder = border.TopBorder; if (topBorder != null) { if (topBorder.Style != null) xlBorder.TopBorder = topBorder.Style.Value.ToClosedXml(); - var topBorderColor = GetColor(topBorder.Color); - if (topBorderColor.HasValue) - xlBorder.TopBorderColor = topBorderColor.Key; + if (topBorder.Color != null) + xlBorder.TopBorderColor = topBorder.Color.ToClosedXMLColor(_colorList).Key; } var leftBorder = border.LeftBorder; if (leftBorder != null) { if (leftBorder.Style != null) xlBorder.LeftBorder = leftBorder.Style.Value.ToClosedXml(); - var leftBorderColor = GetColor(leftBorder.Color); - if (leftBorderColor.HasValue) - xlBorder.LeftBorderColor = leftBorderColor.Key; + if (leftBorder.Color != null) + xlBorder.LeftBorderColor = leftBorder.Color.ToClosedXMLColor(_colorList).Key; } var rightBorder = border.RightBorder; if (rightBorder != null) { if (rightBorder.Style != null) xlBorder.RightBorder = rightBorder.Style.Value.ToClosedXml(); - var rightBorderColor = GetColor(rightBorder.Color); - if (rightBorderColor.HasValue) - xlBorder.RightBorderColor = rightBorderColor.Key; + if (rightBorder.Color != null) + xlBorder.RightBorderColor = rightBorder.Color.ToClosedXMLColor(_colorList).Key; } var diagonalBorder = border.DiagonalBorder; if (diagonalBorder != null) { if (diagonalBorder.Style != null) xlBorder.DiagonalBorder = diagonalBorder.Style.Value.ToClosedXml(); - var diagonalBorderColor = GetColor(diagonalBorder.Color); - if (diagonalBorderColor.HasValue) - xlBorder.DiagonalBorderColor = diagonalBorderColor.Key; + if (diagonalBorder.Color != null) + xlBorder.DiagonalBorderColor = diagonalBorder.Color.ToClosedXMLColor(_colorList).Key; if (border.DiagonalDown != null) xlBorder.DiagonalDown = border.DiagonalDown; if (border.DiagonalUp != null) @@ -2797,9 +2793,8 @@ { xlFont.Bold = GetBoolean(font.Bold); - var fontColor = GetColor(font.Color); - if (fontColor.HasValue) - xlFont.FontColor = fontColor.Key; + if (font.Color != null) + xlFont.FontColor = font.Color.ToClosedXMLColor(_colorList).Key; if (font.FontFamilyNumbering != null && (font.FontFamilyNumbering).Val != null) { diff --git a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs index c6542ee..dbd1e87 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs @@ -139,6 +139,7 @@ public UInt32 BorderId; public UInt32 FillId; public UInt32 FontId; + public Boolean IncludeQuotePrefix; public Int32 NumberFormatId; public XLStyleValue Style; public UInt32 StyleId; diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 9e92af5..2834081 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -1,3 +1,5 @@ +using ClosedXML.Excel.ContentManagers; +using ClosedXML.Excel.Exceptions; using ClosedXML.Extensions; using ClosedXML.Utils; using DocumentFormat.OpenXml; @@ -333,14 +335,19 @@ GenerateThemePartContent(themePart); } + // Custom properties if (CustomProperties.Any()) { - document.GetPartsOfType().ToList().ForEach(p => document.DeletePart(p)); var customFilePropertiesPart = - document.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); + document.CustomFilePropertiesPart ?? document.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); GenerateCustomFilePropertiesPartContent(customFilePropertiesPart); } + else + { + if (document.CustomFilePropertiesPart != null) + document.DeletePart(document.CustomFilePropertiesPart); + } SetPackageProperties(document); // Clear list of deleted worksheets to prevent errors on multiple saves @@ -397,10 +404,14 @@ } } - private static void GenerateTables(XLWorksheet worksheet, WorksheetPart worksheetPart, SaveContext context, XLWSContentManager cm) + private static void GenerateTables(XLWorksheet worksheet, WorksheetPart worksheetPart, SaveContext context, XLWorksheetContentManager cm) { var tables = worksheet.Tables as XLTables; + var emptyTable = tables.FirstOrDefault(t => t.DataRange == null); + if (emptyTable != null) + throw new EmptyTableException($"Table '{emptyTable.Name}' should have at least 1 row."); + TableParts tableParts; if (worksheetPart.Worksheet.Elements().Any()) { @@ -408,11 +419,11 @@ } else { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.TableParts); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.TableParts); tableParts = new TableParts(); worksheetPart.Worksheet.InsertAfter(tableParts, previousElement); } - cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts); + cm.SetElement(XLWorksheetContents.TableParts, tableParts); foreach (var deletedTableRelId in tables.Deleted) { @@ -428,7 +439,7 @@ tables.Deleted.Clear(); - foreach (var xlTable in worksheet.Tables.Cast()) + foreach (var xlTable in tables.Cast()) { if (String.IsNullOrEmpty(xlTable.RelId)) xlTable.RelId = context.RelIdGenerator.GetNext(RelType.Workbook); @@ -450,7 +461,7 @@ } } - tableParts.Count = (UInt32)worksheet.Tables.Count(); + tableParts.Count = (UInt32)tables.Count(); } private void GenerateExtendedFilePropertiesPartContent(ExtendedFilePropertiesPart extendedFilePropertiesPart) @@ -1013,7 +1024,7 @@ { Val = rt.VerticalAlignment.ToOpenXml() }; var shadow = rt.Shadow ? new Shadow() : null; var fontSize = new FontSize { Val = rt.FontSize }; - var color = GetNewColor(rt.FontColor); + var color = new Color().FromClosedXMLColor(rt.FontColor); var fontName = new RunFont { Val = rt.FontName }; var fontFamilyNumbering = new FontFamily { Val = (Int32)rt.FontFamilyNumbering }; @@ -1748,10 +1759,10 @@ themePart.Theme = theme1; } - private void GenerateCustomFilePropertiesPartContent(CustomFilePropertiesPart customFilePropertiesPart1) + private void GenerateCustomFilePropertiesPartContent(CustomFilePropertiesPart customFilePropertiesPart) { - var properties2 = new DocumentFormat.OpenXml.CustomProperties.Properties(); - properties2.AddNamespaceDeclaration("vt", + var properties = new DocumentFormat.OpenXml.CustomProperties.Properties(); + properties.AddNamespaceDeclaration("vt", "http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"); var propertyId = 1; foreach (var p in CustomProperties) @@ -1791,10 +1802,10 @@ var vTBool1 = new VTBool { Text = p.GetValue().ToString().ToLower() }; customDocumentProperty.AppendChild(vTBool1); } - properties2.AppendChild(customDocumentProperty); + properties.AppendChild(customDocumentProperty); } - customFilePropertiesPart1.Properties = properties2; + customFilePropertiesPart.Properties = properties; } private void SetPackageProperties(OpenXmlPackage document) @@ -2006,14 +2017,19 @@ GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt, context); PivotTablePart pivotTablePart; - if (String.IsNullOrWhiteSpace(pt.RelId)) - pivotTablePart = worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); + var createNewPivotTablePart = String.IsNullOrWhiteSpace(pt.RelId); + if (createNewPivotTablePart) + { + var relId = context.RelIdGenerator.GetNext(RelType.Workbook); + pt.RelId = relId; + pivotTablePart = worksheetPart.AddNewPart(relId); + } else pivotTablePart = worksheetPart.GetPartById(pt.RelId) as PivotTablePart; GeneratePivotTablePartContent(pivotTablePart, pt, pivotCache.CacheId, context); - if (String.IsNullOrWhiteSpace(pt.RelId)) + if (createNewPivotTablePart) pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook)); } } @@ -2052,7 +2068,9 @@ case XLPivotTableSourceType.Range: worksheetSource.Name = null; worksheetSource.Reference = source.RangeAddress.ToStringRelative(includeSheet: false); - worksheetSource.Sheet = source.RangeAddress.Worksheet.Name.EscapeSheetName(); + + // Do not quote worksheet name with whitespace here - issue #955 + worksheetSource.Sheet = source.RangeAddress.Worksheet.Name; break; case XLPivotTableSourceType.Table: @@ -2134,10 +2152,11 @@ ptfi.DataType = XLDataType.Number; ptfi.MixedDataType = false; ptfi.DistinctValues = fieldValueCells - .Select(cell => cell.GetDouble()) - .Distinct() - .Cast() - .ToArray(); + .Where(cell => cell.TryGetValue(out Double _)) + .Select(cell => cell.CachedValue.CastTo()) + .Distinct() + .Cast() + .ToArray(); var allInteger = ptfi.DistinctValues.All(v => int.TryParse(v.ToString(), out int val)); if (allInteger) sharedItems.ContainsInteger = true; @@ -2168,7 +2187,8 @@ ptfi.DataType = XLDataType.DateTime; ptfi.MixedDataType = false; ptfi.DistinctValues = fieldValueCells - .Select(cell => cell.GetDateTime()) + .Where(cell => cell.TryGetValue(out DateTime _)) + .Select(cell => cell.CachedValue.CastTo()) .Distinct() .Cast() .ToArray(); @@ -2197,12 +2217,13 @@ if (!ptfi.MixedDataType && ptfi.DataType == XLDataType.Text) ptfi.DistinctValues = fieldValueCells - .Select(cell => cell.Value) - .Cast() + .Where(cell => cell.TryGetValue(out String _)) + .Select(cell => cell.CachedValue.CastTo()) .Distinct(StringComparer.OrdinalIgnoreCase) .ToArray(); else ptfi.DistinctValues = fieldValueCells + .Where(cell => cell.TryGetValue(out String _)) .Select(cell => cell.GetString()) .Distinct(StringComparer.OrdinalIgnoreCase) .ToArray(); @@ -2401,6 +2422,8 @@ } } + var orderedPageFields = new SortedDictionary(); + foreach (var xlpf in pt.Fields.Cast()) { var ptfi = pti.Fields[xlpf.SourceName]; @@ -2469,6 +2492,7 @@ else if (pt.ReportFilters.Contains(xlpf.SourceName)) { labelOrFilterField = pt.ReportFilters.Get(xlpf.SourceName); + var sortOrderIndex = pt.ReportFilters.IndexOf(labelOrFilterField); location.ColumnsPerPage = 1; location.RowPageCount = 1; @@ -2477,7 +2501,7 @@ var pageField = new PageField { Hierarchy = -1, - Field = pt.Fields.IndexOf(xlpf) + Field = pt.Fields.IndexOf(xlpf), }; if (labelOrFilterField.SelectedValues.Count == 1) @@ -2509,11 +2533,29 @@ if (values.Contains(selectedValue)) pageField.Item = Convert.ToUInt32(values.IndexOf(selectedValue)); } + else if (ptfi.DataType == XLDataType.Boolean) + { + var values = ptfi.DistinctValues + .Select(v => Convert.ToBoolean(v)) + .ToList(); + var selectedValue = Convert.ToBoolean(labelOrFilterField.SelectedValues.Single()); + if (values.Contains(selectedValue)) + pageField.Item = Convert.ToUInt32(values.IndexOf(selectedValue)); + } + else if (ptfi.DataType == XLDataType.TimeSpan) + { + var values = ptfi.DistinctValues + .Cast() + .ToList(); + var selectedValue = (TimeSpan)labelOrFilterField.SelectedValues.Single(); + if (values.Contains(selectedValue)) + pageField.Item = Convert.ToUInt32(values.IndexOf(selectedValue)); + } else throw new NotImplementedException(); } - pageFields.AppendChild(pageField); + orderedPageFields.Add(sortOrderIndex, pageField); } if ((labelOrFilterField?.SelectedValues?.Count ?? 0) > 1) @@ -2693,6 +2735,7 @@ if (pt.ReportFilters.Any()) { + pageFields.Append(orderedPageFields.Values); pageFields.Count = Convert.ToUInt32(pageFields.Count()); pivotTableDefinition.AppendChild(pageFields); } @@ -2756,26 +2799,31 @@ pivotTableDefinition.AppendChild(dataFields); } - pivotTableDefinition.AppendChild(new PivotTableStyle + var pts = new PivotTableStyle { - Name = Enum.GetName(typeof(XLPivotTableTheme), pt.Theme), ShowRowHeaders = pt.ShowRowHeaders, ShowColumnHeaders = pt.ShowColumnHeaders, ShowRowStripes = pt.ShowRowStripes, ShowColumnStripes = pt.ShowColumnStripes - }); + }; + + if (pt.Theme != XLPivotTableTheme.None) + pts.Name = Enum.GetName(typeof(XLPivotTableTheme), pt.Theme); + + pivotTableDefinition.AppendChild(pts); #region Excel 2010 Features var pivotTableDefinitionExtensionList = new PivotTableDefinitionExtensionList(); - var pivotTableDefinitionExtension = new PivotTableDefinitionExtension - { Uri = "{962EF5D1-5CA2-4c93-8EF4-DBF5C05439D2}" }; - pivotTableDefinitionExtension.AddNamespaceDeclaration("x14", - "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); + var pivotTableDefinitionExtension = new PivotTableDefinitionExtension { Uri = "{962EF5D1-5CA2-4c93-8EF4-DBF5C05439D2}" }; + pivotTableDefinitionExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); var pivotTableDefinition2 = new DocumentFormat.OpenXml.Office2010.Excel.PivotTableDefinition - { EnableEdit = pt.EnableCellEditing, HideValuesRow = !pt.ShowValuesRow }; + { + EnableEdit = pt.EnableCellEditing, + HideValuesRow = !pt.ShowValuesRow + }; pivotTableDefinition2.AddNamespaceDeclaration("xm", "http://schemas.microsoft.com/office/excel/2006/main"); pivotTableDefinitionExtension.AppendChild(pivotTableDefinition2); @@ -3046,21 +3094,21 @@ case Drawings.XLPicturePlacement.MoveAndSize: var moveAndSizeFromMarker = pic.Markers[Drawings.XLMarkerPosition.TopLeft]; - if (moveAndSizeFromMarker == null) moveAndSizeFromMarker = new Drawings.XLMarker(picture.Worksheet.Cell("A1").Address); + if (moveAndSizeFromMarker == null) moveAndSizeFromMarker = new Drawings.XLMarker(picture.Worksheet.Cell("A1")); fMark = new Xdr.FromMarker { - ColumnId = new Xdr.ColumnId((moveAndSizeFromMarker.Address.ColumnNumber - 1).ToInvariantString()), - RowId = new Xdr.RowId((moveAndSizeFromMarker.Address.RowNumber - 1).ToInvariantString()), + ColumnId = new Xdr.ColumnId((moveAndSizeFromMarker.ColumnNumber - 1).ToInvariantString()), + RowId = new Xdr.RowId((moveAndSizeFromMarker.RowNumber - 1).ToInvariantString()), ColumnOffset = new Xdr.ColumnOffset(ConvertToEnglishMetricUnits(moveAndSizeFromMarker.Offset.X, GraphicsUtils.Graphics.DpiX).ToInvariantString()), RowOffset = new Xdr.RowOffset(ConvertToEnglishMetricUnits(moveAndSizeFromMarker.Offset.Y, GraphicsUtils.Graphics.DpiY).ToInvariantString()) }; var moveAndSizeToMarker = pic.Markers[Drawings.XLMarkerPosition.BottomRight]; - if (moveAndSizeToMarker == null) moveAndSizeToMarker = new Drawings.XLMarker(picture.Worksheet.Cell("A1").Address, new System.Drawing.Point(picture.Width, picture.Height)); + if (moveAndSizeToMarker == null) moveAndSizeToMarker = new Drawings.XLMarker(picture.Worksheet.Cell("A1"), new System.Drawing.Point(picture.Width, picture.Height)); tMark = new Xdr.ToMarker { - ColumnId = new Xdr.ColumnId((moveAndSizeToMarker.Address.ColumnNumber - 1).ToInvariantString()), - RowId = new Xdr.RowId((moveAndSizeToMarker.Address.RowNumber - 1).ToInvariantString()), + ColumnId = new Xdr.ColumnId((moveAndSizeToMarker.ColumnNumber - 1).ToInvariantString()), + RowId = new Xdr.RowId((moveAndSizeToMarker.RowNumber - 1).ToInvariantString()), ColumnOffset = new Xdr.ColumnOffset(ConvertToEnglishMetricUnits(moveAndSizeToMarker.Offset.X, GraphicsUtils.Graphics.DpiX).ToInvariantString()), RowOffset = new Xdr.RowOffset(ConvertToEnglishMetricUnits(moveAndSizeToMarker.Offset.Y, GraphicsUtils.Graphics.DpiY).ToInvariantString()) }; @@ -3093,11 +3141,11 @@ case Drawings.XLPicturePlacement.Move: var moveFromMarker = pic.Markers[Drawings.XLMarkerPosition.TopLeft]; - if (moveFromMarker == null) moveFromMarker = new Drawings.XLMarker(picture.Worksheet.Cell("A1").Address); + if (moveFromMarker == null) moveFromMarker = new Drawings.XLMarker(picture.Worksheet.Cell("A1")); fMark = new Xdr.FromMarker { - ColumnId = new Xdr.ColumnId((moveFromMarker.Address.ColumnNumber - 1).ToInvariantString()), - RowId = new Xdr.RowId((moveFromMarker.Address.RowNumber - 1).ToInvariantString()), + ColumnId = new Xdr.ColumnId((moveFromMarker.ColumnNumber - 1).ToInvariantString()), + RowId = new Xdr.RowId((moveFromMarker.RowNumber - 1).ToInvariantString()), ColumnOffset = new Xdr.ColumnOffset(ConvertToEnglishMetricUnits(moveFromMarker.Offset.X, GraphicsUtils.Graphics.DpiX).ToInvariantString()), RowOffset = new Xdr.RowOffset(ConvertToEnglishMetricUnits(moveFromMarker.Offset.Y, GraphicsUtils.Graphics.DpiY).ToInvariantString()) }; @@ -3280,6 +3328,7 @@ FontId = 0, FillId = 0, BorderId = 0, + IncludeQuotePrefix = false, NumberFormatId = 0 //AlignmentId = 0 }); @@ -3386,7 +3435,8 @@ FontId = context.SharedFonts[xlStyle.Font].FontId, FillId = allSharedFills[xlStyle.Fill].FillId, BorderId = allSharedBorders[xlStyle.Border].BorderId, - NumberFormatId = numberFormatId + NumberFormatId = numberFormatId, + IncludeQuotePrefix = xlStyle.IncludeQuotePrefix }); } @@ -3405,7 +3455,7 @@ foreach (CellFormat f in workbookStylesPart.Stylesheet.CellFormats) { styleId++; - if (CellFormatsAreEqual(f, ss.Value)) + if (CellFormatsAreEqual(f, ss.Value, compareAlignment: true)) break; } if (styleId == -1) @@ -3572,7 +3622,7 @@ { var info = styleInfo; var foundOne = - workbookStylesPart.Stylesheet.CellFormats.Cast().Any(f => CellFormatsAreEqual(f, info)); + workbookStylesPart.Stylesheet.CellFormats.Cast().Any(f => CellFormatsAreEqual(f, info, compareAlignment: true)); if (foundOne) continue; @@ -3611,7 +3661,7 @@ var info = styleInfo; var foundOne = workbookStylesPart.Stylesheet.CellStyleFormats.Cast().Any( - f => CellFormatsAreEqual(f, info)); + f => CellFormatsAreEqual(f, info, compareAlignment: false)); if (foundOne) continue; @@ -3654,6 +3704,7 @@ FontId = styleInfo.FontId, FillId = styleInfo.FillId, BorderId = styleInfo.BorderId, + QuotePrefix = OpenXmlHelper.GetBooleanValue(styleInfo.IncludeQuotePrefix, false), ApplyNumberFormat = true, ApplyAlignment = true, ApplyFill = ApplyFill(styleInfo), @@ -3672,18 +3723,29 @@ }; } - private static bool CellFormatsAreEqual(CellFormat f, StyleInfo styleInfo) + /// + /// Check if two style are equivalent. + /// + /// Style in the OpenXML format. + /// Style in the ClosedXML format. + /// Flag specifying whether or not compare the alignments of two styles. + /// Styles in x:cellStyleXfs section do not include alignment so we don't have to compare it in this case. + /// Styles in x:cellXfs section, on the opposite, do include alignments, and we must compare them. + /// + /// True if two formats are equivalent, false otherwise. + private static bool CellFormatsAreEqual(CellFormat f, StyleInfo styleInfo, bool compareAlignment) { return f.BorderId != null && styleInfo.BorderId == f.BorderId && f.FillId != null && styleInfo.FillId == f.FillId && f.FontId != null && styleInfo.FontId == f.FontId && f.NumberFormatId != null && styleInfo.NumberFormatId == f.NumberFormatId + && QuotePrefixesAreEqual(f.QuotePrefix, styleInfo.IncludeQuotePrefix) && (f.ApplyFill == null && styleInfo.Style.Fill == XLFillValue.Default || f.ApplyFill != null && f.ApplyFill == ApplyFill(styleInfo)) && (f.ApplyBorder == null && styleInfo.Style.Border == XLBorderValue.Default || f.ApplyBorder != null && f.ApplyBorder == ApplyBorder(styleInfo)) - && (f.Alignment == null || AlignmentsAreEqual(f.Alignment, styleInfo.Style.Alignment)) + && (!compareAlignment || AlignmentsAreEqual(f.Alignment, styleInfo.Style.Alignment)) && ProtectionsAreEqual(f.Protection, styleInfo.Style.Protection) ; } @@ -3701,6 +3763,11 @@ return p.Equals(xlProtection.Key); } + private static bool QuotePrefixesAreEqual(BooleanValue quotePrefix, Boolean includeQuotePrefix) + { + return OpenXmlHelper.GetBooleanValueAsBool(quotePrefix, false) == includeQuotePrefix; + } + private static bool AlignmentsAreEqual(Alignment alignment, XLAlignmentValue xlAlignment) { if (alignment != null) @@ -3780,7 +3847,7 @@ var leftBorder = new LeftBorder { Style = borderInfo.Border.LeftBorder.ToOpenXml() }; if (borderInfo.Border.LeftBorderColor != XLBorderValue.Default.LeftBorderColor || ignoreMod) { - var leftBorderColor = GetNewColor(borderInfo.Border.LeftBorderColor); + var leftBorderColor = new Color().FromClosedXMLColor(borderInfo.Border.LeftBorderColor); leftBorder.AppendChild(leftBorderColor); } border.AppendChild(leftBorder); @@ -3791,7 +3858,7 @@ var rightBorder = new RightBorder { Style = borderInfo.Border.RightBorder.ToOpenXml() }; if (borderInfo.Border.RightBorderColor != XLBorderValue.Default.RightBorderColor || ignoreMod) { - var rightBorderColor = GetNewColor(borderInfo.Border.RightBorderColor); + var rightBorderColor = new Color().FromClosedXMLColor(borderInfo.Border.RightBorderColor); rightBorder.AppendChild(rightBorderColor); } border.AppendChild(rightBorder); @@ -3802,7 +3869,7 @@ var topBorder = new TopBorder { Style = borderInfo.Border.TopBorder.ToOpenXml() }; if (borderInfo.Border.TopBorderColor != XLBorderValue.Default.TopBorderColor || ignoreMod) { - var topBorderColor = GetNewColor(borderInfo.Border.TopBorderColor); + var topBorderColor = new Color().FromClosedXMLColor(borderInfo.Border.TopBorderColor); topBorder.AppendChild(topBorderColor); } border.AppendChild(topBorder); @@ -3813,7 +3880,7 @@ var bottomBorder = new BottomBorder { Style = borderInfo.Border.BottomBorder.ToOpenXml() }; if (borderInfo.Border.BottomBorderColor != XLBorderValue.Default.BottomBorderColor || ignoreMod) { - var bottomBorderColor = GetNewColor(borderInfo.Border.BottomBorderColor); + var bottomBorderColor = new Color().FromClosedXMLColor(borderInfo.Border.BottomBorderColor); bottomBorder.AppendChild(bottomBorderColor); } border.AppendChild(bottomBorder); @@ -3825,7 +3892,7 @@ if (borderInfo.Border.DiagonalBorderColor != XLBorderValue.Default.DiagonalBorderColor || ignoreMod) if (borderInfo.Border.DiagonalBorderColor != null) { - var DiagonalBorderColor = GetNewColor(borderInfo.Border.DiagonalBorderColor); + var DiagonalBorderColor = new Color().FromClosedXMLColor(borderInfo.Border.DiagonalBorderColor); DiagonalBorder.AppendChild(DiagonalBorderColor); } border.AppendChild(DiagonalBorder); @@ -3847,45 +3914,40 @@ { if (b.DiagonalBorder.Style != null) nb.DiagonalBorder = b.DiagonalBorder.Style.Value.ToClosedXml(); - var bColor = GetColor(b.DiagonalBorder.Color); - if (bColor.HasValue) - nb.DiagonalBorderColor = bColor.Key; + if (b.DiagonalBorder.Color != null) + nb.DiagonalBorderColor = b.DiagonalBorder.Color.ToClosedXMLColor(_colorList).Key; } if (b.LeftBorder != null) { if (b.LeftBorder.Style != null) nb.LeftBorder = b.LeftBorder.Style.Value.ToClosedXml(); - var bColor = GetColor(b.LeftBorder.Color); - if (bColor.HasValue) - nb.LeftBorderColor = bColor.Key; + if (b.LeftBorder.Color != null) + nb.LeftBorderColor = b.LeftBorder.Color.ToClosedXMLColor(_colorList).Key; } if (b.RightBorder != null) { if (b.RightBorder.Style != null) nb.RightBorder = b.RightBorder.Style.Value.ToClosedXml(); - var bColor = GetColor(b.RightBorder.Color); - if (bColor.HasValue) - nb.RightBorderColor = bColor.Key; + if (b.RightBorder.Color != null) + nb.RightBorderColor = b.RightBorder.Color.ToClosedXMLColor(_colorList).Key; } if (b.TopBorder != null) { if (b.TopBorder.Style != null) nb.TopBorder = b.TopBorder.Style.Value.ToClosedXml(); - var bColor = GetColor(b.TopBorder.Color); - if (bColor.HasValue) - nb.TopBorderColor = bColor.Key; + if (b.TopBorder.Color != null) + nb.TopBorderColor = b.TopBorder.Color.ToClosedXMLColor(_colorList).Key; } if (b.BottomBorder != null) { if (b.BottomBorder.Style != null) nb.BottomBorder = b.BottomBorder.Style.Value.ToClosedXml(); - var bColor = GetColor(b.BottomBorder.Color); - if (bColor.HasValue) - nb.BottomBorderColor = bColor.Key; + if (b.BottomBorder.Color != null) + nb.BottomBorderColor = b.BottomBorder.Color.ToClosedXMLColor(_colorList).Key; } return nb.Equals(xlBorder.Key); @@ -3957,61 +4019,18 @@ break; case XLFillPatternValues.Solid: + if (differentialFillFormat) { patternFill.AppendChild(new ForegroundColor { Auto = true }); - backgroundColor = new BackgroundColor(); - switch (fillInfo.Fill.BackgroundColor.ColorType) - { - case XLColorType.Color: - backgroundColor.Rgb = fillInfo.Fill.BackgroundColor.Color.ToHex(); - break; - - case XLColorType.Indexed: - // 64 is 'transparent' and should be ignored for differential formats - if (fillInfo.Fill.BackgroundColor.Indexed != 64) - backgroundColor.Indexed = (UInt32)fillInfo.Fill.BackgroundColor.Indexed; - break; - - case XLColorType.Theme: - backgroundColor.Theme = (UInt32)fillInfo.Fill.BackgroundColor.ThemeColor; - - if (fillInfo.Fill.BackgroundColor.ThemeTint != 0) - backgroundColor.Tint = fillInfo.Fill.BackgroundColor.ThemeTint; - - break; - } - + backgroundColor = new BackgroundColor().FromClosedXMLColor(fillInfo.Fill.BackgroundColor, true); if (backgroundColor.HasAttributes) patternFill.AppendChild(backgroundColor); } else { // ClosedXML Background color to be populated into OpenXML fgColor - foregroundColor = new ForegroundColor(); - switch (fillInfo.Fill.BackgroundColor.ColorType) - { - case XLColorType.Color: - foregroundColor.Rgb = fillInfo.Fill.BackgroundColor.Color.ToHex(); - break; - - case XLColorType.Indexed: - // 64 is 'transparent' and should be ignored for differential formats - if (fillInfo.Fill.BackgroundColor.Indexed != 64) - foregroundColor.Indexed = (UInt32)fillInfo.Fill.BackgroundColor.Indexed; - - //foregroundColor.Indexed = (UInt32)fillInfo.Fill.BackgroundColor.Indexed; - break; - - case XLColorType.Theme: - foregroundColor.Theme = (UInt32)fillInfo.Fill.BackgroundColor.ThemeColor; - - if (fillInfo.Fill.BackgroundColor.ThemeTint != 0) - foregroundColor.Tint = fillInfo.Fill.BackgroundColor.ThemeTint; - - break; - } - + foregroundColor = new ForegroundColor().FromClosedXMLColor(fillInfo.Fill.BackgroundColor); if (foregroundColor.HasAttributes) patternFill.AppendChild(foregroundColor); } @@ -4019,49 +4038,11 @@ default: - foregroundColor = new ForegroundColor(); - switch (fillInfo.Fill.PatternColor.ColorType) - { - case XLColorType.Color: - foregroundColor.Rgb = fillInfo.Fill.PatternColor.Color.ToHex(); - break; - - case XLColorType.Indexed: - foregroundColor.Indexed = (UInt32)fillInfo.Fill.PatternColor.Indexed; - break; - - case XLColorType.Theme: - foregroundColor.Theme = (UInt32)fillInfo.Fill.PatternColor.ThemeColor; - - if (fillInfo.Fill.PatternColor.ThemeTint != 0) - foregroundColor.Tint = fillInfo.Fill.PatternColor.ThemeTint; - - break; - } - + foregroundColor = new ForegroundColor().FromClosedXMLColor(fillInfo.Fill.PatternColor); if (foregroundColor.HasAttributes) patternFill.AppendChild(foregroundColor); - backgroundColor = new BackgroundColor(); - switch (fillInfo.Fill.BackgroundColor.ColorType) - { - case XLColorType.Color: - backgroundColor.Rgb = fillInfo.Fill.BackgroundColor.Color.ToHex(); - break; - - case XLColorType.Indexed: - backgroundColor.Indexed = (UInt32)fillInfo.Fill.BackgroundColor.Indexed; - break; - - case XLColorType.Theme: - backgroundColor.Theme = (UInt32)fillInfo.Fill.BackgroundColor.ThemeColor; - - if (fillInfo.Fill.BackgroundColor.ThemeTint != 0) - backgroundColor.Tint = fillInfo.Fill.BackgroundColor.ThemeTint; - - break; - } - + backgroundColor = new BackgroundColor().FromClosedXMLColor(fillInfo.Fill.BackgroundColor); if (backgroundColor.HasAttributes) patternFill.AppendChild(backgroundColor); @@ -4136,7 +4117,7 @@ var fontSize = fontInfo.Font.FontSize != XLFontValue.Default.FontSize || ignoreMod ? new FontSize { Val = fontInfo.Font.FontSize } : null; - var color = fontInfo.Font.FontColor != XLFontValue.Default.FontColor || ignoreMod ? GetNewColor(fontInfo.Font.FontColor) : null; + var color = fontInfo.Font.FontColor != XLFontValue.Default.FontColor || ignoreMod ? new Color().FromClosedXMLColor(fontInfo.Font.FontColor) : null; var fontName = fontInfo.Font.FontName != XLFontValue.Default.FontName || ignoreMod ? new FontName { Val = fontInfo.Font.FontName } @@ -4175,38 +4156,6 @@ return font; } - private static Color GetNewColor(XLColor xlColor) - { - var color = new Color(); - if (xlColor.ColorType == XLColorType.Color) - color.Rgb = xlColor.Color.ToHex(); - else if (xlColor.ColorType == XLColorType.Indexed) - color.Indexed = (UInt32)xlColor.Indexed; - else - { - color.Theme = (UInt32)xlColor.ThemeColor; - if (xlColor.ThemeTint != 0) - color.Tint = xlColor.ThemeTint; - } - return color; - } - - private static TabColor GetTabColor(XLColor xlColor) - { - var color = new TabColor(); - if (xlColor.ColorType == XLColorType.Color) - color.Rgb = xlColor.Color.ToHex(); - else if (xlColor.ColorType == XLColorType.Indexed) - color.Indexed = (UInt32)xlColor.Indexed; - else - { - color.Theme = (UInt32)xlColor.ThemeColor; - if (xlColor.ThemeTint != 0) - color.Tint = xlColor.ThemeTint; - } - return color; - } - private bool FontsAreEqual(Font f, XLFontValue xlFont) { var nf = XLFontValue.Default.Key; @@ -4229,9 +4178,8 @@ nf.Shadow = f.Shadow != null; if (f.FontSize != null) nf.FontSize = f.FontSize.Val; - var fColor = GetColor(f.Color); - if (fColor.HasValue) - nf.FontColor = fColor.Key; + if (f.Color != null) + nf.FontColor = f.Color.ToClosedXMLColor(_colorList).Key; if (f.FontName != null) nf.FontName = f.FontName.Val; if (f.FontFamilyNumbering != null) @@ -4324,7 +4272,7 @@ #endregion Worksheet - var cm = new XLWSContentManager(worksheetPart.Worksheet); + var cm = new XLWorksheetContentManager(worksheetPart.Worksheet); #region SheetProperties @@ -4332,10 +4280,10 @@ worksheetPart.Worksheet.SheetProperties = new SheetProperties(); worksheetPart.Worksheet.SheetProperties.TabColor = xlWorksheet.TabColor.HasValue - ? GetTabColor(xlWorksheet.TabColor) + ? new TabColor().FromClosedXMLColor(xlWorksheet.TabColor) : null; - cm.SetElement(XLWSContentManager.XLWSContents.SheetProperties, worksheetPart.Worksheet.SheetProperties); + cm.SetElement(XLWorksheetContents.SheetProperties, worksheetPart.Worksheet.SheetProperties); if (worksheetPart.Worksheet.SheetProperties.OutlineProperties == null) worksheetPart.Worksheet.SheetProperties.OutlineProperties = new OutlineProperties(); @@ -4376,12 +4324,12 @@ if (worksheetPart.Worksheet.SheetDimension == null) worksheetPart.Worksheet.SheetDimension = new SheetDimension { Reference = sheetDimensionReference }; - cm.SetElement(XLWSContentManager.XLWSContents.SheetDimension, worksheetPart.Worksheet.SheetDimension); + cm.SetElement(XLWorksheetContents.SheetDimension, worksheetPart.Worksheet.SheetDimension); if (worksheetPart.Worksheet.SheetViews == null) worksheetPart.Worksheet.SheetViews = new SheetViews(); - cm.SetElement(XLWSContentManager.XLWSContents.SheetViews, worksheetPart.Worksheet.SheetViews); + cm.SetElement(XLWorksheetContents.SheetViews, worksheetPart.Worksheet.SheetViews); var sheetView = (SheetView)worksheetPart.Worksheet.SheetViews.FirstOrDefault(); if (sheetView == null) @@ -4390,6 +4338,8 @@ worksheetPart.Worksheet.SheetViews.AppendChild(sheetView); } + var svcm = new XLSheetViewContentManager(sheetView); + if (xlWorksheet.TabSelected) sheetView.TabSelected = true; else @@ -4449,9 +4399,11 @@ if (pane == null) { pane = new Pane(); - sheetView.AppendChild(pane); + sheetView.InsertAt(pane, 0); } + svcm.SetElement(XLSheetViewContents.Pane, pane); + pane.State = PaneStateValues.FrozenSplit; Double hSplit = xlWorksheet.SheetView.SplitColumn; Double ySplit = xlWorksheet.SheetView.SplitRow; @@ -4469,6 +4421,7 @@ { pane = null; sheetView.RemoveAllChildren(); + svcm.SetElement(XLSheetViewContents.Pane, null); } else sheetView.TopLeftCell = null; @@ -4476,6 +4429,7 @@ if (xlWorksheet.SelectedRanges.Any() || xlWorksheet.ActiveCell != null) { sheetView.RemoveAllChildren(); + svcm.SetElement(XLSheetViewContents.Selection, null); var firstSelection = xlWorksheet.SelectedRanges.FirstOrDefault(); @@ -4499,11 +4453,12 @@ selection.SequenceOfReferences = new ListValue { InnerText = String.Join(" ", seqRef.Distinct().ToArray()) }; - sheetView.Append(selection); + sheetView.InsertAfter(selection, svcm.GetPreviousElementFor(XLSheetViewContents.Selection)); + svcm.SetElement(XLSheetViewContents.Selection, selection); }; - populateSelection(new Selection()); // If a pane exists, we need to set the active pane too + // Yes, this might lead to 2 Selection elements! if (pane != null) { populateSelection(new Selection() @@ -4511,6 +4466,7 @@ Pane = pane.ActivePane }); } + populateSelection(new Selection()); } if (xlWorksheet.SheetView.ZoomScale == 100) @@ -4548,7 +4504,7 @@ if (worksheetPart.Worksheet.SheetFormatProperties == null) worksheetPart.Worksheet.SheetFormatProperties = new SheetFormatProperties(); - cm.SetElement(XLWSContentManager.XLWSContents.SheetFormatProperties, + cm.SetElement(XLWorksheetContents.SheetFormatProperties, worksheetPart.Worksheet.SheetFormatProperties); worksheetPart.Worksheet.SheetFormatProperties.DefaultRowHeight = xlWorksheet.RowHeight.SaveRound(); @@ -4587,12 +4543,12 @@ { if (!worksheetPart.Worksheet.Elements().Any()) { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Columns); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.Columns); worksheetPart.Worksheet.InsertAfter(new Columns(), previousElement); } var columns = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.Columns, columns); + cm.SetElement(XLWorksheetContents.Columns, columns); var sheetColumnsByMin = columns.Elements().ToDictionary(c => c.Min.Value, c => c); //Dictionary sheetColumnsByMax = columns.Elements().ToDictionary(c => c.Max.Value, c => c); @@ -4702,7 +4658,7 @@ if (!columns.Any()) { worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.Columns, null); + cm.SetElement(XLWorksheetContents.Columns, null); } } @@ -4712,12 +4668,12 @@ if (!worksheetPart.Worksheet.Elements().Any()) { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetData); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.SheetData); worksheetPart.Worksheet.InsertAfter(new SheetData(), previousElement); } var sheetData = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.SheetData, sheetData); + cm.SetElement(XLWorksheetContents.SheetData, sheetData); var lastRow = 0; var existingSheetDataRows = @@ -4976,12 +4932,12 @@ { if (!worksheetPart.Worksheet.Elements().Any()) { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetProtection); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.SheetProtection); worksheetPart.Worksheet.InsertAfter(new SheetProtection(), previousElement); } var sheetProtection = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, sheetProtection); + cm.SetElement(XLWorksheetContents.SheetProtection, sheetProtection); var protection = xlWorksheet.Protection; sheetProtection.Sheet = protection.Protected; @@ -5005,7 +4961,7 @@ else { worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, null); + cm.SetElement(XLWorksheetContents.SheetProtection, null); } #endregion SheetProtection @@ -5015,17 +4971,17 @@ worksheetPart.Worksheet.RemoveAllChildren(); if (xlWorksheet.AutoFilter.Enabled) { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.AutoFilter); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.AutoFilter); worksheetPart.Worksheet.InsertAfter(new AutoFilter(), previousElement); var autoFilter = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, autoFilter); + cm.SetElement(XLWorksheetContents.AutoFilter, autoFilter); PopulateAutoFilter(xlWorksheet.AutoFilter, autoFilter); } else { - cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, null); + cm.SetElement(XLWorksheetContents.AutoFilter, null); } #endregion AutoFilter @@ -5036,12 +4992,12 @@ { if (!worksheetPart.Worksheet.Elements().Any()) { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.MergeCells); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.MergeCells); worksheetPart.Worksheet.InsertAfter(new MergeCells(), previousElement); } var mergeCells = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, mergeCells); + cm.SetElement(XLWorksheetContents.MergeCells, mergeCells); mergeCells.RemoveAllChildren(); foreach (var mergeCell in (xlWorksheet).Internals.MergedRanges.Select( @@ -5054,7 +5010,7 @@ else { worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, null); + cm.SetElement(XLWorksheetContents.MergeCells, null); } #endregion MergeCells @@ -5064,12 +5020,12 @@ if (!xlWorksheet.ConditionalFormats.Any()) { worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, null); + cm.SetElement(XLWorksheetContents.ConditionalFormatting, null); } else { worksheetPart.Worksheet.RemoveAllChildren(); - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ConditionalFormatting); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.ConditionalFormatting); var conditionalFormats = xlWorksheet.ConditionalFormats.ToList(); // Required for IndexOf method @@ -5093,7 +5049,7 @@ } worksheetPart.Worksheet.InsertAfter(conditionalFormatting, previousElement); previousElement = conditionalFormatting; - cm.SetElement(XLWSContentManager.XLWSContents.ConditionalFormatting, conditionalFormatting); + cm.SetElement(XLWorksheetContents.ConditionalFormatting, conditionalFormatting); } } @@ -5102,12 +5058,12 @@ { if (!worksheetPart.Worksheet.Elements().Any()) { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.WorksheetExtensionList); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.WorksheetExtensionList); worksheetPart.Worksheet.InsertAfter(new WorksheetExtensionList(), previousElement); } WorksheetExtensionList worksheetExtensionList = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.WorksheetExtensionList, worksheetExtensionList); + cm.SetElement(XLWorksheetContents.WorksheetExtensionList, worksheetExtensionList); var conditionalFormattings = worksheetExtensionList.Descendants().SingleOrDefault(); if (conditionalFormattings == null || !conditionalFormattings.Any()) @@ -5156,18 +5112,18 @@ if (!xlWorksheet.DataValidations.Any(d => d.IsDirty())) { worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, null); + cm.SetElement(XLWorksheetContents.DataValidations, null); } else { if (!worksheetPart.Worksheet.Elements().Any()) { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.DataValidations); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.DataValidations); worksheetPart.Worksheet.InsertAfter(new DataValidations(), previousElement); } var dataValidations = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, dataValidations); + cm.SetElement(XLWorksheetContents.DataValidations, dataValidations); dataValidations.RemoveAllChildren(); xlWorksheet.DataValidations.Consolidate(); @@ -5211,18 +5167,18 @@ if (!xlWorksheet.Hyperlinks.Any()) { worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, null); + cm.SetElement(XLWorksheetContents.Hyperlinks, null); } else { if (!worksheetPart.Worksheet.Elements().Any()) { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.Hyperlinks); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.Hyperlinks); worksheetPart.Worksheet.InsertAfter(new Hyperlinks(), previousElement); } var hyperlinks = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, hyperlinks); + cm.SetElement(XLWorksheetContents.Hyperlinks, hyperlinks); hyperlinks.RemoveAllChildren(); foreach (var hl in xlWorksheet.Hyperlinks) { @@ -5254,12 +5210,12 @@ if (!worksheetPart.Worksheet.Elements().Any()) { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PrintOptions); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.PrintOptions); worksheetPart.Worksheet.InsertAfter(new PrintOptions(), previousElement); } var printOptions = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.PrintOptions, printOptions); + cm.SetElement(XLWorksheetContents.PrintOptions, printOptions); printOptions.HorizontalCentered = xlWorksheet.PageSetup.CenterHorizontally; printOptions.VerticalCentered = xlWorksheet.PageSetup.CenterVertically; @@ -5272,12 +5228,12 @@ if (!worksheetPart.Worksheet.Elements().Any()) { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageMargins); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.PageMargins); worksheetPart.Worksheet.InsertAfter(new PageMargins(), previousElement); } var pageMargins = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.PageMargins, pageMargins); + cm.SetElement(XLWorksheetContents.PageMargins, pageMargins); pageMargins.Left = xlWorksheet.PageSetup.Margins.Left; pageMargins.Right = xlWorksheet.PageSetup.Margins.Right; pageMargins.Top = xlWorksheet.PageSetup.Margins.Top; @@ -5291,12 +5247,12 @@ if (!worksheetPart.Worksheet.Elements().Any()) { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.PageSetup); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.PageSetup); worksheetPart.Worksheet.InsertAfter(new PageSetup(), previousElement); } var pageSetup = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.PageSetup, pageSetup); + cm.SetElement(XLWorksheetContents.PageSetup, pageSetup); pageSetup.Orientation = xlWorksheet.PageSetup.PageOrientation.ToOpenXml(); pageSetup.PaperSize = (UInt32)xlWorksheet.PageSetup.PaperSize; @@ -5361,9 +5317,9 @@ worksheetPart.Worksheet.RemoveAllChildren(); { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.HeaderFooter); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.HeaderFooter); worksheetPart.Worksheet.InsertAfter(headerFooter, previousElement); - cm.SetElement(XLWSContentManager.XLWSContents.HeaderFooter, headerFooter); + cm.SetElement(XLWorksheetContents.HeaderFooter, headerFooter); } if (((XLHeaderFooter)xlWorksheet.PageSetup.Header).Changed || ((XLHeaderFooter)xlWorksheet.PageSetup.Footer).Changed) @@ -5400,7 +5356,7 @@ { if (!worksheetPart.Worksheet.Elements().Any()) { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.RowBreaks); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.RowBreaks); worksheetPart.Worksheet.InsertAfter(new RowBreaks(), previousElement); } @@ -5430,12 +5386,12 @@ ManualPageBreak = true })) rowBreaks.AppendChild(break1); - cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, rowBreaks); + cm.SetElement(XLWorksheetContents.RowBreaks, rowBreaks); } else { worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.RowBreaks, null); + cm.SetElement(XLWorksheetContents.RowBreaks, null); } #endregion RowBreaks @@ -5447,7 +5403,7 @@ { if (!worksheetPart.Worksheet.Elements().Any()) { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ColumnBreaks); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.ColumnBreaks); worksheetPart.Worksheet.InsertAfter(new ColumnBreaks(), previousElement); } @@ -5477,12 +5433,12 @@ ManualPageBreak = true })) columnBreaks.AppendChild(break1); - cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, columnBreaks); + cm.SetElement(XLWorksheetContents.ColumnBreaks, columnBreaks); } else { worksheetPart.Worksheet.RemoveAllChildren(); - cm.SetElement(XLWSContentManager.XLWSContents.ColumnBreaks, null); + cm.SetElement(XLWorksheetContents.ColumnBreaks, null); } #endregion ColumnBreaks @@ -5519,7 +5475,7 @@ var worksheetDrawing = new Drawing { Id = worksheetPart.GetIdOfPart(worksheetPart.DrawingsPart) }; worksheetDrawing.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); worksheetPart.Worksheet.InsertBefore(worksheetDrawing, tableParts); - cm.SetElement(XLWSContentManager.XLWSContents.Drawing, worksheetPart.Worksheet.Elements().First()); + cm.SetElement(XLWorksheetContents.Drawing, worksheetPart.Worksheet.Elements().First()); } // Instead of saving a file with an empty Drawings.xml file, rather remove the .xml file @@ -5541,11 +5497,11 @@ if (!String.IsNullOrWhiteSpace(xlWorksheet.LegacyDrawingId)) { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawing); + var previousElement = cm.GetPreviousElementFor(XLWorksheetContents.LegacyDrawing); worksheetPart.Worksheet.InsertAfter(new LegacyDrawing { Id = xlWorksheet.LegacyDrawingId }, previousElement); - cm.SetElement(XLWSContentManager.XLWSContents.LegacyDrawing, worksheetPart.Worksheet.Elements().First()); + cm.SetElement(XLWorksheetContents.LegacyDrawing, worksheetPart.Worksheet.Elements().First()); } } diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index 6be26bb..0c81a1d 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -1,12 +1,12 @@ using ClosedXML.Excel.Caching; using ClosedXML.Excel.CalcEngine; using ClosedXML.Excel.Drawings; +using ClosedXML.Excel.Ranges.Index; using System; using System.Collections.Generic; using System.Drawing; using System.IO; using System.Linq; -using ClosedXML.Excel.Ranges.Index; namespace ClosedXML.Excel { @@ -31,6 +31,7 @@ /// Fake address to be used everywhere the invalid address is needed. /// internal readonly XLAddress InvalidAddress; + #endregion Fields #region Constructor @@ -171,6 +172,8 @@ get { return _name; } set { + if (_name == value) return; + if (String.IsNullOrWhiteSpace(value)) throw new ArgumentException("Worksheet names cannot be empty"); @@ -868,7 +871,7 @@ return retVal; } - IXLBaseAutoFilter IXLWorksheet.AutoFilter + IXLAutoFilter IXLWorksheet.AutoFilter { get { return AutoFilter; } } @@ -1217,7 +1220,6 @@ int rowNum = rowsShifted > 0 ? firstRow - 1 : firstRow; var model = Row(rowNum).AsRange(); - foreach (var cf in ConditionalFormats.ToList()) { var cfRanges = cf.Ranges.ToList(); @@ -1286,8 +1288,13 @@ WorksheetRangeShiftedRows(range, rowsShifted); foreach (var storedRange in rangesToShift) { - if (!ReferenceEquals(range, storedRange)) - storedRange.WorksheetRangeShiftedRows(range, rowsShifted); + if (storedRange.IsEntireColumn()) + continue; + + if (ReferenceEquals(range, storedRange)) + continue; + + storedRange.WorksheetRangeShiftedRows(range, rowsShifted); } range.WorksheetRangeShiftedRows(range, rowsShifted); } @@ -1307,9 +1314,13 @@ WorksheetRangeShiftedColumns(range, columnsShifted); foreach (var storedRange in rangesToShift) { - var addr = storedRange.RangeAddress; - if (!ReferenceEquals(range, storedRange)) - storedRange.WorksheetRangeShiftedColumns(range, columnsShifted); + if (storedRange.IsEntireRow()) + continue; + + if (ReferenceEquals(range, storedRange)) + continue; + + storedRange.WorksheetRangeShiftedColumns(range, columnsShifted); } range.WorksheetRangeShiftedColumns(range, columnsShifted); } @@ -1368,7 +1379,7 @@ else rangeAddress = range.RangeAddress; - var table = (XLTable) _rangeRepository.GetOrCreate(new XLRangeKey(XLRangeType.Table, rangeAddress)); + var table = (XLTable)_rangeRepository.GetOrCreate(new XLRangeKey(XLRangeType.Table, rangeAddress)); if (table.Name != name) table.Name = name; @@ -1383,6 +1394,7 @@ return table; } + private void CheckRangeNotInTable(XLRange range) { var overlappingTables = Tables.Where(t => t.RangeUsed().Intersects(range)); @@ -1698,6 +1710,5 @@ { _rangeRepository.Remove(new XLRangeKey(XLRangeType.Range, rangeAddress)); } - } } diff --git a/ClosedXML/Extensions.cs b/ClosedXML/Extensions.cs index 230170d..3ad5be4 100644 --- a/ClosedXML/Extensions.cs +++ b/ClosedXML/Extensions.cs @@ -314,6 +314,15 @@ || type == typeof(double) || type == typeof(decimal); } + + public static bool IsSimpleType(this Type type) + { + return type.IsPrimitive + || type == typeof(String) + || type == typeof(DateTime) + || type == typeof(TimeSpan) + || type.IsNumber(); + } } internal static class ObjectExtensions diff --git a/ClosedXML/Utils/OpenXmlHelper.cs b/ClosedXML/Utils/OpenXmlHelper.cs index 3e1af4a..d908976 100644 --- a/ClosedXML/Utils/OpenXmlHelper.cs +++ b/ClosedXML/Utils/OpenXmlHelper.cs @@ -1,9 +1,50 @@ -using DocumentFormat.OpenXml; +// Keep this file CodeMaid organised and cleaned +using ClosedXML.Excel; +using DocumentFormat.OpenXml; +using DocumentFormat.OpenXml.Spreadsheet; +using System; +using System.Collections.Generic; +using Drawing = System.Drawing; +using X14 = DocumentFormat.OpenXml.Office2010.Excel; namespace ClosedXML.Utils { internal static class OpenXmlHelper { + #region Public Methods + + /// + /// Convert color in ClosedXML representation to specified OpenXML type. + /// + /// The descendant of . + /// The existing instance of ColorType. + /// Color in ClosedXML format. + /// Flag specifiying that the color should be saved in + /// differential format (affects the transparent color processing). + /// The original color in OpenXML format. + public static T FromClosedXMLColor(this ColorType openXMLColor, XLColor xlColor, bool isDifferential = false) + where T : ColorType + { + FillFromClosedXMLColor(openXMLColor, xlColor, isDifferential); + return (T)openXMLColor; + } + + /// + /// Convert color in ClosedXML representation to specified OpenXML type. + /// + /// The descendant of . + /// The existing instance of ColorType. + /// Color in ClosedXML format. + /// Flag specifiying that the color should be saved in + /// differential format (affects the transparent color processing). + /// The original color in OpenXML format. + public static T FromClosedXMLColor(this X14.ColorType openXMLColor, XLColor xlColor, bool isDifferential = false) + where T : X14.ColorType + { + FillFromClosedXMLColor(openXMLColor, xlColor, isDifferential); + return (T)openXMLColor; + } + public static BooleanValue GetBooleanValue(bool value, bool defaultValue) { return value == defaultValue ? null : new BooleanValue(value); @@ -13,5 +54,111 @@ { return (value?.HasValue ?? false) ? value.Value : defaultValue; } + + /// + /// Convert color in OpenXML representation to ClosedXML type. + /// + /// Color in OpenXML format. + /// The dictionary containing parsed colors to optimize performance. + /// The color in ClosedXML format. + public static XLColor ToClosedXMLColor(this ColorType openXMLColor, IDictionary colorCache = null) + { + return ConvertToClosedXMLColor(openXMLColor, colorCache); + } + + /// + /// Convert color in OpenXML representation to ClosedXML type. + /// + /// Color in OpenXML format. + /// The dictionary containing parsed colors to optimize performance. + /// The color in ClosedXML format. + public static XLColor ToClosedXMLColor(this X14.ColorType openXMLColor, IDictionary colorCache = null) + { + return ConvertToClosedXMLColor(openXMLColor, colorCache); + } + + #endregion Public Methods + + #region Private Methods + + /// + /// Here we perform the actual convertion from OpenXML color to ClosedXML color. + /// + /// OpenXML color. Must be either or . + /// Since these types do not implement a common interface we use dynamic. + /// The dictionary containing parsed colors to optimize performance. + /// The color in ClosedXML format. + private static XLColor ConvertToClosedXMLColor(dynamic openXMLColor, IDictionary colorCache ) + { + XLColor retVal = null; + if (openXMLColor != null) + { + if (openXMLColor.Rgb != null) + { + String htmlColor = "#" + openXMLColor.Rgb.Value; + Drawing.Color thisColor; + if (colorCache?.ContainsKey(htmlColor) ?? false) + { + thisColor = colorCache[htmlColor]; + } + else + { + thisColor = ColorStringParser.ParseFromHtml(htmlColor); + colorCache?.Add(htmlColor, thisColor); + } + + retVal = XLColor.FromColor(thisColor); + } + else if (openXMLColor.Indexed != null && openXMLColor.Indexed <= 64) + retVal = XLColor.FromIndex((Int32)openXMLColor.Indexed.Value); + else if (openXMLColor.Theme != null) + { + retVal = openXMLColor.Tint != null + ? XLColor.FromTheme((XLThemeColor)openXMLColor.Theme.Value, openXMLColor.Tint.Value) + : XLColor.FromTheme((XLThemeColor)openXMLColor.Theme.Value); + } + } + return retVal ?? XLColor.NoColor; + } + + /// + /// Initialize properties of the existing instance of the color in OpenXML format basing on properties of the color + /// in ClosedXML format. + /// + /// OpenXML color. Must be either or . + /// Since these types do not implement a common interface we use dynamic. + /// Color in ClosedXML format. + /// Flag specifiying that the color should be saved in + /// differential format (affects the transparent color processing). + private static void FillFromClosedXMLColor(dynamic openXMLColor, XLColor xlColor, bool isDifferential) + { + if (openXMLColor == null) + throw new ArgumentNullException(nameof(openXMLColor)); + + if (xlColor == null) + throw new ArgumentNullException(nameof(xlColor)); + + switch (xlColor.ColorType) + { + case XLColorType.Color: + openXMLColor.Rgb = xlColor.Color.ToHex(); + break; + + case XLColorType.Indexed: + // 64 is 'transparent' and should be ignored for differential formats + if (!isDifferential || xlColor.Indexed != 64) + openXMLColor.Indexed = (UInt32)xlColor.Indexed; + break; + + case XLColorType.Theme: + openXMLColor.Theme = (UInt32)xlColor.ThemeColor; + + if (xlColor.ThemeTint != 0) + openXMLColor.Tint = xlColor.ThemeTint; + break; + } + } + + #endregion Private Methods } } diff --git a/ClosedXML/XLHelper.cs b/ClosedXML/XLHelper.cs index 90c74e2..49a4dd3 100644 --- a/ClosedXML/XLHelper.cs +++ b/ClosedXML/XLHelper.cs @@ -71,7 +71,7 @@ else if (i < 26 * 27) letter = letters[i / 26 - 1] + letters[i % 26]; else - letter = letters[i / 26 / 26 - 1] + letters[(i / 26 - 1) % 26] + letters[i % 26]; + letter = letters[(i - 26) / 26 / 26 - 1] + letters[(i / 26 - 1) % 26] + letters[i % 26]; allLetters[i] = letter; letterIndexes.Add(letter, i + 1); } diff --git a/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML_Examples/ClosedXML_Examples.csproj index a614307..d3d0880 100644 --- a/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -4,7 +4,7 @@ netcoreapp2.0;net40;net46 7.2 Exe - 0.93.0 + 0.94.0 $(NoWarn);NU1605 Debug;Release;Release.Signed diff --git a/ClosedXML_Examples/ImageHandling/ImageAnchors.cs b/ClosedXML_Examples/ImageHandling/ImageAnchors.cs index b20b42d..2285c15 100644 --- a/ClosedXML_Examples/ImageHandling/ImageAnchors.cs +++ b/ClosedXML_Examples/ImageHandling/ImageAnchors.cs @@ -28,7 +28,7 @@ fs.Position = 0; ws.AddPicture(fs, XLPictureFormat.Png, "Image11") - .MoveTo(ws.Cell(1, 1).Address); + .MoveTo(ws.Cell(1, 1)); #endregion OneCellAnchor @@ -38,7 +38,7 @@ fs.Position = 0; ws.AddPicture(fs, XLPictureFormat.Png, "Image20") - .MoveTo(ws.Cell(6, 5).Address, ws.Cell(9, 7).Address); + .MoveTo(ws.Cell(6, 5), ws.Cell(9, 7)); #endregion TwoCellAnchor } @@ -49,17 +49,17 @@ ws = wb.Worksheets.Add("Images3"); ws.AddPicture(fs, XLPictureFormat.Jpeg) - .MoveTo(ws.Cell(2, 2).Address, 20, 5, ws.Cell(5, 5).Address, 30, 10) - .MoveTo(ws.Cell(2, 2).Address, ws.Cell(5, 5).Address); + .MoveTo(ws.Cell(2, 2), 20, 5, ws.Cell(5, 5), 30, 10) + .MoveTo(ws.Cell(2, 2), ws.Cell(5, 5)); ws.AddPicture(fs, XLPictureFormat.Jpeg) - .MoveTo(ws.Cell(6, 2).Address, 2, 2, ws.Cell(9, 5).Address, 2, 2) - .MoveTo(ws.Cell(6, 2).Address, 20, 5, ws.Cell(9, 5).Address, 30, 10); + .MoveTo(ws.Cell(6, 2), 2, 2, ws.Cell(9, 5), 2, 2) + .MoveTo(ws.Cell(6, 2), 20, 5, ws.Cell(9, 5), 30, 10); ws.AddPicture(fs, XLPictureFormat.Jpeg) - .MoveTo(ws.Cell(10, 2).Address, 20, 5) + .MoveTo(ws.Cell(10, 2), 20, 5) .Scale(0.2, true) - .MoveTo(ws.Cell(10, 1).Address); + .MoveTo(ws.Cell(10, 1)); } using (Stream fs = Assembly.GetExecutingAssembly().GetManifestResourceStream("ClosedXML_Examples.Resources.SampleImage.jpg")) diff --git a/ClosedXML_Examples/ImageHandling/ImageFormats.cs b/ClosedXML_Examples/ImageHandling/ImageFormats.cs index 1f21297..eaad0f8 100644 --- a/ClosedXML_Examples/ImageHandling/ImageFormats.cs +++ b/ClosedXML_Examples/ImageHandling/ImageFormats.cs @@ -18,7 +18,7 @@ ws = wb.Worksheets.Add("Jpg"); ws.AddPicture(fs, XLPictureFormat.Jpeg, "JpegImage") - .MoveTo(ws.Cell(1, 1).Address); + .MoveTo(ws.Cell(1, 1)); #endregion Jpeg } @@ -29,7 +29,7 @@ ws = wb.Worksheets.Add("Png"); ws.AddPicture(fs, XLPictureFormat.Png, "PngImage") - .MoveTo(ws.Cell(1, 1).Address); + .MoveTo(ws.Cell(1, 1)); #endregion Png diff --git a/ClosedXML_Examples/StyleExamples.cs b/ClosedXML_Examples/StyleExamples.cs index 7617e76..869c9ab 100644 --- a/ClosedXML_Examples/StyleExamples.cs +++ b/ClosedXML_Examples/StyleExamples.cs @@ -61,6 +61,7 @@ new StyleBorder().Create(Path.Combine(path, "styleBorder.xlsx")); new StyleAlignment().Create(Path.Combine(path, "styleAlignment.xlsx")); new StyleNumberFormat().Create(Path.Combine(path, "styleNumberFormat.xlsx")); + new StyleIncludeQuotePrefix().Create(Path.Combine(path, "styleIncludeQuotePrefix.xlsx")); } // Private diff --git a/ClosedXML_Examples/Styles/StyleIncludeQuotePrefix.cs b/ClosedXML_Examples/Styles/StyleIncludeQuotePrefix.cs new file mode 100644 index 0000000..47129c6 --- /dev/null +++ b/ClosedXML_Examples/Styles/StyleIncludeQuotePrefix.cs @@ -0,0 +1,39 @@ +using ClosedXML.Excel; +using System; +using System.Linq; + +namespace ClosedXML_Examples.Styles +{ + public class StyleIncludeQuotePrefix : IXLExample + { + public void Create(String filePath) + { + var workbook = new XLWorkbook(); + var ws = workbook.Worksheets.Add("Style IncludeQuotePrefix"); + + var data = Enumerable.Range(1, 20) + .Select(i => + new + { + IntegerIndex = i, + StringIndex = i.ToString(), + PaddedString1000 = (i * 1000).ToString().PadLeft(8, '0'), + PrependedString1000 = "Str" + (i * 1000).ToString().PadLeft(8, '0') + }); + + ws.FirstCell().InsertData(data); + + // Columns B to D will be of type text + // but column B will not have the leading quotation mark + ws.Column("B").Style.IncludeQuotePrefix = false; + + // Columns C and D will have the leading quotation mark + ws.Column("C").Style.IncludeQuotePrefix = true; + ws.Column("D").Style.SetIncludeQuotePrefix(); + + ws.Columns().AdjustToContents(); + + workbook.SaveAs(filePath); + } + } +} diff --git a/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj b/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj index ebdbf14..1074eff 100644 --- a/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj +++ b/ClosedXML_Sandbox/ClosedXML_Sandbox.csproj @@ -4,7 +4,7 @@ Exe netcoreapp2.0;net40;net46 7.2 - 0.93.0 + 0.94.0 $(NoWarn);NU1605 Debug;Release;Release.Signed diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index a912f8a..573e6bd 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -3,7 +3,7 @@ netcoreapp2.0;net40;net46 7.2 - 0.93.0 + 0.94.0 false $(NoWarn);NU1605 Debug;Release;Release.Signed @@ -34,7 +34,7 @@ - + diff --git a/ClosedXML_Tests/Examples/StylesTests.cs b/ClosedXML_Tests/Examples/StylesTests.cs index c400a81..ac6b17e 100644 --- a/ClosedXML_Tests/Examples/StylesTests.cs +++ b/ClosedXML_Tests/Examples/StylesTests.cs @@ -49,6 +49,12 @@ } [Test] + public void StyleIncludeQuotePrefix() + { + TestHelper.RunTestExample(@"Styles\StyleIncludeQuotePrefix.xlsx"); + } + + [Test] public void StyleRowsColumns() { TestHelper.RunTestExample(@"Styles\StyleRowsColumns.xlsx"); diff --git a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs index 20d17f0..b6c01d5 100644 --- a/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs +++ b/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs @@ -33,6 +33,7 @@ table.DataRange.FirstCell().InsertData(listOfArr); Assert.AreEqual("A1:A5", table.AutoFilter.Range.RangeAddress.ToStringRelative()); + Assert.AreEqual(5, table.AutoFilter.VisibleRows.Count()); } } } @@ -49,7 +50,7 @@ .CellBelow().SetValue("Carlos") .CellBelow().SetValue("Dominic"); ws.RangeUsed().SetAutoFilter().Sort(); - Assert.AreEqual(ws.Cell(4, 3).GetString(), "Carlos"); + Assert.AreEqual("Carlos", ws.Cell(4, 3).GetString()); } } } @@ -156,5 +157,60 @@ } } } + + [Test] + public void AutoFilterVisibleRows() + { + using (var wb = new XLWorkbook()) + { + using (var ws = wb.Worksheets.Add("Sheet1")) + { + ws.Cell(3, 3).SetValue("Names") + .CellBelow().SetValue("Manuel") + .CellBelow().SetValue("Carlos") + .CellBelow().SetValue("Dominic"); + + var autoFilter = ws.RangeUsed() + .SetAutoFilter(); + + autoFilter.Column(1).AddFilter("Carlos"); + + Assert.AreEqual("Carlos", ws.Cell(5, 3).GetString()); + Assert.AreEqual(2, autoFilter.VisibleRows.Count()); + Assert.AreEqual(3, autoFilter.VisibleRows.First().WorksheetRow().RowNumber()); + Assert.AreEqual(5, autoFilter.VisibleRows.Last().WorksheetRow().RowNumber()); + } + } + } + + [Test] + public void ReapplyAutoFilter() + { + using (var wb = new XLWorkbook()) + { + using (var ws = wb.Worksheets.Add("Sheet1")) + { + ws.Cell(3, 3).SetValue("Names") + .CellBelow().SetValue("Manuel") + .CellBelow().SetValue("Carlos") + .CellBelow().SetValue("Dominic") + .CellBelow().SetValue("Jose"); + + var autoFilter = ws.RangeUsed() + .SetAutoFilter(); + + autoFilter.Column(1).AddFilter("Carlos"); + + Assert.AreEqual(3, autoFilter.HiddenRows.Count()); + + // Unhide the rows so that the table is out of sync with the filter + autoFilter.HiddenRows.ForEach(r => r.WorksheetRow().Unhide()); + Assert.False(autoFilter.HiddenRows.Any()); + + autoFilter.Reapply(); + Assert.AreEqual(3, autoFilter.HiddenRows.Count()); + } + } + } } } diff --git a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs index 3e9cf66..ed70e5e 100644 --- a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs +++ b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs @@ -109,6 +109,16 @@ } [Test] + public void InsertData_with_Guids() + { + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); + ws.FirstCell().InsertData(Enumerable.Range(1, 20).Select(i => new { Guid = Guid.NewGuid() })); + + Assert.AreEqual(XLDataType.Text, ws.FirstCell().DataType); + Assert.AreEqual(Guid.NewGuid().ToString().Length, ws.FirstCell().GetString().Length); + } + + [Test] public void IsEmpty1() { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); @@ -340,6 +350,40 @@ } [Test] + public void SetCellValueToGuid() + { + var ws = new XLWorkbook().AddWorksheet("Sheet1"); + var guid = Guid.NewGuid(); + ws.FirstCell().Value = guid; + Assert.AreEqual(XLDataType.Text, ws.FirstCell().DataType); + Assert.AreEqual(guid.ToString(), ws.FirstCell().Value); + Assert.AreEqual(guid.ToString(), ws.FirstCell().GetString()); + + guid = Guid.NewGuid(); + ws.FirstCell().SetValue(guid); + Assert.AreEqual(XLDataType.Text, ws.FirstCell().DataType); + Assert.AreEqual(guid.ToString(), ws.FirstCell().Value); + Assert.AreEqual(guid.ToString(), ws.FirstCell().GetString()); + } + + [Test] + public void SetCellValueToEnum() + { + var ws = new XLWorkbook().AddWorksheet("Sheet1"); + var dataType = XLDataType.Number; + ws.FirstCell().Value = dataType; + Assert.AreEqual(XLDataType.Text, ws.FirstCell().DataType); + Assert.AreEqual(dataType.ToString(), ws.FirstCell().Value); + Assert.AreEqual(dataType.ToString(), ws.FirstCell().GetString()); + + dataType = XLDataType.TimeSpan; + ws.FirstCell().SetValue(dataType); + Assert.AreEqual(XLDataType.Text, ws.FirstCell().DataType); + Assert.AreEqual(dataType.ToString(), ws.FirstCell().Value); + Assert.AreEqual(dataType.ToString(), ws.FirstCell().GetString()); + } + + [Test] public void ValueSetToEmptyString() { string expected = String.Empty; @@ -792,5 +836,23 @@ var _ = cell.Value; }); } + + [Test] + public void TryGetValueFormulaEvaluation() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + var A1 = ws.Cell("A1"); + var A2 = ws.Cell("A2"); + var A3 = ws.Cell("A3"); + A1.FormulaA1 = "A2 + 1"; + A2.FormulaA1 = "A1 + 1"; + + Assert.IsFalse(A1.TryGetValue(out String _)); + Assert.IsFalse(A2.TryGetValue(out String _)); + Assert.IsTrue(A3.TryGetValue(out String _)); + } + } } } diff --git a/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs b/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs index c071eb8..931b263 100644 --- a/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs +++ b/ClosedXML_Tests/Excel/ImageHandling/PictureTests.cs @@ -197,10 +197,10 @@ public void XLMarkerTests() { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); - XLMarker firstMarker = new XLMarker(ws.Cell(1, 10).Address, new Point(100, 0)); + XLMarker firstMarker = new XLMarker(ws.Cell(1, 10), new Point(100, 0)); - Assert.AreEqual("J", firstMarker.Address.ColumnLetter); - Assert.AreEqual(1, firstMarker.Address.RowNumber); + Assert.AreEqual(10, firstMarker.ColumnNumber); + Assert.AreEqual(1, firstMarker.RowNumber); Assert.AreEqual(100, firstMarker.Offset.X); Assert.AreEqual(0, firstMarker.Offset.Y); } @@ -352,7 +352,7 @@ Assert.AreEqual(original.Format, copy.Format); Assert.AreEqual(original.Height, copy.Height); Assert.AreEqual(original.Placement, copy.Placement); - Assert.AreEqual(original.TopLeftCellAddress.ToString(), copy.TopLeftCellAddress.ToString()); + Assert.AreEqual(original.TopLeftCell.ToString(), copy.TopLeftCell.ToString()); Assert.AreEqual(original.Width, copy.Width); Assert.AreEqual(original.ImageStream.ToArray(), copy.ImageStream.ToArray(), "Image streams differ"); @@ -373,7 +373,6 @@ original = (ws1 as XLWorksheet).AddPicture(stream, "Picture 1", 2) .WithPlacement(XLPicturePlacement.FreeFloating) .MoveTo(220, 155) as XLPicture; - } var ws2 = wb.Worksheets.Add("Sheet2"); @@ -390,11 +389,28 @@ Assert.AreEqual(original.Name, copy.Name); Assert.AreEqual(original.Placement, copy.Placement); Assert.AreEqual(original.Top, copy.Top); - Assert.AreEqual(original.TopLeftCellAddress.ToString(), copy.TopLeftCellAddress.ToString()); + Assert.AreEqual(original.TopLeftCell.ToString(), copy.TopLeftCell.ToString()); Assert.AreEqual(original.Width, copy.Width); Assert.AreEqual(original.ImageStream.ToArray(), copy.ImageStream.ToArray(), "Image streams differ"); Assert.AreNotEqual(original.Id, copy.Id); } + + [Test] + public void PictureShiftsWhenInsertingRows() + { + using (var wb = new XLWorkbook()) + using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream("ClosedXML_Tests.Resource.Images.ImageHandling.png")) + { + var ws = wb.Worksheets.Add("ImageShift"); + var picture = ws.AddPicture(stream, XLPictureFormat.Png, "PngImage") + .MoveTo(ws.Cell(5, 2)) + .WithPlacement(XLPicturePlacement.Move); + + ws.Row(2).InsertRowsBelow(20); + + Assert.AreEqual(25, picture.TopLeftCell.Address.RowNumber); + } + } } } diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index b7694d6..89f1714 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -36,7 +36,8 @@ @"Misc\InvalidPrintArea.xlsx", @"Misc\Date1904System.xlsx", @"Misc\LoadImageWithoutTransform2D.xlsx", - @"Misc\PivotTableWithTableSource.xlsx" + @"Misc\PivotTableWithTableSource.xlsx", + @"Misc\TemplateWithTableSourcePivotTables.xlsx" }; foreach (var file in files) diff --git a/ClosedXML_Tests/Excel/Misc/XlHelperTests.cs b/ClosedXML_Tests/Excel/Misc/XlHelperTests.cs index 20853d6..7ef97ff 100644 --- a/ClosedXML_Tests/Excel/Misc/XlHelperTests.cs +++ b/ClosedXML_Tests/Excel/Misc/XlHelperTests.cs @@ -1,5 +1,7 @@ using ClosedXML.Excel; using NUnit.Framework; +using System; +using System.Collections.Generic; namespace ClosedXML_Tests.Excel { @@ -71,6 +73,7 @@ CheckColumnNumber(52); CheckColumnNumber(53); CheckColumnNumber(1000); + CheckColumnNumber(1353); } [Test] @@ -91,5 +94,81 @@ Assert.IsTrue(XLHelper.IsValidA1Address(XLHelper.MaxColumnLetter + "1")); Assert.IsTrue(XLHelper.IsValidA1Address(XLHelper.MaxColumnLetter + XLHelper.MaxRowNumber)); } + + [Test] + public void TestColumnLetterLookup() + { + var columnLetters = new List(); + for (int c = 1; c <= XLHelper.MaxColumnNumber; c++) + { + var columnLetter = NaiveGetColumnLetterFromNumber(c); + columnLetters.Add(columnLetter); + + Assert.AreEqual(columnLetter, XLHelper.GetColumnLetterFromNumber(c)); + } + + foreach (var cl in columnLetters) + { + var columnNumber = NaiveGetColumnNumberFromLetter(cl); + Assert.AreEqual(columnNumber, XLHelper.GetColumnNumberFromLetter(cl)); + } + } + + #region Old XLHelper methods + + private static readonly string[] letters = new[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" }; + + /// + /// These used to be the methods in XLHelper, but were later changed + /// We now use them as a check against the new methods + /// Gets the column number of a given column letter. + /// + /// The column letter to translate into a column number. + private static int NaiveGetColumnNumberFromLetter(string columnLetter) + { + if (string.IsNullOrEmpty(columnLetter)) throw new ArgumentNullException("columnLetter"); + + int retVal; + columnLetter = columnLetter.ToUpper(); + + //Extra check because we allow users to pass row col positions in as strings + if (columnLetter[0] <= '9') + { + retVal = Int32.Parse(columnLetter, XLHelper.NumberStyle, XLHelper.ParseCulture); + return retVal; + } + + int sum = 0; + + for (int i = 0; i < columnLetter.Length; i++) + { + sum *= 26; + sum += (columnLetter[i] - 'A' + 1); + } + + return sum; + } + + /// + /// Gets the column letter of a given column number. + /// + /// The column number to translate into a column letter. + /// if set to true the column letter will be restricted to the allowed range. + /// + private static string NaiveGetColumnLetterFromNumber(int columnNumber, bool trimToAllowed = false) + { + if (trimToAllowed) columnNumber = XLHelper.TrimColumnNumber(columnNumber); + + columnNumber--; // Adjust for start on column 1 + if (columnNumber <= 25) + { + return letters[columnNumber]; + } + var firstPart = (columnNumber) / 26; + var remainder = ((columnNumber) % 26) + 1; + return NaiveGetColumnLetterFromNumber(firstPart) + NaiveGetColumnLetterFromNumber(remainder); + } + + #endregion Old XLHelper methods } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs index eddd1a3..51a89bb 100644 --- a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs +++ b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs @@ -3,6 +3,7 @@ using System; using System.Collections.Generic; using System.IO; +using System.Linq; namespace ClosedXML_Tests { @@ -276,6 +277,239 @@ } } + [Test] + public void SourceSheetWithWhitespace() + { + using (var ms = new MemoryStream()) + { + TestHelper.CreateAndCompare(() => + { + // Based on .\ClosedXML\ClosedXML_Examples\PivotTables\PivotTables.cs + // But with empty column for Month + var pastries = new List + { + new Pastry("Croissant", 101, 150, 60.2, "", new DateTime(2016, 04, 21)), + new Pastry("Croissant", 101, 250, 50.42, "", new DateTime(2016, 05, 03)), + new Pastry("Croissant", 101, 134, 22.12, "", new DateTime(2016, 06, 24)), + new Pastry("Doughnut", 102, 250, 89.99, "", new DateTime(2017, 04, 23)), + new Pastry("Doughnut", 102, 225, 70, "", new DateTime(2016, 05, 24)), + new Pastry("Doughnut", 102, 210, 75.33, "", new DateTime(2016, 06, 02)), + new Pastry("Bearclaw", 103, 134, 10.24, "", new DateTime(2016, 04, 27)), + new Pastry("Bearclaw", 103, 184, 33.33, "", new DateTime(2016, 05, 20)), + new Pastry("Bearclaw", 103, 124, 25, "", new DateTime(2017, 06, 05)), + new Pastry("Danish", 104, 394, -20.24, "", null), + new Pastry("Danish", 104, 190, 60, "", new DateTime(2017, 05, 08)), + new Pastry("Danish", 104, 221, 24.76, "", new DateTime(2016, 06, 21)), + + // Deliberately add different casings of same string to ensure pivot table doesn't duplicate it. + new Pastry("Scone", 105, 135, 0, "", new DateTime(2017, 04, 22)), + new Pastry("SconE", 105, 122, 5.19, "", new DateTime(2017, 05, 03)), + new Pastry("SCONE", 105, 243, 44.2, "", new DateTime(2017, 06, 14)), + + // For ContainsBlank and integer rows/columns test + new Pastry("Scone", null, 255, 18.4, "", null), + }; + + var wb = new XLWorkbook(); + + var sheet = wb.Worksheets.Add("Pastry Sales Data"); + // Insert our list of pastry data into the "PastrySalesData" sheet at cell 1,1 + var table = sheet.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true); + sheet.Cell("F11").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + sheet.Columns().AdjustToContents(); + + IXLWorksheet ptSheet; + IXLPivotTable pt; + + // Add a new sheet for our pivot table + ptSheet = wb.Worksheets.Add("pvt"); + + // Create the pivot table, using the data from the "PastrySalesData" table + pt = ptSheet.PivotTables.Add("pvt", ptSheet.Cell(1, 1), table.AsRange()); + pt.ColumnLabels.Add("Name"); + pt.RowLabels.Add("Month"); + + // The values in our table will come from the "NumberOfOrders" field + // The default calculation setting is a total of each row/column + pt.Values.Add("NumberOfOrders", "NumberOfOrdersPercentageOfBearclaw") + .ShowAsPercentageFrom("Name").And("Bearclaw") + .NumberFormat.Format = "0%"; + + ptSheet.Columns().AdjustToContents(); + + return wb; + }, @"Other\PivotTableReferenceFiles\SourceSheetWithWhitespace\outputfile.xlsx"); + } + } + + [Test] + public void PivotTableWithNoneTheme() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Other\PivotTableReferenceFiles\PivotTableWithNoneTheme\inputfile.xlsx"))) + using (var ms = new MemoryStream()) + { + TestHelper.CreateAndCompare(() => + { + var wb = new XLWorkbook(stream); + wb.SaveAs(ms); + return wb; + }, @"Other\PivotTableReferenceFiles\PivotTableWithNoneTheme\outputfile.xlsx"); + } + } + + [Test] + public void MaintainPivotTablePageFieldOrder() + { + var pastries = new List + { + new Pastry("Croissant", 101, 150, 60.2, "", new DateTime(2016, 04, 21)), + new Pastry("Croissant", 101, 250, 50.42, "", new DateTime(2016, 05, 03)), + new Pastry("Croissant", 101, 134, 22.12, "", new DateTime(2016, 06, 24)), + new Pastry("Doughnut", 102, 250, 89.99, "", new DateTime(2017, 04, 23)), + new Pastry("Doughnut", 102, 225, 70, "", new DateTime(2016, 05, 24)), + new Pastry("Doughnut", 102, 210, 75.33, "", new DateTime(2016, 06, 02)), + new Pastry("Bearclaw", 103, 134, 10.24, "", new DateTime(2016, 04, 27)), + new Pastry("Bearclaw", 103, 184, 33.33, "", new DateTime(2016, 05, 20)), + new Pastry("Bearclaw", 103, 124, 25, "", new DateTime(2017, 06, 05)), + new Pastry("Danish", 104, 394, -20.24, "", null), + new Pastry("Danish", 104, 190, 60, "", new DateTime(2017, 05, 08)), + new Pastry("Danish", 104, 221, 24.76, "", new DateTime(2016, 06, 21)), + + // Deliberately add different casings of same string to ensure pivot table doesn't duplicate it. + new Pastry("Scone", 105, 135, 0, "", new DateTime(2017, 04, 22)), + new Pastry("SconE", 105, 122, 5.19, "", new DateTime(2017, 05, 03)), + new Pastry("SCONE", 105, 243, 44.2, "", new DateTime(2017, 06, 14)), + + // For ContainsBlank and integer rows/columns test + new Pastry("Scone", null, 255, 18.4, "", null), + }; + + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("PastrySalesData"); + // Insert our list of pastry data into the "PastrySalesData" sheet at cell 1,1 + var table = sheet.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true); + sheet.Cell("F11").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + sheet.Columns().AdjustToContents(); + + IXLWorksheet ptSheet; + IXLPivotTable pt; + + // Add a new sheet for our pivot table + ptSheet = wb.Worksheets.Add("pvt"); + + // Create the pivot table, using the data from the "PastrySalesData" table + pt = ptSheet.PivotTables.Add("PastryPivot", ptSheet.Cell(1, 1), table); + + pt.ReportFilters.Add("Month"); + pt.ReportFilters.Add("Name"); + + pt.RowLabels.Add("BakeDate"); + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + + wb.SaveAs(ms); + } + + ms.Seek(0, SeekOrigin.Begin); + + using (var wb = new XLWorkbook(ms)) + { + var pageFields = wb.Worksheets.SelectMany(ws => ws.PivotTables) + .First() + .ReportFilters + .ToArray(); + + Assert.AreEqual("Month", pageFields[0].SourceName); + Assert.AreEqual("Name", pageFields[1].SourceName); + } + } + } + + [Test] + public void MaintainPivotTableIntegrityOnMultipleSaves() + { + var pastries = new List + { + new Pastry("Croissant", 101, 150, 60.2, "", new DateTime(2016, 04, 21)), + new Pastry("Croissant", 101, 250, 50.42, "", new DateTime(2016, 05, 03)), + new Pastry("Croissant", 101, 134, 22.12, "", new DateTime(2016, 06, 24)), + new Pastry("Doughnut", 102, 250, 89.99, "", new DateTime(2017, 04, 23)), + new Pastry("Doughnut", 102, 225, 70, "", new DateTime(2016, 05, 24)), + new Pastry("Doughnut", 102, 210, 75.33, "", new DateTime(2016, 06, 02)), + new Pastry("Bearclaw", 103, 134, 10.24, "", new DateTime(2016, 04, 27)), + new Pastry("Bearclaw", 103, 184, 33.33, "", new DateTime(2016, 05, 20)), + new Pastry("Bearclaw", 103, 124, 25, "", new DateTime(2017, 06, 05)), + new Pastry("Danish", 104, 394, -20.24, "", null), + new Pastry("Danish", 104, 190, 60, "", new DateTime(2017, 05, 08)), + new Pastry("Danish", 104, 221, 24.76, "", new DateTime(2016, 06, 21)), + + // Deliberately add different casings of same string to ensure pivot table doesn't duplicate it. + new Pastry("Scone", 105, 135, 0, "", new DateTime(2017, 04, 22)), + new Pastry("SconE", 105, 122, 5.19, "", new DateTime(2017, 05, 03)), + new Pastry("SCONE", 105, 243, 44.2, "", new DateTime(2017, 06, 14)), + + // For ContainsBlank and integer rows/columns test + new Pastry("Scone", null, 255, 18.4, "", null), + }; + + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("PastrySalesData"); + var table = ws.FirstCell().InsertTable(pastries, "PastrySalesData", true); + + var pvtSheet = wb.Worksheets.Add("pvt"); + var pvt = table.CreatePivotTable(pvtSheet.FirstCell(), "PastryPvt"); + + pvt.ColumnLabels.Add("Month"); + pvt.RowLabels.Add("Name"); + pvt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + + //Deliberately try to save twice + wb.SaveAs(ms); + wb.SaveAs(ms); + } + + ms.Seek(0, SeekOrigin.Begin); + + using (var wb = new XLWorkbook(ms)) + { + Assert.AreEqual(1, wb.Worksheets.SelectMany(ws => ws.PivotTables).Count()); + } + } + } + + [Test] + public void ClearPivotTableTenderedTange() + { + // https://github.com/ClosedXML/ClosedXML/pull/856 + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Other\PivotTableReferenceFiles\ClearPivotTableRenderedRangeWhenLoading\inputfile.xlsx"))) + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheet("Sheet1"); + Assert.IsTrue(ws.Cell("B1").IsEmpty()); + Assert.IsTrue(ws.Cell("C2").IsEmpty()); + Assert.IsTrue(ws.Cell("D5").IsEmpty()); + wb.SaveAs(ms); + } + + ms.Seek(0, SeekOrigin.Begin); + + using (var wb = new XLWorkbook(ms)) + { + var ws = wb.Worksheet("Sheet1"); + Assert.IsTrue(ws.Cell("B1").IsEmpty()); + Assert.IsTrue(ws.Cell("C2").IsEmpty()); + Assert.IsTrue(ws.Cell("D5").IsEmpty()); + } + } + } + private static void SetFieldOptions(IXLPivotField field, bool withDefaults) { field.SubtotalsAtTop = !withDefaults; diff --git a/ClosedXML_Tests/Excel/Ranges/UsedAndUnusedCellsTests.cs b/ClosedXML_Tests/Excel/Ranges/UsedAndUnusedCellsTests.cs index abced23..990a249 100644 --- a/ClosedXML_Tests/Excel/Ranges/UsedAndUnusedCellsTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/UsedAndUnusedCellsTests.cs @@ -145,5 +145,71 @@ Assert.AreEqual("A1:E2", used); } } + + [TestCase(true, "A1:D2", "A1")] + [TestCase(true, "A2:D2", "A2")] + [TestCase(true, "A1:D2", "A1", "B2")] + [TestCase(true, "B2:D3", "C3")] + [TestCase(true, "B2:F4", "F4")] + [TestCase(false, "A1:D2", "A1")] + [TestCase(false, "A2:D2", "A2")] + [TestCase(false, "A1:D2", "A1", "B2")] + [TestCase(false, "B2:D3", "C3")] + [TestCase(false, "B2:F4", "F4")] + public void RangeUsedIncludesMergedCells(bool includeFormatting, string expectedRange, + params string[] cellsWithValues) + { + using (XLWorkbook wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + foreach (var cellAddress in cellsWithValues) + { + ws.Cell(cellAddress).Value = "Not empty"; + } + ws.Range("B2:D2").Merge(); + + var actual = ws.RangeUsed(includeFormatting).RangeAddress; + + Assert.AreEqual(expectedRange, actual.ToString()); + } + } + + [Test] + public void LastCellUsedPredicateConsidersMergedRanges() + { + using (XLWorkbook wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell("A1").Style.Fill.BackgroundColor = XLColor.Red; + ws.Cell("A2").Style.Fill.BackgroundColor = XLColor.Yellow; + ws.Cell("A3").Style.Fill.BackgroundColor = XLColor.Green; + ws.Range("A1:C1").Merge(); + ws.Range("A2:C2").Merge(); + ws.Range("A3:C3").Merge(); + + var actual = ws.LastCellUsed(true, c => c.Style.Fill.BackgroundColor == XLColor.Yellow); + + Assert.AreEqual("C2", actual.Address.ToString()); + } + } + + [Test] + public void FirstCellUsedPredicateConsidersMergedRanges() + { + using (XLWorkbook wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell("A1").Style.Fill.BackgroundColor = XLColor.Red; + ws.Cell("A2").Style.Fill.BackgroundColor = XLColor.Yellow; + ws.Cell("A3").Style.Fill.BackgroundColor = XLColor.Green; + ws.Range("A1:C1").Merge(); + ws.Range("A2:C2").Merge(); + ws.Range("A3:C3").Merge(); + + var actual = ws.FirstCellUsed(true, c => c.Style.Fill.BackgroundColor == XLColor.Yellow); + + Assert.AreEqual("A2", actual.Address.ToString()); + } + } } } diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs index 10b13b1..a5aa34e 100644 --- a/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs @@ -180,6 +180,31 @@ Assert.AreEqual("#REF!#REF!", address.ToStringRelative()); Assert.AreEqual("#REF!#REF!", address.ToStringRelative(true)); } + + [Test] + public void FullSpanAddressCannotChange() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + var wsRange = ws.AsRange(); + var row = ws.FirstRow().RowBelow(4).AsRange(); + var column = ws.FirstColumn().ColumnRight(4).AsRange(); + + Assert.AreEqual("A1:XFD1048576", wsRange.RangeAddress.ToString()); + Assert.AreEqual("A5:XFD5", row.RangeAddress.ToString()); + Assert.AreEqual("E1:E1048576", column.RangeAddress.ToString()); + + ws.Columns("Y:Z").Delete(); + ws.Rows("9:10").Delete(); + + Assert.AreEqual("A1:XFD1048576", wsRange.RangeAddress.ToString()); + Assert.AreEqual("A5:XFD5", row.RangeAddress.ToString()); + Assert.AreEqual("E1:E1048576", column.RangeAddress.ToString()); + } + } + #region Private Methods private IXLRangeAddress ProduceInvalidAddress() diff --git a/ClosedXML_Tests/Excel/Saving/SavingTests.cs b/ClosedXML_Tests/Excel/Saving/SavingTests.cs index bd7b27e..1cbf38d 100644 --- a/ClosedXML_Tests/Excel/Saving/SavingTests.cs +++ b/ClosedXML_Tests/Excel/Saving/SavingTests.cs @@ -490,5 +490,23 @@ } } } + + [Test] + public void PreserveAlignmentOnSaving() + { + using (var input = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\HorizontalAlignment.xlsx"))) + using (var output = new MemoryStream()) + { + using (var wb = new XLWorkbook(input)) + { + wb.SaveAs(output); + } + + using (var wb = new XLWorkbook(output)) + { + Assert.AreEqual(XLAlignmentHorizontalValues.Center, wb.Worksheets.First().Cell("B1").Style.Alignment.Horizontal); + } + } + } } } diff --git a/ClosedXML_Tests/Excel/Styles/ColorTests.cs b/ClosedXML_Tests/Excel/Styles/ColorTests.cs index 72cbb27..d631194 100644 --- a/ClosedXML_Tests/Excel/Styles/ColorTests.cs +++ b/ClosedXML_Tests/Excel/Styles/ColorTests.cs @@ -1,6 +1,9 @@ -using System.Drawing; using ClosedXML.Excel; +using ClosedXML.Utils; +using DocumentFormat.OpenXml.Spreadsheet; using NUnit.Framework; +using Color = System.Drawing.Color; +using X14 = DocumentFormat.OpenXml.Office2010.Excel; namespace ClosedXML_Tests.Excel { @@ -38,5 +41,137 @@ Assert.AreEqual(64, color.Indexed); Assert.AreEqual(Color.Transparent, color.Color); } + + [Test] + public void CanConvertXLColorToColorType() + { + var xlColor1 = XLColor.Red; + var xlColor2 = XLColor.FromIndex(20); + var xlColor3 = XLColor.FromTheme(XLThemeColor.Accent1); + var xlColor4 = XLColor.FromTheme(XLThemeColor.Accent2, 0.4); + + var color1 = new ForegroundColor().FromClosedXMLColor(xlColor1); + var color2 = new ForegroundColor().FromClosedXMLColor(xlColor2); + var color3 = new BackgroundColor().FromClosedXMLColor(xlColor3); + var color4 = new BackgroundColor().FromClosedXMLColor(xlColor4); + + Assert.AreEqual("FFFF0000", color1.Rgb.Value); + Assert.IsNull(color1.Indexed); + Assert.IsNull(color1.Theme); + Assert.IsNull(color1.Tint); + + Assert.IsNull(color2.Rgb); + Assert.AreEqual(20, color2.Indexed.Value); + Assert.IsNull(color2.Theme); + Assert.IsNull(color2.Tint); + + Assert.IsNull(color3.Rgb); + Assert.IsNull(color3.Indexed); + Assert.AreEqual(4, color3.Theme.Value); + Assert.IsNull(color3.Tint); + + Assert.IsNull(color4.Rgb); + Assert.IsNull(color4.Indexed); + Assert.AreEqual(5, color4.Theme.Value); + Assert.AreEqual(0.4, color4.Tint.Value); + } + + [Test] + public void CanConvertXlColorToX14ColorType() + { + var xlColor1 = XLColor.Red; + var xlColor2 = XLColor.FromIndex(20); + var xlColor3 = XLColor.FromTheme(XLThemeColor.Accent1); + var xlColor4 = XLColor.FromTheme(XLThemeColor.Accent2, 0.4); + + var color1 = new X14.AxisColor().FromClosedXMLColor(xlColor1); + var color2 = new X14.BorderColor().FromClosedXMLColor(xlColor2); + var color3 = new X14.FillColor().FromClosedXMLColor(xlColor3); + var color4 = new X14.HighMarkerColor().FromClosedXMLColor(xlColor4); + + Assert.AreEqual("FFFF0000", color1.Rgb.Value); + Assert.IsNull(color1.Indexed); + Assert.IsNull(color1.Theme); + Assert.IsNull(color1.Tint); + + Assert.IsNull(color2.Rgb); + Assert.AreEqual(20, color2.Indexed.Value); + Assert.IsNull(color2.Theme); + Assert.IsNull(color2.Tint); + + Assert.IsNull(color3.Rgb); + Assert.IsNull(color3.Indexed); + Assert.AreEqual(4, color3.Theme.Value); + Assert.IsNull(color3.Tint); + + Assert.IsNull(color4.Rgb); + Assert.IsNull(color4.Indexed); + Assert.AreEqual(5, color4.Theme.Value); + Assert.AreEqual(0.4, color4.Tint.Value); + } + + [Test] + public void CanConvertColorTypeToXlColor() + { + var color1 = new ForegroundColor { Rgb = new DocumentFormat.OpenXml.HexBinaryValue("FFFF0000") }; + var color2 = new ForegroundColor { Indexed = new DocumentFormat.OpenXml.UInt32Value((uint)20) }; + var color3 = new BackgroundColor { Theme = new DocumentFormat.OpenXml.UInt32Value((uint)4) }; + var color4 = new BackgroundColor + { + Theme = new DocumentFormat.OpenXml.UInt32Value((uint)4), + Tint = new DocumentFormat.OpenXml.DoubleValue(0.4) + }; + + var xlColor1 = color1.ToClosedXMLColor(); + var xlColor2 = color2.ToClosedXMLColor(); + var xlColor3 = color3.ToClosedXMLColor(); + var xlColor4 = color4.ToClosedXMLColor(); + + Assert.AreEqual(XLColorType.Color, xlColor1.ColorType); + Assert.AreEqual(XLColor.Red.Color, xlColor1.Color); + + Assert.AreEqual(XLColorType.Indexed, xlColor2.ColorType); + Assert.AreEqual(20, xlColor2.Indexed); + + Assert.AreEqual(XLColorType.Theme, xlColor3.ColorType); + Assert.AreEqual(XLThemeColor.Accent1, xlColor3.ThemeColor); + Assert.AreEqual(0, xlColor3.ThemeTint, XLHelper.Epsilon); + + Assert.AreEqual(XLColorType.Theme, xlColor4.ColorType); + Assert.AreEqual(XLThemeColor.Accent1, xlColor4.ThemeColor); + Assert.AreEqual(0.4, xlColor4.ThemeTint, XLHelper.Epsilon); + } + + [Test] + public void CanConvertX14ColorTypeToXlColor() + { + var color1 = new X14.AxisColor { Rgb = new DocumentFormat.OpenXml.HexBinaryValue("FFFF0000") }; + var color2 = new X14.BorderColor { Indexed = new DocumentFormat.OpenXml.UInt32Value((uint)20) }; + var color3 = new X14.FillColor { Theme = new DocumentFormat.OpenXml.UInt32Value((uint)4) }; + var color4 = new X14.HighMarkerColor + { + Theme = new DocumentFormat.OpenXml.UInt32Value((uint)4), + Tint = new DocumentFormat.OpenXml.DoubleValue(0.4) + }; + + var xlColor1 = color1.ToClosedXMLColor(); + var xlColor2 = color2.ToClosedXMLColor(); + var xlColor3 = color3.ToClosedXMLColor(); + var xlColor4 = color4.ToClosedXMLColor(); + + Assert.AreEqual(XLColorType.Color, xlColor1.ColorType); + Assert.AreEqual(XLColor.Red.Color, xlColor1.Color); + + Assert.AreEqual(XLColorType.Indexed, xlColor2.ColorType); + Assert.AreEqual(20, xlColor2.Indexed); + + Assert.AreEqual(XLColorType.Theme, xlColor3.ColorType); + Assert.AreEqual(XLThemeColor.Accent1, xlColor3.ThemeColor); + Assert.AreEqual(0, xlColor3.ThemeTint, XLHelper.Epsilon); + + Assert.AreEqual(XLColorType.Theme, xlColor4.ColorType); + Assert.AreEqual(XLThemeColor.Accent1, xlColor4.ThemeColor); + Assert.AreEqual(0.4, xlColor4.ThemeTint, XLHelper.Epsilon); + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Tables/AddingAndReplacingTableDataTests.cs b/ClosedXML_Tests/Excel/Tables/AddingAndReplacingTableDataTests.cs new file mode 100644 index 0000000..ccae4ad --- /dev/null +++ b/ClosedXML_Tests/Excel/Tables/AddingAndReplacingTableDataTests.cs @@ -0,0 +1,675 @@ +using ClosedXML.Attributes; +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Collections; +using System.Collections.Generic; +using System.IO; +using System.Linq; + +namespace ClosedXML_Tests.Excel.Tables +{ + [TestFixture] + public class AppendingAndReplacingTableDataTests + { + public class TestObjectWithoutAttributes + { + public String Column1 { get; set; } + public String Column2 { get; set; } + } + + public class Person + { + public int Age { get; set; } + + [XLColumn(Header = "Last name", Order = 2)] + public String LastName { get; set; } + + [XLColumn(Header = "First name", Order = 1)] + public String FirstName { get; set; } + + [XLColumn(Header = "Full name", Order = 0)] + public String FullName { get => string.Concat(FirstName, " ", LastName); } + + [XLColumn(Order = 3)] + public DateTime DateOfBirth { get; set; } + + [XLColumn(Header = "Is active", Order = 4)] + public bool IsActive; + } + + private XLWorkbook PrepareWorkbook() + { + var wb = new XLWorkbook(); + var ws = wb.AddWorksheet("Tables"); + + var data = new[] + { + new Person{FirstName = "Francois", LastName = "Botha", Age = 39, DateOfBirth = new DateTime(1980,1,1), IsActive = true}, + new Person{FirstName = "Leon", LastName = "Oosthuizen", Age = 40, DateOfBirth = new DateTime(1979,1,1), IsActive = false}, + new Person{FirstName = "Rian", LastName = "Prinsloo", Age = 41, DateOfBirth = new DateTime(1978,1,1), IsActive = false} + }; + + ws.FirstCell().CellRight().CellBelow().InsertTable(data); + + ws.Columns().AdjustToContents(); + + return wb; + } + + private XLWorkbook PrepareWorkbookWithAdditionalColumns() + { + var wb = PrepareWorkbook(); + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + table.HeadersRow() + .LastCell().CellRight() + .InsertData(new[] { "CumulativeAge", "NameLength", "IsOld", "HardCodedValue" }, transpose: true); + + table.Resize(ws.Range(table.FirstCell(), table.LastCell().CellRight(4))); + + table.Field("CumulativeAge").DataCells.ForEach(c => c.FormulaA1 = $"SUM($G$3:G{c.WorksheetRow().RowNumber()})"); + table.Field("NameLength").DataCells.ForEach(c => c.FormulaA1 = $"LEN(B{c.WorksheetRow().RowNumber()})"); + table.Field("IsOld").DataCells.ForEach(c => c.FormulaA1 = $"=G{c.WorksheetRow().RowNumber()}>=40"); + table.Field("HardCodedValue").DataCells.Value = "40 is not old!"; + + return wb; + } + + private Person[] NewData + { + get + { + return new[] + { + new Person{FirstName = "Michelle", LastName = "de Beer", Age = 35, DateOfBirth = new DateTime(1983,1,1), IsActive = false}, + new Person{FirstName = "Marichen", LastName = "van der Gryp", Age = 30, DateOfBirth = new DateTime(1990,1,1), IsActive = true} + }; + } + } + + [Test] + public void AddingEmptyEnumerables() + { + using (var wb = PrepareWorkbook()) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + + IEnumerable personEnumerable = null; + Assert.AreEqual(null, table.AppendData(personEnumerable)); + + personEnumerable = new Person[] { }; + Assert.AreEqual(null, table.AppendData(personEnumerable)); + + IEnumerable enumerable = null; + Assert.AreEqual(null, table.AppendData(enumerable)); + + enumerable = new Person[] { }; + Assert.AreEqual(null, table.AppendData(enumerable)); + } + } + + [Test] + public void ReplaceWithEmptyEnumerables() + { + using (var wb = PrepareWorkbook()) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + + IEnumerable personEnumerable = null; + Assert.Throws(() => table.ReplaceData(personEnumerable)); + + personEnumerable = new Person[] { }; + Assert.Throws(() => table.ReplaceData(personEnumerable)); + + IEnumerable enumerable = null; + Assert.Throws(() => table.ReplaceData(enumerable)); + + enumerable = new Person[] { }; + Assert.Throws(() => table.ReplaceData(enumerable)); + } + } + + [Test] + public void CanAppendTypedEnumerable() + { + using (var ms = new MemoryStream()) + { + using (var wb = PrepareWorkbook()) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + + IEnumerable personEnumerable = NewData; + var addedRange = table.AppendData(personEnumerable); + + Assert.AreEqual("B6:G7", addedRange.RangeAddress.ToString()); + ws.Columns().AdjustToContents(); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var table = wb.Worksheets.SelectMany(ws => ws.Tables).First(); + + Assert.AreEqual(5, table.DataRange.RowCount()); + Assert.AreEqual(6, table.DataRange.ColumnCount()); + } + } + } + + [Test] + public void CanAppendToTableWithTotalsRow() + { + using (var ms = new MemoryStream()) + { + using (var wb = PrepareWorkbook()) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + table.SetShowTotalsRow(true); + table.Fields.Last().TotalsRowFunction = XLTotalsRowFunction.Average; + + IEnumerable personEnumerable = NewData; + var addedRange = table.AppendData(personEnumerable); + + Assert.AreEqual("B6:G7", addedRange.RangeAddress.ToString()); + ws.Columns().AdjustToContents(); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var table = wb.Worksheets.SelectMany(ws => ws.Tables).First(); + + Assert.AreEqual(5, table.DataRange.RowCount()); + Assert.AreEqual(6, table.DataRange.ColumnCount()); + } + } + } + + [Test] + public void CanAppendTypedEnumerableAndPushDownCellsBelowTable() + { + using (var ms = new MemoryStream()) + { + var value = "Some value that will be overwritten"; + IXLAddress address; + using (var wb = PrepareWorkbook()) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + + var cell = table.LastRow().FirstCell().CellRight(2).CellBelow(1); + address = cell.Address; + cell.Value = value; + + IEnumerable personEnumerable = NewData; + var addedRange = table.AppendData(personEnumerable); + + Assert.AreEqual("B6:G7", addedRange.RangeAddress.ToString()); + ws.Columns().AdjustToContents(); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + + var cell = ws.Cell(address); + Assert.AreEqual("de Beer", cell.Value); + Assert.AreEqual(5, table.DataRange.RowCount()); + Assert.AreEqual(6, table.DataRange.ColumnCount()); + + Assert.AreEqual(value, cell.CellBelow(NewData.Count()).Value); + } + } + } + + [Test] + public void CanAppendUntypedEnumerable() + { + using (var ms = new MemoryStream()) + { + using (var wb = PrepareWorkbook()) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + + var list = new ArrayList(); + list.AddRange(NewData); + + var addedRange = table.AppendData(list); + + Assert.AreEqual("B6:G7", addedRange.RangeAddress.ToString()); + + ws.Columns().AdjustToContents(); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var table = wb.Worksheets.SelectMany(ws => ws.Tables).First(); + + Assert.AreEqual(5, table.DataRange.RowCount()); + Assert.AreEqual(6, table.DataRange.ColumnCount()); + } + } + } + + [Test] + public void CanAppendDataTable() + { + using (var ms = new MemoryStream()) + { + using (var wb = PrepareWorkbook()) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + + IEnumerable personEnumerable = NewData; + + var ws2 = wb.AddWorksheet("temp"); + var dataTable = ws2.FirstCell().InsertTable(personEnumerable).AsNativeDataTable(); + + var addedRange = table.AppendData(dataTable); + + Assert.AreEqual("B6:G7", addedRange.RangeAddress.ToString()); + ws.Columns().AdjustToContents(); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var table = wb.Worksheets.SelectMany(ws => ws.Tables).First(); + + Assert.AreEqual(5, table.DataRange.RowCount()); + Assert.AreEqual(6, table.DataRange.ColumnCount()); + } + } + } + + [Test] + public void CanReplaceWithTypedEnumerable() + { + using (var ms = new MemoryStream()) + { + using (var wb = PrepareWorkbook()) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + + IEnumerable personEnumerable = NewData; + var replacedRange = table.ReplaceData(personEnumerable); + + Assert.AreEqual("B3:G4", replacedRange.RangeAddress.ToString()); + ws.Columns().AdjustToContents(); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var table = wb.Worksheets.SelectMany(ws => ws.Tables).First(); + + Assert.AreEqual(2, table.DataRange.RowCount()); + Assert.AreEqual(6, table.DataRange.ColumnCount()); + } + } + } + + [Test] + public void CanReplaceWithUntypedEnumerable() + { + using (var ms = new MemoryStream()) + { + using (var wb = PrepareWorkbook()) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + + var list = new ArrayList(); + list.AddRange(NewData); + + var replacedRange = table.ReplaceData(list); + + Assert.AreEqual("B3:G4", replacedRange.RangeAddress.ToString()); + + ws.Columns().AdjustToContents(); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var table = wb.Worksheets.SelectMany(ws => ws.Tables).First(); + + Assert.AreEqual(2, table.DataRange.RowCount()); + Assert.AreEqual(6, table.DataRange.ColumnCount()); + } + } + } + + [Test] + public void CanReplaceWithDataTable() + { + using (var ms = new MemoryStream()) + { + using (var wb = PrepareWorkbook()) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + + IEnumerable personEnumerable = NewData; + + var ws2 = wb.AddWorksheet("temp"); + var dataTable = ws2.FirstCell().InsertTable(personEnumerable).AsNativeDataTable(); + + var replacedRange = table.ReplaceData(dataTable); + + Assert.AreEqual("B3:G4", replacedRange.RangeAddress.ToString()); + ws.Columns().AdjustToContents(); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var table = wb.Worksheets.SelectMany(ws => ws.Tables).First(); + + Assert.AreEqual(2, table.DataRange.RowCount()); + Assert.AreEqual(6, table.DataRange.ColumnCount()); + } + } + } + + [Test] + public void CanReplaceToTableWithTablesRow1() + { + using (var ms = new MemoryStream()) + { + using (var wb = PrepareWorkbook()) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + table.SetShowTotalsRow(true); + table.Fields.Last().TotalsRowFunction = XLTotalsRowFunction.Average; + + // Will cause table to overflow + IEnumerable personEnumerable = NewData.Union(NewData).Union(NewData); + var replacedRange = table.ReplaceData(personEnumerable); + + Assert.AreEqual("B3:G8", replacedRange.RangeAddress.ToString()); + ws.Columns().AdjustToContents(); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var table = wb.Worksheets.SelectMany(ws => ws.Tables).First(); + + Assert.AreEqual(6, table.DataRange.RowCount()); + Assert.AreEqual(6, table.DataRange.ColumnCount()); + } + } + } + + [Test] + public void CanReplaceToTableWithTablesRow2() + { + using (var ms = new MemoryStream()) + { + using (var wb = PrepareWorkbook()) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + table.SetShowTotalsRow(true); + table.Fields.Last().TotalsRowFunction = XLTotalsRowFunction.Average; + + // Will cause table to shrink + IEnumerable personEnumerable = NewData.Take(1); + var replacedRange = table.ReplaceData(personEnumerable); + + Assert.AreEqual("B3:G3", replacedRange.RangeAddress.ToString()); + ws.Columns().AdjustToContents(); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var table = wb.Worksheets.SelectMany(ws => ws.Tables).First(); + + Assert.AreEqual(1, table.DataRange.RowCount()); + Assert.AreEqual(6, table.DataRange.ColumnCount()); + } + } + } + + [Test] + public void CanReplaceWithUntypedEnumerableAndPropagateExtraColumns() + { + using (var ms = new MemoryStream()) + { + using (var wb = PrepareWorkbookWithAdditionalColumns()) + { + var ws = wb.Worksheets.First(); + var table = ws.Tables.First(); + + var list = new ArrayList(); + list.AddRange(NewData); + list.AddRange(NewData); + + var replacedRange = table.ReplaceData(list, propagateExtraColumns: true); + + Assert.AreEqual("B3:G6", replacedRange.RangeAddress.ToString()); + + ws.Columns().AdjustToContents(); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var table = wb.Worksheets.SelectMany(ws => ws.Tables).First(); + + Assert.AreEqual(4, table.DataRange.RowCount()); + Assert.AreEqual(10, table.DataRange.ColumnCount()); + + Assert.AreEqual("SUM($G$3:G5)", table.Worksheet.Cell("H5").FormulaA1); + Assert.AreEqual("SUM($G$3:G6)", table.Worksheet.Cell("H6").FormulaA1); + Assert.AreEqual(100, table.Worksheet.Cell("H5").Value); + Assert.AreEqual(130, table.Worksheet.Cell("H6").Value); + + Assert.AreEqual("LEN(B5)", table.Worksheet.Cell("I5").FormulaA1); + Assert.AreEqual("LEN(B6)", table.Worksheet.Cell("I6").FormulaA1); + Assert.AreEqual(16, table.Worksheet.Cell("I5").Value); + Assert.AreEqual(21, table.Worksheet.Cell("I6").Value); + + Assert.AreEqual("G5>=40", table.Worksheet.Cell("J5").FormulaA1); + Assert.AreEqual("G6>=40", table.Worksheet.Cell("J6").FormulaA1); + Assert.AreEqual(false, table.Worksheet.Cell("J5").Value); + Assert.AreEqual(false, table.Worksheet.Cell("J6").Value); + + Assert.AreEqual("40 is not old!", table.Worksheet.Cell("K5").Value); + Assert.AreEqual("40 is not old!", table.Worksheet.Cell("K6").Value); + } + } + } + + [Test] + public void CanReplaceWithTypedEnumerableAndPropagateExtraColumns() + { + using (var ms = new MemoryStream()) + { + using (var wb = PrepareWorkbookWithAdditionalColumns()) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + + IEnumerable personEnumerable = NewData.Concat(NewData).OrderBy(p => p.Age); + var replacedRange = table.ReplaceData(personEnumerable, propagateExtraColumns: true); + + Assert.AreEqual("B3:G6", replacedRange.RangeAddress.ToString()); + ws.Columns().AdjustToContents(); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var table = wb.Worksheets.SelectMany(ws => ws.Tables).First(); + + Assert.AreEqual(4, table.DataRange.RowCount()); + Assert.AreEqual(10, table.DataRange.ColumnCount()); + + Assert.AreEqual("SUM($G$3:G5)", table.Worksheet.Cell("H5").FormulaA1); + Assert.AreEqual("SUM($G$3:G6)", table.Worksheet.Cell("H6").FormulaA1); + Assert.AreEqual(95, table.Worksheet.Cell("H5").Value); + Assert.AreEqual(130, table.Worksheet.Cell("H6").Value); + + Assert.AreEqual("LEN(B5)", table.Worksheet.Cell("I5").FormulaA1); + Assert.AreEqual("LEN(B6)", table.Worksheet.Cell("I6").FormulaA1); + Assert.AreEqual(16, table.Worksheet.Cell("I5").Value); + Assert.AreEqual(16, table.Worksheet.Cell("I6").Value); + + Assert.AreEqual("G5>=40", table.Worksheet.Cell("J5").FormulaA1); + Assert.AreEqual("G6>=40", table.Worksheet.Cell("J6").FormulaA1); + Assert.AreEqual(false, table.Worksheet.Cell("J5").Value); + Assert.AreEqual(false, table.Worksheet.Cell("J6").Value); + + Assert.AreEqual("40 is not old!", table.Worksheet.Cell("K5").Value); + Assert.AreEqual("40 is not old!", table.Worksheet.Cell("K6").Value); + } + } + } + + [Test] + public void CanAppendWithUntypedEnumerableAndPropagateExtraColumns() + { + using (var ms = new MemoryStream()) + { + using (var wb = PrepareWorkbookWithAdditionalColumns()) + { + var ws = wb.Worksheets.First(); + var table = ws.Tables.First(); + + var list = new ArrayList(); + list.AddRange(NewData); + list.AddRange(NewData); + + var appendedRange = table.AppendData(list, propagateExtraColumns: true); + + Assert.AreEqual("B6:G9", appendedRange.RangeAddress.ToString()); + + ws.Columns().AdjustToContents(); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var table = wb.Worksheets.SelectMany(ws => ws.Tables).First(); + + Assert.AreEqual(7, table.DataRange.RowCount()); + Assert.AreEqual(10, table.DataRange.ColumnCount()); + + Assert.AreEqual("SUM($G$3:G8)", table.Worksheet.Cell("H8").FormulaA1); + Assert.AreEqual("SUM($G$3:G9)", table.Worksheet.Cell("H9").FormulaA1); + Assert.AreEqual(220, table.Worksheet.Cell("H8").Value); + Assert.AreEqual(250, table.Worksheet.Cell("H9").Value); + + Assert.AreEqual("LEN(B8)", table.Worksheet.Cell("I8").FormulaA1); + Assert.AreEqual("LEN(B9)", table.Worksheet.Cell("I9").FormulaA1); + Assert.AreEqual(16, table.Worksheet.Cell("I8").Value); + Assert.AreEqual(21, table.Worksheet.Cell("I9").Value); + + Assert.AreEqual("G8>=40", table.Worksheet.Cell("J8").FormulaA1); + Assert.AreEqual("G9>=40", table.Worksheet.Cell("J9").FormulaA1); + Assert.AreEqual(false, table.Worksheet.Cell("J8").Value); + Assert.AreEqual(false, table.Worksheet.Cell("J9").Value); + + Assert.AreEqual("40 is not old!", table.Worksheet.Cell("K8").Value); + Assert.AreEqual("40 is not old!", table.Worksheet.Cell("K9").Value); + } + } + } + + [Test] + public void CanAppendTypedEnumerableAndPropagateExtraColumns() + { + using (var ms = new MemoryStream()) + { + using (var wb = PrepareWorkbookWithAdditionalColumns()) + { + var ws = wb.Worksheets.First(); + + var table = ws.Tables.First(); + + IEnumerable personEnumerable = + NewData + .Concat(NewData) + .Concat(NewData) + .OrderBy(p => p.FirstName); + + var addedRange = table.AppendData(personEnumerable); + + Assert.AreEqual("B6:G11", addedRange.RangeAddress.ToString()); + ws.Columns().AdjustToContents(); + + wb.SaveAs(ms); + } + + using (var wb = new XLWorkbook(ms)) + { + var table = wb.Worksheets.SelectMany(ws => ws.Tables).First(); + + Assert.AreEqual(9, table.DataRange.RowCount()); + Assert.AreEqual(10, table.DataRange.ColumnCount()); + + Assert.AreEqual("SUM($G$3:G10)", table.Worksheet.Cell("H10").FormulaA1); + Assert.AreEqual("SUM($G$3:G11)", table.Worksheet.Cell("H11").FormulaA1); + Assert.AreEqual(280, table.Worksheet.Cell("H10").Value); + Assert.AreEqual(315, table.Worksheet.Cell("H11").Value); + + Assert.AreEqual("LEN(B10)", table.Worksheet.Cell("I10").FormulaA1); + Assert.AreEqual("LEN(B11)", table.Worksheet.Cell("I11").FormulaA1); + Assert.AreEqual(16, table.Worksheet.Cell("I10").Value); + Assert.AreEqual(16, table.Worksheet.Cell("I11").Value); + + Assert.AreEqual("G10>=40", table.Worksheet.Cell("J10").FormulaA1); + Assert.AreEqual("G11>=40", table.Worksheet.Cell("J11").FormulaA1); + Assert.AreEqual(false, table.Worksheet.Cell("J10").Value); + Assert.AreEqual(false, table.Worksheet.Cell("J11").Value); + + Assert.AreEqual("40 is not old!", table.Worksheet.Cell("K10").Value); + Assert.AreEqual("40 is not old!", table.Worksheet.Cell("K11").Value); + } + } + } + } +} diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index 193b38f..7d5d3ed 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -1,5 +1,6 @@ using ClosedXML.Attributes; using ClosedXML.Excel; +using ClosedXML.Excel.Exceptions; using NUnit.Framework; using System; using System.Collections.Generic; @@ -112,6 +113,25 @@ } [Test] + public void DataRange_returns_null_if_empty() + { + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Categories") + .CellBelow().SetValue("A") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C"); + + IXLTable table = ws.RangeUsed().CreateTable(); + + ws.Rows("2:4").Delete(); + + Assert.IsNull(table.DataRange); + } + } + + [Test] public void SavingLoadingTableWithNewLineInHeader() { using (var wb = new XLWorkbook()) @@ -441,6 +461,47 @@ } [Test] + public void TestFieldCellTypes() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } + }; + + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + var table = ws.Cell("B2").InsertTable(l); + + Assert.AreEqual(4, table.Fields.Count()); + + Assert.AreEqual("B2", table.Field(0).HeaderCell.Address.ToString()); + Assert.AreEqual("C2", table.Field(1).HeaderCell.Address.ToString()); + Assert.AreEqual("D2", table.Field(2).HeaderCell.Address.ToString()); + Assert.AreEqual("E2", table.Field(3).HeaderCell.Address.ToString()); + + Assert.IsNull(table.Field(0).TotalsCell); + Assert.IsNull(table.Field(1).TotalsCell); + Assert.IsNull(table.Field(2).TotalsCell); + Assert.IsNull(table.Field(3).TotalsCell); + + table.SetShowTotalsRow(); + + Assert.AreEqual("B5", table.Field(0).TotalsCell.Address.ToString()); + Assert.AreEqual("C5", table.Field(1).TotalsCell.Address.ToString()); + Assert.AreEqual("D5", table.Field(2).TotalsCell.Address.ToString()); + Assert.AreEqual("E5", table.Field(3).TotalsCell.Address.ToString()); + + var field = table.Fields.Last(); + + Assert.AreEqual("E2:E5", field.Column.RangeAddress.ToString()); + Assert.AreEqual("E3", field.DataCells.First().Address.ToString()); + Assert.AreEqual("E4", field.DataCells.Last().Address.ToString()); + } + } + + [Test] public void CanDeleteTable() { var l = new List() @@ -484,7 +545,9 @@ using (var wb = new XLWorkbook()) { var ws = wb.AddWorksheet("Sheet1"); - var table = ws.FirstCell().InsertTable(l); + var table = ws.Cell("B2").InsertTable(l); + + Assert.AreEqual("B2:E4", table.RangeAddress.ToString()); table.Field("SomeFieldNotProperty").Delete(); @@ -495,6 +558,37 @@ Assert.AreEqual("UnOrderedColumn", table.Fields.Last().Name); Assert.AreEqual(2, table.Fields.Last().Index); + + Assert.AreEqual("B2:D4", table.RangeAddress.ToString()); + } + } + + [Test] + public void CanDeleteTableRows() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 }, + new TestObjectWithAttributes() { Column1 = "e", Column2 = "f", MyField = 6, UnOrderedColumn = 555 }, + new TestObjectWithAttributes() { Column1 = "g", Column2 = "h", MyField = 7, UnOrderedColumn = 333 } + }; + + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + var table = ws.Cell("B2").InsertTable(l); + + Assert.AreEqual("B2:E6", table.RangeAddress.ToString()); + + table.DataRange.Rows(3, 4).Delete(); + + Assert.AreEqual(2, table.DataRange.Rows().Count()); + + Assert.AreEqual("b", table.DataRange.FirstCell().Value); + Assert.AreEqual(777, table.DataRange.LastCell().Value); + + Assert.AreEqual("B2:E4", table.RangeAddress.ToString()); } } @@ -778,7 +872,6 @@ ws.FirstCell().InsertTable(l); Assert.Throws(() => ws.RangeUsed().CreateTable()); } - } [Test] @@ -792,7 +885,6 @@ TestDelegate action = () => table.CopyTo(ws1); Assert.Throws(typeof(InvalidOperationException), action); - } [Test] @@ -886,6 +978,37 @@ Assert.AreEqual("", ws2.Cell("C2").Value); } + [Test] + public void SavingTableWithNullDataRangeThrowsException() + { + using (var ms = new MemoryStream()) + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + + var data = Enumerable.Range(1, 10) + .Select(i => new + { + Number = i, + NumberString = String.Concat("Number", i.ToString()) + }); + + var table = ws.FirstCell() + .InsertTable(data) + .SetShowTotalsRow(); + + table.Fields.Last().TotalsRowFunction = XLTotalsRowFunction.Count; + + table.DataRange.Rows() + .OrderByDescending(r => r.RowNumber()) + .ToList() + .ForEach(r => r.WorksheetRow().Delete()); + + Assert.IsNull(table.DataRange); + Assert.Throws(() => wb.SaveAs(ms)); + } + } + private void AssertTablesAreEqual(IXLTable table1, IXLTable table2) { Assert.AreEqual(table1.RangeAddress.ToString(XLReferenceStyle.A1, false), table2.RangeAddress.ToString(XLReferenceStyle.A1, false)); diff --git a/ClosedXML_Tests/Excel/Worksheets/XLSheetProtectionTests.cs b/ClosedXML_Tests/Excel/Worksheets/XLSheetProtectionTests.cs new file mode 100644 index 0000000..b74eaff --- /dev/null +++ b/ClosedXML_Tests/Excel/Worksheets/XLSheetProtectionTests.cs @@ -0,0 +1,40 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; + +namespace ClosedXML_Tests.Excel.Worksheets +{ + [TestFixture] + public class XLSheetProtectionTests + { + [Test] + public void TestUnprotectWorksheetWithNoPassword() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\SHA512PasswordProtection.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheet("Sheet1"); + Assert.IsTrue(ws.Protection.Protected); + ws.Unprotect(); + Assert.IsFalse(ws.Protection.Protected); + } + } + + [Test] + public void TestWorksheetWithSHA512Protection() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\SHA512PasswordProtection.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheet("Sheet2"); + Assert.IsTrue(ws.Protection.Protected); + // Protected with SHA-512 password - not yet supported. + Assert.Throws(() => ws.Unprotect()); + + // Protected with SHA-512 password - not yet supported. + Assert.Throws(() => ws.Unprotect("abc")); + Assert.IsTrue(ws.Protection.Protected); + } + } + } +} diff --git a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs index c280c16..b847362 100644 --- a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs +++ b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs @@ -683,7 +683,7 @@ Assert.AreEqual(original.Name, copy.Name); Assert.AreEqual(original.Placement, copy.Placement); Assert.AreEqual(original.Top, copy.Top); - Assert.AreEqual(original.TopLeftCellAddress.ToString(), copy.TopLeftCellAddress.ToString()); + Assert.AreEqual(original.TopLeftCell.Address.ToString(), copy.TopLeftCell.Address.ToString()); Assert.AreEqual(original.Width, copy.Width); Assert.AreEqual(original.ImageStream.ToArray(), copy.ImageStream.ToArray(), "Image streams differ"); } diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx index f61af20..e73de6b 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx index cab157a..ace0780 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx index 2b49926..b68d649 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/DataTypesUnderDifferentCulture.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProperties.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProperties.xlsx index d9fba2c..969d71b 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProperties.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProperties.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleIncludeQuotePrefix.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleIncludeQuotePrefix.xlsx new file mode 100644 index 0000000..5e08f98 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleIncludeQuotePrefix.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/HorizontalAlignment.xlsx b/ClosedXML_Tests/Resource/Misc/HorizontalAlignment.xlsx new file mode 100644 index 0000000..319da43 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/HorizontalAlignment.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/SHA512PasswordProtection.xlsx b/ClosedXML_Tests/Resource/Misc/SHA512PasswordProtection.xlsx new file mode 100644 index 0000000..61bf9c3 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/SHA512PasswordProtection.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/TemplateWithTableSourcePivotTables.xlsx b/ClosedXML_Tests/Resource/Misc/TemplateWithTableSourcePivotTables.xlsx new file mode 100644 index 0000000..d2d5a95 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/TemplateWithTableSourcePivotTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/ClearPivotTableRenderedRangeWhenLoading/inputfile.xlsx b/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/ClearPivotTableRenderedRangeWhenLoading/inputfile.xlsx new file mode 100644 index 0000000..4d0d6aa --- /dev/null +++ b/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/ClearPivotTableRenderedRangeWhenLoading/inputfile.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/PivotTableWithNoneTheme/inputfile.xlsx b/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/PivotTableWithNoneTheme/inputfile.xlsx new file mode 100644 index 0000000..915c374 --- /dev/null +++ b/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/PivotTableWithNoneTheme/inputfile.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/PivotTableWithNoneTheme/outputfile.xlsx b/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/PivotTableWithNoneTheme/outputfile.xlsx new file mode 100644 index 0000000..6843aec --- /dev/null +++ b/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/PivotTableWithNoneTheme/outputfile.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/SourceSheetWithWhitespace/outputfile.xlsx b/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/SourceSheetWithWhitespace/outputfile.xlsx new file mode 100644 index 0000000..77bfc8d --- /dev/null +++ b/ClosedXML_Tests/Resource/Other/PivotTableReferenceFiles/SourceSheetWithWhitespace/outputfile.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Other/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx b/ClosedXML_Tests/Resource/Other/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx index 6953f33..8e76ffd 100644 --- a/ClosedXML_Tests/Resource/Other/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx +++ b/ClosedXML_Tests/Resource/Other/StyleReferenceFiles/TransparentBackgroundFill/TransparentBackgroundFill.xlsx Binary files differ diff --git a/README.md b/README.md index e6eec9f..636b708 100644 --- a/README.md +++ b/README.md @@ -1,10 +1,11 @@ -# ClosedXML -[![Build status](https://ci.appveyor.com/api/projects/status/wobbmnlbukxejjgb?svg=true)](https://ci.appveyor.com/project/ClosedXML/ClosedXML/branch/develop/artifacts) +![ClosedXML](https://github.com/ClosedXML/ClosedXML/blob/develop/resources/logo/readme.png) + +[![Release](https://img.shields.io/badge/release-0.93.1-blue.svg)](https://github.com/ClosedXML/ClosedXML/releases/latest) [![NuGet Badge](https://buildstats.info/nuget/ClosedXML)](https://www.nuget.org/packages/ClosedXML/) [![.NET Framework](https://img.shields.io/badge/.NET%20Framework-%3E%3D%204.0-red.svg)](#) [![.NET Standard](https://img.shields.io/badge/.NET%20Standard-%3E%3D%202.0-red.svg)](#) [![Build status](https://ci.appveyor.com/api/projects/status/wobbmnlbukxejjgb?svg=true)](https://ci.appveyor.com/project/ClosedXML/ClosedXML/branch/develop/artifacts) [![Open Source Helpers](https://www.codetriage.com/closedxml/closedxml/badges/users.svg)](https://www.codetriage.com/closedxml/closedxml) [💾 Download unstable CI build](https://ci.appveyor.com/project/ClosedXML/ClosedXML/branch/develop/artifacts) -ClosedXML makes it easier for developers to create Excel 2007+ (.xlsx, .xlsm, etc) files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and Visual Basic (VB). +ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. It aims to provide an intuitive and user-friendly interface to dealing with the underlying [OpenXML](https://github.com/OfficeDev/Open-XML-SDK) API. [For more information see the wiki](https://github.com/closedxml/closedxml/wiki) @@ -20,15 +21,17 @@ ### What can you do with this? -ClosedXML allows you to create Excel 2007+ (.xlsx, .xlsm, etc) files without the Excel application. The typical example is creating Excel reports on a web server. +ClosedXML allows you to create Excel files without the Excel application. The typical example is creating Excel reports on a web server. -If you've ever used the Microsoft Open XML Format SDK you know just how much code you have to write to get the same results as the following 4 lines of code. - +**Example:** ```c# -var workbook = new XLWorkbook(); -var worksheet = workbook.Worksheets.Add("Sample Sheet"); -worksheet.Cell("A1").Value = "Hello World!"; -workbook.SaveAs("HelloWorld.xlsx"); +using (var workbook = new XLWorkbook()) +{ + var worksheet = workbook.Worksheets.Add("Sample Sheet"); + worksheet.Cell("A1").Value = "Hello World!"; + worksheet.Cell("A2").FormulaA1 = "=MID(A1, 7, 5)"; + workbook.SaveAs("HelloWorld.xlsx"); +} ``` ### Extensions @@ -39,7 +42,12 @@ ## Developer guidelines _Full guidelines to follow later_ * Please submit pull requests that are based on the `develop` branch. - ![sample work](https://i.imgur.com/cZfW7iW.png) Your pull request will take "yourWork" branch in your repo and merge into our develop branch. * Where possible, pull requests should include unit tests that cover as many uses cases as possible. This is especially relevant when implementing Excel functions. * Install [NUnit 3.0 Test Adapter](https://github.com/nunit/docs/wiki/Adapter-Installation) if you want to run the test suite in Visual Studio. * We use 4 spaces for code indentation. This is the default in Visual Studio. Don't leave any trailing white space at the end of lines or files. To make this easier, ClosedXML has an [editorconfig](http://www.editorconfig.org) configuration file. It is recommended you install editorconfig from the Visual Studio Extension Manager. + +## Credits +* Project originally created by Manuel de Leon +* Current maintainer: [Francois Botha](https://github.com/igitur) +* Master of Computing Patterns: [Aleksei Pankratev](https://github.com/Pankraty) +* Logo design by [@Tobaloidee](https://github.com/Tobaloidee) diff --git a/appveyor.yml b/appveyor.yml index 437b64b..8eb58b7 100644 --- a/appveyor.yml +++ b/appveyor.yml @@ -1,4 +1,4 @@ -version: 0.93.0.{build} +version: 0.94.0.{build} os: Visual Studio 2017 image: Visual Studio 2017 diff --git a/resources/logo/favicon-01.png b/resources/logo/favicon-01.png new file mode 100644 index 0000000..5b5e744 --- /dev/null +++ b/resources/logo/favicon-01.png Binary files differ diff --git a/resources/logo/favicon-01.svg b/resources/logo/favicon-01.svg new file mode 100644 index 0000000..2e903db --- /dev/null +++ b/resources/logo/favicon-01.svg @@ -0,0 +1,17 @@ + + + + + + + + + + + + + diff --git a/resources/logo/favicon-02.png b/resources/logo/favicon-02.png new file mode 100644 index 0000000..e830ad6 --- /dev/null +++ b/resources/logo/favicon-02.png Binary files differ diff --git a/resources/logo/favicon-02.svg b/resources/logo/favicon-02.svg new file mode 100644 index 0000000..5def4d0 --- /dev/null +++ b/resources/logo/favicon-02.svg @@ -0,0 +1,17 @@ + + + + + + + + + + + + + diff --git a/resources/logo/favicon-03.png b/resources/logo/favicon-03.png new file mode 100644 index 0000000..acfe5d1 --- /dev/null +++ b/resources/logo/favicon-03.png Binary files differ diff --git a/resources/logo/favicon-03.svg b/resources/logo/favicon-03.svg new file mode 100644 index 0000000..31107a1 --- /dev/null +++ b/resources/logo/favicon-03.svg @@ -0,0 +1,18 @@ + + + + + + + + + + + + + + diff --git a/resources/logo/favicon-04.png b/resources/logo/favicon-04.png new file mode 100644 index 0000000..bec930f --- /dev/null +++ b/resources/logo/favicon-04.png Binary files differ diff --git a/resources/logo/favicon-04.svg b/resources/logo/favicon-04.svg new file mode 100644 index 0000000..011185f --- /dev/null +++ b/resources/logo/favicon-04.svg @@ -0,0 +1,18 @@ + + + + + + + + + + + + + + diff --git a/resources/logo/logotype-a-05.png b/resources/logo/logotype-a-05.png new file mode 100644 index 0000000..de81445 --- /dev/null +++ b/resources/logo/logotype-a-05.png Binary files differ diff --git a/resources/logo/logotype-a-05.svg b/resources/logo/logotype-a-05.svg new file mode 100644 index 0000000..452feaf --- /dev/null +++ b/resources/logo/logotype-a-05.svg @@ -0,0 +1,61 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/resources/logo/logotype-a-06.png b/resources/logo/logotype-a-06.png new file mode 100644 index 0000000..8c8b21b --- /dev/null +++ b/resources/logo/logotype-a-06.png Binary files differ diff --git a/resources/logo/logotype-a-06.svg b/resources/logo/logotype-a-06.svg new file mode 100644 index 0000000..f51dc07 --- /dev/null +++ b/resources/logo/logotype-a-06.svg @@ -0,0 +1,59 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/resources/logo/logotype-a-07.png b/resources/logo/logotype-a-07.png new file mode 100644 index 0000000..be4bf8f --- /dev/null +++ b/resources/logo/logotype-a-07.png Binary files differ diff --git a/resources/logo/logotype-a-07.svg b/resources/logo/logotype-a-07.svg new file mode 100644 index 0000000..df63808 --- /dev/null +++ b/resources/logo/logotype-a-07.svg @@ -0,0 +1,58 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/resources/logo/logotype-a-08.png b/resources/logo/logotype-a-08.png new file mode 100644 index 0000000..043d8d7 --- /dev/null +++ b/resources/logo/logotype-a-08.png Binary files differ diff --git a/resources/logo/logotype-a-08.svg b/resources/logo/logotype-a-08.svg new file mode 100644 index 0000000..8077a2c --- /dev/null +++ b/resources/logo/logotype-a-08.svg @@ -0,0 +1,60 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/resources/logo/logotype-b-09.png b/resources/logo/logotype-b-09.png new file mode 100644 index 0000000..e082c0b --- /dev/null +++ b/resources/logo/logotype-b-09.png Binary files differ diff --git a/resources/logo/logotype-b-09.svg b/resources/logo/logotype-b-09.svg new file mode 100644 index 0000000..da6c5e5 --- /dev/null +++ b/resources/logo/logotype-b-09.svg @@ -0,0 +1,55 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/resources/logo/logotype-b-10.png b/resources/logo/logotype-b-10.png new file mode 100644 index 0000000..972834c --- /dev/null +++ b/resources/logo/logotype-b-10.png Binary files differ diff --git a/resources/logo/logotype-b-10.svg b/resources/logo/logotype-b-10.svg new file mode 100644 index 0000000..63442f1 --- /dev/null +++ b/resources/logo/logotype-b-10.svg @@ -0,0 +1,54 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/resources/logo/logotype-b-11.png b/resources/logo/logotype-b-11.png new file mode 100644 index 0000000..8757bcb --- /dev/null +++ b/resources/logo/logotype-b-11.png Binary files differ diff --git a/resources/logo/logotype-b-11.svg b/resources/logo/logotype-b-11.svg new file mode 100644 index 0000000..d9837b4 --- /dev/null +++ b/resources/logo/logotype-b-11.svg @@ -0,0 +1,56 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/resources/logo/logotype-b-12.png b/resources/logo/logotype-b-12.png new file mode 100644 index 0000000..758e04d --- /dev/null +++ b/resources/logo/logotype-b-12.png Binary files differ diff --git a/resources/logo/logotype-b-12.svg b/resources/logo/logotype-b-12.svg new file mode 100644 index 0000000..f1983dc --- /dev/null +++ b/resources/logo/logotype-b-12.svg @@ -0,0 +1,57 @@ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + diff --git a/resources/logo/nuget-logo.png b/resources/logo/nuget-logo.png new file mode 100644 index 0000000..3159920 --- /dev/null +++ b/resources/logo/nuget-logo.png Binary files differ diff --git a/resources/logo/readme.png b/resources/logo/readme.png new file mode 100644 index 0000000..99505ed --- /dev/null +++ b/resources/logo/readme.png Binary files differ