Пример #1
0
 static void AddAverageConditionalFormatting(IWorkbook workbook)
 {
     workbook.Calculate();
     workbook.BeginUpdate();
     try
     {
         Worksheet worksheet = workbook.Worksheets["cfBooks"];
         workbook.Worksheets.ActiveWorksheet = worksheet;
         #region #AverageConditionalFormatting
         ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
         // Create the rule highlighting values that are above the average in cells C2 through C15.
         AverageConditionalFormatting cfRule1 = conditionalFormattings.AddAverageConditionalFormatting(worksheet.Range["$C$2:$C$15"], ConditionalFormattingAverageCondition.AboveOrEqual);
         // Specify formatting options to be applied to cells if the condition is true.
         // Set the background color to yellow.
         cfRule1.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);
         // Set the font color to red.
         cfRule1.Formatting.Font.Color = Color.Red;
         // Create the rule highlighting values that are one standard deviation below the mean in cells D2 through D15.
         AverageConditionalFormatting cfRule2 = conditionalFormattings.AddAverageConditionalFormatting(worksheet.Range["$D$2:$D$15"], ConditionalFormattingAverageCondition.BelowOrEqual, 1);
         // Specify formatting options to be applied to cells if the conditions is true.
         // Set the background color to light-green.
         cfRule2.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0x9F, 0xFB, 0x69);
         // Set the font color to blue-violet.
         cfRule2.Formatting.Font.Color = Color.BlueViolet;
         #endregion #AverageConditionalFormatting
         // Add an explanation to the created rule.
         CellRange ruleExplanation = worksheet.Range["A17:G18"];
         ruleExplanation.Value = "Determine cost values that are above the average in the first quarter and one standard deviation below the mean in the second quarter.";
     }
     finally
     {
         workbook.EndUpdate();
     }
 }
Пример #2
0
 static void AddColorScale2ConditionalFormatting(IWorkbook workbook)
 {
     workbook.Calculate();
     workbook.BeginUpdate();
     try
     {
         Worksheet worksheet = workbook.Worksheets["cfBooks"];
         workbook.Worksheets.ActiveWorksheet = worksheet;
         #region #ColorScale2ConditionalFormatting
         ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
         // Set the minimum threshold to the lowest value in the range of cells.
         ConditionalFormattingValue minPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
         // Set the maximum threshold to the highest value in the range of cells.
         ConditionalFormattingValue maxPoint = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
         // Create the two-color scale rule to differentiate low and high values in cells C2 through D15. Blue represents the lower values and yellow represents the higher values.
         ColorScale2ConditionalFormatting cfRule = conditionalFormattings.AddColorScale2ConditionalFormatting(worksheet.Range["$C$2:$D$15"], minPoint, Color.FromArgb(255, 0x9D, 0xE9, 0xFA), maxPoint, Color.FromArgb(255, 0xFF, 0xF6, 0xA9));
         #endregion #ColorScale2ConditionalFormatting
         // Add an explanation to the created rule.
         CellRange ruleExplanation = worksheet.Range["A17:G18"];
         ruleExplanation.Value = "Examine cost distribution using a gradation of two colors. Blue represents the lower values and yellow represents the higher values.";
     }
     finally
     {
         workbook.EndUpdate();
     }
 }
Пример #3
0
 static void AddExpressionConditionalFormatting(IWorkbook workbook)
 {
     workbook.Calculate();
     workbook.BeginUpdate();
     try
     {
         Worksheet worksheet = workbook.Worksheets["cfBooks"];
         workbook.Worksheets.ActiveWorksheet = worksheet;
         #region #ExpressionConditionalFormatting
         // Create the rule to identify values that are above the average in cells F2 through F15.
         ExpressionConditionalFormatting cfRule =
             worksheet.ConditionalFormattings.AddExpressionConditionalFormatting(worksheet.Range["$F$2:$F$15"], ConditionalFormattingExpressionCondition.GreaterThan, "=AVERAGE($F$2:$F$15)");
         // Specify formatting options to be applied to cells if the condition is true.
         // Set the background color to yellow.
         cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);
         // Set the font color to red.
         cfRule.Formatting.Font.Color = Color.Red;
         #endregion #ExpressionConditionalFormatting
         // Add an explanation to the created rule.
         CellRange ruleExplanation = worksheet.Range["A17:G18"];
         ruleExplanation.Value = "Identify book prices that are greater than the average price.";
     }
     finally
     {
         workbook.EndUpdate();
     }
 }
