We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Conditional formatting not kept when loading excel file

Hi,


I’ve attached a source xlsx document(Book1.xlsx). If you run the following code sequence, you will see that the resulting document(res.xlsx) has the same number of conditional formatting rules, but only 1 color is kept, instead of 3.

var xlsDoc = new Workbook(“C:\Book1.xlsx”);
var ms = new MemoryStream();
xlsDoc.Save(ms, Aspose.Cells.SaveFormat.Xlsx);
var bytes = ms.ToArray();
var ims = new MemoryStream(bytes);
var wb = new Workbook(ims);
wb.Save(“C:\res.xlsx”, Aspose.Cells.SaveFormat.Xlsx);

internalId - 27973

Thank you,
George Radu
Software Developer
IBM Romania

Hi,

Thanks for your posting and using Aspose.Cells.

We have tested this issue with your sample code and source excel file using the latest version: Aspose.Cells
for .NET v8.5.0.4
and it worked fine.

I have attached the output excel file generated with the latest version for your reference.

Hi Shakeel,

We tested this issue with Aspose Cells 8.5.1 and the issue is still reproducible.

Hi,

Thanks for your feedback and using Aspose.Cells.

Please download and try the latest version: Aspose.Cells
for .NET v8.5.1.2
it works fine.

I have attached the resulting output excel file for your reference. I have also attached the screenshot showing all the 3 conditional formattings which means the output excel file is good.

Hello,

The problem is now that the font is losing its proprieties
like font size and color. I have found another way to isolate this
problem. Here is the code used to reproduce it, along with the attached
file:

var workbook = new Workbook(“testFile.xlsx”);

var excelCell1 = workbook.Worksheets[0].Cells[“A1”];
var excelCell2 = workbook.Worksheets[0].Cells[“A2”];
var conditionalFormattingResult1 = excelCell1.GetConditionalFormattingResult();
var conditionalFormattingResult2 = excelCell2.GetConditionalFormattingResult();

if (conditionalFormattingResult1 == null || conditionalFormattingResult2 == null) return;
var conditionalStyle1 = conditionalFormattingResult1.ConditionalStyle;
var conditionalStyle2 = conditionalFormattingResult2.ConditionalStyle;

Console.WriteLine(“A1 normal cell: " + excelCell1.HtmlString);
Console.WriteLine(”\nA1 conditional formatting: " + conditionalStyle1.Font);
Console.WriteLine("\n\nA2 normal cell: " + excelCell2.HtmlString);
Console.WriteLine("\nA2 conditional formatting: " + conditionalStyle2.Font);

InternalID: 27973

Thank you,
Stanescu Jean-Alexandru
Junior Developer
IBM Romania

Hi,


Thanks for the template file and sample code:

After an initial test, I observed the issue as you mentioned. I found that conditionally formatted style for font attributes of the A1 and A2 cells (e.g Font size, color etc.) are not retrieved fine. I used the following sample code with his template file:
e.g
Sample code:

var workbook = new Workbook(“testFile.xlsx”);

var excelCell1 = workbook.Worksheets[0].Cells[“A1”];
var excelCell2 = workbook.Worksheets[0].Cells[“A2”];
var conditionalFormattingResult1 = excelCell1.GetConditionalFormattingResult();
var conditionalFormattingResult2 = excelCell2.GetConditionalFormattingResult();

if (conditionalFormattingResult1 == null || conditionalFormattingResult2 == null) return;
var conditionalStyle1 = conditionalFormattingResult1.ConditionalStyle;
var conditionalStyle2 = conditionalFormattingResult2.ConditionalStyle;

Console.WriteLine(“A1 normal cell: " + excelCell1.HtmlString);
Console.WriteLine(”\nA1 conditional formatting: " + conditionalStyle1.Font); //Not Ok
Console.WriteLine("\n\nA2 normal cell: " + excelCell2.HtmlString);
Console.WriteLine("\nA2 conditional formatting: " + conditionalStyle2.Font);//Not Ok

I have logged a ticket with an id “CELLSNET-43872” for your issue. We will look into it soon.
Once we have any update on it, we will let you know here.

Thank you.

Hi,

Thanks for using Aspose.Cells.

If you want to check the setting of conditional formatting, please check the property is set with Style.IsModified() method. For example, please see the following code.

C#


if(conditionalStyle1.IsModified(StyleModifyFlag.Font))

{

if(conditionalStyle1.IsModified(StyleModifyFlag.FontSize))

{

Console.WriteLine(“Font Size :” + conditionalStyle1.Font.DoubleSize);

}

if (conditionalStyle1.IsModified(StyleModifyFlag.FontColor))

{

Console.WriteLine(“Font color :” + conditionalStyle1.Font.Color);

}

}

Hello,

I have understood what the problem was. That font displayed is the default one for conditional formatting, in case the rule passed and the font needs to be modified. In this case, we do not modify the font so we want it to stay as it was before. I can check this using Style.IsModified as you mentioned.

Thank you!

Stanescu Jean-Alexandru
Junior Developer
IBM Romania

Hi,


Thanks for your feedback.

Good to know that the suggested method works for your needs. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.