public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
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 }
};
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:F7"].Value = data;
worksheet.Range["A:F"].ColumnWidth = 15;
IIconSetCondition iconset = worksheet.Range["F2:F7"].FormatConditions.AddIconSetCondition();
iconset.IconSet = workbook.IconSets[IconSetType.Icon3TrafficLights1];
//green traffic light will in the top.
worksheet.Sort.SortFields.Add(new IconSortField(worksheet.Range["F2:F7"], workbook.IconSets[IconSetType.Icon3TrafficLights1][2], SortOrder.Ascending));
worksheet.Sort.Range = worksheet.Range["A2:F7"];
worksheet.Sort.Orientation = SortOrientation.Columns;
worksheet.Sort.Apply();
}
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;
worksheet.Range["A:F"].ColumnWidth = 15;
IIconSetCondition iconset = worksheet.Range["E2:E7"].FormatConditions.AddIconSetCondition();
iconset.IconSet = workbook.IconSets[IconSetType.Icon3TrafficLights1];
IFormatCondition condition = worksheet.Range["E2:E7"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Between, "71", "79") as IFormatCondition;
condition.StopIfTrue = true;
//filter in the rows which has no icon.
worksheet.Range["A1:F7"].AutoFilter(4, null, AutoFilterOperator.NoIcon);
}
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;
//iconset rule
IIconSetCondition iconset = worksheet.Range["E2:E7"].FormatConditions.AddIconSetCondition();
iconset.IconSet = workbook.IconSets[IconSetType.Icon3TrafficLights1];
//cell value rule added later, it has the highest priority, set StopIfTrue to true, if cell match condition, it will not apply icon set rule.
IFormatCondition cellvalueRule = worksheet.Range["E2:E7"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Between, "66", "70") as IFormatCondition;
cellvalueRule.Font.ThemeColor = ThemeColor.Accent1;
cellvalueRule.StopIfTrue = true;
}
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;
//icon set rule.
IIconSetCondition condition = worksheet.Range["E2:E7"].FormatConditions.AddIconSetCondition();
condition.IconSet = workbook.IconSets[IconSetType.Icon3Symbols];
condition.IconCriteria[1].Operator = FormatConditionOperator.GreaterEqual;
condition.IconCriteria[1].Value = 30;
condition.IconCriteria[1].Type = ConditionValueTypes.Percent;
condition.IconCriteria[2].Operator = FormatConditionOperator.GreaterEqual;
condition.IconCriteria[2].Value = 70;
condition.IconCriteria[2].Type = ConditionValueTypes.Percent;
}
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];
}
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);
}
