Setting IRange.CellStyleName results in ArgumentOutOfRangeException

[Jonny Shipton]

Hi there :)

When setting the CellStyleName property on an IRange object, I get a ArgumentOutOfRangeException, but only when the style has IncludePatterns set to true, as far as I can tell. I tested it with other styles without pattern content, and they worked.

Example code:

IStyle style = XLBook.IWorkbook.Styles.Add(XLAssistant.TitleStyle);

	style.BeginUpdate();
	style.IncludeNumberFormat = false;
	style.IncludeFont = true;
	style.IncludeAlignment = true;
	style.IncludeBorder = false;
	style.IncludePatterns = false;
	style.IncludeProtection = false;
	style.Font.Size = 20;
	style.Font.Bold = true;
	style.Font.Italic = true;
	style.EndUpdate();
 
	style = XLBook.IWorkbook.Styles.Add(XLAssistant.HeadingStyle);
	style.BeginUpdate();
	style.IncludeNumberFormat = false;
	style.IncludeFont = true;
	style.IncludeAlignment = true;
	style.IncludeBorder = false;
	style.IncludePatterns = true;
	style.IncludeProtection = false;
	style.Interior.ColorIndex = ExcelKnownColors.Custom9;
	style.Interior.FillPattern = ExcelPattern.Solid;
	style.Font.Italic = true;
	style.Font.Bold = true;
	style.Font.Color = ExcelKnownColors.Custom1;
	style.HorizontalAlignment = ExcelHAlign.HAlignCenter;
	style.VerticalAlignment = ExcelVAlign.VAlignBottom;
	style.WrapText = true;
	style.EndUpdate();

...

IRange range = worksheet.Range[2, 1];

// Works

range.CellStyleName = XLAssistant.TitleStyle;

// Throws ArgumentOutOfRangeException

range.CellStyleName = XLAssistant.HeadingStyle;

I am using Syncfusion.XlsIO.Base version 12.4400.0.24 for .NET v4.0.30319.

Thanks for your help,

Jonny

[Jonny Shipton]

After further testing, I have found that using the style.ColorIndex and style.FillPattern properties works. But how am I supposed to change the Interior properties?

Also, I have found that range.CellStyleName is case-sensitive, which it is not in Excel. Is this by design, or is this a bug?

[Dilli Babu Nandha Gopal]

Hi Jonny,

Thank you using Syncfusion products.

Regarding Interior query:

We recommend you to use IStyle.ColorIndex instead of Interior.ColorIndex and IStyle.FillPattern instead of Interior.FillPattern. Could please share us the scenario in which you’re using the Interior property so that we can provide a prompt solution or any other alternative way to achieve it.

Regarding CellStyleName query:

CellStyleName value is a case-sentitive. This is not a bug

Regards,

Dilli babu.

[Jonny Shipton]

Hi Dilli,

Thanks for your response. I just expected Interior.FillPattern to work since it was there, and that is how it is used in VBA. IStyle.FillPattern, etc. is working fine though :)

When I save the excel file, and then try to open it, I get an error message saying "Repaired Records: Format from /xl/styles.xml part (Styles)". Any ideas as to why? :S

I tried creating a file with just the styles in, and that works fine.

Thanks,

Jonny

[Jonny Shipton]

It turned out that a number format was incorrect :L

Thanks :)

[Dilli Babu Nandha Gopal]

Hi Jonny,

Thank you for updating us.

You can use Style.FillPattern and Style.ColorIndex which meets the requirement. Please let us know if you need any further assistance.

Regards,

Dilli babu.

[stephan weyer]

Sorry for request in this thread.

I NEED to set a gradient in cells. So i have to set the property "interior":

            ExcelEngine excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;
            application.DefaultVersion = ExcelVersion.Excel2010;
            IWorkbook workbook = application.Workbooks.Create(2);
            IWorksheet sheet = workbook.Worksheets[0];

            IStyle style = workbook.Styles.Add("style1");
            style.BeginUpdate();
            style.FillPattern = ExcelPattern.Gradient;
            style.Interior.Gradient.GradientStyle = ExcelGradientStyle.Vertical;
            style.Interior.Gradient.BackColor = Color.Black;
            style.Interior.Gradient.ForeColor = Color.Beige;

            for (int i = 1; i < 2000; i++)
            {
                IRange cell = sheet.Range[string.Format("A{0}", i)];
                cell.CellStyleName = "style1";
            }
            style.EndUpdate();
            workbook.SaveAs(@"c:\temp\test.xlsx");

            workbook.Close();
            excelEngine.Dispose();

This code ends in ArgumentOutOfRangeException.
What can i do?

Thanks you for help
Stephan

[Dilli Babu Nandha Gopal]

Hi Stephan,

We were able to reproduce the problem and have logged defect report regarding this. A support incident to track the status of this defect has been created under your account. Please log on to our support website to check for further updates

https://www.syncfusion.com/account/login?ReturnUrl=%2fsupport%2fdirecttrac%2fincidents

Please let me know if you have any questions.

Regards,
Dilli babu.