0

In my MVC project I'm using SpreadsheetGear to generate an excel document. I've got a column that has a validation rule against it that only allows the user to select from the following options: A,B,C,D. I need to be able to set different background colours for each of the options. For example:

  1. A - Green
  2. B - Yellow
  3. C - Orange
  4. D - Red

I've arrived to this error when running the code:

Maximum number of FormatConditions already exists.

My code is as follows:

conditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Equal, "A", null).Interior.Color = Color.LightGreen;
conditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Equal, "B", null).Interior.Color = Color.Yellow;
conditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Equal, "C", null).Interior.Color = Color.Orange;
conditions.Add(FormatConditionType.CellValue, FormatConditionOperator.Equal, "D", null).Interior.Color = Color.Red;

It seems that 3 is the maximum amount of Format conditions you can have for a cell. Is there anyway around this limitation?

Bad Dub
  • 1,503
  • 2
  • 22
  • 52

1 Answers1

1

You must be using an older version of SpreadsheetGear, as SpreadsheetGear 2012 and prior versions only supported Conditional Formatting features that were available in Excel 2003, which among many other limitations included a limit of 3 CF rules per cell.

The release of SpreadsheetGear 2017 enhanced support for Conditional Formats to a level that is compatible with the latest versions of Excel, including specifying more than 3 rules in a cell, so it sounds like you just need to upgrade your application to the latest version. Assuming you have a recent / active subscription, you can download SpreadsheetGear 2017 from the Licensed User Downloads page.

Tim Andersen
  • 3,014
  • 1
  • 15
  • 11
  • Yeah I updated using Nuget a while ago and tested this again and it worked fine. We must have been on a pretty old version still. Thanks for the comment, Ill mark it as the answer as it was the answer! – Bad Dub Jan 07 '19 at 16:38
  • Glad you got this working. Just to clarify--the product on NuGet is "SpreadsheetGear for .NET Standard" which is an entirely different product from "SpreadsheetGear for .NET" that's available for download on the page I provided a link to. The former targets the .NET Standard whereas the latter targets the .NET Framework. The NuGet product requires either a license to that specific product or to the SpreadsheetGear Bundle, although there is a "free" mode that works straight off of NuGet with limits. See https://www.spreadsheetgear.com/nuget/spreadsheetgear/project/ for more info. – Tim Andersen Jan 07 '19 at 18:05
  • Just found out we cant updated to the newer version due to high pricing. I tried using a template with the 4 conditional formats and copying that worksheet. Didnt work as the copy worksheet function applies the same 3 max rule unfortunately. – Bad Dub Jan 08 '19 at 16:18