Пример #4
0
 static void AddTimePeriodConditionalFormatting(IWorkbook workbook)
 {
     workbook.Calculate();
     workbook.BeginUpdate();
     try
     {
         Worksheet worksheet = workbook.Worksheets["cfTasks"];
         workbook.Worksheets.ActiveWorksheet = worksheet;
         #region #TimePeriodConditionalFormatting
         // Create the rule to highlight today's dates in cells B2 through B6.
         TimePeriodConditionalFormatting cfRule =
             worksheet.ConditionalFormattings.AddTimePeriodConditionalFormatting(worksheet.Range["$B$2:$B$6"], ConditionalFormattingTimePeriod.Today);
         // Specify formatting options to be applied to cells if the condition is true.
         // Set the background color to pink.
         cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xF2, 0xAE, 0xE3);
         #endregion #TimePeriodConditionalFormatting
         // Add an explanation to the created rule.
         CellRange ruleExplanation = worksheet.Range["A8:B9"];
         ruleExplanation.Value = "Determine the today's task in the TO DO list.";
     }
     finally
     {
         workbook.EndUpdate();
     }
 }
Пример #5
0
 static void AddTimePeriodConditionalFormatting(IWorkbook workbook)
 {
     #region #TimePeriodConditionalFormatting
     workbook.Calculate();
     Worksheet worksheet = workbook.Worksheets["cfTasks"];
     workbook.Worksheets.ActiveWorksheet = worksheet;
     TimePeriodConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddTimePeriodConditionalFormatting(worksheet["$C$5:$C$9"], ConditionalFormattingTimePeriod.Today);
     cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xF2, 0xAE, 0xE3);
     worksheet["B2"].Value = "Determine the today's task in the TO DO list.";
     worksheet.Visible     = true;
     #endregion #TimePeriodConditionalFormatting
 }
        void GenerateAnnuityPaymentsContent()
        {
            AddDefinedNamesForAnnuityPayments();

            Sheet["I4"].FormulaInvariant = "=PMT(Interest_Rate_Per_Month,Scheduled_Number_Payments,-Loan_Amount)";
            Sheet["I5"].FormulaInvariant = "=Loan_Years*Number_of_Payments_Per_Year";
            Sheet["I6"].FormulaInvariant = "=ROUNDUP(Actual_Number_Payments,0)";
            workbook.Calculate();
            Sheet["I7"].FormulaInvariant = "=SUM(F12:F" + ActualLastRow + ")";
            Sheet["I8"].FormulaInvariant = "=SUM($I$12:$I$" + ActualLastRow + ")";

            if (ScheduledNumberOfPayments == 0)
            {
                return;
            }

            for (int i = 0; i < ActualNumberOfPayments; i++)
            {
                Sheet["B" + (i + 12).ToString()].Value = i + 1;
            }

            Sheet["C12:C" + ActualLastRow].FormulaInvariant = "=DATE(YEAR(Loan_Start),MONTH(Loan_Start)+(B12)*12/Number_of_Payments_Per_Year,DAY(Loan_Start))";
            Sheet["D12"].Formula = "=Loan_Amount";

            if (ScheduledNumberOfPayments > 1)
            {
                Sheet["D13:D" + ActualLastRow].Formula = "=J12";
            }

            Sheet["E12:E" + ActualLastRow].FormulaInvariant = "=IF(D12>0,IF(Scheduled_payment<D12, Scheduled_payment, D12),0)";
            Sheet["F12:F" + ActualLastRow].FormulaInvariant = "=IF(Extra_Payments<>0, IF(Scheduled_payment<D12, G12-E12, 0), 0)";
            Sheet["G12:G" + ActualLastRow].FormulaInvariant = "=H12+I12";
            Sheet["H12:H" + ActualLastRow].FormulaInvariant = "=IF(J12>0,PPMT(Interest_Rate_Per_Month,B12,Actual_Number_Payments,-Loan_Amount),D12)";
            Sheet["I12:I" + ActualLastRow].FormulaInvariant = "=IF(D12>0,IPMT(Interest_Rate_Per_Month,B12,Actual_Number_Payments,-Loan_Amount),0)";
            Sheet["J12:J" + ActualLastRow].FormulaInvariant = "=IF(D12-PPMT(Interest_Rate_Per_Month,B12,Actual_Number_Payments,-Loan_Amount)>0,D12-PPMT(Interest_Rate_Per_Month,B12,Actual_Number_Payments,-Loan_Amount),0)";
            Sheet["K12:K" + ActualLastRow].FormulaInvariant = "=SUM($I$12:$I12)";

            workbook.Calculate();
        }
