/****************************************************************************************/
#region Constructors
/// <summary>
/// Initialize the <see cref="ExcelConditionalFormattingRule"/>
/// </summary>
/// <param name="type"></param>
/// <param name="address"></param>
/// <param name="priority">Used also as the cfRule unique key</param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingRule(
eExcelConditionalFormattingRuleType type,
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
namespaceManager,
itemElementNode)
{
Require.Argument(address).IsNotNull("address");
// While MSDN states that 1 is the "highest priority," it also defines this
// field as W3C XML Schema int, which would allow values less than 1. Excel
// itself will, on occasion, use a value of 0, so this check will allow a 0.
Require.Argument(priority).IsInRange(0, int.MaxValue, "priority");
Require.Argument(worksheet).IsNotNull("worksheet");
_type = type;
_worksheet = worksheet;
SchemaNodeOrder = _worksheet.SchemaNodeOrder;
if (itemElementNode == null)
{
// Create/Get the <cfRule> inside <conditionalFormatting>
itemElementNode = CreateComplexNode(
_worksheet.WorksheetXml.DocumentElement,
string.Format(
"{0}[{1}='{2}']/{1}='{2}'/{3}[{4}='{5}']/{4}='{5}'",
//{0}
ExcelConditionalFormattingConstants.Paths.ConditionalFormatting,
// {1}
ExcelConditionalFormattingConstants.Paths.SqrefAttribute,
// {2}
address.AddressSpaceSeparated, //CF node don't what to have comma between multi addresses, use space instead.
// {3}
ExcelConditionalFormattingConstants.Paths.CfRule,
//{4}
ExcelConditionalFormattingConstants.Paths.PriorityAttribute,
//{5}
priority));
}
// Point to <cfRule>
TopNode = itemElementNode;
Address = address;
Priority = priority;
Type = type;
if (DxfId >= 0)
{
worksheet.Workbook.Styles.Dxfs[DxfId].AllowChange = true; //This Id is referenced by CF, so we can use it when we save.
_style = worksheet.Workbook.Styles.Dxfs[DxfId].Clone(); //Clone, so it can be altered without effecting other dxf styles
}
}
/// <summary>
///
/// </summary>
/// <param name="address"></param>
/// <param name="priority"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingThreeColorScale(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.ThreeColorScale,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode == null)
{
// Create the <colorScale> node inside the <cfRule> node
var colorScaleNode = CreateComplexNode(
Node,
ExcelConditionalFormattingConstants.Paths.ColorScale);
// LowValue default
LowValue = new ExcelConditionalFormattingColorScaleValue(
eExcelConditionalFormattingValueObjectPosition.Low,
eExcelConditionalFormattingValueObjectType.Min,
ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoLowValue),
eExcelConditionalFormattingRuleType.ThreeColorScale,
address,
priority,
worksheet,
NameSpaceManager);
// MiddleValue default
MiddleValue = new ExcelConditionalFormattingColorScaleValue(
eExcelConditionalFormattingValueObjectPosition.Middle,
eExcelConditionalFormattingValueObjectType.Percent,
ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoMiddleValue),
50,
string.Empty,
eExcelConditionalFormattingRuleType.ThreeColorScale,
address,
priority,
worksheet,
NameSpaceManager);
// HighValue default
HighValue = new ExcelConditionalFormattingColorScaleValue(
eExcelConditionalFormattingValueObjectPosition.High,
eExcelConditionalFormattingValueObjectType.Max,
ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoHighValue),
eExcelConditionalFormattingRuleType.ThreeColorScale,
address,
priority,
worksheet,
NameSpaceManager);
}
}
public RangeConditionalFormatting(
ExcelWorksheet worksheet,
ExcelAddress address)
{
Require.Argument(worksheet).IsNotNull("worksheet");
Require.Argument(address).IsNotNull("address");
_worksheet = worksheet;
_address = address;
}
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
internal ExcelConditionalFormattingUniqueValues(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode)
: this(address,
priority,
worksheet,
itemElementNode,
null)
{
}
/// <summary>
/// Initialize the <see cref="ExcelConditionalFormattingRule"/>
/// </summary>
/// <param name="type"></param>
/// <param name="address"></param>
/// <param name="priority">Used also as the cfRule unique key</param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingRule(
eExcelConditionalFormattingRuleType type,
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(namespaceManager,
itemElementNode)
{
Require.Argument(address).IsNotNull("address");
Require.Argument(priority).IsInRange(1, int.MaxValue, "priority");
Require.Argument(worksheet).IsNotNull("worksheet");
_type = type;
_worksheet = worksheet;
SchemaNodeOrder = _worksheet.SchemaNodeOrder;
if (itemElementNode == null)
{
// Create/Get the <cfRule> inside <conditionalFormatting>
itemElementNode = CreateComplexNode(
_worksheet.WorksheetXml.DocumentElement,
string.Format(
"{0}[{1}='{2}']/{1}='{2}'/{3}[{4}='{5}']/{4}='{5}'",
//{0}
ExcelConditionalFormattingConstants.Paths.ConditionalFormatting,
// {1}
ExcelConditionalFormattingConstants.Paths.SqrefAttribute,
// {2}
address.Address,
// {3}
ExcelConditionalFormattingConstants.Paths.CfRule,
//{4}
ExcelConditionalFormattingConstants.Paths.PriorityAttribute,
//{5}
priority));
}
// Point to <cfRule>
TopNode = itemElementNode;
Address = address;
Priority = priority;
Type = type;
if (DxfId >= 0)
{
worksheet.Workbook.Styles.Dxfs[DxfId].AllowChange = true; //This Id is referenced by CF, so we can use it when we save.
_style = worksheet.Workbook.Styles.Dxfs[DxfId].Clone(); //Clone, so it can be altered without effecting other dxf styles
}
}
/// <summary>
///
/// </summary>
/// <param name="type"></param>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingUniqueValues(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.UniqueValues,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
}
public LookupArguments(IEnumerable<FunctionArgument> arguments, ArgumentParsers argumentParsers, ParsingContext context)
{
_argumentParsers = argumentParsers;
SearchedValue = arguments.ElementAt(0).Value;
var arg1 = arguments.ElementAt(1).Value;
var dataArray = arg1 as IEnumerable<FunctionArgument>;
if (dataArray != null)
{
DataArray = dataArray;
ArgumentDataType = LookupArgumentDataType.DataArray;
}
else
{
//if (arg1 is ExcelDataProvider.INameInfo) arg1 = ((ExcelDataProvider.INameInfo) arg1).Value;
var rangeInfo = arg1 as ExcelDataProvider.IRangeInfo;
if (rangeInfo != null)
{
RangeAddress = string.IsNullOrEmpty(rangeInfo.Address.WorkSheet) ? rangeInfo.Address.Address : "'" + rangeInfo.Address.WorkSheet + "'!" + rangeInfo.Address.Address;
RangeInfo = rangeInfo;
ArgumentDataType = LookupArgumentDataType.ExcelRange;
}
else
{
RangeAddress = arg1.ToString();
ArgumentDataType = LookupArgumentDataType.ExcelRange;
}
}
var indexVal = arguments.ElementAt(2);
if (indexVal.DataType == DataType.ExcelAddress)
{
var address = new ExcelAddress(indexVal.Value.ToString());
var indexObj = context.ExcelDataProvider.GetRangeValue(address.WorkSheet, address._fromRow, address._fromCol);
LookupIndex = (int) _argumentParsers.GetParser(DataType.Integer).Parse(indexObj);
}
else
{
LookupIndex = (int)_argumentParsers.GetParser(DataType.Integer).Parse(arguments.ElementAt(2).Value);
}
if (arguments.Count() > 3)
{
RangeLookup = (bool)_argumentParsers.GetParser(DataType.Boolean).Parse(arguments.ElementAt(3).Value);
}
else
{
RangeLookup = true;
}
}
/// <summary>
///
/// </summary>
/// <param name="type"></param>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingExpression(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.Expression,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
Formula = string.Empty;
}
/// <summary>
///
/// </summary>
/// <param name="type"></param>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingTimePeriodGroup(
eExcelConditionalFormattingRuleType type,
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(type,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
}
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingAboveAverage(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.AboveAverage,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
AboveAverage = true;
EqualAverage = false;
}
/// <summary>
///
/// </summary>
/// <param name="type"></param>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingNotEqual(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.NotEqual,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
Operator = eExcelConditionalFormattingOperatorType.NotEqual;
Formula = string.Empty;
}
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingBelowStdDev(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.BelowStdDev,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
AboveAverage = false;
StdDev = 1;
}
/// <summary>
///
/// </summary>
/// <param name="type"></param>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingNotContainsBlanks(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.NotContainsBlanks,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
Formula = string.Format(
"LEN(TRIM({0}))>0",
Address.Start.Address);
}
public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
{
ValidateArguments(arguments, 1);
var address = ArgToString(arguments, 0);
var adr = new ExcelAddress(address);
var ws = adr.WorkSheet;
if (string.IsNullOrEmpty(ws))
{
ws = context.Scopes.Current.Address.Worksheet;
}
var result = context.ExcelDataProvider.GetRange(ws, adr._fromRow, adr._fromCol, address);
if (result.IsEmpty)
{
return CompileResult.Empty;
}
return new CompileResult(result, DataType.Enumerable);
}
public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
{
var functionArguments = arguments as FunctionArgument[] ?? arguments.ToArray();
ValidateArguments(functionArguments, 3);
var startRange = ArgToString(functionArguments, 0);
var rowOffset = ArgToInt(functionArguments, 1);
var colOffset = ArgToInt(functionArguments, 2);
int width = 0, height = 0;
if (functionArguments.Length > 3)
{
height = ArgToInt(functionArguments, 3);
ThrowExcelErrorValueExceptionIf(() => height == 0, eErrorType.Ref);
}
if (functionArguments.Length > 4)
{
width = ArgToInt(functionArguments, 4);
ThrowExcelErrorValueExceptionIf(() => width == 0, eErrorType.Ref);
}
var adr = new ExcelAddress(startRange);
var ws = adr.WorkSheet;
var fromRow = adr._fromRow + rowOffset;
var fromCol = adr._fromCol + colOffset;
var toRow = (height != 0 ? height : adr._toRow) + rowOffset;
var toCol = (width != 0 ? width : adr._toCol) + colOffset;
//var toRow = (height != 0 ? fromRow + height : adr._toRow + rowOffset);
//var toCol = (width != 0 ? fromCol + width : adr._toCol + colOffset);
var newRange = context.ExcelDataProvider.GetRange(ws, fromRow, fromCol, toRow, toCol);
if (!newRange.IsMulti)
{
if (newRange.IsEmpty) return CompileResult.Empty;
var val = newRange.GetValue(fromRow, fromCol);
if (IsNumeric(val))
{
return CreateResult(val, DataType.Decimal);
}
if (val is ExcelErrorValue)
{
return CreateResult(val, DataType.ExcelError);
}
return CreateResult(val, DataType.String);
}
return CreateResult(newRange, DataType.Enumerable);
}
/// <summary>
///
/// </summary>
/// <param name="type"></param>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingBottom(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.Bottom,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
Bottom = true;
Percent = false;
Rank = 10; // Last 10 values
}
/// <summary>
///
/// </summary>
/// <param name="type"></param>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingContainsErrors(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.ContainsErrors,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
Formula = string.Format(
"ISERROR({0})",
Address.Start.Address);
}
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingToday(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.Today,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
TimePeriod = eExcelConditionalFormattingTimePeriodType.Today;
Formula = string.Format(
"FLOOR({0},1)=TODAY()",
Address.Start.Address);
}
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingNextMonth(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.NextMonth,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
TimePeriod = eExcelConditionalFormattingTimePeriodType.NextMonth;
Formula = string.Format(
"AND(MONTH({0})=MONTH(EDATE(TODAY(),0+1)), YEAR({0})=YEAR(EDATE(TODAY(),0+1)))",
Address.Start.Address);
}
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingNextWeek(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.NextWeek,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
TimePeriod = eExcelConditionalFormattingTimePeriodType.NextWeek;
Formula = string.Format(
"AND(ROUNDDOWN({0},0)-TODAY()>(7-WEEKDAY(TODAY())),ROUNDDOWN({0},0)-TODAY()<(15-WEEKDAY(TODAY())))",
Address.Start.Address);
}
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingTwoColorScale(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(eExcelConditionalFormattingRuleType.TwoColorScale,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
// If the node is not null, parse values out of it instead of clobbering it.
if (itemElementNode == null)
{
// Create the <colorScale> node inside the <cfRule> node
var colorScaleNode = CreateComplexNode(
Node,
ExcelConditionalFormattingConstants.Paths.ColorScale);
// LowValue default
LowValue = new ExcelConditionalFormattingColorScaleValue(
eExcelConditionalFormattingValueObjectPosition.Low,
eExcelConditionalFormattingValueObjectType.Min,
ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoLowValue),
eExcelConditionalFormattingRuleType.TwoColorScale,
address,
priority,
worksheet,
NameSpaceManager);
// HighValue default
HighValue = new ExcelConditionalFormattingColorScaleValue(
eExcelConditionalFormattingValueObjectPosition.High,
eExcelConditionalFormattingValueObjectType.Max,
ColorTranslator.FromHtml(ExcelConditionalFormattingConstants.Colors.CfvoHighValue),
eExcelConditionalFormattingRuleType.TwoColorScale,
address,
priority,
worksheet,
NameSpaceManager);
}
}
/****************************************************************************************/
#region Constructors
/// <summary>
///
/// </summary>
/// <param name="address"></param>
/// <param name="priority"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingExpression(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
eExcelConditionalFormattingRuleType.Expression,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode==null) //Set default values and create attributes if needed
{
Formula = string.Empty;
}
}
/****************************************************************************************/
#region Constructors
/// <summary>
///
/// </summary>
/// <param name="address"></param>
/// <param name="priority"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingBelowOrEqualAverage(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
eExcelConditionalFormattingRuleType.BelowOrEqualAverage,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode==null) //Set default values and create attributes if needed
{
AboveAverage = false;
EqualAverage = true;
}
}
/****************************************************************************************/
#region Constructors
/// <summary>
///
/// </summary>
/// <param name="address"></param>
/// <param name="priority"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingBeginsWith(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
eExcelConditionalFormattingRuleType.BeginsWith,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode==null) //Set default values and create attributes if needed
{
Operator = eExcelConditionalFormattingOperatorType.BeginsWith;
Text = string.Empty;
}
}
/****************************************************************************************/
#region Constructors
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingTop(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
eExcelConditionalFormattingRuleType.Top,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode==null) //Set default values and create attributes if needed
{
Bottom = false;
Percent = false;
Rank = 10; // First 10 values
}
}
/****************************************************************************************/
#region Constructors
/// <summary>
///
/// </summary>
/// <param name="address"></param>
/// <param name="priority"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingContainsBlanks(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
eExcelConditionalFormattingRuleType.ContainsBlanks,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode==null) //Set default values and create attributes if needed
{
Formula = string.Format(
"LEN(TRIM({0}))=0",
Address.Start.Address);
}
}
/// <summary>
/// Evaluates the INDIRECT function with the specified <paramref name="arguments"/>
/// in the specified <paramref name="context"/>.
/// </summary>
/// <param name="arguments">The arguments to evaluate the function with.</param>
/// <param name="context">The context with which to evaluate the function in.</param>
/// <returns>A <see cref="CompileResult"/> containing the result of evaluation.</returns>
public override CompileResult Execute(IEnumerable <FunctionArgument> arguments, ParsingContext context)
{
if (this.ArgumentsAreValid(arguments, 1, out eErrorType argumentError) == false)
{
return(new CompileResult(argumentError));
}
var address = base.ArgToString(arguments, 0);
var adr = new ExcelAddress(address);
var ws = adr.WorkSheet;
if (string.IsNullOrEmpty(ws))
{
ws = context.Scopes.Current.Address.Worksheet;
}
var result = context.ExcelDataProvider.GetRange(ws, adr._fromRow, adr._fromCol, address);
if (result.IsEmpty)
{
return(CompileResult.Empty);
}
return(new CompileResult(result, DataType.Enumerable));
}
/****************************************************************************************/
#region Constructors
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingThisMonth(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
eExcelConditionalFormattingRuleType.ThisMonth,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode == null) //Set default values and create attributes if needed
{
TimePeriod = eExcelConditionalFormattingTimePeriodType.ThisMonth;
Formula = string.Format(
"AND(MONTH({0})=MONTH(TODAY()), YEAR({0})=YEAR(TODAY()))",
Address.Start.Address);
}
}
/****************************************************************************************/
#region Constructors
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingThisWeek(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
eExcelConditionalFormattingRuleType.ThisWeek,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode == null) //Set default values and create attributes if needed
{
TimePeriod = eExcelConditionalFormattingTimePeriodType.ThisWeek;
Formula = string.Format(
"AND(TODAY()-ROUNDDOWN({0},0)<=WEEKDAY(TODAY())-1,ROUNDDOWN({0},0)-TODAY()<=7-WEEKDAY(TODAY()))",
Address.Start.Address);
}
}
/****************************************************************************************/
#region Constructors
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingTomorrow(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
eExcelConditionalFormattingRuleType.Tomorrow,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode == null) //Set default values and create attributes if needed
{
TimePeriod = eExcelConditionalFormattingTimePeriodType.Tomorrow;
Formula = string.Format(
"FLOOR({0},1)=TODAY()+1",
Address.Start.Address);
}
}
public int FormatColumns(int row, int col, int numRows)
{
_ws.DefaultColWidth = 19.0 / 7; // 2
foreach (var writer in _writers)
{
if (writer.IsDecimal)
{
_ws.Column(col).Style.Numberformat.Format = "0.0";
}
if (writer.IsAutofit)
{
_ws.Column(col).AutoFit();
}
var column = new ExcelAddress(1, col, numRows, col);
writer.ApplyConditionalFormatting(_ws, column);
col++;
}
return(col);
}
internal void Generate_dLbls()
{
ExcelAddress s = new ExcelAddress(Range);
int count = s._toRow - s._fromRow + 1;
List <string> dLblList = new List <string>();
for (int i = 0; i < count; i++)
{
ExcelChartSerieFromCellsDataLabel label = new ExcelChartSerieFromCellsDataLabel(NameSpaceManager, TopNode, _scatterChartSerie, i, Guid.NewGuid().ToString().ToUpper());
}
TopNode.InnerXml += "<c:spPr><a:noFill/><a:ln><a:noFill/></a:ln><a:effectLst/></c:spPr><c:dLblPos val=\"ctr\"/><c:showLegendKey val=\"0\"/><c:showVal val=\"0\"/><c:showCatName val=\"0\"/><c:showSerName val=\"0\"/><c:showPercent val=\"0\"/><c:showBubbleSize val=\"0\"/><c:showLeaderLines val=\"0\"/>";
XmlElement extLst = TopNode.OwnerDocument.CreateElement("c:extLst", ExcelPackage.schemaChart);
TopNode.AppendChild(extLst);
extLst.InnerXml = "<c:ext uri=\"{CE6537A1-D6FC-4f65-9D91-7224C49458BB}\" xmlns:c15=\"http://schemas.microsoft.com/office/drawing/2012/chart\"><c15:layout/><c15:showDataLabelsRange val=\"1\"/><c15:showLeaderLines val=\"1\"/></c:ext>";
string test = "asdf";
}
/****************************************************************************************/
#region Constructors
/// <summary>
///
/// </summary>
/// <param name="address"></param>
/// <param name="priority"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingLastWeek(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
eExcelConditionalFormattingRuleType.LastWeek,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode==null) //Set default values and create attributes if needed
{
TimePeriod = eExcelConditionalFormattingTimePeriodType.LastWeek;
Formula = string.Format(
"AND(TODAY()-ROUNDDOWN({0},0)>=(WEEKDAY(TODAY())),TODAY()-ROUNDDOWN({0},0)<(WEEKDAY(TODAY())+7))",
Address.Start.Address);
}
}
/// <summary>
/// Gets merged range cell value.
/// </summary>
/// <param name="worksheet"></param>
/// <param name="row"></param>
/// <param name="column"></param>
/// <returns></returns>
public static string GetMegerValue(this ExcelWorksheet worksheet, int row, int column)
{
if (worksheet is null)
{
throw new ArgumentNullException(nameof(worksheet));
}
var rangeStr = worksheet.MergedCells[row, column];
var excelRange = worksheet.Cells;
var cellVal = excelRange[row, column].Value;
if (rangeStr is null)
{
return(cellVal?.ToString());
}
var startCell = new ExcelAddress(rangeStr).Start;
return(excelRange[startCell.Row, startCell.Column].Value?.ToString() ?? string.Empty);
}
/****************************************************************************************/
#region Constructors
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingLast7Days(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
eExcelConditionalFormattingRuleType.Last7Days,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode==null) //Set default values and create attributes if needed
{
TimePeriod = eExcelConditionalFormattingTimePeriodType.Last7Days;
Formula = string.Format(
"AND(TODAY()-FLOOR({0},1)<=6,FLOOR({0},1)<=TODAY())",
Address.Start.Address);
}
}
/****************************************************************************************/
#region Constructors
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingLastMonth(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
eExcelConditionalFormattingRuleType.LastMonth,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode==null) //Set default values and create attributes if needed
{
TimePeriod = eExcelConditionalFormattingTimePeriodType.LastMonth;
Formula = string.Format(
"AND(MONTH({0})=MONTH(EDATE(TODAY(),0-1)),YEAR({0})=YEAR(EDATE(TODAY(),0-1)))",
Address.Start.Address);
}
}
private static void WriteErrors(ExcelWorksheet sheet,
int rownum, int colnum,
List <string> errors,
string isBlankErrorMessage = null)
{
if (!errors.Any() && string.IsNullOrWhiteSpace(isBlankErrorMessage))
{
return;
}
StringBuilder sb = new StringBuilder();
if (errors.Any() && !string.IsNullOrWhiteSpace(isBlankErrorMessage))
{
errors.Add(isBlankErrorMessage);
}
else if (!string.IsNullOrWhiteSpace(isBlankErrorMessage))
{
ExcelAddress cellAddress = new ExcelAddress(rownum,
colnum,
rownum,
colnum);
var cf = sheet.ConditionalFormatting.AddContainsBlanks(cellAddress);
cf.Style.Fill.BackgroundColor.Color = Common.Colours.ErrorColour;
}
if (errors.Any())
{
foreach (string error in errors)
{
sb.AppendLine(error);
}
if (!string.IsNullOrEmpty(sb.ToString()))
{
SetComment(sheet, rownum, colnum, sb.ToString(), Colours.ErrorColour);
}
}
}
/// <summary>
/// Initialize the cfvo (§18.3.1.11) node
/// </summary>
/// <param name="ruleType"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode">The cfvo (§18.3.1.11) node parent. Can be any of the following:
/// colorScale (§18.3.1.16); dataBar (§18.3.1.28); iconSet (§18.3.1.49)</param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingIconDataBarValue(
eExcelConditionalFormattingRuleType ruleType,
ExcelAddress address,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
namespaceManager,
itemElementNode)
{
Require.Argument(address).IsNotNull("address");
Require.Argument(worksheet).IsNotNull("worksheet");
// Save the worksheet for private methods to use
_worksheet = worksheet;
// Schema order list
SchemaNodeOrder = new string[]
{
ExcelConditionalFormattingConstants.Nodes.Cfvo,
};
//Check if the parent does not exists
if (itemElementNode == null)
{
// Get the parent node path by the rule type
string parentNodePath = ExcelConditionalFormattingValueObjectType.GetParentPathByRuleType(
ruleType);
// Check for en error (rule type does not have <cfvo>)
if (parentNodePath == string.Empty)
{
throw new Exception(
ExcelConditionalFormattingConstants.Errors.MissingCfvoParentNode);
}
}
RuleType = ruleType;
}
/// <summary>
/// Validates the excel table against the generating type.
/// </summary>
/// <typeparam name="T">Generating class type</typeparam>
/// <param name="table">Extended object</param>
/// <returns>An enumerable of <see cref="ExcelTableConvertExceptionArgs"/> containing </returns>
public static IEnumerable <ExcelTableConvertExceptionArgs> Validate <T>(this ExcelTable table) where T : class, new()
{
IList mapping = PrepareMappings <T>(table);
var result = new LinkedList <ExcelTableConvertExceptionArgs>();
ExcelAddress bounds = table.GetDataBounds();
var item = (T)Activator.CreateInstance(typeof(T));
// Parse table
for (int row = bounds.Start.Row; row <= bounds.End.Row; row++)
{
foreach (KeyValuePair <int, PropertyInfo> map in mapping)
{
object cell = table.WorkSheet.Cells[row, map.Key + table.Address.Start.Column].Value;
PropertyInfo property = map.Value;
try
{
TrySetProperty(item, property, cell);
}
catch
{
result.AddLast(new ExcelTableConvertExceptionArgs
{
ColumnName = table.Columns[map.Key].Name,
ExpectedType = property.PropertyType,
PropertyName = property.Name,
CellValue = cell,
CellAddress = new ExcelCellAddress(row, map.Key + table.Address.Start.Column)
});
}
}
}
return(result);
}
/****************************************************************************************/
#region Conditional Formatting Rules
/// <summary>
/// Add rule (internal)
/// </summary>
/// <param name="type"></param>
/// <param name="address"></param>
/// <returns></returns>F
internal IExcelConditionalFormattingRule AddRule(
eExcelConditionalFormattingRuleType type,
ExcelAddress address)
{
Require.Argument(address).IsNotNull("address");
address = ValidateAddress(address);
EnsureRootElementExists();
// Create the Rule according to the correct type, address and priority
IExcelConditionalFormattingRule cfRule = ExcelConditionalFormattingRuleFactory.Create(
type,
address,
GetNextPriority(),
_worksheet,
null);
// Add the newly created rule to the list
_rules.Add(cfRule);
// Return the newly created rule
return(cfRule);
}
/// <summary>
/// Validates address - not empty, collisions
/// </summary>
/// <param name="address"></param>
/// <param name="validatingValidation"></param>
private void ValidateAddress(string address, IExcelDataValidation validatingValidation)
{
Require.Argument(address).IsNotNullOrEmpty("address");
// ensure that the new address does not collide with an existing validation.
var newAddress = new ExcelAddress(address);
if (_validations.Count > 0)
{
foreach (var validation in _validations)
{
if (validatingValidation != null && validatingValidation == validation)
{
continue;
}
var result = validation.Address.Collide(newAddress);
if (result != ExcelAddressBase.eAddressCollition.No)
{
throw new InvalidOperationException(string.Format("The address ({0}) collides with an existing validation ({1})", address, validation.Address.Address));
}
}
}
}
/// <summary>
/// Initialize the <see cref="ExcelConditionalFormattingColorScaleValue"/>
/// </summary>
/// <param name="position"></param>
/// <param name="type"></param>
/// <param name="color"></param>
/// <param name="ruleType"></param>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingColorScaleValue(
eExcelConditionalFormattingValueObjectPosition position,
eExcelConditionalFormattingValueObjectType type,
Color color,
eExcelConditionalFormattingRuleType ruleType,
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNamespaceManager namespaceManager)
: this(
position,
type,
color,
0,
null,
ruleType,
address,
priority,
worksheet,
null,
namespaceManager)
{
}
/// <summary>
/// Add a pivot table on the supplied range.
/// </summary>
/// <param name="range">The range address including header and total row</param>
/// <param name="source">The Source data range address</param>
/// <param name="name">The name of the table. Must be unique </param>
/// <returns>The pivot table object</returns>
public ExcelPivotTable Add(ExcelAddress range, ExcelRangeBase source, string name)
{
if (string.IsNullOrEmpty(name))
{
name = this.GetNewTableName();
}
if (range.WorkSheet != myWorksheet.Name)
{
throw (new Exception("The Range must be in the current worksheet"));
}
else if (myWorksheet.Workbook.ExistsTableName(name))
{
throw (new ArgumentException("Tablename is not unique"));
}
foreach (var t in myPivotTables)
{
if (t.Address.Collide(range) != ExcelAddress.eAddressCollition.No)
{
throw (new ArgumentException(string.Format("Table range collides with table {0}", t.Name)));
}
}
return(Add(new ExcelPivotTable(myWorksheet, range, source, name, myWorksheet.Workbook.NextPivotTableID++)));
}
/// <summary>
/// Extracts a DataTable from the ExcelWorksheet.
/// </summary>
/// <param name="worksheet">The ExcelWorksheet.</param>
/// <param name="hasHeaderRow">Indicates whether worksheet has a header row or not.</param>
/// <returns></returns>
public static DataTable ToDataTable(this ExcelWorksheet worksheet, bool hasHeaderRow = true)
{
ExcelAddress dataBounds = worksheet.GetDataBounds(hasHeaderRow);
IEnumerable <DataColumn> columns = worksheet.AsExcelTable(hasHeaderRow).Columns.Select(x => new DataColumn(!hasHeaderRow ? "Column" + x.Id : x.Name));
var dataTable = new DataTable(worksheet.Name);
dataTable.Columns.AddRange(columns.ToArray());
for (int rowIndex = dataBounds.Start.Row; rowIndex <= dataBounds.End.Row; ++rowIndex)
{
ExcelRangeBase[] inputRow = worksheet.Cells[rowIndex, dataBounds.Start.Column, rowIndex, dataBounds.End.Column].ToArray();
DataRow row = dataTable.Rows.Add();
for (var j = 0; j < inputRow.Length; ++j)
{
row[j] = inputRow[j].Value;
}
}
return(dataTable);
}
public static List <LinhaPlanilhaModel> LerArquivo(this ImportacaoController controller, DadosImportacaoVM model, ExcelWorksheet worksheet)
{
List <LinhaPlanilhaModel> linhasArquivoImportado = new List <LinhaPlanilhaModel>();
var colunas = System.Enum.GetValues(typeof(EnumColunasPlanilha)).Cast <int>().ToList();
for (var linha = LINHA_INICIO; linha <= worksheet.Dimension.Rows; linha++)
{
var celulas = new List <CelulaPlanilhaModel>();
colunas.ForEach(x =>
{
var celula = worksheet.Cells[linha, x].Value;
celulas.Add(new CelulaPlanilhaModel
{
Linha = linha,
Coluna = x,
ValorOriginal = celula,
Endereco = ExcelAddress.GetAddress(linha, x),
ValorManipulado = celula != null ? celula.ToString() : null
});
});
controller.ValidarCelulas(celulas);
var tipoErroLinhaPlanilha = controller.ValidarRegraCNPJouCPF(celulas);
linhasArquivoImportado.Add(new LinhaPlanilhaModel()
{
ExcelRow = worksheet.Row(linha),
Celulas = celulas,
Erro = tipoErroLinhaPlanilha
});
}
return(linhasArquivoImportado);
}
internal static void AdjustFormulasColumn(ExcelWorksheet ws, int columnFrom, int columns)
{
var delSF = new List <int>();
foreach (var sf in ws._sharedFormulas.Values)
{
var a = new ExcelAddress(sf.Address).DeleteColumn(columnFrom, columns);
if (a == null)
{
delSF.Add(sf.Index);
}
else
{
sf.Address = a.Address;
if (sf.StartCol > columnFrom)
{
var c = Math.Min(sf.StartCol - columnFrom, columns);
sf.Formula = ExcelCellBase.UpdateFormulaReferences(sf.Formula, 0, -c, 0, 1, ws.Name, ws.Name);
sf.StartCol -= c;
}
}
}
foreach (var ix in delSF)
{
ws._sharedFormulas.Remove(ix);
}
delSF = null;
var cse = new CellStoreEnumerator <object>(ws._formulas, 1, 1, ExcelPackage.MaxRows, ExcelPackage.MaxColumns);
while (cse.Next())
{
if (cse.Value is string)
{
cse.Value = ExcelCellBase.UpdateFormulaReferences(cse.Value.ToString(), 0, -columns, 0, columnFrom, ws.Name, ws.Name);
}
}
}
/// <summary>
/// Set the border style around the range.
/// </summary>
/// <param name="Style">The border style</param>
/// <param name="Color">The color of the border</param>
public void BorderAround(ExcelBorderStyle Style, System.Drawing.Color Color)
{
var addr = new ExcelAddress(_address);
if (addr.Addresses?.Count > 1)
{
foreach (var a in addr.Addresses)
{
SetBorderAroundStyle(Style, a);
if (!Color.IsEmpty)
{
SetBorderColor(Color, a);
}
}
}
else
{
SetBorderAroundStyle(Style, addr);
if (!Color.IsEmpty)
{
SetBorderColor(Color, addr);
}
}
}
private static Dictionary <int, ExcelAddress> GetCopiedMergedCells(ExcelRangeBase sourceRange, ExcelRangeBase Destination)
{
var toRow = sourceRange._toRow;
var toCol = sourceRange._toCol;
var fromRow = sourceRange._fromRow;
var fromCol = sourceRange._fromCol;
var worksheet = sourceRange._worksheet;
var copiedMergedCells = new Dictionary <int, ExcelAddress>();
//Merged cells
var csem = new CellStoreEnumerator <int>(worksheet.MergedCells._cells, fromRow, fromCol, toRow, toCol);
while (csem.Next())
{
if (!copiedMergedCells.ContainsKey(csem.Value))
{
var adr = new ExcelAddress(worksheet.Name, worksheet.MergedCells._list[csem.Value]);
var collideResult = sourceRange.Collide(adr);
if (collideResult == eAddressCollition.Inside || collideResult == eAddressCollition.Equal)
{
copiedMergedCells.Add(csem.Value, new ExcelAddress(
Destination._fromRow + (adr.Start.Row - fromRow),
Destination._fromCol + (adr.Start.Column - fromCol),
Destination._fromRow + (adr.End.Row - fromRow),
Destination._fromCol + (adr.End.Column - fromCol)));
}
else
{
//Partial merge of the address ignore.
copiedMergedCells.Add(csem.Value, null);
}
}
}
return(copiedMergedCells);
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="worksheet">worksheet that owns the validation</param>
/// <param name="itemElementNode">Xml top node (dataValidations) when importing xml</param>
/// <param name="validationType">Data validation type</param>
/// <param name="address">address for data validation</param>
/// <param name="namespaceManager">Xml Namespace manager</param>
internal ExcelDataValidation(ExcelWorksheet worksheet, string address, ExcelDataValidationType validationType, XmlNode itemElementNode, XmlNamespaceManager namespaceManager)
: base(namespaceManager != null ? namespaceManager : worksheet.NameSpaceManager)
{
Require.Argument(address).IsNotNullOrEmpty("address");
address = CheckAndFixRangeAddress(address);
if (itemElementNode == null)
{
//var xmlDoc = worksheet.WorksheetXml;
TopNode = worksheet.WorksheetXml.SelectSingleNode("//d:dataValidations", worksheet.NameSpaceManager);
// did not succeed using the XmlHelper methods here... so I'm creating the new node using XmlDocument...
var nsUri = NameSpaceManager.LookupNamespace("d");
//itemElementNode = TopNode.OwnerDocument.CreateElement(_itemElementNodeName, nsUri);
itemElementNode = TopNode.OwnerDocument.CreateElement(_itemElementNodeName.Split(':')[1], nsUri);
TopNode.AppendChild(itemElementNode);
}
TopNode = itemElementNode;
ValidationType = validationType;
Address = new ExcelAddress(address);
if (validationType.AllowOperator)
{
Operator = ExcelDataValidationOperator.any;
}
Init();
}
private static void DefineTablesForDataInAllWorksheets(ExcelPackage excel, CompositeLink link)
{
for (var i = 1; i <= excel.Workbook.Worksheets.Count; i++)
{
var ws = excel.Workbook.Worksheets[i];
if (ws.Dimension == null || !CanDefineTable(link, i))
{
continue;
}
var tableRange = new ExcelAddress(ws.Dimension.Address);
tableRange = new ExcelAddress(tableRange.Start.Row, tableRange.Start.Column,
tableRange.End.Row, tableRange.End.Column);
// Check if there is any row in the table. Otherwise, don't create one.
if (tableRange.Start.Row != tableRange.End.Row)
{
var table = ws.Tables.Add(tableRange, ws.Name);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium23;
}
}
}
public static void AutoMergeRowsHaveSameValue(this ExcelWorksheet sheet, ExcelAddress excelAddress)
{
ValidateExcelAddressWhenAutoMergeRow(sheet, excelAddress);
var columnIndex = excelAddress.Start.Column;
var currentIndex = excelAddress.Start.Row;
while (currentIndex <= excelAddress.End.Row)
{
var nextIndex = currentIndex + 1;
var currentValue = sheet.Cells[currentIndex, columnIndex].Value;
while (nextIndex <= excelAddress.End.Row && sheet.Cells[nextIndex, columnIndex].Value.Equals(currentValue))
{
nextIndex++;
}
if (nextIndex - 1 > currentIndex)
{
sheet.Cells[currentIndex, columnIndex, nextIndex - 1, columnIndex].Merge = true;
}
currentIndex = nextIndex;
}
}
protected override void PostCreate(ExcelWorksheet worksheet, ExcelAddress headerAddress, ExcelAddress bodyAddress)
{
base.PostCreate(worksheet, headerAddress, bodyAddress);
worksheet.Cells[headerAddress.Start.Row, headerAddress.Start.Column, Math.Min(100, bodyAddress.End.Row), bodyAddress.End.Column].AutoFitColumns();
}
/****************************************************************************************/
#region Private Properties
#endregion Private Properties
/****************************************************************************************/
#region Constructors
/// <summary>
///
/// </summary>
/// <param name="type"></param>
/// <param name="address"></param>
/// <param name="priority"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingIconSetBase(
eExcelConditionalFormattingRuleType type,
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
type,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
if (itemElementNode != null && itemElementNode.HasChildNodes)
{
int pos = 1;
foreach (XmlNode node in itemElementNode.SelectNodes("d:iconSet/d:cfvo", NameSpaceManager))
{
if (pos == 1)
{
Icon1 = new ExcelConditionalFormattingIconDataBarValue(
type,
address,
worksheet,
node,
namespaceManager);
}
else if (pos == 2)
{
Icon2 = new ExcelConditionalFormattingIconDataBarValue(
type,
address,
worksheet,
node,
namespaceManager);
}
else if (pos == 3)
{
Icon3 = new ExcelConditionalFormattingIconDataBarValue(
type,
address,
worksheet,
node,
namespaceManager);
}
else
{
break;
}
pos++;
}
}
else
{
var iconSetNode = CreateComplexNode(
Node,
ExcelConditionalFormattingConstants.Paths.IconSet);
//Create the <iconSet> node inside the <cfRule> node
double spann;
if (type == eExcelConditionalFormattingRuleType.ThreeIconSet)
{
spann = 3;
}
else if (type == eExcelConditionalFormattingRuleType.FourIconSet)
{
spann = 4;
}
else
{
spann = 5;
}
var iconNode1 = iconSetNode.OwnerDocument.CreateElement(ExcelConditionalFormattingConstants.Paths.Cfvo, ExcelPackage.schemaMain);
iconSetNode.AppendChild(iconNode1);
Icon1 = new ExcelConditionalFormattingIconDataBarValue(eExcelConditionalFormattingValueObjectType.Percent,
0,
"",
eExcelConditionalFormattingRuleType.ThreeIconSet,
address,
priority,
worksheet,
iconNode1,
namespaceManager);
var iconNode2 = iconSetNode.OwnerDocument.CreateElement(ExcelConditionalFormattingConstants.Paths.Cfvo, ExcelPackage.schemaMain);
iconSetNode.AppendChild(iconNode2);
Icon2 = new ExcelConditionalFormattingIconDataBarValue(eExcelConditionalFormattingValueObjectType.Percent,
Math.Round(100D / spann, 0),
"",
eExcelConditionalFormattingRuleType.ThreeIconSet,
address,
priority,
worksheet,
iconNode2,
namespaceManager);
var iconNode3 = iconSetNode.OwnerDocument.CreateElement(ExcelConditionalFormattingConstants.Paths.Cfvo, ExcelPackage.schemaMain);
iconSetNode.AppendChild(iconNode3);
Icon3 = new ExcelConditionalFormattingIconDataBarValue(eExcelConditionalFormattingValueObjectType.Percent,
Math.Round(100D * (2D / spann), 0),
"",
eExcelConditionalFormattingRuleType.ThreeIconSet,
address,
priority,
worksheet,
iconNode3,
namespaceManager);
Type = type;
}
}
public override bool Execute(ProgramOptions programOptions, JobConfiguration jobConfiguration)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
StepTiming stepTimingFunction = new StepTiming();
stepTimingFunction.JobFileName = programOptions.OutputJobFilePath;
stepTimingFunction.StepName = jobConfiguration.Status.ToString();
stepTimingFunction.StepID = (int)jobConfiguration.Status;
stepTimingFunction.StartTime = DateTime.Now;
stepTimingFunction.NumEntities = jobConfiguration.Target.Count;
this.DisplayJobStepStartingStatus(jobConfiguration);
FilePathMap = new FilePathMap(programOptions, jobConfiguration);
if (this.ShouldExecute(jobConfiguration) == false)
{
return(true);
}
if (jobConfiguration.Target.Count(t => t.Type == APPLICATION_TYPE_WEB) == 0)
{
return(true);
}
try
{
loggerConsole.Info("Prepare Detected WEB Entities Report File");
#region Prepare the report package
// Prepare package
ExcelPackage excelReport = new ExcelPackage();
excelReport.Workbook.Properties.Author = String.Format("AppDynamics DEXTER {0}", Assembly.GetEntryAssembly().GetName().Version);
excelReport.Workbook.Properties.Title = "AppDynamics DEXTER Detected WEB Entities Report";
excelReport.Workbook.Properties.Subject = programOptions.JobName;
excelReport.Workbook.Properties.Comments = String.Format("Targets={0}\nFrom={1:o}\nTo={2:o}", jobConfiguration.Target.Count, jobConfiguration.Input.TimeRange.From, jobConfiguration.Input.TimeRange.To);
#endregion
#region Parameters sheet
// Parameters sheet
ExcelWorksheet sheet = excelReport.Workbook.Worksheets.Add(SHEET_PARAMETERS);
var hyperLinkStyle = sheet.Workbook.Styles.CreateNamedStyle("HyperLinkStyle");
hyperLinkStyle.Style.Font.UnderLineType = ExcelUnderLineType.Single;
hyperLinkStyle.Style.Font.Color.SetColor(colorBlueForHyperlinks);
fillReportParametersSheet(sheet, jobConfiguration, "AppDynamics DEXTER Detected WEB Entities Report");
#endregion
#region TOC sheet
// Navigation sheet with link to other sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_TOC);
#endregion
#region Entity sheets and their associated pivots
// Entity sheets
sheet = excelReport.Workbook.Worksheets.Add(SHEET_CONTROLLERS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_ALL_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_APPLICATIONS_WEB_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_WEB_PAGES_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_WEB_PAGES_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_WEB_PAGES_TYPE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_WEB_PAGES_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 3, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_PAGE_RESOURCES_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_PAGE_RESOURCES_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_PAGE_RESOURCES_TYPE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_PAGE_RESOURCES_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_PAGE_BUSINESS_TRANSACTIONS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_PAGE_BUSINESS_TRANSACTIONS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_PAGE_BUSINESS_TRANSACTIONS_TYPE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_PAGE_BUSINESS_TRANSACTIONS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_GEO_LOCATIONS_LIST);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Pivot";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_GEO_LOCATIONS_TYPE_PIVOT);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(LIST_SHEET_START_TABLE_AT + 1, 1);
sheet = excelReport.Workbook.Worksheets.Add(SHEET_GEO_LOCATIONS_TYPE_PIVOT);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Cells[1, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_TOC);
sheet.Cells[1, 2].StyleName = "HyperLinkStyle";
sheet.Cells[2, 1].Value = "See Table";
sheet.Cells[2, 2].Formula = String.Format(@"=HYPERLINK(""#'{0}'!A1"", ""<Go>"")", SHEET_GEO_LOCATIONS_LIST);
sheet.Cells[2, 2].StyleName = "HyperLinkStyle";
sheet.View.FreezePanes(PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 2, 1);
#endregion
loggerConsole.Info("Fill Detected WEB Entities Report File");
#region Report file variables
ExcelRangeBase range = null;
ExcelTable table = null;
#endregion
#region Controllers
loggerConsole.Info("List of Controllers");
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerSummaryReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Applications - All
loggerConsole.Info("List of Applications - All");
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.ControllerApplicationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Applications
loggerConsole.Info("List of Applications");
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_WEB_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.WEBApplicationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Web Pages
loggerConsole.Info("List of Web Pages");
sheet = excelReport.Workbook.Worksheets[SHEET_WEB_PAGES_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.WEBPagesReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Web Page Resources
loggerConsole.Info("List of Web Page Resources");
sheet = excelReport.Workbook.Worksheets[SHEET_PAGE_RESOURCES_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.WEBPageResourcesReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Web Page Business Transactions
loggerConsole.Info("List of Web Page Business Transactions");
sheet = excelReport.Workbook.Worksheets[SHEET_PAGE_BUSINESS_TRANSACTIONS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.WEBPageBusinessTransactionsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
#region Geo Locations
loggerConsole.Info("List of Geo Locations");
sheet = excelReport.Workbook.Worksheets[SHEET_GEO_LOCATIONS_LIST];
EPPlusCSVHelper.ReadCSVFileIntoExcelRange(FilePathMap.WEBGeoLocationsReportFilePath(), 0, sheet, LIST_SHEET_START_TABLE_AT, 1);
#endregion
loggerConsole.Info("Finalize Detected WEB Entities Report File");
#region Controllers sheet
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_CONTROLLERS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_CONTROLLERS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 25;
sheet.Column(table.Columns["Version"].Position + 1).Width = 15;
}
#endregion
#region Applications - All
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_ALL_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_APPLICATIONS_ALL);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["Description"].Position + 1).Width = 15;
sheet.Column(table.Columns["CreatedBy"].Position + 1).Width = 15;
sheet.Column(table.Columns["UpdatedBy"].Position + 1).Width = 15;
sheet.Column(table.Columns["CreatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["UpdatedOn"].Position + 1).Width = 20;
sheet.Column(table.Columns["CreatedOnUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["UpdatedOnUtc"].Position + 1).Width = 20;
}
#endregion
#region Applications
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_APPLICATIONS_WEB_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_APPLICATIONS_WEB);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
ExcelAddress cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumPages"].Position + 1, sheet.Dimension.Rows, table.Columns["NumPages"].Position + 1);
var cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumAJAXRequests"].Position + 1, sheet.Dimension.Rows, table.Columns["NumAJAXRequests"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumVirtualPages"].Position + 1, sheet.Dimension.Rows, table.Columns["NumVirtualPages"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumIFrames"].Position + 1, sheet.Dimension.Rows, table.Columns["NumIFrames"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumActivity"].Position + 1, sheet.Dimension.Rows, table.Columns["NumActivity"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
cfAddressNum = new ExcelAddress(LIST_SHEET_START_TABLE_AT + 1, table.Columns["NumNoActivity"].Position + 1, sheet.Dimension.Rows, table.Columns["NumNoActivity"].Position + 1);
cfNum = sheet.ConditionalFormatting.AddDatabar(cfAddressNum, colorLightBlueForDatabars);
}
#endregion
#region Web Pages
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_WEB_PAGES_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_WEB_PAGES);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["PageType"].Position + 1).Width = 10;
sheet.Column(table.Columns["PageName"].Position + 1).Width = 20;
sheet.Column(table.Columns["FirstSegment"].Position + 1).Width = 20;
sheet.Column(table.Columns["From"].Position + 1).Width = 20;
sheet.Column(table.Columns["To"].Position + 1).Width = 20;
sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[SHEET_WEB_PAGES_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT + 1, 1], range, PIVOT_WEB_PAGES_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "HasActivity");
addFilterFieldToPivot(pivot, "ARTRange", eSortType.Ascending);
addFilterFieldToPivot(pivot, "NumNameSegments", eSortType.Ascending);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "PageType");
addRowFieldToPivot(pivot, "FirstSegment");
addRowFieldToPivot(pivot, "PageName");
addDataFieldToPivot(pivot, "PageID", DataFieldFunctions.Count, "NumPages");
addDataFieldToPivot(pivot, "ART", DataFieldFunctions.Average, "ART");
addDataFieldToPivot(pivot, "TimeTotal", DataFieldFunctions.Sum, "Time");
addDataFieldToPivot(pivot, "Calls", DataFieldFunctions.Sum, "Calls");
addDataFieldToPivot(pivot, "CPM", DataFieldFunctions.Average, "CPM");
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_WEB_PAGES_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
}
#endregion
#region Web Page Resources
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_PAGE_RESOURCES_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_PAGE_RESOURCES);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["PageType"].Position + 1).Width = 10;
sheet.Column(table.Columns["PageName"].Position + 1).Width = 20;
sheet.Column(table.Columns["ChildPageType"].Position + 1).Width = 10;
sheet.Column(table.Columns["ChildPageName"].Position + 1).Width = 20;
sheet.Column(table.Columns["From"].Position + 1).Width = 20;
sheet.Column(table.Columns["To"].Position + 1).Width = 20;
sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[SHEET_PAGE_RESOURCES_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_PAGE_RESOURCES_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "HasActivity");
addFilterFieldToPivot(pivot, "ARTRange", eSortType.Ascending);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "PageName");
addRowFieldToPivot(pivot, "ChildPageType");
addRowFieldToPivot(pivot, "ChildPageName");
addDataFieldToPivot(pivot, "ChildPageID", DataFieldFunctions.Count, "NumPages");
addDataFieldToPivot(pivot, "ART", DataFieldFunctions.Average, "ART");
addDataFieldToPivot(pivot, "Calls", DataFieldFunctions.Sum, "Calls");
addDataFieldToPivot(pivot, "CPM", DataFieldFunctions.Average, "CPM");
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_PAGE_RESOURCES_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
}
#endregion
#region Web Page Business Transactions
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_PAGE_BUSINESS_TRANSACTIONS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_PAGE_BUSINESS_TRANSACTIONS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["PageType"].Position + 1).Width = 10;
sheet.Column(table.Columns["PageName"].Position + 1).Width = 20;
sheet.Column(table.Columns["TierName"].Position + 1).Width = 20;
sheet.Column(table.Columns["BTName"].Position + 1).Width = 20;
sheet.Column(table.Columns["BTType"].Position + 1).Width = 15;
sheet.Column(table.Columns["From"].Position + 1).Width = 20;
sheet.Column(table.Columns["To"].Position + 1).Width = 20;
sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[SHEET_PAGE_BUSINESS_TRANSACTIONS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_PAGE_RESOURCES_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "HasActivity");
addFilterFieldToPivot(pivot, "ARTRange", eSortType.Ascending);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "PageName");
addRowFieldToPivot(pivot, "TierName");
addRowFieldToPivot(pivot, "BTName");
addDataFieldToPivot(pivot, "BTID", DataFieldFunctions.Count, "NumBTs");
addDataFieldToPivot(pivot, "ART", DataFieldFunctions.Average, "ART");
addDataFieldToPivot(pivot, "Calls", DataFieldFunctions.Sum, "Calls");
addDataFieldToPivot(pivot, "CPM", DataFieldFunctions.Average, "CPM");
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_PAGE_BUSINESS_TRANSACTIONS_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
}
#endregion
#region Geo Locations
// Make table
sheet = excelReport.Workbook.Worksheets[SHEET_GEO_LOCATIONS_LIST];
logger.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
loggerConsole.Info("{0} Sheet ({1} rows)", sheet.Name, sheet.Dimension.Rows);
if (sheet.Dimension.Rows > LIST_SHEET_START_TABLE_AT)
{
range = sheet.Cells[LIST_SHEET_START_TABLE_AT, 1, sheet.Dimension.Rows, sheet.Dimension.Columns];
table = sheet.Tables.Add(range, TABLE_GEO_LOCATIONS);
table.ShowHeader = true;
table.TableStyle = TableStyles.Medium2;
table.ShowFilter = true;
table.ShowTotal = false;
sheet.Column(table.Columns["Controller"].Position + 1).Width = 15;
sheet.Column(table.Columns["ApplicationName"].Position + 1).Width = 20;
sheet.Column(table.Columns["LocationName"].Position + 1).Width = 15;
sheet.Column(table.Columns["Country"].Position + 1).Width = 15;
sheet.Column(table.Columns["Region"].Position + 1).Width = 15;
sheet.Column(table.Columns["GeoCode"].Position + 1).Width = 15;
sheet.Column(table.Columns["From"].Position + 1).Width = 20;
sheet.Column(table.Columns["To"].Position + 1).Width = 20;
sheet.Column(table.Columns["FromUtc"].Position + 1).Width = 20;
sheet.Column(table.Columns["ToUtc"].Position + 1).Width = 20;
// Make pivot
sheet = excelReport.Workbook.Worksheets[SHEET_GEO_LOCATIONS_TYPE_PIVOT];
ExcelPivotTable pivot = sheet.PivotTables.Add(sheet.Cells[PIVOT_SHEET_START_PIVOT_AT + PIVOT_SHEET_CHART_HEIGHT, 1], range, PIVOT_PAGE_RESOURCES_TYPE);
setDefaultPivotTableSettings(pivot);
addFilterFieldToPivot(pivot, "HasActivity");
addFilterFieldToPivot(pivot, "ARTRange", eSortType.Ascending);
addRowFieldToPivot(pivot, "Controller");
addRowFieldToPivot(pivot, "ApplicationName");
addRowFieldToPivot(pivot, "LocationType");
addRowFieldToPivot(pivot, "Country");
addRowFieldToPivot(pivot, "Region");
addRowFieldToPivot(pivot, "LocationName");
addDataFieldToPivot(pivot, "ART", DataFieldFunctions.Average, "ART");
addDataFieldToPivot(pivot, "Calls", DataFieldFunctions.Sum, "Calls");
addDataFieldToPivot(pivot, "CPM", DataFieldFunctions.Average, "CPM");
ExcelChart chart = sheet.Drawings.AddChart(GRAPH_GEO_LOCATIONS_TYPE, eChartType.ColumnClustered, pivot);
chart.SetPosition(2, 0, 0, 0);
chart.SetSize(800, 300);
sheet.Column(1).Width = 20;
sheet.Column(2).Width = 20;
sheet.Column(3).Width = 20;
sheet.Column(4).Width = 20;
sheet.Column(5).Width = 20;
sheet.Column(6).Width = 20;
}
#endregion
#region TOC sheet
// TOC sheet again
sheet = excelReport.Workbook.Worksheets[SHEET_TOC];
fillTableOfContentsSheet(sheet, excelReport);
#endregion
#region Save file
if (Directory.Exists(FilePathMap.ReportFolderPath()) == false)
{
Directory.CreateDirectory(FilePathMap.ReportFolderPath());
}
string reportFilePath = FilePathMap.WEBEntitiesExcelReportFilePath(jobConfiguration.Input.TimeRange);
logger.Info("Saving Excel report {0}", reportFilePath);
loggerConsole.Info("Saving Excel report {0}", reportFilePath);
try
{
// Save full report Excel files
excelReport.SaveAs(new FileInfo(reportFilePath));
}
catch (InvalidOperationException ex)
{
logger.Warn("Unable to save Excel file {0}", reportFilePath);
logger.Warn(ex);
loggerConsole.Warn("Unable to save Excel file {0}", reportFilePath);
}
#endregion
return(true);
}
catch (Exception ex)
{
logger.Error(ex);
loggerConsole.Error(ex);
return(false);
}
finally
{
stopWatch.Stop();
this.DisplayJobStepEndedStatus(jobConfiguration, stopWatch);
stepTimingFunction.EndTime = DateTime.Now;
stepTimingFunction.Duration = stopWatch.Elapsed;
stepTimingFunction.DurationMS = stopWatch.ElapsedMilliseconds;
List <StepTiming> stepTimings = new List <StepTiming>(1);
stepTimings.Add(stepTimingFunction);
FileIOHelper.WriteListToCSVFile(stepTimings, new StepTimingReportMap(), FilePathMap.StepTimingReportFilePath(), true);
}
}
private static ExcelRangeBase PutCommandToWorksheet(ExcelWorksheet worksheet, ExcelAddress address, SubArray <string> command)
{
var text = string.Join(Environment.NewLine, command);
worksheet.SetValue(address.Address, text);
var cell = worksheet.Cells[address.Address];
cell.AutoFilter = false;
cell.AutoFitColumns();
return(cell);
}
/****************************************************************************************/
#region Constructors
/// <summary>
/// Initialize the cfvo (§18.3.1.11) node
/// </summary>
/// <param name="position"></param>
/// <param name="type"></param>
/// <param name="color"></param>
/// <param name="value"></param>
/// <param name="formula"></param>
/// <param name="ruleType"></param>
/// <param name="address"></param>
/// <param name="priority"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode">The cfvo (§18.3.1.11) node parent. Can be any of the following:
/// colorScale (§18.3.1.16); dataBar (§18.3.1.28); iconSet (§18.3.1.49)</param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingColorScaleValue(
eExcelConditionalFormattingValueObjectPosition position,
eExcelConditionalFormattingValueObjectType type,
Color color,
double value,
string formula,
eExcelConditionalFormattingRuleType ruleType,
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
namespaceManager,
itemElementNode)
{
Require.Argument(priority).IsInRange(1, int.MaxValue, "priority");
Require.Argument(address).IsNotNull("address");
Require.Argument(worksheet).IsNotNull("worksheet");
// Save the worksheet for private methods to use
_worksheet = worksheet;
// Schema order list
SchemaNodeOrder = new string[]
{
ExcelConditionalFormattingConstants.Nodes.Cfvo,
ExcelConditionalFormattingConstants.Nodes.Color
};
// Check if the parent does not exists
if (itemElementNode == null)
{
// Get the parent node path by the rule type
string parentNodePath = ExcelConditionalFormattingValueObjectType.GetParentPathByRuleType(
ruleType);
// Check for en error (rule type does not have <cfvo>)
if (parentNodePath == string.Empty)
{
throw new Exception(
ExcelConditionalFormattingConstants.Errors.MissingCfvoParentNode);
}
// Point to the <cfvo> parent node
itemElementNode = _worksheet.WorksheetXml.SelectSingleNode(
string.Format(
"//{0}[{1}='{2}']/{3}[{4}='{5}']/{6}",
// {0}
ExcelConditionalFormattingConstants.Paths.ConditionalFormatting,
// {1}
ExcelConditionalFormattingConstants.Paths.SqrefAttribute,
// {2}
address.Address,
// {3}
ExcelConditionalFormattingConstants.Paths.CfRule,
// {4}
ExcelConditionalFormattingConstants.Paths.PriorityAttribute,
// {5}
priority,
// {6}
parentNodePath),
_worksheet.NameSpaceManager);
// Check for en error (rule type does not have <cfvo>)
if (itemElementNode == null)
{
throw new Exception(
ExcelConditionalFormattingConstants.Errors.MissingCfvoParentNode);
}
}
// Point to the <cfvo> parent node (<colorScale>, <dataBar> or <iconSet>)
// This is different than normal, as TopNode does not point to the node itself but to
// its PARENT. Later, in the CreateNodeByOrdem method the TopNode will be updated.
TopNode = itemElementNode;
// Save the attributes
Position = position;
RuleType = ruleType;
Type = type;
Color = color;
Value = value;
Formula = formula;
}
/// <summary>
///
/// </summary>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
internal ExcelConditionalFormattingLastMonth(
ExcelAddress address,
int priority,
ExcelWorksheet worksheet)
: this(
address,
priority,
worksheet,
null,
null)
{
}
/****************************************************************************************/
#region Private Properties
#endregion Private Properties
/****************************************************************************************/
#region Constructors
/// <summary>
///
/// </summary>
/// <param name="type"></param>
/// <param name="priority"></param>
/// <param name="address"></param>
/// <param name="worksheet"></param>
/// <param name="itemElementNode"></param>
/// <param name="namespaceManager"></param>
internal ExcelConditionalFormattingDataBar(
eExcelConditionalFormattingRuleType type,
ExcelAddress address,
int priority,
ExcelWorksheet worksheet,
XmlNode itemElementNode,
XmlNamespaceManager namespaceManager)
: base(
type,
address,
priority,
worksheet,
itemElementNode,
(namespaceManager == null) ? worksheet.NameSpaceManager : namespaceManager)
{
SchemaNodeOrder = new string[] { "cfvo", "color" };
//Create the <dataBar> node inside the <cfRule> node
if (itemElementNode != null && itemElementNode.HasChildNodes)
{
bool high = false;
foreach (XmlNode node in itemElementNode.SelectNodes("d:dataBar/d:cfvo", NameSpaceManager))
{
if (high == false)
{
LowValue = new ExcelConditionalFormattingIconDataBarValue(
type,
address,
worksheet,
node,
namespaceManager);
high = true;
}
else
{
HighValue = new ExcelConditionalFormattingIconDataBarValue(
type,
address,
worksheet,
node,
namespaceManager);
}
}
}
else
{
var iconSetNode = CreateComplexNode(
Node,
ExcelConditionalFormattingConstants.Paths.DataBar);
var lowNode = iconSetNode.OwnerDocument.CreateElement(ExcelConditionalFormattingConstants.Paths.Cfvo, ExcelPackage.schemaMain);
iconSetNode.AppendChild(lowNode);
LowValue = new ExcelConditionalFormattingIconDataBarValue(eExcelConditionalFormattingValueObjectType.Min,
0,
"",
eExcelConditionalFormattingRuleType.DataBar,
address,
priority,
worksheet,
lowNode,
namespaceManager);
var highNode = iconSetNode.OwnerDocument.CreateElement(ExcelConditionalFormattingConstants.Paths.Cfvo, ExcelPackage.schemaMain);
iconSetNode.AppendChild(highNode);
HighValue = new ExcelConditionalFormattingIconDataBarValue(eExcelConditionalFormattingValueObjectType.Max,
0,
"",
eExcelConditionalFormattingRuleType.DataBar,
address,
priority,
worksheet,
highNode,
namespaceManager);
}
Type = type;
}
/// <summary>
/// Set the border style around the range.
/// </summary>
/// <param name="Style">The border style</param>
public void BorderAround(ExcelBorderStyle Style)
{
var addr = new ExcelAddress(_address);
SetBorderAroundStyle(Style, addr);
}
private static ExcelRangeBase PutTableToWorksheet1(ExcelWorksheet worksheet, ExcelAddress address, SubArray <string> table)
{
return(worksheet.Cells[address.Address].LoadFromDataTable(GetDataTable(table, tableName: address.Address), true, TableStyles.None));
}
