public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
object[,] data = new object[, ] {
{ "Name", "City", "Birthday", "Eye color", "Weight", "Height" },
{ "Richard", "New York", new DateTime(1968, 6, 8), "Blue", 67, 165 },
{ "Nia", "New York", new DateTime(1972, 7, 3), "Brown", 62, 134 },
{ "Jared", "New York", new DateTime(1964, 3, 2), "Hazel", 72, 180 },
{ "Natalie", "Washington", new DateTime(1972, 8, 8), "Blue", 66, 163 },
{ "Damon", "Washington", new DateTime(1986, 2, 2), "Hazel", 76, 176 },
{ "Angela", "Washington", new DateTime(1993, 2, 15), "Brown", 68, 145 }
};
worksheet.Range["A1:F7"].Value = data;
//data bar rule.
IDataBar dataBar = worksheet.Range["E2:E7"].FormatConditions.AddDatabar();
dataBar.MinPoint.Type = ConditionValueTypes.LowestValue;
dataBar.MinPoint.Value = null;
dataBar.MaxPoint.Type = ConditionValueTypes.HighestValue;
dataBar.MaxPoint.Value = null;
dataBar.BarFillType = DataBarFillType.Gradient;
dataBar.BarColor.Color = Color.Green;
dataBar.Direction = DataBarDirection.Context;
dataBar.AxisColor.Color = Color.Red;
dataBar.AxisPosition = DataBarAxisPosition.Automatic;
dataBar.NegativeBarFormat.BorderColorType = DataBarNegativeColorType.Color;
dataBar.NegativeBarFormat.BorderColor.Color = Color.Blue;
dataBar.NegativeBarFormat.ColorType = DataBarNegativeColorType.Color;
dataBar.NegativeBarFormat.Color.Color = Color.Pink;
dataBar.ShowValue = false;
}
public double Execute(ISecurity sec, int barNum)
{
List <double> positionProfits = PreparePositionProfits();
int len = m_context.BarsCount;
for (int j = positionProfits.Count; j < len; j++)
{
positionProfits.Add(Constants.NaN);
}
{
int barsCount = m_context.BarsCount;
if (!m_context.IsLastBarUsed)
{
barsCount--;
}
if (barNum < barsCount - 1)
{
return(positionProfits[barNum]);
}
}
if (sec == null)
{
return(Constants.NaN);
}
double cache, pnl;
IDataBar bar = sec.Bars[barNum];
PositionsManager posMan = PositionsManager.GetManager(m_context);
double rawProfit = posMan.GetTotalProfit(sec, barNum, m_algo, bar.Close, out cache, out pnl);
if (m_context.IsFixedBarsCount)
{
// В этом режиме сдвигаю все значения влево
// Если мы уже набрали в буфер необходимое число баров
if (len <= positionProfits.Count)
{
for (int j = 0; j < positionProfits.Count - 1; j++)
{
positionProfits[j] = positionProfits[j + 1];
}
}
}
// Пересчитываю прибыль в привычные денежные единицы
rawProfit *= ScaleMultiplier;
positionProfits[barNum] = rawProfit; // заполняю индекс barNumber
m_profit.Value = rawProfit;
if (PrintProfitInLog)
{
m_context.Log(MsgId + ": " + m_profit.Value, MessageType.Info, PrintProfitInLog);
}
return(rawProfit);
}
public void Update(IList <double> data, IDataBar bar, bool isNewBar)
{
var ndt = GetData(bar);
if (isNewBar)
{
data.Add(ndt);
}
else if (data.Count > 0)
{
data[data.Count - 1] = ndt;
}
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet sheet = workbook.Worksheets[0];
//Conditional formatting on merge cell
sheet.Range["B2:C4"].Merge();
sheet.Range["B2:C4"].Value = 123;
var cf = (IFormatCondition)sheet.Range["B2:C4"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 0);
cf.Borders.ThemeColor = ThemeColor.Accent1;
cf.Borders.LineStyle = BorderLineStyle.Thin;
//Set cell values
int[] data = new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
sheet.Range["B10:B19"].Value = data;
sheet.Range["C10:C19"].Value = data;
sheet.Range["D10:D19"].Value = data;
//Set conditional formattings
//Color scale
IColorScale cf1 = sheet.Range["B10:B19"].FormatConditions.AddColorScale(ColorScaleType.ThreeColorScale);
cf1.ColorScaleCriteria[0].Type = ConditionValueTypes.LowestValue;
cf1.ColorScaleCriteria[0].FormatColor.Color = Color.FromRGB(248, 105, 107);
cf1.ColorScaleCriteria[1].Type = ConditionValueTypes.Percentile;
cf1.ColorScaleCriteria[1].Value = 50;
cf1.ColorScaleCriteria[1].FormatColor.Color = Color.FromRGB(255, 235, 132);
cf1.ColorScaleCriteria[2].Type = ConditionValueTypes.HighestValue;
cf1.ColorScaleCriteria[2].FormatColor.Color = Color.FromRGB(99, 190, 123);
//Data bar
sheet.Range["C14"].Value = -5;
sheet.Range["C17"].Value = -8;
IDataBar cf2 = sheet.Range["C10:C19"].FormatConditions.AddDatabar();
cf2.MinPoint.Type = ConditionValueTypes.AutomaticMin;
cf2.MaxPoint.Type = ConditionValueTypes.AutomaticMax;
cf2.BarFillType = DataBarFillType.Gradient;
cf2.BarColor.Color = Color.FromRGB(0, 138, 239);
cf2.BarBorder.Color.Color = Color.FromRGB(0, 138, 239);
cf2.NegativeBarFormat.Color.Color = Color.FromRGB(255, 0, 0);
cf2.NegativeBarFormat.BorderColorType = DataBarNegativeColorType.Color;
cf2.NegativeBarFormat.BorderColor.Color = Color.FromRGB(255, 0, 0);
cf2.AxisColor.Color = Color.Black;
cf2.AxisPosition = DataBarAxisPosition.Automatic;
//Icon set
IIconSetCondition cf3 = sheet.Range["D10:D19"].FormatConditions.AddIconSetCondition();
cf3.IconSet = workbook.IconSets[IconSetType.Icon3Symbols];
}
/// <summary>
/// Метод под флаг TemplateTypes.SECURITY, чтобы подключаться к источнику-БА
/// </summary>
public IList <double> Execute(ISecurity sec)
{
List <double> historySigmas = m_context.LoadObject(VariableId + "historySigmas") as List <double>;
if (historySigmas == null)
{
historySigmas = new List <double>();
m_context.StoreObject(VariableId + "historySigmas", historySigmas);
}
int len = sec.Bars.Count;
int barLengthInSeconds = (int)sec.IntervalInstance.ToSeconds();
LinkedList <KeyValuePair <DateTime, double> > logs = m_context.LoadObject(VariableId + "logs") as LinkedList <KeyValuePair <DateTime, double> >;
if (logs == null)
{
logs = new LinkedList <KeyValuePair <DateTime, double> >();
m_context.StoreObject(VariableId + "logs", logs);
}
// Типа, кеширование?
for (int j = historySigmas.Count; j < len; j++)
{
IDataBar bar = sec.Bars[j];
DateTime t = bar.Date;
double v = bar.Close;
double ln = Math.Log(v);
logs.AddLast(new KeyValuePair <DateTime, double>(t, ln));
double hv;
if (HV.TryEstimateHv(
logs, m_period, barLengthInSeconds, m_annualizingMultiplier,
m_useAllData, out hv))
{
double vol = hv;
historySigmas.Add(vol);
}
else
{
historySigmas.Add(Double.NaN);
}
}
return(new ReadOnlyCollection <double>(historySigmas));
}
private void OnExportClicked(object obj)
{
var grid = obj as SfDataGrid;
var options = new ExcelExportingOptions();
options.ExcelVersion = ExcelVersion.Excel2013;
var excelEngine = grid.ExportToExcel(grid.View, options);
var workBook = excelEngine.Excel.Workbooks[0];
//Apply conditional format to worksheet
IWorksheet worksheet = workBook.Worksheets[0];
IConditionalFormats formats = worksheet["F2:F11"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
format.FormatType = ExcelCFType.DataBar;
IDataBar dataBar = format.DataBar;
dataBar.BarColor = Color.Blue;
workBook.SaveAs("Sample.xlsx");
}
public ISecurity Execute(IList <double> openList, IList <double> closeList, IList <double> highList, IList <double> lowList, IList <double> volumeList)
{
if (openList == null)
{
throw new ArgumentNullException(nameof(openList));
}
if (closeList == null)
{
throw new ArgumentNullException(nameof(closeList));
}
if (highList == null)
{
throw new ArgumentNullException(nameof(highList));
}
if (lowList == null)
{
throw new ArgumentNullException(nameof(lowList));
}
if (volumeList == null)
{
throw new ArgumentNullException(nameof(volumeList));
}
var security = Context.Runtime.Securities.First();
var securityBars = security.Bars;
var count = Math.Min(openList.Count, Math.Min(closeList.Count, Math.Min(highList.Count, Math.Min(lowList.Count, Math.Min(volumeList.Count, securityBars.Count)))));
var bars = new IDataBar[count];
for (var i = 0; i < count; i++)
{
bars[i] = new DataBar(securityBars[i].Date, openList[i], highList[i], lowList[i], closeList[i], volumeList[i]);
}
return(new Security(bars, VariableName, security));
}
protected override double GetData(IDataBar bar)
{
return(bar is IBar bar1 ? bar1.TheoreticalPrice : 0);
}
protected override double GetData(IDataBar bar)
{
return(bar is IBar bar1 ? bar1.Volatility : 1);
}
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
{
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "ConditionalFormattings";
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
storageFile = await local.CreateFileAsync("ConditionalFormattings.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile == null)
{
return;
}
//Instantiate excel Engine
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
Assembly assembly = typeof(CondFormat).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.CFTemplate.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook myWorkbook = await excelEngine.Excel.Workbooks.OpenAsync(fileStream);
IWorksheet sheet = myWorkbook.Worksheets[0];
#region Databar
//Add condition for the range
IConditionalFormats formats = sheet.Range["C7:C46"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Data bar and icon set for the same cell
//Set the format type
format.FormatType = ExcelCFType.DataBar;
IDataBar dataBar = format.DataBar;
//Set the constraint
dataBar.MinPoint.Type = ConditionValueType.LowestValue;
dataBar.MinPoint.Value = "0";
dataBar.MaxPoint.Type = ConditionValueType.HighestValue;
dataBar.MaxPoint.Value = "0";
//Set color for Bar
dataBar.BarColor = Color.FromArgb(255, 156, 208, 243);
//Hide the value in data bar
dataBar.ShowValue = false;
#endregion
#region Iconset
//Add another condition in the same range
format = formats.AddCondition();
//Set Icon format type
format.FormatType = ExcelCFType.IconSet;
IIconSet iconSet = format.IconSet;
iconSet.IconSet = ExcelIconSetType.FourRating;
iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
iconSet.IconCriteria[0].Value = "0";
iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
iconSet.IconCriteria[1].Value = "0";
iconSet.ShowIconOnly = true;
//Sets Icon sets for another range
formats = sheet.Range["E7:E46"].ConditionalFormats;
format = formats.AddCondition();
format.FormatType = ExcelCFType.IconSet;
iconSet = format.IconSet;
iconSet.IconSet = ExcelIconSetType.ThreeSymbols;
iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
iconSet.IconCriteria[0].Value = "0";
iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
iconSet.IconCriteria[1].Value = "0";
iconSet.ShowIconOnly = true;
#endregion
#region Duplicate
formats = sheet.Range["D7:D46"].ConditionalFormats;
format = formats.AddCondition();
format.FormatType = ExcelCFType.Duplicate;
format.BackColorRGB = Color.FromArgb(255, 255, 199, 206);
#endregion
#region TopBottom and AboveBelowAverage
sheet = myWorkbook.Worksheets[1];
formats = sheet.Range["N6:N35"].ConditionalFormats;
format = formats.AddCondition();
format.FormatType = ExcelCFType.TopBottom;
format.TopBottom.Type = ExcelCFTopBottomType.Bottom;
format.BackColorRGB = Color.FromArgb(255, 51, 153, 102);
formats = sheet.Range["M6:M35"].ConditionalFormats;
format = formats.AddCondition();
format.FormatType = ExcelCFType.AboveBelowAverage;
format.AboveBelowAverage.AverageType = ExcelCFAverageType.Below;
format.FontColorRGB = Color.FromArgb(255, 255, 255, 255);
format.BackColorRGB = Color.FromArgb(255, 166, 59, 38);
#endregion
await myWorkbook.SaveAsAsync(storageFile);
//Close the workbook.
myWorkbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
protected abstract double GetValue(IDataBar bar, IReadOnlyList <double> currentResult);
protected override double GetData(IDataBar bar)
{
return(bar.Interest);
}
private Workbook GetBidTracker()
{
Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
//**********************Set RowHeight & ColumnWidth**************
worksheet.StandardHeight = 30;
worksheet.Range["1:1"].RowHeight = 57.75;
worksheet.Range["2:9"].RowHeight = 30;
worksheet.Range["A:A"].ColumnWidth = 2.71;
worksheet.Range["B:B"].ColumnWidth = 11.71;
worksheet.Range["C:C"].ColumnWidth = 28;
worksheet.Range["D:D"].ColumnWidth = 22.425;
worksheet.Range["E:E"].ColumnWidth = 16.71;
worksheet.Range["F:F"].ColumnWidth = 28;
worksheet.Range["G:H"].ColumnWidth = 16.71;
worksheet.Range["I:I"].ColumnWidth = 2.71;
//*************************Set Table Value & Formulas********************
ITable table = worksheet.Tables.Add(worksheet.Range["B2:H9"], true);
worksheet.Range["B2:H9"].Value = new object[, ]
{
{ "BID #", "DESCRIPTION", "DATE RECEIVED", "AMOUNT", "PERCENT COMPLETE", "DEADLINE", "DAYS LEFT" },
{ 1, "Bid number 1", null, 2000, 0.5, null, null },
{ 2, "Bid number 2", null, 3500, 0.25, null, null },
{ 3, "Bid number 3", null, 5000, 0.3, null, null },
{ 4, "Bid number 4", null, 4000, 0.2, null, null },
{ 5, "Bid number 5", null, 4000, 0.75, null, null },
{ 6, "Bid number 6", null, 1500, 0.45, null, null },
{ 7, "Bid number 7", null, 5000, 0.65, null, null },
};
worksheet.Range["B1"].Value = "Bid Details";
worksheet.Range["D3"].Formula = "=TODAY()-10";
worksheet.Range["D4:D5"].Formula = "=TODAY()-20";
worksheet.Range["D6"].Formula = "=TODAY()-10";
worksheet.Range["D7"].Formula = "=TODAY()-28";
worksheet.Range["D8"].Formula = "=TODAY()-17";
worksheet.Range["D9"].Formula = "=TODAY()-15";
worksheet.Range["G3:G9"].Formula = "=[@[DATE RECEIVED]]+30";
worksheet.Range["H3:H9"].Formula = "=[@DEADLINE]-TODAY()";
//***************************Set Table Style*******************************
ITableStyle tableStyle = workbook.TableStyles.Add("Bid Tracker");
workbook.DefaultTableStyle = "Bid Tracker";
var wholeTableStyle = tableStyle.TableStyleElements[TableStyleElementType.WholeTable];
//Set WholeTable element style.
wholeTableStyle.Font.Color = Color.FromArgb(89, 89, 89);
wholeTableStyle.Borders.Color = Color.FromArgb(89, 89, 89);
wholeTableStyle.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
var headerRowStyle = tableStyle.TableStyleElements[TableStyleElementType.HeaderRow];
//Set HeaderRow element style.
headerRowStyle.Borders.Color = Color.FromArgb(89, 89, 89);
headerRowStyle.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
headerRowStyle.Interior.Color = Color.FromArgb(131, 95, 1);
headerRowStyle.Interior.PatternColor = Color.FromArgb(254, 184, 10);
var totalRowStyle = tableStyle.TableStyleElements[TableStyleElementType.TotalRow];
//Set TotalRow element style.
totalRowStyle.Borders.Color = Color.White;
totalRowStyle.Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.InsideVertical].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
totalRowStyle.Interior.Color = Color.FromArgb(131, 95, 1);
//**********************************Set Named Styles****************************
IStyle titleStyle = workbook.Styles["Title"];
titleStyle.Font.Name = "Calibri";
titleStyle.Font.Size = 36;
titleStyle.Font.Color = Color.FromArgb(56, 145, 167);
titleStyle.IncludeAlignment = true;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
IStyle heading1Style = workbook.Styles["Heading 1"];
heading1Style.IncludeAlignment = true;
heading1Style.HorizontalAlignment = HorizontalAlignment.Left;
heading1Style.IndentLevel = 1;
heading1Style.VerticalAlignment = VerticalAlignment.Bottom;
heading1Style.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.None;
heading1Style.Font.Size = 14;
heading1Style.Font.Color = Color.White;
heading1Style.Font.Bold = false;
heading1Style.IncludePatterns = true;
heading1Style.Interior.Color = Color.White;
IStyle dateStyle = workbook.Styles.Add("Date");
dateStyle.IncludeNumber = true;
dateStyle.NumberFormat = "m/d/yyyy";
dateStyle.IncludeAlignment = true;
dateStyle.HorizontalAlignment = HorizontalAlignment.Left;
dateStyle.IndentLevel = 1;
dateStyle.VerticalAlignment = VerticalAlignment.Center;
dateStyle.IncludeFont = false;
dateStyle.IncludeBorder = false;
dateStyle.IncludePatterns = false;
IStyle commaStyle = workbook.Styles["Comma"];
commaStyle.IncludeNumber = true;
commaStyle.NumberFormat = "#,##0_);(#,##0)";
commaStyle.IncludeAlignment = true;
commaStyle.HorizontalAlignment = HorizontalAlignment.Left;
commaStyle.IndentLevel = 1;
commaStyle.VerticalAlignment = VerticalAlignment.Center;
IStyle normalStyle = workbook.Styles["Normal"];
normalStyle.HorizontalAlignment = HorizontalAlignment.Left;
normalStyle.IndentLevel = 1;
normalStyle.VerticalAlignment = VerticalAlignment.Center;
normalStyle.WrapText = true;
normalStyle.Font.Color = Color.FromArgb(89, 89, 89);
IStyle currencyStyle = workbook.Styles["Currency"];
currencyStyle.NumberFormat = "$#,##0.00";
currencyStyle.IncludeAlignment = true;
currencyStyle.HorizontalAlignment = HorizontalAlignment.Right;
currencyStyle.IndentLevel = 1;
currencyStyle.VerticalAlignment = VerticalAlignment.Center;
IStyle percentStyle = workbook.Styles["Percent"];
percentStyle.IncludeAlignment = true;
percentStyle.HorizontalAlignment = HorizontalAlignment.Right;
percentStyle.VerticalAlignment = VerticalAlignment.Center;
percentStyle.IncludeFont = true;
percentStyle.Font.Name = "Calibri";
percentStyle.Font.Size = 20;
percentStyle.Font.Bold = true;
percentStyle.Font.Color = Color.FromArgb(89, 89, 89);
IStyle comma0Style = workbook.Styles["Comma [0]"];
comma0Style.NumberFormat = "#,##0_);(#,##0)";
comma0Style.IncludeAlignment = true;
comma0Style.HorizontalAlignment = HorizontalAlignment.Right;
comma0Style.IndentLevel = 3;
comma0Style.VerticalAlignment = VerticalAlignment.Center;
//***********************************Add Conditional Formatting***************
IDataBar dataBar = worksheet.Range["F3:F9"].FormatConditions.AddDatabar();
dataBar.MinPoint.Type = ConditionValueTypes.Number;
dataBar.MinPoint.Value = 1;
dataBar.MaxPoint.Type = ConditionValueTypes.Number;
dataBar.MaxPoint.Value = 0;
dataBar.BarFillType = DataBarFillType.Gradient;
dataBar.BarColor.Color = Color.FromArgb(126, 194, 211);
dataBar.Direction = DataBarDirection.Context;
dataBar.AxisColor.Color = Color.Black;
dataBar.AxisPosition = DataBarAxisPosition.Automatic;
dataBar.NegativeBarFormat.ColorType = DataBarNegativeColorType.Color;
dataBar.NegativeBarFormat.Color.Color = Color.Red;
dataBar.ShowValue = true;
//***************************************Use NamedStyle*************************
worksheet.SheetView.DisplayGridlines = false;
table.TableStyle = tableStyle;
worksheet.Range["B1"].Style = titleStyle;
worksheet.Range["B1"].WrapText = false;
worksheet.Range["B2:H2"].Style = heading1Style;
worksheet.Range["B3:B9"].Style = commaStyle;
worksheet.Range["C3:C9"].Style = normalStyle;
worksheet.Range["D3:D9"].Style = dateStyle;
worksheet.Range["E3:E9"].Style = currencyStyle;
worksheet.Range["F3:F9"].Style = percentStyle;
worksheet.Range["G3:G9"].Style = dateStyle;
worksheet.Range["H3:H9"].Style = comma0Style;
return(workbook);
}
/// <summary>
/// Apply the conditonal format using workbook
/// </summary>
/// <param name="worksheet">worksheet used to get the range and set the conditional formats</param>
private void ApplyConditionFormatting(IWorksheet worksheet)
{
IConditionalFormats statusCondition = worksheet["H2:AL31"].ConditionalFormats;
IConditionalFormat leaveCondition = statusCondition.AddCondition();
leaveCondition.FormatType = ExcelCFType.CellValue;
leaveCondition.Operator = ExcelComparisonOperator.Equal;
leaveCondition.FirstFormula = "\"L\"";
leaveCondition.BackColorRGB = Color.FromArgb(253, 167, 92);
IConditionalFormat absentCondition = statusCondition.AddCondition();
absentCondition.FormatType = ExcelCFType.CellValue;
absentCondition.Operator = ExcelComparisonOperator.Equal;
absentCondition.FirstFormula = "\"A\"";
absentCondition.BackColorRGB = Color.FromArgb(255, 105, 124);
IConditionalFormat presentCondition = statusCondition.AddCondition();
presentCondition.FormatType = ExcelCFType.CellValue;
presentCondition.Operator = ExcelComparisonOperator.Equal;
presentCondition.FirstFormula = "\"P\"";
presentCondition.BackColorRGB = Color.FromArgb(67, 233, 123);
IConditionalFormat weekendCondition = statusCondition.AddCondition();
weekendCondition.FormatType = ExcelCFType.CellValue;
weekendCondition.Operator = ExcelComparisonOperator.Equal;
weekendCondition.FirstFormula = "\"WE\"";
weekendCondition.BackColorRGB = Color.FromArgb(240, 240, 240);
IConditionalFormats presentSummaryCF = worksheet["C2:C31"].ConditionalFormats;
IConditionalFormat presentCountCF = presentSummaryCF.AddCondition();
presentCountCF.FormatType = ExcelCFType.DataBar;
IDataBar dataBar = presentCountCF.DataBar;
dataBar.BarColor = Color.FromArgb(61, 242, 142);
IConditionalFormats leaveSummaryCF = worksheet["D2:D31"].ConditionalFormats;
IConditionalFormat leaveCountCF = leaveSummaryCF.AddCondition();
leaveCountCF.FormatType = ExcelCFType.DataBar;
dataBar = leaveCountCF.DataBar;
dataBar.BarColor = Color.FromArgb(242, 71, 23);
IConditionalFormats absentSummaryCF = worksheet["E2:E31"].ConditionalFormats;
IConditionalFormat absentCountCF = absentSummaryCF.AddCondition();
absentCountCF.FormatType = ExcelCFType.DataBar;
dataBar = absentCountCF.DataBar;
dataBar.BarColor = Color.FromArgb(255, 10, 69);
IConditionalFormats unplannedSummaryCF = worksheet["F2:F31"].ConditionalFormats;
IConditionalFormat unplannedCountCF = unplannedSummaryCF.AddCondition();
unplannedCountCF.FormatType = ExcelCFType.DataBar;
dataBar = unplannedCountCF.DataBar;
dataBar.MaxPoint.Type = ConditionValueType.HighestValue;
dataBar.BarColor = Color.FromArgb(142, 142, 142);
IConditionalFormats plannedSummaryCF = worksheet["G2:G31"].ConditionalFormats;
IConditionalFormat plannedCountCF = plannedSummaryCF.AddCondition();
plannedCountCF.FormatType = ExcelCFType.DataBar;
dataBar = plannedCountCF.DataBar;
dataBar.MaxPoint.Type = ConditionValueType.HighestValue;
dataBar.BarColor = Color.FromArgb(56, 136, 254);
}
private void btnCreate_Click(object sender, System.EventArgs e)
{
#region Initialize Workbook
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2007;
//Get the path of the input file
if (rdImagewtSize.Checked)
{
fileName = "TemplateMarkerImageWithSize.xlsx";
}
else if (rdImageOnly.Checked)
{
fileName = "TemplateMarkerImageOnly.xlsx";
}
else if (rdImagewtPosition.Checked)
{
fileName = "TemplateMarkerImageWithPosition.xlsx";
}
else if (rdImagewtSizeAndPosition.Checked)
{
fileName = "TemplateMarkerImageWithSize&Position.xlsx";
}
else if (rdImageFitToCell.Checked)
{
fileName = "TemplateMarkerImageFitToCell.xlsx";
}
inputPath = GetFullTemplatePath(fileName);
//Open an existing spreadsheet which will be used as a template for generating the new spreadsheet.
//After opening, the workbook object represents the complete in-memory object model of the template spreadsheet.
IWorkbook workbook = excelEngine.Excel.Workbooks.Open(inputPath);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet1 = workbook.Worksheets[0];
IWorksheet worksheet2 = workbook.Worksheets[1];
#endregion
#region Create Template Marker
//Create Template Marker Processor
ITemplateMarkersProcessor marker = workbook.CreateTemplateMarkersProcessor();
IConditionalFormats conditionalFormats = marker.CreateConditionalFormats(worksheet1["C5"]);
#region Data Bar
//Apply markers using Formula
IConditionalFormat condition = conditionalFormats.AddCondition();
//Set Data bar and icon set for the same cell
//Set the format type
condition.FormatType = ExcelCFType.DataBar;
IDataBar dataBar = condition.DataBar;
//Set the constraint
dataBar.MinPoint.Type = ConditionValueType.LowestValue;
dataBar.MinPoint.Value = "0";
dataBar.MaxPoint.Type = ConditionValueType.HighestValue;
dataBar.MaxPoint.Value = "0";
//Set color for Bar
dataBar.BarColor = Color.FromArgb(156, 208, 243);
//Hide the value in data bar
dataBar.ShowValue = false;
#endregion
#region IconSet
condition = conditionalFormats.AddCondition();
condition.FormatType = ExcelCFType.IconSet;
IIconSet iconSet = condition.IconSet;
iconSet.IconSet = ExcelIconSetType.FourRating;
iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
iconSet.IconCriteria[0].Value = "0";
iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
iconSet.IconCriteria[1].Value = "0";
iconSet.ShowIconOnly = true;
#endregion
conditionalFormats = marker.CreateConditionalFormats(worksheet1["D5"]);
#region Color Scale
condition = conditionalFormats.AddCondition();
condition.FormatType = ExcelCFType.ColorScale;
IColorScale colorScale = condition.ColorScale;
//Sets 3 - color scale.
colorScale.SetConditionCount(3);
colorScale.Criteria[0].FormatColorRGB = Color.FromArgb(230, 197, 218);
colorScale.Criteria[0].Type = ConditionValueType.LowestValue;
colorScale.Criteria[0].Value = "0";
colorScale.Criteria[1].FormatColorRGB = Color.FromArgb(244, 210, 178);
colorScale.Criteria[1].Type = ConditionValueType.Percentile;
colorScale.Criteria[1].Value = "50";
colorScale.Criteria[2].FormatColorRGB = Color.FromArgb(245, 247, 171);
colorScale.Criteria[2].Type = ConditionValueType.HighestValue;
colorScale.Criteria[2].Value = "0";
#endregion
conditionalFormats = marker.CreateConditionalFormats(worksheet1["E5"]);
#region Iconset
condition = conditionalFormats.AddCondition();
condition.FormatType = ExcelCFType.IconSet;
iconSet = condition.IconSet;
iconSet.IconSet = ExcelIconSetType.ThreeSymbols;
iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
iconSet.IconCriteria[0].Value = "0";
iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
iconSet.IconCriteria[1].Value = "0";
iconSet.ShowIconOnly = false;
#endregion
//Northwind customers table
if (rdbDataTable.Checked)
{
worksheet1["A5"].Value = worksheet1["A5"].Value.Replace("Customers.Hyperlink.", "Customers.");
marker.AddVariable("Customers", northwindDt);
}
else
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
if (this._customers.Count == 0)
{
this._customers = GetCustomerAsObjects();
}
marker.AddVariable("Customers", _customers);
}
//Stretch Formula. This shows the data getting replaced in the marker specified in another worksheet.
marker.AddVariable("NumbersTable", numbersDt);
//Process the markers in the template.
marker.ApplyMarkers();
#endregion
#region Save the Workbook
workbook.Version = ExcelVersion.Excel2007;
//Saving the workbook to disk. This spreadsheet is the result of opening and modifying
//an existing spreadsheet and then saving the result to a new workbook.
workbook.SaveAs(fileName);
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start(fileName);
}
#endregion
}
protected override double GetValue(IDataBar bar)
{
return(bar.Low);
}
protected override double GetValue(IDataBar bar, IReadOnlyList <double> currentResult)
{
return(Math.Min(bar.Low, currentResult[Session]));
}
protected override double GetInitialValue(IDataBar bar)
{
return(bar.High);
}
protected override double GetValue(IDataBar bar, IReadOnlyList <double> currentResult)
{
return(bar.Close);
}
protected override double GetValue(IDataBar bar, IReadOnlyList <double> currentResult)
{
return(currentResult[Session]);
}
protected override double GetData(IDataBar bar)
{
return(bar is IBar bar1 ? bar1.AskQty : 0);
}
protected abstract double GetInitialValue(IDataBar bar);
protected override double GetData(IDataBar bar)
{
return(bar.Volume);
}
protected override double GetData(IDataBar bar)
{
return(bar.Close);
}
private Workbook GetToToList()
{
Workbook workbook = new Workbook();
object[,] data = new object[, ] {
{ "TASK", "PRIORITY", "STATUS", "START DATE", "DUE DATE", "% COMPLETE", "DONE?", "NOTES" },
{ "First Thing I Need To Do", "Normal", "Not Started", null, null, 0, null, null },
{ "Other Thing I Need To Finish", "High", "In Progress", null, null, 0.5, null, null },
{ "Something Else To Get Done", "Low", "Complete", null, null, 1, null, null },
{ "More Errands And Things", "Normal", "In Progress", null, null, 0.75, null, null },
{ "So Much To Get Done This Week", "High", "In Progress", null, null, 0.25, null, null }
};
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "To-Do List";
worksheet.TabColor = Color.FromArgb(148, 112, 135);
worksheet.SheetView.DisplayGridlines = false;
//Set Value.
worksheet.Range["B1"].Value = "To-Do List";
worksheet.Range["B2:I7"].Value = data;
//Set formula.
worksheet.Range["E3"].Formula = "=TODAY()";
worksheet.Range["E4"].Formula = "=TODAY()-30";
worksheet.Range["E5"].Formula = "=TODAY()-23";
worksheet.Range["E6"].Formula = "=TODAY()-15";
worksheet.Range["E7"].Formula = "=TODAY()-5";
//Change the range's RowHeight and ColumnWidth.
worksheet.StandardHeight = 30;
worksheet.StandardWidth = 8.88671875;
worksheet.Range["1:1"].RowHeight = 72.75;
worksheet.Range["2:2"].RowHeight = 33;
worksheet.Range["3:7"].RowHeight = 30;
worksheet.Range["A:A"].ColumnWidth = 2.77734375;
worksheet.Range["B:B"].ColumnWidth = 29.109375;
worksheet.Range["C:G"].ColumnWidth = 16.77734375;
worksheet.Range["H:H"].ColumnWidth = 10.77734375;
worksheet.Range["I:I"].ColumnWidth = 29.6640625;
worksheet.Range["J:J"].ColumnWidth = 2.77734375;
//Modify the build in name styles.
var nameStyle_Normal = workbook.Styles["Normal"];
nameStyle_Normal.VerticalAlignment = VerticalAlignment.Center;
nameStyle_Normal.WrapText = true;
nameStyle_Normal.Font.ThemeFont = ThemeFont.Minor;
nameStyle_Normal.Font.ThemeColor = ThemeColor.Dark1;
nameStyle_Normal.Font.TintAndShade = 0.25;
var nameStyle_Title = workbook.Styles["Title"];
nameStyle_Title.HorizontalAlignment = HorizontalAlignment.General;
nameStyle_Title.VerticalAlignment = VerticalAlignment.Bottom;
nameStyle_Title.Font.ThemeFont = ThemeFont.Minor;
nameStyle_Title.Font.Bold = true;
nameStyle_Title.Font.Size = 38;
nameStyle_Title.Font.ThemeColor = ThemeColor.Dark1;
nameStyle_Title.Font.TintAndShade = 0.249946592608417;
nameStyle_Title.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick;
nameStyle_Title.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Dark1;
nameStyle_Title.IncludeAlignment = true;
nameStyle_Title.IncludeBorder = true;
var nameStyle_Percent = workbook.Styles["Percent"];
nameStyle_Percent.HorizontalAlignment = HorizontalAlignment.Right;
nameStyle_Percent.IndentLevel = 1;
nameStyle_Percent.VerticalAlignment = VerticalAlignment.Center;
nameStyle_Percent.IncludeAlignment = true;
var nameStyle_Heading_1 = workbook.Styles["Heading 1"];
nameStyle_Heading_1.HorizontalAlignment = HorizontalAlignment.Left;
nameStyle_Heading_1.VerticalAlignment = VerticalAlignment.Bottom;
nameStyle_Heading_1.Font.ThemeFont = ThemeFont.Major;
nameStyle_Heading_1.Font.Bold = false;
nameStyle_Heading_1.Font.Size = 11;
nameStyle_Heading_1.Font.ThemeColor = ThemeColor.Dark1;
nameStyle_Heading_1.Font.TintAndShade = 0.249946592608417;
nameStyle_Heading_1.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.None;
nameStyle_Heading_1.IncludeNumber = true;
nameStyle_Heading_1.IncludeAlignment = true;
nameStyle_Heading_1.IncludeBorder = false;
var nameStyle_Heading_2 = workbook.Styles["Heading 2"];
nameStyle_Heading_2.HorizontalAlignment = HorizontalAlignment.Right;
nameStyle_Heading_2.IndentLevel = 2;
nameStyle_Heading_2.VerticalAlignment = VerticalAlignment.Bottom;
nameStyle_Heading_2.Font.ThemeFont = ThemeFont.Major;
nameStyle_Heading_2.Font.Bold = false;
nameStyle_Heading_2.Font.Size = 11;
nameStyle_Heading_2.Font.ThemeColor = ThemeColor.Dark2;
nameStyle_Heading_2.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.None;
nameStyle_Heading_2.IncludeNumber = true;
nameStyle_Heading_2.IncludeAlignment = true;
//Create custom name styes.
IStyle nameStyle_Done = workbook.Styles.Add("Done");
nameStyle_Done.NumberFormat = "\"Done\";\"\";\"\"";
nameStyle_Done.HorizontalAlignment = HorizontalAlignment.Center;
nameStyle_Done.VerticalAlignment = VerticalAlignment.Center;
nameStyle_Done.Font.ThemeFont = ThemeFont.Minor;
nameStyle_Done.Font.ThemeColor = ThemeColor.Light1;
IStyle nameStyle_Date = workbook.Styles.Add("Date");
nameStyle_Date.NumberFormat = "yyyy/m/d";
nameStyle_Date.HorizontalAlignment = HorizontalAlignment.Right;
nameStyle_Date.VerticalAlignment = VerticalAlignment.Center;
nameStyle_Date.Font.ThemeFont = ThemeFont.Minor;
nameStyle_Date.Font.ThemeColor = ThemeColor.Dark1;
nameStyle_Date.Font.TintAndShade = 0.249946592608417;
nameStyle_Date.IncludeBorder = false;
nameStyle_Date.IncludePatterns = false;
//Apply the above name styles on ranges.
worksheet.Range["B1:I1"].Style = workbook.Styles["Title"];
worksheet.Range["B2:D2"].Style = workbook.Styles["Heading 1"];
worksheet.Range["E2:F2"].Style = workbook.Styles["Heading 2"];
worksheet.Range["G2"].Style = workbook.Styles["Heading 1"];
worksheet.Range["H2:H7"].Style = workbook.Styles["Done"];
worksheet.Range["I2"].Style = workbook.Styles["Heading 1"];
worksheet.Range["E3:F7"].Style = workbook.Styles["Date"];
worksheet.Range["G3:G7"].Style = workbook.Styles["Percent"];
//Add one custom table style.
ITableStyle style = workbook.TableStyles.Add("To-do List");
var wholeTableStyle = style.TableStyleElements[TableStyleElementType.WholeTable];
wholeTableStyle.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Light1;
wholeTableStyle.Borders[BordersIndex.EdgeBottom].TintAndShade = -0.14993743705557422;
wholeTableStyle.Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
wholeTableStyle.Borders[BordersIndex.InsideHorizontal].ThemeColor = ThemeColor.Light1;
wholeTableStyle.Borders[BordersIndex.InsideHorizontal].TintAndShade = -0.14993743705557422;
//Create a table and apply the above table style.
ITable table = worksheet.Tables.Add(worksheet.Range["B2:I7"], true);
table.Name = "ToDoList";
table.TableStyle = style;
//Use table formula in table range.
worksheet.Range["F3"].Formula = "=[@[START DATE]]+7";
worksheet.Range["F4"].Formula = "=[@[START DATE]]+35";
worksheet.Range["F5"].Formula = "=[@[START DATE]]+10";
worksheet.Range["F6"].Formula = "=[@[START DATE]]+36";
worksheet.Range["F7"].Formula = "=[@[START DATE]]+14";
worksheet.Range["H3:H7"].Formula = "=--([@[% COMPLETE]]>=1)";
//Add a expression rule.
IFormatCondition expression = worksheet.Range["B3:I7"].FormatConditions.Add(FormatConditionType.Expression, FormatConditionOperator.Between, "=AND($G3=0,$G3<>\"\")", null) as IFormatCondition;
expression.Interior.ThemeColor = ThemeColor.Light1;
expression.Interior.TintAndShade = -0.0499893185216834;
//Add a data bar rule.
IDataBar dataBar = worksheet.Range["G3:G7"].FormatConditions.AddDatabar();
dataBar.BarFillType = DataBarFillType.Solid;
dataBar.BarColor.ThemeColor = ThemeColor.Accent1;
dataBar.BarColor.TintAndShade = 0.39997558519241921;
//Add an icon set rule.
IIconSetCondition iconSet = worksheet.Range["H3:H7"].FormatConditions.AddIconSetCondition();
iconSet.IconSet = workbook.IconSets[IconSetType.Icon3Symbols];
iconSet.IconCriteria[0].Operator = FormatConditionOperator.GreaterEqual;
iconSet.IconCriteria[0].Value = 1;
iconSet.IconCriteria[0].Type = ConditionValueTypes.Number;
//Create list validations.
worksheet.Range["C3:C7"].Validation.Add(ValidationType.List, ValidationAlertStyle.Warning, ValidationOperator.Between, "Low, Normal, High", null);
IValidation validation = worksheet.Range["C3:C7"].Validation;
validation.ErrorMessage = "Select entry from the list. Select CANCEL, then press ALT+DOWN ARROW to navigate the list. Select ENTER to make selection";
worksheet.Range["D3:D7"].Validation.Add(ValidationType.List, ValidationAlertStyle.Warning, ValidationOperator.Between, "Not Started,In Progress, Deferred, Complete", null);
validation = worksheet.Range["D3:D7"].Validation;
validation.ErrorMessage = "Select entry from the list. Select CANCEL, then press ALT+DOWN ARROW to navigate the list. Select ENTER to make selection";
worksheet.Range["G3:G7"].Validation.Add(ValidationType.List, ValidationAlertStyle.Warning, ValidationOperator.Between, "0%,25%,50%,75%,100%", null);
validation = worksheet.Range["G3:G7"].Validation;
validation.ErrorMessage = "Select entry from the list. Select CANCEL, then press ALT+DOWN ARROW to navigate the list. Select ENTER to make selection";
//Create custom validation.
worksheet.Range["F3:F7"].Validation.Add(ValidationType.Custom, ValidationAlertStyle.Warning, ValidationOperator.Between, "=F3>=E3", null);
validation = worksheet.Range["F3:F7"].Validation;
validation.ErrorMessage = "The Due Date must be greater than or equal to the Start Date. Select YES to keep the value, NO to retry or CANCEL to clear the entry";
//Create none validations, set inputmessage.
worksheet.Range["B2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["B2"].Validation;
validation.InputMessage = "Enter Task in this column under this heading. Use heading filters to find specific entries";
worksheet.Range["C2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["C2"].Validation;
validation.InputMessage = "Select Priority in this column under this heading. Press ALT+DOWN ARROW to open the drop-down list, then ENTER to make selection";
worksheet.Range["D2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["D2"].Validation;
validation.InputMessage = "Select Status in this column under this heading. Press ALT+DOWN ARROW to open the drop-down list, then ENTER to make selection";
worksheet.Range["E2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["E2"].Validation;
validation.InputMessage = "Enter Start Date in this column under this heading";
worksheet.Range["F2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["F2"].Validation;
validation.InputMessage = "Enter Due Date in this column under this heading";
worksheet.Range["G2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["G2"].Validation;
validation.InputMessage = "Select % Complete in this column. Press ALT+DOWN ARROW to open the drop-down list, then ENTER to make selection. A status bar indicates progress toward completion";
worksheet.Range["H2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["H2"].Validation;
validation.InputMessage = "Icon indicator for task completion in this column under this heading is automatically updated as tasks complete";
worksheet.Range["I2"].Validation.Add(ValidationType.None, ValidationAlertStyle.Stop, ValidationOperator.Between, null, null);
validation = worksheet.Range["I2"].Validation;
validation.InputMessage = "Enter Notes in this column under this heading";
//Create customize theme.
Themes themes = new Themes();
ITheme theme = themes.Add("test");
theme.ThemeColorScheme[ThemeColor.Dark1].RGB = Color.FromArgb(0, 0, 0);
theme.ThemeColorScheme[ThemeColor.Light1].RGB = Color.FromArgb(255, 255, 255);
theme.ThemeColorScheme[ThemeColor.Dark2].RGB = Color.FromArgb(37, 28, 34);
theme.ThemeColorScheme[ThemeColor.Light2].RGB = Color.FromArgb(240, 248, 246);
theme.ThemeColorScheme[ThemeColor.Accent1].RGB = Color.FromArgb(148, 112, 135);
theme.ThemeColorScheme[ThemeColor.Accent2].RGB = Color.FromArgb(71, 166, 181);
theme.ThemeColorScheme[ThemeColor.Accent3].RGB = Color.FromArgb(234, 194, 53);
theme.ThemeColorScheme[ThemeColor.Accent4].RGB = Color.FromArgb(107, 192, 129);
theme.ThemeColorScheme[ThemeColor.Accent5].RGB = Color.FromArgb(233, 115, 61);
theme.ThemeColorScheme[ThemeColor.Accent6].RGB = Color.FromArgb(251, 147, 59);
theme.ThemeColorScheme[ThemeColor.Hyperlink].RGB = Color.FromArgb(71, 166, 181);
theme.ThemeColorScheme[ThemeColor.FollowedHyperlink].RGB = Color.FromArgb(148, 112, 135);
theme.ThemeFontScheme.Major[FontLanguageIndex.Latin].Name = "Franklin Gothic Medium";
theme.ThemeFontScheme.Minor[FontLanguageIndex.Latin].Name = "Bookman Old Style";
//Apply the above custom theme.
workbook.Theme = theme;
//Set active cell.
worksheet.Range["G4"].Activate();
return(workbook);
}
protected override double GetData(IDataBar bar)
{
return(bar.High);
}
//
// GET: /ConditionalFormatting/
public ActionResult ConditionalFormatting(string SaveOption)
{
string basePath = _hostingEnvironment.WebRootPath;
if (SaveOption == null)
{
return(View());
}
//New instance of XlsIO is created.[Equivalent to launching Microsoft Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
string OutputFileName = "";
//Open an existing Excel 2007 file
IWorkbook workbook = null;
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = null;
//Set the File Format as XLS
if (SaveOption == "Xls")
{
workbook = application.Workbooks.Create(3);
sheet = workbook.Worksheets[0];
workbook.Version = ExcelVersion.Excel97to2003;
OutputFileName = "ConditionalFormatting.xls";
}
//Set the File Format as XLSX
else
{
FileStream inputStream = new FileStream(basePath + @"/XlsIO/CFTemplate.xlsx", FileMode.Open, FileAccess.Read);
workbook = excelEngine.Excel.Workbooks.Open(inputStream);
sheet = workbook.Worksheets[0];
workbook.Version = ExcelVersion.Excel2016;
OutputFileName = "ConditionalFormatting.xlsx";
}
if (SaveOption != "Xls")
{
#region Databar
//Add condition for the range
IConditionalFormats formats = sheet.Range["C7:C46"].ConditionalFormats;
IConditionalFormat format = formats.AddCondition();
//Set Data bar and icon set for the same cell
//Set the format type
format.FormatType = ExcelCFType.DataBar;
IDataBar dataBar = format.DataBar;
//Set the constraint
dataBar.MinPoint.Type = ConditionValueType.LowestValue;
dataBar.MinPoint.Value = "0";
dataBar.MaxPoint.Type = ConditionValueType.HighestValue;
dataBar.MaxPoint.Value = "0";
//Set color for Bar
dataBar.BarColor = Color.FromArgb(156, 208, 243);
//Hide the value in data bar
dataBar.ShowValue = false;
#endregion
#region Iconset
//Add another condition in the same range
format = formats.AddCondition();
//Set Icon format type
format.FormatType = ExcelCFType.IconSet;
IIconSet iconSet = format.IconSet;
iconSet.IconSet = ExcelIconSetType.FourRating;
iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
iconSet.IconCriteria[0].Value = "0";
iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
iconSet.IconCriteria[1].Value = "0";
iconSet.ShowIconOnly = true;
//Sets Icon sets for another range
formats = sheet.Range["E7:E46"].ConditionalFormats;
format = formats.AddCondition();
format.FormatType = ExcelCFType.IconSet;
iconSet = format.IconSet;
iconSet.IconSet = ExcelIconSetType.ThreeSymbols;
iconSet.IconCriteria[0].Type = ConditionValueType.LowestValue;
iconSet.IconCriteria[0].Value = "0";
iconSet.IconCriteria[1].Type = ConditionValueType.HighestValue;
iconSet.IconCriteria[1].Value = "0";
iconSet.ShowIconOnly = true;
#endregion
#region Databar Negative value settings
//Add condition for the range
IConditionalFormats conditionalFormats1 = sheet.Range["E7:E46"].ConditionalFormats;
IConditionalFormat conditionalFormat1 = conditionalFormats1.AddCondition();
//Set Data bar and icon set for the same cell
//Set the conditionalFormat type
conditionalFormat1.FormatType = ExcelCFType.DataBar;
IDataBar dataBar1 = conditionalFormat1.DataBar;
//Set the constraint
dataBar1.BarColor = Color.YellowGreen;
dataBar1.NegativeFillColor = Color.Pink;
dataBar1.NegativeBorderColor = Color.WhiteSmoke;
dataBar1.BarAxisColor = Color.Yellow;
dataBar1.BorderColor = Color.WhiteSmoke;
dataBar1.DataBarDirection = DataBarDirection.context;
dataBar1.DataBarAxisPosition = DataBarAxisPosition.middle;
dataBar1.HasGradientFill = true;
//Hide the value in data bar
dataBar1.ShowValue = false;
#endregion
#region Duplicate
formats = sheet.Range["D7:D46"].ConditionalFormats;
format = formats.AddCondition();
format.FormatType = ExcelCFType.Duplicate;
format.BackColorRGB = Color.FromArgb(255, 199, 206);
#endregion
#region TopBottom and AboveBelowAverage
sheet = workbook.Worksheets[1];
formats = sheet.Range["N6:N35"].ConditionalFormats;
format = formats.AddCondition();
format.FormatType = ExcelCFType.TopBottom;
format.TopBottom.Type = ExcelCFTopBottomType.Bottom;
format.BackColorRGB = Color.FromArgb(51, 153, 102);
formats = sheet.Range["M6:M35"].ConditionalFormats;
format = formats.AddCondition();
format.FormatType = ExcelCFType.AboveBelowAverage;
format.AboveBelowAverage.AverageType = ExcelCFAverageType.Below;
format.FontColorRGB = Color.FromArgb(255, 255, 255);
format.BackColorRGB = Color.FromArgb(166, 59, 38);
#endregion
}
else
{
sheet.IsGridLinesVisible = false;
sheet.Range["D2"].Text = "Conditional Formatting";
sheet.Range["D2:E2"].Merge();
sheet.Range["D2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
sheet.Range["D2"].CellStyle.Font.Bold = true;
sheet.Range["D2"].CellStyle.Font.Size = 14;
//Applying conditional formatting to "E5" for format type as CellValue( Between)
IConditionalFormats condition = sheet.Range["E5"].ConditionalFormats;
sheet.Range["E5"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["E5"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["E5"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
sheet.Range["E5"].AddComment().Text = "Entering a Number between 10 to 20 will set the backcolor for the cell";
//Adding formats to IConditionalFormats collection
IConditionalFormat condition1 = condition.AddCondition();
sheet.Range["D5"].Text = "Enter a Number between 10 to 20";
condition1.FirstFormula = "10";
condition1.SecondFormula = "20";
//Setting format properties.
condition1.Operator = ExcelComparisonOperator.Between;
condition1.FormatType = ExcelCFType.CellValue;
condition1.BackColorRGB = Color.FromArgb(238, 122, 3);
condition1.IsBold = true;
condition1.IsItalic = true;
//Applying conditional formatting to "E8" for format type as CellValue( Equal)
IConditionalFormats condition2 = sheet.Range["E8"].ConditionalFormats;
sheet.Range["E8"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["E8"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["E8"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
sheet.Range["E8"].AddComment().Text = "Entering a Number as 1000 will set the highlight the number with Red color";
//Adding formats to IConditionalFormats collection
IConditionalFormat condition3 = condition2.AddCondition();
sheet.Range["D8"].Text = "Enter the Number as 1000";
//Setting format properties.
condition3.FormatType = ExcelCFType.CellValue;
condition3.Operator = ExcelComparisonOperator.Equal;
condition3.FirstFormula = "1000";
condition3.FontColorRGB = Color.FromArgb(0xde, 0x64, 0x13);
//Applying conditional formatting to "E11" for format type as CellValue( Not between)
IConditionalFormats condition4 = sheet.Range["E11"].ConditionalFormats;
sheet.Range["E11"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["E11"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["E11"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
sheet.Range["E11"].AddComment().Text = "Entering a Number which is not between 100 to 200 will retain the pattern";
//Adding formats to IConditionalFormats collection
IConditionalFormat condition5 = condition4.AddCondition();
sheet.Range["D11"].Text = "Enter a Number not between 100 to 200";
//Setting format properties
condition5.FormatType = ExcelCFType.CellValue;
condition5.Operator = ExcelComparisonOperator.NotBetween;
condition5.FirstFormula = "100";
condition5.SecondFormula = "200";
condition5.FillPattern = ExcelPattern.DarkVertical;
//Applying conditional formatting to "E14" for format type as CellValue( LessOrEqual)
IConditionalFormats condition6 = sheet.Range["E14"].ConditionalFormats;
sheet.Range["E14"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["E14"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["E14"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
sheet.Range["E14"].AddComment().Text = "Entering a Number which is less than or equal to 1000 will retain the pattern";
//Adding formats to IConditionalFormats collection
IConditionalFormat condition7 = condition6.AddCondition();
sheet.Range["D14"].Text = "Enter a Number which is less than or equal to 1000";
//Setting format properties.
condition7.FormatType = ExcelCFType.CellValue;
condition7.Operator = ExcelComparisonOperator.LessOrEqual;
condition7.FirstFormula = "1000";
condition7.BackColorRGB = Color.FromArgb(204, 212, 230);
//Applying conditional formatting to "E17" for format type as CellValue( NotEqual)
IConditionalFormats condition8 = sheet.Range["E17"].ConditionalFormats;
sheet.Range["E17"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
sheet.Range["E17"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].ShowDiagonalLine = false;
sheet.Range["E17"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].ShowDiagonalLine = false;
sheet.Range["E17"].AddComment().Text = "Entering a Number which is not equal to 1000 will retain the pattern";
//Adding formats to IConditionalFormats collection
IConditionalFormat condition9 = condition8.AddCondition();
sheet.Range["D17"].Text = "Enter a Number which is not equal to 1000";
//Setting format properties.
condition9.FormatType = ExcelCFType.CellValue;
condition9.Operator = ExcelComparisonOperator.NotEqual;
condition9.FirstFormula = "1000";
condition9.BackColorRGB = Color.ForestGreen;
sheet.UsedRange.AutofitColumns();
}
try
{
string ContentType = null;
string fileName = null;
if (SaveOption == "Xls")
{
workbook.Version = ExcelVersion.Excel97to2003;
ContentType = "Application/vnd.ms-excel";
fileName = "ConditionalFormatting.xls";
}
else
{
workbook.Version = ExcelVersion.Excel2013;
ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
fileName = "ConditionalFormatting.xlsx";
}
MemoryStream ms = new MemoryStream();
workbook.SaveAs(ms);
ms.Position = 0;
return(File(ms, ContentType, fileName));
}
catch (Exception)
{
}
// Close the workbook
workbook.Close();
excelEngine.Dispose();
return(View());
}
protected override double GetData(IDataBar bar)
{
return(bar is IBar bar1 ? bar1.StepPrice : 0);
}
protected abstract double GetData(IDataBar bar);
protected abstract double GetValue(IDataBar bar);