Пример #7
0
        static void AddDataBarConditionalFormatting(IWorkbook workbook)
        {
            workbook.Calculate();
            workbook.BeginUpdate();
            try
            {
                Worksheet worksheet = workbook.Worksheets["cfBooks"];
                workbook.Worksheets.ActiveWorksheet = worksheet;
                #region #DataBarConditionalFormatting
                ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
                // Set the value corresponding to the shortest bar to the lowest value.
                ConditionalFormattingValue lowBound1 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
                // Set the value corresponding to the longest bar to the highest value.
                ConditionalFormattingValue highBound1 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.MinMax);
                // Create the rule to compare values in cells E2 through E15 using data bars.
                DataBarConditionalFormatting cfRule1 = conditionalFormattings.AddDataBarConditionalFormatting(worksheet.Range["$E$2:$E$15"], lowBound1, highBound1, DXColor.Green);
                // Set the positive bar border color to green.
                cfRule1.BorderColor = DXColor.Green;
                // Set the negative bar color to red.
                cfRule1.NegativeBarColor = DXColor.Red;
                // Set the negative bar border color to red.
                cfRule1.NegativeBarBorderColor = DXColor.Red;
                // Set the axis position to display the axis in the middle of the cell.
                cfRule1.AxisPosition = ConditionalFormattingDataBarAxisPosition.Middle;
                // Set the axis color to dark blue.
                cfRule1.AxisColor = Color.DarkBlue;

                // Set the value corresponding to the shortest bar to 0 percent.
                ConditionalFormattingValue lowBound2 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percent, "0");
                // Set the value corresponding to the longest bar to 100 percent.
                ConditionalFormattingValue highBound2 = conditionalFormattings.CreateValue(ConditionalFormattingValueType.Percent, "100");
                // Create the rule to compare values in cells G2 through G15 using data bars.
                DataBarConditionalFormatting cfRule2 = conditionalFormattings.AddDataBarConditionalFormatting(worksheet.Range["$G$2:$G$15"], lowBound2, highBound2, DXColor.SkyBlue);
                // Set the data bar border color to sky blue.
                cfRule2.BorderColor = DXColor.SkyBlue;
                // Specify the solid fill type.
                cfRule2.GradientFill = false;
                // Hide values of cells to which the rule is applied.
                cfRule2.ShowValue = false;
                #endregion #DataBarConditionalFormatting
                // Add an explanation to the created rule.
                CellRange ruleExplanation = worksheet.Range["A17:G18"];
                ruleExplanation.Value = "Compare values in the \"Cost Trend\" and \"Markup\" columns using data bars.";
            }
            finally
            {
                workbook.EndUpdate();
            }
        }
Пример #8
0
        static void SimpleSearchValue(IWorkbook workbook)
        {
            #region #SimpleSearch
            workbook.LoadDocument("Documents\\ExpenseReport.xlsx");
            workbook.Calculate();
            Worksheet worksheet = workbook.Worksheets[0];

            // Find and highlight cells containing the word "holiday".
            IEnumerable <Cell> searchResult = worksheet.Search("holiday");
            foreach (Cell cell in searchResult)
            {
                cell.Fill.BackgroundColor = System.Drawing.Color.LightGreen;
            }
            #endregion #SimpleSearch

            // Add a note.
            worksheet["E1"].Value = "Find the word \"holiday\" in the expense report";
        }
Пример #9
0
 static void AddIconSetConditionalFormatting(IWorkbook workbook)
 {
     workbook.Calculate();
     workbook.BeginUpdate();
     try
     {
         Worksheet worksheet = workbook.Worksheets["cfBooks"];
         workbook.Worksheets.ActiveWorksheet = worksheet;
         #region #IconSetConditionalFormatting
         ConditionalFormattingCollection conditionalFormattings = worksheet.ConditionalFormattings;
         // Set the first threshold to the lowest value in the range of cells using the MIN() formula.
         ConditionalFormattingIconSetValue minPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Formula, "=MIN($E$2:$E$15)", ConditionalFormattingValueOperator.GreaterOrEqual);
         // Set the second threshold to 0.
         ConditionalFormattingIconSetValue midPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0", ConditionalFormattingValueOperator.GreaterOrEqual);
         // Set the third threshold to 0.01.
         ConditionalFormattingIconSetValue maxPoint = conditionalFormattings.CreateIconSetValue(ConditionalFormattingValueType.Number, "0.01", ConditionalFormattingValueOperator.GreaterOrEqual);
         // Create the rule to apply a specific icon from the three arrow icon set to each cell in the range  E2:E15 based on its value.
         IconSetConditionalFormatting cfRule = conditionalFormattings.AddIconSetConditionalFormatting(worksheet.Range["$E$2:$E$15"], IconSetType.Arrows3, new ConditionalFormattingIconSetValue[] { minPoint, midPoint, maxPoint });
         // Specify the custom icon to be displayed if the second condition is true.
         // To do this, set the IconSetConditionalFormatting.IsCustom property to true, which is false by default.
         cfRule.IsCustom = true;
         // Initialize the ConditionalFormattingCustomIcon object.
         ConditionalFormattingCustomIcon cfCustomIcon = new ConditionalFormattingCustomIcon();
         // Specify the icon set where you wish to get the icon.
         cfCustomIcon.IconSet = IconSetType.TrafficLights13;
         // Specify the index of the desired icon in the set.
         cfCustomIcon.IconIndex = 1;
         // Add the custom icon at the specified position in the initial icon set.
         cfRule.SetCustomIcon(1, cfCustomIcon);
         // Hide values of cells to which the rule is applied.
         cfRule.ShowValue = false;
         #endregion #IconSetConditionalFormatting
         // Add an explanation to the created rule.
         CellRange ruleExplanation = worksheet.Range["A17:G18"];
         ruleExplanation.Value = "Identify upward and downward cost trends.";
     }
     finally
     {
         workbook.EndUpdate();
     }
 }
