SampleApp/Sample14.cs

/*******************************************************************************
 * You may amend and distribute as you like, but don't remove this header!
 * 
 * All rights reserved.
 * 
 * EPPlus is an Open Source project provided under the 
 * GNU General Public License (GPL) as published by the 
 * Free Software Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
 * 
 * EPPlus provides server-side generation of Excel 2007 spreadsheets.
 * See http://www.codeplex.com/EPPlus for details.
 *
 *
 * 
 * The GNU General Public License can be viewed at http://www.opensource.org/licenses/gpl-license.php
 * If you unfamiliar with this license or have questions about it, here is an http://www.gnu.org/licenses/gpl-faq.html
 * 
 * The code for this project may be used and redistributed by any means PROVIDING it is 
 * not sold for profit without the author's written consent, and providing that this notice 
 * and the author's name and all copyright notices remain intact.
 * 
 * All code and executables are provided "as is" with no warranty either express or implied. 
 * The author accepts no liability for any damage or loss of business that this product may cause.
 *
 *
 * Code change notes:
 * 
 * Author							Change						Date
 *******************************************************************************
 * Eyal Seagull				Added							2012-04-03
 *******************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using OfficeOpenXml;
using System.Xml;
using System.Drawing;
using OfficeOpenXml.Style;
using OfficeOpenXml.Style.Dxf;
using OfficeOpenXml.DataValidation;
using OfficeOpenXml.ConditionalFormatting;

namespace EPPlusSamples
{
  class Sample14
  {
    /// <summary>
    /// Sample 14 - Conditional formatting example
    /// </summary>
    public static string RunSample14(DirectoryInfo outputDir)
    {
      FileInfo newFile = new FileInfo(outputDir.FullName + @"\sample14.xlsx");

      if (newFile.Exists)
      {
        newFile.Delete();  // ensures we create a new workbook
        newFile = new FileInfo(outputDir.FullName + @"\sample14.xlsx");
      }

      using (ExcelPackage package = new ExcelPackage(newFile))
      {
        // add a new worksheet to the empty workbook
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Conditional Formatting");

        // Create 4 columns of samples data
        for (int col = 1; col < 10; col++)
        {
          // Add the headers
          worksheet.Cells[1, col].Value = "Sample " + col;

          for (int row = 2; row < 21; row++)
          {
            // Add some items...
            worksheet.Cells[row, col].Value = row;
          }
        }

        // -------------------------------------------------------------------
        // TwoColorScale Conditional Formatting example
        // -------------------------------------------------------------------
        ExcelAddress cfAddress1 = new ExcelAddress("A2:A10");
        var cfRule1 = worksheet.ConditionalFormatting.AddTwoColorScale(cfAddress1);

        // Now, lets change some properties:
        cfRule1.LowValue.Type = eExcelConditionalFormattingValueObjectType.Num;
        cfRule1.LowValue.Value = 4;
        cfRule1.LowValue.Color = ColorTranslator.FromHtml("#FFFFEB84");
        cfRule1.HighValue.Type = eExcelConditionalFormattingValueObjectType.Formula;
        cfRule1.HighValue.Formula = "IF($G$1=\"A</x:&'cfRule>\",1,5)";
        cfRule1.StopIfTrue = true;
        cfRule1.Style.Font.Bold = true;

        // But others you can't (readonly)
        // cfRule1.Type = eExcelConditionalFormattingRuleType.ThreeColorScale;

        // -------------------------------------------------------------------
        // ThreeColorScale Conditional Formatting example
        // -------------------------------------------------------------------
        ExcelAddress cfAddress2 = new ExcelAddress(2, 2, 10, 2);  //="B2:B10"
        var cfRule2 = worksheet.ConditionalFormatting.AddThreeColorScale(cfAddress2);

        // Changing some properties again
        cfRule2.Priority = 1;
        cfRule2.MiddleValue.Type = eExcelConditionalFormattingValueObjectType.Percentile;
        cfRule2.MiddleValue.Value = 30;
        cfRule2.StopIfTrue = true;

        // You can access a rule by its Priority
        var cfRule2Priority = cfRule2.Priority;
        var cfRule2_1 = worksheet.ConditionalFormatting.RulesByPriority(cfRule2Priority);

        // And you can even change the rule's Address
        cfRule2_1.Address = new ExcelAddress("Z1:Z3");

        // -------------------------------------------------------------------
        // Adding another ThreeColorScale in a different way (observe that we are
        // pointing to the same range as the first rule we entered. Excel allows it to
        // happen and group the rules in one <conditionalFormatting> node)
        // -------------------------------------------------------------------
        var cfRule3 = worksheet.Cells[cfAddress1.Address].ConditionalFormatting.AddThreeColorScale();
        cfRule3.LowValue.Color = Color.LemonChiffon;
        
        // -------------------------------------------------------------------
        // Change the rules priorities to change their execution order
        // -------------------------------------------------------------------
        cfRule3.Priority = 1;
        cfRule1.Priority = 2;
        cfRule2.Priority = 3;

        // -------------------------------------------------------------------
        // Create an Above Average rule
        // -------------------------------------------------------------------
        var cfRule5 = worksheet.ConditionalFormatting.AddAboveAverage(
          new ExcelAddress("B11:B20"));
        cfRule5.Style.Font.Bold = true;
        cfRule5.Style.Font.Color.Color = Color.Red;
        cfRule5.Style.Font.Strike = true;

        // -------------------------------------------------------------------
        // Create an Above Or Equal Average rule
        // -------------------------------------------------------------------
        var cfRule6 = worksheet.ConditionalFormatting.AddAboveOrEqualAverage(
          new ExcelAddress("B11:B20"));

        // -------------------------------------------------------------------
        // Create a Below Average rule
        // -------------------------------------------------------------------
        var cfRule7 = worksheet.ConditionalFormatting.AddBelowAverage(
          new ExcelAddress("B11:B20"));

        // -------------------------------------------------------------------
        // Create a Below Or Equal Average rule
        // -------------------------------------------------------------------
        var cfRule8 = worksheet.ConditionalFormatting.AddBelowOrEqualAverage(
          new ExcelAddress("B11:B20"));

        // -------------------------------------------------------------------
        // Create a Above StdDev rule
        // -------------------------------------------------------------------
        var cfRule9 = worksheet.ConditionalFormatting.AddAboveStdDev(
          new ExcelAddress("B11:B20"));
          cfRule9.StdDev = 0;

        // -------------------------------------------------------------------
        // Create a Below StdDev rule
        // -------------------------------------------------------------------
        var cfRule10 = worksheet.ConditionalFormatting.AddBelowStdDev(
          new ExcelAddress("B11:B20"));

        cfRule10.StdDev = 2;

        // -------------------------------------------------------------------
        // Create a Bottom rule
        // -------------------------------------------------------------------
        var cfRule11 = worksheet.ConditionalFormatting.AddBottom(
          new ExcelAddress("B11:B20"));

        cfRule11.Rank = 4;

        // -------------------------------------------------------------------
        // Create a Bottom Percent rule
        // -------------------------------------------------------------------
        var cfRule12 = worksheet.ConditionalFormatting.AddBottomPercent(
          new ExcelAddress("B11:B20"));

        cfRule12.Rank = 15;

        // -------------------------------------------------------------------
        // Create a Top rule
        // -------------------------------------------------------------------
        var cfRule13 = worksheet.ConditionalFormatting.AddTop(
          new ExcelAddress("B11:B20"));

        // -------------------------------------------------------------------
        // Create a Top Percent rule
        // -------------------------------------------------------------------
        var cfRule14 = worksheet.ConditionalFormatting.AddTopPercent(
          new ExcelAddress("B11:B20"));
        
        cfRule14.Style.Border.Left.Style = ExcelBorderStyle.Thin;
        cfRule14.Style.Border.Left.Color.Theme = 3;
        cfRule14.Style.Border.Bottom.Style = ExcelBorderStyle.DashDot;
        cfRule14.Style.Border.Bottom.Color.Index=8;
        cfRule14.Style.Border.Right.Style = ExcelBorderStyle.Thin;
        cfRule14.Style.Border.Right.Color.Color=Color.Blue;
        cfRule14.Style.Border.Top.Style = ExcelBorderStyle.Hair;
        cfRule14.Style.Border.Top.Color.Auto=true;

        // -------------------------------------------------------------------
        // Create a Last 7 Days rule
        // -------------------------------------------------------------------
        ExcelAddress timePeriodAddress = new ExcelAddress("D21:G34 C11:C20");
        var cfRule15 = worksheet.ConditionalFormatting.AddLast7Days(
          timePeriodAddress);

        cfRule15.Style.Fill.PatternType = ExcelFillStyle.LightTrellis;
        cfRule15.Style.Fill.PatternColor.Color = Color.BurlyWood;
        cfRule15.Style.Fill.BackgroundColor.Color = Color.LightCyan;

        // -------------------------------------------------------------------
        // Create a Last Month rule
        // -------------------------------------------------------------------
        var cfRule16 = worksheet.ConditionalFormatting.AddLastMonth(
          timePeriodAddress);

        cfRule16.Style.NumberFormat.Format = "YYYY";
        // -------------------------------------------------------------------
        // Create a Last Week rule
        // -------------------------------------------------------------------
        var cfRule17 = worksheet.ConditionalFormatting.AddLastWeek(
          timePeriodAddress);
        cfRule17.Style.NumberFormat.Format = "YYYY";

        // -------------------------------------------------------------------
        // Create a Next Month rule
        // -------------------------------------------------------------------
        var cfRule18 = worksheet.ConditionalFormatting.AddNextMonth(
          timePeriodAddress);

        // -------------------------------------------------------------------
        // Create a Next Week rule
        // -------------------------------------------------------------------
        var cfRule19 = worksheet.ConditionalFormatting.AddNextWeek(
          timePeriodAddress);

        // -------------------------------------------------------------------
        // Create a This Month rule
        // -------------------------------------------------------------------
        var cfRule20 = worksheet.ConditionalFormatting.AddThisMonth(
          timePeriodAddress);

        // -------------------------------------------------------------------
        // Create a This Week rule
        // -------------------------------------------------------------------
        var cfRule21 = worksheet.ConditionalFormatting.AddThisWeek(
          timePeriodAddress);

        // -------------------------------------------------------------------
        // Create a Today rule
        // -------------------------------------------------------------------
        var cfRule22 = worksheet.ConditionalFormatting.AddToday(
          timePeriodAddress);

        // -------------------------------------------------------------------
        // Create a Tomorrow rule
        // -------------------------------------------------------------------
        var cfRule23 = worksheet.ConditionalFormatting.AddTomorrow(
          timePeriodAddress);

        // -------------------------------------------------------------------
        // Create a Yesterday rule
        // -------------------------------------------------------------------
        var cfRule24 = worksheet.ConditionalFormatting.AddYesterday(
          timePeriodAddress);

        // -------------------------------------------------------------------
        // Create a BeginsWith rule
        // -------------------------------------------------------------------
        ExcelAddress cellIsAddress = new ExcelAddress("E11:E20");
        var cfRule25 = worksheet.ConditionalFormatting.AddBeginsWith(
          cellIsAddress);

        cfRule25.Text = "SearchMe";

        // -------------------------------------------------------------------
        // Create a Between rule
        // -------------------------------------------------------------------
        var cfRule26 = worksheet.ConditionalFormatting.AddBetween(
          cellIsAddress);

        cfRule26.Formula = "IF(E11>5,10,20)";
        cfRule26.Formula2 = "IF(E11>5,30,50)";

        // -------------------------------------------------------------------
        // Create a ContainsBlanks rule
        // -------------------------------------------------------------------
        var cfRule27 = worksheet.ConditionalFormatting.AddContainsBlanks(
          cellIsAddress);

        // -------------------------------------------------------------------
        // Create a ContainsErrors rule
        // -------------------------------------------------------------------
        var cfRule28 = worksheet.ConditionalFormatting.AddContainsErrors(
          cellIsAddress);

        // -------------------------------------------------------------------
        // Create a ContainsText rule
        // -------------------------------------------------------------------
        var cfRule29 = worksheet.ConditionalFormatting.AddContainsText(
          cellIsAddress);

        cfRule29.Text = "Me";

        // -------------------------------------------------------------------
        // Create a DuplicateValues rule
        // -------------------------------------------------------------------
        var cfRule30 = worksheet.ConditionalFormatting.AddDuplicateValues(
          cellIsAddress);

        // -------------------------------------------------------------------
        // Create an EndsWith rule
        // -------------------------------------------------------------------
        var cfRule31 = worksheet.ConditionalFormatting.AddEndsWith(
          cellIsAddress);

        cfRule31.Text = "EndText";

        // -------------------------------------------------------------------
        // Create an Equal rule
        // -------------------------------------------------------------------
        var cfRule32 = worksheet.ConditionalFormatting.AddEqual(
          cellIsAddress);

        cfRule32.Formula = "6";

        // -------------------------------------------------------------------
        // Create an Expression rule
        // -------------------------------------------------------------------
        var cfRule33 = worksheet.ConditionalFormatting.AddExpression(
          cellIsAddress);

        cfRule33.Formula = "E11=E12";

        // -------------------------------------------------------------------
        // Create a GreaterThan rule
        // -------------------------------------------------------------------
        var cfRule34 = worksheet.ConditionalFormatting.AddGreaterThan(
          cellIsAddress);

        cfRule34.Formula = "SE(E11<10,10,65)";

        // -------------------------------------------------------------------
        // Create a GreaterThanOrEqual rule
        // -------------------------------------------------------------------
        var cfRule35 = worksheet.ConditionalFormatting.AddGreaterThanOrEqual(
          cellIsAddress);

        cfRule35.Formula = "35";

        // -------------------------------------------------------------------
        // Create a LessThan rule
        // -------------------------------------------------------------------
        var cfRule36 = worksheet.ConditionalFormatting.AddLessThan(
          cellIsAddress);

        cfRule36.Formula = "36";

        // -------------------------------------------------------------------
        // Create a LessThanOrEqual rule
        // -------------------------------------------------------------------
        var cfRule37 = worksheet.ConditionalFormatting.AddLessThanOrEqual(
          cellIsAddress);

        cfRule37.Formula = "37";

        // -------------------------------------------------------------------
        // Create a NotBetween rule
        // -------------------------------------------------------------------
        var cfRule38 = worksheet.ConditionalFormatting.AddNotBetween(
          cellIsAddress);

        cfRule38.Formula = "333";
        cfRule38.Formula2 = "999";

        // -------------------------------------------------------------------
        // Create a NotContainsBlanks rule
        // -------------------------------------------------------------------
        var cfRule39 = worksheet.ConditionalFormatting.AddNotContainsBlanks(
          cellIsAddress);

        // -------------------------------------------------------------------
        // Create a NotContainsErrors rule
        // -------------------------------------------------------------------
        var cfRule40 = worksheet.ConditionalFormatting.AddNotContainsErrors(
          cellIsAddress);

        // -------------------------------------------------------------------
        // Create a NotContainsText rule
        // -------------------------------------------------------------------
        var cfRule41 = worksheet.ConditionalFormatting.AddNotContainsText(
          cellIsAddress);

        cfRule41.Text = "NotMe";

        // -------------------------------------------------------------------
        // Create an NotEqual rule
        // -------------------------------------------------------------------
        var cfRule42 = worksheet.ConditionalFormatting.AddNotEqual(
          cellIsAddress);

        cfRule42.Formula = "14";

        ExcelAddress cfAddress43 = new ExcelAddress("G2:G10");
        var cfRule43 = worksheet.ConditionalFormatting.AddThreeIconSet(cfAddress43, eExcelconditionalFormatting3IconsSetType.TrafficLights1);

        ExcelAddress cfAddress44 = new ExcelAddress("H2:H10");
        var cfRule44 = worksheet.ConditionalFormatting.AddDatabar(cfAddress44, Color.DarkBlue);
        
          // -----------------------------------------------------------
        // Removing Conditional Formatting rules
        // -----------------------------------------------------------
        // Remove one Rule by its object
        //worksheet.ConditionalFormatting.Remove(cfRule1);

        // Remove one Rule by index
        //worksheet.ConditionalFormatting.RemoveAt(1);

        // Remove one Rule by its Priority
        //worksheet.ConditionalFormatting.RemoveByPriority(2);

        // Remove all the Rules
        //worksheet.ConditionalFormatting.RemoveAll();

        // set some document properties
        package.Workbook.Properties.Title = "Conditional Formatting";
        package.Workbook.Properties.Author = "Eyal Seagull";
        package.Workbook.Properties.Comments = "This sample demonstrates how to add Conditional Formatting to an Excel 2007 worksheet using EPPlus";

        // set some custom property values
        package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Eyal Seagull");
        package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");

        // save our new workbook and we are done!
        package.Save();
      }

      return newFile.FullName;
    }
  }
}