This project has moved. For the latest updates, please go here.

Existing Spreadsheet with Conditional Formatting - Error

Oct 3, 2012 at 11:33 PM
Edited Oct 8, 2012 at 4:20 PM

RESOLVED:  This problem was fixed in F23162df86ce changeset.  Source was released for testing 10/07/2012.  --DaveH

I have an existing spreadsheet that was converted from 2003 xls to 2007 xlsx.  The spreadsheet is happy when I open and save using excel 2007.  The spreadsheet has extensive pre-existing conditional formatting used.

When the "xlPackage.Save()" executes in the code below, I get the error below.

Any ideas? Is the conversion from 2003 the problem?

See also: http://epplus.codeplex.com/workitem/14749

Here is the code to load and save the spreadsheet:

string newFileName = @"C:\TEMP\ProductCofC.xlsx";
FileInfo fileInfo = new FileInfo(@"CofCTemplate.xlsx");
fileInfo.CopyTo(newFileName);
fileInfo = null;
fileInfo = new FileInfo(newFileName);

ExcelPackage xlPackage = new ExcelPackage(fileInfo);

xlPackage.Save();


The error...
System.InvalidOperationException was unhandled
  Message=Error saving file C:\TEMP\ProductCofC_20123003_173025.xlsx
  Source=EPPlus
  StackTrace:
       at OfficeOpenXml.ExcelPackage.Save() in C:\Users\dherron\Downloads\EPPlus-7afb9506d6e0\epplus_7afb9506d6e0\EPPlus\ExcelPackage.cs:line 761
       at App.ShipDocs.ProductCofC_EPP.CreateDocument() in U:\dherron\Projects\csharp\ExcelTestProject\clsProductCofC_EPP.cs:line 36
       at ExcelTestProject.frmMain.button1_Click_1(Object sender, EventArgs e) in U:\dherron\Projects\csharp\ExcelTestProject\frmMain.cs:line 72
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at ExcelTestProject.Program.Main() in U:\dherron\Projects\csharp\ExcelTestProject\Program.cs:line 18
  InnerException:
       Message=Unexpected eExcelConditionalFormattingRuleType attribute in Conditional Formatting Rule
       Source=EPPlus
       StackTrace:
            at OfficeOpenXml.ConditionalFormatting.ExcelConditionalFormattingRuleType.GetTypeByAttrbiute(String attribute, XmlNode topNode, XmlNamespaceManager nameSpaceManager) in C:\Users\dherron\Downloads\EPPlus-7afb9506d6e0\epplus_7afb9506d6e0\EPPlus\ConditionalFormatting\ExcelConditionalFormattingRuleType.cs:line 140
            at OfficeOpenXml.ConditionalFormatting.ExcelConditionalFormattingCollection..ctor(ExcelWorksheet worksheet) in C:\Users\dherron\Downloads\EPPlus-7afb9506d6e0\epplus_7afb9506d6e0\EPPlus\ConditionalFormatting\ExcelConditionalFormattingCollection.cs:line 153
            at OfficeOpenXml.ExcelWorksheet.get_ConditionalFormatting() in C:\Users\dherron\Downloads\EPPlus-7afb9506d6e0\epplus_7afb9506d6e0\EPPlus\ExcelWorksheet.cs:line 2722
            at OfficeOpenXml.ExcelStyles.UpdateXml() in C:\Users\dherron\Downloads\EPPlus-7afb9506d6e0\epplus_7afb9506d6e0\EPPlus\ExcelStyles.cs:line 609
            at OfficeOpenXml.ExcelWorkbook.Save() in C:\Users\dherron\Downloads\EPPlus-7afb9506d6e0\epplus_7afb9506d6e0\EPPlus\ExcelWorkbook.cs:line 649
            at OfficeOpenXml.ExcelPackage.Save() in C:\Users\dherron\Downloads\EPPlus-7afb9506d6e0\epplus_7afb9506d6e0\EPPlus\ExcelPackage.cs:line 710
       InnerException:


Oct 4, 2012 at 2:18 PM
Edited Oct 4, 2012 at 3:25 PM

From my investigation into the source code, the "CellIs" conditional formatting type has not been fully implemented.

 

