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;
}
static void Main(string[] args)
{
// Create a new empty workbook.
IWorkbook workbook = Factory.GetWorkbook();
IRange cells = workbook.Worksheets[0].Cells["A1:A5"];
// Place formulas resulting in random numbers between 0 and 1000.
cells.Formula = "=RAND()*1000";
cells.NumberFormat = "0";
// Add a format condition to use blue background and white text for numbers >500.
IFormatCondition fc = cells.FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Greater, "500", "");
fc.Interior.Color = System.Drawing.Color.Navy;
fc.Font.Color = System.Drawing.Color.White;
// Save to xls and xlsx.
workbook.SaveAs(@"c:\tmp\FormatConditions.xls", FileFormat.Excel8);
workbook.SaveAs(@"c:\tmp\FormatConditions.xlsx", FileFormat.OpenXMLWorkbook);
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
//Range A1's displaying color is table style
worksheet.Tables.Add(worksheet.Range["A1:E5"], true);
var color_A1 = worksheet.Range["A1"].DisplayFormat.Interior.Color;
//Range A1's displaying color will be cell style, yellow.
worksheet.Range["A1"].Interior.Color = Color.Yellow;
var color_A1_1 = worksheet.Range["A1"].DisplayFormat.Interior.Color;
//Range A1's displaying color will be conditional format style, green.
IFormatCondition condition = worksheet.Range["A1"].FormatConditions.Add(FormatConditionType.NoBlanksCondition) as IFormatCondition;
condition.Interior.Color = Color.Green;
var color_A1_2 = worksheet.Range["A1"].DisplayFormat.Interior.Color;
}
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;
//expression rule, format weight greater than 80.
IFormatCondition condition = worksheet.Range["E2:E7"].FormatConditions.Add(FormatConditionType.Expression, 0, "=E2>68") as IFormatCondition;
condition.Interior.Color = Color.LightPink;
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["B:C"].ColumnWidth = 17;
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;
//weight between 71 and 79, set its interior color to yellow.
IFormatCondition condition = worksheet.Range["E2:E7"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Between, 66, 70) as IFormatCondition;
condition.Interior.Color = Color.LightGreen;
}
public static void ApplyHeatMap(this TableDataRange source, Color low, Color mid, Color high)
{
Color color = Color.FromArgb(245, 245, 245);
source.Label.Interior.Color = color;
source.RowLabel.Interior.Color = color;
source.RowHeader.Interior.Color = color;
source.ColumnLabel.Interior.Color = color;
source.ColumnHeader.Interior.Color = color;
IFormatCondition format = source.DataBody.FormatConditions.AddColorScale(3);
format.ColorScale.ColorScaleCriteria[0].Type = ConditionValueTypes.Percentile;
format.ColorScale.ColorScaleCriteria[0].Value = 0;
format.ColorScale.ColorScaleCriteria[0].FormatColor.Color = low;
format.ColorScale.ColorScaleCriteria[1].Type = ConditionValueTypes.Percentile;
format.ColorScale.ColorScaleCriteria[1].Value = 50;
format.ColorScale.ColorScaleCriteria[1].FormatColor.Color = mid;
format.ColorScale.ColorScaleCriteria[2].Type = ConditionValueTypes.Percentile;
format.ColorScale.ColorScaleCriteria[2].Value = 100;
format.ColorScale.ColorScaleCriteria[2].FormatColor.Color = high;
}
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);
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
IWorksheet worksheet = workbook.Worksheets[0];
#region theme
//create a custom theme.
Themes themes = new Themes();
ITheme theme = themes.Add("testTheme");
theme.ThemeColorScheme[ThemeColor.Light1].RGB = Color.FromRGB(255, 255, 255);
theme.ThemeColorScheme[ThemeColor.Dark1].RGB = Color.FromRGB(0, 0, 0);
theme.ThemeColorScheme[ThemeColor.Light2].RGB = Color.FromRGB(222, 222, 212);
theme.ThemeColorScheme[ThemeColor.Dark2].RGB = Color.FromRGB(30, 46, 47);
theme.ThemeColorScheme[ThemeColor.Accent1].RGB = Color.FromRGB(233, 117, 90);
theme.ThemeColorScheme[ThemeColor.Accent2].RGB = Color.FromRGB(122, 182, 186);
theme.ThemeColorScheme[ThemeColor.Accent3].RGB = Color.FromRGB(125, 181, 135);
theme.ThemeColorScheme[ThemeColor.Accent4].RGB = Color.FromRGB(230, 191, 94);
theme.ThemeColorScheme[ThemeColor.Accent5].RGB = Color.FromRGB(230, 143, 77);
theme.ThemeColorScheme[ThemeColor.Accent6].RGB = Color.FromRGB(194, 107, 112);
theme.ThemeColorScheme[ThemeColor.Hyperlink].RGB = Color.FromRGB(122, 182, 186);
theme.ThemeColorScheme[ThemeColor.FollowedHyperlink].RGB = Color.FromRGB(166, 140, 177);
theme.ThemeFontScheme.Major[FontLanguageIndex.Latin].Name = "Gill Sans";
theme.ThemeFontScheme.Minor[FontLanguageIndex.Latin].Name = "Gill Sans";
//assign the custom theme for workbook.
workbook.Theme = theme;
#endregion
//does not show sheet gridlines.
worksheet.SheetView.DisplayGridlines = false;
#region RowHeightColumnWidth
//set row height and column width.
worksheet.StandardHeight = 12.75;
worksheet.StandardWidth = 8.43;
worksheet.Rows[1].RowHeight = 32.25;
worksheet.Rows[2].RowHeight = 13.5;
worksheet.Rows[3].RowHeight = 18.75;
worksheet.Rows[6].RowHeight = 18.75;
worksheet.Rows[9].RowHeight = 18.75;
worksheet.Rows[12].RowHeight = 18.75;
worksheet.Rows[15].RowHeight = 19.5;
worksheet.Rows[16].RowHeight = 13.5;
worksheet.Rows[33].RowHeight = 19.5;
worksheet.Rows[34].RowHeight = 13.5;
worksheet.Columns[0].ColumnWidth = 1.7109375;
worksheet.Columns[1].ColumnWidth = 12.140625;
worksheet.Columns[2].ColumnWidth = 12.140625;
worksheet.Columns[3].ColumnWidth = 12.140625;
worksheet.Columns[4].ColumnWidth = 11.85546875;
worksheet.Columns[5].ColumnWidth = 12.7109375;
worksheet.Columns[6].ColumnWidth = 13.85546875;
worksheet.Columns[7].ColumnWidth = 44.7109375;
#endregion
#region Values
//initialize worksheet's values.
worksheet.Name = "BLOOD PRESSURE DATA";
worksheet.Range["B2"].Value = "BLOOD PRESSURE TRACKER";
worksheet.Range["B4:F13"].Value = new object[, ] {
{ "NAME", null, null, null, null },
{ null, null, null, null, null },
{ null, null, null, "Systolic", "Diastolic" },
{ "TARGET BLOOD PRESSURE", null, null, 120, 80 },
{ null, null, null, null, null },
{ null, null, null, "Systolic", "Diastolic" },
{ "CALL PHYSICIAN IF ABOVE", null, null, 140, 90 },
{ null, null, null, null, null },
{ null, null, null, null, null },
{ "PHYSICIAN PHONE NUMBER", null, null, "[Phone Number]", null }
};
worksheet.Range["B16"].Value = "CHARTED PROGRESS";
worksheet.Range["B34"].Value = "DATA ENTRY";
#endregion
#region Table
//initialize table data.
worksheet.Range["B36:H44"].Value = new object[, ] {
{ "TIME", "DATE", "AM/PM", "SYSTOLIC", "DIASTOLIC", "HEART RATE", "NOTES" },
{ new TimeSpan(10, 00, 00), DateTime.Parse("7/1/2013 12:00:00 AM"), "AM", 129, 99, 72, null },
{ new TimeSpan(18, 00, 00), DateTime.Parse("7/1/2013 12:00:00 AM"), "PM", 133, 80, 75, null },
{ new TimeSpan(10, 30, 00), DateTime.Parse("7/2/2012 12:00:00 AM"), "AM", 142, 86, 70, null },
{ new TimeSpan(19, 00, 00), DateTime.Parse("7/2/2012 12:00:00 AM"), "PM", 141, 84, 68, null },
{ new TimeSpan(09, 00, 00), DateTime.Parse("7/3/2012 12:00:00 AM"), "AM", 137, 84, 70, null },
{ new TimeSpan(18, 30, 00), DateTime.Parse("7/3/2012 12:00:00 AM"), "PM", 139, 83, 72, null },
{ new TimeSpan(10, 00, 00), DateTime.Parse("7/4/2012 12:00:00 AM"), "AM", 140, 85, 78, null },
{ new TimeSpan(18, 00, 00), DateTime.Parse("7/4/2012 12:00:00 AM"), "PM", 138, 85, 69, null },
};
ITable table = worksheet.Tables.Add(worksheet.Range["B36:H44"], true);
table.ShowTotals = true;
//set total row formulas.
table.Columns[0].Total.Value = "Average";
table.Columns[3].TotalsCalculation = TotalsCalculation.Average;
table.Columns[4].TotalsCalculation = TotalsCalculation.Average;
table.Columns[5].TotalsCalculation = TotalsCalculation.Average;
table.Columns[6].TotalsCalculation = TotalsCalculation.None;
//config data body range and total range's number format.
table.Columns[0].DataBodyRange.NumberFormat = "h:mm;@";
table.Columns[1].DataBodyRange.NumberFormat = "m/d/yyyy";
table.Columns[3].DataBodyRange.NumberFormat = "0";
table.Columns[4].DataBodyRange.NumberFormat = "0";
table.Columns[5].DataBodyRange.NumberFormat = "0";
table.Columns[3].Total.NumberFormat = "0";
table.Columns[4].Total.NumberFormat = "0";
table.Columns[5].Total.NumberFormat = "0";
//config table range's alignment.
table.Range.HorizontalAlignment = HorizontalAlignment.Left;
table.Range.IndentLevel = 0;
table.Range.VerticalAlignment = VerticalAlignment.Center;
#endregion
#region TableStyle
//create a custom table style.
ITableStyle tablestyle = workbook.TableStyles.Add("testStyle");
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Font.ThemeColor = ThemeColor.Dark1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Font.TintAndShade = 0.25;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeTop].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeTop].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideHorizontal].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideHorizontal].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.InsideHorizontal].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeBottom].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeLeft].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeLeft].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeLeft].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeRight].ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.WholeTable].Borders[BordersIndex.EdgeRight].TintAndShade = 0.4;
tablestyle.TableStyleElements[TableStyleElementType.FirstRowStripe].Interior.ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.FirstRowStripe].Interior.TintAndShade = 0.8;
tablestyle.TableStyleElements[TableStyleElementType.HeaderRow].Font.Bold = true;
tablestyle.TableStyleElements[TableStyleElementType.HeaderRow].Font.ThemeColor = ThemeColor.Dark1;
tablestyle.TableStyleElements[TableStyleElementType.HeaderRow].Font.TintAndShade = 0.25;
tablestyle.TableStyleElements[TableStyleElementType.HeaderRow].Interior.ThemeColor = ThemeColor.Accent1;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Font.Bold = true;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Font.ThemeColor = ThemeColor.Dark1;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Font.TintAndShade = 0.25;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Borders[BordersIndex.EdgeTop].LineStyle = BorderLineStyle.Double;
tablestyle.TableStyleElements[TableStyleElementType.TotalRow].Borders[BordersIndex.EdgeTop].ThemeColor = ThemeColor.Accent1;
//assign custom table style for table.
table.TableStyle = workbook.TableStyles["testStyle"];
#endregion
#region Style
//assign built-in styles for ranges.
worksheet.Range["B2:H2"].Style = workbook.Styles["Heading 1"];
worksheet.Range["B4:F4, B7:D7, B10:D10, B13:D13"].Style = workbook.Styles["Heading 2"];
worksheet.Range["B16:H16, B34:H34"].Style = workbook.Styles["Heading 3"];
//modify built-in styles.
IStyle style_Heading1 = workbook.Styles["Heading 1"];
style_Heading1.HorizontalAlignment = HorizontalAlignment.General;
style_Heading1.VerticalAlignment = VerticalAlignment.Center;
style_Heading1.Font.ThemeFont = ThemeFont.Major;
style_Heading1.Font.Size = 24;
style_Heading1.Font.Bold = true;
style_Heading1.Font.ThemeColor = ThemeColor.Accent1;
style_Heading1.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick;
style_Heading1.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Accent1;
style_Heading1.IncludeAlignment = true;
style_Heading1.IncludeFont = true;
style_Heading1.IncludeBorder = true;
style_Heading1.IncludeNumber = false;
style_Heading1.IncludePatterns = false;
style_Heading1.IncludeProtection = false;
IStyle style_Heading2 = workbook.Styles["Heading 2"];
style_Heading2.HorizontalAlignment = HorizontalAlignment.General;
style_Heading2.VerticalAlignment = VerticalAlignment.Bottom;
style_Heading2.Font.ThemeFont = ThemeFont.Minor;
style_Heading2.Font.Size = 14;
style_Heading2.Font.ThemeColor = ThemeColor.Dark1;
style_Heading2.Font.TintAndShade = 0.25;
style_Heading2.Font.Bold = false;
style_Heading2.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Dotted;
style_Heading2.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Light1;
style_Heading2.Borders[BordersIndex.EdgeBottom].TintAndShade = -0.5;
style_Heading2.IncludeAlignment = true;
style_Heading2.IncludeFont = true;
style_Heading2.IncludeBorder = true;
style_Heading2.IncludeNumber = false;
style_Heading2.IncludePatterns = false;
style_Heading2.IncludeProtection = false;
IStyle style_Heading3 = workbook.Styles["Heading 3"];
style_Heading3.HorizontalAlignment = HorizontalAlignment.General;
style_Heading3.VerticalAlignment = VerticalAlignment.Center;
style_Heading3.Font.ThemeFont = ThemeFont.Minor;
style_Heading3.Font.Size = 14;
style_Heading3.Font.Bold = true;
style_Heading3.Font.ThemeColor = ThemeColor.Dark1;
style_Heading3.Font.TintAndShade = 0.25;
style_Heading3.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thick;
style_Heading3.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Accent2;
style_Heading3.IncludeAlignment = true;
style_Heading3.IncludeFont = true;
style_Heading3.IncludeBorder = true;
style_Heading3.IncludeNumber = false;
style_Heading3.IncludePatterns = false;
style_Heading3.IncludeProtection = false;
IStyle style_Normal = workbook.Styles["Normal"];
style_Normal.NumberFormat = "General";
style_Normal.HorizontalAlignment = HorizontalAlignment.General;
style_Normal.VerticalAlignment = VerticalAlignment.Center;
style_Normal.Font.ThemeFont = ThemeFont.Minor;
style_Normal.Font.Size = 10;
style_Normal.Font.ThemeColor = ThemeColor.Dark1;
style_Normal.Font.TintAndShade = 0.25;
style_Normal.IncludeAlignment = true;
style_Normal.IncludeFont = true;
style_Normal.IncludeBorder = true;
style_Normal.IncludeNumber = true;
style_Normal.IncludePatterns = true;
style_Normal.IncludeProtection = true;
//modify cell styles.
worksheet.Range["B4"].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.None;
worksheet.Range["C4:F4"].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Thin;
IRange range1 = worksheet.Range["E7:F7, E10:F10"];
range1.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Dotted;
range1.Borders[BordersIndex.EdgeBottom].ThemeColor = ThemeColor.Light1;
range1.Borders[BordersIndex.EdgeBottom].TintAndShade = -0.5;
range1.Font.Bold = true;
IRange range2 = worksheet.Range["E7, E10"];
range2.Borders[BordersIndex.EdgeRight].LineStyle = BorderLineStyle.Thin;
range2.Borders[BordersIndex.EdgeRight].ThemeColor = ThemeColor.Light1;
range2.Borders[BordersIndex.EdgeRight].TintAndShade = -0.5;
#endregion
#region Chart
//create a new chart.
IShape shape = worksheet.Shapes.AddChart(ChartType.ColumnClustered, 8.99984251968504, 268.5, 627.750157480315, 184.5);
//create series for chart.
ISeries series_systolic = shape.Chart.SeriesCollection.NewSeries();
ISeries series_diatolic = shape.Chart.SeriesCollection.NewSeries();
ISeries series_HeartRate = shape.Chart.SeriesCollection.NewSeries();
//set series formulas.
series_systolic.Formula = "=SERIES('BLOOD PRESSURE DATA'!$E$36,'BLOOD PRESSURE DATA'!$C$37:$D$44,'BLOOD PRESSURE DATA'!$E$37:$E$44,1)";
series_diatolic.Formula = "=SERIES('BLOOD PRESSURE DATA'!$F$36,'BLOOD PRESSURE DATA'!$C$37:$D$44,'BLOOD PRESSURE DATA'!$F$37:$F$44,2)";
//set series plot on secondary axis, and change its chart type.
series_HeartRate.AxisGroup = AxisGroup.Secondary;
series_HeartRate.ChartType = ChartType.Line;
series_HeartRate.Formula = "=SERIES('BLOOD PRESSURE DATA'!$G$36,,'BLOOD PRESSURE DATA'!$G$37:$G$44,3)";
//set series fill to gradient fill.
series_systolic.Format.Fill.TwoColorGradient(GradientStyle.Horizontal, 1);
series_systolic.Format.Fill.GradientAngle = 270;
series_systolic.Format.Fill.GradientStops[0].Color.RGB = Color.FromRGB(255, 172, 175);
series_systolic.Format.Fill.GradientStops[1].Color.RGB = Color.FromRGB(255, 227, 228);
series_systolic.Format.Fill.GradientStops.Insert(0xFEC6C8, 0.35);
series_systolic.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent6;
series_diatolic.Format.Fill.TwoColorGradient(GradientStyle.Horizontal, 1);
series_diatolic.Format.Fill.GradientAngle = 270;
series_diatolic.Format.Fill.GradientStops[0].Color.RGB = Color.FromRGB(255, 192, 147);
series_diatolic.Format.Fill.GradientStops[1].Color.RGB = Color.FromRGB(255, 227, 212);
series_diatolic.Format.Fill.GradientStops.Insert(0xFFCBA9, 0.35);
series_diatolic.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent5;
//set series gap width and overlap.
shape.Chart.ColumnGroups[0].GapWidth = 150;
shape.Chart.ColumnGroups[0].Overlap = 0;
//set series line style.
series_HeartRate.Format.Line.BeginArrowheadLength = ArrowheadLength.Medium;
series_HeartRate.Format.Line.BeginArrowheadStyle = ArrowheadStyle.None;
series_HeartRate.Format.Line.BeginArrowheadWidth = ArrowheadWidth.Medium;
series_HeartRate.Format.Line.Color.ObjectThemeColor = ThemeColor.Accent4;
series_HeartRate.Format.Line.Color.TintAndShade = 0;
series_HeartRate.Format.Line.DashStyle = LineDashStyle.Solid;
series_HeartRate.Format.Line.EndArrowheadLength = ArrowheadLength.Medium;
series_HeartRate.Format.Line.EndArrowheadStyle = ArrowheadStyle.None;
series_HeartRate.Format.Line.EndArrowheadWidth = ArrowheadWidth.Medium;
series_HeartRate.Format.Line.Style = LineStyle.Single;
series_HeartRate.Format.Line.Weight = 1.25;
IAxis primary_axis = shape.Chart.Axes.Item(AxisType.Value, AxisGroup.Primary);
primary_axis.HasTitle = true;
primary_axis.AxisTitle.Text = "BLOOD PRESSURE";
primary_axis.AxisTitle.IncludeInLayout = true;
IAxis secondary_axis = shape.Chart.Axes.Item(AxisType.Value, AxisGroup.Secondary);
secondary_axis.HasTitle = true;
secondary_axis.AxisTitle.Text = "HEART RATE";
secondary_axis.AxisTitle.IncludeInLayout = true;
IAxis category_axis = shape.Chart.Axes.Item(AxisType.Category, AxisGroup.Primary);
category_axis.HasTitle = true;
category_axis.CategoryType = CategoryType.CategoryScale;
category_axis.Format.Line.Color.ColorType = SolidColorType.None;
shape.Chart.HasTitle = false;
//set chart font style.
shape.Chart.ChartArea.Font.Size = 9;
shape.Chart.ChartArea.Font.Color.ObjectThemeColor = ThemeColor.Dark1;
shape.Chart.ChartArea.Font.Color.Brightness = 0.5;
#endregion
#region Shape
IShape shape1 = worksheet.Shapes.AddShape(AutoShapeType.Rectangle, 402, 77.25, 234, 100);
shape1.Fill.Solid();
shape1.Fill.Color.ObjectThemeColor = ThemeColor.Accent1;
shape1.Fill.Color.Brightness = 0.6;
//set shape's border to no line.
shape1.Line.Color.ColorType = SolidColorType.None;
//set shape rich text.
ITextRange shape1_p1 = shape1.TextFrame.TextRange.Paragraphs[0];
shape1_p1.Text = "*";
shape1_p1.Runs.Add(" Blood pressures may vary dependent on many");
shape1_p1.Runs.Add(" factors. Always consult with a physician about what is normal for you. These numbers may vary slightly.");
ITextRange shape1_p2 = shape1.TextFrame.TextRange.Paragraphs.Add("");
ITextRange shape1_p3 = shape1.TextFrame.TextRange.Paragraphs.Add("Info from National Institute of Health:");
ITextRange shape1_p4 = shape1.TextFrame.TextRange.Paragraphs.Add("http://www.nhlbi.nih.gov/health/health-topics/topics/hbp/");
shape1.TextFrame.TextRange.Font.Size = 10;
shape1.TextFrame.TextRange.Font.ThemeFont = ThemeFont.Minor;
shape1.TextFrame.TextRange.Font.Color.ObjectThemeColor = ThemeColor.Dark1;
shape1.TextFrame.TextRange.Font.Color.Brightness = 0.25;
shape1_p3.Runs[0].Font.Bold = true;
IShape shape2 = worksheet.Shapes.AddShape(AutoShapeType.Rectangle, 421.5, 546.75, 198, 50);
shape2.Fill.Solid();
shape2.Fill.Color.ObjectThemeColor = ThemeColor.Accent3;
shape2.Fill.Color.Brightness = 0.6;
//set shape's border to no line.
shape2.Line.Color.ColorType = SolidColorType.None;
ITextRange shape2_p1 = shape2.TextFrame.TextRange.Paragraphs[0];
shape2_p1.Text = "NOTE:";
shape2_p1.Runs.Add(" Any blood pressure readings over the indicated numbers (\"CALL PHYSICIAN IF ABOVE\") will be");
shape2_p1.Runs.Add(" highlighted.");
shape2.TextFrame.TextRange.Font.Size = 10;
shape2.TextFrame.TextRange.Font.ThemeFont = ThemeFont.Minor;
shape2.TextFrame.TextRange.Font.Color.ObjectThemeColor = ThemeColor.Dark1;
shape2.TextFrame.TextRange.Font.Color.Brightness = 0.25;
shape2_p1.Runs[0].Font.Bold = true;
#endregion
#region DefinedName
//create defined names for workbook.
workbook.Names.Add("MaxDiastolic", "='BLOOD PRESSURE DATA'!$F$10");
workbook.Names.Add("MaxSystolic", "='BLOOD PRESSURE DATA'!$E$10");
#endregion
#region ConditionalFormat
//create conditional format for ranges.
IFormatCondition condition1 = worksheet.Range["E37:E44"].FormatConditions.Add(FormatConditionType.Expression, formula1: "=E37>MaxSystolic") as IFormatCondition;
IFormatCondition condition2 = worksheet.Range["F37:F44"].FormatConditions.Add(FormatConditionType.Expression, formula1: "=F37>MaxDiastolic") as IFormatCondition;
condition1.Interior.Color = Color.Red;
condition2.Interior.Color = Color.Red;
#endregion
}
public override void Execute(GrapeCity.Documents.Excel.Workbook workbook)
{
GrapeCity.Documents.Excel.Workbook.AddCustomFunction(new MyConditionalSumFunctionX());
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Range["A1:A10"].Value = new object[, ] {
{ 1 },
{ 2 },
{ 3 },
{ 4 },
{ 5 },
{ 6 },
{ 7 },
{ 8 },
{ 9 },
{ 10 }
};
IFormatCondition cellValueRule = worksheet.Range["A1:A10"].FormatConditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Greater, 5) as IFormatCondition;
cellValueRule.Interior.Color = System.Drawing.Color.Red;
//Sum cells value which display format interior color are red.
worksheet.Range["C1"].Formula = "=MyConditionalSum(A1:A10)";
//Range["C1"]'s value is 40.
var result = worksheet.Range["C1"].Value;
/* Implementation of MyConditionalSumFunctionX
*
* public class MyConditionalSumFunctionX : CustomFunction
* {
* public MyConditionalSumFunctionX() : base("MyConditionalSum", FunctionValueType.Number, CreateParameters())
* {
*
* }
*
* private static Parameter[] CreateParameters()
* {
* Parameter[] parameters = new Parameter[254];
* for (int i = 0; i < 254; i++)
* {
* parameters[i] = new Parameter(FunctionValueType.Object, true);
* }
*
* return parameters;
* }
*
* public override object Evaluate(object[] arguments, ICalcContext context)
* {
* double sum = 0d;
* foreach (var argument in arguments)
* {
* foreach (var item in Enumerate(argument))
* {
* if (item is CalcError)
* {
* return item;
* }
* if (item is double)
* {
* sum += (double)item;
* }
* }
* }
*
* return sum;
* }
*
* private static IEnumerable<object> Enumerate(object obj)
* {
* if (obj is IEnumerable<object>)
* {
* foreach (var item in obj as IEnumerable<object>)
* {
* foreach (var item2 in Enumerate(item))
* {
* yield return item2;
* }
* }
* }
* else if (obj is object[,])
* {
* var array = obj as object[,];
* int rowCount = array.GetLength(0);
* int colCount = array.GetLength(1);
* for (int i = 0; i < rowCount; i++)
* {
* for (int j = 0; j < colCount; j++)
* {
* yield return array[i, j];
* }
* }
* }
* else if (obj is CalcReference)
* {
* foreach (var item in Enumerate(obj as CalcReference))
* {
* yield return item;
* }
* }
*
* yield return obj;
* }
*
* private static IEnumerable<object> Enumerate(CalcReference reference)
* {
* foreach (var range in reference.GetRanges())
* {
* int rowCount = range.Rows.Count;
* int colCount = range.Columns.Count;
* for (int i = 0; i < rowCount; i++)
* {
* for (int j = 0; j < colCount; j++)
* {
* if (range.Cells[i, j].DisplayFormat.Interior.Color == System.Drawing.Color.Red)
* {
* yield return range.Cells[i, j].Value;
* }
* }
* }
* }
* }
* }
*
*/
}
