Hi
I am Facing one Problem -
I have Ten Columns,in each column if any specific value comes then i want to change the style of that cell.
here is the code which i am using for 5 column, but i am able to change to only 3 columns other two columns style is not changeing
It is Applying only for the FAmily, MemberId, Business Column, not for LastName and FirstName.
Please can any one help meout to solve this problem.
Thanks and regards
Mohammed Irfan
Code :
CellArea CAForW1 = new CellArea();
CAForW1.StartRow = 0;
CAForW1.EndRow = 65535;
CAForW1.StartColumn = 0;
CAForW1.EndColumn = 10;
fcs.AddArea(CAForW1);
int conditionFamily = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, “Family”, “Family”);
int conditionMemberID = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, “Memberid”, “Memberid”);
int conditionBusiness = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, “Business”, “Business”);
int conditionLastName = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, “LastName”, “LastName”);
int conditionFirstName = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, “FirstName”, “FirstName”);
//Sets the background color.
FormatCondition fcFamily = fcs[conditionFamily];
fcFamily.Style.Font.IsBold = true;
fcFamily.Style.BackgroundColor = System.Drawing.Color.Silver;
FormatCondition fcMemberID = fcs[conditionMemberID];
fcMemberID.Style.Font.IsBold = true;
fcMemberID.Style.BackgroundColor = System.Drawing.Color.Silver;
FormatCondition fcBusiness = fcs[conditionBusiness];
fcBusiness.Style.Font.IsBold = true;
fcBusiness.Style.BackgroundColor = System.Drawing.Color.Silver;
FormatCondition fcLastName = fcs[conditionLastName];
fcLastName.Style.Font.IsBold = true;
fcLastName.Style.BackgroundColor = System.Drawing.Color.Silver;
FormatCondition fcFirstName = fcs[conditionFirstName];
fcFirstName.Style.Font.IsBold = true;
fcFirstName.Style.BackgroundColor = System.Drawing.Color.Silver;
Hi Irfan,
Thank you for considering Aspose.
Well, you have to use a separate conditional formatting for all the columns to get your desired result. Please see the sample code as under:
//Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
//Adds an empty conditional formatting
int index = sheet.ConditionalFormattings.Add();
FormatConditions fcs = sheet.ConditionalFormattings[index];
index = sheet.ConditionalFormattings.Add();
FormatConditions fcs2 = sheet.ConditionalFormattings[index];
index = sheet.ConditionalFormattings.Add();
FormatConditions fcs3 = sheet.ConditionalFormattings[index];
index = sheet.ConditionalFormattings.Add();
FormatConditions fcs4 = sheet.ConditionalFormattings[index];
index = sheet.ConditionalFormattings.Add();
FormatConditions fcs5= sheet.ConditionalFormattings[index];
CellArea CAForW1 = new CellArea();
CAForW1.StartRow = 0;
CAForW1.EndRow = 65535;
CAForW1.StartColumn = 0;
CAForW1.EndColumn = 0;
fcs.AddArea(CAForW1);
CAForW1.StartColumn = 1;
CAForW1.EndColumn = 1;
fcs2.AddArea(CAForW1);
CAForW1.StartColumn = 2;
CAForW1.EndColumn = 2;
fcs3.AddArea(CAForW1);
CAForW1.StartColumn = 3;
CAForW1.EndColumn = 3;
fcs4.AddArea(CAForW1);
CAForW1.StartColumn = 4;
CAForW1.EndColumn = 4;
fcs5.AddArea(CAForW1);
int conditionFamily = fcs.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "Family", "Family");
int conditionMemberID = fcs2.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "Memberid", "Memberid");
int conditionBusiness = fcs3.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "Business", "Business");
int conditionLastName = fcs4.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "LastName", "LastName");
int conditionFirstName = fcs5.AddCondition(FormatConditionType.CellValue, OperatorType.Between, "FirstName", "FirstName");
Style style = workbook.Styles[workbook.Styles.Add()];
style.Font.IsBold = true;
style.BackgroundColor = System.Drawing.Color.Silver;
//Sets the background color.
FormatCondition fcFamily = fcs[conditionFamily];
fcFamily.Style = style;
FormatCondition fcMemberID = fcs2[conditionMemberID];
fcMemberID.Style = style;
FormatCondition fcBusiness = fcs3[conditionBusiness];
fcBusiness.Style = style;
FormatCondition fcLastName = fcs4[conditionLastName];
fcLastName.Style = style;
FormatCondition fcFirstName = fcs5[conditionFirstName];
fcFirstName.Style = style;
//Saving the Excel file
workbook.Save("c:\\excels\\Formatting.xls", FileFormatType.Default);
Thank You & Best Regards,
Hi.
I used the same format condition in my report.
I see the report with Office 2003 but I wrote one format condition type.
If, I see the report with Office 2010 I worked fine.
I need you to work with both office.
Can you help me?
Thank you.
Hi,
I am not sure about your issue. We recommend you to try our latest version/fix i.e. v7.0.3.6.
Aspose.Cells for .NET (Latest Version)
If you still find the issue, kindly give us your sample code and template file(s) to reproduce the issue on our end, we will check it soon.
Also, we recommend you to check the topic:
Note: If you are using Advanced conditional formatting, then you should save your Excel file to XLSX (Excel 2007/2010 formats). The reason is simple, Excel 2003 does not support advanced conditional formatting, so your formatting will be implemented in Excel 2007/2010 for XLSX, XLSM formats etc.
Thank you.
Hi.
I used the version 7.0.3 of Aspose.Cells.
In attachment, my code and my template.
If, you open the report with Office 2010 I worked fine.
If, you open the report with Office 2003, the apply only one format condition type…
As you say probably is because Office 2003 does not use the advanced format condition type.
How you can create the format condition for worked in both Office?
Thank you.
Hi,
I checked your code a bit, but it is more like a generic code that depends on some external data source. I cannot execute your code properly. Please simplify your code and create a sample runnable console application or a web project, zip it and post it here to reproduce the issue, we will check it soon. You may use Access database or even use DataTable to fill some dummy data to be imported/filled into the cells. Also, make sure that you use our latest fixed version v7.0.3.6.
Also, give us screen shots to mention where is the issue, you may take the screen shots for the generated file (while opening it into MS Excel 2003 and 2010).
Also, I can see a minor issue with your last line of code:
wb.Save(HttpContext.Current.Response, fileName + ".xlsx", ContentDisposition.Attachment, new XlsSaveOptions(SaveFormat.Xlsx));
It should be like following if you are saving to XLSX format:
wb.Save(HttpContext.Current.Response, fileName + ".xlsx", ContentDisposition.Attachment, new OoxmlSaveOptions());
Thank you.
Hi.
I change the format condition and I used the following type:
//Format condition
int index1 = ws.ConditionalFormattings.Add();
FormatConditionCollection fcs1 = ws.ConditionalFormattings[index1];
fcs1.AddArea(areacell);
int conditionIndex1 = fcs1.AddCondition(FormatConditionType.NotContainsBlanks);
FormatCondition fc2 = fcs1[conditionIndex1];
fc2.Style.BackgroundColor = dict[5];
fc2.Style.Pattern = BackgroundType.Solid;
Now, the format condition type it worked fine in both Office 2003/2010.
Only with Office 2003, when I opened the report I see the warning message not blocked (in attachment)…why? Can be avoided?
Hi,
I think the reason might be due to the fact that “FormatConditionType.NotContainsBlanks” looks like an Excel 2007/2010 advanced conditional formatting type, so, might be getting this error message in Excel 2003. Could you translate the MS Excel’s error message into English, so that could better understand it.
Thank you.
Hi,
It’s ok.
Thank you