diff --git a/.github/ISSUE_TEMPLATE.md b/.github/ISSUE_TEMPLATE.md index 984600d..5b1b779 100644 --- a/.github/ISSUE_TEMPLATE.md +++ b/.github/ISSUE_TEMPLATE.md @@ -1,3 +1,5 @@ +## Read and complete the full issue template + **Do you want to request a *feature* or report a *bug*?** - [x] Bug - [ ] Feature @@ -8,20 +10,26 @@ **What is the current behavior?** -**If the current behavior is a bug, please provide the steps to reproduce and -if possible a minimal demo of the problem with a sample spreadsheet.** +Complete this. **What is the expected behavior or new feature?** +Complete this. + **Did this work in previous versions of our tool? Which versions?** -- [ ] I attached a sample spreadsheet. (You can drag files on to this issue) +Yes/No/v0.XX + +## Reproducibility +**This is an important section. Read it carefully. Failure to do so will cause a 'RTFM' comment.** + +Without a code sample, it is unlikely that your issue will get attention. Don't be lazy. Do the effort and assist the developers to reproduce your problem. Code samples should be [minimal complete and verifiable](https://stackoverflow.com/help/mcve). Sample spreadsheets should be attached whenever applicable. Remove sensitive information. **Code to reproduce problem:** ```c# public void Main() { - // Where possible, post full code to reproduce your issue that adheres to: + // Code standards: // - Fully runnable. I should be able to copy and paste this code into a // console application and run it without having to edit it much. // - Declare all your variables (this follows from the previous point) @@ -30,3 +38,4 @@ // post your full application. } ``` +- [ ] I attached a sample spreadsheet. (You can drag files on to this issue) diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index 4bc2ce0..4d4338c 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -65,6 +65,14 @@ + + + + + + + + diff --git a/ClosedXML/Excel/CalcEngine/CalcEngine.cs b/ClosedXML/Excel/CalcEngine/CalcEngine.cs index 8cc752e..c1a1aa1 100644 --- a/ClosedXML/Excel/CalcEngine/CalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/CalcEngine.cs @@ -253,6 +253,18 @@ #region ** token/keyword tables + private static readonly IDictionary ErrorMap = new Dictionary() + { + ["#REF!"] = ErrorExpression.ExpressionErrorType.CellReference, + ["#VALUE!"] = ErrorExpression.ExpressionErrorType.CellValue, + ["#DIV/0!"] = ErrorExpression.ExpressionErrorType.DivisionByZero, + ["#NAME?"] = ErrorExpression.ExpressionErrorType.NameNotRecognized, + ["#N/A"] = ErrorExpression.ExpressionErrorType.NoValueAvailable, + ["#NULL!"] = ErrorExpression.ExpressionErrorType.NullValue, + ["#NUM!"] = ErrorExpression.ExpressionErrorType.NumberInvalid + }; + + // build/get static token table private Dictionary GetSymbolTable() { @@ -463,6 +475,10 @@ } break; + + case TKTYPE.ERROR: + x = new ErrorExpression((ErrorExpression.ExpressionErrorType)_token.Value); + break; } // make sure we got something... @@ -663,26 +679,12 @@ return; } - // parse dates (review) - if (c == '#') + // parse #REF! (and other errors) in formula + if (c == '#' && ErrorMap.Any(pair => _len > _ptr+pair.Key.Length && _expr.Substring(_ptr, pair.Key.Length).Equals(pair.Key, StringComparison.OrdinalIgnoreCase))) { - // look for end # - for (i = 1; i + _ptr < _len; i++) - { - c = _expr[_ptr + i]; - if (c == '#') break; - } - - // check that we got the end of the date - if (c != '#') - { - Throw("Can't find final date delimiter ('#')."); - } - - // end of date - var lit = _expr.Substring(_ptr + 1, i - 1); - _ptr += i + 1; - _token = new Token(DateTime.Parse(lit, _ci), TKID.ATOM, TKTYPE.LITERAL); + var errorPair = ErrorMap.Single(pair => _len > _ptr + pair.Key.Length && _expr.Substring(_ptr, pair.Key.Length).Equals(pair.Key, StringComparison.OrdinalIgnoreCase)); + _ptr += errorPair.Key.Length; + _token = new Token(errorPair.Value, TKID.ATOM, TKTYPE.ERROR); return; } diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/CalcEngineException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/CalcEngineException.cs new file mode 100644 index 0000000..962b1c6 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/CalcEngineException.cs @@ -0,0 +1,18 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + internal abstract class CalcEngineException : ArgumentException + { + protected CalcEngineException() + : base() + { } + protected CalcEngineException(string message) + : base(message) + { } + + protected CalcEngineException(string message, Exception innerException) + : base(message, innerException) + { } + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/CellReferenceException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/CellReferenceException.cs new file mode 100644 index 0000000..f0fd88c --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/CellReferenceException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// This error occurs when you delete a cell referred to in the + /// formula or if you paste cells over the ones referred to in the + /// formula. + /// Corresponds to the #REF! error in Excel + /// + /// + internal class CellReferenceException : CalcEngineException + { + public CellReferenceException() + : base() + { } + + public CellReferenceException(string message) + : base(message) + { } + + public CellReferenceException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/CellValueException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/CellValueException.cs new file mode 100644 index 0000000..0716353 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/CellValueException.cs @@ -0,0 +1,26 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// This error is most often the result of specifying a + /// mathematical operation with one or more cells that contain + /// text. + /// Corresponds to the #VALUE! error in Excel + /// + /// + internal class CellValueException : CalcEngineException + { + public CellValueException() + : base() + { } + + public CellValueException(string message) + : base(message) + { } + + public CellValueException(string message, Exception innerException) + : base(message, innerException) + { } + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/DivisionByZeroException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/DivisionByZeroException.cs new file mode 100644 index 0000000..53e2ed1 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/DivisionByZeroException.cs @@ -0,0 +1,26 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// The division operation in your formula refers to a cell that + /// contains the value 0 or is blank. + /// Corresponds to the #DIV/0! error in Excel + /// + /// + internal class DivisionByZeroException : CalcEngineException + { + public DivisionByZeroException() + : base() + { } + + public DivisionByZeroException(string message) + : base(message) + { } + + public DivisionByZeroException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} \ No newline at end of file diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/NameNotRecognizedException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/NameNotRecognizedException.cs new file mode 100644 index 0000000..0f51e56 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/NameNotRecognizedException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// This error value appears when you incorrectly type the range + /// name, refer to a deleted range name, or forget to put quotation + /// marks around a text string in a formula. + /// Corresponds to the #NAME? error in Excel + /// + /// + internal class NameNotRecognizedException : CalcEngineException + { + public NameNotRecognizedException() + : base() + { } + + public NameNotRecognizedException(string message) + : base(message) + { } + + public NameNotRecognizedException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/NoValueAvailableException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/NoValueAvailableException.cs new file mode 100644 index 0000000..0e97fe5 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/NoValueAvailableException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// Technically, this is not an error value but a special value + /// that you can manually enter into a cell to indicate that you + /// don’t yet have a necessary value. + /// Corresponds to the #N/A error in Excel. + /// + /// + internal class NoValueAvailableException : CalcEngineException + { + public NoValueAvailableException() + : base() + { } + + public NoValueAvailableException(string message) + : base(message) + { } + + public NoValueAvailableException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} \ No newline at end of file diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/NullValueException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/NullValueException.cs new file mode 100644 index 0000000..d3153d7 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/NullValueException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// Because a space indicates an intersection, this error will + /// occur if you insert a space instead of a comma(the union operator) + /// between ranges used in function arguments. + /// Corresponds to the #NULL! error in Excel + /// + /// + internal class NullValueException : CalcEngineException + { + public NullValueException() + : base() + { } + + public NullValueException(string message) + : base(message) + { } + + public NullValueException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} diff --git a/ClosedXML/Excel/CalcEngine/Exceptions/NumberException.cs b/ClosedXML/Excel/CalcEngine/Exceptions/NumberException.cs new file mode 100644 index 0000000..4ce87d0 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/Exceptions/NumberException.cs @@ -0,0 +1,27 @@ +using System; + +namespace ClosedXML.Excel.CalcEngine.Exceptions +{ + /// + /// This error can be caused by an invalid argument in an Excel + /// function or a formula that produces a number too large or too small + /// to be represented in the worksheet. + /// Corresponds to the #NUM! error in Excel + /// + /// + internal class NumberException : CalcEngineException + { + public NumberException() + : base() + { } + + public NumberException(string message) + : base(message) + { } + + public NumberException(string message, Exception innerException) + : base(message, innerException) + { } + + } +} diff --git a/ClosedXML/Excel/CalcEngine/Expression.cs b/ClosedXML/Excel/CalcEngine/Expression.cs index 9dfdf28..b9a29ca 100644 --- a/ClosedXML/Excel/CalcEngine/Expression.cs +++ b/ClosedXML/Excel/CalcEngine/Expression.cs @@ -1,3 +1,4 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; using System.Collections; using System.Collections.Generic; @@ -79,12 +80,18 @@ public static implicit operator string(Expression x) { + if (x is ErrorExpression) + (x as ErrorExpression).ThrowApplicableException(); + var v = x.Evaluate(); return v == null ? string.Empty : v.ToString(); } public static implicit operator double(Expression x) { + if (x is ErrorExpression) + (x as ErrorExpression).ThrowApplicableException(); + // evaluate var v = x.Evaluate(); @@ -119,6 +126,9 @@ public static implicit operator bool(Expression x) { + if (x is ErrorExpression) + (x as ErrorExpression).ThrowApplicableException(); + // evaluate var v = x.Evaluate(); @@ -146,6 +156,9 @@ public static implicit operator DateTime(Expression x) { + if (x is ErrorExpression) + (x as ErrorExpression).ThrowApplicableException(); + // evaluate var v = x.Evaluate(); @@ -490,6 +503,52 @@ } } + internal class ErrorExpression : Expression + { + internal enum ExpressionErrorType + { + CellReference, + CellValue, + DivisionByZero, + NameNotRecognized, + NoValueAvailable, + NullValue, + NumberInvalid + } + + internal ErrorExpression(ExpressionErrorType eet) + : base(new Token(eet, TKID.ATOM, TKTYPE.ERROR)) + { } + + public override object Evaluate() + { + return this._token.Value; + } + + public void ThrowApplicableException() + { + var eet = (ExpressionErrorType)_token.Value; + switch (eet) + { + // TODO: include last token in exception message + case ExpressionErrorType.CellReference: + throw new CellReferenceException(); + case ExpressionErrorType.CellValue: + throw new CellValueException(); + case ExpressionErrorType.DivisionByZero: + throw new DivisionByZeroException(); + case ExpressionErrorType.NameNotRecognized: + throw new NameNotRecognizedException(); + case ExpressionErrorType.NoValueAvailable: + throw new NoValueAvailableException(); + case ExpressionErrorType.NullValue: + throw new NullValueException(); + case ExpressionErrorType.NumberInvalid: + throw new NumberException(); + } + } + } + /// /// Interface supported by external objects that have to return a value /// other than themselves (e.g. a cell range object should return the diff --git a/ClosedXML/Excel/CalcEngine/Functions/Information.cs b/ClosedXML/Excel/CalcEngine/Functions/Information.cs index 6df1a1e..6b19dfb 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Information.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Information.cs @@ -1,3 +1,4 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; using System.Collections.Generic; using System.Globalization; @@ -9,27 +10,42 @@ public static void Register(CalcEngine ce) { //TODO: Add documentation - ce.RegisterFunction("ERRORTYPE",1,ErrorType); - ce.RegisterFunction("ISBLANK", 1,int.MaxValue, IsBlank); - ce.RegisterFunction("ISERR",1, int.MaxValue, IsErr); - ce.RegisterFunction("ISERROR",1, int.MaxValue, IsError); - ce.RegisterFunction("ISEVEN",1, IsEven); - ce.RegisterFunction("ISLOGICAL",1,int.MaxValue,IsLogical); - ce.RegisterFunction("ISNA",1, int.MaxValue, IsNa); - ce.RegisterFunction("ISNONTEXT",1, int.MaxValue, IsNonText); - ce.RegisterFunction("ISNUMBER",1, int.MaxValue, IsNumber); - ce.RegisterFunction("ISODD",1,IsOdd); - ce.RegisterFunction("ISREF",1, int.MaxValue, IsRef); + ce.RegisterFunction("ERRORTYPE", 1, ErrorType); + ce.RegisterFunction("ISBLANK", 1, int.MaxValue, IsBlank); + ce.RegisterFunction("ISERR", 1, int.MaxValue, IsErr); + ce.RegisterFunction("ISERROR", 1, int.MaxValue, IsError); + ce.RegisterFunction("ISEVEN", 1, IsEven); + ce.RegisterFunction("ISLOGICAL", 1, int.MaxValue, IsLogical); + ce.RegisterFunction("ISNA", 1, int.MaxValue, IsNa); + ce.RegisterFunction("ISNONTEXT", 1, int.MaxValue, IsNonText); + ce.RegisterFunction("ISNUMBER", 1, int.MaxValue, IsNumber); + ce.RegisterFunction("ISODD", 1, IsOdd); + ce.RegisterFunction("ISREF", 1, int.MaxValue, IsRef); ce.RegisterFunction("ISTEXT", 1, int.MaxValue, IsText); - ce.RegisterFunction("N",1,N); - ce.RegisterFunction("NA",0,NA); - ce.RegisterFunction("TYPE",1,Type); + ce.RegisterFunction("N", 1, N); + ce.RegisterFunction("NA", 0, NA); + ce.RegisterFunction("TYPE", 1, Type); } + static IDictionary errorTypes = new Dictionary() + { + [ErrorExpression.ExpressionErrorType.NullValue] = 1, + [ErrorExpression.ExpressionErrorType.DivisionByZero] = 2, + [ErrorExpression.ExpressionErrorType.CellValue] = 3, + [ErrorExpression.ExpressionErrorType.CellReference] = 4, + [ErrorExpression.ExpressionErrorType.NameNotRecognized] = 5, + [ErrorExpression.ExpressionErrorType.NumberInvalid] = 6, + [ErrorExpression.ExpressionErrorType.NoValueAvailable] = 7 + }; + static object ErrorType(List p) { - //TODO: Write Code - throw new NotSupportedException();; + var v = p[0].Evaluate(); + + if (v is ErrorExpression.ExpressionErrorType) + return errorTypes[(ErrorExpression.ExpressionErrorType)v]; + else + throw new NoValueAvailableException(); } static object IsBlank(List p) @@ -46,17 +62,19 @@ return isBlank; } - //TODO: Support for Error Values static object IsErr(List p) { - //TODO: Write Code - throw new NotSupportedException(); + var v = p[0].Evaluate(); + + return v is ErrorExpression.ExpressionErrorType + && ((ErrorExpression.ExpressionErrorType)v) != ErrorExpression.ExpressionErrorType.NoValueAvailable; } - + static object IsError(List p) { - //TODO: Write Code - throw new NotSupportedException(); + var v = p[0].Evaluate(); + + return v is ErrorExpression.ExpressionErrorType; } static object IsEven(List p) @@ -74,7 +92,7 @@ { var v = p[0].Evaluate(); var isLogical = v is bool; - + if (isLogical && p.Count > 1) { var sublist = p.GetRange(1, p.Count); @@ -86,8 +104,10 @@ static object IsNa(List p) { - //TODO: Write Code - throw new NotSupportedException();; + var v = p[0].Evaluate(); + + return v is ErrorExpression.ExpressionErrorType + && ((ErrorExpression.ExpressionErrorType)v) == ErrorExpression.ExpressionErrorType.NoValueAvailable; } static object IsNonText(List p) @@ -110,16 +130,16 @@ try { var stringValue = (string) v; - double.Parse(stringValue.TrimEnd('%', ' '), NumberStyles.Any); - isNumber = true; + double dv; + return double.TryParse(stringValue.TrimEnd('%', ' '), NumberStyles.Any, null, out dv); } catch (Exception) { isNumber = false; } } - - if (isNumber && p.Count > 1) + + if (isNumber && p.Count > 1) { var sublist = p.GetRange(1, p.Count); isNumber = (bool)IsNumber(sublist); @@ -135,8 +155,13 @@ static object IsRef(List p) { - //TODO: Write Code - throw new NotSupportedException();; + var oe = p[0] as XObjectExpression; + if (oe == null) + return false; + + var crr = oe.Value as CellRangeReference; + + return crr != null; } static object IsText(List p) @@ -161,8 +186,7 @@ static object NA(List p) { - //TODO: Write Code - throw new NotSupportedException();; + return ErrorExpression.ExpressionErrorType.NoValueAvailable; } static object Type(List p) @@ -190,4 +214,4 @@ return null; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs index 964e72b..801a195 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Lookup.cs @@ -1,3 +1,4 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; using System.Collections.Generic; using System.Linq; @@ -35,11 +36,11 @@ var table_array = p[1] as XObjectExpression; if (table_array == null) - throw new ArgumentException("table_array has to be a range"); + throw new NoValueAvailableException("table_array has to be a range"); var range_reference = table_array.Value as CellRangeReference; if (range_reference == null) - throw new ArgumentException("table_array has to be a range"); + throw new NoValueAvailableException("table_array has to be a range"); var range = range_reference.Range; @@ -49,10 +50,10 @@ || (bool)(p[3]); if (row_index_num < 1) - throw new ArgumentOutOfRangeException("Row index", "row_index_num has to be positive"); + throw new CellReferenceException("Row index has to be positive"); if (row_index_num > range.RowCount()) - throw new ArgumentOutOfRangeException("Row index", "row_index_num has to be positive"); + throw new CellReferenceException("Row index has to be positive"); IXLRangeColumn matching_column; matching_column = range.FindColumn(c => !c.Cell(1).IsEmpty() && new Expression(c.Cell(1).Value).CompareTo(lookup_value) == 0); @@ -72,7 +73,7 @@ } if (matching_column == null) - throw new ArgumentException("No matches found."); + throw new NoValueAvailableException("No matches found."); return matching_column .Cell(row_index_num) @@ -85,11 +86,11 @@ var table_array = p[1] as XObjectExpression; if (table_array == null) - throw new ArgumentException("table_array has to be a range"); + throw new NoValueAvailableException("table_array has to be a range"); var range_reference = table_array.Value as CellRangeReference; if (range_reference == null) - throw new ArgumentException("table_array has to be a range"); + throw new NoValueAvailableException("table_array has to be a range"); var range = range_reference.Range; @@ -99,13 +100,20 @@ || (bool)(p[3]); if (col_index_num < 1) - throw new ArgumentOutOfRangeException("Column index", "col_index_num has to be positive"); + throw new CellReferenceException("Column index has to be positive"); if (col_index_num > range.ColumnCount()) - throw new ArgumentOutOfRangeException("Column index", "col_index_num must be smaller or equal to the number of columns in the table array"); + throw new CellReferenceException("Colum index must be smaller or equal to the number of columns in the table array"); IXLRangeRow matching_row; - matching_row = range.FindRow(r => !r.Cell(1).IsEmpty() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) == 0); + try + { + matching_row = range.FindRow(r => !r.Cell(1).IsEmpty() && new Expression(r.Cell(1).Value).CompareTo(lookup_value) == 0); + } + catch (Exception ex) + { + throw new NoValueAvailableException("No matches found", ex); + } if (range_lookup && matching_row == null) { var first_row = range.FirstRow().RowNumber(); @@ -122,7 +130,7 @@ } if (matching_row == null) - throw new ArgumentException("No matches found."); + throw new NoValueAvailableException("No matches found."); return matching_row .Cell(col_index_num) diff --git a/ClosedXML/Excel/CalcEngine/Functions/Text.cs b/ClosedXML/Excel/CalcEngine/Functions/Text.cs index 6046455..8ff1da3 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Text.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Text.cs @@ -1,3 +1,4 @@ +using ClosedXML.Excel.CalcEngine.Exceptions; using System; using System.Collections.Generic; using System.Globalization; @@ -44,7 +45,9 @@ private static object _Char(List p) { var i = (int)p[0]; - if (i < 1 || i > 255) throw new IndexOutOfRangeException(); + if (i < 1 || i > 255) + throw new CellValueException(string.Format("The number {0} is out of the required range (1 to 255)", i)); + var c = (char)i; return c.ToString(); } diff --git a/ClosedXML/Excel/CalcEngine/Token.cs b/ClosedXML/Excel/CalcEngine/Token.cs index 67351df..4766a51 100644 --- a/ClosedXML/Excel/CalcEngine/Token.cs +++ b/ClosedXML/Excel/CalcEngine/Token.cs @@ -1,12 +1,13 @@ namespace ClosedXML.Excel.CalcEngine { /// - /// Represents a node in the expression tree. + /// Represents a node in the expression tree. /// internal class Token - { + { // ** fields - public TKID ID; + public TKID ID; + public TKTYPE Type; public object Value; @@ -15,22 +16,25 @@ { Value = value; ID = id; - Type = type; - } + Type = type; + } } + /// /// Token types (used when building expressions, sequence defines operator priority) /// internal enum TKTYPE { - COMPARE, // < > = <= >= - ADDSUB, // + - - MULDIV, // * / - POWER, // ^ - GROUP, // ( ) , . - LITERAL, // 123.32, "Hello", etc. - IDENTIFIER // functions, external objects, bindings + COMPARE, // < > = <= >= + ADDSUB, // + - + MULDIV, // * / + POWER, // ^ + GROUP, // ( ) , . + LITERAL, // 123.32, "Hello", etc. + IDENTIFIER, // functions, external objects, bindings + ERROR // e.g. #REF! } + /// /// Token ID (used when evaluating expressions) /// diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 819f8f9..fa5e7db 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -15,6 +15,7 @@ using Attributes; using ClosedXML.Extensions; + [DebuggerDisplay("{Address}")] internal class XLCell : IXLCell, IXLStylized { public static readonly DateTime BaseDate = new DateTime(1899, 12, 30); @@ -73,49 +74,7 @@ internal XLCellValues _dataType; private XLHyperlink _hyperlink; private XLRichText _richText; - - #endregion Fields - - #region Constructor - - private Int32 _styleCacheId; - - public XLCell(XLWorksheet worksheet, XLAddress address, Int32 styleId) - { - Address = address; - ShareString = true; - _worksheet = worksheet; - SetStyle(styleId); - } - - private IXLStyle GetStyleForRead() - { - return Worksheet.Workbook.GetStyleById(GetStyleId()); - } - - public Int32 GetStyleId() - { - if (StyleChanged) - SetStyle(Style); - - return _styleCacheId; - } - - private void SetStyle(IXLStyle styleToUse) - { - _styleCacheId = Worksheet.Workbook.GetStyleId(styleToUse); - _style = null; - StyleChanged = false; - } - - private void SetStyle(Int32 styleId) - { - _styleCacheId = styleId; - _style = null; - StyleChanged = false; - } - - #endregion Constructor + private Int32? _styleCacheId; public bool SettingHyperlink; public int SharedStringId; @@ -123,6 +82,25 @@ private string _formulaR1C1; private IXLStyle _style; + #endregion Fields + + #region Constructor + + public XLCell(XLWorksheet worksheet, XLAddress address, Int32 styleId) + : this(worksheet, address) + { + SetStyle(styleId); + } + + public XLCell(XLWorksheet worksheet, XLAddress address) + { + Address = address; + ShareString = true; + _worksheet = worksheet; + } + + #endregion Constructor + public XLWorksheet Worksheet { get { return _worksheet; } @@ -411,8 +389,7 @@ var retValEnumerable = retVal as IEnumerable; if (retValEnumerable != null && !(retVal is String)) - foreach (var v in retValEnumerable) - return v; + return retValEnumerable.Cast().First(); return retVal; } @@ -466,7 +443,7 @@ if (!SetRichText(value)) SetValue(value); - if (_cellValue.Length > 32767) throw new ArgumentException("Cells can only hold 32,767 characters."); + if (_cellValue.Length > 32767) throw new ArgumentException("Cells can hold only 32,767 characters."); } } @@ -535,7 +512,7 @@ } else { - const BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.Instance; + const BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static; var memberCache = new Dictionary>(); var accessorCache = new Dictionary(); IEnumerable members = null; @@ -644,7 +621,7 @@ { foreach (var mi in members) { - if ((mi as IEnumerable) == null) + if (!(mi is IEnumerable)) { var fieldName = XLColumnAttribute.GetHeader(mi); if (String.IsNullOrWhiteSpace(fieldName)) @@ -662,7 +639,13 @@ foreach (var mi in members) { - _worksheet.SetValue(accessor[m, mi.Name], ro, co); + if (mi.MemberType == MemberTypes.Property && (mi as PropertyInfo).GetGetMethod().IsStatic) + _worksheet.SetValue((mi as PropertyInfo).GetValue(null), ro, co); + else if (mi.MemberType == MemberTypes.Field && (mi as FieldInfo).IsStatic) + _worksheet.SetValue((mi as FieldInfo).GetValue(null), ro, co); + else + _worksheet.SetValue(accessor[m, mi.Name], ro, co); + co++; } } @@ -762,7 +745,7 @@ var isDataTable = false; var isDataReader = false; - const BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.Instance; + const BindingFlags bindingFlags = BindingFlags.Public | BindingFlags.Instance | BindingFlags.Static; var memberCache = new Dictionary>(); var accessorCache = new Dictionary(); IEnumerable members = null; @@ -771,28 +754,12 @@ foreach (var m in data) { var itemType = m.GetType(); - 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); - } - - members = memberCache[itemType]; - accessor = accessorCache[itemType]; if (transpose) rowNumber = Address.RowNumber; else columnNumber = Address.ColumnNumber; - if (itemType.IsPrimitive || itemType == typeof(String) || itemType == typeof(DateTime) || itemType.IsNumber()) { _worksheet.SetValue(m, rowNumber, columnNumber); @@ -849,9 +816,30 @@ } 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) { - _worksheet.SetValue(accessor[m, mi.Name], rowNumber, columnNumber); + if (mi.MemberType == MemberTypes.Property && (mi as PropertyInfo).GetGetMethod().IsStatic) + _worksheet.SetValue((mi as PropertyInfo).GetValue(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++; @@ -1202,10 +1190,15 @@ public Boolean IsEmpty(Boolean includeFormats) { + return IsEmpty(includeFormats, includeFormats); + } + + public Boolean IsEmpty(Boolean includeNormalFormats, Boolean includeConditionalFormats) + { if (InnerText.Length > 0) return false; - if (includeFormats) + if (includeNormalFormats) { if (!Style.Equals(Worksheet.Style) || IsMerged() || HasComment || HasDataValidation) return false; @@ -1220,10 +1213,12 @@ if (Worksheet.Internals.ColumnsCollection.TryGetValue(Address.ColumnNumber, out column) && !column.Style.Equals(Worksheet.Style)) return false; } - - if (Worksheet.ConditionalFormats.Any(cf => cf.Range.Contains(this))) - return false; } + + if (includeConditionalFormats + && Worksheet.ConditionalFormats.Any(cf => cf.Range.Contains(this))) + return false; + return true; } @@ -1623,14 +1618,57 @@ return _worksheet.Range(Address, Address); } + #region Styles + private IXLStyle GetStyle() { if (_style != null) return _style; - return _style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId)); + return _style = new XLStyle(this, Worksheet.Workbook.GetStyleById(StyleCacheId())); } + private IXLStyle GetStyleForRead() + { + return Worksheet.Workbook.GetStyleById(GetStyleId()); + } + + public Int32 GetStyleId() + { + if (StyleChanged) + SetStyle(Style); + + return StyleCacheId(); + } + + private void SetStyle(IXLStyle styleToUse) + { + _styleCacheId = Worksheet.Workbook.GetStyleId(styleToUse); + _style = null; + StyleChanged = false; + } + + private void SetStyle(Int32 styleId) + { + _styleCacheId = styleId; + _style = null; + StyleChanged = false; + } + + public Int32 StyleCacheId() + { + if (!_styleCacheId.HasValue) + _styleCacheId = Worksheet.GetStyleId(); + return _styleCacheId.Value; + } + + public Boolean IsDefaultWorksheetStyle() + { + return !_styleCacheId.HasValue && !StyleChanged || GetStyleId() == Worksheet.GetStyleId(); + } + + #endregion Styles + public void DeleteComment() { _comment = null; @@ -1743,7 +1781,6 @@ mergeToDelete.ForEach(m => Worksheet.Internals.MergedRanges.Remove(m)); } - private void SetValue(object value) { FormulaA1 = String.Empty; @@ -2100,7 +2137,8 @@ CopyValuesFrom(source); - SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId)); + if (source._styleCacheId.HasValue) + SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId.Value)); var conditionalFormats = source.Worksheet.ConditionalFormats.Where(c => c.Range.Contains(source)).ToList(); foreach (var cf in conditionalFormats) diff --git a/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/Excel/Cells/XLCells.cs index 5f49b98..c8fef10 100644 --- a/ClosedXML/Excel/Cells/XLCells.cs +++ b/ClosedXML/Excel/Cells/XLCells.cs @@ -52,16 +52,18 @@ { if (oneRange) { - var cellRange = range.Worksheet.Internals.CellsCollection - .GetCells( - range.FirstAddress.RowNumber, - range.FirstAddress.ColumnNumber, - range.LastAddress.RowNumber, - range.LastAddress.ColumnNumber) - .Where(c => - !c.IsEmpty(_includeFormats) - && (_predicate == null || _predicate(c)) - ); + var cellRange = range + .Worksheet + .Internals + .CellsCollection + .GetCells( + range.FirstAddress.RowNumber, + range.FirstAddress.ColumnNumber, + range.LastAddress.RowNumber, + range.LastAddress.ColumnNumber) + .Where(c => !c.IsEmpty(_includeFormats) + && (_predicate == null || _predicate(c)) + ); foreach(var cell in cellRange) { @@ -121,13 +123,16 @@ { if (_usedCellsOnly) { - var cellRange = cellsInRanges.SelectMany( - cir => - cir.Value.Select(a => cir.Key.Internals.CellsCollection.GetCell(a)).Where( - cell => cell != null && ( - !cell.IsEmpty(_includeFormats) - && (_predicate == null || _predicate(cell)) - ))); + var cellRange = cellsInRanges + .SelectMany( + cir => + cir.Value.Select(a => cir.Key.Internals.CellsCollection.GetCell(a)).Where( + cell => + cell != null + && !cell.IsEmpty(_includeFormats) + && (_predicate == null || _predicate(cell)) + ) + ); foreach (var cell in cellRange) { @@ -277,4 +282,4 @@ cell.Select(); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Cells/XLCellsCollection.cs b/ClosedXML/Excel/Cells/XLCellsCollection.cs index 32a89aa..8f11a98 100644 --- a/ClosedXML/Excel/Cells/XLCellsCollection.cs +++ b/ClosedXML/Excel/Cells/XLCellsCollection.cs @@ -95,7 +95,7 @@ Count--; DecrementUsage(RowsUsed, row); DecrementUsage(ColumnsUsed, row); - + HashSet delHash; if (deleted.TryGetValue(row, out delHash)) { @@ -119,7 +119,7 @@ } } - + } internal IEnumerable GetCells(Int32 rowStart, Int32 columnStart, @@ -148,14 +148,22 @@ { HashSet ids = new HashSet(); ids.Add(initial); - foreach (var row in rowsCollection) + foreach (var row in rowsCollection.Values) { - foreach (var column in row.Value) + foreach (var cell in row.Values) { - var id = column.Value.GetStyleId(); - if (!ids.Contains(id)) + Int32? id = null; + + if (cell.StyleChanged) + id = cell.GetStyleId(); + else if (cell.StyleCacheId() != cell.Worksheet.GetStyleId()) { - ids.Add(id); + id = cell.GetStyleId(); + } + + if (id.HasValue && !ids.Contains(id.Value)) + { + ids.Add(id.Value); } } } @@ -494,4 +502,4 @@ return GetCells(row, 1, row, MaxColumnUsed); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Coordinates/IXLAddress.cs b/ClosedXML/Excel/Coordinates/IXLAddress.cs index bc70a90..6158251 100644 --- a/ClosedXML/Excel/Coordinates/IXLAddress.cs +++ b/ClosedXML/Excel/Coordinates/IXLAddress.cs @@ -5,18 +5,27 @@ { public interface IXLAddress : IEqualityComparer, IEquatable { - IXLWorksheet Worksheet { get; } - Int32 RowNumber { get; } - Int32 ColumnNumber { get; } String ColumnLetter { get; } - Boolean FixedRow { get; } + Int32 ColumnNumber { get; } Boolean FixedColumn { get; } - String ToStringRelative(); - String ToStringRelative(Boolean includeSheet); - String ToStringFixed(); - String ToStringFixed(XLReferenceStyle referenceStyle); - String ToStringFixed(XLReferenceStyle referenceStyle, Boolean includeSheet); - String ToString(XLReferenceStyle referenceStyle); + Boolean FixedRow { get; } + Int32 RowNumber { get; } String UniqueId { get; } + IXLWorksheet Worksheet { get; } + + String ToString(XLReferenceStyle referenceStyle); + + String ToString(XLReferenceStyle referenceStyle, Boolean includeSheet); + + String ToStringFixed(); + + String ToStringFixed(XLReferenceStyle referenceStyle); + + String ToStringFixed(XLReferenceStyle referenceStyle, Boolean includeSheet); + + String ToStringRelative(); + + + String ToStringRelative(Boolean includeSheet); } } diff --git a/ClosedXML/Excel/Coordinates/XLAddress.cs b/ClosedXML/Excel/Coordinates/XLAddress.cs index b1d7ff8..81f4977 100644 --- a/ClosedXML/Excel/Coordinates/XLAddress.cs +++ b/ClosedXML/Excel/Coordinates/XLAddress.cs @@ -7,6 +7,7 @@ internal class XLAddress : IXLAddress { #region Static + /// /// Create address without worksheet. For calculation only! /// @@ -72,22 +73,32 @@ } return new XLAddress(worksheet, rowNumber, columnLetter, fixedRow, fixedColumn); } - #endregion + + #endregion Static + #region Private fields + [DebuggerBrowsable(DebuggerBrowsableState.Never)] private bool _fixedRow; + [DebuggerBrowsable(DebuggerBrowsableState.Never)] private bool _fixedColumn; + [DebuggerBrowsable(DebuggerBrowsableState.Never)] private string _columnLetter; [DebuggerBrowsable(DebuggerBrowsableState.Never)] private readonly int _rowNumber; + [DebuggerBrowsable(DebuggerBrowsableState.Never)] private readonly int _columnNumber; + private string _trimmedAddress; - #endregion + + #endregion Private fields + #region Constructors + /// /// Initializes a new struct using a mixed notation. Attention: without worksheet for calculation only! /// @@ -99,6 +110,7 @@ : this(null, rowNumber, columnLetter, fixedRow, fixedColumn) { } + /// /// Initializes a new struct using a mixed notation. /// @@ -124,6 +136,7 @@ : this(null, rowNumber, columnNumber, fixedRow, fixedColumn) { } + /// /// Initializes a new struct using R1C1 notation. /// @@ -142,12 +155,14 @@ _columnLetter = null; _fixedColumn = fixedColumn; _fixedRow = fixedRow; - - } - #endregion + + #endregion Constructors + #region Properties + public XLWorksheet Worksheet { get; internal set; } + IXLWorksheet IXLAddress.Worksheet { [DebuggerStepThrough] @@ -195,8 +210,11 @@ { get { return _columnLetter ?? (_columnLetter = XLHelper.GetColumnLetterFromNumber(_columnNumber)); } } - #endregion + + #endregion Properties + #region Overrides + public override string ToString() { String retVal = ColumnLetter; @@ -214,31 +232,45 @@ public string ToString(XLReferenceStyle referenceStyle) { - if (referenceStyle == XLReferenceStyle.A1) - { - return ColumnLetter + _rowNumber.ToInvariantString(); - } - if (referenceStyle == XLReferenceStyle.R1C1) - { - return String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber); - } - if (HasWorksheet && Worksheet.Workbook.ReferenceStyle == XLReferenceStyle.R1C1) - { - return String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber); - } - return ColumnLetter + _rowNumber.ToInvariantString(); + return ToString(referenceStyle, false); } - #endregion + + public string ToString(XLReferenceStyle referenceStyle, bool includeSheet) + { + string address = string.Empty; + if (referenceStyle == XLReferenceStyle.A1) + + address = ColumnLetter + _rowNumber.ToInvariantString(); + else if (referenceStyle == XLReferenceStyle.R1C1) + + address = String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber); + else if (HasWorksheet && Worksheet.Workbook.ReferenceStyle == XLReferenceStyle.R1C1) + + address = String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber); + else + address = ColumnLetter + _rowNumber.ToInvariantString(); + + if (includeSheet) + return String.Format("{0}!{1}", + Worksheet.Name.WrapSheetNameInQuotesIfRequired(), + address); + + return address; + } + + #endregion Overrides + #region Methods + public string GetTrimmedAddress() { return _trimmedAddress ?? (_trimmedAddress = ColumnLetter + _rowNumber.ToInvariantString()); } + #endregion Methods - - #endregion #region Operator Overloads + public static XLAddress operator +(XLAddress left, XLAddress right) { return new XLAddress(left.Worksheet, @@ -288,9 +320,13 @@ { return !(left == right); } - #endregion + + #endregion Operator Overloads + #region Interface Requirements + #region IEqualityComparer Members + public Boolean Equals(IXLAddress x, IXLAddress y) { return x == y; @@ -315,8 +351,11 @@ { return _rowNumber ^ _columnNumber; } - #endregion + + #endregion IEqualityComparer Members + #region IEquatable Members + public bool Equals(IXLAddress other) { var right = other as XLAddress; @@ -329,10 +368,12 @@ public override Boolean Equals(Object other) { - return Equals((XLAddress) other); + return Equals((XLAddress)other); } - #endregion - #endregion + + #endregion IEquatable Members + + #endregion Interface Requirements public String ToStringRelative() { diff --git a/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs b/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs index 2e5bebe..caf81d2 100644 --- a/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs +++ b/ClosedXML/Excel/Hyperlinks/XLHyperlink_Internal.cs @@ -5,7 +5,7 @@ public partial class XLHyperlink { internal XLHyperlink() - { + { } @@ -36,7 +36,7 @@ else { _internalAddress = address; - IsExternal = false; + IsExternal = false; } } } @@ -51,14 +51,14 @@ internal void SetValues(IXLCell cell, String tooltip) { Tooltip = tooltip; - _internalAddress = cell.Address.ToString(); + _internalAddress = cell.Address.ToString(XLReferenceStyle.A1, true); IsExternal = false; } internal void SetValues(IXLRangeBase range, String tooltip) { Tooltip = tooltip; - _internalAddress = range.RangeAddress.ToString(); + _internalAddress = range.RangeAddress.ToString(XLReferenceStyle.A1, true); IsExternal = false; } diff --git a/ClosedXML/Excel/PivotTables/IXLPivotFields.cs b/ClosedXML/Excel/PivotTables/IXLPivotFields.cs index 08c5312..df5a497 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotFields.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotFields.cs @@ -1,17 +1,22 @@ using System; using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { - public interface IXLPivotFields: IEnumerable + public interface IXLPivotFields : IEnumerable { IXLPivotField Add(String sourceName); - IXLPivotField Add(String sourceName, String customName); - void Clear(); - void Remove(String sourceName); - int IndexOf(IXLPivotField pf); + IXLPivotField Add(String sourceName, String customName); + + void Clear(); + + Boolean Contains(String sourceName); + + IXLPivotField Get(String sourceName); + + Int32 IndexOf(IXLPivotField pf); + + void Remove(String sourceName); } } diff --git a/ClosedXML/Excel/PivotTables/XLPivotFields.cs b/ClosedXML/Excel/PivotTables/XLPivotFields.cs index c1a52f2..19accb7 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotFields.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotFields.cs @@ -1,13 +1,40 @@ using System; using System.Collections.Generic; using System.Linq; -using System.Text; namespace ClosedXML.Excel { - public class XLPivotFields: IXLPivotFields + public class XLPivotFields : IXLPivotFields { private readonly Dictionary _pivotFields = new Dictionary(); + + public IXLPivotField Add(String sourceName) + { + return Add(sourceName, sourceName); + } + + public IXLPivotField Add(String sourceName, String customName) + { + var pivotField = new XLPivotField(sourceName) { CustomName = customName }; + _pivotFields.Add(sourceName, pivotField); + return pivotField; + } + + public void Clear() + { + _pivotFields.Clear(); + } + + public Boolean Contains(String sourceName) + { + return _pivotFields.ContainsKey(sourceName); + } + + public IXLPivotField Get(string sourceName) + { + return _pivotFields[sourceName]; + } + public IEnumerator GetEnumerator() { return _pivotFields.Values.GetEnumerator(); @@ -18,32 +45,17 @@ return GetEnumerator(); } - public IXLPivotField Add(String sourceName) + public Int32 IndexOf(IXLPivotField pf) { - return Add(sourceName, sourceName); - } - public IXLPivotField Add(String sourceName, String customName) - { - var pivotField = new XLPivotField(sourceName) {CustomName = customName}; - _pivotFields.Add(sourceName, pivotField); - return pivotField; - } - - public void Clear() - { - _pivotFields.Clear(); - } - public void Remove(String sourceName) - { - _pivotFields.Remove(sourceName); - } - - public int IndexOf(IXLPivotField pf) - { - var selectedItem = _pivotFields.Select((item, index) => new {Item = item, Position = index}).FirstOrDefault(i => i.Item.Key == pf.SourceName); + var selectedItem = _pivotFields.Select((item, index) => new { Item = item, Position = index }).FirstOrDefault(i => i.Item.Key == pf.SourceName); if (selectedItem == null) throw new IndexOutOfRangeException("Invalid field name."); return selectedItem.Position; } + + public void Remove(String sourceName) + { + _pivotFields.Remove(sourceName); + } } } diff --git a/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/Excel/Ranges/IXLRange.cs index 992ebf9..945c0c9 100644 --- a/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/Excel/Ranges/IXLRange.cs @@ -46,7 +46,7 @@ /// /// Gets the specified column of the range. /// - /// Column letter. + /// Column letter. IXLRangeColumn Column(string columnLetter); /// diff --git a/ClosedXML/Excel/Ranges/IXLRangeAddress.cs b/ClosedXML/Excel/Ranges/IXLRangeAddress.cs index 9da98e4..86ed2b1 100644 --- a/ClosedXML/Excel/Ranges/IXLRangeAddress.cs +++ b/ClosedXML/Excel/Ranges/IXLRangeAddress.cs @@ -4,8 +4,6 @@ { public interface IXLRangeAddress { - IXLWorksheet Worksheet { get; } - /// /// Gets or sets the first address in the range. /// @@ -13,13 +11,7 @@ /// The first address. /// IXLAddress FirstAddress { get; set; } - /// - /// Gets or sets the last address in the range. - /// - /// - /// The last address. - /// - IXLAddress LastAddress { get; set; } + /// /// Gets or sets a value indicating whether this range is invalid. /// @@ -28,10 +20,28 @@ /// Boolean IsInvalid { get; set; } - String ToStringRelative(); - String ToStringRelative(Boolean includeSheet); + /// + /// Gets or sets the last address in the range. + /// + /// + /// The last address. + /// + IXLAddress LastAddress { get; set; } + + IXLWorksheet Worksheet { get; } + + String ToString(XLReferenceStyle referenceStyle); + + String ToString(XLReferenceStyle referenceStyle, Boolean includeSheet); + String ToStringFixed(); + String ToStringFixed(XLReferenceStyle referenceStyle); + String ToStringFixed(XLReferenceStyle referenceStyle, Boolean includeSheet); + + String ToStringRelative(); + + String ToStringRelative(Boolean includeSheet); } } diff --git a/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/Excel/Ranges/XLRangeAddress.cs index fbe5135..13f986a 100644 --- a/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -1,7 +1,6 @@ using ClosedXML.Extensions; using System; using System.Diagnostics; -using System.Globalization; using System.Linq; namespace ClosedXML.Excel @@ -10,16 +9,18 @@ { #region Private fields - [DebuggerBrowsable(DebuggerBrowsableState.Never)] private XLAddress _firstAddress; - [DebuggerBrowsable(DebuggerBrowsableState.Never)] private XLAddress _lastAddress; + [DebuggerBrowsable(DebuggerBrowsableState.Never)] + private XLAddress _firstAddress; - #endregion + [DebuggerBrowsable(DebuggerBrowsableState.Never)] + private XLAddress _lastAddress; + + #endregion Private fields #region Constructor - public XLRangeAddress(XLRangeAddress rangeAddress): this(rangeAddress.FirstAddress, rangeAddress.LastAddress) + public XLRangeAddress(XLRangeAddress rangeAddress) : this(rangeAddress.FirstAddress, rangeAddress.LastAddress) { - } public XLRangeAddress(XLAddress firstAddress, XLAddress lastAddress) @@ -73,7 +74,7 @@ Worksheet = worksheet; } - #endregion + #endregion Constructor #region Public properties @@ -122,10 +123,9 @@ set { LastAddress = value as XLAddress; } } - public bool IsInvalid { get; set; } - #endregion + #endregion Public properties #region Public methods @@ -168,7 +168,20 @@ public override string ToString() { - return _firstAddress + ":" + _lastAddress; + return String.Concat(_firstAddress, ':', _lastAddress); + } + + public string ToString(XLReferenceStyle referenceStyle) + { + return ToString(referenceStyle, false); + } + + public string ToString(XLReferenceStyle referenceStyle, bool includeSheet) + { + if (referenceStyle == XLReferenceStyle.R1C1) + return ToStringFixed(referenceStyle, true); + else + return ToStringRelative(includeSheet); } public override bool Equals(object obj) @@ -187,6 +200,6 @@ ^ LastAddress.GetHashCode(); } - #endregion + #endregion Public methods } } diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 0534107..190d473 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -841,12 +841,14 @@ Int32 newCellStyleId = styleId; + XLCell newCell; // 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; + if (styleId == 0 && worksheetStyleId != 0 || styleId == worksheetStyleId) + newCell = new XLCell(Worksheet, absoluteAddress); + else + newCell = new XLCell(Worksheet, absoluteAddress, newCellStyleId); - var newCell = new XLCell(Worksheet, absoluteAddress, newCellStyleId); Worksheet.Internals.CellsCollection.Add(absRow, absColumn, newCell); return newCell; } diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index c6868f5..aa9a77d 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -146,7 +146,6 @@ if (worksheet == null) return; - string sheetName = worksheet.Name; // Get the pivot Table Parts IEnumerable pvtTableCacheParts = wbPart.PivotTableCacheDefinitionParts; @@ -204,11 +203,9 @@ foreach (CalculationCell Item in calChainEntries) calcsToDelete.Add(Item); - foreach (CalculationCell Item in calcsToDelete) Item.Remove(); - if (!calChainPart.CalculationChain.Any()) wbPart.DeletePart(calChainPart); } @@ -675,6 +672,8 @@ var xlSheet = Worksheet(sheet.Name); if (xlSheet.Visibility != XLWorksheetVisibility.Visible) sheet.State = xlSheet.Visibility.ToOpenXml(); + else + sheet.State = null; if (foundVisible) continue; @@ -1964,7 +1963,11 @@ { var columnNumber = c.ColumnNumber(); var columnName = c.FirstCell().Value.ToString(); - var xlpf = pt.Fields.Add(columnName); + IXLPivotField xlpf; + if (pt.Fields.Contains(columnName)) + xlpf = pt.Fields.Get(columnName); + else + xlpf = pt.Fields.Add(columnName); var field = pt.RowLabels.Union(pt.ColumnLabels).Union(pt.ReportFilters).FirstOrDefault(f => f.SourceName == columnName); @@ -2150,10 +2153,12 @@ 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; @@ -4144,15 +4149,15 @@ cm.SetElement(XLWSContentManager.XLWSContents.SheetData, sheetData); var lastRow = 0; - var sheetDataRows = + var existingSheetDataRows = sheetData.Elements().ToDictionary(r => r.RowIndex == null ? ++lastRow : (Int32)r.RowIndex.Value, r => r); foreach ( var r in - xlWorksheet.Internals.RowsCollection.Deleted.Where(r => sheetDataRows.ContainsKey(r.Key))) + xlWorksheet.Internals.RowsCollection.Deleted.Where(r => existingSheetDataRows.ContainsKey(r.Key))) { - sheetData.RemoveChild(sheetDataRows[r.Key]); - sheetDataRows.Remove(r.Key); + sheetData.RemoveChild(existingSheetDataRows[r.Key]); + existingSheetDataRows.Remove(r.Key); xlWorksheet.Internals.CellsCollection.deleted.Remove(r.Key); } @@ -4161,28 +4166,10 @@ foreach (var distinctRow in distinctRows.OrderBy(r => r)) { Row row; - if (sheetDataRows.ContainsKey(distinctRow)) - row = sheetDataRows[distinctRow]; + if (existingSheetDataRows.ContainsKey(distinctRow)) + row = existingSheetDataRows[distinctRow]; else - { row = new Row { RowIndex = (UInt32)distinctRow }; - if (noRows) - { - sheetData.AppendChild(row); - noRows = false; - } - else - { - if (sheetDataRows.Any(r => r.Key > row.RowIndex.Value)) - { - var minRow = sheetDataRows.Where(r => r.Key > (Int32)row.RowIndex.Value).Min(r => r.Key); - var rowBeforeInsert = sheetDataRows[minRow]; - sheetData.InsertBefore(row, rowBeforeInsert); - } - else - sheetData.AppendChild(row); - } - } if (maxColumn > 0) row.Spans = new ListValue { InnerText = "1:" + maxColumn.ToInvariantString() }; @@ -4236,121 +4223,158 @@ xlWorksheet.Internals.CellsCollection.deleted.Remove(kpDel.Key); } - if (!xlWorksheet.Internals.CellsCollection.RowsCollection.ContainsKey(distinctRow)) continue; - - var isNewRow = !row.Elements().Any(); - lastCell = 0; - var mRows = row.Elements().ToDictionary(c => XLHelper.GetColumnNumberFromAddress(c.CellReference == null - ? (XLHelper.GetColumnLetterFromNumber(++lastCell) + distinctRow) : c.CellReference.Value), c => c); - foreach (var xlCell in xlWorksheet.Internals.CellsCollection.RowsCollection[distinctRow].Values - .OrderBy(c => c.Address.ColumnNumber) - .Select(c => c)) + if (xlWorksheet.Internals.CellsCollection.RowsCollection.ContainsKey(distinctRow)) { - XLTableField field = null; - - var styleId = context.SharedStyles[xlCell.GetStyleId()].StyleId; - var cellReference = (xlCell.Address).GetTrimmedAddress(); - var isEmpty = xlCell.IsEmpty(true); - - Cell cell = null; - if (cellsByReference.ContainsKey(cellReference)) + var isNewRow = !row.Elements().Any(); + lastCell = 0; + var mRows = row.Elements().ToDictionary(c => XLHelper.GetColumnNumberFromAddress(c.CellReference == null + ? (XLHelper.GetColumnLetterFromNumber(++lastCell) + distinctRow) : c.CellReference.Value), c => c); + foreach (var xlCell in xlWorksheet.Internals.CellsCollection.RowsCollection[distinctRow].Values + .OrderBy(c => c.Address.ColumnNumber) + .Select(c => c)) { - cell = cellsByReference[cellReference]; - if (isEmpty) - { - cell.Remove(); - } - } + XLTableField field = null; - if (!isEmpty) - { - if (cell == null) - { - cell = new Cell(); - cell.CellReference = new StringValue(cellReference); + var styleId = context.SharedStyles[xlCell.GetStyleId()].StyleId; + var cellReference = (xlCell.Address).GetTrimmedAddress(); - if (isNewRow) - row.AppendChild(cell); - else + // For saving cells to file, ignore conditional formatting. They just bloat the file + var isEmpty = xlCell.IsEmpty(true, false); + + Cell cell = null; + if (cellsByReference.ContainsKey(cellReference)) + { + cell = cellsByReference[cellReference]; + if (isEmpty) { - var newColumn = XLHelper.GetColumnNumberFromAddress(cellReference); + cell.Remove(); + } + } - Cell cellBeforeInsert = null; - int[] lastCo = { Int32.MaxValue }; - foreach (var c in mRows.Where(kp => kp.Key > newColumn).Where(c => lastCo[0] > c.Key)) - { - cellBeforeInsert = c.Value; - lastCo[0] = c.Key; - } - if (cellBeforeInsert == null) + if (!isEmpty) + { + if (cell == null) + { + cell = new Cell(); + cell.CellReference = new StringValue(cellReference); + + if (isNewRow) row.AppendChild(cell); else - row.InsertBefore(cell, cellBeforeInsert); - } - } - - cell.StyleIndex = styleId; - if (xlCell.HasFormula) - { - var formula = xlCell.FormulaA1; - if (xlCell.HasArrayFormula) - { - formula = formula.Substring(1, formula.Length - 2); - var f = new CellFormula { FormulaType = CellFormulaValues.Array }; - - if (xlCell.FormulaReference == null) - xlCell.FormulaReference = xlCell.AsRange().RangeAddress; - - if (xlCell.FormulaReference.FirstAddress.Equals(xlCell.Address)) { - f.Text = formula; - f.Reference = xlCell.FormulaReference.ToStringRelative(); + var newColumn = XLHelper.GetColumnNumberFromAddress(cellReference); + + Cell cellBeforeInsert = null; + int[] lastCo = { Int32.MaxValue }; + foreach (var c in mRows.Where(kp => kp.Key > newColumn).Where(c => lastCo[0] > c.Key)) + { + cellBeforeInsert = c.Value; + lastCo[0] = c.Key; + } + if (cellBeforeInsert == null) + row.AppendChild(cell); + else + row.InsertBefore(cell, cellBeforeInsert); + } + } + + cell.StyleIndex = styleId; + if (xlCell.HasFormula) + { + var formula = xlCell.FormulaA1; + if (xlCell.HasArrayFormula) + { + formula = formula.Substring(1, formula.Length - 2); + var f = new CellFormula { FormulaType = CellFormulaValues.Array }; + + if (xlCell.FormulaReference == null) + xlCell.FormulaReference = xlCell.AsRange().RangeAddress; + + if (xlCell.FormulaReference.FirstAddress.Equals(xlCell.Address)) + { + f.Text = formula; + f.Reference = xlCell.FormulaReference.ToStringRelative(); + } + + cell.CellFormula = f; + } + else + { + cell.CellFormula = new CellFormula(); + cell.CellFormula.Text = formula; } - cell.CellFormula = f; + cell.CellValue = null; + } + else if (xlCell.TableCellType() == XLTableCellType.Total) + { + var table = xlWorksheet.Tables.First(t => t.AsRange().Contains(xlCell)); + field = table.Fields.First(f => f.Column.ColumnNumber() == xlCell.Address.ColumnNumber) as XLTableField; + + if (!String.IsNullOrWhiteSpace(field.TotalsRowLabel)) + { + cell.DataType = XLWorkbook.CvSharedString; + } + else + { + cell.DataType = null; + } + cell.CellFormula = null; } else { - cell.CellFormula = new CellFormula(); - cell.CellFormula.Text = formula; + cell.CellFormula = null; + cell.DataType = xlCell.DataType == XLCellValues.DateTime ? null : GetCellValueType(xlCell); } - cell.CellValue = null; + if (evaluateFormulae || field != null || !xlCell.HasFormula) + SetCellValue(xlCell, field, cell); } - else if (xlCell.TableCellType() == XLTableCellType.Total) - { - var table = xlWorksheet.Tables.First(t => t.AsRange().Contains(xlCell)); - field = table.Fields.First(f => f.Column.ColumnNumber() == xlCell.Address.ColumnNumber) as XLTableField; + } + xlWorksheet.Internals.CellsCollection.deleted.Remove(distinctRow); + } - if (!String.IsNullOrWhiteSpace(field.TotalsRowLabel)) - { - cell.DataType = XLWorkbook.CvSharedString; - } - else - { - cell.DataType = null; - } - cell.CellFormula = null; + // If we're adding a new row (not in sheet already and it's not "empty" + if (!existingSheetDataRows.ContainsKey(distinctRow)) + { + var invalidRow = row.Height == null + && row.CustomHeight == null + && row.Hidden == null + && row.StyleIndex == null + && row.CustomFormat == null + && row.Collapsed == null + && row.OutlineLevel == null + && !row.Elements().Any(); + + if (!invalidRow) + { + if (noRows) + { + sheetData.AppendChild(row); + noRows = false; } else { - cell.CellFormula = null; - cell.DataType = xlCell.DataType == XLCellValues.DateTime ? null : GetCellValueType(xlCell); + if (existingSheetDataRows.Any(r => r.Key > row.RowIndex.Value)) + { + var minRow = existingSheetDataRows.Where(r => r.Key > (Int32)row.RowIndex.Value).Min(r => r.Key); + var rowBeforeInsert = existingSheetDataRows[minRow]; + sheetData.InsertBefore(row, rowBeforeInsert); + } + else + sheetData.AppendChild(row); } - - if (evaluateFormulae || field != null || !xlCell.HasFormula) - SetCellValue(xlCell, field, cell); } } - xlWorksheet.Internals.CellsCollection.deleted.Remove(distinctRow); } + foreach ( var r in xlWorksheet.Internals.CellsCollection.deleted.Keys.Where( - sheetDataRows.ContainsKey)) + existingSheetDataRows.ContainsKey)) { - sheetData.RemoveChild(sheetDataRows[r]); - sheetDataRows.Remove(r); + sheetData.RemoveChild(existingSheetDataRows[r]); + existingSheetDataRows.Remove(r); } #endregion SheetData diff --git a/ClosedXML_Examples/Misc/InsertingData.cs b/ClosedXML_Examples/Misc/InsertingData.cs index d1035d5..1d4a32b 100644 --- a/ClosedXML_Examples/Misc/InsertingData.cs +++ b/ClosedXML_Examples/Misc/InsertingData.cs @@ -55,9 +55,13 @@ ws.Range(6, 6, 6, 8).Merge().AddToNamed("Titles"); ws.Cell(7, 6).InsertData(people.AsEnumerable()); - ws.Cell("F13").Value = "Transposed"; - ws.Range(13, 6, 13, 8).Merge().AddToNamed("Titles"); - ws.Cell("F14").InsertData(people.AsEnumerable(), true); + ws.Cell(11, 6).Value = "From List"; + ws.Range(11, 6, 11, 9).Merge().AddToNamed("Titles"); + ws.Cell(12, 6).InsertData(list); + + ws.Cell("A13").Value = "Transposed"; + ws.Range(13, 1, 13, 3).Merge().AddToNamed("Titles"); + ws.Cell("A14").InsertData(people.AsEnumerable(), true); // Prepare the style for the titles var titlesStyle = wb.Style; @@ -79,6 +83,7 @@ public String House { get; set; } public String Name { get; set; } public Int32 Age { get; set; } + public static String ClassType { get { return nameof(Person); } } } // Private diff --git a/ClosedXML_Examples/Styles/UsingPhonetics.cs b/ClosedXML_Examples/Styles/UsingPhonetics.cs index eaf2e32..f6c6483 100644 --- a/ClosedXML_Examples/Styles/UsingPhonetics.cs +++ b/ClosedXML_Examples/Styles/UsingPhonetics.cs @@ -23,8 +23,10 @@ // And then we add the phonetics cell.RichText.Phonetics.SetFontSize(8); - cell.RichText.Phonetics.Add("げん", 7, 1); - cell.RichText.Phonetics.Add("き", 8, 1); + cell.RichText.Phonetics.Add("げん", 7, 8); + cell.RichText.Phonetics.Add("き", 8, 9); + + //TODO: I'm looking for someone who understands Japanese to confirm the validity of the above code. wb.SaveAs(filePath); } diff --git a/ClosedXML_Examples/Tables/InsertingTables.cs b/ClosedXML_Examples/Tables/InsertingTables.cs index bb3718f..5c86a6e 100644 --- a/ClosedXML_Examples/Tables/InsertingTables.cs +++ b/ClosedXML_Examples/Tables/InsertingTables.cs @@ -14,59 +14,65 @@ // Public public void Create(String filePath) { - var wb = new XLWorkbook(); - var ws = wb.Worksheets.Add("Inserting Tables"); + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Inserting Tables"); - // From a list of strings - var listOfStrings = new List(); - listOfStrings.Add("House"); - listOfStrings.Add("Car"); - ws.Cell(1, 1).Value = "From Strings"; - ws.Cell(1, 1).AsRange().AddToNamed("Titles"); - ws.Cell(2, 1).InsertTable(listOfStrings); + // From a list of strings + var listOfStrings = new List(); + listOfStrings.Add("House"); + listOfStrings.Add("Car"); + ws.Cell(1, 1).Value = "From Strings"; + ws.Cell(1, 1).AsRange().AddToNamed("Titles"); + ws.Cell(2, 1).InsertTable(listOfStrings); - // From a list of arrays - var listOfArr = new List(); - listOfArr.Add(new Int32[] { 1, 2, 3 }); - listOfArr.Add(new Int32[] { 1 }); - listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 }); - ws.Cell(1, 3).Value = "From Arrays"; - ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles"); - ws.Cell(2, 3).InsertTable(listOfArr); + // From a list of arrays + var listOfArr = new List(); + listOfArr.Add(new Int32[] { 1, 2, 3 }); + listOfArr.Add(new Int32[] { 1 }); + listOfArr.Add(new Int32[] { 1, 2, 3, 4, 5, 6 }); + ws.Cell(1, 3).Value = "From Arrays"; + ws.Range(1, 3, 1, 8).Merge().AddToNamed("Titles"); + ws.Cell(2, 3).InsertTable(listOfArr); - // From a DataTable - var dataTable = GetTable(); - ws.Cell(7, 1).Value = "From DataTable"; - ws.Range(7, 1, 7, 4).Merge().AddToNamed("Titles"); - ws.Cell(8, 1).InsertTable(dataTable.AsEnumerable()); + // From a DataTable + var dataTable = GetTable(); + ws.Cell(7, 1).Value = "From DataTable"; + ws.Range(7, 1, 7, 4).Merge().AddToNamed("Titles"); + ws.Cell(8, 1).InsertTable(dataTable.AsEnumerable()); - // From a query - var list = new List(); - list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St." }); - list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St." }); - list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St." }); - list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." }); + // From a query + var list = new List(); + list.Add(new Person() { Name = "John", Age = 30, House = "On Elm St." }); + list.Add(new Person() { Name = "Mary", Age = 15, House = "On Main St." }); + list.Add(new Person() { Name = "Luis", Age = 21, House = "On 23rd St." }); + list.Add(new Person() { Name = "Henry", Age = 45, House = "On 5th Ave." }); - var people = from p in list - where p.Age >= 21 - select p; + var people = from p in list + where p.Age >= 21 + select p; - ws.Cell(7, 6).Value = "From Query"; - ws.Range(7, 6, 7, 8).Merge().AddToNamed("Titles"); - ws.Cell(8, 6).InsertTable(people.AsEnumerable()); + ws.Cell(7, 6).Value = "From Query"; + ws.Range(7, 6, 7, 9).Merge().AddToNamed("Titles"); + ws.Cell(8, 6).InsertTable(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; + ws.Cell(15, 6).Value = "From List"; + ws.Range(15, 6, 15, 9).Merge().AddToNamed("Titles"); + ws.Cell(16, 6).InsertTable(people); - // Format all titles in one shot - wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle; + // Prepare the style for the titles + var titlesStyle = wb.Style; + titlesStyle.Font.Bold = true; + titlesStyle.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + titlesStyle.Fill.BackgroundColor = XLColor.Cyan; - ws.Columns().AdjustToContents(); + // Format all titles in one shot + wb.NamedRanges.NamedRange("Titles").Ranges.Style = titlesStyle; - wb.SaveAs(filePath); + ws.Columns().AdjustToContents(); + + wb.SaveAs(filePath); + } } private class Person @@ -76,6 +82,9 @@ public String Name { get; set; } public Int32 Age { get; set; } + + [XLColumn(Header = "Class Type")] + public static String ClassType { get { return nameof(Person); } } } // Private diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 388b050..28a64f7 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -78,6 +78,7 @@ + @@ -86,6 +87,7 @@ + @@ -290,6 +292,7 @@ + diff --git a/ClosedXML_Tests/Examples/StylesTests.cs b/ClosedXML_Tests/Examples/StylesTests.cs index 4ecfb15..c400a81 100644 --- a/ClosedXML_Tests/Examples/StylesTests.cs +++ b/ClosedXML_Tests/Examples/StylesTests.cs @@ -67,9 +67,15 @@ } [Test] + public void UsingPhonetics() + { + TestHelper.RunTestExample(@"Styles\UsingPhonetics.xlsx"); + } + + [Test] public void UsingRichText() { TestHelper.RunTestExample(@"Styles\UsingRichText.xlsx"); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/CalcEngineExceptionTests.cs b/ClosedXML_Tests/Excel/CalcEngine/CalcEngineExceptionTests.cs new file mode 100644 index 0000000..b1be35f --- /dev/null +++ b/ClosedXML_Tests/Excel/CalcEngine/CalcEngineExceptionTests.cs @@ -0,0 +1,29 @@ +using ClosedXML.Excel; +using ClosedXML.Excel.CalcEngine.Exceptions; +using NUnit.Framework; +using System; +using System.Collections.Generic; +using System.Globalization; +using System.Linq; +using System.Text; +using System.Threading; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + [TestFixture] + public class CalcEngineExceptionTests + { + [OneTimeSetUp] + public void SetCultureInfo() + { + Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("en-US"); + } + + [Test] + public void InvalidCharNumber() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr("CHAR(-2)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("CHAR(270)")); + } + } +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs b/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs index 93ad203..19de6ca 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/InformationTests.cs @@ -158,6 +158,17 @@ } #endregion IsLogical Tests + [Test] + public void IsNA() + { + object actual; + actual = XLWorkbook.EvaluateExpr("ISNA(#N/A)"); + Assert.AreEqual(true, actual); + + actual = XLWorkbook.EvaluateExpr("ISNA(#REF!)"); + Assert.AreEqual(false, actual); + } + #region IsNotText Tests [Test] @@ -288,6 +299,30 @@ } #endregion IsOdd Test + [Test] + public void IsRef() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").Value = "123"; + + ws.Cell("B1").FormulaA1 = "ISREF(A1)"; + ws.Cell("B2").FormulaA1 = "ISREF(5)"; + ws.Cell("B3").FormulaA1 = "ISREF(YEAR(TODAY()))"; + + bool actual; + actual = ws.Cell("B1").GetValue(); + Assert.AreEqual(true, actual); + + actual = ws.Cell("B2").GetValue(); + Assert.AreEqual(false, actual); + + actual = ws.Cell("B3").GetValue(); + Assert.AreEqual(false, actual); + } + } + #region IsText Tests [Test] diff --git a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs index f416784..a18e509 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/LookupTests.cs @@ -1,4 +1,5 @@ using ClosedXML.Excel; +using ClosedXML.Excel.CalcEngine.Exceptions; using NUnit.Framework; using System; @@ -122,11 +123,11 @@ [Test] public void Vlookup_Exceptions() { - 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.Throws(() => workbook.Evaluate(@"=VLOOKUP("""",Data!$B$2:$I$71,3,FALSE)")); + Assert.Throws(() => workbook.Evaluate(@"=VLOOKUP(50,Data!$B$2:$I$71,3,FALSE)")); + Assert.Throws(() => workbook.Evaluate(@"=VLOOKUP(-1,Data!$B$2:$I$71,2,TRUE)")); - Assert.That(() => workbook.Evaluate(@"=VLOOKUP(-1,Data!$B$2:$I$71,9,TRUE)"), Throws.TypeOf()); + Assert.Throws(() => workbook.Evaluate(@"=VLOOKUP(20,Data!$B$2:$I$71,9,FALSE)")); } } } diff --git a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs index efb58d3..59d0a59 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs @@ -1,5 +1,6 @@ using ClosedXML.Excel; using ClosedXML.Excel.CalcEngine; +using ClosedXML.Excel.CalcEngine.Exceptions; using NUnit.Framework; using System; using System.Globalization; @@ -20,13 +21,13 @@ [Test] public void Char_Empty_Input_String() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Char("""")"), Throws.TypeOf()); + Assert.Throws(() => XLWorkbook.EvaluateExpr(@"Char("""")")); } [Test] public void Char_Input_Too_Large() { - Assert.That(() => XLWorkbook.EvaluateExpr(@"Char(9797)"), Throws.TypeOf()); + Assert.Throws< CellValueException>(() => XLWorkbook.EvaluateExpr(@"Char(9797)")); } [Test] diff --git a/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs b/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs index 4d0329f..686c1f4 100644 --- a/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs +++ b/ClosedXML_Tests/Excel/Coordinates/XLAddressTests.cs @@ -9,13 +9,14 @@ [Test] public void ToStringTest() { - IXLWorksheet ws = new XLWorkbook().Worksheets.Add("Sheet1"); + var ws = new XLWorkbook().Worksheets.Add("Sheet1"); IXLAddress address = ws.Cell(1, 1).Address; Assert.AreEqual("A1", address.ToString()); Assert.AreEqual("A1", address.ToString(XLReferenceStyle.A1)); Assert.AreEqual("R1C1", address.ToString(XLReferenceStyle.R1C1)); Assert.AreEqual("A1", address.ToString(XLReferenceStyle.Default)); + Assert.AreEqual("Sheet1!A1", address.ToString(XLReferenceStyle.Default, true)); Assert.AreEqual("A1", address.ToStringRelative()); Assert.AreEqual("Sheet1!A1", address.ToStringRelative(true)); @@ -39,6 +40,7 @@ Assert.AreEqual("A1", address.ToString(XLReferenceStyle.A1)); Assert.AreEqual("R1C1", address.ToString(XLReferenceStyle.R1C1)); Assert.AreEqual("A1", address.ToString(XLReferenceStyle.Default)); + Assert.AreEqual("'Sheet 1'!A1", address.ToString(XLReferenceStyle.Default, true)); Assert.AreEqual("A1", address.ToStringRelative()); Assert.AreEqual("'Sheet 1'!A1", address.ToStringRelative(true)); diff --git a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs index 16ac2a1..3ff4c55 100644 --- a/ClosedXML_Tests/Excel/Misc/FormulaTests.cs +++ b/ClosedXML_Tests/Excel/Misc/FormulaTests.cs @@ -1,4 +1,5 @@ using ClosedXML.Excel; +using ClosedXML.Excel.CalcEngine.Exceptions; using NUnit.Framework; using System; using System.Linq; @@ -173,5 +174,17 @@ actual = XLWorkbook.EvaluateExpr("+MID(\"This is a test\", 6, 2)"); Assert.AreEqual("is", actual); } + + [Test] + public void FormulasWithErrors() + { + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#REF!)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#VALUE!)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#DIV/0!)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#NAME?)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#N/A)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#NULL!)")); + Assert.Throws(() => XLWorkbook.EvaluateExpr("YEAR(#NUM!)")); + } } } diff --git a/ClosedXML_Tests/Excel/Misc/HyperlinkTests.cs b/ClosedXML_Tests/Excel/Misc/HyperlinkTests.cs new file mode 100644 index 0000000..95b2431 --- /dev/null +++ b/ClosedXML_Tests/Excel/Misc/HyperlinkTests.cs @@ -0,0 +1,32 @@ +using ClosedXML.Excel; +using NUnit.Framework; + +namespace ClosedXML_Tests.Excel.Misc +{ + [TestFixture] + public class HyperlinkTests + { + [Test] + public void TestHyperlinks() + { + using (var wb = new XLWorkbook()) + { + var ws1 = wb.Worksheets.Add("Sheet1"); + var ws2 = wb.Worksheets.Add("Sheet2"); + + var targetCell = ws2.Cell("A1"); + var targetRange = ws2.Range("A1", "B1"); + + var linkCell1 = ws1.Cell("A1"); + linkCell1.Value = "Link to IXLCell"; + linkCell1.Hyperlink = new XLHyperlink(targetCell); + Assert.AreEqual("Sheet2!A1", linkCell1.Hyperlink.InternalAddress); + + var linkRange1 = ws1.Cell("A2"); + linkRange1.Value = "Link to IXLRangeBase"; + linkRange1.Hyperlink = new XLHyperlink(targetRange); + Assert.AreEqual("Sheet2!A1:B1", linkRange1.Hyperlink.InternalAddress); + } + } + } +} diff --git a/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs b/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs index 669e811..c103c6e 100644 --- a/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/XLRangeAddressTests.cs @@ -13,6 +13,7 @@ IXLRangeAddress address = ws.Cell(1, 1).AsRange().RangeAddress; Assert.AreEqual("A1:A1", address.ToString()); + Assert.AreEqual("Sheet1!R1C1:R1C1", address.ToString(XLReferenceStyle.R1C1, true)); Assert.AreEqual("A1:A1", address.ToStringRelative()); Assert.AreEqual("Sheet1!A1:A1", address.ToStringRelative(true)); @@ -33,6 +34,7 @@ IXLRangeAddress address = ws.Cell(1, 1).AsRange().RangeAddress; Assert.AreEqual("A1:A1", address.ToString()); + Assert.AreEqual("'Sheet 1'!R1C1:R1C1", address.ToString(XLReferenceStyle.R1C1, true)); Assert.AreEqual("A1:A1", address.ToStringRelative()); Assert.AreEqual("'Sheet 1'!A1:A1", address.ToStringRelative(true)); diff --git a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs index 0918e5a..76f612e 100644 --- a/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs +++ b/ClosedXML_Tests/Excel/Worksheets/XLWorksheetTests.cs @@ -1,6 +1,7 @@ using ClosedXML.Excel; using NUnit.Framework; using System; +using System.IO; using System.Linq; namespace ClosedXML_Tests @@ -156,8 +157,40 @@ Assert.AreEqual("sheet2", ws2.Name); Assert.Throws(() => ws1.Name = "SHEET2"); + } + } + [Test] + public void HideWorksheet() + { + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook()) + { + wb.Worksheets.Add("VisibleSheet"); + wb.Worksheets.Add("HiddenSheet").Hide(); + wb.SaveAs(ms); + } + // unhide the hidden sheet + using (var wb = new XLWorkbook(ms)) + { + Assert.AreEqual(XLWorksheetVisibility.Visible, wb.Worksheet("VisibleSheet").Visibility); + Assert.AreEqual(XLWorksheetVisibility.Hidden, wb.Worksheet("HiddenSheet").Visibility); + + var ws = wb.Worksheet("HiddenSheet"); + ws.Unhide().Name = "NoAlsoVisible"; + + Assert.AreEqual(XLWorksheetVisibility.Visible, ws.Visibility); + + wb.Save(); + } + + using (var wb = new XLWorkbook(ms)) + { + Assert.AreEqual(XLWorksheetVisibility.Visible, wb.Worksheet("VisibleSheet").Visibility); + Assert.AreEqual(XLWorksheetVisibility.Visible, wb.Worksheet("NoAlsoVisible").Visibility); + } } } } diff --git a/ClosedXML_Tests/Resource/Examples/Columns/DeletingColumns.xlsx b/ClosedXML_Tests/Resource/Examples/Columns/DeletingColumns.xlsx index bac6e30..e2ac29f 100644 --- a/ClosedXML_Tests/Resource/Examples/Columns/DeletingColumns.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Columns/DeletingColumns.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Comments/AddingComments.xlsx b/ClosedXML_Tests/Resource/Examples/Comments/AddingComments.xlsx index 3879998..5c9ae0a 100644 --- a/ClosedXML_Tests/Resource/Examples/Comments/AddingComments.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Comments/AddingComments.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsBlank.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsBlank.xlsx index 80be4ab..e57b38d 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsBlank.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFIsBlank.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotBlank.xlsx b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotBlank.xlsx index 5f4f6ce..e233171 100644 --- a/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotBlank.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ConditionalFormatting/CFNotBlank.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageAnchors.xlsx b/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageAnchors.xlsx index 53aa802..d2a90ba 100644 --- a/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageAnchors.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageAnchors.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageFormats.xlsx b/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageFormats.xlsx index 237d910..3ec78e0 100644 --- a/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageFormats.xlsx +++ b/ClosedXML_Tests/Resource/Examples/ImageHandling/ImageFormats.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/BlankCells.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/BlankCells.xlsx index 21ea101..3f4195a 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/BlankCells.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/BlankCells.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx index 87f74be..d7b9afd 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 5d0b538..f451273 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/HideUnhide.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/HideUnhide.xlsx index a3c6ea5..1f99477 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/HideUnhide.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/HideUnhide.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx index 222ba7b..d724e36 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/Hyperlinks.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx index c8ae77a..d9d1a57 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/Misc/MergeMoves.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx index 9839a6f..2cbaac9 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx index 81bc2a4..a1aa8d0 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/ShiftingFormulas.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index 7c0b68e..369bb0c 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/Examples/Ranges/ClearingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx index c1c9431..5e18500 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingRows.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingRows.xlsx index 6cdd101..9588f7d 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingRows.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/InsertingDeletingRows.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/SelectingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/SelectingRanges.xlsx index a33e2a6..adecfee 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/SelectingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/SelectingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx index 3f1bafd..453328f 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx index 8b26d86..bd2c3d6 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx index 44fbbbe..037fc86 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/WalkingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/WalkingRanges.xlsx index 4a705f7..e12dfc5 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/WalkingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/WalkingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/UsingPhonetics.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/UsingPhonetics.xlsx new file mode 100644 index 0000000..86fe3e6 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Styles/UsingPhonetics.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx index 212cbfe..89de679 100644 --- a/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx index d15dffc..dad3954 100644 --- a/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx Binary files differ