//internal ExcelPivotTableField _field; internal ExcelPivotTableSlicer(ExcelDrawings drawings, XmlNode node, ExcelPivotTableField field, ExcelGroupShape parent = null) : base(drawings, node, parent) { _ws = drawings.Worksheet; //_field = field; var name = drawings.Worksheet.Workbook.GetSlicerName(field.Cache.Name); CreateDrawing(name); SlicerName = name; Caption = field.Name; RowHeight = 19; if (field.Slicer == null) { CacheName = "Slicer_" + ExcelAddressUtil.GetValidName(name); var cache = new ExcelPivotTableSlicerCache(NameSpaceManager); if (field.Slicer == null) { field.Slicer = this; } cache.Init(drawings.Worksheet.Workbook, name, field); _cache = cache; } else { CacheName = field.Slicer.Cache.Name; _cache = field.Slicer.Cache; } //If items has not been init, refresh! if (field._items == null) { field.Items.Refresh(); } }
private bool HaveTwoRanges(IEnumerable <FunctionArgument> arguments) { if (arguments.Count() == 2) { return(false); } return(ExcelAddressUtil.IsValidAddress(arguments.ElementAt(2).Value.ToString())); }
public void IsValidName() { Assert.IsFalse(ExcelAddressUtil.IsValidName("123sa")); //invalid start char Assert.IsFalse(ExcelAddressUtil.IsValidName("*d")); //invalid start char Assert.IsFalse(ExcelAddressUtil.IsValidName("\t")); //invalid start char Assert.IsFalse(ExcelAddressUtil.IsValidName("\\t")); //Backslash at least three chars Assert.IsFalse(ExcelAddressUtil.IsValidName("A+1")); //invalid char Assert.IsFalse(ExcelAddressUtil.IsValidName("A%we")); //Address invalid Assert.IsFalse(ExcelAddressUtil.IsValidName("BB73")); //Address invalid Assert.IsTrue(ExcelAddressUtil.IsValidName("BBBB75")); //Valid Assert.IsTrue(ExcelAddressUtil.IsValidName("BB1500005")); //Valid }
/// <summary> /// Calculates the row of either the given range or the column that the function is executed in. /// </summary> /// <param name="arguments">The collection of arguments to be used to calculate the row value.</param> /// <param name="context">The context of the function when parsed.</param> /// <returns>Returns a <see cref="CompileResult"/> containing either the resulting row or an error value.</returns> public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context) { var rangeAddress = arguments.Count() == 0 ? null : arguments.ElementAt(0).ValueAsRangeInfo?.Address; if (arguments == null || arguments.Count() == 0 || rangeAddress == null) { return(CreateResult(context.Scopes.Current.Address.FromRow, DataType.Integer)); } if (!ExcelAddressUtil.IsValidAddress(rangeAddress.Address)) { return(new CompileResult(eErrorType.Value)); } return(CreateResult(rangeAddress._fromRow, DataType.Integer)); }
internal static void FormatAsTable(ExcelRangeBase range, TableStyles tableStyle, string tableName, bool autoFitColumns = true) { string escapedTableName = ExcelAddressUtil.GetValidName(tableName); //format the table var table = range.Worksheet.Tables.Add(range, escapedTableName); table.TableStyle = tableStyle; if (autoFitColumns) { range.AutoFitColumns(); } }
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context) { if (arguments == null || arguments.Count() == 0) { return(CreateResult(context.Scopes.Current.Address.FromCol, DataType.Integer)); } var rangeAddress = ArgToAddress(arguments, 0, context); if (!ExcelAddressUtil.IsValidAddress(rangeAddress)) { throw new ArgumentException("An invalid argument was supplied"); } var factory = new RangeAddressFactory(context.ExcelDataProvider); var address = factory.Create(rangeAddress); return(CreateResult(address.FromCol, DataType.Integer)); }
internal void Init(ExcelWorkbook wb, string name, ExcelPivotTableField field) { if (wb._slicerCaches == null) { wb.LoadSlicerCaches(); } CreatePart(wb); TopNode = SlicerCacheXml.DocumentElement; Name = "Slicer_" + ExcelAddressUtil.GetValidName(name); _field = field; SourceName = _field.Cache.Name; wb.Names.AddFormula(Name, "#N/A"); PivotTables.Add(_field._pivotTable); CreateWorkbookReference(wb, ExtLstUris.WorkbookSlicerPivotTableUri); SlicerCacheXml.Save(Part.GetStream()); Data.Items.Refresh(); }
private void ValidateName(string name) { if (string.IsNullOrEmpty(name.Trim())) { throw new ArgumentException("Tablename is blank", "Name"); } var c = name[0]; if (char.IsLetter(c) == false && c != '\\' && c != '_') { throw new ArgumentException("Tablename start with invalid character", "Name"); } if (!ExcelAddressUtil.IsValidName(name)) { throw (new ArgumentException("Tablename is not valid", "Name")); } }
private string GetStartXml(string name, int tblId) { name = ConvertUtil.ExcelEscapeString(name); string xml = "<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\" ?>"; xml += string.Format("<table xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\" id=\"{0}\" name=\"{1}\" displayName=\"{2}\" ref=\"{3}\" headerRowCount=\"1\">", tblId, name, ExcelAddressUtil.GetValidName(name), Address.Address); xml += string.Format("<autoFilter ref=\"{0}\" />", Address.Address); int cols = Address._toCol - Address._fromCol + 1; xml += string.Format("<tableColumns count=\"{0}\">", cols); var names = new Dictionary <string, string>(); for (int i = 1; i <= cols; i++) { var cell = WorkSheet.Cells[Address._fromRow, Address._fromCol + i - 1]; string colName; if (cell.Value == null || names.ContainsKey(cell.Value.ToString())) { //Get an unique name int a = i; do { colName = string.Format("Column{0}", a++); }while (names.ContainsKey(colName)); } else { colName = SecurityElement.Escape(cell.Value.ToString()); } names.Add(colName, colName); xml += string.Format("<tableColumn id=\"{0}\" name=\"{1}\" />", i, colName); } xml += "</tableColumns>"; xml += "<tableStyleInfo name=\"TableStyleMedium9\" showFirstColumn=\"0\" showLastColumn=\"0\" showRowStripes=\"1\" showColumnStripes=\"0\" /> "; xml += "</table>"; return(xml); }
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context) { ValidateArguments(arguments, 1); var r = arguments.ElementAt(0).ValueAsRangeInfo; if (r != null) { return(CreateResult(r.Address._toRow - r.Address._fromRow + 1, DataType.Integer)); } else { var range = ArgToAddress(arguments, 0, context); if (ExcelAddressUtil.IsValidAddress(range)) { var factory = new RangeAddressFactory(context.ExcelDataProvider); var address = factory.Create(range); return(CreateResult(address.ToRow - address.FromRow + 1, DataType.Integer)); } } throw new ArgumentException("Invalid range supplied"); }
/// <summary> /// Add a new named range /// </summary> /// <param name="Name">The name</param> /// <param name="Range">The range</param> /// <returns></returns> public ExcelNamedRange Add(string Name, ExcelRangeBase Range) { ExcelNamedRange item; if (!ExcelAddressUtil.IsValidName(Name)) { throw (new ArgumentException("Name contains invalid characters")); } if (Range.IsName) { item = new ExcelNamedRange(Name, _wb, _ws, _dic.Count); } else { item = new ExcelNamedRange(Name, _ws, Range.Worksheet, Range.Address, _dic.Count); } AddName(Name, item); return(item); }
internal ExcelTableSlicer(ExcelDrawings drawings, XmlNode node, ExcelTableColumn column) : base(drawings, node) { TableColumn = column; if (column.Slicer == null) { column.Slicer = this; } var name = drawings.Worksheet.Workbook.GetSlicerName(column.Name); CreateDrawing(name); SlicerName = name; Caption = column.Name; RowHeight = 19; CacheName = "Slicer_" + ExcelAddressUtil.GetValidName(name); var cache = new ExcelTableSlicerCache(NameSpaceManager); cache.Init(column, CacheName); _cache = cache; }
private void ValidateTableName(string Name) { if (string.IsNullOrEmpty(Name)) { throw new ArgumentException("Tablename is null or empty"); } char firstLetterOfName = Name[0]; if (Char.IsLetter(firstLetterOfName) == false && firstLetterOfName != '_' && firstLetterOfName != '\\') { throw new ArgumentException("Tablename start with invalid character"); } if (Name.Contains(" ")) { throw new ArgumentException("Tablename has spaces"); } if (!ExcelAddressUtil.IsValidName(Name)) { throw (new ArgumentException("Tablename is not valid")); } }
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context) { if (arguments == null || arguments.Count() == 0) { return(CreateResult(context.Scopes.Current.Address.FromRow, DataType.Integer)); } var r = arguments.ElementAt(0).ValueAsRangeInfo; if (r != null) { return(CreateResult(r.Address._fromRow, DataType.Integer)); } else { var rangeAddress = ArgToString(arguments, 0); if (ExcelAddressUtil.IsValidAddress(rangeAddress)) { var factory = new RangeAddressFactory(context.ExcelDataProvider); var address = factory.Create(rangeAddress); return(CreateResult(address.FromRow, DataType.Integer)); } } throw new ArgumentException("An invalid argument was supplied"); }
/// <summary> /// Executes the function with the specified <paramref name="arguments"/> in the specified <paramref name="context"/>. /// </summary> /// <param name="arguments">The arguments with which to evaluate the function.</param> /// <param name="context">The context in which to evaluate the function.</param> /// <returns>The <see cref="CompileResult"/>.</returns> public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context) { if (this.ArgumentsAreValid(arguments, 1, out eErrorType argumentError) == false) { return(new CompileResult(argumentError)); } var r = arguments.ElementAt(0).ValueAsRangeInfo; if (r != null) { return(CreateResult(r.Address._toCol - r.Address._fromCol + 1, DataType.Integer)); } else { var range = ArgToString(arguments, 0); if (ExcelAddressUtil.IsValidAddress(range)) { var factory = new RangeAddressFactory(context.ExcelDataProvider); var address = factory.Create(range); return(CreateResult(address.ToCol - address.FromCol + 1, DataType.Integer)); } } throw new ArgumentException("Invalid range supplied"); }