diff --git a/.github/ISSUE_TEMPLATE.md b/.github/ISSUE_TEMPLATE.md new file mode 100644 index 0000000..cfa55f4 --- /dev/null +++ b/.github/ISSUE_TEMPLATE.md @@ -0,0 +1,14 @@ +**Do you want to request a *feature* or report a *bug*?** +- [x] Bug +- [ ] Feature + +**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.** + +**What is the expected behavior or new feature?** + +**Did this work in previous versions of our tool? Which versions?** + +- [ ] I attached a sample spreadsheet. (You can drag files on to this issue) diff --git a/.github/PULL_REQUEST_TEMPLATE.md b/.github/PULL_REQUEST_TEMPLATE.md new file mode 100644 index 0000000..891ee85 --- /dev/null +++ b/.github/PULL_REQUEST_TEMPLATE.md @@ -0,0 +1,13 @@ +Fixes # . + +Changes proposed in this pull request: + - + - + - + +How did I test this code: + - + - + - + +- [ ] I attached a sample spreadsheet. (You can drag files on to this pull request) diff --git a/ClosedXML.sln b/ClosedXML.sln index 5edd50b..4104ff4 100644 --- a/ClosedXML.sln +++ b/ClosedXML.sln @@ -18,6 +18,12 @@ EndProject Project("{FAE04EC0-301F-11D3-BF4B-00C04F79EFBC}") = "ClosedXML_Tests", "ClosedXML_Tests\ClosedXML_Tests.csproj", "{09B066ED-E4A7-4545-A1A4-FF03DD524BDF}" EndProject +Project("{2150E333-8FDC-42A3-9474-1A3956D46DE8}") = ".github", ".github", "{073CFB1C-43DC-4ADC-9D12-BB8D7B10C099}" + ProjectSection(SolutionItems) = preProject + .github\ISSUE_TEMPLATE.md = .github\ISSUE_TEMPLATE.md + .github\PULL_REQUEST_TEMPLATE.md = .github\PULL_REQUEST_TEMPLATE.md + EndProjectSection +EndProject Global GlobalSection(SolutionConfigurationPlatforms) = preSolution Debug|Any CPU = Debug|Any CPU diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index 9bdfd6d..8b9ecc4 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -62,6 +62,7 @@ + diff --git a/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs new file mode 100644 index 0000000..b5853b8 --- /dev/null +++ b/ClosedXML/Excel/CalcEngine/CalcEngineHelpers.cs @@ -0,0 +1,74 @@ +using System; +using System.Diagnostics; +using System.Text.RegularExpressions; + +namespace ClosedXML.Excel.CalcEngine +{ + internal class CalcEngineHelpers + { + internal static bool ValueSatisfiesCriteria(object value, object criteria, CalcEngine ce) + { + // safety... + if (value == null) + { + return false; + } + + // if criteria is a number, straight comparison + if (criteria is double) + { + if (value is Double) + return (double)value == (double)criteria; + Double dValue; + return Double.TryParse(value.ToString(), out dValue) && dValue == (double)criteria; + } + + // convert criteria to string + var cs = criteria as string; + if (!string.IsNullOrEmpty(cs)) + { + // if criteria is an expression (e.g. ">20"), use calc engine + if (cs[0] == '=' || cs[0] == '<' || cs[0] == '>') + { + // build expression + var expression = string.Format("{0}{1}", value, cs); + + // add quotes if necessary + var pattern = @"(\w+)(\W+)(\w+)"; + var m = Regex.Match(expression, pattern); + if (m.Groups.Count == 4) + { + double d; + if (!double.TryParse(m.Groups[1].Value, out d) || + !double.TryParse(m.Groups[3].Value, out d)) + { + expression = string.Format("\"{0}\"{1}\"{2}\"", + m.Groups[1].Value, + m.Groups[2].Value, + m.Groups[3].Value); + } + } + + // evaluate + return (bool)ce.Evaluate(expression); + } + + // if criteria is a regular expression, use regex + if (cs.IndexOf('*') > -1) + { + var pattern = cs.Replace(@"\", @"\\"); + pattern = pattern.Replace(".", @"\"); + pattern = pattern.Replace("*", ".*"); + return Regex.IsMatch(value.ToString(), pattern, RegexOptions.IgnoreCase); + } + + // straight string comparison + return string.Equals(value.ToString(), cs, StringComparison.OrdinalIgnoreCase); + } + + // should never get here? + Debug.Assert(false, "failed to evaluate criteria in SumIf"); + return false; + } + } +} diff --git a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs index 4c33e64..48a2bdd 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/MathTrig.cs @@ -1,10 +1,7 @@ using System; -using System.Diagnostics; using System.Collections; using System.Collections.Generic; -using System.Text; using System.Linq; -using System.Text.RegularExpressions; using ClosedXML.Excel.CalcEngine.Functions; namespace ClosedXML.Excel.CalcEngine @@ -221,7 +218,7 @@ var tally = new Tally(); for (var i = 0; i < Math.Min(rangeValues.Count, sumRangeValues.Count); i++) { - if (ValueSatisfiesCriteria(rangeValues[i], criteria, ce)) + if (CalcEngineHelpers.ValueSatisfiesCriteria(rangeValues[i], criteria, ce)) { tally.AddValue(sumRangeValues[i]); } @@ -231,71 +228,6 @@ return tally.Sum(); } - private static bool ValueSatisfiesCriteria(object value, object criteria, CalcEngine ce) - { - // safety... - if (value == null) - { - return false; - } - - // if criteria is a number, straight comparison - if (criteria is double) - { - if (value is Double) - return (double) value == (double) criteria; - Double dValue; - return Double.TryParse(value.ToString(), out dValue) && dValue == (double) criteria; - } - - // convert criteria to string - var cs = criteria as string; - if (!string.IsNullOrEmpty(cs)) - { - // if criteria is an expression (e.g. ">20"), use calc engine - if (cs[0] == '=' || cs[0] == '<' || cs[0] == '>') - { - // build expression - var expression = string.Format("{0}{1}", value, cs); - - // add quotes if necessary - var pattern = @"(\w+)(\W+)(\w+)"; - var m = Regex.Match(expression, pattern); - if (m.Groups.Count == 4) - { - double d; - if (!double.TryParse(m.Groups[1].Value, out d) || - !double.TryParse(m.Groups[3].Value, out d)) - { - expression = string.Format("\"{0}\"{1}\"{2}\"", - m.Groups[1].Value, - m.Groups[2].Value, - m.Groups[3].Value); - } - } - - // evaluate - return (bool) ce.Evaluate(expression); - } - - // if criteria is a regular expression, use regex - if (cs.IndexOf('*') > -1) - { - var pattern = cs.Replace(@"\", @"\\"); - pattern = pattern.Replace(".", @"\"); - pattern = pattern.Replace("*", ".*"); - return Regex.IsMatch(value.ToString(), pattern, RegexOptions.IgnoreCase); - } - - // straight string comparison - return string.Equals(value.ToString(), cs, StringComparison.OrdinalIgnoreCase); - } - - // should never get here? - Debug.Assert(false, "failed to evaluate criteria in SumIf"); - return false; - } - private static object Tan(List p) { return Math.Tan(p[0]); @@ -609,9 +541,9 @@ case 1: return tally.Average(); case 2: - return tally.Count(); + return tally.Count(true); case 3: - return tally.CountA(); + return tally.Count(false); case 4: return tally.Max(); case 5: @@ -636,7 +568,7 @@ private static object SumSq(List p) { var t = new Tally(p); - return t.Numerics().Sum(v => Math.Pow(v, 2)); + return t.NumericValues().Sum(v => Math.Pow(v, 2)); } private static object MMult(List p) @@ -700,4 +632,4 @@ return m.Invert().mat; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs index 973bb90..34b14f1 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Statistical.cs @@ -10,7 +10,7 @@ public static void Register(CalcEngine ce) { //ce.RegisterFunction("AVEDEV", AveDev, 1, int.MaxValue); - ce.RegisterFunction("AVERAGE", 1, int.MaxValue, Average); + ce.RegisterFunction("AVERAGE", 1, int.MaxValue, Average); // Returns the average (arithmetic mean) of the arguments ce.RegisterFunction("AVERAGEA", 1, int.MaxValue, AverageA); //BETADIST Returns the beta cumulative distribution function //BETAINV Returns the inverse of the cumulative distribution function for a specified beta distribution @@ -22,7 +22,7 @@ //CORREL Returns the correlation coefficient between two data sets ce.RegisterFunction("COUNT", 1, int.MaxValue, Count); ce.RegisterFunction("COUNTA", 1, int.MaxValue, CountA); - ce.RegisterFunction("COUNTBLANK", 1, int.MaxValue, CountBlank); + ce.RegisterFunction("COUNTBLANK", 1, CountBlank); ce.RegisterFunction("COUNTIF", 2, CountIf); //COVAR Returns covariance, the average of the products of paired deviations //CRITBINOM Returns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value @@ -77,6 +77,8 @@ ce.RegisterFunction("STDEVA", 1, int.MaxValue, StDevA); ce.RegisterFunction("STDEVP", 1, int.MaxValue, StDevP); ce.RegisterFunction("STDEVPA", 1, int.MaxValue, StDevPA); + ce.RegisterFunction("STDEV.S", 1, int.MaxValue, StDev); + ce.RegisterFunction("STDEV.P", 1, int.MaxValue, StDevP); //STEYX Returns the standard error of the predicted y-value for each x in the regression //TDIST Returns the Student's t-distribution //TINV Returns the inverse of the Student's t-distribution @@ -87,6 +89,8 @@ ce.RegisterFunction("VARA", 1, int.MaxValue, VarA); ce.RegisterFunction("VARP", 1, int.MaxValue, VarP); ce.RegisterFunction("VARPA", 1, int.MaxValue, VarPA); + ce.RegisterFunction("VAR.S", 1, int.MaxValue, Var); + ce.RegisterFunction("VAR.P", 1, int.MaxValue, VarP); //WEIBULL Returns the Weibull distribution //ZTEST Returns the one-tailed probability-value of a z-test } @@ -131,10 +135,10 @@ } return cnt; } - static bool IsBlank(object value) + internal static bool IsBlank(object value) { - return - value == null || + return + value == null || value is string && ((string)value).Length == 0; } static object CountIf(List p) @@ -144,13 +148,12 @@ var ienum = p[0] as IEnumerable; if (ienum != null) { - var crit = (string)p[1].Evaluate(); + var criteria = (string)p[1].Evaluate(); foreach (var value in ienum) { if (!IsBlank(value)) { - var exp = string.Format("{0}{1}", value, crit); - if ((bool)ce.Evaluate(exp)) + if (CalcEngineHelpers.ValueSatisfiesCriteria(value, criteria, ce)) cnt++; } } @@ -209,7 +212,7 @@ // utility for tallying statistics static Tally GetTally(List p, bool numbersOnly) { - return new Tally(p); + return new Tally(p, numbersOnly); } } } diff --git a/ClosedXML/Excel/CalcEngine/Functions/Tally.cs b/ClosedXML/Excel/CalcEngine/Functions/Tally.cs index f7d76fc..7f5766b 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Tally.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Tally.cs @@ -1,22 +1,38 @@ using System; -using System.Linq; -using System.Collections.Generic; -using System.Net; using System.Collections; +using System.Collections.Generic; +using System.Linq; namespace ClosedXML.Excel.CalcEngine { - internal class Tally: IEnumerable + internal class Tally : IEnumerable { private readonly List _list = new List(); + private readonly bool NumbersOnly; - public Tally(){} + public Tally() + : this(false) + { } + + public Tally(bool numbersOnly) + : this(null, numbersOnly) + { } + public Tally(IEnumerable p) + : this(p, false) + { } + + public Tally(IEnumerable p, bool numbersOnly) { - foreach (var e in p) + if (p != null) { - Add(e); + foreach (var e in p) + { + Add(e); + } } + + this.NumbersOnly = numbersOnly; } public void Add(Expression e) @@ -47,93 +63,74 @@ _list.Add(v); } - public double Count() { return _list.Count; } - public double CountA() + public double Count() { - Double cntA = 0; + return this.Count(this.NumbersOnly); + } + + public double Count(bool numbersOnly) + { + if (numbersOnly) + return NumericValues().Count(); + else + return _list.Where(o => !Statistical.IsBlank(o)).Count(); + } + + public IEnumerable NumericValues() + { + var retVal = new List(); foreach (var value in _list) { + Double tmp; var vEnumerable = value as IEnumerable; - if (vEnumerable == null) - cntA += AddCount(value); + if (vEnumerable == null && Double.TryParse(value.ToString(), out tmp)) + yield return tmp; else { foreach (var v in vEnumerable) { - cntA += AddCount(v); + if (Double.TryParse(v.ToString(), out tmp)) + yield return tmp; break; } } } - return cntA; - } - - private static double AddCount(object value) - { - var strVal = value as String; - if (value != null && (strVal == null || !XLHelper.IsNullOrWhiteSpace(strVal))) - return 1; - return 0; - } - - public List Numerics() - { - List retVal = new List(); - foreach (var value in _list) - { - var vEnumerable = value as IEnumerable; - if (vEnumerable == null) - AddNumericValue(value, retVal); - else - { - foreach (var v in vEnumerable) - { - AddNumericValue(v, retVal); - break; - } - } - } - return retVal; - } - - private static void AddNumericValue(object value, List retVal) - { - Double tmp; - if (Double.TryParse(value.ToString(), out tmp)) - { - retVal.Add(tmp); - } } public double Product() { - var nums = Numerics(); - if (nums.Count == 0) return 0; + var nums = NumericValues(); + if (!nums.Any()) return 0; Double retVal = 1; nums.ForEach(n => retVal *= n); return retVal; } - public double Sum() { return Numerics().Sum(); } + + public double Sum() { return NumericValues().Sum(); } + public double Average() { - return Numerics().Count == 0 ? 0 : Numerics().Average(); + if (NumericValues().Any()) + return NumericValues().Average(); + else + throw new ApplicationException("No values"); } public double Min() { - return Numerics().Count == 0 ? 0 : Numerics().Min(); + return NumericValues().Any() ? NumericValues().Min() : 0; } public double Max() { - return Numerics().Count == 0 ? 0 : Numerics().Max(); + return NumericValues().Any() ? NumericValues().Max() : 0; } public double Range() { - var nums = Numerics(); + var nums = NumericValues(); return nums.Max() - nums.Min(); } @@ -144,40 +141,46 @@ public double VarP() { - var nums = Numerics(); + var nums = NumericValues(); var avg = nums.Average(); var sum2 = nums.Sum(d => d * d); - return nums.Count <= 1 ? 0 : sum2 / nums.Count - avg * avg; + return nums.Count() <= 1 ? 0 : sum2 / nums.Count() - avg * avg; } + public double StdP() { - var nums = Numerics(); + var nums = NumericValues(); var avg = nums.Average(); var sum2 = nums.Sum(d => d * d); - return nums.Count <= 1 ? 0 : Math.Sqrt(sum2 / nums.Count - avg * avg); + return nums.Count() <= 1 ? 0 : Math.Sqrt(sum2 / nums.Count() - avg * avg); } + public double Var() { - var nums = Numerics(); + var nums = NumericValues(); var avg = nums.Average(); var sum2 = nums.Sum(d => d * d); - return nums.Count <= 1 ? 0 : (sum2 / nums.Count - avg * avg) * nums.Count / (nums.Count - 1); + return nums.Count() <= 1 ? 0 : (sum2 / nums.Count() - avg * avg) * nums.Count() / (nums.Count() - 1); } + public double Std() { - var values = Numerics(); + var values = NumericValues(); double ret = 0; - if (values.Count > 0) + if (values.Any()) { - //Compute the Average + //Compute the Average double avg = values.Average(); - //Perform the Sum of (value-avg)_2_2 + //Perform the Sum of (value-avg)_2_2 double sum = values.Sum(d => Math.Pow(d - avg, 2)); - //Put it all together + //Put it all together ret = Math.Sqrt((sum) / (values.Count() - 1)); } + else + { + throw new ApplicationException("No values"); + } return ret; - } public IEnumerator GetEnumerator() diff --git a/ClosedXML/Excel/CalcEngine/Functions/Text.cs b/ClosedXML/Excel/CalcEngine/Functions/Text.cs index 8e35edf..b91728c 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Text.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Text.cs @@ -256,6 +256,11 @@ private static object _Text(List p) { + var value = p[0].Evaluate(); + + // Input values of type string don't get any formatting applied. + if (value is string) return value; + var number = (double)p[0]; var format = (string)p[1]; if (string.IsNullOrEmpty(format.Trim())) return ""; @@ -343,4 +348,4 @@ return retVal; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index a65389c..4eff7e8 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -134,7 +134,7 @@ if (HasRichText) return _richText.ToString(); - return XLHelper.IsNullOrWhiteSpace(_cellValue) ? FormulaA1 : _cellValue; + return string.Empty == _cellValue ? FormulaA1 : _cellValue; } } @@ -220,6 +220,7 @@ { FormulaA1 = String.Empty; _richText = null; + var style = GetStyleForRead(); if (value is String || value is char) { _cellValue = value.ToString(); @@ -231,12 +232,14 @@ { _cellValue = value.ToString(); _dataType = XLCellValues.TimeSpan; + if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) Style.NumberFormat.NumberFormatId = 46; } else if (value is DateTime) { _dataType = XLCellValues.DateTime; var dtTest = (DateTime)Convert.ChangeType(value, typeof(DateTime)); + if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) Style.NumberFormat.NumberFormatId = dtTest.Date == dtTest ? 14 : 22; _cellValue = dtTest.ToOADate().ToInvariantString(); @@ -1631,8 +1634,8 @@ val = string.Empty; else if (value is DateTime) val = ((DateTime)value).ToString("o"); - else if (value is double) - val = ((double)value).ToInvariantString(); + else if (value.IsNumber()) + val = Convert.ToDecimal(value).ToInvariantString(); else val = value.ToString(); _richText = null; @@ -1941,7 +1944,7 @@ return columnPart; } - internal void CopyValues(XLCell source) + internal void CopyValuesFrom(XLCell source) { _cellValue = source._cellValue; _dataType = source._dataType; @@ -1973,7 +1976,7 @@ { var source = otherCell as XLCell; // To expose GetFormulaR1C1, etc //var source = castedOtherCell; - CopyValues(source); + CopyValuesFrom(source); SetStyle(source._style ?? source.Worksheet.Workbook.GetStyleById(source._styleCacheId)); diff --git a/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/Excel/Columns/XLColumn.cs index 9ee72db..6a266c6 100644 --- a/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/Excel/Columns/XLColumn.cs @@ -47,8 +47,8 @@ _width = column._width; IsReference = column.IsReference; if (IsReference) - SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); - _collapsed = column._collapsed; + SubscribeToShiftedColumns((range, columnsShifted) => this.WorksheetRangeShiftedColumns(range, columnsShifted)); + _collapsed = column._collapsed; _isHidden = column._isHidden; _outlineLevel = column._outlineLevel; SetStyle(column.GetStyleId()); @@ -299,6 +299,17 @@ { var fontCache = new Dictionary(); Double colMaxWidth = minWidth; + + List autoFilterRows = new List(); + if (this.Worksheet.AutoFilter != null && this.Worksheet.AutoFilter.Range != null) + autoFilterRows.Add(this.Worksheet.AutoFilter.Range.FirstRow().RowNumber()); + + autoFilterRows.AddRange(Worksheet.Tables.Where(t => + t.AutoFilter != null + && t.AutoFilter.Range != null + && !autoFilterRows.Contains(t.AutoFilter.Range.FirstRow().RowNumber())) + .Select(t => t.AutoFilter.Range.FirstRow().RowNumber())); + foreach (XLCell c in Column(startRow, endRow).CellsUsed()) { if (c.IsMerged()) continue; @@ -442,9 +453,7 @@ else thisWidthMax = c.Style.Font.GetWidth(c.GetFormattedString(), fontCache); - if (c.Worksheet.AutoFilter != null - && c.Worksheet.AutoFilter.Range != null - && c.Worksheet.AutoFilter.Range.Contains(c)) + if (autoFilterRows.Contains(c.Address.RowNumber)) thisWidthMax += 2.7148; // Allow room for arrow icon in autofilter diff --git a/ClosedXML/Excel/Comments/XLComment.cs b/ClosedXML/Excel/Comments/XLComment.cs index 83f5846..87cae88 100644 --- a/ClosedXML/Excel/Comments/XLComment.cs +++ b/ClosedXML/Excel/Comments/XLComment.cs @@ -164,7 +164,7 @@ if (pRow > 1) { pRow--; - double prevHeight = cell.CellAbove().WorksheetRow().Height; + double prevHeight = cell.Worksheet.Row(pRow).Height; if (prevHeight > 7) pRowOffset = prevHeight - 7; } @@ -204,4 +204,4 @@ ShapeId = cell.Worksheet.Workbook.ShapeIdManager.GetNext(); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/IXLSheetView.cs b/ClosedXML/Excel/IXLSheetView.cs index eee1ed0..9de885a 100644 --- a/ClosedXML/Excel/IXLSheetView.cs +++ b/ClosedXML/Excel/IXLSheetView.cs @@ -3,27 +3,45 @@ namespace ClosedXML.Excel { public enum XLSheetViewOptions { Normal, PageBreakPreview, PageLayout } + public interface IXLSheetView { /// - /// Gets or sets the row after which the vertical split should take place. - /// - Int32 SplitRow { get; set; } - /// /// Gets or sets the column after which the horizontal split should take place. /// Int32 SplitColumn { get; set; } - //Boolean FreezePanes { get; set; } + /// - /// Freezes the top X rows. + /// Gets or sets the row after which the vertical split should take place. /// - /// The rows to freeze. - void FreezeRows(Int32 rows); + Int32 SplitRow { get; set; } + + XLSheetViewOptions View { get; set; } + /// - /// Freezes the left X columns. + /// Window zoom magnification for current view representing percent values. Horizontal & Vertical scale together. /// - /// The columns to freeze. - void FreezeColumns(Int32 columns); + /// Representing percent values ranging from 10 to 400. + Int32 ZoomScale { get; set; } + + /// + /// Zoom magnification to use when in normal view. Horizontal & Vertical scale together + /// + /// Representing percent values ranging from 10 to 400. + Int32 ZoomScaleNormal { get; set; } + + /// + /// Zoom magnification to use when in page layout view. Horizontal & Vertical scale together. + /// + /// Representing percent values ranging from 10 to 400. + Int32 ZoomScalePageLayoutView { get; set; } + + /// + /// Zoom magnification to use when in page break preview. Horizontal & Vertical scale together. + /// + /// Representing percent values ranging from 10 to 400. + Int32 ZoomScaleSheetLayoutView { get; set; } + /// /// Freezes the specified rows and columns. /// @@ -31,7 +49,18 @@ /// The columns to freeze. void Freeze(Int32 rows, Int32 columns); - XLSheetViewOptions View { get; set; } + /// + /// Freezes the left X columns. + /// + /// The columns to freeze. + void FreezeColumns(Int32 columns); + + //Boolean FreezePanes { get; set; } + /// + /// Freezes the top X rows. + /// + /// The rows to freeze. + void FreezeRows(Int32 rows); IXLSheetView SetView(XLSheetViewOptions value); } diff --git a/ClosedXML/Excel/PageSetup/XLHFItem.cs b/ClosedXML/Excel/PageSetup/XLHFItem.cs index 1f03c36..4fd398e 100644 --- a/ClosedXML/Excel/PageSetup/XLHFItem.cs +++ b/ClosedXML/Excel/PageSetup/XLHFItem.cs @@ -40,7 +40,7 @@ public IXLRichString AddText(String text, XLHFOccurrence occurrence) { - XLRichString richText = new XLRichString(text, XLWorkbook.DefaultStyle.Font, this); + XLRichString richText = new XLRichString(text, this.HeaderFooter.Worksheet.Style.Font, this); var hfText = new XLHFText(richText, this); if (occurrence == XLHFOccurrence.AllPages) diff --git a/ClosedXML/Excel/PivotTables/IXLPivotField.cs b/ClosedXML/Excel/PivotTables/IXLPivotField.cs index 8e69e8b..011e222 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotField.cs @@ -36,6 +36,7 @@ Boolean InsertBlankLines { get; set; } Boolean ShowBlankItems { get; set; } Boolean InsertPageBreaks { get; set; } + Boolean Collapsed { get; set; } IXLPivotField SetCustomName(String value); @@ -48,6 +49,7 @@ IXLPivotField SetInsertBlankLines(); IXLPivotField SetInsertBlankLines(Boolean value); IXLPivotField SetShowBlankItems(); IXLPivotField SetShowBlankItems(Boolean value); IXLPivotField SetInsertPageBreaks(); IXLPivotField SetInsertPageBreaks(Boolean value); + IXLPivotField SetCollapsed(); IXLPivotField SetCollapsed(Boolean value); List SharedStrings { get; set; } } diff --git a/ClosedXML/Excel/PivotTables/XLPivotField.cs b/ClosedXML/Excel/PivotTables/XLPivotField.cs index 84291fd..909db34 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotField.cs @@ -1,12 +1,9 @@ using System; using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { - - public class XLPivotField: IXLPivotField + public class XLPivotField : IXLPivotField { public XLPivotField(string sourceName) { @@ -15,18 +12,60 @@ Subtotals = new List(); } - public String SourceName { get; private set; } - public String CustomName { get; set; } public IXLPivotField SetCustomName(String value) { CustomName = value; return this; } + public String SourceName { get; private set; } + public String CustomName { get; set; } - public List Subtotals { get; private set; } public IXLPivotField AddSubtotal(XLSubtotalFunction value) { Subtotals.Add(value); return this; } - public Boolean IncludeNewItemsInFilter { get; set; } public IXLPivotField SetIncludeNewItemsInFilter() { IncludeNewItemsInFilter = true; return this; } public IXLPivotField SetIncludeNewItemsInFilter(Boolean value) { IncludeNewItemsInFilter = value; return this; } + public IXLPivotField SetCustomName(String value) { CustomName = value; return this; } - public XLPivotLayout Layout { get; set; } public IXLPivotField SetLayout(XLPivotLayout value) { Layout = value; return this; } - public Boolean SubtotalsAtTop { get; set; } public IXLPivotField SetSubtotalsAtTop() { SubtotalsAtTop = true; return this; } public IXLPivotField SetSubtotalsAtTop(Boolean value) { SubtotalsAtTop = value; return this; } - public Boolean RepeatItemLabels { get; set; } public IXLPivotField SetRepeatItemLabels() { RepeatItemLabels = true; return this; } public IXLPivotField SetRepeatItemLabels(Boolean value) { RepeatItemLabels = value; return this; } - public Boolean InsertBlankLines { get; set; } public IXLPivotField SetInsertBlankLines() { InsertBlankLines = true; return this; } public IXLPivotField SetInsertBlankLines(Boolean value) { InsertBlankLines = value; return this; } - public Boolean ShowBlankItems { get; set; } public IXLPivotField SetShowBlankItems() { ShowBlankItems = true; return this; } public IXLPivotField SetShowBlankItems(Boolean value) { ShowBlankItems = value; return this; } - public Boolean InsertPageBreaks { get; set; } public IXLPivotField SetInsertPageBreaks() { InsertPageBreaks = true; return this; } public IXLPivotField SetInsertPageBreaks(Boolean value) { InsertPageBreaks = value; return this; } + public List Subtotals { get; private set; } + + public IXLPivotField AddSubtotal(XLSubtotalFunction value) { Subtotals.Add(value); return this; } + + public Boolean IncludeNewItemsInFilter { get; set; } + + public IXLPivotField SetIncludeNewItemsInFilter() { IncludeNewItemsInFilter = true; return this; } + + public IXLPivotField SetIncludeNewItemsInFilter(Boolean value) { IncludeNewItemsInFilter = value; return this; } + + public XLPivotLayout Layout { get; set; } + + public IXLPivotField SetLayout(XLPivotLayout value) { Layout = value; return this; } + + public Boolean SubtotalsAtTop { get; set; } + + public IXLPivotField SetSubtotalsAtTop() { SubtotalsAtTop = true; return this; } + + public IXLPivotField SetSubtotalsAtTop(Boolean value) { SubtotalsAtTop = value; return this; } + + public Boolean RepeatItemLabels { get; set; } + + public IXLPivotField SetRepeatItemLabels() { RepeatItemLabels = true; return this; } + + public IXLPivotField SetRepeatItemLabels(Boolean value) { RepeatItemLabels = value; return this; } + + public Boolean InsertBlankLines { get; set; } + + public IXLPivotField SetInsertBlankLines() { InsertBlankLines = true; return this; } + + public IXLPivotField SetInsertBlankLines(Boolean value) { InsertBlankLines = value; return this; } + + public Boolean ShowBlankItems { get; set; } + + public IXLPivotField SetShowBlankItems() { ShowBlankItems = true; return this; } + + public IXLPivotField SetShowBlankItems(Boolean value) { ShowBlankItems = value; return this; } + + public Boolean InsertPageBreaks { get; set; } + + public IXLPivotField SetInsertPageBreaks() { InsertPageBreaks = true; return this; } + + public IXLPivotField SetInsertPageBreaks(Boolean value) { InsertPageBreaks = value; return this; } + + public Boolean Collapsed { get; set; } + + public IXLPivotField SetCollapsed() { Collapsed = true; return this; } + + public IXLPivotField SetCollapsed(Boolean value) { Collapsed = value; return this; } public List SharedStrings { get; set; } } diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 60d12d4..8251bcd 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -58,25 +58,25 @@ #endregion - private XLCallbackAction _shiftedRowsAction; + private XLCallbackAction _shiftedRowsAction; protected void SubscribeToShiftedRows(Action action) { if (Worksheet == null || !Worksheet.EventTrackingEnabled) return; - _shiftedRowsAction = new XLCallbackAction(action); + _shiftedRowsAction = new XLCallbackAction(action); - RangeAddress.Worksheet.RangeShiftedRows.Add(_shiftedRowsAction); + RangeAddress.Worksheet.RangeShiftedRows.Add(_shiftedRowsAction); } private XLCallbackAction _shiftedColumnsAction; - protected void SubscribeToShiftedColumns(Action action) + protected void SubscribeToShiftedColumns(Action action) { if (Worksheet == null || !Worksheet.EventTrackingEnabled) return; _shiftedColumnsAction = new XLCallbackAction(action); - RangeAddress.Worksheet.RangeShiftedColumns.Add(_shiftedColumnsAction); + RangeAddress.Worksheet.RangeShiftedColumns.Add(_shiftedColumnsAction); } #region Public properties @@ -178,7 +178,7 @@ dvEmpty.ForEach(dv => Worksheet.DataValidations.Delete(dv)); - var newRanges = new XLRanges {AsRange()}; + var newRanges = new XLRanges { AsRange() }; var dataValidation = new XLDataValidation(newRanges); if (dataValidationToCopy != null) dataValidation.CopyFrom(dataValidationToCopy); @@ -262,7 +262,7 @@ { get { - var retVal = new XLRanges {AsRange()}; + var retVal = new XLRanges { AsRange() }; return retVal; } } @@ -394,7 +394,8 @@ return this; } - public void DeleteComments() { + public void DeleteComments() + { Cells().DeleteComments(); } @@ -427,7 +428,7 @@ public bool Intersects(string rangeAddress) { - using (var range = Worksheet.Range(rangeAddress)) + using (var range = Worksheet.Range(rangeAddress)) return Intersects(range); } @@ -948,7 +949,7 @@ public IXLCells CellsUsed(bool includeFormats) { - var cells = new XLCells(true, includeFormats) {RangeAddress}; + var cells = new XLCells(true, includeFormats) { RangeAddress }; return cells; } @@ -991,14 +992,14 @@ } public IXLRangeColumns InsertColumnsAfter(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) - { - return InsertColumnsAfterInternal(onlyUsedCells, numberOfColumns, formatFromLeft); - } + { + return InsertColumnsAfterInternal(onlyUsedCells, numberOfColumns, formatFromLeft); + } - public void InsertColumnsAfterVoid(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) - { - InsertColumnsAfterInternal(onlyUsedCells, numberOfColumns, formatFromLeft, nullReturn: true); - } + public void InsertColumnsAfterVoid(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) + { + InsertColumnsAfterInternal(onlyUsedCells, numberOfColumns, formatFromLeft, nullReturn: true); + } private IXLRangeColumns InsertColumnsAfterInternal(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true, Boolean nullReturn = false) { @@ -1045,15 +1046,15 @@ return retVal; } - public IXLRangeColumns InsertColumnsBefore(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) - { - return InsertColumnsBeforeInternal(onlyUsedCells, numberOfColumns, formatFromLeft); - } + public IXLRangeColumns InsertColumnsBefore(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) + { + return InsertColumnsBeforeInternal(onlyUsedCells, numberOfColumns, formatFromLeft); + } - public void InsertColumnsBeforeVoid(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) - { - InsertColumnsBeforeInternal(onlyUsedCells, numberOfColumns, formatFromLeft, nullReturn: true); - } + public void InsertColumnsBeforeVoid(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true) + { + InsertColumnsBeforeInternal(onlyUsedCells, numberOfColumns, formatFromLeft, nullReturn: true); + } private IXLRangeColumns InsertColumnsBeforeInternal(Boolean onlyUsedCells, Int32 numberOfColumns, Boolean formatFromLeft = true, Boolean nullReturn = false) { @@ -1088,7 +1089,7 @@ Worksheet.Cell(oldKey); var newCell = new XLCell(Worksheet, newKey, oldCell.GetStyleId()); - newCell.CopyValues(oldCell); + newCell.CopyValuesFrom(oldCell); newCell.FormulaA1 = oldCell.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); @@ -1106,12 +1107,12 @@ int newColumn = c.Address.ColumnNumber + numberOfColumns; var newKey = new XLAddress(Worksheet, c.Address.RowNumber, newColumn, false, false); var newCell = new XLCell(Worksheet, newKey, c.GetStyleId()); - newCell.CopyValues(c); + newCell.CopyValuesFrom(c); if (c.HasDataValidation) { cellsDataValidations.Add(newCell.Address, new DataValidationToCopy - {DataValidation = c.DataValidation, SourceAddress = c.Address}); + { DataValidation = c.DataValidation, SourceAddress = c.Address }); c.DataValidation.Clear(); } newCell.FormulaA1 = c.FormulaA1; @@ -1135,8 +1136,8 @@ //cellsDataValidations.ForEach(kp => Worksheet.Cell(kp.Key).CopyDataValidation(Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation)); Int32 firstRowReturn = RangeAddress.FirstAddress.RowNumber; - Int32 lastRowReturn = RangeAddress.LastAddress.RowNumber ; - Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber ; + Int32 lastRowReturn = RangeAddress.LastAddress.RowNumber; + Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber; Int32 lastColumnReturn = RangeAddress.FirstAddress.ColumnNumber + numberOfColumns - 1; Worksheet.BreakConditionalFormatsIntoCells(cellsToDelete.Except(cellsToInsert.Keys).ToList()); @@ -1184,8 +1185,8 @@ } } - if(nullReturn) - return null; + if (nullReturn) + return null; return rangeToReturn.Columns(); } @@ -1216,18 +1217,18 @@ return retVal; } - public IXLRangeRows InsertRowsBelow(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) - { - return InsertRowsBelowInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: false); - } + public IXLRangeRows InsertRowsBelow(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) + { + return InsertRowsBelowInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: false); + } - public void InsertRowsBelowVoid(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) - { - InsertRowsBelowInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: true); - } + public void InsertRowsBelowVoid(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove = true) + { + InsertRowsBelowInternal(onlyUsedCells, numberOfRows, formatFromAbove, nullReturn: true); + } - private IXLRangeRows InsertRowsBelowInternal(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove, Boolean nullReturn) - { + private IXLRangeRows InsertRowsBelowInternal(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove, Boolean nullReturn) + { int rowCount = RowCount(); int firstRow = RangeAddress.FirstAddress.RowNumber + rowCount; if (firstRow > XLHelper.MaxRowNumber) @@ -1276,22 +1277,22 @@ 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); - } + 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); + } - private IXLRangeRows InsertRowsAboveInternal(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove, Boolean nullReturn) - { + private IXLRangeRows InsertRowsAboveInternal(Boolean onlyUsedCells, Int32 numberOfRows, Boolean formatFromAbove, Boolean nullReturn) + { using (var asRange = AsRange()) foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) { foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !XLHelper.IsNullOrWhiteSpace(c.FormulaA1))) - cell.ShiftFormulaRows(asRange, numberOfRows); + cell.ShiftFormulaRows(asRange, numberOfRows); } var cellsToInsert = new Dictionary(); @@ -1319,7 +1320,7 @@ if (oldCell != null) { var newCell = new XLCell(Worksheet, newKey, oldCell.GetStyleId()); - newCell.CopyValues(oldCell); + newCell.CopyValuesFrom(oldCell); newCell.FormulaA1 = oldCell.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); @@ -1339,12 +1340,12 @@ int newRow = c.Address.RowNumber + numberOfRows; var newKey = new XLAddress(Worksheet, newRow, c.Address.ColumnNumber, false, false); var newCell = new XLCell(Worksheet, newKey, c.GetStyleId()); - newCell.CopyValues(c); + newCell.CopyValuesFrom(c); if (c.HasDataValidation) { cellsDataValidations.Add(newCell.Address, new DataValidationToCopy - {DataValidation = c.DataValidation, SourceAddress = c.Address}); + { DataValidation = c.DataValidation, SourceAddress = c.Address }); c.DataValidation.Clear(); } newCell.FormulaA1 = c.FormulaA1; @@ -1354,19 +1355,19 @@ } } - cellsDataValidations.ForEach(kp => - { - XLCell targetCell; - if(!cellsToInsert.TryGetValue(kp.Key, out targetCell)) - targetCell = Worksheet.Cell(kp.Key); + cellsDataValidations + .ForEach(kp => + { + XLCell targetCell; + if (!cellsToInsert.TryGetValue(kp.Key, out targetCell)) + targetCell = Worksheet.Cell(kp.Key); - targetCell.CopyDataValidation( - Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation); - }); + targetCell.CopyDataValidation( + Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation); + }); 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)); + cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value)); Int32 firstRowReturn = RangeAddress.FirstAddress.RowNumber; @@ -1418,9 +1419,9 @@ } } - // Skip calling .Rows() for performance reasons if required. - if(nullReturn) - return null; + // Skip calling .Rows() for performance reasons if required. + if (nullReturn) + return null; return rangeToReturn.Rows(); } @@ -1498,7 +1499,7 @@ c.Address.ColumnNumber - columnModifier, false, false); var newCell = new XLCell(Worksheet, newKey, c.GetStyleId()); - newCell.CopyValues(c); + newCell.CopyValuesFrom(c); newCell.FormulaA1 = c.FormulaA1; cellsToDelete.Add(c.Address); @@ -1709,7 +1710,7 @@ public IXLAutoFilter SetAutoFilter() { using (var asRange = AsRange()) - return Worksheet.AutoFilter.Set(asRange); + return Worksheet.AutoFilter.Set(asRange); } #region Sort @@ -1976,17 +1977,17 @@ public void Dispose() { - if(_shiftedRowsAction != null) + if (_shiftedRowsAction != null) { RangeAddress.Worksheet.RangeShiftedRows.Remove(_shiftedRowsAction); - _shiftedRowsAction = null; + _shiftedRowsAction = null; } - if(_shiftedColumnsAction != null) - { - RangeAddress.Worksheet.RangeShiftedColumns.Remove(_shiftedColumnsAction); - _shiftedColumnsAction = null; - } + if (_shiftedColumnsAction != null) + { + RangeAddress.Worksheet.RangeShiftedColumns.Remove(_shiftedColumnsAction); + _shiftedColumnsAction = null; + } } public IXLDataValidation SetDataValidation() diff --git a/ClosedXML/Excel/XLSheetView.cs b/ClosedXML/Excel/XLSheetView.cs index 2d7b22d..169bb67 100644 --- a/ClosedXML/Excel/XLSheetView.cs +++ b/ClosedXML/Excel/XLSheetView.cs @@ -2,31 +2,62 @@ namespace ClosedXML.Excel { - internal class XLSheetView: IXLSheetView + internal class XLSheetView : IXLSheetView { - public XLSheetView() { + public XLSheetView() + { View = XLSheetViewOptions.Normal; + + ZoomScale = 100; + ZoomScaleNormal = 100; + ZoomScalePageLayoutView = 100; + ZoomScaleSheetLayoutView = 100; } - public XLSheetView(IXLSheetView sheetView):this() + + public XLSheetView(IXLSheetView sheetView) + : this() { this.SplitRow = sheetView.SplitRow; this.SplitColumn = sheetView.SplitColumn; this.FreezePanes = ((XLSheetView)sheetView).FreezePanes; } - public Int32 SplitRow { get; set; } - public Int32 SplitColumn { get; set; } public Boolean FreezePanes { get; set; } - public void FreezeRows(Int32 rows) + public Int32 SplitColumn { get; set; } + public Int32 SplitRow { get; set; } + public XLSheetViewOptions View { get; set; } + + public int ZoomScale { - SplitRow = rows; - FreezePanes = true; + get { return _zoomScale; } + set + { + _zoomScale = value; + switch (View) + { + case XLSheetViewOptions.Normal: + ZoomScaleNormal = value; + break; + + case XLSheetViewOptions.PageBreakPreview: + ZoomScalePageLayoutView = value; + break; + + case XLSheetViewOptions.PageLayout: + ZoomScaleSheetLayoutView = value; + break; + } + } } - public void FreezeColumns(Int32 columns) - { - SplitColumn = columns; - FreezePanes = true; - } + + public int ZoomScaleNormal { get; set; } + + public int ZoomScalePageLayoutView { get; set; } + + public int ZoomScaleSheetLayoutView { get; set; } + + private int _zoomScale { get; set; } + public void Freeze(Int32 rows, Int32 columns) { SplitRow = rows; @@ -34,8 +65,17 @@ FreezePanes = true; } + public void FreezeColumns(Int32 columns) + { + SplitColumn = columns; + FreezePanes = true; + } - public XLSheetViewOptions View { get; set; } + public void FreezeRows(Int32 rows) + { + SplitRow = rows; + FreezePanes = true; + } public IXLSheetView SetView(XLSheetViewOptions value) { diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index bb05e45..d62bb95 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -180,8 +180,16 @@ using (var reader = OpenXmlReader.Create(wsPart)) { + Type[] ignoredElements = new Type[] + { + typeof(CustomSheetViews) // Custom sheet views contain its own auto filter data, and more, which should be ignored for now + }; + while (reader.Read()) { + while (ignoredElements.Contains(reader.ElementType)) + reader.ReadNextSibling(); + if (reader.ElementType == typeof(SheetFormatProperties)) { var sheetFormatProperties = (SheetFormatProperties)reader.LoadCurrentElement(); @@ -430,7 +438,20 @@ && pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource.WorksheetSource != null) { // TODO: Implement other sources besides worksheetSource (e.g. Table source?) - source = ws.Workbook.Range(pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource.WorksheetSource.Name.Value); + // But for now assume names and references point directly to a range + var wss = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheSource.WorksheetSource; + string rangeAddress = string.Empty; + if (wss.Name != null) + rangeAddress = wss.Name.Value; + else + { + var sourceSheet = wss.Sheet == null ? ws : this.Worksheet(wss.Sheet.Value); + rangeAddress = sourceSheet.Range(wss.Reference.Value).RangeAddress.ToStringRelative(true); + } + + source = this.Range(rangeAddress); + if (source == null) + continue; } if (target != null && source != null) @@ -472,51 +493,106 @@ pt.ErrorValueReplacement = pivotTableDefinition.ErrorCaption.Value; // Row labels - foreach (var rf in pivotTableDefinition.RowFields.Cast()) + if (pivotTableDefinition.RowFields != null) { - if (rf.Index.Value == -2) - pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel); - else if (rf.Index < pivotTableDefinition.PivotFields.Count) + foreach (var rf in pivotTableDefinition.RowFields.Cast()) { - var pf = pivotTableDefinition.PivotFields.ElementAt(rf.Index.Value) as PivotField; - if (pf != null && pf.Name != null) pt.RowLabels.Add(pf.Name.Value); + if (rf.Index < pivotTableDefinition.PivotFields.Count) + { + IXLPivotField pivotField = null; + if (rf.Index.Value == -2) + pivotField = pt.RowLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + else + { + var pf = pivotTableDefinition.PivotFields.ElementAt(rf.Index.Value) as PivotField; + if (pf == null) + continue; + + var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt(rf.Index.Value) as CacheField; + if (pf.Name != null) + pivotField = pt.RowLabels.Add(pf.Name.Value); + else if (cacheField.Name != null) + pivotField = pt.RowLabels.Add(cacheField.Name.Value); + else + continue; + + if (pivotField != null) + { + var items = pf.Items.OfType().Where(i => i.Index != null && i.Index.HasValue); + if (!items.Any(i => i.HideDetails == null || BooleanValue.ToBoolean(i.HideDetails))) + pivotField.SetCollapsed(); + } + } + } } } // Column labels - foreach (var cf in pivotTableDefinition.ColumnFields.Cast()) + if (pivotTableDefinition.ColumnFields != null) { - if (cf.Index.Value == -2) - pt.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel); - - else if (cf.Index < pivotTableDefinition.PivotFields.Count) + foreach (var cf in pivotTableDefinition.ColumnFields.Cast()) { - var pf = pivotTableDefinition.PivotFields.ElementAt(cf.Index.Value) as PivotField; - if (pf != null && pf.Name != null) pt.ColumnLabels.Add(pf.Name.Value); + IXLPivotField pivotField = null; + if (cf.Index.Value == -2) + pivotField = pt.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel); + else if (cf.Index < pivotTableDefinition.PivotFields.Count) + { + var pf = pivotTableDefinition.PivotFields.ElementAt(cf.Index.Value) as PivotField; + if (pf == null) + continue; + + var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt(cf.Index.Value) as CacheField; + if (pf.Name != null) + pivotField = pt.ColumnLabels.Add(pf.Name.Value); + else if (cacheField.Name != null) + pivotField = pt.ColumnLabels.Add(cacheField.Name.Value); + else + continue; + + if (pivotField != null) + { + var items = pf.Items.OfType().Where(i => i.Index != null && i.Index.HasValue); + if (!items.Any(i => i.HideDetails == null || BooleanValue.ToBoolean(i.HideDetails))) + pivotField.SetCollapsed(); + } + } } } // Values - foreach (var df in pivotTableDefinition.DataFields.Cast()) + if (pivotTableDefinition.DataFields != null) { - if ((int)df.Field.Value == -2) - pt.Values.Add(XLConstants.PivotTableValuesSentinalLabel); - - else if (df.Field.Value < pivotTableDefinition.PivotFields.Count) + foreach (var df in pivotTableDefinition.DataFields.Cast()) { - var pf = pivotTableDefinition.PivotFields.ElementAt((int)df.Field.Value) as PivotField; - if (pf != null && pf.Name != null) + IXLPivotValue pivotValue = null; + if ((int)df.Field.Value == -2) + pivotValue = pt.Values.Add(XLConstants.PivotTableValuesSentinalLabel); + else if (df.Field.Value < pivotTableDefinition.PivotFields.Count) { - var pv = pt.Values.Add(pf.Name.Value, df.Name.Value); - if (df.NumberFormatId != null) pv.NumberFormat.SetNumberFormatId((int)df.NumberFormatId.Value); - if (df.Subtotal != null) pv = pv.SetSummaryFormula(df.Subtotal.Value.ToClosedXml()); + var pf = pivotTableDefinition.PivotFields.ElementAt((int)df.Field.Value) as PivotField; + if (pf == null) + continue; + + var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt((int)df.Field.Value) as CacheField; + + if (pf.Name != null) + pivotValue = pt.Values.Add(pf.Name.Value, df.Name.Value); + else if (cacheField.Name != null) + pivotValue = pt.Values.Add(cacheField.Name.Value, df.Name.Value); + else + continue; + + if (df.NumberFormatId != null) pivotValue.NumberFormat.SetNumberFormatId((int)df.NumberFormatId.Value); + if (df.Subtotal != null) pivotValue = pivotValue.SetSummaryFormula(df.Subtotal.Value.ToClosedXml()); if (df.ShowDataAs != null) { - var calculation = pv.Calculation; + var calculation = pivotValue.Calculation; calculation = df.ShowDataAs.Value.ToClosedXml(); - pv = pv.SetCalculation(calculation); + pivotValue = pivotValue.SetCalculation(calculation); } - if (df.BaseField != null) { + + if (df.BaseField != null) + { var col = pt.SourceRange.Column(df.BaseField.Value + 1); var items = col.CellsUsed() @@ -524,8 +600,8 @@ .Skip(1) // Skip header column .Distinct().ToList(); - pv.BaseField = col.FirstCell().GetValue(); - if (df.BaseItem != null) pv.BaseItem = items[(int)df.BaseItem.Value].ToString(); + pivotValue.BaseField = col.FirstCell().GetValue(); + if (df.BaseItem != null) pivotValue.BaseItem = items[(int)df.BaseItem.Value].ToString(); } } } @@ -536,7 +612,6 @@ } #endregion - } #region Comment Helpers @@ -1050,18 +1125,11 @@ { if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) xlCell._cellValue = Double.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); + if (s == null) - { xlCell._dataType = XLCellValues.Number; - } else - { - var numberFormatId = ((CellFormat)(s.CellFormats).ElementAt(styleIndex)).NumberFormatId; - if (numberFormatId == 46U) - xlCell.DataType = XLCellValues.TimeSpan; - else - xlCell._dataType = XLCellValues.Number; - } + xlCell.DataType = GetDataTypeFromCell(xlCell.Style.NumberFormat); } } else if (cell.CellValue != null) @@ -1075,6 +1143,7 @@ var numberFormatId = ((CellFormat)(s.CellFormats).ElementAt(styleIndex)).NumberFormatId; if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToInvariantString(); + if (s.NumberingFormats != null && s.NumberingFormats.Any(nf => ((NumberingFormat)nf).NumberFormatId.Value == numberFormatId)) { @@ -1087,15 +1156,7 @@ else xlCell.Style.NumberFormat.NumberFormatId = Int32.Parse(numberFormatId); - if (!XLHelper.IsNullOrWhiteSpace(xlCell.Style.NumberFormat.Format)) - xlCell._dataType = GetDataTypeFromFormat(xlCell.Style.NumberFormat.Format); - else if ((numberFormatId >= 14 && numberFormatId <= 22) || - (numberFormatId >= 45 && numberFormatId <= 47)) - xlCell._dataType = XLCellValues.DateTime; - else if (numberFormatId == 49) - xlCell._dataType = XLCellValues.Text; - else - xlCell._dataType = XLCellValues.Number; + xlCell.DataType = GetDataTypeFromCell(xlCell.Style.NumberFormat); } } } @@ -1360,7 +1421,29 @@ } } - private static XLCellValues GetDataTypeFromFormat(String format) + private static XLCellValues GetDataTypeFromCell(IXLNumberFormat numberFormat) + { + var numberFormatId = numberFormat.NumberFormatId; + if (numberFormatId == 46U) + return XLCellValues.TimeSpan; + else if ((numberFormatId >= 14 && numberFormatId <= 22) || + (numberFormatId >= 45 && numberFormatId <= 47)) + return XLCellValues.DateTime; + else if (numberFormatId == 49) + return XLCellValues.Text; + else + { + if (!XLHelper.IsNullOrWhiteSpace(numberFormat.Format)) + { + var dataType = GetDataTypeFromFormat(numberFormat.Format); + return dataType.HasValue ? dataType.Value : XLCellValues.Number; + } + else + return XLCellValues.Number; + } + } + + private static XLCellValues? GetDataTypeFromFormat(String format) { int length = format.Length; String f = format.ToLower(); @@ -1374,7 +1457,7 @@ else if (c == 'y' || c == 'm' || c == 'd' || c == 'h' || c == 's') return XLCellValues.DateTime; } - return XLCellValues.Text; + return null; } private static void LoadAutoFilter(AutoFilter af, XLWorksheet ws) @@ -1757,7 +1840,6 @@ if (sheetProperty.OutlineProperties != null) { - if (sheetProperty.OutlineProperties.SummaryBelow != null) { ws.Outline.SummaryVLocation = sheetProperty.OutlineProperties.SummaryBelow @@ -1917,8 +1999,16 @@ ws.Cell(selection.ActiveCell).SetActive(); } - var pane = sheetView.Elements().FirstOrDefault(); + if (sheetView.ZoomScale != null) + ws.SheetView.ZoomScale = (int)UInt32Value.ToUInt32(sheetView.ZoomScale); + if (sheetView.ZoomScaleNormal != null) + ws.SheetView.ZoomScaleNormal = (int)UInt32Value.ToUInt32(sheetView.ZoomScaleNormal); + if (sheetView.ZoomScalePageLayoutView != null) + ws.SheetView.ZoomScalePageLayoutView = (int)UInt32Value.ToUInt32(sheetView.ZoomScalePageLayoutView); + if (sheetView.ZoomScaleSheetLayoutView != null) + ws.SheetView.ZoomScaleSheetLayoutView = (int)UInt32Value.ToUInt32(sheetView.ZoomScaleSheetLayoutView); + var pane = sheetView.Elements().FirstOrDefault(); if (pane == null) return; if (pane.State == null || diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 0c40d9d..9a99f83 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -43,6 +43,7 @@ using Run = DocumentFormat.OpenXml.Spreadsheet.Run; using RunProperties = DocumentFormat.OpenXml.Spreadsheet.RunProperties; using VerticalTextAlignment = DocumentFormat.OpenXml.Spreadsheet.VerticalTextAlignment; +using System.Threading; namespace ClosedXML.Excel { @@ -82,8 +83,20 @@ private bool Validate(SpreadsheetDocument package) { - var validator = new OpenXmlValidator(); - var errors = validator.Validate(package); + var backupCulture = Thread.CurrentThread.CurrentCulture; + + IEnumerable errors; + try + { + Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture; + var validator = new OpenXmlValidator(); + errors = validator.Validate(package); + } + finally + { + Thread.CurrentThread.CurrentCulture = backupCulture; + } + if (errors.Any()) { var message = string.Join("\r\n", errors.Select(e => string.Format("{0} in {1}", e.Description, e.Path.XPath)).ToArray()); @@ -225,6 +238,7 @@ // 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() .SelectMany(ws => ws.Tables.Cast()) @@ -276,8 +290,9 @@ if (worksheet.Internals.CellsCollection.GetCells(c => c.HasComment).Any()) { + var id = context.RelIdGenerator.GetNext(RelType.Workbook); var worksheetCommentsPart = - worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); + worksheetPart.AddNewPart(id); GenerateWorksheetCommentsPartContent(worksheetCommentsPart, worksheet); @@ -302,6 +317,13 @@ { GeneratePivotTables(workbookPart, worksheetPart, worksheet, context); } + + // Remove any orphaned references - maybe more types? + foreach (var orphan in worksheetPart.Worksheet.OfType().Where(lg => !worksheetPart.Parts.Any(p => p.RelationshipId == lg.Id))) + worksheetPart.Worksheet.RemoveChild(orphan); + + foreach (var orphan in worksheetPart.Worksheet.OfType().Where(d => !worksheetPart.Parts.Any(p => p.RelationshipId == d.Id))) + worksheetPart.Worksheet.RemoveChild(orphan); } // Remove empty pivot cache part @@ -1983,7 +2005,7 @@ ShowDataTips = GetBooleanValue(pt.ShowContextualTooltips, true), ShowMemberPropertyTips = GetBooleanValue(pt.ShowPropertiesInTooltips, true), ShowHeaders = GetBooleanValue(pt.DisplayCaptionsAndDropdowns, true), - GridDropZones = GetBooleanValue(pt.ClassicPivotTableLayout, true), + GridDropZones = GetBooleanValue(pt.ClassicPivotTableLayout, false), ShowEmptyRow = GetBooleanValue(pt.ShowEmptyItemsOnRows, true), ShowEmptyColumn = GetBooleanValue(pt.ShowEmptyItemsOnColumns, true), ShowItems = GetBooleanValue(pt.DisplayItemLabels, true), @@ -2080,14 +2102,17 @@ foreach (var xlpf in pt.Fields) { + IXLPivotField labelField = null; var pf = new PivotField { ShowAll = false, Name = xlpf.CustomName }; if (pt.RowLabels.Any(p => p.SourceName == xlpf.SourceName)) { + labelField = pt.RowLabels.Single(p => p.SourceName == xlpf.SourceName); pf.Axis = PivotTableAxisValues.AxisRow; } else if (pt.ColumnLabels.Any(p => p.SourceName == xlpf.SourceName)) { + labelField = pt.ColumnLabels.Single(p => p.SourceName == xlpf.SourceName); pf.Axis = PivotTableAxisValues.AxisColumn; } else if (pt.ReportFilters.Any(p => p.SourceName == xlpf.SourceName)) @@ -2097,10 +2122,9 @@ pf.Axis = PivotTableAxisValues.AxisPage; pageFields.AppendChild(new PageField { Hierarchy = -1, Field = pt.Fields.IndexOf(xlpf) }); } - else if (pt.Values.Any(p => p.SourceName == xlpf.SourceName)) - { + + if (pt.Values.Any(p => p.SourceName == xlpf.SourceName)) pf.DataField = true; - } var fieldItems = new Items(); @@ -2108,7 +2132,10 @@ { for (uint i = 0; i < xlpf.SharedStrings.Count; i++) { - fieldItems.AppendChild(new Item { Index = i }); + var item = new Item { Index = i }; + if (labelField != null && labelField.Collapsed) + item.HideDetails = BooleanValue.FromBoolean(false); + fieldItems.AppendChild(item); } } @@ -2607,7 +2634,15 @@ // To determine the default workbook style, we look for the style with builtInId = 0 (I hope that is the correct approach) UInt32 defaultFormatId; if (workbookStylesPart.Stylesheet.CellStyles.Elements().Any(c => c.BuiltinId != null && c.BuiltinId.HasValue && c.BuiltinId.Value == 0)) - defaultFormatId = workbookStylesPart.Stylesheet.CellStyles.Elements().Single(c => c.BuiltinId != null && c.BuiltinId.HasValue && c.BuiltinId.Value == 0).FormatId.Value; + { + // Possible to have duplicate default cell styles - occurs when file gets saved under different cultures. + // We prefer the style that is named Normal + var normalCellStyles = workbookStylesPart.Stylesheet.CellStyles.Elements() + .Where(c => c.BuiltinId != null && c.BuiltinId.HasValue && c.BuiltinId.Value == 0) + .OrderBy(c => c.Name != null && c.Name.HasValue && c.Name.Value == "Normal"); + + defaultFormatId = normalCellStyles.Last().FormatId.Value; + } else if (workbookStylesPart.Stylesheet.CellStyles.Elements().Any()) defaultFormatId = workbookStylesPart.Stylesheet.CellStyles.Elements().Max(c => c.FormatId.Value) + 1; else @@ -3647,6 +3682,26 @@ sheetView.Append(selection); } + if (xlWorksheet.SheetView.ZoomScale == 100) + sheetView.ZoomScale = null; + else + sheetView.ZoomScale = (UInt32)Math.Max(10, Math.Min(400, xlWorksheet.SheetView.ZoomScale)); + + if (xlWorksheet.SheetView.ZoomScaleNormal == 100) + sheetView.ZoomScaleNormal = null; + else + sheetView.ZoomScaleNormal = (UInt32)Math.Max(10, Math.Min(400, xlWorksheet.SheetView.ZoomScaleNormal)); + + if (xlWorksheet.SheetView.ZoomScalePageLayoutView == 100) + sheetView.ZoomScalePageLayoutView = null; + else + sheetView.ZoomScalePageLayoutView = (UInt32)Math.Max(10, Math.Min(400, xlWorksheet.SheetView.ZoomScalePageLayoutView)); + + if (xlWorksheet.SheetView.ZoomScaleSheetLayoutView == 100) + sheetView.ZoomScaleSheetLayoutView = null; + else + sheetView.ZoomScaleSheetLayoutView = (UInt32)Math.Max(10, Math.Min(400, xlWorksheet.SheetView.ZoomScaleSheetLayoutView)); + #endregion var maxOutlineColumn = 0; @@ -4689,8 +4744,7 @@ private static double GetColumnWidth(double columnWidth) { - var retVal = columnWidth + ColumnWidthOffset; - return retVal > 0 ? retVal : 0; + return Math.Min(255.0, Math.Max(0.0, columnWidth + ColumnWidthOffset)); } private static void UpdateColumn(Column column, Columns columns, Dictionary sheetColumnsByMin) diff --git a/ClosedXML/Extensions.cs b/ClosedXML/Extensions.cs index b5c8ae2..18518d9 100644 --- a/ClosedXML/Extensions.cs +++ b/ClosedXML/Extensions.cs @@ -20,7 +20,7 @@ // Adds the .ForEach method to all IEnumerables - private static readonly char[] hexDigits = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F' }; + private static readonly char[] hexDigits = { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F'}; public static String ToHex(this Color color) { @@ -75,10 +75,10 @@ HashSet distinctItems = new HashSet(); foreach (var item in source) { - if (!distinctItems.Add(item)) - { - return true; - } + if (!distinctItems.Add(item)) + { + return true; + } } return false; } @@ -203,6 +203,20 @@ } + public static class DecimalExtensions + { + //All numbers are stored in XL files as invarient culture this is just a easy helper + public static String ToInvariantString(this Decimal value) + { + return value.ToString(CultureInfo.InvariantCulture); + } + + public static Decimal SaveRound(this Decimal value) + { + return Math.Round(value, 6); + } + } + public static class DoubleExtensions { //All numbers are stored in XL files as invarient culture this is just a easy helper @@ -252,7 +266,7 @@ if (font.Bold) fontStyle |= FontStyle.Bold; if (font.Italic) fontStyle |= FontStyle.Italic; if (font.Strikethrough) fontStyle |= FontStyle.Strikeout; - if (font.Underline != XLFontUnderlineValues.None) fontStyle |= FontStyle.Underline; + if (font.Underline != XLFontUnderlineValues.None ) fontStyle |= FontStyle.Underline; return fontStyle; } @@ -311,6 +325,24 @@ } } + public static class ObjectExtensions + { + public static bool IsNumber(this object value) + { + return value is sbyte + || value is byte + || value is short + || value is ushort + || value is int + || value is uint + || value is long + || value is ulong + || value is float + || value is double + || value is decimal; + } + } + public static class TypeExtensions { public static bool IsNumber(this Type type) diff --git a/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML_Examples/ClosedXML_Examples.csproj index 9bdf8de..155d094 100644 --- a/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -75,6 +75,7 @@ + diff --git a/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML_Examples/Creating/CreateFiles.cs index 694109c..64b2ce4 100644 --- a/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML_Examples/Creating/CreateFiles.cs @@ -90,6 +90,7 @@ new WalkingRanges().Create(Path.Combine(path, "CellMoves.xlsx")); new AddingComments().Create(Path.Combine(path, "AddingComments.xlsx")); new PivotTables().Create(Path.Combine(path, "PivotTables.xlsx")); + new SheetViews().Create(Path.Combine(path, "SheetViews.xlsx")); } } } diff --git a/ClosedXML_Examples/Misc/AdjustToContents.cs b/ClosedXML_Examples/Misc/AdjustToContents.cs index c35304d..24c17c6 100644 --- a/ClosedXML_Examples/Misc/AdjustToContents.cs +++ b/ClosedXML_Examples/Misc/AdjustToContents.cs @@ -1,145 +1,113 @@ -using System; -using ClosedXML.Excel; - +using ClosedXML.Excel; +using System; namespace ClosedXML_Examples.Misc { public class AdjustToContents : IXLExample { - #region Variables - - // Public - - // Private - - - #endregion - - #region Properties - - // Public - - // Private - - // Override - - - #endregion - - #region Events - - // Public - - // Private - - // Override - - - #endregion - - #region Methods - // Public public void Create(String filePath) { - var wb = new XLWorkbook(); - var ws = wb.Worksheets.Add("Adjust To Contents"); - - // Set some values with different font sizes - ws.Cell(1, 1).Value = "Tall Row"; - ws.Cell(1, 1).Style.Font.FontSize = 30; - ws.Cell(2, 1).Value = "Very Wide Column"; - ws.Cell(2, 1).Style.Font.FontSize = 20; - - // Adjust column width - ws.Column(1).AdjustToContents(); - - // Adjust row heights - ws.Rows(1, 2).AdjustToContents(); - - // You can also adjust all rows/columns in one shot - // ws.Rows().AdjustToContents(); - // ws.Columns().AdjustToContents(); - - - // We'll now select which cells should be used for calculating the - // column widths (same method applies for row heights) - - // Set the values - ws.Cell(4, 2).Value = "Width ignored because calling column.AdjustToContents(5, 7)"; - ws.Cell(5, 2).Value = "Short text"; - ws.Cell(6, 2).Value = "Width ignored because it's part of a merge"; - ws.Range(6, 2, 6, 4).Merge(); - ws.Cell(7, 2).Value = "Width should adjust to this cell"; - ws.Cell(8, 2).Value = "Width ignored because calling column.AdjustToContents(5, 7)"; - - // Adjust column widths only taking into account rows 5-7 - // (merged cells will be ignored) - ws.Column(2).AdjustToContents(5, 7); - - // You can also specify the starting row to start calculating the widths: - // e.g. ws.Column(3).AdjustToContents(9); - - var ws2 = wb.Worksheets.Add("Adjust Widths"); - ws2.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; - for (Int32 co = 0; co < 90; co += 5) + using (var wb = new XLWorkbook()) { - ws2.Cell(1, (co / 5) + 2).SetValue("Text to adjust - " + co).Style.Alignment.TextRotation = co; + var ws = wb.Worksheets.Add("Adjust To Contents"); + + // Set some values with different font sizes + ws.Cell(1, 1).Value = "Tall Row"; + ws.Cell(1, 1).Style.Font.FontSize = 30; + ws.Cell(2, 1).Value = "Very Wide Column"; + ws.Cell(2, 1).Style.Font.FontSize = 20; + + // Adjust column width + ws.Column(1).AdjustToContents(); + + // Adjust row heights + ws.Rows(1, 2).AdjustToContents(); + + // You can also adjust all rows/columns in one shot + // ws.Rows().AdjustToContents(); + // ws.Columns().AdjustToContents(); + + // We'll now select which cells should be used for calculating the + // column widths (same method applies for row heights) + + // Set the values + ws.Cell(4, 2).Value = "Width ignored because calling column.AdjustToContents(5, 7)"; + ws.Cell(5, 2).Value = "Short text"; + ws.Cell(6, 2).Value = "Width ignored because it's part of a merge"; + ws.Range(6, 2, 6, 4).Merge(); + ws.Cell(7, 2).Value = "Width should adjust to this cell"; + ws.Cell(8, 2).Value = "Width ignored because calling column.AdjustToContents(5, 7)"; + + // Adjust column widths only taking into account rows 5-7 + // (merged cells will be ignored) + ws.Column(2).AdjustToContents(5, 7); + + // You can also specify the starting row to start calculating the widths: + // e.g. ws.Column(3).AdjustToContents(9); + + var ws2 = wb.Worksheets.Add("Adjust Widths"); + ws2.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; + for (Int32 co = 0; co < 90; co += 5) + { + ws2.Cell(1, (co / 5) + 2).SetValue("Text to adjust - " + co).Style.Alignment.TextRotation = co; + } + + ws2.Columns().AdjustToContents(); + + var ws4 = wb.Worksheets.Add("Adjust Widths 2"); + ws4.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; + for (Int32 co = 0; co < 90; co += 5) + { + var c = ws4.Cell(1, (co / 5) + 2); + + c.RichText.AddText("Text to adjust - " + co).SetBold(); + c.RichText.AddText(Environment.NewLine); + c.RichText.AddText("World!").SetBold().SetFontColor(XLColor.Blue).SetFontSize(25); + c.RichText.AddText(Environment.NewLine); + c.RichText.AddText("Hello Cruel and unsusual world").SetBold().SetFontSize(20); + c.RichText.AddText(Environment.NewLine); + c.RichText.AddText("Hello").SetBold(); + c.Style.Alignment.SetTextRotation(co); + } + ws4.Columns().AdjustToContents(); + + var ws3 = wb.Worksheets.Add("Adjust Heights"); + ws3.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; + for (Int32 ro = 0; ro < 90; ro += 5) + { + ws3.Cell((ro / 5) + 2, 1).SetValue("Text to adjust - " + ro).Style.Alignment.TextRotation = ro; + } + + ws3.Rows().AdjustToContents(); + + var ws5 = wb.Worksheets.Add("Adjust Heights 2"); + ws5.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; + for (Int32 ro = 0; ro < 90; ro += 5) + { + var c = ws5.Cell((ro / 5) + 2, 1); + c.RichText.AddText("Text to adjust - " + ro).SetBold(); + c.RichText.AddText(Environment.NewLine); + c.RichText.AddText("World!").SetBold().SetFontColor(XLColor.Blue).SetFontSize(10); + c.RichText.AddText(Environment.NewLine); + c.RichText.AddText("Hello Cruel and unsusual world").SetBold().SetFontSize(15); + c.RichText.AddText(Environment.NewLine); + c.RichText.AddText("Hello").SetBold(); + c.Style.Alignment.SetTextRotation(ro); + } + + ws5.Rows().AdjustToContents(); + + var ws6 = wb.Worksheets.Add("Absurdly wide column"); + ws6.Cell("A1").Value = "Some string"; + + // This column's width should be capped at 255 + ws6.Cell("B1").Value = @"Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum."; + + ws6.Columns().AdjustToContents(); + + wb.SaveAs(filePath, true); } - - ws2.Columns().AdjustToContents(); - - var ws4 = wb.Worksheets.Add("Adjust Widths 2"); - ws4.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; - for (Int32 co = 0; co < 90; co += 5) - { - var c = ws4.Cell(1, (co / 5) + 2); - - c.RichText.AddText("Text to adjust - " + co).SetBold(); - c.RichText.AddText(Environment.NewLine); - c.RichText.AddText("World!").SetBold().SetFontColor(XLColor.Blue).SetFontSize(25); - c.RichText.AddText(Environment.NewLine); - c.RichText.AddText("Hello Cruel and unsusual world").SetBold().SetFontSize(20); - c.RichText.AddText(Environment.NewLine); - c.RichText.AddText("Hello").SetBold(); - c.Style.Alignment.SetTextRotation(co); - } - ws4.Columns().AdjustToContents(); - - var ws3 = wb.Worksheets.Add("Adjust Heights"); - ws3.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; - for (Int32 ro = 0; ro < 90; ro += 5) - { - ws3.Cell((ro / 5) + 2, 1).SetValue("Text to adjust - " + ro).Style.Alignment.TextRotation = ro; - } - - ws3.Rows().AdjustToContents(); - - var ws5 = wb.Worksheets.Add("Adjust Heights 2"); - ws5.Cell(1, 1).SetValue("Text to adjust - 255").Style.Alignment.TextRotation = 255; - for (Int32 ro = 0; ro < 90; ro += 5) - { - var c = ws5.Cell((ro / 5) + 2, 1); - c.RichText.AddText("Text to adjust - " + ro).SetBold(); - c.RichText.AddText(Environment.NewLine); - c.RichText.AddText("World!").SetBold().SetFontColor(XLColor.Blue).SetFontSize(10); - c.RichText.AddText(Environment.NewLine); - c.RichText.AddText("Hello Cruel and unsusual world").SetBold().SetFontSize(15); - c.RichText.AddText(Environment.NewLine); - c.RichText.AddText("Hello").SetBold(); - c.Style.Alignment.SetTextRotation(ro); - } - - ws5.Rows().AdjustToContents(); - - wb.SaveAs(filePath); } - - // Private - - // Override - - - #endregion } } diff --git a/ClosedXML_Examples/Misc/CellValues.cs b/ClosedXML_Examples/Misc/CellValues.cs index ea08869..2eac33e 100644 --- a/ClosedXML_Examples/Misc/CellValues.cs +++ b/ClosedXML_Examples/Misc/CellValues.cs @@ -1,185 +1,148 @@ -using System; -using ClosedXML.Excel; - +using ClosedXML.Excel; +using System; namespace ClosedXML_Examples.Misc { public class CellValues : IXLExample { - #region Variables - - // Public - - // Private - - - #endregion - - #region Properties - - // Public - - // Private - - // Override - - - #endregion - - #region Events - - // Public - - // Private - - // Override - - - #endregion - - #region Methods - - // Public public void Create(String filePath) { - var workbook = new XLWorkbook(); - var ws = workbook.Worksheets.Add("Cell Values"); + using (var workbook = new XLWorkbook()) + { + var ws = workbook.Worksheets.Add("Cell Values"); - // Set the titles - ws.Cell(2, 2).Value = "Initial Value"; - ws.Cell(2, 3).Value = "Casting"; - ws.Cell(2, 4).Value = "Using Get...()"; - ws.Cell(2, 5).Value = "Using GetValue()"; - ws.Cell(2, 6).Value = "GetString()"; - ws.Cell(2, 7).Value = "GetFormattedString()"; + // Set the titles + ws.Cell(2, 2).Value = "Initial Value"; + ws.Cell(2, 3).Value = "Casting"; + ws.Cell(2, 4).Value = "Using Get...()"; + ws.Cell(2, 5).Value = "Using GetValue()"; + ws.Cell(2, 6).Value = "GetString()"; + ws.Cell(2, 7).Value = "GetFormattedString()"; - ////////////////////////////////////////////////////////////////// - // DateTime + ////////////////////////////////////////////////////////////////// + // DateTime - // Fill a cell with a date - var cellDateTime = ws.Cell(3, 2); - cellDateTime.Value = new DateTime(2010, 9, 2); - cellDateTime.Style.DateFormat.Format = "yyyy-MMM-dd"; + // Fill a cell with a date + var cellDateTime = ws.Cell(3, 2); + cellDateTime.Value = new DateTime(2010, 9, 2); + cellDateTime.Style.DateFormat.Format = "yyyy-MMM-dd"; - // Extract the date in different ways - DateTime dateTime1 = (DateTime)cellDateTime.Value; - DateTime dateTime2 = cellDateTime.GetDateTime(); - DateTime dateTime3 = cellDateTime.GetValue(); - String dateTimeString = cellDateTime.GetString(); - String dateTimeFormattedString = cellDateTime.GetFormattedString(); + // Extract the date in different ways + DateTime dateTime1 = (DateTime)cellDateTime.Value; + DateTime dateTime2 = cellDateTime.GetDateTime(); + DateTime dateTime3 = cellDateTime.GetValue(); + String dateTimeString = cellDateTime.GetString(); + String dateTimeFormattedString = cellDateTime.GetFormattedString(); - // Set the values back to cells - // The apostrophe is to force ClosedXML to treat the date as a string - ws.Cell(3, 3).Value = dateTime1; - ws.Cell(3, 4).Value = dateTime2; - ws.Cell(3, 5).Value = dateTime3; - ws.Cell(3, 6).Value = "'" + dateTimeString; - ws.Cell(3, 7).Value = "'" + dateTimeFormattedString; + // Set the values back to cells + // The apostrophe is to force ClosedXML to treat the date as a string + ws.Cell(3, 3).Value = dateTime1; + ws.Cell(3, 4).Value = dateTime2; + ws.Cell(3, 5).Value = dateTime3; + ws.Cell(3, 6).Value = "'" + dateTimeString; + ws.Cell(3, 7).Value = "'" + dateTimeFormattedString; - ////////////////////////////////////////////////////////////////// - // Boolean + ////////////////////////////////////////////////////////////////// + // Boolean - // Fill a cell with a boolean - var cellBoolean = ws.Cell(4, 2); - cellBoolean.Value = true; + // Fill a cell with a boolean + var cellBoolean = ws.Cell(4, 2); + cellBoolean.Value = true; - // Extract the boolean in different ways - Boolean boolean1 = (Boolean)cellBoolean.Value; - Boolean boolean2 = cellBoolean.GetBoolean(); - Boolean boolean3 = cellBoolean.GetValue(); - String booleanString = cellBoolean.GetString(); - String booleanFormattedString = cellBoolean.GetFormattedString(); + // Extract the boolean in different ways + Boolean boolean1 = (Boolean)cellBoolean.Value; + Boolean boolean2 = cellBoolean.GetBoolean(); + Boolean boolean3 = cellBoolean.GetValue(); + String booleanString = cellBoolean.GetString(); + String booleanFormattedString = cellBoolean.GetFormattedString(); - // Set the values back to cells - // The apostrophe is to force ClosedXML to treat the boolean as a string - ws.Cell(4, 3).Value = boolean1; - ws.Cell(4, 4).Value = boolean2; - ws.Cell(4, 5).Value = boolean3; - ws.Cell(4, 6).Value = "'" + booleanString; - ws.Cell(4, 7).Value = "'" + booleanFormattedString; + // Set the values back to cells + // The apostrophe is to force ClosedXML to treat the boolean as a string + ws.Cell(4, 3).Value = boolean1; + ws.Cell(4, 4).Value = boolean2; + ws.Cell(4, 5).Value = boolean3; + ws.Cell(4, 6).Value = "'" + booleanString; + ws.Cell(4, 7).Value = "'" + booleanFormattedString; - ////////////////////////////////////////////////////////////////// - // Double + ////////////////////////////////////////////////////////////////// + // Double - // Fill a cell with a double - var cellDouble = ws.Cell(5, 2); - cellDouble.Value = 1234.567; - cellDouble.Style.NumberFormat.Format = "#,##0.00"; + // Fill a cell with a double + var cellDouble = ws.Cell(5, 2); + cellDouble.Value = 1234.567; + cellDouble.Style.NumberFormat.Format = "#,##0.00"; - // Extract the double in different ways - Double double1 = (Double)cellDouble.Value; - Double double2 = cellDouble.GetDouble(); - Double double3 = cellDouble.GetValue(); - String doubleString = cellDouble.GetString(); - String doubleFormattedString = cellDouble.GetFormattedString(); + // Extract the double in different ways + Double double1 = (Double)cellDouble.Value; + Double double2 = cellDouble.GetDouble(); + Double double3 = cellDouble.GetValue(); + String doubleString = cellDouble.GetString(); + String doubleFormattedString = cellDouble.GetFormattedString(); - // Set the values back to cells - // The apostrophe is to force ClosedXML to treat the double as a string - ws.Cell(5, 3).Value = double1; - ws.Cell(5, 4).Value = double2; - ws.Cell(5, 5).Value = double3; - ws.Cell(5, 6).Value = "'" + doubleString; - ws.Cell(5, 7).Value = "'" + doubleFormattedString; + // Set the values back to cells + // The apostrophe is to force ClosedXML to treat the double as a string + ws.Cell(5, 3).Value = double1; + ws.Cell(5, 4).Value = double2; + ws.Cell(5, 5).Value = double3; + ws.Cell(5, 6).Value = "'" + doubleString; + ws.Cell(5, 7).Value = "'" + doubleFormattedString; - ////////////////////////////////////////////////////////////////// - // String + ////////////////////////////////////////////////////////////////// + // String - // Fill a cell with a string - var cellString = ws.Cell(6, 2); - cellString.Value = "Test Case"; + // Fill a cell with a string + var cellString = ws.Cell(6, 2); + cellString.Value = "Test Case"; - // Extract the string in different ways - String string1 = (String)cellString.Value; - String string2 = cellString.GetString(); - String string3 = cellString.GetValue(); - String stringString = cellString.GetString(); - String stringFormattedString = cellString.GetFormattedString(); + // Extract the string in different ways + String string1 = (String)cellString.Value; + String string2 = cellString.GetString(); + String string3 = cellString.GetValue(); + String stringString = cellString.GetString(); + String stringFormattedString = cellString.GetFormattedString(); - // Set the values back to cells - ws.Cell(6, 3).Value = string1; - ws.Cell(6, 4).Value = string2; - ws.Cell(6, 5).Value = string3; - ws.Cell(6, 6).Value = stringString; - ws.Cell(6, 7).Value = stringFormattedString; + // Set the values back to cells + ws.Cell(6, 3).Value = string1; + ws.Cell(6, 4).Value = string2; + ws.Cell(6, 5).Value = string3; + ws.Cell(6, 6).Value = stringString; + ws.Cell(6, 7).Value = stringFormattedString; - ////////////////////////////////////////////////////////////////// - // TimeSpan + ////////////////////////////////////////////////////////////////// + // TimeSpan - // Fill a cell with a timeSpan - var cellTimeSpan = ws.Cell(7, 2); - cellTimeSpan.Value = new TimeSpan(1, 2, 31, 45); + // Fill a cell with a timeSpan + var cellTimeSpan = ws.Cell(7, 2); + cellTimeSpan.Value = new TimeSpan(1, 2, 31, 45); - // Extract the timeSpan in different ways - TimeSpan timeSpan1 = (TimeSpan)cellTimeSpan.Value; - TimeSpan timeSpan2 = cellTimeSpan.GetTimeSpan(); - TimeSpan timeSpan3 = cellTimeSpan.GetValue(); - String timeSpanString = "'" + cellTimeSpan.GetString(); - String timeSpanFormattedString = "'" + cellTimeSpan.GetFormattedString(); + // Extract the timeSpan in different ways + TimeSpan timeSpan1 = (TimeSpan)cellTimeSpan.Value; + TimeSpan timeSpan2 = cellTimeSpan.GetTimeSpan(); + TimeSpan timeSpan3 = cellTimeSpan.GetValue(); + String timeSpanString = "'" + cellTimeSpan.GetString(); + String timeSpanFormattedString = "'" + cellTimeSpan.GetFormattedString(); - // Set the values back to cells - ws.Cell(7, 3).Value = timeSpan1; - ws.Cell(7, 4).Value = timeSpan2; - ws.Cell(7, 5).Value = timeSpan3; - ws.Cell(7, 6).Value = timeSpanString; - ws.Cell(7, 7).Value = timeSpanFormattedString; + // Set the values back to cells + ws.Cell(7, 3).Value = timeSpan1; + ws.Cell(7, 4).Value = timeSpan2; + ws.Cell(7, 5).Value = timeSpan3; + ws.Cell(7, 6).Value = timeSpanString; + ws.Cell(7, 7).Value = timeSpanFormattedString; - ////////////////////////////////////////////////////////////////// - // Do some formatting - ws.Columns("B:G").Width = 20; - var rngTitle = ws.Range("B2:G2"); - rngTitle.Style.Font.Bold = true; - rngTitle.Style.Fill.BackgroundColor = XLColor.Cyan; + ////////////////////////////////////////////////////////////////// + // Do some formatting + ws.Columns("B:G").Width = 20; + var rngTitle = ws.Range("B2:G2"); + rngTitle.Style.Font.Bold = true; + rngTitle.Style.Fill.BackgroundColor = XLColor.Cyan; - ws.Columns().AdjustToContents(); + ws.Columns().AdjustToContents(); - workbook.SaveAs(filePath); + ws = workbook.AddWorksheet("Test Whitespace"); + ws.FirstCell().Value = "' "; + + workbook.SaveAs(filePath); + } } - - // Private - - // Override - - - #endregion } } diff --git a/ClosedXML_Examples/Misc/DataTypes.cs b/ClosedXML_Examples/Misc/DataTypes.cs index e4feeb1..14abad9 100644 --- a/ClosedXML_Examples/Misc/DataTypes.cs +++ b/ClosedXML_Examples/Misc/DataTypes.cs @@ -68,6 +68,17 @@ ro++; + ws.Cell(++ro, co).Value = "Decimal Number:"; + ws.Cell(ro, co + 1).Value = 123.45m; + + ws.Cell(++ro, co).Value = "Float Number:"; + ws.Cell(ro, co + 1).Value = 123.45f; + + ws.Cell(++ro, co).Value = "Double Number:"; + ws.Cell(ro, co + 1).Value = 123.45d; + + ro++; + ws.Cell(++ro, co).Value = "Explicit Text:"; ws.Cell(ro, co + 1).Value = "'Hello World."; @@ -192,7 +203,7 @@ .SetDataType(XLCellValues.DateTime) .SetDataType(XLCellValues.Boolean) .SetDataType(XLCellValues.Text); - + ws.Columns(2, 3).AdjustToContents(); workbook.SaveAs(filePath); diff --git a/ClosedXML_Examples/Misc/SheetViews.cs b/ClosedXML_Examples/Misc/SheetViews.cs new file mode 100644 index 0000000..70f9b5c --- /dev/null +++ b/ClosedXML_Examples/Misc/SheetViews.cs @@ -0,0 +1,45 @@ +using ClosedXML.Excel; +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML_Examples.Misc +{ + public class SheetViews : IXLExample + { + public void Create(string filePath) + { + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws; + + ws = wb.AddWorksheet("ZoomScale"); + ws.FirstCell().SetValue(ws.Name); + ws.SheetView.ZoomScale = 50; + + ws = wb.AddWorksheet("ZoomScaleNormal"); + ws.FirstCell().SetValue(ws.Name); + ws.SheetView.ZoomScaleNormal = 70; + + ws = wb.AddWorksheet("ZoomScalePageLayoutView"); + ws.FirstCell().SetValue(ws.Name); + ws.SheetView.ZoomScalePageLayoutView = 85; + + ws = wb.AddWorksheet("ZoomScaleSheetLayoutView"); + ws.FirstCell().SetValue(ws.Name); + ws.SheetView.ZoomScaleSheetLayoutView = 120; + + ws = wb.AddWorksheet("ZoomScaleTooSmall"); + ws.FirstCell().SetValue(ws.Name); + ws.SheetView.ZoomScale = 5; + + ws = wb.AddWorksheet("ZoomScaleTooBig"); + ws.FirstCell().SetValue(ws.Name); + ws.SheetView.ZoomScale = 500; + + wb.SaveAs(filePath); + } + } + } +} diff --git a/ClosedXML_Examples/PivotTables/PivotTables.cs b/ClosedXML_Examples/PivotTables/PivotTables.cs index 9af9775..61692b9 100644 --- a/ClosedXML_Examples/PivotTables/PivotTables.cs +++ b/ClosedXML_Examples/PivotTables/PivotTables.cs @@ -63,10 +63,10 @@ for (int i = 1; i <= 3; i++) { // Add a new sheet for our pivot table - ptSheet = wb.Worksheets.Add("PivotTable" + i); + ptSheet = wb.Worksheets.Add("pvt" + i); // Create the pivot table, using the data from the "PastrySalesData" table - pt = ptSheet.PivotTables.AddNew("PivotTable", ptSheet.Cell(1, 1), dataRange); + pt = ptSheet.PivotTables.AddNew("pvt", ptSheet.Cell(1, 1), dataRange); // The rows in our pivot table will be the names of the pastries pt.RowLabels.Add("Name"); @@ -96,14 +96,35 @@ } // Different kind of pivot - ptSheet = wb.Worksheets.Add("PivotTableNoColumnLabels"); - pt = ptSheet.PivotTables.AddNew("PivotTableNoColumnLabels", ptSheet.Cell(1, 1), dataRange); + ptSheet = wb.Worksheets.Add("pvtNoColumnLabels"); + pt = ptSheet.PivotTables.AddNew("pvtNoColumnLabels", ptSheet.Cell(1, 1), dataRange); pt.RowLabels.Add("Name"); pt.RowLabels.Add("Month"); - pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);//.NumberFormat.Format = "#0.00"; - pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum);//.NumberFormat.Format = "#0.00"; + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum); + + + // Pivot table with collapsed fields + ptSheet = wb.Worksheets.Add("pvtCollapsedFields"); + pt = ptSheet.PivotTables.AddNew("pvtCollapsedFields", ptSheet.Cell(1, 1), dataRange); + + pt.RowLabels.Add("Name").SetCollapsed(); + pt.RowLabels.Add("Month").SetCollapsed(); + + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + pt.Values.Add("Quality").SetSummaryFormula(XLPivotSummary.Sum); + + + // 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); + + pt.RowLabels.Add("Name"); + pt.RowLabels.Add("Month"); + + pt.Values.Add("Name").SetSummaryFormula(XLPivotSummary.Count);//.NumberFormat.Format = "#0.00"; wb.SaveAs(filePath); } diff --git a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index d44362e..b828af4 100644 --- a/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -100,6 +100,9 @@ Excel\CalcEngine\CalcEngine.cs + + Excel\CalcEngine\CalcEngineHelpers.cs + Excel\CalcEngine\Expression.cs diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index e89fd07..eabbee3 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -73,6 +73,7 @@ + @@ -103,6 +104,7 @@ + @@ -162,6 +164,7 @@ + @@ -254,7 +257,9 @@ + + diff --git a/ClosedXML_Tests/Examples/MiscTests.cs b/ClosedXML_Tests/Examples/MiscTests.cs index eebb483..47865bc 100644 --- a/ClosedXML_Tests/Examples/MiscTests.cs +++ b/ClosedXML_Tests/Examples/MiscTests.cs @@ -170,6 +170,12 @@ } [Test] + public void SheetViews() + { + TestHelper.RunTestExample(@"Misc\SheetViews.xlsx"); + } + + [Test] public void ShiftingFormulas() { TestHelper.RunTestExample(@"Misc\ShiftingFormulas.xlsx"); diff --git a/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs b/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs index 98f8d87..48691a6 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/FunctionsTests.cs @@ -467,10 +467,10 @@ public void SubtotalCount() { object actual = XLWorkbook.EvaluateExpr("Subtotal(2,2,3)"); - Assert.AreEqual(2.0, actual); + Assert.AreEqual(2, actual); actual = XLWorkbook.EvaluateExpr(@"Subtotal(2,""A"",3)"); - Assert.AreEqual(2.0, actual); + Assert.AreEqual(1, actual); } [Test] @@ -609,14 +609,12 @@ XLWorkbook wb = new XLWorkbook(); wb.Worksheets.Add("TallyTests"); var cell = wb.Worksheet(1).Cell(1, 1).SetFormulaA1("=MAX(D1,D2)"); - Assert.AreEqual(0, cell.Value); + Assert.AreEqual(0, cell.Value); cell = wb.Worksheet(1).Cell(2, 1).SetFormulaA1("=MIN(D1,D2)"); Assert.AreEqual(0, cell.Value); cell = wb.Worksheet(1).Cell(3, 1).SetFormulaA1("=SUM(D1,D2)"); Assert.AreEqual(0, cell.Value); - cell = wb.Worksheet(1).Cell(3, 1).SetFormulaA1("=AVERAGE(D1,D2)"); - Assert.AreEqual(0, cell.Value); - + Assert.That(() => wb.Worksheet(1).Cell(3, 1).SetFormulaA1("=AVERAGE(D1,D2)").Value, Throws.Exception); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs new file mode 100644 index 0000000..9285cb4 --- /dev/null +++ b/ClosedXML_Tests/Excel/CalcEngine/StatisticalTests.cs @@ -0,0 +1,270 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Linq; + +namespace ClosedXML_Tests.Excel.CalcEngine +{ + [TestFixture] + public class StatisticalTests + { + private double tolerance = 1e-6; + private XLWorkbook workbook; + + [Test] + public void Average() + { + double value; + value = workbook.Evaluate("AVERAGE(-27.5,93.93,64.51,-70.56)").CastTo(); + Assert.AreEqual(15.095, value, tolerance); + + var ws = workbook.Worksheets.First(); + value = ws.Evaluate("AVERAGE(G3:G45)").CastTo(); + Assert.AreEqual(49.3255814, value, tolerance); + + Assert.That(() => ws.Evaluate("AVERAGE(D3:D45)"), Throws.Exception); + } + + [Test] + public void Count() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=COUNT(D3:D45)").CastTo(); + Assert.AreEqual(0, value); + + value = ws.Evaluate(@"=COUNT(G3:G45)").CastTo(); + Assert.AreEqual(43, value); + + value = ws.Evaluate(@"=COUNT(G:G)").CastTo(); + Assert.AreEqual(43, value); + + value = workbook.Evaluate(@"=COUNT(Data!G:G)").CastTo(); + Assert.AreEqual(43, value); + } + + [Test] + public void CountA() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=COUNTA(D3:D45)").CastTo(); + Assert.AreEqual(43, value); + + value = ws.Evaluate(@"=COUNTA(G3:G45)").CastTo(); + Assert.AreEqual(43, value); + + value = ws.Evaluate(@"=COUNTA(G:G)").CastTo(); + Assert.AreEqual(44, value); + + value = workbook.Evaluate(@"=COUNTA(Data!G:G)").CastTo(); + Assert.AreEqual(44, value); + } + + [Test] + public void CountBlank() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=COUNTBLANK(B:B)").CastTo(); + Assert.AreEqual(1048532, value); + + value = ws.Evaluate(@"=COUNTBLANK(D43:D49)").CastTo(); + Assert.AreEqual(4, value); + + value = workbook.Evaluate(@"=COUNTBLANK(E3:E45)").CastTo(); + Assert.AreEqual(0, value); + } + + [Test] + public void CountIf() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=COUNTIF(D3:D45,""Central"")").CastTo(); + Assert.AreEqual(24, value); + + value = ws.Evaluate(@"=COUNTIF(D:D,""Central"")").CastTo(); + Assert.AreEqual(24, value); + + value = workbook.Evaluate(@"=COUNTIF(Data!D:D,""Central"")").CastTo(); + Assert.AreEqual(24, value); + } + + [OneTimeTearDown] + public void Dispose() + { + workbook.Dispose(); + } + + [OneTimeSetUp] + public void Init() + { + // Make sure tests run on a deterministic culture + System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); + workbook = SetupWorkbook(); + } + + [Test] + public void Max() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=MAX(D3:D45)").CastTo(); + Assert.AreEqual(0, value); + + value = ws.Evaluate(@"=MAX(G3:G45)").CastTo(); + Assert.AreEqual(96, value); + + value = ws.Evaluate(@"=MAX(G:G)").CastTo(); + Assert.AreEqual(96, value); + + value = workbook.Evaluate(@"=MAX(Data!G:G)").CastTo(); + Assert.AreEqual(96, value); + } + + [Test] + public void Min() + { + var ws = workbook.Worksheets.First(); + int value; + value = ws.Evaluate(@"=MIN(D3:D45)").CastTo(); + Assert.AreEqual(0, value); + + value = ws.Evaluate(@"=MIN(G3:G45)").CastTo(); + Assert.AreEqual(2, value); + + value = ws.Evaluate(@"=MIN(G:G)").CastTo(); + Assert.AreEqual(2, value); + + value = workbook.Evaluate(@"=MIN(Data!G:G)").CastTo(); + Assert.AreEqual(2, value); + } + + [Test] + public void StDev() + { + var ws = workbook.Worksheets.First(); + double value; + Assert.That(() => ws.Evaluate(@"=STDEV(D3:D45)"), Throws.Exception); + + value = ws.Evaluate(@"=STDEV(H3:H45)").CastTo(); + Assert.AreEqual(47.34511769, value, tolerance); + + value = ws.Evaluate(@"=STDEV(H:H)").CastTo(); + Assert.AreEqual(47.34511769, value, tolerance); + + value = workbook.Evaluate(@"=STDEV(Data!H:H)").CastTo(); + Assert.AreEqual(47.34511769, value, tolerance); + } + + [Test] + public void StDevP() + { + var ws = workbook.Worksheets.First(); + double value; + Assert.That(() => ws.Evaluate(@"=STDEVP(D3:D45)"), Throws.Exception); + + value = ws.Evaluate(@"=STDEVP(H3:H45)").CastTo(); + Assert.AreEqual(46.79135458, value, tolerance); + + value = ws.Evaluate(@"=STDEVP(H:H)").CastTo(); + Assert.AreEqual(46.79135458, value, tolerance); + + value = workbook.Evaluate(@"=STDEVP(Data!H:H)").CastTo(); + Assert.AreEqual(46.79135458, value, tolerance); + } + + [Test] + public void Var() + { + var ws = workbook.Worksheets.First(); + double value; + Assert.That(() => ws.Evaluate(@"=VAR(D3:D45)"), Throws.Exception); + + value = ws.Evaluate(@"=VAR(H3:H45)").CastTo(); + Assert.AreEqual(2241.560169, value, tolerance); + + value = ws.Evaluate(@"=VAR(H:H)").CastTo(); + Assert.AreEqual(2241.560169, value, tolerance); + + value = workbook.Evaluate(@"=VAR(Data!H:H)").CastTo(); + Assert.AreEqual(2241.560169, value, tolerance); + } + + [Test] + public void VarP() + { + var ws = workbook.Worksheets.First(); + double value; + Assert.That(() => ws.Evaluate(@"=VARP(D3:D45)"), Throws.Exception); + + value = ws.Evaluate(@"=VARP(H3:H45)").CastTo(); + Assert.AreEqual(2189.430863, value, tolerance); + + value = ws.Evaluate(@"=VARP(H:H)").CastTo(); + Assert.AreEqual(2189.430863, value, tolerance); + + value = workbook.Evaluate(@"=VARP(Data!H:H)").CastTo(); + Assert.AreEqual(2189.430863, value, tolerance); + } + + private XLWorkbook SetupWorkbook() + { + var wb = new XLWorkbook(); + var ws = wb.AddWorksheet("Data"); + var data = new object[] + { + new {Id=1, OrderDate = DateTime.Parse("2015-01-06"), Region = "East", Rep = "Jones", Item = "Pencil", Units = 95, UnitCost = 1.99, Total = 189.05 }, + new {Id=2, OrderDate = DateTime.Parse("2015-01-23"), Region = "Central", Rep = "Kivell", Item = "Binder", Units = 50, UnitCost = 19.99, Total = 999.5}, + new {Id=3, OrderDate = DateTime.Parse("2015-02-09"), Region = "Central", Rep = "Jardine", Item = "Pencil", Units = 36, UnitCost = 4.99, Total = 179.64}, + new {Id=4, OrderDate = DateTime.Parse("2015-02-26"), Region = "Central", Rep = "Gill", Item = "Pen", Units = 27, UnitCost = 19.99, Total = 539.73}, + new {Id=5, OrderDate = DateTime.Parse("2015-03-15"), Region = "West", Rep = "Sorvino", Item = "Pencil", Units = 56, UnitCost = 2.99, Total = 167.44}, + new {Id=6, OrderDate = DateTime.Parse("2015-04-01"), Region = "East", Rep = "Jones", Item = "Binder", Units = 60, UnitCost = 4.99, Total = 299.4}, + new {Id=7, OrderDate = DateTime.Parse("2015-04-18"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 75, UnitCost = 1.99, Total = 149.25}, + new {Id=8, OrderDate = DateTime.Parse("2015-05-05"), Region = "Central", Rep = "Jardine", Item = "Pencil", Units = 90, UnitCost = 4.99, Total = 449.1}, + new {Id=9, OrderDate = DateTime.Parse("2015-05-22"), Region = "West", Rep = "Thompson", Item = "Pencil", Units = 32, UnitCost = 1.99, Total = 63.68}, + new {Id=10, OrderDate = DateTime.Parse("2015-06-08"), Region = "East", Rep = "Jones", Item = "Binder", Units = 60, UnitCost = 8.99, Total = 539.4}, + new {Id=11, OrderDate = DateTime.Parse("2015-06-25"), Region = "Central", Rep = "Morgan", Item = "Pencil", Units = 90, UnitCost = 4.99, Total = 449.1}, + new {Id=12, OrderDate = DateTime.Parse("2015-07-12"), Region = "East", Rep = "Howard", Item = "Binder", Units = 29, UnitCost = 1.99, Total = 57.71}, + new {Id=13, OrderDate = DateTime.Parse("2015-07-29"), Region = "East", Rep = "Parent", Item = "Binder", Units = 81, UnitCost = 19.99, Total = 1619.19}, + new {Id=14, OrderDate = DateTime.Parse("2015-08-15"), Region = "East", Rep = "Jones", Item = "Pencil", Units = 35, UnitCost = 4.99, Total = 174.65}, + new {Id=15, OrderDate = DateTime.Parse("2015-09-01"), Region = "Central", Rep = "Smith", Item = "Desk", Units = 2, UnitCost = 125, Total = 250}, + new {Id=16, OrderDate = DateTime.Parse("2015-09-18"), Region = "East", Rep = "Jones", Item = "Pen Set", Units = 16, UnitCost = 15.99, Total = 255.84}, + new {Id=17, OrderDate = DateTime.Parse("2015-10-05"), Region = "Central", Rep = "Morgan", Item = "Binder", Units = 28, UnitCost = 8.99, Total = 251.72}, + new {Id=18, OrderDate = DateTime.Parse("2015-10-22"), Region = "East", Rep = "Jones", Item = "Pen", Units = 64, UnitCost = 8.99, Total = 575.36}, + new {Id=19, OrderDate = DateTime.Parse("2015-11-08"), Region = "East", Rep = "Parent", Item = "Pen", Units = 15, UnitCost = 19.99, Total = 299.85}, + new {Id=20, OrderDate = DateTime.Parse("2015-11-25"), Region = "Central", Rep = "Kivell", Item = "Pen Set", Units = 96, UnitCost = 4.99, Total = 479.04}, + new {Id=21, OrderDate = DateTime.Parse("2015-12-12"), Region = "Central", Rep = "Smith", Item = "Pencil", Units = 67, UnitCost = 1.29, Total = 86.43}, + new {Id=22, OrderDate = DateTime.Parse("2015-12-29"), Region = "East", Rep = "Parent", Item = "Pen Set", Units = 74, UnitCost = 15.99, Total = 1183.26}, + new {Id=23, OrderDate = DateTime.Parse("2016-01-15"), Region = "Central", Rep = "Gill", Item = "Binder", Units = 46, UnitCost = 8.99, Total = 413.54}, + new {Id=24, OrderDate = DateTime.Parse("2016-02-01"), Region = "Central", Rep = "Smith", Item = "Binder", Units = 87, UnitCost = 15, Total = 1305}, + new {Id=25, OrderDate = DateTime.Parse("2016-02-18"), Region = "East", Rep = "Jones", Item = "Binder", Units = 4, UnitCost = 4.99, Total = 19.96}, + new {Id=26, OrderDate = DateTime.Parse("2016-03-07"), Region = "West", Rep = "Sorvino", Item = "Binder", Units = 7, UnitCost = 19.99, Total = 139.93}, + new {Id=27, OrderDate = DateTime.Parse("2016-03-24"), Region = "Central", Rep = "Jardine", Item = "Pen Set", Units = 50, UnitCost = 4.99, Total = 249.5}, + new {Id=28, OrderDate = DateTime.Parse("2016-04-10"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 66, UnitCost = 1.99, Total = 131.34}, + new {Id=29, OrderDate = DateTime.Parse("2016-04-27"), Region = "East", Rep = "Howard", Item = "Pen", Units = 96, UnitCost = 4.99, Total = 479.04}, + new {Id=30, OrderDate = DateTime.Parse("2016-05-14"), Region = "Central", Rep = "Gill", Item = "Pencil", Units = 53, UnitCost = 1.29, Total = 68.37}, + new {Id=31, OrderDate = DateTime.Parse("2016-05-31"), Region = "Central", Rep = "Gill", Item = "Binder", Units = 80, UnitCost = 8.99, Total = 719.2}, + new {Id=32, OrderDate = DateTime.Parse("2016-06-17"), Region = "Central", Rep = "Kivell", Item = "Desk", Units = 5, UnitCost = 125, Total = 625}, + new {Id=33, OrderDate = DateTime.Parse("2016-07-04"), Region = "East", Rep = "Jones", Item = "Pen Set", Units = 62, UnitCost = 4.99, Total = 309.38}, + new {Id=34, OrderDate = DateTime.Parse("2016-07-21"), Region = "Central", Rep = "Morgan", Item = "Pen Set", Units = 55, UnitCost = 12.49, Total = 686.95}, + new {Id=35, OrderDate = DateTime.Parse("2016-08-07"), Region = "Central", Rep = "Kivell", Item = "Pen Set", Units = 42, UnitCost = 23.95, Total = 1005.9}, + new {Id=36, OrderDate = DateTime.Parse("2016-08-24"), Region = "West", Rep = "Sorvino", Item = "Desk", Units = 3, UnitCost = 275, Total = 825}, + new {Id=37, OrderDate = DateTime.Parse("2016-09-10"), Region = "Central", Rep = "Gill", Item = "Pencil", Units = 7, UnitCost = 1.29, Total = 9.03}, + new {Id=38, OrderDate = DateTime.Parse("2016-09-27"), Region = "West", Rep = "Sorvino", Item = "Pen", Units = 76, UnitCost = 1.99, Total = 151.24}, + new {Id=39, OrderDate = DateTime.Parse("2016-10-14"), Region = "West", Rep = "Thompson", Item = "Binder", Units = 57, UnitCost = 19.99, Total = 1139.43}, + new {Id=40, OrderDate = DateTime.Parse("2016-10-31"), Region = "Central", Rep = "Andrews", Item = "Pencil", Units = 14, UnitCost = 1.29, Total = 18.06}, + new {Id=41, OrderDate = DateTime.Parse("2016-11-17"), Region = "Central", Rep = "Jardine", Item = "Binder", Units = 11, UnitCost = 4.99, Total = 54.89}, + new {Id=42, OrderDate = DateTime.Parse("2016-12-04"), Region = "Central", Rep = "Jardine", Item = "Binder", Units = 94, UnitCost = 19.99, Total = 1879.06}, + new {Id=43, OrderDate = DateTime.Parse("2016-12-21"), Region = "Central", Rep = "Andrews", Item = "Binder", Units = 28, UnitCost = 4.99, Total = 139.72} + }; + ws.FirstCell() + .CellBelow() + .CellRight() + .InsertTable(data, "Table1"); + + return wb; + } + } +} diff --git a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs index 013950b..b5a940e 100644 --- a/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs +++ b/ClosedXML_Tests/Excel/CalcEngine/TextTests.cs @@ -480,6 +480,13 @@ } [Test] + public void Text_String_Input() + { + Object actual = XLWorkbook.EvaluateExpr(@"TEXT(""211x"", ""#00"")"); + Assert.AreEqual("211x", actual); + } + + [Test] public void Trim_EmptyInput_Striong() { Object actual = XLWorkbook.EvaluateExpr(@"Trim("""")"); @@ -523,4 +530,4 @@ Assert.AreEqual(654.32, actual); } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs index 8398a20..d2e7069 100644 --- a/ClosedXML_Tests/Excel/Loading/LoadingTests.cs +++ b/ClosedXML_Tests/Excel/Loading/LoadingTests.cs @@ -19,7 +19,8 @@ { @"Misc\TableWithCustomTheme.xlsx", @"Misc\EmptyTable.xlsx", - @"Misc\LoadPivotTables.xlsx" + @"Misc\LoadPivotTables.xlsx", + @"Misc\LoadFileWithCustomSheetViews.xlsx" }; foreach (var file in files) @@ -96,5 +97,25 @@ } } } + + /// + /// As per https://msdn.microsoft.com/en-us/library/documentformat.openxml.spreadsheet.cellvalues(v=office.15).aspx + /// the 'Date' DataType is available only in files saved with Microsoft Office + /// In other files, the data type will be saved as numeric + /// ClosedXML then deduces the data type by inspecting the number format string + /// + [Test] + public void CanLoadLibreOfficeFileWithDates() + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Misc\LibreOfficeFileWithDates.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheets.First(); + foreach (var cell in ws.CellsUsed()) + { + Assert.AreEqual(XLCellValues.DateTime, cell.DataType); + } + } + } } } diff --git a/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs b/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs index dcda221..a614b55 100644 --- a/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs +++ b/ClosedXML_Tests/Excel/Ranges/InsertingRangesTests.cs @@ -81,5 +81,33 @@ Assert.AreEqual(XLColor.Fulvous, ws.Cell(4, 2).Style.Fill.BackgroundColor); Assert.AreEqual(XLColor.MacaroniAndCheese, ws.Cell(5, 2).Style.Fill.BackgroundColor); } + + [Test] + public void InsertingRowsPreservesComments() + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Sheet1"); + + ws.Cell("A1").SetValue("Insert Below"); + ws.Cell("A2").SetValue("Already existing cell"); + ws.Cell("A3").SetValue("Cell with comment").Comment.AddText("Comment here"); + + ws.Row(1).InsertRowsBelow(2); + Assert.AreEqual("Comment here", ws.Cell("A5").Comment.Text); + } + + [Test] + public void InsertingColumnsPreservesComments() + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Sheet1"); + + ws.Cell("A1").SetValue("Insert to the right"); + ws.Cell("B1").SetValue("Already existing cell"); + ws.Cell("C1").SetValue("Cell with comment").Comment.AddText("Comment here"); + + ws.Column(1).InsertColumnsAfter(2); + Assert.AreEqual("Comment here", ws.Cell("E1").Comment.Text); + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/Saving/SavingTests.cs b/ClosedXML_Tests/Excel/Saving/SavingTests.cs index 22aa34a..064c912 100644 --- a/ClosedXML_Tests/Excel/Saving/SavingTests.cs +++ b/ClosedXML_Tests/Excel/Saving/SavingTests.cs @@ -1,6 +1,8 @@ using ClosedXML.Excel; using NUnit.Framework; +using System.Globalization; using System.IO; +using System.Threading; namespace ClosedXML_Tests.Excel.Saving { @@ -13,6 +15,10 @@ using (var wb = new XLWorkbook()) { var sheet = wb.Worksheets.Add("TestSheet"); + + // Comments might cause duplicate VmlDrawing Id's - ensure it's tested: + sheet.Cell(1, 1).Comment.AddText("abc"); + var memoryStream = new MemoryStream(); wb.SaveAs(memoryStream, true); @@ -26,5 +32,24 @@ memoryStream.Dispose(); } } + + [Test] + public void CanSaveAndValidateFileInAnotherCulture() + { + string[] cultures = new string[] { "it", "de-AT" }; + + foreach (var culture in cultures) + { + Thread.CurrentThread.CurrentCulture = CultureInfo.GetCultureInfo(culture); + + using (var wb = new XLWorkbook()) + { + var memoryStream = new MemoryStream(); + var ws = wb.Worksheets.Add("Sheet1"); + + wb.SaveAs(memoryStream, true); + } + } + } } } diff --git a/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs new file mode 100644 index 0000000..cf3aa2b --- /dev/null +++ b/ClosedXML_Tests/Excel/Styles/NumberFormatTests.cs @@ -0,0 +1,33 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Data; +using System.Linq; + +namespace ClosedXML_Tests.Excel +{ + public class NumberFormatTests + { + [Test] + public void PreserveCellFormat() + { + 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)); + + for (int i = 0; i < 10; i++) + { + table.Rows.Add(new DateTime(2017, 1, 1).AddMonths(i)); + } + + ws.Cell("A1").InsertData(table.AsEnumerable()); + + Assert.AreEqual("yy-MM-dd", ws.Cell("A5").Style.DateFormat.Format); + } + } + } +} diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx index 8fd67be..1c3d359 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx index 53d5ab2..21de466 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx index 2fe73bc..26c33b7 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx index 9b5b3c3..a438a52 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/CellValues.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx index 23582c4..027c1a7 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataTypes.xlsx index 5b29266..9b03a64 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 ca68eee..0221170 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/InsertingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/InsertingTables.xlsx index 29317a1..80c7c3d 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/InsertingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/InsertingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/SheetViews.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/SheetViews.xlsx new file mode 100644 index 0000000..723756a --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Misc/SheetViews.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx index a9f74c5..a9d4499 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index d28109f..5bc0db9 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/UsingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx index 50cd14f..6e89783 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/UsingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/LibreOfficeFileWithDates.xlsx b/ClosedXML_Tests/Resource/Misc/LibreOfficeFileWithDates.xlsx new file mode 100644 index 0000000..9556636 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/LibreOfficeFileWithDates.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Misc/LoadFileWithCustomSheetViews.xlsx b/ClosedXML_Tests/Resource/Misc/LoadFileWithCustomSheetViews.xlsx new file mode 100644 index 0000000..e6d28c1 --- /dev/null +++ b/ClosedXML_Tests/Resource/Misc/LoadFileWithCustomSheetViews.xlsx Binary files differ diff --git a/README.md b/README.md index 5d195da..961bba2 100644 --- a/README.md +++ b/README.md @@ -4,6 +4,8 @@ ClosedXML makes it easier for developers to create Excel 2007/2010/2013 files. It provides a nice object oriented way to manipulate the files (similar to VBA) without dealing with the hassles of XML Documents. It can be used by any .NET language like C# and Visual Basic (VB). +[For more information see the wiki](https://github.com/closedxml/closedxml/wiki) + ### Install ClosedXML via NuGet If you want to include ClosedXML in your project, you can [install it directly from NuGet](https://www.nuget.org/packages/ClosedXML)