diff --git a/.github/ISSUE_TEMPLATE.md b/.github/ISSUE_TEMPLATE.md index e5313db..984600d 100644 --- a/.github/ISSUE_TEMPLATE.md +++ b/.github/ISSUE_TEMPLATE.md @@ -2,6 +2,10 @@ - [x] Bug - [ ] Feature +**Version of ClosedXML** + +e.g. 0.87.1 + **What is the current behavior?** **If the current behavior is a bug, please provide the steps to reproduce and diff --git a/ClosedXML/Attributes/XLColumnAttribute.cs b/ClosedXML/Attributes/XLColumnAttribute.cs index 41b541b..526dcb0 100644 --- a/ClosedXML/Attributes/XLColumnAttribute.cs +++ b/ClosedXML/Attributes/XLColumnAttribute.cs @@ -23,7 +23,7 @@ { var attribute = GetXLColumnAttribute(mi); if (attribute == null) return null; - return XLHelper.IsNullOrWhiteSpace(attribute.Header) ? null : attribute.Header; + return String.IsNullOrWhiteSpace(attribute.Header) ? null : attribute.Header; } internal static Int32 GetOrder(MemberInfo mi) diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index 893e0dd..0aa0a6a 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -68,6 +68,7 @@ + diff --git a/ClosedXML/Excel/CalcEngine/CalcEngine.cs b/ClosedXML/Excel/CalcEngine/CalcEngine.cs index 71e3d72..bafa237 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngine.cs @@ -816,7 +816,7 @@ private static void Throw(string msg) { - throw new Exception(msg); + throw new ExpressionParseException(msg); } #endregion ** static helpers diff --git a/ClosedXML/Excel/CalcEngine/ExpressionParseException.cs b/ClosedXML/Excel/CalcEngine/ExpressionParseException.cs new file mode 100644 index 0000000..2de2293 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/ExpressionParseException.cs @@ -0,0 +1,21 @@ +using System; +using System.Text; + +namespace ClosedXML.Excel.CalcEngine +{ + /// + /// The exception that is thrown when the strings to be parsed to an expression is invalid. + /// + public class ExpressionParseException : Exception + { + /// + /// Initializes a new instance of the ExpressionParseException class with a + /// specified error message. + /// + /// The message that describes the error. + public ExpressionParseException(string message) + : base(message) + { + } + } +} diff --git a/ClosedXML/Excel/CalcEngine/Functions/Text.cs b/ClosedXML/Excel/CalcEngine/Functions/Text.cs index cb66322..6046455 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Text.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Text.cs @@ -76,7 +76,7 @@ } var index = text.IndexOf(srch, start, StringComparison.Ordinal); if (index == -1) - throw new Exception("String not found."); + throw new ArgumentException("String not found."); else return index + 1; } @@ -192,7 +192,7 @@ var search = WildcardToRegex((string)p[0]); var text = (string)p[1]; - if ("" == text) throw new Exception("Invalid input string."); + if ("" == text) throw new ArgumentException("Invalid input string."); var start = 0; if (p.Count > 2) @@ -203,12 +203,12 @@ Regex r = new Regex(search, RegexOptions.Compiled | RegexOptions.IgnoreCase); var match = r.Match(text.Substring(start)); if (!match.Success) - throw new Exception("Search failed."); + throw new ArgumentException("Search failed."); else return match.Index + start + 1; //var index = text.IndexOf(search, start, StringComparison.OrdinalIgnoreCase); //if (index == -1) - // throw new Exception("String not found."); + // throw new ArgumentException("String not found."); //else // return index + 1; } @@ -233,7 +233,7 @@ int index = (int)p[3]; if (index < 1) { - throw new Exception("Invalid index in Substitute."); + throw new ArgumentException("Invalid index in Substitute."); } int pos = text.IndexOf(oldText); while (pos > -1 && index > 1) diff --git a/ClosedXML/Excel/CalcEngine/Functions/XLMatrix.cs b/ClosedXML/Excel/CalcEngine/Functions/XLMatrix.cs index 80d2663..79d6a70 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/XLMatrix.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/XLMatrix.cs @@ -59,7 +59,7 @@ public void MakeLU() // Function for LU decomposition { - if (!IsSquare()) throw new Exception("The matrix is not square!"); + if (!IsSquare()) throw new InvalidOperationException("The matrix is not square!"); L = IdentityMatrix(rows, cols); U = Duplicate(); @@ -79,8 +79,8 @@ k0 = i; } } - if (p == 0) - throw new Exception("The matrix is singular!"); + if (p == 0) + throw new InvalidOperationException("The matrix is singular!"); var pom1 = pi[k]; pi[k] = pi[k0]; @@ -115,8 +115,8 @@ public XLMatrix SolveWith(XLMatrix v) // Function solves Ax = v in confirmity with solution vector "v" { - if (rows != cols) throw new Exception("The matrix is not square!"); - if (rows != v.rows) throw new Exception("Wrong number of results in solution vector!"); + if (rows != cols) throw new InvalidOperationException("The matrix is not square!"); + if (rows != v.rows) throw new ArgumentException("Wrong number of results in solution vector!"); if (L == null) MakeLU(); var b = new XLMatrix(rows, 1); @@ -242,9 +242,9 @@ for (var j = 0; j < nums.Length; j++) matrix[i, j] = double.Parse(nums[j]); } } - catch (FormatException) + catch (FormatException fe) { - throw new Exception("Wrong input format!"); + throw new FormatException("Wrong input format!", fe); } return matrix; } @@ -345,7 +345,7 @@ private static XLMatrix StrassenMultiply(XLMatrix A, XLMatrix B) // Smart matrix multiplication { - if (A.cols != B.rows) throw new Exception("Wrong dimension of matrix!"); + if (A.cols != B.rows) throw new ArgumentException("Wrong dimension of matrix!"); XLMatrix R; @@ -513,7 +513,7 @@ public static XLMatrix StupidMultiply(XLMatrix m1, XLMatrix m2) // Stupid matrix multiplication { - if (m1.cols != m2.rows) throw new Exception("Wrong dimensions of matrix!"); + if (m1.cols != m2.rows) throw new ArgumentException("Wrong dimensions of matrix!"); var result = ZeroMatrix(m1.rows, m2.cols); for (var i = 0; i < result.rows; i++) @@ -535,7 +535,7 @@ private static XLMatrix Add(XLMatrix m1, XLMatrix m2) { if (m1.rows != m2.rows || m1.cols != m2.cols) - throw new Exception("Matrices must have the same dimensions!"); + throw new ArgumentException("Matrices must have the same dimensions!"); var r = new XLMatrix(m1.rows, m1.cols); for (var i = 0; i < r.rows; i++) for (var j = 0; j < r.cols; j++) diff --git a/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs b/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs index 33011e2..108ea40 100644 --- a/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs @@ -92,13 +92,13 @@ { if (_evaluating) { - throw new Exception("Circular Reference"); + throw new InvalidOperationException("Circular Reference"); } try { _evaluating = true; var f = cell.FormulaA1; - if (XLHelper.IsNullOrWhiteSpace(f)) + if (String.IsNullOrWhiteSpace(f)) return cell.Value; else return new XLCalcEngine(cell.Worksheet).Evaluate(f); diff --git a/ClosedXML/Excel/Cells/IXLCell.cs b/ClosedXML/Excel/Cells/IXLCell.cs index d685041..9e82e5a 100644 --- a/ClosedXML/Excel/Cells/IXLCell.cs +++ b/ClosedXML/Excel/Cells/IXLCell.cs @@ -173,6 +173,14 @@ IXLRange InsertData(IEnumerable data); /// + /// 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. + /// + IXLRange InsertData(IEnumerable data, Boolean tranpose); + + /// /// Inserts the IEnumerable data elements as a table and returns it. /// The new table will receive a generic name: Table# /// @@ -260,7 +268,7 @@ IXLCells InsertCellsBefore(int numberOfColumns); /// - /// Creates a named range out of this cell. + /// Creates a named range out of this cell. /// If the named range exists, it will add this range to that named range. /// The default scope for the named range is Workbook. /// @@ -268,7 +276,7 @@ IXLCell AddToNamed(String rangeName); /// - /// Creates a named range out of this cell. + /// Creates a named range out of this cell. /// If the named range exists, it will add this range to that named range. /// Name of the range. /// The scope for the named range. @@ -276,7 +284,7 @@ IXLCell AddToNamed(String rangeName, XLScope scope); /// - /// Creates a named range out of this cell. + /// Creates a named range out of this cell. /// If the named range exists, it will add this range to that named range. /// Name of the range. /// The scope for the named range. diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index faf129b..8aaea02 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -281,7 +281,7 @@ if (TryGetValue(out retVal)) return retVal; - throw new Exception("Cannot convert cell value to " + typeof(T)); + throw new FormatException("Cannot convert cell value to " + typeof(T)); } public string GetString() @@ -365,7 +365,7 @@ get { var fA1 = FormulaA1; - if (!XLHelper.IsNullOrWhiteSpace(fA1)) + if (!String.IsNullOrWhiteSpace(fA1)) { if (fA1[0] == '{') fA1 = fA1.Substring(1, fA1.Length - 2); @@ -470,7 +470,7 @@ public IXLTable InsertTable(IEnumerable data, string tableName, bool createTable) { - if (data != null && data.GetType() != typeof(String)) + if (data != null && !(data is String)) { var ro = Address.RowNumber + 1; var fRo = Address.RowNumber; @@ -496,7 +496,7 @@ if (!hasTitles) { var fieldName = XLColumnAttribute.GetHeader(itemType); - if (XLHelper.IsNullOrWhiteSpace(fieldName)) + if (String.IsNullOrWhiteSpace(fieldName)) fieldName = itemType.Name; SetValue(fieldName, fRo, co); @@ -574,7 +574,7 @@ if (!hasTitles) { foreach (var fieldName in from DataColumn column in row.Table.Columns - select XLHelper.IsNullOrWhiteSpace(column.Caption) + select String.IsNullOrWhiteSpace(column.Caption) ? column.ColumnName : column.Caption) { @@ -627,7 +627,7 @@ if ((mi as IEnumerable) == null) { var fieldName = XLColumnAttribute.GetHeader(mi); - if (XLHelper.IsNullOrWhiteSpace(fieldName)) + if (String.IsNullOrWhiteSpace(fieldName)) fieldName = mi.Name; SetValue(fieldName, fRo, co); @@ -713,10 +713,18 @@ public IXLRange InsertData(IEnumerable data) { - if (data != null && data.GetType() != typeof(String)) + return InsertData(data, false); + } + + public IXLRange InsertData(IEnumerable data, Boolean transpose) + { + if (data != null && !(data is String)) { - var ro = Address.RowNumber; - var maxCo = 0; + var rowNumber = Address.RowNumber; + var columnNumber = Address.ColumnNumber; + + var maxColumnNumber = 0; + var maxRowNumber = 0; var isDataTable = false; var isDataReader = false; @@ -745,20 +753,31 @@ members = memberCache[itemType]; accessor = accessorCache[itemType]; - var co = Address.ColumnNumber; + if (transpose) + rowNumber = Address.RowNumber; + else + columnNumber = Address.ColumnNumber; + if (itemType.IsPrimitive || itemType == typeof(String) || itemType == typeof(DateTime) || itemType.IsNumber()) { - SetValue(m, ro, co); - co++; + SetValue(m, rowNumber, columnNumber); + + if (transpose) + rowNumber++; + else + columnNumber++; } else if (itemType.IsArray) { - // dynamic arr = m; foreach (var item in (Array)m) { - SetValue(item, ro, co); - co++; + SetValue(item, rowNumber, columnNumber); + + if (transpose) + rowNumber++; + else + columnNumber++; } } else if (isDataTable || m is DataRow) @@ -768,8 +787,12 @@ foreach (var item in (m as DataRow).ItemArray) { - SetValue(item, ro, co); - co++; + SetValue(item, rowNumber, columnNumber); + + if (transpose) + rowNumber++; + else + columnNumber++; } } else if (isDataReader || m is IDataRecord) @@ -782,31 +805,45 @@ var fieldCount = record.FieldCount; for (var i = 0; i < fieldCount; i++) { - SetValue(record[i], ro, co); - co++; + SetValue(record[i], rowNumber, columnNumber); + + if (transpose) + rowNumber++; + else + columnNumber++; } } else { foreach (var mi in members) { - SetValue(accessor[m, mi.Name], ro, co); - co++; + SetValue(accessor[m, mi.Name], rowNumber, columnNumber); + + if (transpose) + rowNumber++; + else + columnNumber++; } } - if (co > maxCo) - maxCo = co; + if (transpose) + columnNumber++; + else + rowNumber++; - ro++; + if (columnNumber > maxColumnNumber) + maxColumnNumber = columnNumber; + + if (rowNumber > maxRowNumber) + maxRowNumber = rowNumber; } ClearMerged(); return _worksheet.Range( Address.RowNumber, Address.ColumnNumber, - ro - 1, - maxCo - 1); + maxRowNumber - 1, + maxColumnNumber - 1); } return null; @@ -968,9 +1005,9 @@ { get { - if (XLHelper.IsNullOrWhiteSpace(_formulaA1)) + if (String.IsNullOrWhiteSpace(_formulaA1)) { - if (!XLHelper.IsNullOrWhiteSpace(_formulaR1C1)) + if (!String.IsNullOrWhiteSpace(_formulaR1C1)) { _formulaA1 = GetFormulaA1(_formulaR1C1); return FormulaA1; @@ -990,7 +1027,7 @@ set { - _formulaA1 = XLHelper.IsNullOrWhiteSpace(value) ? null : value; + _formulaA1 = String.IsNullOrWhiteSpace(value) ? null : value; _formulaR1C1 = null; } @@ -1000,7 +1037,7 @@ { get { - if (XLHelper.IsNullOrWhiteSpace(_formulaR1C1)) + if (String.IsNullOrWhiteSpace(_formulaR1C1)) _formulaR1C1 = GetFormulaR1C1(FormulaA1); return _formulaR1C1; @@ -1008,7 +1045,7 @@ set { - _formulaR1C1 = XLHelper.IsNullOrWhiteSpace(value) ? null : value; + _formulaR1C1 = String.IsNullOrWhiteSpace(value) ? null : value; } } @@ -1532,7 +1569,7 @@ { var style = GetStyleForRead(); return _dataType == XLCellValues.Number - && XLHelper.IsNullOrWhiteSpace(style.NumberFormat.Format) + && String.IsNullOrWhiteSpace(style.NumberFormat.Format) && ((style.NumberFormat.NumberFormatId >= 14 && style.NumberFormat.NumberFormatId <= 22) || (style.NumberFormat.NumberFormatId >= 45 @@ -1543,7 +1580,7 @@ { var format = String.Empty; var style = GetStyleForRead(); - if (XLHelper.IsNullOrWhiteSpace(style.NumberFormat.Format)) + if (String.IsNullOrWhiteSpace(style.NumberFormat.Format)) { var formatCodes = GetFormatCodes(); if (formatCodes.ContainsKey(style.NumberFormat.NumberFormatId)) @@ -1620,7 +1657,7 @@ private bool SetEnumerable(object collectionObject) { // IXLRichText implements IEnumerable, but we don't want to handle this here. - if ((collectionObject as IXLRichText) != null) return false; + if (collectionObject is IXLRichText) return false; var asEnumerable = collectionObject as IEnumerable; return InsertData(asEnumerable) != null; @@ -1639,13 +1676,10 @@ { if (value == null) _worksheet.Cell(ro, co).SetValue(String.Empty); + else if (value is IConvertible) + _worksheet.Cell(ro, co).SetValue((T)Convert.ChangeType(value, typeof(T))); else - { - if (value is IConvertible) - _worksheet.Cell(ro, co).SetValue((T)Convert.ChangeType(value, typeof(T))); - else - _worksheet.Cell(ro, co).SetValue(value); - } + _worksheet.Cell(ro, co).SetValue(value); } private void SetValue(object value) @@ -1783,7 +1817,7 @@ private string GetFormula(string strValue, FormulaConversionType conversionType, int rowsToShift, int columnsToShift) { - if (XLHelper.IsNullOrWhiteSpace(strValue)) + if (String.IsNullOrWhiteSpace(strValue)) return String.Empty; var value = ">" + strValue + "<"; @@ -2057,7 +2091,7 @@ internal static String ShiftFormulaRows(String formulaA1, XLWorksheet worksheetInAction, XLRange shiftedRange, int rowsShifted) { - if (XLHelper.IsNullOrWhiteSpace(formulaA1)) return String.Empty; + if (String.IsNullOrWhiteSpace(formulaA1)) return String.Empty; var value = formulaA1; // ">" + formulaA1 + "<"; @@ -2262,7 +2296,7 @@ internal static String ShiftFormulaColumns(String formulaA1, XLWorksheet worksheetInAction, XLRange shiftedRange, int columnsShifted) { - if (XLHelper.IsNullOrWhiteSpace(formulaA1)) return String.Empty; + if (String.IsNullOrWhiteSpace(formulaA1)) return String.Empty; var value = formulaA1; // ">" + formulaA1 + "<"; @@ -2590,7 +2624,7 @@ #endregion XLCell Right - public Boolean HasFormula { get { return !XLHelper.IsNullOrWhiteSpace(FormulaA1); } } + public Boolean HasFormula { get { return !String.IsNullOrWhiteSpace(FormulaA1); } } public IXLRangeAddress FormulaReference { get; set; } } diff --git a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs index f0bd151..ff2d3c0 100644 --- a/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs +++ b/ClosedXML/Excel/ConditionalFormats/Save/XLCFDataBarConverter.cs @@ -7,20 +7,32 @@ namespace ClosedXML.Excel { - internal class XLCFDataBarConverter:IXLCFConverter + internal class XLCFDataBarConverter : IXLCFConverter { public ConditionalFormattingRule Convert(IXLConditionalFormat cf, Int32 priority, XLWorkbook.SaveContext context) { var conditionalFormattingRule = new ConditionalFormattingRule { Type = cf.ConditionalFormatType.ToOpenXml(), Priority = priority }; - var dataBar = new DataBar {ShowValue = !cf.ShowBarOnly}; - var conditionalFormatValueObject1 = new ConditionalFormatValueObject { Type = cf.ContentTypes[1].ToOpenXml()}; - if (cf.Values.Count >= 1) conditionalFormatValueObject1.Val = cf.Values[1].Value; + var dataBar = new DataBar { ShowValue = !cf.ShowBarOnly }; + var conditionalFormatValueObject1 = new ConditionalFormatValueObject { Type = cf.ContentTypes[1].ToOpenXml() }; + if (cf.Values.Any() && cf.Values[1]?.Value != null) conditionalFormatValueObject1.Val = cf.Values[1].Value; - var conditionalFormatValueObject2 = new ConditionalFormatValueObject { Type = cf.ContentTypes[2].ToOpenXml()}; - if (cf.Values.Count >= 2) conditionalFormatValueObject2.Val = cf.Values[2].Value; + var conditionalFormatValueObject2 = new ConditionalFormatValueObject { Type = cf.ContentTypes[2].ToOpenXml() }; + if (cf.Values.Count >= 2 && cf.Values[2]?.Value != null) conditionalFormatValueObject2.Val = cf.Values[2].Value; - var color = new Color { Rgb = cf.Colors[1].Color.ToHex() }; + var color = new Color(); + switch (cf.Colors[1].ColorType) + { + case XLColorType.Color: + color.Rgb = cf.Colors[1].Color.ToHex(); + break; + case XLColorType.Theme: + color.Theme = System.Convert.ToUInt32(cf.Colors[1].ThemeColor); + break; + case XLColorType.Indexed: + color.Indexed = System.Convert.ToUInt32(cf.Colors[1].Indexed); + break; + } dataBar.Append(conditionalFormatValueObject1); dataBar.Append(conditionalFormatValueObject2); diff --git a/ClosedXML/Excel/DataValidation/XLDataValidation.cs b/ClosedXML/Excel/DataValidation/XLDataValidation.cs index 321e6d8..17164c0 100644 --- a/ClosedXML/Excel/DataValidation/XLDataValidation.cs +++ b/ClosedXML/Excel/DataValidation/XLDataValidation.cs @@ -41,9 +41,9 @@ return AllowedValues != XLAllowedValues.AnyValue || (ShowInputMessage && - (!XLHelper.IsNullOrWhiteSpace(InputTitle) || !XLHelper.IsNullOrWhiteSpace(InputMessage))) + (!String.IsNullOrWhiteSpace(InputTitle) || !String.IsNullOrWhiteSpace(InputMessage))) ||(ShowErrorMessage && - (!XLHelper.IsNullOrWhiteSpace(ErrorTitle) || !XLHelper.IsNullOrWhiteSpace(ErrorMessage))); + (!String.IsNullOrWhiteSpace(ErrorTitle) || !String.IsNullOrWhiteSpace(ErrorMessage))); } diff --git a/ClosedXML/Excel/Drawings/XLPicture.cs b/ClosedXML/Excel/Drawings/XLPicture.cs index 86d45ae..422336d 100644 --- a/ClosedXML/Excel/Drawings/XLPicture.cs +++ b/ClosedXML/Excel/Drawings/XLPicture.cs @@ -159,7 +159,7 @@ if (value.IndexOfAny(InvalidNameChars.ToCharArray()) != -1) throw new ArgumentException($"Picture names cannot contain any of the following characters: {InvalidNameChars}"); - if (XLHelper.IsNullOrWhiteSpace(value)) + if (String.IsNullOrWhiteSpace(value)) throw new ArgumentException("Picture names cannot be empty"); if (value.Length > 31) diff --git a/ClosedXML/Excel/Misc/XLFormula.cs b/ClosedXML/Excel/Misc/XLFormula.cs index 34cea84..8027667 100644 --- a/ClosedXML/Excel/Misc/XLFormula.cs +++ b/ClosedXML/Excel/Misc/XLFormula.cs @@ -44,7 +44,7 @@ else { _value = value.Trim(); - IsFormula = !XLHelper.IsNullOrWhiteSpace(_value) && _value.TrimStart()[0] == '=' ; + IsFormula = !String.IsNullOrWhiteSpace(_value) && _value.TrimStart()[0] == '=' ; if (IsFormula) _value = _value.Substring(1); } diff --git a/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 76902d0..8129cfa 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -1,4 +1,5 @@ using System; +using System.Globalization; namespace ClosedXML.Excel { @@ -8,7 +9,7 @@ Worksheet } - public interface IXLRangeBase: IDisposable + public interface IXLRangeBase : IDisposable { IXLWorksheet Worksheet { get; } @@ -64,7 +65,6 @@ IXLHyperlinks Hyperlinks { get; } - /// /// Returns the collection of cells. /// @@ -94,6 +94,15 @@ IXLCells CellsUsed(Boolean includeFormats, Func predicate); /// + /// Searches the cells' contents for a given piece of text + /// + /// The search text. + /// The compare options. + /// if set to true search formulae instead of cell values. + /// + IXLCells Search(String searchText, CompareOptions compareOptions = CompareOptions.Ordinal, Boolean searchFormulae = false); + + /// /// Returns the first cell of this range. /// IXLCell FirstCell(); @@ -236,15 +245,15 @@ IXLRange AsRange(); Boolean IsMerged(); + Boolean IsEmpty(); + Boolean IsEmpty(Boolean includeFormats); - IXLPivotTable CreatePivotTable(IXLCell targetCell); + IXLPivotTable CreatePivotTable(IXLCell targetCell, String name); - - //IXLChart CreateChart(Int32 firstRow, Int32 firstColumn, Int32 lastRow, Int32 lastColumn); IXLAutoFilter SetAutoFilter(); diff --git a/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/Excel/Ranges/XLRangeAddress.cs index df631ec..fbe5135 100644 --- a/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -84,7 +84,7 @@ get { if (IsInvalid) - throw new Exception("Range is invalid."); + throw new InvalidOperationException("Range is invalid."); return _firstAddress; } @@ -96,7 +96,7 @@ get { if (IsInvalid) - throw new Exception("Range is an invalid state."); + throw new InvalidOperationException("Range is an invalid state."); return _lastAddress; } diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 6e34637..8f3f613 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -2,36 +2,39 @@ using ClosedXML.Extensions; using System; using System.Collections.Generic; +using System.Globalization; using System.Linq; - - namespace ClosedXML.Excel { internal abstract class XLRangeBase : IXLRangeBase, IXLStylized { public Boolean StyleChanged { get; set; } + #region Fields private IXLStyle _style; private XLSortElements _sortRows; private XLSortElements _sortColumns; - #endregion + #endregion Fields private Int32 _styleCacheId; + protected void SetStyle(IXLStyle styleToUse) { _styleCacheId = Worksheet.Workbook.GetStyleId(styleToUse); _style = null; StyleChanged = false; } + protected void SetStyle(Int32 styleId) { _styleCacheId = styleId; _style = null; StyleChanged = false; } + public Int32 GetStyleId() { if (StyleChanged) @@ -39,6 +42,7 @@ return _styleCacheId; } + protected IXLStyle GetStyle() { return _style ?? (_style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId))); @@ -46,18 +50,17 @@ #region Constructor - static Int32 IdCounter = 0; - readonly Int32 Id; + private static Int32 IdCounter = 0; + private readonly Int32 Id; protected XLRangeBase(XLRangeAddress rangeAddress) { - Id = ++IdCounter; RangeAddress = new XLRangeAddress(rangeAddress); } - #endregion + #endregion Constructor private XLCallbackAction _shiftedRowsAction; @@ -71,6 +74,7 @@ } private XLCallbackAction _shiftedColumnsAction; + protected void SubscribeToShiftedColumns(Action action) { if (Worksheet == null || !Worksheet.EventTrackingEnabled) return; @@ -85,6 +89,7 @@ //public XLRangeAddress RangeAddress { get; protected set; } private XLRangeAddress _rangeAddress; + public XLRangeAddress RangeAddress { get { return _rangeAddress; } @@ -243,8 +248,6 @@ } } - - public Object Value { set { Cells().ForEach(c => c.Value = value); } @@ -255,7 +258,7 @@ set { Cells().ForEach(c => c.DataType = value); } } - #endregion + #endregion IXLRangeBase Members #region IXLStylized Members @@ -268,9 +271,9 @@ } } - #endregion + #endregion IXLStylized Members - #endregion + #endregion Public properties #region IXLRangeBase Members @@ -457,17 +460,17 @@ get { return GetStyle(); } set { Cells().ForEach(c => c.Style = value); } } + IXLRange IXLRangeBase.AsRange() { return AsRange(); } + public virtual XLRange AsRange() { return Worksheet.Range(RangeAddress.FirstAddress, RangeAddress.LastAddress); } - - public IXLRange AddToNamed(String rangeName) { return AddToNamed(rangeName, XLScope.Workbook); @@ -516,7 +519,7 @@ CellsUsed(includeFormats).Cast().Any(c => c.IsEmpty(includeFormats)); } - #endregion + #endregion IXLRangeBase Members #region IXLStylized Members @@ -539,7 +542,28 @@ set { SetStyle(value); } } - #endregion + #endregion IXLStylized Members + + public IXLCells Search(String searchText, CompareOptions compareOptions = CompareOptions.Ordinal, Boolean searchFormulae = false) + { + var culture = CultureInfo.CurrentCulture; + return this.CellsUsed(false, c => + { + try + { + if (searchFormulae) + return c.HasFormula + && culture.CompareInfo.IndexOf(c.FormulaA1, searchText, compareOptions) >= 0 + || culture.CompareInfo.IndexOf(c.Value.ToString(), searchText, compareOptions) >= 0; + else + return culture.CompareInfo.IndexOf(c.GetFormattedString(), searchText, compareOptions) >= 0; + } + catch + { + return false; + } + }); + } public XLCell FirstCell() { @@ -642,7 +666,6 @@ } } - if (sp.Row > 0) return Worksheet.Cell(sp.Row, sp.Column); @@ -734,7 +757,6 @@ } } - if (sp.Row > 0) return Worksheet.Cell(sp.Row, sp.Column); @@ -748,7 +770,6 @@ public XLCell Cell(String cellAddressInRange) { - if (XLHelper.IsValidA1Address(cellAddressInRange)) return Cell(XLAddress.Create(Worksheet, cellAddressInRange)); @@ -810,7 +831,7 @@ Int32 newCellStyleId = styleId; - // If the default style for this range base is empty, but the worksheet + // If the default style for this range base is empty, but the worksheet // has a default style, use the worksheet's default style if (styleId == 0 && worksheetStyleId != 0) newCellStyleId = worksheetStyleId; @@ -904,7 +925,6 @@ public XLRange Range(IXLRangeAddress rangeAddress) { - var newFirstCellAddress = new XLAddress((XLWorksheet)rangeAddress.FirstAddress.Worksheet, rangeAddress.FirstAddress.RowNumber + RangeAddress.FirstAddress.RowNumber - 1, rangeAddress.FirstAddress.ColumnNumber + RangeAddress.FirstAddress.ColumnNumber - 1, @@ -1072,12 +1092,11 @@ { foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) { - foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !XLHelper.IsNullOrWhiteSpace(c.FormulaA1))) + foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !String.IsNullOrWhiteSpace(c.FormulaA1))) using (var asRange = AsRange()) cell.ShiftFormulaColumns(asRange, numberOfColumns); } - var cellsDataValidations = new Dictionary(); var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); @@ -1193,7 +1212,6 @@ : Worksheet.Style; rangeToReturn.Row(ro).Style = styleToUse; } - } } @@ -1284,15 +1302,17 @@ return retVal; } - struct DataValidationToCopy + private struct DataValidationToCopy { public XLAddress SourceAddress; public XLDataValidation DataValidation; } + public void InsertRowsAboveVoid(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) { InsertRowsAboveInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: true); } + public IXLRangeRows InsertRowsAbove(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) { return InsertRowsAboveInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: false); @@ -1303,7 +1323,7 @@ using (var asRange = AsRange()) foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) { - foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !XLHelper.IsNullOrWhiteSpace(c.FormulaA1))) + foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !String.IsNullOrWhiteSpace(c.FormulaA1))) cell.ShiftFormulaRows(asRange, numberOfRows); } @@ -1363,7 +1383,6 @@ newCell.FormulaA1 = c.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(c.Address); - } } @@ -1381,7 +1400,6 @@ cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c.RowNumber, c.ColumnNumber)); cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value)); - Int32 firstRowReturn = RangeAddress.FirstAddress.RowNumber; Int32 lastRowReturn = RangeAddress.FirstAddress.RowNumber + numberOfRows - 1; Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber; @@ -1472,12 +1490,11 @@ RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber); - foreach ( XLCell cell in Worksheet.Workbook.Worksheets.Cast().SelectMany( xlWorksheet => (xlWorksheet).Internals.CellsCollection.GetCells( - c => !XLHelper.IsNullOrWhiteSpace(c.FormulaA1)))) + c => !String.IsNullOrWhiteSpace(c.FormulaA1)))) { if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) cell.ShiftFormulaRows((XLRange)shiftedRangeFormula, numberOfRows * -1); @@ -1501,7 +1518,6 @@ Worksheet.Internals.CellsCollection.MaxRowUsed, RangeAddress.LastAddress.ColumnNumber); - int columnModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? ColumnCount() : 0; int rowModifier = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp ? RowCount() : 0; var cellsQuery = shiftDeleteCells == XLShiftDeletedCells.ShiftCellsLeft ? shiftLeftQuery : shiftUpQuery; @@ -1523,7 +1539,6 @@ cellsToInsert.Add(newKey, newCell); } - cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c.RowNumber, c.ColumnNumber)); cellsToInsert.ForEach( c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value)); @@ -1698,17 +1713,16 @@ // return chart; //} - IXLPivotTable IXLRangeBase.CreatePivotTable(IXLCell targetCell) { return CreatePivotTable(targetCell); } + IXLPivotTable IXLRangeBase.CreatePivotTable(IXLCell targetCell, String name) { return CreatePivotTable(targetCell, name); } - public XLPivotTable CreatePivotTable(IXLCell targetCell) { return CreatePivotTable(targetCell, Guid.NewGuid().ToString()); @@ -1760,7 +1774,7 @@ public IXLRangeBase Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { SortColumns.Clear(); - if (XLHelper.IsNullOrWhiteSpace(columnsToSortBy)) + if (String.IsNullOrWhiteSpace(columnsToSortBy)) { columnsToSortBy = String.Empty; Int32 maxColumn = ColumnCount(); @@ -1821,7 +1835,6 @@ return this; } - #region Sort Rows private void SortRangeRows() @@ -1886,7 +1899,7 @@ SortingRangeRows(pivot + 1, end); } - #endregion + #endregion Sort Rows #region Sort Columns @@ -1951,9 +1964,9 @@ SortingRangeColumns(pivot + 1, end); } - #endregion + #endregion Sort Columns - #endregion + #endregion Sort public XLRangeColumn ColumnQuick(Int32 column) { @@ -2017,7 +2030,6 @@ } } - internal IXLConditionalFormat AddConditionalFormat(IXLConditionalFormat source) { using (var asRange = AsRange()) diff --git a/ClosedXML/Excel/Style/Colors/XLColor_Public.cs b/ClosedXML/Excel/Style/Colors/XLColor_Public.cs index 6feefe3..4d0d9d3 100644 --- a/ClosedXML/Excel/Style/Colors/XLColor_Public.cs +++ b/ClosedXML/Excel/Style/Colors/XLColor_Public.cs @@ -55,7 +55,7 @@ get { if (_colorType == XLColorType.Theme) - throw new Exception("Cannot convert theme color to Color."); + throw new InvalidOperationException("Cannot convert theme color to Color."); if (_colorType == XLColorType.Indexed) if (_indexed == TOOLTIPCOLORINDEX) @@ -72,12 +72,12 @@ get { if (ColorType == XLColorType.Theme) - throw new Exception("Cannot convert theme color to indexed color."); + throw new InvalidOperationException("Cannot convert theme color to indexed color."); if (ColorType == XLColorType.Indexed) return _indexed; - throw new Exception("Cannot convert Color to indexed color."); + throw new InvalidOperationException("Cannot convert Color to indexed color."); } } @@ -89,9 +89,9 @@ return _themeColor; if (ColorType == XLColorType.Indexed) - throw new Exception("Cannot convert indexed color to theme color."); + throw new InvalidOperationException("Cannot convert indexed color to theme color."); - throw new Exception("Cannot convert Color to theme color."); + throw new InvalidOperationException("Cannot convert Color to theme color."); } } @@ -103,7 +103,7 @@ return _themeTint; if (ColorType == XLColorType.Indexed) - throw new Exception("Cannot extract theme tint from an indexed color."); + throw new InvalidOperationException("Cannot extract theme tint from an indexed color."); return _color.A/255.0; } diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index 51bfac7..3a3bc84 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -65,7 +65,7 @@ foreach (var cell in headersRow.Cells()) { var name = cell.GetString(); - if (XLHelper.IsNullOrWhiteSpace(name)) + if (String.IsNullOrWhiteSpace(name)) { name = "Column" + (cellPos + 1); cell.SetValue(name); @@ -395,7 +395,7 @@ Int32 co = 1; foreach (IXLCell c in range.Row(1).Cells()) { - if (XLHelper.IsNullOrWhiteSpace(((XLCell)c).InnerText)) + if (String.IsNullOrWhiteSpace(((XLCell)c).InnerText)) c.Value = GetUniqueName("Column" + co.ToInvariantString()); _uniqueNames.Add(c.GetString()); co++; @@ -424,6 +424,10 @@ public Int32 GetFieldIndex(String name) { + // There is a discrepancy in the way headers with line breaks are stored. + // The entry in the table definition will contain \r\n + // but the shared string value of the actual cell will contain only \n + name = name.Replace("\r\n", "\n"); if (FieldNames.ContainsKey(name)) return FieldNames[name].Index; @@ -445,7 +449,7 @@ Int32 co = 1; foreach (IXLCell c in headersRow.Cells()) { - if (XLHelper.IsNullOrWhiteSpace(((XLCell)c).InnerText)) + if (String.IsNullOrWhiteSpace(((XLCell)c).InnerText)) c.Value = GetUniqueName("Column" + co.ToInvariantString()); _uniqueNames.Add(c.GetString()); co++; diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index b204272..c4848c2 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -4,6 +4,7 @@ using System; using System.Collections.Generic; using System.Data; +using System.Globalization; using System.IO; using System.Linq; @@ -443,7 +444,7 @@ { checkForWorksheetsPresent(); if (_loadSource == XLLoadSource.New) - throw new Exception("This is a new file, please use one of the SaveAs methods."); + throw new InvalidOperationException("This is a new file, please use one of the SaveAs methods."); if (_loadSource == XLLoadSource.Stream) { @@ -503,7 +504,8 @@ private static SpreadsheetDocumentType GetSpreadsheetDocumentType(string filePath) { var extension = Path.GetExtension(filePath); - if (extension == null) throw new Exception("Empty extension is not supported."); + + if (extension == null) throw new ArgumentException("Empty extension is not supported."); extension = extension.Substring(1).ToLowerInvariant(); switch (extension) @@ -524,7 +526,7 @@ private void checkForWorksheetsPresent() { if (Worksheets.Count() == 0) - throw new Exception("Workbooks need at least one worksheet."); + throw new InvalidOperationException("Workbooks need at least one worksheet."); } /// @@ -651,6 +653,22 @@ return columns; } + /// + /// Searches the cells' contents for a given piece of text + /// + /// The search text. + /// The compare options. + /// if set to true search formulae instead of cell values. + /// + public IEnumerable Search(String searchText, CompareOptions compareOptions = CompareOptions.Ordinal, Boolean searchFormulae = false) + { + foreach (var ws in WorksheetsInternal) + { + foreach (var cell in ws.Search(searchText, compareOptions, searchFormulae)) + yield return cell; + } + } + #region Fields private readonly XLLoadSource _loadSource = XLLoadSource.New; diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 743d60b..4b5ab28 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -506,6 +506,14 @@ if (pivotTableDefinition.ShowError != null && pivotTableDefinition.ErrorCaption != null) pt.ErrorValueReplacement = pivotTableDefinition.ErrorCaption.Value; + // Subtotal configuration + if (pivotTableDefinition.PivotFields.Cast().All(pf => pf.SubtotalTop != null && pf.SubtotalTop.HasValue && pf.SubtotalTop.Value)) + pt.SetSubtotals(XLPivotSubtotals.AtTop); + else if (pivotTableDefinition.PivotFields.Cast().All(pf => pf.SubtotalTop != null && pf.SubtotalTop.HasValue && !pf.SubtotalTop.Value)) + pt.SetSubtotals(XLPivotSubtotals.AtBottom); + else + pt.SetSubtotals(XLPivotSubtotals.DoNotShow); + // Row labels if (pivotTableDefinition.RowFields != null) { @@ -731,7 +739,7 @@ if (shape != null) break; } - if (xdoc == null) throw new Exception("Could not load comments file"); + if (xdoc == null) throw new ArgumentException("Could not load comments file"); return xdoc; } @@ -1229,7 +1237,7 @@ } else if (cell.DataType == CellValues.SharedString) { - if (cell.CellValue != null && !XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) + if (cell.CellValue != null && !String.IsNullOrWhiteSpace(cell.CellValue.Text)) { var sharedString = sharedStrings[Int32.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture)]; ParseCellValue(sharedString, xlCell); @@ -1241,7 +1249,7 @@ } else if (cell.DataType == CellValues.Date) { - if (cell.CellValue != null && !XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) + if (cell.CellValue != null && !String.IsNullOrWhiteSpace(cell.CellValue.Text)) xlCell._cellValue = Double.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); xlCell._dataType = XLCellValues.DateTime; } @@ -1253,7 +1261,7 @@ } else if (cell.DataType == CellValues.Number) { - if (cell.CellValue != null && !XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) + if (cell.CellValue != null && !String.IsNullOrWhiteSpace(cell.CellValue.Text)) xlCell._cellValue = Double.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); if (s == null) @@ -1271,7 +1279,7 @@ else { var numberFormatId = ((CellFormat)(s.CellFormats).ElementAt(styleIndex)).NumberFormatId; - if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) + if (!String.IsNullOrWhiteSpace(cell.CellValue.Text)) xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToInvariantString(); if (s.NumberingFormats != null && @@ -1563,7 +1571,7 @@ return XLCellValues.Text; else { - if (!XLHelper.IsNullOrWhiteSpace(numberFormat.Format)) + if (!String.IsNullOrWhiteSpace(numberFormat.Format)) { var dataType = GetDataTypeFromFormat(numberFormat.Format); return dataType.HasValue ? dataType.Value : XLCellValues.Number; @@ -1778,7 +1786,7 @@ foreach (DataValidation dvs in dataValidations.Elements()) { String txt = dvs.SequenceOfReferences.InnerText; - if (XLHelper.IsNullOrWhiteSpace(txt)) continue; + if (String.IsNullOrWhiteSpace(txt)) continue; foreach (var dvt in txt.Split(' ').Select(rangeAddress => ws.Range(rangeAddress).DataValidation)) { if (dvs.AllowBlank != null) dvt.IgnoreBlanks = dvs.AllowBlank; @@ -1826,7 +1834,7 @@ if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.CellIs && fr.Operator != null) conditionalFormat.Operator = fr.Operator.Value.ToClosedXml(); - if (fr.Text != null && !XLHelper.IsNullOrWhiteSpace(fr.Text)) + if (fr.Text != null && !String.IsNullOrWhiteSpace(fr.Text)) conditionalFormat.Values.Add(GetFormula(fr.Text.Value)); if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.Top10) diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 5a00d0d..d3026f1 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -241,12 +241,12 @@ // Ensure all RelId's have been added to the context context.RelIdGenerator.AddValues(workbookPart.Parts.Select(p => p.RelationshipId), RelType.Workbook); - context.RelIdGenerator.AddValues(WorksheetsInternal.Cast().Where(ws => !XLHelper.IsNullOrWhiteSpace(ws.RelId)).Select(ws => ws.RelId), RelType.Workbook); - context.RelIdGenerator.AddValues(WorksheetsInternal.Cast().Where(ws => !XLHelper.IsNullOrWhiteSpace(ws.LegacyDrawingId)).Select(ws => ws.LegacyDrawingId), RelType.Workbook); + context.RelIdGenerator.AddValues(WorksheetsInternal.Cast().Where(ws => !String.IsNullOrWhiteSpace(ws.RelId)).Select(ws => ws.RelId), RelType.Workbook); + context.RelIdGenerator.AddValues(WorksheetsInternal.Cast().Where(ws => !String.IsNullOrWhiteSpace(ws.LegacyDrawingId)).Select(ws => ws.LegacyDrawingId), RelType.Workbook); context.RelIdGenerator.AddValues(WorksheetsInternal .Cast() .SelectMany(ws => ws.Tables.Cast()) - .Where(t => !XLHelper.IsNullOrWhiteSpace(t.RelId)) + .Where(t => !String.IsNullOrWhiteSpace(t.RelId)) .Select(t => t.RelId), RelType.Workbook); var extendedFilePropertiesPart = document.ExtendedFilePropertiesPart ?? @@ -303,7 +303,7 @@ var vmlDrawingPart = worksheetPart.VmlDrawingParts.FirstOrDefault(); if (vmlDrawingPart == null) { - if (XLHelper.IsNullOrWhiteSpace(worksheet.LegacyDrawingId)) + if (String.IsNullOrWhiteSpace(worksheet.LegacyDrawingId)) { worksheet.LegacyDrawingId = context.RelIdGenerator.GetNext(RelType.Workbook); worksheet.LegacyDrawingIsNew = true; @@ -473,7 +473,7 @@ if (Properties.Manager != null) { - if (!XLHelper.IsNullOrWhiteSpace(Properties.Manager)) + if (!String.IsNullOrWhiteSpace(Properties.Manager)) { if (properties.Manager == null) properties.Manager = new Manager(); @@ -486,7 +486,7 @@ if (Properties.Company == null) return; - if (!XLHelper.IsNullOrWhiteSpace(Properties.Company)) + if (!String.IsNullOrWhiteSpace(Properties.Company)) { if (properties.Company == null) properties.Company = new Company(); @@ -616,7 +616,7 @@ foreach (var xlSheet in WorksheetsInternal.Cast().OrderBy(w => w.Position)) { string rId; - if (xlSheet.SheetId == 0 && XLHelper.IsNullOrWhiteSpace(xlSheet.RelId)) + if (xlSheet.SheetId == 0 && String.IsNullOrWhiteSpace(xlSheet.RelId)) { rId = context.RelIdGenerator.GetNext(RelType.Workbook); @@ -628,7 +628,7 @@ } else { - if (XLHelper.IsNullOrWhiteSpace(xlSheet.RelId)) + if (String.IsNullOrWhiteSpace(xlSheet.RelId)) { rId = String.Format("rId{0}", xlSheet.SheetId); context.RelIdGenerator.AddValues(new List { rId }, RelType.Workbook); @@ -773,7 +773,7 @@ if (!nr.Visible) definedName.Hidden = BooleanValue.FromBoolean(true); - if (!XLHelper.IsNullOrWhiteSpace(nr.Comment)) + if (!String.IsNullOrWhiteSpace(nr.Comment)) definedName.Comment = nr.Comment; definedNames.AppendChild(definedName); } @@ -827,7 +827,7 @@ if (!nr.Visible) definedName.Hidden = BooleanValue.FromBoolean(true); - if (!XLHelper.IsNullOrWhiteSpace(nr.Comment)) + if (!String.IsNullOrWhiteSpace(nr.Comment)) definedName.Comment = nr.Comment; definedNames.AppendChild(definedName); } @@ -873,7 +873,7 @@ w.Internals.CellsCollection.GetCells( c => ((c.DataType == XLCellValues.Text && c.ShareString) || c.HasRichText) && (c as XLCell).InnerText.Length > 0 - && XLHelper.IsNullOrWhiteSpace(c.FormulaA1) + && String.IsNullOrWhiteSpace(c.FormulaA1) ))) { c.DataType = XLCellValues.Text; @@ -1019,7 +1019,7 @@ var cellsWithoutFormulas = new HashSet(); foreach (var c in worksheet.Internals.CellsCollection.GetCells()) { - if (XLHelper.IsNullOrWhiteSpace(c.FormulaA1)) + if (String.IsNullOrWhiteSpace(c.FormulaA1)) cellsWithoutFormulas.Add(c.Address.ToStringRelative()); else { @@ -1810,7 +1810,7 @@ tableColumn1.TotalsRowFormula = new TotalsRowFormula(xlField.TotalsRowFormulaA1); } - if (!XLHelper.IsNullOrWhiteSpace(xlField.TotalsRowLabel)) + if (!String.IsNullOrWhiteSpace(xlField.TotalsRowLabel)) tableColumn1.TotalsRowLabel = xlField.TotalsRowLabel; } tableColumns1.AppendChild(tableColumn1); @@ -1872,7 +1872,7 @@ var workbookCacheRelId = pt.WorkbookCacheRelId; PivotCache pivotCache; PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart; - if (!XLHelper.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) + if (!String.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) { pivotCache = pivotCaches.Cast().Single(pc => pc.Id.Value == pt.WorkbookCacheRelId); pivotTableCacheDefinitionPart = workbookPart.GetPartById(pt.WorkbookCacheRelId) as PivotTableCacheDefinitionPart; @@ -1886,18 +1886,18 @@ GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt); - if (XLHelper.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) + if (String.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) pivotCaches.AppendChild(pivotCache); PivotTablePart pivotTablePart; - if (XLHelper.IsNullOrWhiteSpace(pt.RelId)) + if (String.IsNullOrWhiteSpace(pt.RelId)) pivotTablePart = worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); else pivotTablePart = worksheetPart.GetPartById(pt.RelId) as PivotTablePart; GeneratePivotTablePartContent(pivotTablePart, pt, pivotCache.CacheId, context); - if (XLHelper.IsNullOrWhiteSpace(pt.RelId)) + if (String.IsNullOrWhiteSpace(pt.RelId)) pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook)); } } @@ -2112,6 +2112,21 @@ IXLPivotField labelField = null; var pf = new PivotField { ShowAll = false, Name = xlpf.CustomName }; + switch (pt.Subtotals) + { + case XLPivotSubtotals.DoNotShow: + pf.DefaultSubtotal = false; + break; + case XLPivotSubtotals.AtBottom: + pf.DefaultSubtotal = true; + pf.SubtotalTop = false; + break; + case XLPivotSubtotals.AtTop: + pf.DefaultSubtotal = true; + pf.SubtotalTop = true; + break; + } + if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName)) { labelField = pt.RowLabels.Single(p => p.SourceName == xlpf.SourceName); @@ -2135,7 +2150,7 @@ var fieldItems = new Items(); - if (xlpf.SharedStrings.Count > 0) + if (xlpf.SharedStrings.Any()) { for (uint i = 0; i < xlpf.SharedStrings.Count; i++) { @@ -2146,7 +2161,7 @@ } } - if (xlpf.Subtotals.Count > 0) + if (xlpf.Subtotals.Any()) { foreach (var subtotal in xlpf.Subtotals) { @@ -2211,13 +2226,17 @@ fieldItems.AppendChild(itemSubtotal); } } - else + // If the field itself doesn't have subtotals, but the pivot table is set to show pivot tables, add the default item + else if (pt.Subtotals != XLPivotSubtotals.DoNotShow) { fieldItems.AppendChild(new Item { ItemType = ItemValues.Default }); } - fieldItems.Count = Convert.ToUInt32(fieldItems.Count()); - pf.AppendChild(fieldItems); + if (fieldItems.Any()) + { + fieldItems.Count = Convert.ToUInt32(fieldItems.Count()); + pf.AppendChild(fieldItems); + } pivotFields.AppendChild(pf); } @@ -2494,7 +2513,7 @@ StrokeWeight = String.Format(CultureInfo.InvariantCulture, "{0}pt", c.Comment.Style.ColorsAndLines.LineWeight), InsetMode = c.Comment.Style.Margins.Automatic ? InsetMarginValues.Auto : InsetMarginValues.Custom }; - if (!XLHelper.IsNullOrWhiteSpace(c.Comment.Style.Web.AlternateText)) + if (!String.IsNullOrWhiteSpace(c.Comment.Style.Web.AlternateText)) shape.Alternate = c.Comment.Style.Web.AlternateText; return shape; @@ -2525,7 +2544,7 @@ // https://en.wikipedia.org/wiki/Office_Open_XML_file_formats#DrawingML private static Int64 ConvertToEnglishMetricUnits(Int32 pixels, Double resolution) { - return Convert.ToInt64(914400 * pixels / resolution); + return Convert.ToInt64(914400L * pixels / resolution); } private static void AddPictureAnchor(WorksheetPart worksheetPart, Drawings.IXLPicture picture, SaveContext context) @@ -2556,6 +2575,7 @@ using (var stream = new MemoryStream()) { + pic.ImageStream.Position = 0; pic.ImageStream.CopyTo(stream); stream.Seek(0, SeekOrigin.Begin); imagePart.FeedData(stream); @@ -3047,7 +3067,7 @@ { var differentialFormat = new DifferentialFormat(); differentialFormat.Append(GetNewFont(new FontInfo { Font = cf.Style.Font as XLFont }, false)); - if (!XLHelper.IsNullOrWhiteSpace(cf.Style.NumberFormat.Format)) + if (!String.IsNullOrWhiteSpace(cf.Style.NumberFormat.Format)) { var numberFormat = new NumberingFormat { @@ -3691,7 +3711,7 @@ { var newXLNumberFormat = new XLNumberFormat(); - if (nf.FormatCode != null && !XLHelper.IsNullOrWhiteSpace(nf.FormatCode.Value)) + if (nf.FormatCode != null && !String.IsNullOrWhiteSpace(nf.FormatCode.Value)) newXLNumberFormat.Format = nf.FormatCode.Value; else if (nf.NumberFormatId != null) newXLNumberFormat.NumberFormatId = (Int32)nf.NumberFormatId.Value; @@ -4300,7 +4320,7 @@ var protection = xlWorksheet.Protection; sheetProtection.Sheet = protection.Protected; - if (!XLHelper.IsNullOrWhiteSpace(protection.PasswordHash)) + if (!String.IsNullOrWhiteSpace(protection.PasswordHash)) sheetProtection.Password = protection.PasswordHash; sheetProtection.FormatCells = GetBooleanValue(!protection.FormatCells, true); sheetProtection.FormatColumns = GetBooleanValue(!protection.FormatColumns, true); @@ -4501,7 +4521,7 @@ Display = hl.Cell.GetFormattedString() }; } - if (!XLHelper.IsNullOrWhiteSpace(hl.Tooltip)) + if (!String.IsNullOrWhiteSpace(hl.Tooltip)) hyperlink.Tooltip = hl.Tooltip; hyperlinks.AppendChild(hyperlink); } @@ -4758,7 +4778,7 @@ { worksheetPart.Worksheet.RemoveAllChildren(); { - if (!XLHelper.IsNullOrWhiteSpace(xlWorksheet.LegacyDrawingId)) + if (!String.IsNullOrWhiteSpace(xlWorksheet.LegacyDrawingId)) { var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawing); worksheetPart.Worksheet.InsertAfter(new LegacyDrawing { Id = xlWorksheet.LegacyDrawingId }, @@ -4838,7 +4858,7 @@ } else if (dataType == XLCellValues.DateTime || dataType == XLCellValues.Number) { - if (!XLHelper.IsNullOrWhiteSpace(xlCell.InnerText)) + if (!String.IsNullOrWhiteSpace(xlCell.InnerText)) { var cellValue = new CellValue(); cellValue.Text = Double.Parse(xlCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index c28ef98..033fd27 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -176,7 +176,7 @@ throw new ArgumentException("Worksheet names cannot contain any of the following characters: " + InvalidNameChars); - if (XLHelper.IsNullOrWhiteSpace(value)) + if (String.IsNullOrWhiteSpace(value)) throw new ArgumentException("Worksheet names cannot be empty"); if (value.Length > 31) @@ -651,7 +651,7 @@ private String ReplaceRelativeSheet(string newSheetName, String value) { - if (XLHelper.IsNullOrWhiteSpace(value)) return value; + if (String.IsNullOrWhiteSpace(value)) return value; var newValue = new StringBuilder(); var addresses = value.Split(','); diff --git a/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/Excel/XLWorksheets.cs index 4d14cb7..e3f42b7 100644 --- a/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/Excel/XLWorksheets.cs @@ -77,18 +77,18 @@ if (wss.Any()) return wss.First().Value; - throw new Exception("There isn't a worksheet named '" + sheetName + "'."); + throw new ArgumentException("There isn't a worksheet named '" + sheetName + "'."); } public IXLWorksheet Worksheet(Int32 position) { int wsCount = _worksheets.Values.Count(w => w.Position == position); if (wsCount == 0) - throw new Exception("There isn't a worksheet associated with that position."); + throw new ArgumentException("There isn't a worksheet associated with that position."); if (wsCount > 1) { - throw new Exception( + throw new ArgumentException( "Can't retrieve a worksheet because there are multiple worksheets associated with that position."); } @@ -130,14 +130,14 @@ { int wsCount = _worksheets.Values.Count(w => w.Position == position); if (wsCount == 0) - throw new Exception("There isn't a worksheet associated with that index."); + throw new ArgumentException("There isn't a worksheet associated with that index."); if (wsCount > 1) - throw new Exception( + throw new ArgumentException( "Can't delete the worksheet because there are multiple worksheets associated with that index."); var ws = _worksheets.Values.Single(w => w.Position == position); - if (!XLHelper.IsNullOrWhiteSpace(ws.RelId) && !Deleted.Contains(ws.RelId)) + if (!String.IsNullOrWhiteSpace(ws.RelId) && !Deleted.Contains(ws.RelId)) Deleted.Add(ws.RelId); _worksheets.RemoveAll(w => w.Position == position); @@ -178,7 +178,7 @@ public void Rename(String oldSheetName, String newSheetName) { - if (XLHelper.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName)) return; + if (String.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName)) return; if (_worksheets.Any(ws1 => ws1.Key.Equals(newSheetName, StringComparison.OrdinalIgnoreCase))) throw new ArgumentException(String.Format("A worksheet with the same name ({0}) has already been added.", newSheetName), nameof(newSheetName)); diff --git a/ClosedXML/Extensions.cs b/ClosedXML/Extensions.cs index 7655168..699d251 100644 --- a/ClosedXML/Extensions.cs +++ b/ClosedXML/Extensions.cs @@ -221,7 +221,7 @@ public static Double GetWidth(this IXLFontBase fontBase, String text, Dictionary fontCache) { - if (XLHelper.IsNullOrWhiteSpace(text)) + if (String.IsNullOrWhiteSpace(text)) return 0; var font = GetCachedFont(fontBase, fontCache); diff --git a/ClosedXML/XLHelper.cs b/ClosedXML/XLHelper.cs index 585b8ca..dd08d44 100644 --- a/ClosedXML/XLHelper.cs +++ b/ClosedXML/XLHelper.cs @@ -117,7 +117,7 @@ public static bool IsValidColumn(string column) { var length = column.Length; - if (IsNullOrWhiteSpace(column) || length > 3) + if (String.IsNullOrWhiteSpace(column) || length > 3) return false; var theColumn = column.ToUpper(); @@ -153,7 +153,7 @@ public static bool IsValidA1Address(string address) { - if (IsNullOrWhiteSpace(address)) + if (String.IsNullOrWhiteSpace(address)) return false; address = address.Replace("$", ""); @@ -226,26 +226,6 @@ return rows; } - public static bool IsNullOrWhiteSpace(string value) - { -#if NET4 - return String.IsNullOrWhiteSpace(value); -#else - if (value != null) - { - var length = value.Length; - for (int i = 0; i < length; i++) - { - if (!char.IsWhiteSpace(value[i])) - { - return false; - } - } - } - return true; -#endif - } - private static readonly Regex A1RegexRelative = new Regex( @"(?<=\W)(?\$?[a-zA-Z]{1,3}\$?\d{1,7})(?=\W)" // A1 + @"|(?<=\W)(?\$?\d{1,7}:\$?\d{1,7})(?=\W)" // 1:1 diff --git a/ClosedXML_Examples/Misc/InsertingData.cs b/ClosedXML_Examples/Misc/InsertingData.cs index d0ade19..d1035d5 100644 --- a/ClosedXML_Examples/Misc/InsertingData.cs +++ b/ClosedXML_Examples/Misc/InsertingData.cs @@ -13,59 +13,65 @@ // Public public void Create(String filePath) { - var wb = new XLWorkbook(); - var ws = wb.Worksheets.Add("Inserting Data"); + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Inserting Data"); - // From a list of strings - var listOfStrings = new List(); - listOfStrings.Add("House"); - listOfStrings.Add("001"); - ws.Cell(1, 1).Value = "From Strings"; - ws.Cell(1, 1).AsRange().AddToNamed("Titles"); - ws.Cell(2, 1).InsertData(listOfStrings); + // From a list of strings + var listOfStrings = new List(); + listOfStrings.Add("House"); + listOfStrings.Add("001"); + ws.Cell(1, 1).Value = "From Strings"; + ws.Cell(1, 1).AsRange().AddToNamed("Titles"); + ws.Cell(2, 1).InsertData(listOfStrings); - // From a list of arrays - var listOfArr = new List(); - listOfArr.Add(new Int32[] { 1, 2, 3 }); - listOfArr.Add(new Int32[] { 1 }); - listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 }); - ws.Cell(1, 3).Value = "From Arrays"; - ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles"); - ws.Cell(2, 3).InsertData(listOfArr); + // From a list of arrays + var listOfArr = new List(); + listOfArr.Add(new Int32[] { 1, 2, 3 }); + listOfArr.Add(new Int32[] { 1 }); + listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 }); + ws.Cell(1, 3).Value = "From Arrays"; + ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles"); + ws.Cell(2, 3).InsertData(listOfArr); - // From a DataTable - var dataTable = GetTable(); - ws.Cell(6, 1).Value = "From DataTable"; - ws.Range(6, 1, 6, 4).Merge().AddToNamed("Titles"); - ws.Cell(7, 1).InsertData(dataTable.AsEnumerable()); + // From a DataTable + var dataTable = GetTable(); + ws.Cell(6, 1).Value = "From DataTable"; + ws.Range(6, 1, 6, 4).Merge().AddToNamed("Titles"); + ws.Cell(7, 1).InsertData(dataTable.AsEnumerable()); - // From a query - var list = new List(); - list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St." }); - list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St." }); - list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St." }); - list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." }); + // From a query + var list = new List(); + list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St." }); + list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St." }); + list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St." }); + list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." }); - var people = from p in list - where p.Age >= 21 - select new { p.Name, p.House, p.Age }; + var people = from p in list + where p.Age >= 21 + select new { p.Name, p.House, p.Age }; - ws.Cell(6, 6).Value = "From Query"; - ws.Range(6, 6, 6, 8).Merge().AddToNamed("Titles"); - ws.Cell(7, 6).InsertData(people.AsEnumerable()); + ws.Cell(6, 6).Value = "From Query"; + ws.Range(6, 6, 6, 8).Merge().AddToNamed("Titles"); + ws.Cell(7, 6).InsertData(people.AsEnumerable()); - // Prepare the style for the titles - var titlesStyle = wb.Style; - titlesStyle.Font.Bold = true; - titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; - titlesStyle.Fill.BackgroundColor = XLColor.Cyan; - - // Format all titles in one shot - wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle; + ws.Cell("F13").Value = "Transposed"; + ws.Range(13, 6, 13, 8).Merge().AddToNamed("Titles"); + ws.Cell("F14").InsertData(people.AsEnumerable(), true); - ws.Columns().AdjustToContents(); + // Prepare the style for the titles + var titlesStyle = wb.Style; + titlesStyle.Font.Bold = true; + titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + titlesStyle.Fill.BackgroundColor = XLColor.Cyan; - wb.SaveAs(filePath); + // Format all titles in one shot + wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle; + + ws.Columns().AdjustToContents(); + + wb.SaveAs(filePath); + } } class Person diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index 9eb24ea..fa479da 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -60,6 +60,8 @@ IXLWorksheet ptSheet; IXLPivotTable pt; + #region Pivots + for (int i = 1; i <= 3; i++) { // Add a new sheet for our pivot table @@ -95,7 +97,10 @@ ptSheet.Columns().AdjustToContents(); } - // Different kind of pivot + #endregion Pivots + + #region Different kind of pivot + ptSheet = wb.Worksheets.Add("pvtNoColumnLabels"); pt = ptSheet.PivotTables.AddNew("pvtNoColumnLabels", ptSheet.Cell(1, 1), dataRange); @@ -105,8 +110,10 @@ pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum); + #endregion Different kind of pivot - // Pivot table with collapsed fields + #region Pivot table with collapsed fields + ptSheet = wb.Worksheets.Add("pvtCollapsedFields"); pt = ptSheet.PivotTables.AddNew("pvtCollapsedFields", ptSheet.Cell(1, 1), dataRange); @@ -116,8 +123,10 @@ pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum); + #endregion Pivot table with collapsed fields - // Pivot table with a field both as a value and as a row/column/filter label + #region Pivot table with a field both as a value and as a row/column/filter label + ptSheet = wb.Worksheets.Add("pvtFieldAsValueAndLabel"); pt = ptSheet.PivotTables.AddNew("pvtFieldAsValueAndLabel", ptSheet.Cell(1, 1), dataRange); @@ -126,6 +135,37 @@ pt.Values.Add("Name").SetSummaryFormula(XLPivotSummary.Count);//.NumberFormat.Format = "#0.00"; + #endregion Pivot table with a field both as a value and as a row/column/filter label + + #region Pivot table with subtotals disabled + + ptSheet = wb.Worksheets.Add("pvtHideSubTotals"); + + // Create the pivot table, using the data from the "PastrySalesData" table + pt = ptSheet.PivotTables.AddNew("pvtHidesubTotals", ptSheet.Cell(1, 1), dataRange); + + // The rows in our pivot table will be the names of the pastries + pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + + // The columns will be the months + pt.ColumnLabels.Add("Month"); + pt.ColumnLabels.Add("Name"); + + // 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%"; + + pt.Values.Add("Quality", "Sum of Quality") + .NumberFormat.SetFormat("#,##0.00"); + + pt.Subtotals = XLPivotSubtotals.DoNotShow; + + ptSheet.Columns().AdjustToContents(); + + #endregion Pivot table with subtotals disabled + wb.SaveAs(filePath); } } diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 18441ef..d1ba111 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -84,6 +84,7 @@ + @@ -286,6 +287,7 @@ + diff --git a/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs b/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs index 3fca48e..3ad7f8e 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs @@ -620,7 +620,7 @@ Assert.AreEqual(0, cell.Value); cell = wb.Worksheet(1).Cell(3, 1).SetFormulaA1("=SUM(D1,D2)"); Assert.AreEqual(0, cell.Value); - Assert.That(() => wb.Worksheet(1).Cell(3, 1).SetFormulaA1("=AVERAGE(D1,D2)").Value, Throws.Exception); + Assert.That(() => wb.Worksheet(1).Cell(3, 1).SetFormulaA1("=AVERAGE(D1,D2)").Value, Throws.TypeOf()); } [Test] diff --git a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs index a112f3d..60427e6 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs @@ -116,11 +116,11 @@ [Test] public void Vlookup_Exceptions() { - Assert.That(() => workbook.Evaluate(@"=VLOOKUP("""",Data!$B$2:$I$71,3,FALSE)"), Throws.Exception); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(50,Data!$B$2:$I$71,3,FALSE)"), Throws.Exception); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$B$2:$I$71,9,FALSE)"), Throws.Exception); + Assert.That(() => workbook.Evaluate(@"=VLOOKUP("""",Data!$B$2:$I$71,3,FALSE)"), Throws.TypeOf()); + Assert.That(() => workbook.Evaluate(@"=VLOOKUP(50,Data!$B$2:$I$71,3,FALSE)"), Throws.TypeOf()); + Assert.That(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$B$2:$I$71,9,FALSE)"), Throws.TypeOf()); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(-1,Data!$B$2:$I$71,9,TRUE)"), Throws.Exception); + Assert.That(() => workbook.Evaluate(@"=VLOOKUP(-1,Data!$B$2:$I$71,9,TRUE)"), Throws.TypeOf()); } } } diff --git a/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs index c59e34d..2550868 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs @@ -22,7 +22,7 @@ value = ws.Evaluate("AVERAGE(G3:G45)").CastTo(); Assert.AreEqual(49.3255814, value, tolerance); - Assert.That(() => ws.Evaluate("AVERAGE(D3:D45)"), Throws.Exception); + Assert.That(() => ws.Evaluate("AVERAGE(D3:D45)"), Throws.TypeOf()); } [Test] @@ -146,7 +146,7 @@ { var ws = workbook.Worksheets.First(); double value; - Assert.That(() => ws.Evaluate(@"=STDEV(D3:D45)"), Throws.Exception); + Assert.That(() => ws.Evaluate(@"=STDEV(D3:D45)"), Throws.TypeOf()); value = ws.Evaluate(@"=STDEV(H3:H45)").CastTo(); Assert.AreEqual(47.34511769, value, tolerance); @@ -163,7 +163,7 @@ { var ws = workbook.Worksheets.First(); double value; - Assert.That(() => ws.Evaluate(@"=STDEVP(D3:D45)"), Throws.Exception); + Assert.That(() => ws.Evaluate(@"=STDEVP(D3:D45)"), Throws.InvalidOperationException); value = ws.Evaluate(@"=STDEVP(H3:H45)").CastTo(); Assert.AreEqual(46.79135458, value, tolerance); @@ -180,7 +180,7 @@ { var ws = workbook.Worksheets.First(); double value; - Assert.That(() => ws.Evaluate(@"=VAR(D3:D45)"), Throws.Exception); + Assert.That(() => ws.Evaluate(@"=VAR(D3:D45)"), Throws.InvalidOperationException); value = ws.Evaluate(@"=VAR(H3:H45)").CastTo(); Assert.AreEqual(2241.560169, value, tolerance); @@ -197,7 +197,7 @@ { var ws = workbook.Worksheets.First(); double value; - Assert.That(() => ws.Evaluate(@"=VARP(D3:D45)"), Throws.Exception); + Assert.That(() => ws.Evaluate(@"=VARP(D3:D45)"), Throws.InvalidOperationException); value = ws.Evaluate(@"=VARP(H3:H45)").CastTo(); Assert.AreEqual(2189.430863, value, tolerance); diff --git a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs index 3a89de2..efb58d3 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs @@ -1,4 +1,5 @@ using ClosedXML.Excel; +using ClosedXML.Excel.CalcEngine; using NUnit.Framework; using System; using System.Globalization; @@ -19,13 +20,13 @@ [Test] public void Char_Empty_Input_String() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Char("""")"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Char("""")"), Throws.TypeOf()); } [Test] public void Char_Input_Too_Large() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Char(9797)"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Char(9797)"), Throws.TypeOf()); } [Test] @@ -56,7 +57,7 @@ public void Code_Empty_Input_String() { // Todo: more specific exception - ValueException? - Assert.That(() => XLWorkbook.EvaluateExpr(@"Code("""")"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Code("""")"), Throws.TypeOf()); } [Test] @@ -82,7 +83,7 @@ [Test] public void Dollar_Empty_Input_String() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Dollar("", 3)"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Dollar("", 3)"), Throws.TypeOf()); } [Test] @@ -121,26 +122,26 @@ [Test] public void Find_Start_Position_Too_Large() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Find(""abc"", ""abcdef"", 10)"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Find(""abc"", ""abcdef"", 10)"), Throws.TypeOf()); } [Test] public void Find_String_In_Another_Empty_String() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Find(""abc"", """")"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Find(""abc"", """")"), Throws.TypeOf()); } [Test] public void Find_String_Not_Found() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Find(""123"", ""asdf"")"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Find(""123"", ""asdf"")"), Throws.TypeOf()); } [Test] public void Find_Case_Sensitive_String_Not_Found() { // Find is case-sensitive - Assert.That(() => XLWorkbook.EvaluateExpr(@"Find(""excel"", ""Microsoft Excel 2010"")"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Find(""excel"", ""Microsoft Excel 2010"")"), Throws.TypeOf()); } [Test] @@ -159,7 +160,7 @@ [Test] public void Fixed_Input_Is_String() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Fixed(""asdf"")"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Fixed(""asdf"")"), Throws.TypeOf()); } [Test] @@ -297,7 +298,7 @@ [Test] public void Rept_Start_Is_Negative() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Rept(""Francois"", -1)"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Rept(""Francois"", -1)"), Throws.TypeOf()); } [Test] @@ -344,7 +345,7 @@ [Test] public void Search_No_Parameters_With_Values() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Search("""", """")"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Search("""", """")"), Throws.TypeOf()); } [Test] @@ -357,31 +358,31 @@ [Test] public void Search_Start_Position_Too_Large() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Search(""abc"", ""abcdef"", 10)"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Search(""abc"", ""abcdef"", 10)"), Throws.TypeOf()); } [Test] public void Search_Empty_Input_String() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Search(""abc"", """")"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Search(""abc"", """")"), Throws.TypeOf()); } [Test] public void Search_String_Not_Found() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Search(""123"", ""asdf"")"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Search(""123"", ""asdf"")"), Throws.TypeOf()); } [Test] public void Search_Wildcard_String_Not_Found() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Search(""soft?2010"", ""Microsoft Excel 2010"")"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Search(""soft?2010"", ""Microsoft Excel 2010"")"), Throws.TypeOf()); } [Test] public void Search_Start_Position_Too_Large2() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Search(""text"", ""This is some text"", 15)"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Search(""text"", ""This is some text"", 15)"), Throws.TypeOf()); } // http://www.excel-easy.com/examples/find-vs-search.html @@ -517,7 +518,7 @@ [Test] public void Value_Input_String_Is_Not_A_Number() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Value(""asdf"")"), Throws.Exception); + Assert.That(() => XLWorkbook.EvaluateExpr(@"Value(""asdf"")"), Throws.TypeOf()); } [Test] diff --git a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs index 31e66f9..9620511 100644 --- a/ClosedXML_Tests/Excel/Cells/XLCellTests.cs +++ b/ClosedXML_Tests/Excel/Cells/XLCellTests.cs @@ -83,6 +83,22 @@ } [Test] + public void InsertData2() + { + IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); + IXLRange range = ws.Cell(2, 2).InsertData(new[] { "a", "b", "c" }, false); + Assert.AreEqual("Sheet1!B2:B4", range.ToString()); + } + + [Test] + public void InsertData3() + { + IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); + IXLRange range = ws.Cell(2, 2).InsertData(new[] { "a", "b", "c" }, true); + Assert.AreEqual("Sheet1!B2:D2", range.ToString()); + } + + [Test] public void IsEmpty1() { IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index 1aeba43..a6b3312 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -29,7 +29,8 @@ @"Misc\InvalidPrintTitles.xlsx", @"Misc\ExcelProducedWorkbookWithImages.xlsx", @"Misc\EmptyCellValue.xlsx", - @"Misc\AllShapes.xlsx" + @"Misc\AllShapes.xlsx", + @"Misc\TableHeadersWithLineBreaks.xlsx" }; foreach (var file in files) diff --git a/ClosedXML_Tests/Excel/Misc/SearchTests.cs b/ClosedXML_Tests/Excel/Misc/SearchTests.cs new file mode 100644 index 0000000..0da76e2 --- /dev/null +++ b/ClosedXML_Tests/Excel/Misc/SearchTests.cs @@ -0,0 +1,78 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System.Globalization; +using System.Linq; + +namespace ClosedXML_Tests.Excel.Misc +{ + [TestFixture] + public class SearchTests + { + [Test] + public void TestSearch() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Examples\Misc\CellValues.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + + IXLCells foundCells; + + foundCells = ws.Search("Initial Value"); + Assert.AreEqual(1, foundCells.Count()); + Assert.AreEqual("B2", foundCells.Single().Address.ToString()); + Assert.AreEqual("Initial Value", foundCells.Single().GetString()); + + foundCells = ws.Search("Using"); + Assert.AreEqual(2, foundCells.Count()); + Assert.AreEqual("D2", foundCells.First().Address.ToString()); + Assert.AreEqual("Using Get...()", foundCells.First().GetString()); + Assert.AreEqual(2, foundCells.Count()); + Assert.AreEqual("E2", foundCells.Last().Address.ToString()); + Assert.AreEqual("Using GetValue()", foundCells.Last().GetString()); + + foundCells = ws.Search("1234"); + Assert.AreEqual(4, foundCells.Count()); + Assert.AreEqual("C5,D5,E5,F5", string.Join(",", foundCells.Select(c => c.Address.ToString()).ToArray())); + + foundCells = ws.Search("Sep"); + Assert.AreEqual(2, foundCells.Count()); + Assert.AreEqual("B3,G3", string.Join(",", foundCells.Select(c => c.Address.ToString()).ToArray())); + + foundCells = ws.Search("1234", CompareOptions.Ordinal, true); + Assert.AreEqual(5, foundCells.Count()); + Assert.AreEqual("B5,C5,D5,E5,F5", string.Join(",", foundCells.Select(c => c.Address.ToString()).ToArray())); + + foundCells = ws.Search("test case", CompareOptions.Ordinal); + Assert.AreEqual(0, foundCells.Count()); + + foundCells = ws.Search("test case", CompareOptions.OrdinalIgnoreCase); + Assert.AreEqual(6, foundCells.Count()); + } + } + + [Test] + public void TestSearch2() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Examples\Misc\Formulas.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + + IXLCells foundCells; + + foundCells = ws.Search("3", CompareOptions.Ordinal); + Assert.AreEqual(10, foundCells.Count()); + Assert.AreEqual("C2", foundCells.First().Address.ToString()); + + foundCells = ws.Search("A2", CompareOptions.Ordinal, true); + Assert.AreEqual(4, foundCells.Count()); + Assert.AreEqual("C2,D2,B6,A11", string.Join(",", foundCells.Select(c => c.Address.ToString()).ToArray())); + + foundCells = ws.Search("RC", CompareOptions.Ordinal, true); + Assert.AreEqual(3, foundCells.Count()); + Assert.AreEqual("E2,E3,E4", string.Join(",", foundCells.Select(c => c.Address.ToString()).ToArray())); + } + } + } +} diff --git a/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs index 3c94664..326340b 100644 --- a/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs +++ b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs @@ -14,7 +14,6 @@ using (var wb = new XLWorkbook()) { var ws = wb.AddWorksheet("Sheet1"); - ws.Column(1).Style.NumberFormat.Format = "yy-MM-dd"; var table = new DataTable(); table.Columns.Add("Date", typeof(DateTime)); @@ -24,9 +23,13 @@ table.Rows.Add(new DateTime(2017, 1, 1).AddMonths(i)); } + ws.Column(1).Style.NumberFormat.Format = "yy-MM-dd"; ws.Cell("A1").InsertData(table.AsEnumerable()); - Assert.AreEqual("yy-MM-dd", ws.Cell("A5").Style.DateFormat.Format); + + ws.Row(1).Style.NumberFormat.Format = "yy-MM-dd"; + ws.Cell("A1").InsertData(table.AsEnumerable(), true); + Assert.AreEqual("yy-MM-dd", ws.Cell("E1").Style.DateFormat.Format); } } } diff --git a/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx index 9e9d20f..c8ae77a 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index 6e3156d..7c0b68e 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/TableHeadersWithLineBreaks.xlsx b/ClosedXML_Tests/Resource/Misc/TableHeadersWithLineBreaks.xlsx new file mode 100644 index 0000000..8e7dbdc --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/TableHeadersWithLineBreaks.xlsx Binary files differ diff --git a/ClosedXML_Tests/packages.config b/ClosedXML_Tests/packages.config index c32aa84..5e0ab7c 100644 --- a/ClosedXML_Tests/packages.config +++ b/ClosedXML_Tests/packages.config @@ -1,5 +1,5 @@ - - - - + + + + \ No newline at end of file