diff --git a/ClosedXML/Attributes/XLColumnAttribute.cs b/ClosedXML/Attributes/XLColumnAttribute.cs index a86a1be..67ab26f 100644 --- a/ClosedXML/Attributes/XLColumnAttribute.cs +++ b/ClosedXML/Attributes/XLColumnAttribute.cs @@ -22,7 +22,7 @@ { var attribute = GetXLColumnAttribute(mi); if (attribute == null) return null; - return XLHelper.IsNullOrWhiteSpace(attribute.Header) ? null : attribute.Header; + return String.IsNullOrWhiteSpace(attribute.Header) ? null : attribute.Header; } internal static Int32 GetOrder(MemberInfo mi) diff --git a/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs b/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs index 86155cb..da2f83b 100644 --- a/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs +++ b/ClosedXML/Excel/CalcEngine/XLCalcEngine.cs @@ -101,7 +101,7 @@ { _evaluating = true; var f = cell.FormulaA1; - if (XLHelper.IsNullOrWhiteSpace(f)) + if (String.IsNullOrWhiteSpace(f)) return cell.Value; else return new XLCalcEngine(cell.Worksheet).Evaluate(f); diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index 4783c6e..c3313e0 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -369,7 +369,7 @@ get { var fA1 = FormulaA1; - if (!XLHelper.IsNullOrWhiteSpace(fA1)) + if (!String.IsNullOrWhiteSpace(fA1)) { if (IsEvaluating) throw new InvalidOperationException("Circular Reference"); @@ -527,7 +527,7 @@ if (!hasTitles) { var fieldName = XLColumnAttribute.GetHeader(itemType); - if (XLHelper.IsNullOrWhiteSpace(fieldName)) + if (String.IsNullOrWhiteSpace(fieldName)) fieldName = itemType.Name; _worksheet.SetValue(fieldName, fRo, co); @@ -605,7 +605,7 @@ if (!hasTitles) { foreach (var fieldName in from DataColumn column in row.Table.Columns - select XLHelper.IsNullOrWhiteSpace(column.Caption) + select String.IsNullOrWhiteSpace(column.Caption) ? column.ColumnName : column.Caption) { @@ -658,7 +658,7 @@ if (!(mi is IEnumerable)) { var fieldName = XLColumnAttribute.GetHeader(mi); - if (XLHelper.IsNullOrWhiteSpace(fieldName)) + if (String.IsNullOrWhiteSpace(fieldName)) fieldName = mi.Name; _worksheet.SetValue(fieldName, fRo, co); @@ -1079,9 +1079,9 @@ { get { - if (XLHelper.IsNullOrWhiteSpace(_formulaA1)) + if (String.IsNullOrWhiteSpace(_formulaA1)) { - if (!XLHelper.IsNullOrWhiteSpace(_formulaR1C1)) + if (!String.IsNullOrWhiteSpace(_formulaR1C1)) { _formulaA1 = GetFormulaA1(_formulaR1C1); return FormulaA1; @@ -1101,7 +1101,7 @@ set { - _formulaA1 = XLHelper.IsNullOrWhiteSpace(value) ? null : value; + _formulaA1 = String.IsNullOrWhiteSpace(value) ? null : value; _formulaR1C1 = null; } @@ -1111,7 +1111,7 @@ { get { - if (XLHelper.IsNullOrWhiteSpace(_formulaR1C1)) + if (String.IsNullOrWhiteSpace(_formulaR1C1)) _formulaR1C1 = GetFormulaR1C1(FormulaA1); return _formulaR1C1; @@ -1119,7 +1119,7 @@ set { - _formulaR1C1 = XLHelper.IsNullOrWhiteSpace(value) ? null : value; + _formulaR1C1 = String.IsNullOrWhiteSpace(value) ? null : value; _formulaA1 = null; } @@ -1720,7 +1720,7 @@ { var style = GetStyleForRead(); return _dataType == XLDataType.Number - && XLHelper.IsNullOrWhiteSpace(style.NumberFormat.Format) + && String.IsNullOrWhiteSpace(style.NumberFormat.Format) && ((style.NumberFormat.NumberFormatId >= 14 && style.NumberFormat.NumberFormatId <= 22) || (style.NumberFormat.NumberFormatId >= 45 @@ -1731,7 +1731,7 @@ { var format = String.Empty; var style = GetStyleForRead(); - if (XLHelper.IsNullOrWhiteSpace(style.NumberFormat.Format)) + if (String.IsNullOrWhiteSpace(style.NumberFormat.Format)) { var formatCodes = XLPredefinedFormat.FormatCodes; if (formatCodes.ContainsKey(style.NumberFormat.NumberFormatId)) @@ -1996,7 +1996,7 @@ private string GetFormula(string strValue, FormulaConversionType conversionType, int rowsToShift, int columnsToShift) { - if (XLHelper.IsNullOrWhiteSpace(strValue)) + if (String.IsNullOrWhiteSpace(strValue)) return String.Empty; var value = ">" + strValue + "<"; @@ -2277,7 +2277,7 @@ internal static String ShiftFormulaRows(String formulaA1, XLWorksheet worksheetInAction, XLRange shiftedRange, int rowsShifted) { - if (XLHelper.IsNullOrWhiteSpace(formulaA1)) return String.Empty; + if (String.IsNullOrWhiteSpace(formulaA1)) return String.Empty; var value = formulaA1; @@ -2426,7 +2426,7 @@ internal static String ShiftFormulaColumns(String formulaA1, XLWorksheet worksheetInAction, XLRange shiftedRange, int columnsShifted) { - if (XLHelper.IsNullOrWhiteSpace(formulaA1)) return String.Empty; + if (String.IsNullOrWhiteSpace(formulaA1)) return String.Empty; var value = formulaA1; @@ -2695,7 +2695,7 @@ #endregion XLCell Right - public Boolean HasFormula { get { return !XLHelper.IsNullOrWhiteSpace(FormulaA1); } } + public Boolean HasFormula { get { return !String.IsNullOrWhiteSpace(FormulaA1); } } public Boolean HasArrayFormula { get { return FormulaA1.StartsWith("{"); } } diff --git a/ClosedXML/Excel/DataValidation/XLDataValidation.cs b/ClosedXML/Excel/DataValidation/XLDataValidation.cs index 8c189db..3ee5896 100644 --- a/ClosedXML/Excel/DataValidation/XLDataValidation.cs +++ b/ClosedXML/Excel/DataValidation/XLDataValidation.cs @@ -48,9 +48,9 @@ return AllowedValues != XLAllowedValues.AnyValue || (ShowInputMessage && - (!XLHelper.IsNullOrWhiteSpace(InputTitle) || !XLHelper.IsNullOrWhiteSpace(InputMessage))) + (!String.IsNullOrWhiteSpace(InputTitle) || !String.IsNullOrWhiteSpace(InputMessage))) ||(ShowErrorMessage && - (!XLHelper.IsNullOrWhiteSpace(ErrorTitle) || !XLHelper.IsNullOrWhiteSpace(ErrorMessage))); + (!String.IsNullOrWhiteSpace(ErrorTitle) || !String.IsNullOrWhiteSpace(ErrorMessage))); } diff --git a/ClosedXML/Excel/IXLWorksheets.cs b/ClosedXML/Excel/IXLWorksheets.cs index f811507..180c304 100644 --- a/ClosedXML/Excel/IXLWorksheets.cs +++ b/ClosedXML/Excel/IXLWorksheets.cs @@ -4,21 +4,28 @@ namespace ClosedXML.Excel { - public interface IXLWorksheets: IEnumerable + public interface IXLWorksheets : IEnumerable { int Count { get; } - bool TryGetWorksheet(string sheetName,out IXLWorksheet worksheet); + + bool TryGetWorksheet(string sheetName, out IXLWorksheet worksheet); IXLWorksheet Worksheet(String sheetName); - IXLWorksheet Worksheet(Int32 position); - IXLWorksheet Add(String sheetName); - IXLWorksheet Add(String sheetName, Int32 position); - IXLWorksheet Add(DataTable dataTable); - IXLWorksheet Add(DataTable dataTable, String sheetName); - void Add(DataSet dataSet); - void Delete(String sheetName); - void Delete(Int32 position); - + IXLWorksheet Worksheet(Int32 position); + + IXLWorksheet Add(String sheetName); + + IXLWorksheet Add(String sheetName, Int32 position); + + IXLWorksheet Add(DataTable dataTable); + + IXLWorksheet Add(DataTable dataTable, String sheetName); + + void Add(DataSet dataSet); + + void Delete(String sheetName); + + void Delete(Int32 position); } } diff --git a/ClosedXML/Excel/Misc/XLFormula.cs b/ClosedXML/Excel/Misc/XLFormula.cs index 34cea84..8027667 100644 --- a/ClosedXML/Excel/Misc/XLFormula.cs +++ b/ClosedXML/Excel/Misc/XLFormula.cs @@ -44,7 +44,7 @@ else { _value = value.Trim(); - IsFormula = !XLHelper.IsNullOrWhiteSpace(_value) && _value.TrimStart()[0] == '=' ; + IsFormula = !String.IsNullOrWhiteSpace(_value) && _value.TrimStart()[0] == '=' ; if (IsFormula) _value = _value.Substring(1); } diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index c4f79e9..f2a9831 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -1081,7 +1081,7 @@ { foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) { - foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !XLHelper.IsNullOrWhiteSpace(c.FormulaA1))) + foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !String.IsNullOrWhiteSpace(c.FormulaA1))) using (var asRange = AsRange()) cell.ShiftFormulaColumns(asRange, numberOfColumns); } @@ -1297,7 +1297,7 @@ using (var asRange = AsRange()) foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) { - foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !XLHelper.IsNullOrWhiteSpace(c.FormulaA1))) + foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !String.IsNullOrWhiteSpace(c.FormulaA1))) cell.ShiftFormulaRows(asRange, numberOfRows); } @@ -1458,7 +1458,7 @@ XLCell cell in Worksheet.Workbook.Worksheets.Cast().SelectMany( xlWorksheet => (xlWorksheet).Internals.CellsCollection.GetCells( - c => !XLHelper.IsNullOrWhiteSpace(c.FormulaA1)))) + c => !String.IsNullOrWhiteSpace(c.FormulaA1)))) { if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) cell.ShiftFormulaRows((XLRange)shiftedRangeFormula, numberOfRows * -1); @@ -1746,7 +1746,7 @@ public IXLRangeBase Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { SortColumns.Clear(); - if (XLHelper.IsNullOrWhiteSpace(columnsToSortBy)) + if (String.IsNullOrWhiteSpace(columnsToSortBy)) { columnsToSortBy = DefaultSortString(); } diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index d42ddf1..528eb6d 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -96,7 +96,7 @@ continue; } - if (XLHelper.IsNullOrWhiteSpace(name)) + if (String.IsNullOrWhiteSpace(name)) { name = GetUniqueName("Column", cellPos + 1, true); cell.SetValue(name); @@ -219,13 +219,39 @@ get { return _name; } set { - if (Worksheet.Tables.Any(t => t.Name == value)) - { - throw new ArgumentException(String.Format("This worksheet already contains a table named '{0}'", - value)); - } + if (_name == value) return; + + // Validation rules for table names + var oldname = _name ?? string.Empty; + + if (String.IsNullOrWhiteSpace(value)) + throw new ArgumentException($"The table name '{value}' is invalid"); + + // Table names are case insensitive + if (!oldname.Equals(value, StringComparison.OrdinalIgnoreCase) + && Worksheet.Tables.Any(t => t.Name.Equals(value, StringComparison.OrdinalIgnoreCase))) + throw new ArgumentException($"This worksheet already contains a table named '{value}'"); + + if (value[0] != '_' && !char.IsLetter(value[0])) + throw new ArgumentException($"The table name '{value}' does not begin with a letter or an underscore"); + + if (value.Length > 255) + throw new ArgumentException("The table name is more than 255 characters"); + + if (new[] { 'C', 'R' }.Any(c => value.ToUpper().Equals(c.ToString()))) + throw new ArgumentException($"The table name '{value}' is invalid"); _name = value; + + // Some totals row formula depend on the table name. Update them. + if (_fieldNames?.Any() ?? false) + this.Fields.ForEach(f => (f as XLTableField).UpdateTableFieldTotalsRowFormula()); + + if (!String.IsNullOrWhiteSpace(oldname)) + { + Worksheet.Tables.Add(this); + Worksheet.Tables.Remove(oldname); + } } } @@ -337,6 +363,8 @@ var existingHeaders = this.FieldNames.Keys; var newHeaders = new HashSet(); + + // Force evaluation of f.Column field var tempArray = this.Fields.Select(f => f.Column).ToArray(); var firstRow = range.Row(1); @@ -347,7 +375,7 @@ var co = 1; foreach (var c in firstRow.Cells()) { - if (XLHelper.IsNullOrWhiteSpace(((XLCell)c).InnerText)) + if (String.IsNullOrWhiteSpace(((XLCell)c).InnerText)) c.Value = GetUniqueName("Column", co, true); var header = c.GetString(); @@ -391,9 +419,9 @@ { foreach (var f in this._fieldNames.Values.Cast()) { - f.UpdateUnderlyingCellFormula(); + f.UpdateTableFieldTotalsRowFormula(); var c = this.TotalsRow().Cell(f.Index + 1); - if (!XLHelper.IsNullOrWhiteSpace(f.TotalsRowLabel)) + if (!String.IsNullOrWhiteSpace(f.TotalsRowLabel)) { c.DataType = XLDataType.Text; @@ -566,7 +594,7 @@ Int32 co = 1; foreach (IXLCell c in range.Row(1).Cells()) { - if (XLHelper.IsNullOrWhiteSpace(((XLCell)c).InnerText)) + if (String.IsNullOrWhiteSpace(((XLCell)c).InnerText)) c.Value = GetUniqueName("Column", co, true); _uniqueNames.Add(c.GetString()); co++; @@ -619,7 +647,7 @@ Int32 co = 1; foreach (IXLCell c in headersRow.Cells()) { - if (XLHelper.IsNullOrWhiteSpace(((XLCell)c).InnerText)) + if (String.IsNullOrWhiteSpace(((XLCell)c).InnerText)) c.Value = GetUniqueName("Column", co, true); _uniqueNames.Add(c.GetString()); co++; diff --git a/ClosedXML/Excel/Tables/XLTableField.cs b/ClosedXML/Excel/Tables/XLTableField.cs index 893d3ff..dabb701 100644 --- a/ClosedXML/Excel/Tables/XLTableField.cs +++ b/ClosedXML/Excel/Tables/XLTableField.cs @@ -1,4 +1,5 @@ using System; +using System.Collections.Generic; using System.Diagnostics; using System.Linq; @@ -89,7 +90,7 @@ set { totalsRowFunction = value; - UpdateUnderlyingCellFormula(); + UpdateTableFieldTotalsRowFormula(); } } @@ -173,25 +174,37 @@ return distinctStyles.Count() == 1; } - internal void UpdateUnderlyingCellFormula() + private static IEnumerable QuotedTableFieldCharacters = new[] { "'", "#" }; + + internal void UpdateTableFieldTotalsRowFormula() { if (TotalsRowFunction != XLTotalsRowFunction.None && TotalsRowFunction != XLTotalsRowFunction.Custom) { var cell = table.TotalsRow().Cell(Index + 1); - String formula = String.Empty; + var formulaCode = String.Empty; switch (TotalsRowFunction) { - case XLTotalsRowFunction.Sum: formula = "109"; break; - case XLTotalsRowFunction.Minimum: formula = "105"; break; - case XLTotalsRowFunction.Maximum: formula = "104"; break; - case XLTotalsRowFunction.Average: formula = "101"; break; - case XLTotalsRowFunction.Count: formula = "103"; break; - case XLTotalsRowFunction.CountNumbers: formula = "102"; break; - case XLTotalsRowFunction.StandardDeviation: formula = "107"; break; - case XLTotalsRowFunction.Variance: formula = "110"; break; + case XLTotalsRowFunction.Sum: formulaCode = "109"; break; + case XLTotalsRowFunction.Minimum: formulaCode = "105"; break; + case XLTotalsRowFunction.Maximum: formulaCode = "104"; break; + case XLTotalsRowFunction.Average: formulaCode = "101"; break; + case XLTotalsRowFunction.Count: formulaCode = "103"; break; + case XLTotalsRowFunction.CountNumbers: formulaCode = "102"; break; + case XLTotalsRowFunction.StandardDeviation: formulaCode = "107"; break; + case XLTotalsRowFunction.Variance: formulaCode = "110"; break; } - cell.FormulaA1 = "SUBTOTAL(" + formula + ",[" + Name + "])"; + var modifiedName = Name; + QuotedTableFieldCharacters.ForEach(c => modifiedName = modifiedName.Replace(c, "'" + c)); + + if (modifiedName.StartsWith(" ") || modifiedName.EndsWith(" ")) + { + modifiedName = "[" + modifiedName + "]"; + } + + var prependTableName = modifiedName.Contains(" "); + + cell.FormulaA1 = $"SUBTOTAL({formulaCode},{(prependTableName ? table.Name : string.Empty)}[{modifiedName}])"; var lastCell = table.LastRow().Cell(Index + 1); if (lastCell.DataType != XLDataType.Text) { diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index c65a114..7ca1145 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -279,7 +279,7 @@ String reference = dTable.Reference.Value; String tableName = dTable?.Name ?? dTable.DisplayName ?? string.Empty; - if (XLHelper.IsNullOrWhiteSpace(tableName)) + if (String.IsNullOrWhiteSpace(tableName)) throw new InvalidDataException("The table name is missing."); var xlTable = ws.Range(reference).CreateTable(tableName, false) as XLTable; @@ -481,10 +481,10 @@ { var pt = ws.PivotTables.AddNew(pivotTableDefinition.Name, target, source) as XLPivotTable; - if (!XLHelper.IsNullOrWhiteSpace(StringValue.ToString(pivotTableDefinition?.ColumnHeaderCaption ?? String.Empty))) + if (!String.IsNullOrWhiteSpace(StringValue.ToString(pivotTableDefinition?.ColumnHeaderCaption ?? String.Empty))) pt.SetColumnHeaderCaption(StringValue.ToString(pivotTableDefinition.ColumnHeaderCaption)); - if (!XLHelper.IsNullOrWhiteSpace(StringValue.ToString(pivotTableDefinition?.RowHeaderCaption ?? String.Empty))) + if (!String.IsNullOrWhiteSpace(StringValue.ToString(pivotTableDefinition?.RowHeaderCaption ?? String.Empty))) pt.SetRowHeaderCaption(StringValue.ToString(pivotTableDefinition.RowHeaderCaption)); pt.RelId = worksheetPart.GetIdOfPart(pivotTablePart); @@ -1377,7 +1377,7 @@ { if (cell.CellFormula.SharedIndex != null) xlCell.FormulaR1C1 = sharedFormulasR1C1[cell.CellFormula.SharedIndex.Value]; - else if (!XLHelper.IsNullOrWhiteSpace(cell.CellFormula.Text)) + else if (!String.IsNullOrWhiteSpace(cell.CellFormula.Text)) { String formula; if (cell.CellFormula.FormulaType != null && cell.CellFormula.FormulaType == CellFormulaValues.Array) @@ -1422,7 +1422,7 @@ } else if (cell.DataType == CellValues.SharedString) { - if (cell.CellValue != null && !XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) + if (cell.CellValue != null && !String.IsNullOrWhiteSpace(cell.CellValue.Text)) { var sharedString = sharedStrings[Int32.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture)]; ParseCellValue(sharedString, xlCell); @@ -1434,7 +1434,7 @@ } else if (cell.DataType == CellValues.Date) { - if (cell.CellValue != null && !XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) + if (cell.CellValue != null && !String.IsNullOrWhiteSpace(cell.CellValue.Text)) xlCell._cellValue = Double.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); xlCell._dataType = XLDataType.DateTime; } @@ -1446,7 +1446,7 @@ } else if (cell.DataType == CellValues.Number) { - if (cell.CellValue != null && !XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) + if (cell.CellValue != null && !String.IsNullOrWhiteSpace(cell.CellValue.Text)) xlCell._cellValue = Double.Parse(cell.CellValue.Text, XLHelper.NumberStyle, XLHelper.ParseCulture).ToInvariantString(); if (s == null) @@ -1464,7 +1464,7 @@ else { var numberFormatId = ((CellFormat)(s.CellFormats).ElementAt(styleIndex)).NumberFormatId; - if (!XLHelper.IsNullOrWhiteSpace(cell.CellValue.Text)) + if (!String.IsNullOrWhiteSpace(cell.CellValue.Text)) xlCell._cellValue = Double.Parse(cell.CellValue.Text, CultureInfo.InvariantCulture).ToInvariantString(); if (s.NumberingFormats != null && @@ -1789,7 +1789,7 @@ return XLDataType.Text; else { - if (!XLHelper.IsNullOrWhiteSpace(numberFormat.Format)) + if (!String.IsNullOrWhiteSpace(numberFormat.Format)) { var dataType = GetDataTypeFromFormat(numberFormat.Format); return dataType.HasValue ? dataType.Value : XLDataType.Number; @@ -2090,7 +2090,7 @@ foreach (DataValidation dvs in dataValidations.Elements()) { String txt = dvs.SequenceOfReferences.InnerText; - if (XLHelper.IsNullOrWhiteSpace(txt)) continue; + if (String.IsNullOrWhiteSpace(txt)) continue; foreach (var rangeAddress in txt.Split(' ')) { using (var range = ws.Range(rangeAddress)) @@ -2146,7 +2146,7 @@ if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.CellIs && fr.Operator != null) conditionalFormat.Operator = fr.Operator.Value.ToClosedXml(); - if (fr.Text != null && !XLHelper.IsNullOrWhiteSpace(fr.Text)) + if (fr.Text != null && !String.IsNullOrWhiteSpace(fr.Text)) conditionalFormat.Values.Add(GetFormula(fr.Text.Value)); if (conditionalFormat.ConditionalFormatType == XLConditionalFormatType.Top10) @@ -2178,7 +2178,7 @@ conditionalFormat.ShowBarOnly = !dataBar.ShowValue.Value; var id = fr.Descendants().FirstOrDefault(); - if (id != null && id.Text != null && !XLHelper.IsNullOrWhiteSpace(id.Text)) + if (id != null && id.Text != null && !String.IsNullOrWhiteSpace(id.Text)) conditionalFormat.Id = new Guid(id.Text.Substring(1, id.Text.Length - 2)); ExtractConditionalFormatValueObjects(conditionalFormat, dataBar); diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index c6bfa21..7c89863 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -235,12 +235,12 @@ // Ensure all RelId's have been added to the context context.RelIdGenerator.AddValues(workbookPart.Parts.Select(p => p.RelationshipId), RelType.Workbook); - context.RelIdGenerator.AddValues(WorksheetsInternal.Cast().Where(ws => !XLHelper.IsNullOrWhiteSpace(ws.RelId)).Select(ws => ws.RelId), RelType.Workbook); - context.RelIdGenerator.AddValues(WorksheetsInternal.Cast().Where(ws => !XLHelper.IsNullOrWhiteSpace(ws.LegacyDrawingId)).Select(ws => ws.LegacyDrawingId), RelType.Workbook); + context.RelIdGenerator.AddValues(WorksheetsInternal.Cast().Where(ws => !String.IsNullOrWhiteSpace(ws.RelId)).Select(ws => ws.RelId), RelType.Workbook); + context.RelIdGenerator.AddValues(WorksheetsInternal.Cast().Where(ws => !String.IsNullOrWhiteSpace(ws.LegacyDrawingId)).Select(ws => ws.LegacyDrawingId), RelType.Workbook); context.RelIdGenerator.AddValues(WorksheetsInternal .Cast() .SelectMany(ws => ws.Tables.Cast()) - .Where(t => !XLHelper.IsNullOrWhiteSpace(t.RelId)) + .Where(t => !String.IsNullOrWhiteSpace(t.RelId)) .Select(t => t.RelId), RelType.Workbook); var extendedFilePropertiesPart = document.ExtendedFilePropertiesPart ?? @@ -292,7 +292,7 @@ var vmlDrawingPart = worksheetPart.VmlDrawingParts.FirstOrDefault(); if (vmlDrawingPart == null) { - if (XLHelper.IsNullOrWhiteSpace(worksheet.LegacyDrawingId)) + if (String.IsNullOrWhiteSpace(worksheet.LegacyDrawingId)) { worksheet.LegacyDrawingId = context.RelIdGenerator.GetNext(RelType.Workbook); worksheet.LegacyDrawingIsNew = true; @@ -510,7 +510,7 @@ if (Properties.Manager != null) { - if (!XLHelper.IsNullOrWhiteSpace(Properties.Manager)) + if (!String.IsNullOrWhiteSpace(Properties.Manager)) { if (properties.Manager == null) properties.Manager = new Manager(); @@ -523,7 +523,7 @@ if (Properties.Company == null) return; - if (!XLHelper.IsNullOrWhiteSpace(Properties.Company)) + if (!String.IsNullOrWhiteSpace(Properties.Company)) { if (properties.Company == null) properties.Company = new Company(); @@ -653,7 +653,7 @@ foreach (var xlSheet in WorksheetsInternal.Cast().OrderBy(w => w.Position)) { string rId; - if (xlSheet.SheetId == 0 && XLHelper.IsNullOrWhiteSpace(xlSheet.RelId)) + if (xlSheet.SheetId == 0 && String.IsNullOrWhiteSpace(xlSheet.RelId)) { rId = context.RelIdGenerator.GetNext(RelType.Workbook); @@ -665,7 +665,7 @@ } else { - if (XLHelper.IsNullOrWhiteSpace(xlSheet.RelId)) + if (String.IsNullOrWhiteSpace(xlSheet.RelId)) { rId = String.Concat("rId", xlSheet.SheetId); context.RelIdGenerator.AddValues(new List { rId }, RelType.Workbook); @@ -812,7 +812,7 @@ if (!nr.Visible) definedName.Hidden = BooleanValue.FromBoolean(true); - if (!XLHelper.IsNullOrWhiteSpace(nr.Comment)) + if (!String.IsNullOrWhiteSpace(nr.Comment)) definedName.Comment = nr.Comment; definedNames.AppendChild(definedName); } @@ -866,7 +866,7 @@ if (!nr.Visible) definedName.Hidden = BooleanValue.FromBoolean(true); - if (!XLHelper.IsNullOrWhiteSpace(nr.Comment)) + if (!String.IsNullOrWhiteSpace(nr.Comment)) definedName.Comment = nr.Comment; definedNames.AppendChild(definedName); } @@ -912,7 +912,7 @@ w.Internals.CellsCollection.GetCells( c => ((c.DataType == XLDataType.Text && c.ShareString) || c.HasRichText) && (c as XLCell).InnerText.Length > 0 - && XLHelper.IsNullOrWhiteSpace(c.FormulaA1) + && String.IsNullOrWhiteSpace(c.FormulaA1) ))) { c.DataType = XLDataType.Text; @@ -1060,7 +1060,7 @@ var cellsWithoutFormulas = new HashSet(); foreach (var c in worksheet.Internals.CellsCollection.GetCells()) { - if (XLHelper.IsNullOrWhiteSpace(c.FormulaA1)) + if (String.IsNullOrWhiteSpace(c.FormulaA1)) cellsWithoutFormulas.Add(c.Address.ToStringRelative()); else { @@ -1904,7 +1904,7 @@ while (formula.StartsWith("=") && formula.Length > 1) formula = formula.Substring(1); - if (!XLHelper.IsNullOrWhiteSpace(formula)) + if (!String.IsNullOrWhiteSpace(formula)) { tableColumn.CalculatedColumnFormula = new CalculatedColumnFormula { @@ -1925,7 +1925,7 @@ tableColumn.TotalsRowFormula = new TotalsRowFormula(xlField.TotalsRowFormulaA1); } - if (!XLHelper.IsNullOrWhiteSpace(xlField.TotalsRowLabel)) + if (!String.IsNullOrWhiteSpace(xlField.TotalsRowLabel)) tableColumn.TotalsRowLabel = xlField.TotalsRowLabel; } tableColumns.AppendChild(tableColumn); @@ -1988,7 +1988,7 @@ PivotCache pivotCache; PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart; - if (!XLHelper.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) + if (!String.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) { pivotCache = pivotCaches.Cast().Single(pc => pc.Id.Value == pt.WorkbookCacheRelId); pivotTableCacheDefinitionPart = workbookPart.GetPartById(pt.WorkbookCacheRelId) as PivotTableCacheDefinitionPart; @@ -2002,18 +2002,18 @@ GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt, context); - if (XLHelper.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) + if (String.IsNullOrWhiteSpace(pt.WorkbookCacheRelId)) pivotCaches.AppendChild(pivotCache); PivotTablePart pivotTablePart; - if (XLHelper.IsNullOrWhiteSpace(pt.RelId)) + if (String.IsNullOrWhiteSpace(pt.RelId)) pivotTablePart = worksheetPart.AddNewPart(context.RelIdGenerator.GetNext(RelType.Workbook)); else pivotTablePart = worksheetPart.GetPartById(pt.RelId) as PivotTablePart; GeneratePivotTablePartContent(pivotTablePart, pt, pivotCache.CacheId, context); - if (XLHelper.IsNullOrWhiteSpace(pt.RelId)) + if (String.IsNullOrWhiteSpace(pt.RelId)) pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook)); } } @@ -2878,7 +2878,7 @@ StrokeWeight = String.Concat(c.Comment.Style.ColorsAndLines.LineWeight.ToInvariantString(), "pt"), InsetMode = c.Comment.Style.Margins.Automatic ? InsetMarginValues.Auto : InsetMarginValues.Custom }; - if (!XLHelper.IsNullOrWhiteSpace(c.Comment.Style.Web.AlternateText)) + if (!String.IsNullOrWhiteSpace(c.Comment.Style.Web.AlternateText)) shape.Alternate = c.Comment.Style.Web.AlternateText; return shape; @@ -3406,8 +3406,6 @@ { foreach (var cf in ws.ConditionalFormats) { - //var ie = context.DifferentialFormats.Keys.First().Equals(cf.Style); - if (!cf.Style.Value.Equals(DefaultStyle.Value) && !context.DifferentialFormats.ContainsKey(cf.Style.Value.Key)) AddConditionalDifferentialFormat(workbookStylesPart.Stylesheet.DifferentialFormats, cf, context); } @@ -3462,7 +3460,7 @@ if (diffFont?.HasChildren ?? false) differentialFormat.Append(diffFont); - if (!XLHelper.IsNullOrWhiteSpace(cf.Style.NumberFormat.Format)) + if (!String.IsNullOrWhiteSpace(cf.Style.NumberFormat.Format)) { var numberFormat = new NumberingFormat { @@ -3494,7 +3492,7 @@ if (diffFont?.HasChildren ?? false) differentialFormat.Append(diffFont); - if (!XLHelper.IsNullOrWhiteSpace(style.NumberFormat.Format) || style.NumberFormat.NumberFormatId != 0) + if (!String.IsNullOrWhiteSpace(style.NumberFormat.Format) || style.NumberFormat.NumberFormatId != 0) { var numberFormat = new NumberingFormat(); @@ -4252,7 +4250,7 @@ private static bool NumberFormatsAreEqual(NumberingFormat nf, XLNumberFormatValue xlNumberFormat) { - if (nf.FormatCode != null && !XLHelper.IsNullOrWhiteSpace(nf.FormatCode.Value)) + if (nf.FormatCode != null && !String.IsNullOrWhiteSpace(nf.FormatCode.Value)) return string.Equals(xlNumberFormat?.Format, nf.FormatCode.Value); else if (nf.NumberFormatId != null) return xlNumberFormat?.NumberFormatId == (Int32)nf.NumberFormatId.Value; @@ -4847,7 +4845,7 @@ var table = xlWorksheet.Tables.First(t => t.AsRange().Contains(xlCell)); field = table.Fields.First(f => f.Column.ColumnNumber() == xlCell.Address.ColumnNumber) as XLTableField; - if (!XLHelper.IsNullOrWhiteSpace(field.TotalsRowLabel)) + if (!String.IsNullOrWhiteSpace(field.TotalsRowLabel)) { cell.DataType = XLWorkbook.CvSharedString; } @@ -4930,7 +4928,7 @@ var protection = xlWorksheet.Protection; sheetProtection.Sheet = protection.Protected; - if (!XLHelper.IsNullOrWhiteSpace(protection.PasswordHash)) + if (!String.IsNullOrWhiteSpace(protection.PasswordHash)) sheetProtection.Password = protection.PasswordHash; sheetProtection.FormatCells = OpenXmlHelper.GetBooleanValue(!protection.FormatCells, true); sheetProtection.FormatColumns = OpenXmlHelper.GetBooleanValue(!protection.FormatColumns, true); @@ -5185,7 +5183,7 @@ Display = hl.Cell.GetFormattedString() }; } - if (!XLHelper.IsNullOrWhiteSpace(hl.Tooltip)) + if (!String.IsNullOrWhiteSpace(hl.Tooltip)) hyperlink.Tooltip = hl.Tooltip; hyperlinks.AppendChild(hyperlink); } @@ -5512,7 +5510,7 @@ { if (field != null) { - if (!XLHelper.IsNullOrWhiteSpace(field.TotalsRowLabel)) + if (!String.IsNullOrWhiteSpace(field.TotalsRowLabel)) { var cellValue = new CellValue(); cellValue.Text = xlCell.SharedStringId.ToString(); @@ -5578,7 +5576,7 @@ } else if (dataType == XLDataType.DateTime || dataType == XLDataType.Number) { - if (!XLHelper.IsNullOrWhiteSpace(xlCell.InnerText)) + if (!String.IsNullOrWhiteSpace(xlCell.InnerText)) { var cellValue = new CellValue(); var d = Double.Parse(xlCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture); diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index 9e786d1..5775416 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -161,7 +161,7 @@ throw new ArgumentException("Worksheet names cannot contain any of the following characters: " + InvalidNameChars); - if (XLHelper.IsNullOrWhiteSpace(value)) + if (String.IsNullOrWhiteSpace(value)) throw new ArgumentException("Worksheet names cannot be empty"); if (value.Length > 31) @@ -184,7 +184,7 @@ set { if (value > Workbook.WorksheetsInternal.Count + Workbook.UnsupportedSheets.Count + 1) - throw new IndexOutOfRangeException("Index must be equal or less than the number of worksheets + 1."); + throw new ArgumentOutOfRangeException(nameof(value), "Index must be equal or less than the number of worksheets + 1."); if (value < _position) { diff --git a/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/Excel/XLWorksheets.cs index 01aa54b..94cace4 100644 --- a/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/Excel/XLWorksheets.cs @@ -130,7 +130,7 @@ "Can't delete the worksheet because there are multiple worksheets associated with that index."); var ws = _worksheets.Values.Single(w => w.Position == position); - if (!XLHelper.IsNullOrWhiteSpace(ws.RelId) && !Deleted.Contains(ws.RelId)) + if (!String.IsNullOrWhiteSpace(ws.RelId) && !Deleted.Contains(ws.RelId)) Deleted.Add(ws.RelId); _worksheets.RemoveAll(w => w.Position == position); @@ -171,7 +171,7 @@ public void Rename(String oldSheetName, String newSheetName) { - if (XLHelper.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName.ToLowerInvariant())) return; + if (String.IsNullOrWhiteSpace(oldSheetName) || !_worksheets.ContainsKey(oldSheetName.ToLowerInvariant())) return; if (!oldSheetName.Equals(newSheetName, StringComparison.OrdinalIgnoreCase) && _worksheets.Any(ws1 => ws1.Key.Equals(newSheetName, StringComparison.OrdinalIgnoreCase))) diff --git a/ClosedXML/Extensions.cs b/ClosedXML/Extensions.cs index 88c1f8d..5d14a91 100644 --- a/ClosedXML/Extensions.cs +++ b/ClosedXML/Extensions.cs @@ -215,7 +215,7 @@ { public static Double GetWidth(this IXLFontBase fontBase, String text, Dictionary fontCache) { - if (XLHelper.IsNullOrWhiteSpace(text)) + if (String.IsNullOrWhiteSpace(text)) return 0; var font = GetCachedFont(fontBase, fontCache); diff --git a/ClosedXML/XLHelper.cs b/ClosedXML/XLHelper.cs index 6f25f72..0796264 100644 --- a/ClosedXML/XLHelper.cs +++ b/ClosedXML/XLHelper.cs @@ -117,7 +117,7 @@ public static bool IsValidColumn(string column) { var length = column.Length; - if (XLHelper.IsNullOrWhiteSpace(column) || length > 3) + if (String.IsNullOrWhiteSpace(column) || length > 3) return false; var theColumn = column.ToUpper(); @@ -153,7 +153,7 @@ public static bool IsValidA1Address(string address) { - if (XLHelper.IsNullOrWhiteSpace(address)) + if (String.IsNullOrWhiteSpace(address)) return false; address = address.Replace("$", ""); @@ -226,7 +226,8 @@ return rows; } - +#if false +// Not using this anymore, but keeping it around for in case we bring back .NET3.5 support. public static bool IsNullOrWhiteSpace(string value) { #if _NET35_ @@ -236,6 +237,8 @@ return String.IsNullOrWhiteSpace(value); #endif } +#endif + private static readonly Regex A1RegexRelative = new Regex( @"(?<=\W)(?\$?[a-zA-Z]{1,3}\$?\d{1,7})(?=\W)" // A1 + @"|(?<=\W)(?\$?\d{1,7}:\$?\d{1,7})(?=\W)" // 1:1 diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index 5df66aa..e5868fa 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -44,11 +44,11 @@ using (var wb = new XLWorkbook()) { - wb.AddWorksheet(dt); + wb.AddWorksheet(dt); - using (var ms = new MemoryStream()) - wb.SaveAs(ms, true); - } + using (var ms = new MemoryStream()) + wb.SaveAs(ms, true); + } } [Test] @@ -70,13 +70,13 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Title"); - ws.Range("A1").CreateTable(); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Title"); + ws.Range("A1").CreateTable(); - using (var ms = new MemoryStream()) - wb.SaveAs(ms, true); - } + using (var ms = new MemoryStream()) + wb.SaveAs(ms, true); + } } [Test] @@ -84,14 +84,14 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Title") - .CellBelow().SetValue("X"); - ws.Range("A1").CreateTable(); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Title") + .CellBelow().SetValue("X"); + ws.Range("A1").CreateTable(); - Assert.AreEqual(ws.Cell("A2").GetString(), String.Empty); - Assert.AreEqual(ws.Cell("A3").GetString(), "X"); - } + Assert.AreEqual(ws.Cell("A2").GetString(), String.Empty); + Assert.AreEqual(ws.Cell("A3").GetString(), "X"); + } } [Test] @@ -99,16 +99,16 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Categories") - .CellBelow().SetValue("A") - .CellBelow().SetValue("B") - .CellBelow().SetValue("C"); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Categories") + .CellBelow().SetValue("A") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C"); - IXLTable table = ws.RangeUsed().CreateTable(); - table.InsertColumnsAfter(1); - Assert.AreEqual("Column2", table.HeadersRow().LastCell().GetString()); - } + IXLTable table = ws.RangeUsed().CreateTable(); + table.InsertColumnsAfter(1); + Assert.AreEqual("Column2", table.HeadersRow().LastCell().GetString()); + } } [Test] @@ -116,54 +116,54 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - string columnName = "Line1" + Environment.NewLine + "Line2"; - ws.FirstCell().SetValue(columnName) - .CellBelow().SetValue("A"); - ws.RangeUsed().CreateTable(); - using (var ms = new MemoryStream()) - { - wb.SaveAs(ms, true); - var wb2 = new XLWorkbook(ms); - IXLWorksheet ws2 = wb2.Worksheet(1); - IXLTable table2 = ws2.Table(0); - string fieldName = table2.Field(0).Name; - Assert.AreEqual("Line1\nLine2", fieldName); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + string columnName = "Line1" + Environment.NewLine + "Line2"; + ws.FirstCell().SetValue(columnName) + .CellBelow().SetValue("A"); + ws.RangeUsed().CreateTable(); + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + var wb2 = new XLWorkbook(ms); + IXLWorksheet ws2 = wb2.Worksheet(1); + IXLTable table2 = ws2.Table(0); + string fieldName = table2.Field(0).Name; + Assert.AreEqual("Line1\nLine2", fieldName); + } } } - } [Test] public void SavingLoadingTableWithNewLineInHeader2() { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.Worksheets.Add("Test"); + IXLWorksheet ws = wb.Worksheets.Add("Test"); - var dt = new DataTable(); - string columnName = "Line1" + Environment.NewLine + "Line2"; - dt.Columns.Add(columnName); + var dt = new DataTable(); + string columnName = "Line1" + Environment.NewLine + "Line2"; + dt.Columns.Add(columnName); - DataRow dr = dt.NewRow(); - dr[columnName] = "some text"; - dt.Rows.Add(dr); + DataRow dr = dt.NewRow(); + dr[columnName] = "some text"; + dt.Rows.Add(dr); ws.Cell(1, 1).InsertTable(dt); - IXLTable table1 = ws.Table(0); - string fieldName1 = table1.Field(0).Name; - Assert.AreEqual(columnName, fieldName1); + IXLTable table1 = ws.Table(0); + string fieldName1 = table1.Field(0).Name; + Assert.AreEqual(columnName, fieldName1); - using (var ms = new MemoryStream()) - { - wb.SaveAs(ms, true); - var wb2 = new XLWorkbook(ms); - IXLWorksheet ws2 = wb2.Worksheet(1); - IXLTable table2 = ws2.Table(0); - string fieldName2 = table2.Field(0).Name; - Assert.AreEqual("Line1\nLine2", fieldName2); + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + var wb2 = new XLWorkbook(ms); + IXLWorksheet ws2 = wb2.Worksheet(1); + IXLTable table2 = ws2.Table(0); + string fieldName2 = table2.Field(0).Name; + Assert.AreEqual("Line1\nLine2", fieldName2); + } } } - } [Test] public void TableCreatedFromEmptyDataTable() @@ -174,10 +174,10 @@ using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().InsertTable(dt); - Assert.AreEqual(2, ws.Tables.First().ColumnCount()); - } + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(dt); + Assert.AreEqual(2, ws.Tables.First().ColumnCount()); + } } [Test] @@ -187,10 +187,10 @@ using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().InsertTable(l); - Assert.AreEqual(1, ws.Tables.First().ColumnCount()); - } + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(l); + Assert.AreEqual(1, ws.Tables.First().ColumnCount()); + } } [Test] @@ -200,10 +200,10 @@ using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().InsertTable(l); - Assert.AreEqual(2, ws.Tables.First().ColumnCount()); - } + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(l); + Assert.AreEqual(2, ws.Tables.First().ColumnCount()); + } } [Test] @@ -217,14 +217,14 @@ using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().InsertTable(l); - Assert.AreEqual(4, ws.Tables.First().ColumnCount()); - Assert.AreEqual("FirstColumn", ws.FirstCell().Value); - Assert.AreEqual("SecondColumn", ws.FirstCell().CellRight().Value); - Assert.AreEqual("SomeFieldNotProperty", ws.FirstCell().CellRight().CellRight().Value); - Assert.AreEqual("UnOrderedColumn", ws.FirstCell().CellRight().CellRight().CellRight().Value); - } + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(l); + Assert.AreEqual(4, ws.Tables.First().ColumnCount()); + Assert.AreEqual("FirstColumn", ws.FirstCell().Value); + Assert.AreEqual("SecondColumn", ws.FirstCell().CellRight().Value); + Assert.AreEqual("SomeFieldNotProperty", ws.FirstCell().CellRight().CellRight().Value); + Assert.AreEqual("UnOrderedColumn", ws.FirstCell().CellRight().CellRight().CellRight().Value); + } } [Test] @@ -232,24 +232,24 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Value"); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); - IXLTable table = ws.Range("A1:A2").CreateTable(); - table.SetShowTotalsRow() - .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + IXLTable table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; - IXLTableRow row = table.DataRange.FirstRow(); - row.Field("Value").Value = 3; - row = table.DataRange.InsertRowsAbove(1).First(); - row.Field("Value").Value = 2; - row = table.DataRange.InsertRowsAbove(1).First(); - row.Field("Value").Value = 1; + IXLTableRow row = table.DataRange.FirstRow(); + row.Field("Value").Value = 3; + row = table.DataRange.InsertRowsAbove(1).First(); + row.Field("Value").Value = 2; + row = table.DataRange.InsertRowsAbove(1).First(); + row.Field("Value").Value = 1; - Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); - Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); - Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); - } + Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); + Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); + Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + } } [Test] @@ -257,24 +257,24 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Value"); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); - IXLTable table = ws.Range("A1:A2").CreateTable(); - table.SetShowTotalsRow() - .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + IXLTable table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; - IXLTableRow row = table.DataRange.FirstRow(); - row.Field("Value").Value = 3; - row = row.InsertRowsAbove(1).First(); - row.Field("Value").Value = 2; - row = row.InsertRowsAbove(1).First(); - row.Field("Value").Value = 1; + IXLTableRow row = table.DataRange.FirstRow(); + row.Field("Value").Value = 3; + row = row.InsertRowsAbove(1).First(); + row.Field("Value").Value = 2; + row = row.InsertRowsAbove(1).First(); + row.Field("Value").Value = 1; - Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); - Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); - Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); - } + Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); + Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); + Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + } } [Test] @@ -282,24 +282,24 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Value"); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); - IXLTable table = ws.Range("A1:A2").CreateTable(); - table.SetShowTotalsRow() - .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + IXLTable table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; - IXLTableRow row = table.DataRange.FirstRow(); - row.Field("Value").Value = 1; - row = table.DataRange.InsertRowsBelow(1).First(); - row.Field("Value").Value = 2; - row = table.DataRange.InsertRowsBelow(1).First(); - row.Field("Value").Value = 3; + IXLTableRow row = table.DataRange.FirstRow(); + row.Field("Value").Value = 1; + row = table.DataRange.InsertRowsBelow(1).First(); + row.Field("Value").Value = 2; + row = table.DataRange.InsertRowsBelow(1).First(); + row.Field("Value").Value = 3; - Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); - Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); - Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); - } + Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); + Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); + Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + } } [Test] @@ -307,24 +307,24 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Value"); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Value"); - IXLTable table = ws.Range("A1:A2").CreateTable(); - table.SetShowTotalsRow() - .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; + IXLTable table = ws.Range("A1:A2").CreateTable(); + table.SetShowTotalsRow() + .Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum; - IXLTableRow row = table.DataRange.FirstRow(); - row.Field("Value").Value = 1; - row = row.InsertRowsBelow(1).First(); - row.Field("Value").Value = 2; - row = row.InsertRowsBelow(1).First(); - row.Field("Value").Value = 3; + IXLTableRow row = table.DataRange.FirstRow(); + row.Field("Value").Value = 1; + row = row.InsertRowsBelow(1).First(); + row.Field("Value").Value = 2; + row = row.InsertRowsBelow(1).First(); + row.Field("Value").Value = 3; - Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); - Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); - Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); - } + Assert.AreEqual(1, ws.Cell(2, 1).GetDouble()); + Assert.AreEqual(2, ws.Cell(3, 1).GetDouble()); + Assert.AreEqual(3, ws.Cell(4, 1).GetDouble()); + } } [Test] @@ -332,46 +332,46 @@ { using (var wb = new XLWorkbook()) { - IXLWorksheet ws = wb.AddWorksheet("Sheet1"); - ws.FirstCell().SetValue("Categories") - .CellBelow().SetValue("A") - .CellBelow().SetValue("B") - .CellBelow().SetValue("C"); + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().SetValue("Categories") + .CellBelow().SetValue("A") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C"); - IXLTable table = ws.RangeUsed().CreateTable(); + IXLTable table = ws.RangeUsed().CreateTable(); - Assert.AreEqual("Categories", table.Fields.First().Name); + Assert.AreEqual("Categories", table.Fields.First().Name); - table.SetShowHeaderRow(false); + table.SetShowHeaderRow(false); - Assert.AreEqual("Categories", table.Fields.First().Name); + Assert.AreEqual("Categories", table.Fields.First().Name); - Assert.IsTrue(ws.Cell(1, 1).IsEmpty(true)); - Assert.AreEqual(null, table.HeadersRow()); - Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString()); - Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString()); - Assert.AreEqual("A", table.DataRange.FirstCell().GetString()); - Assert.AreEqual("C", table.DataRange.LastCell().GetString()); + Assert.IsTrue(ws.Cell(1, 1).IsEmpty(true)); + Assert.AreEqual(null, table.HeadersRow()); + Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString()); + Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString()); + Assert.AreEqual("A", table.DataRange.FirstCell().GetString()); + Assert.AreEqual("C", table.DataRange.LastCell().GetString()); - table.SetShowHeaderRow(); - IXLRangeRow headerRow = table.HeadersRow(); - Assert.AreNotEqual(null, headerRow); - Assert.AreEqual("Categories", headerRow.Cell(1).GetString()); + table.SetShowHeaderRow(); + IXLRangeRow headerRow = table.HeadersRow(); + Assert.AreNotEqual(null, headerRow); + Assert.AreEqual("Categories", headerRow.Cell(1).GetString()); - table.SetShowHeaderRow(false); + table.SetShowHeaderRow(false); - ws.FirstCell().SetValue("x"); + ws.FirstCell().SetValue("x"); - table.SetShowHeaderRow(); + table.SetShowHeaderRow(); - Assert.AreEqual("x", ws.FirstCell().GetString()); - Assert.AreEqual("Categories", ws.Cell("A2").GetString()); - Assert.AreNotEqual(null, headerRow); - Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString()); - Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString()); - Assert.AreEqual("A", table.DataRange.FirstCell().GetString()); - Assert.AreEqual("C", table.DataRange.LastCell().GetString()); - } + Assert.AreEqual("x", ws.FirstCell().GetString()); + Assert.AreEqual("Categories", ws.Cell("A2").GetString()); + Assert.AreNotEqual(null, headerRow); + Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString()); + Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString()); + Assert.AreEqual("A", table.DataRange.FirstCell().GetString()); + Assert.AreEqual("C", table.DataRange.LastCell().GetString()); + } } [Test] @@ -379,40 +379,40 @@ { using (var wb = new XLWorkbook()) { - var ws = wb.AddWorksheet("Sheet"); - ws.Cell("A1").SetValue("FName") - .CellBelow().SetValue("John"); + var ws = wb.AddWorksheet("Sheet"); + ws.Cell("A1").SetValue("FName") + .CellBelow().SetValue("John"); - ws.Cell("B1").SetValue("LName") - .CellBelow().SetValue("Doe"); + ws.Cell("B1").SetValue("LName") + .CellBelow().SetValue("Doe"); - var tbl = ws.RangeUsed().CreateTable(); - var nameBefore = tbl.Field(tbl.Fields.Last().Index).Name; - tbl.Field(tbl.Fields.Last().Index).Name = "LastName"; - var nameAfter = tbl.Field(tbl.Fields.Last().Index).Name; + var tbl = ws.RangeUsed().CreateTable(); + var nameBefore = tbl.Field(tbl.Fields.Last().Index).Name; + tbl.Field(tbl.Fields.Last().Index).Name = "LastName"; + var nameAfter = tbl.Field(tbl.Fields.Last().Index).Name; - var cellValue = ws.Cell("B1").GetString(); + var cellValue = ws.Cell("B1").GetString(); - Assert.AreEqual("LName", nameBefore); - Assert.AreEqual("LastName", nameAfter); - Assert.AreEqual("LastName", cellValue); + Assert.AreEqual("LName", nameBefore); + Assert.AreEqual("LastName", nameAfter); + Assert.AreEqual("LastName", cellValue); - tbl.ShowHeaderRow = false; - tbl.Field(tbl.Fields.Last().Index).Name = "LastNameChanged"; - nameAfter = tbl.Field(tbl.Fields.Last().Index).Name; - Assert.AreEqual("LastNameChanged", nameAfter); + tbl.ShowHeaderRow = false; + tbl.Field(tbl.Fields.Last().Index).Name = "LastNameChanged"; + nameAfter = tbl.Field(tbl.Fields.Last().Index).Name; + Assert.AreEqual("LastNameChanged", nameAfter); - tbl.SetShowHeaderRow(true); - nameAfter = tbl.Cell("B1").Value.ToString(); - Assert.AreEqual("LastNameChanged", nameAfter); + tbl.SetShowHeaderRow(true); + nameAfter = tbl.Cell("B1").Value.ToString(); + Assert.AreEqual("LastNameChanged", nameAfter); var field = tbl.Field("LastNameChanged"); Assert.AreEqual("LastNameChanged", field.Name); tbl.Cell(1, 1).Value = "FirstName"; Assert.AreEqual("FirstName", tbl.Field(0).Name); + } } - } [Test] public void CanDeleteTableColumn() @@ -437,7 +437,7 @@ Assert.AreEqual("UnOrderedColumn", table.Fields.Last().Name); Assert.AreEqual(2, table.Fields.Last().Index); -} + } } [Test] @@ -510,8 +510,8 @@ IXLWorksheet ws = wb.AddWorksheet("Sheet1"); ws.FirstCell().InsertTable(dt, true); Assert.Throws(() => ws.FirstCell().CellRight().InsertTable(dt, true)); - } -} + } + } [Test] public void OverwritingTableTotalsRow() @@ -555,6 +555,41 @@ } [Test] + public void TableRenameTests() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } + }; + + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws = wb.AddWorksheet("Sheet1"); + var table1 = ws.FirstCell().InsertTable(l); + var table2 = ws.Cell("A10").InsertTable(l); + + Assert.AreEqual("Table1", table1.Name); + Assert.AreEqual("Table2", table2.Name); + + table1.Name = "table1"; + Assert.AreEqual("table1", table1.Name); + + Assert.Throws(() => table1.Name = ""); + Assert.Throws(() => table1.Name = "R"); + Assert.Throws(() => table1.Name = "C"); + Assert.Throws(() => table1.Name = "r"); + Assert.Throws(() => table1.Name = "c"); + + Assert.Throws(() => table1.Name = "123"); + Assert.Throws(() => table1.Name = new String('A', 256)); + + Assert.Throws(() => table1.Name = "Table2"); + Assert.Throws(() => table1.Name = "TABLE2"); + } + } + + [Test] public void CanResizeTable() { using (var wb = new XLWorkbook()) @@ -692,6 +727,42 @@ } } + [Test] + public void TotalsFunctionsOfHeadersWithWeirdCharacters() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } + }; + + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(l, false); + + // Give the headings weird names (i.e. spaces, hashes, single quotes + ws.Cell("A1").Value = "ABCD "; + ws.Cell("B1").Value = " #BCD"; + ws.Cell("C1").Value = " as'df "; + ws.Cell("D1").Value = "Normal"; + + var table = ws.RangeUsed().CreateTable(); + Assert.IsNotNull(table); + + table.ShowTotalsRow = true; + table.Field(0).TotalsRowFunction = XLTotalsRowFunction.Count; + table.Field(1).TotalsRowFunction = XLTotalsRowFunction.Count; + table.Field(2).TotalsRowFunction = XLTotalsRowFunction.Sum; + table.Field(3).TotalsRowFunction = XLTotalsRowFunction.Sum; + + Assert.AreEqual("SUBTOTAL(103,Table1[[ABCD ]])", table.Field(0).TotalsRowFormulaA1); + Assert.AreEqual("SUBTOTAL(103,Table1[[ '#BCD]])", table.Field(1).TotalsRowFormulaA1); + Assert.AreEqual("SUBTOTAL(109,Table1[[ as''df ]])", table.Field(2).TotalsRowFormulaA1); + Assert.AreEqual("SUBTOTAL(109,[Normal])", table.Field(3).TotalsRowFormulaA1); + } + } + //TODO: Delete table (not underlying range) } }