Пример #10
0
 static void AddFormulaExpressionConditionalFormatting(IWorkbook workbook)
 {
     workbook.Calculate();
     workbook.BeginUpdate();
     try
     {
         Worksheet worksheet = workbook.Worksheets["cfBooks"];
         workbook.Worksheets.ActiveWorksheet = worksheet;
         #region #FormulaExpressionConditionalFormatting
         // Create the rule to shade alternate rows without applying a new style.
         FormulaExpressionConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddFormulaExpressionConditionalFormatting(worksheet.Range["$A$2:$G$15"], "=MOD(ROW(),2)=1");
         // Specify formatting options to be applied to cells if the condition is true.
         // Set the background color to light blue.
         cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xBC, 0xDA, 0xF7);
         #endregion #FormulaExpressionConditionalFormatting
         // Add an explanation to the created rule.
         CellRange ruleExplanation = worksheet.Range["A17:G18"];
         ruleExplanation.Value = "Shade alternate rows in light blue without applying a new style.";
     }
     finally
     {
         workbook.EndUpdate();
     }
 }
Пример #11
0
 static void AddSpecialConditionalFormatting(IWorkbook workbook)
 {
     workbook.Calculate();
     workbook.BeginUpdate();
     try
     {
         Worksheet worksheet = workbook.Worksheets["cfBooks"];
         workbook.Worksheets.ActiveWorksheet = worksheet;
         #region #SpecialConditionalFormatting
         // Create the rule to identify unique values in cells A2 through A15.
         SpecialConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddSpecialConditionalFormatting(worksheet["$A$2:$A$15"], ConditionalFormattingSpecialCondition.ContainUniqueValue);
         // Specify formatting options to be applied to cells if the condition is true.
         // Set the background color to yellow.
         cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xFA, 0xF7, 0xAA);
         #endregion #SpecialConditionalFormatting
         // Add an explanation to the created rule.
         CellRange ruleExplanation = worksheet.Range["A17:G18"];
         ruleExplanation.Value = "In a list of authors quickly identify unique values.";
     }
     finally
     {
         workbook.EndUpdate();
     }
 }
Пример #12
0
 static void AddTextConditionalFormatting(IWorkbook workbook)
 {
     workbook.Calculate();
     workbook.BeginUpdate();
     try
     {
         Worksheet worksheet = workbook.Worksheets["cfBooks"];
         workbook.Worksheets.ActiveWorksheet = worksheet;
         #region #TextConditionalFormatting
         // Create the rule to highlight values with the given text string in cells A2 through A15.
         TextConditionalFormatting cfRule = worksheet.ConditionalFormattings.AddTextConditionalFormatting(worksheet["$A$2:$A$15"], ConditionalFormattingTextCondition.Contains, "Bradbury");
         // Specify formatting options to be applied to cells if the condition is true.
         // Set the background color to pink.
         cfRule.Formatting.Fill.BackgroundColor = Color.FromArgb(255, 0xE1, 0x95, 0xC2);
         #endregion #TextConditionalFormatting
         // Add an explanation to the created rule.
         CellRange ruleExplanation = worksheet.Range["A17:G18"];
         ruleExplanation.Value = "Quickly find books written by Ray Bradbury.";
     }
     finally
     {
         workbook.EndUpdate();
     }
 }