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);