OuterXml = "<cfRule type=\"cellIs\" dxfId=\"493\" priority=\"91\" stopIfTrue=\"1\" operator=\"greaterThan\" xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><formula>$G$24</formula></cfRule>"

Oct 4, 2012 at 3:23 PM

This is the function call where there is no support for the ruletype "cellIs".

internal static class ExcelConditionalFormattingRuleType
	{
        /// <summary>
        ///
        /// </summary>
        /// <param name="attribute"></param>
        /// <param name="topNode"></param>
        /// <param name="nameSpaceManager"></param>
        /// <returns></returns> internal static eExcelConditionalFormattingRuleType GetTypeByAttrbiute( string attribute, XmlNode topNode, XmlNamespaceManager nameSpaceManager) { switch (attribute) { case ExcelConditionalFormattingConstants.RuleType.AboveAverage: return GetAboveAverageType( topNode, nameSpaceManager); case ExcelConditionalFormattingConstants.RuleType.Top10: return GetTop10Type( topNode, nameSpaceManager); case ExcelConditionalFormattingConstants.RuleType.TimePeriod: return GetTimePeriodType( topNode, nameSpaceManager); case ExcelConditionalFormattingConstants.RuleType.BeginsWith: return eExcelConditionalFormattingRuleType.BeginsWith; case ExcelConditionalFormattingConstants.RuleType.Between: return eExcelConditionalFormattingRuleType.Between; case ExcelConditionalFormattingConstants.RuleType.ContainsBlanks: return eExcelConditionalFormattingRuleType.ContainsBlanks; case ExcelConditionalFormattingConstants.RuleType.ContainsErrors: return eExcelConditionalFormattingRuleType.ContainsErrors; case ExcelConditionalFormattingConstants.RuleType.ContainsText: return eExcelConditionalFormattingRuleType.ContainsText; case ExcelConditionalFormattingConstants.RuleType.DuplicateValues: return eExcelConditionalFormattingRuleType.DuplicateValues; case ExcelConditionalFormattingConstants.RuleType.EndsWith: return eExcelConditionalFormattingRuleType.EndsWith; case ExcelConditionalFormattingConstants.RuleType.Equal: return eExcelConditionalFormattingRuleType.Equal; case ExcelConditionalFormattingConstants.RuleType.Expression: return eExcelConditionalFormattingRuleType.Expression; case ExcelConditionalFormattingConstants.RuleType.GreaterThan: return eExcelConditionalFormattingRuleType.GreaterThan; case ExcelConditionalFormattingConstants.RuleType.GreaterThanOrEqual: return eExcelConditionalFormattingRuleType.GreaterThanOrEqual; case ExcelConditionalFormattingConstants.RuleType.LessThan: return eExcelConditionalFormattingRuleType.LessThan; case ExcelConditionalFormattingConstants.RuleType.LessThanOrEqual: return eExcelConditionalFormattingRuleType.LessThanOrEqual; case ExcelConditionalFormattingConstants.RuleType.NotBetween: return eExcelConditionalFormattingRuleType.NotBetween; case ExcelConditionalFormattingConstants.RuleType.NotContainsBlanks: return eExcelConditionalFormattingRuleType.NotContainsBlanks; case ExcelConditionalFormattingConstants.RuleType.NotContainsErrors: return eExcelConditionalFormattingRuleType.NotContainsErrors; case ExcelConditionalFormattingConstants.RuleType.NotContainsText: return eExcelConditionalFormattingRuleType.NotContainsText; case ExcelConditionalFormattingConstants.RuleType.NotEqual: return eExcelConditionalFormattingRuleType.NotEqual; case ExcelConditionalFormattingConstants.RuleType.UniqueValues: return eExcelConditionalFormattingRuleType.UniqueValues; case ExcelConditionalFormattingConstants.RuleType.ColorScale: return GetColorScaleType( topNode, nameSpaceManager); case ExcelConditionalFormattingConstants.RuleType.IconSet: return GetIconSetType(topNode, nameSpaceManager); case ExcelConditionalFormattingConstants.RuleType.DataBar: return eExcelConditionalFormattingRuleType.DataBar; } throw new Exception( ExcelConditionalFormattingConstants.Errors.UnexpectedRuleTypeAttribute); }

Coordinator
Oct 4, 2012 at 5:21 PM

Ok, thanks we'll have a look at it.