CODEWITHSUNDEEP
c#openxmlconditional-formattingclosedxmloffice-2016
user581453
user581453

Reputation: 69

ClosedXML Add WorkSheet with Conditional Formatting

I am using ClosedXML to add new worksheet to existing Excel document. It works fine for normal Excel document.

But if a excel document sheet contains conditional formatting on some cell then it throws error

at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at ClosedXML.Excel.XLCFConverters.Convert(IXLConditionalFormat conditionalFormat, Int32 priority, SaveContext context)
   at ClosedXML.Excel.XLWorkbook.GenerateWorksheetPartContent(WorksheetPart worksheetPart, XLWorksheet xlWorksheet, SaveContext context)
   at ClosedXML.Excel.XLWorkbook.CreateParts(SpreadsheetDocument document)
   at ClosedXML.Excel.XLWorkbook.CreatePackage(String filePath, SpreadsheetDocumentType spreadsheetDocumentType)
   at ClosedXML.Excel.XLWorkbook.SaveAs(String file)

Below is the sample code

using (var excelDoc = new ClosedXML.Excel.XLWorkbook(strFilePath))
{
    excelDoc.Worksheets.Add("New Result Sheet");                    
    excelDoc.SaveAs(strFilePathSave);
}

Please help how to fix this issue.

Upvotes: 3

Views: 2764

Answers (1)

Dillibabu Nandhagopal
Dillibabu Nandhagopal

Reputation: 185

XlsIO is a .NET library that reads and writes Excel 2003/2007/2010/2013/2016 files. Using XlsIO, you can add/modify a worksheet with conditional formatting very easily without issues. The whole suite of controls is available for free (commercial applications also) through the community license program if you qualify. The community license is the full product with no limitations or watermarks.

Step 1: Create a console application

Step 2: Add reference to Syncfusion.XlsIO.Base and Syncfusion.Compression.Base, you can add these reference to your project using NuGet also.

Step 3: Copy & paste the following code snippet.

The following code snippet illustrates how to add a worksheet with conditional formatting using XlsIO

using (ExcelEngine excelEngine = new ExcelEngine())
{
    //Instantiate the excel application object.
    IApplication application = excelEngine.Excel;

    application.DefaultVersion = ExcelVersion.Excel2013;

    //Open the workbook
    IWorkbook workbook = application.Workbooks.Open("Input.xlsx");

    (workbook.Worksheets as WorksheetsCollection).Add("NewSheet");

    IWorksheet worksheet = workbook.Worksheets[1];

    IConditionalFormats condition = worksheet.Range["A1"].ConditionalFormats;

    IConditionalFormat condition1 = condition.AddCondition();

    condition1.FormatType = ExcelCFType.CellValue;

    condition1.Operator = ExcelComparisonOperator.Between;

    condition1.FirstFormula = "10";

    condition1.SecondFormula = "20";

    condition1.BackColor = ExcelKnownColors.Red; 

    worksheet.Range["A1"].Number = 13;

    //Save the workbook
    workbook.SaveAs("AddedWorkbook.xlsx");
}

Kindly refer the sample to achieve this scenario and the sample can be downloaded from following location.

Download Demo

For further information about XlsIO, please refer our help documentation

Note: I work for Syncfusion

Upvotes: -2

Related Questions