//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");
}
