How would I go about adding theme to a chart? The only information I’ve found regarding chart formatting is changing the ChartArea.Area methods (Background, Foreground, FillFormat, etc). Is it possible to get themes applied to a charts as I do with cells using the ForegroundThemeColor etc etc…?
Hi,
Thanks for your posting and using Aspose.Cells for .NET.
By themes, I think, you meant chart styles as shown in the screenshot below. If you meant something else, then please provide me your source and expected output xlsx files.
Normally, you will have to apply foreground, background, fonts etc yourself and there is no direct way to apply chart styles.
For setting chart’s appearance, please refer to this article for more.
Setting Charts Appearance
Hi,
We have logged a ticket for an enhancement for your requirements with an id: CELLSNET-40829
We will soon look into it and enhance this feature of apply themes to charts. Once we have an update on it, we will let you know here.
I’ll look into the previous post of yours which I think may lead me in the correct direction. Essentially I want the colors, font size, font name to change according to which theme is selected from the Page Layout tab. I’ve been able to manually apply the style to my chart if under the Chart Tools > Design, I select one of those items, much like what your screenshot is indicating. Then if I go under the Page Layout > Themes and mouse over other themes that chart’s appearance reflects that theme’s settings.
Thank you for the quick response.
Hi,
Thank you for understanding the concern.
You are right. For the time being, you can use the work around of Chart appearance as suggested by Shakeel. As soon as we have an update from our development team regarding your requirements, we will let you know here.
If we can be of any additional help to you, please feel free to contact us.
Hi,
Please download and try the latest fix: Aspose.Cells for .NET v7.2.2.7 and let us know your feedback.
Please use the following sample code.
C#
Chart chart = workbook.Worksheets[0].Charts[0];
CellsColor cc = chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor;
cc.ThemeColor = new ThemeColor(ThemeColorType.Accent6, 0.6);
chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor = cc;
Thank you for the prompt response. I updated Aspose.Cells to the version you posted 7.2.2.7, when I tried using the code you supplied, I ran into issues.
Here is the block of code that applies my formatting. I have bolded the part which is bombing out.
chart.Title.Font.Color = Color.Black;
chart.Title.Font.IsBold = true;
chart.Title.Font.Size = 12;
Series series = chart.NSeries[0];
//Testing themeing
CellsColor cc = chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor;
/
cc.ThemeColor = new ThemeColor(ThemeColorType.Accent1, .95);
chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor = cc;
Series series = chart.NSeries[0];
series.Border.IsVisible = false;
/
series.Area.ForegroundColor = Color.FromArgb(171, 197, 228);
series.Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.Solid;
series.Border.IsVisible = false;
series.DataLabels.Font.Size = 10;
chart.NSeries.IsColorVaried = false;
DataLabels dataLabels = series.DataLabels;
dataLabels.Position = LabelPositionType.OutsideEnd;
dataLabels.ShowCategoryName = false;
dataLabels.ShowValue = true;
dataLabels.ShowPercentage = false;
chart.CategoryAxis.AxisLine.IsVisible = false;
chart.CategoryAxis.MajorGridLines.IsVisible = false;
chart.CategoryAxis.TickLabels.Font.Size = 10;
chart.ValueAxis.MinValue = 0;
chart.ValueAxis.MaxValue = maxRange + 5;
chart.ValueAxis.IsVisible = false;
chart.ValueAxis.MinorGridLines.IsVisible = false;
chart.ValueAxis.MajorGridLines.IsVisible = false;
chart.PlotArea.Border.IsVisible = false;
chart.PlotArea.Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.None;
chart.ChartArea.Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.None;
chart.ChartArea.Area.ForegroundColor = Color.White;
chart.ShowLegend = false;
Error message to help
System.NullReferenceException: Object reference not set to an instance of an object.
I checked in the debugger, it is apparently saying the SolidFill portion of FillFormat is null, thus it cannot grab the CellsColor. Do I need to do some kind of initilization to FillFormat beforehand?
Hi,
Thanks for your feedback.
It seems like SolidFill constructor is private so that’s why you are getting the Null Reference exception.
I was able to reproduce your mentioned exception with the following sample code and the source xlsx file attached by me.
We will fix this bug asap and update you.
I have attached the screenshot for a reference.
C#
string filePath = @“F:\Shak-Data-RW\Downloads\source.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.Worksheets[0];
Chart chart = worksheet.Charts[0];
CellsColor cc = chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor;
cc.ThemeColor = new ThemeColor(ThemeColorType.Accent6, 0.6);
chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor = cc;
workbook.Save(filePath + “.out.xlsx”);
Screenshot:
Great! Thank you.
Hi,
Thanks for your patience.
Please set the FillFomart.Type before calling FillFormat.SolidFill.
Please see the following code.
C#
string filePath = @“D:\Filetemp\source.xlsx”;
Workbook workbook = new Workbook(filePath);
Worksheet worksheet = workbook.Worksheets[0];
Chart chart = worksheet.Charts[0];
chart.NSeries[0].Area.FillFormat.Type = FillType.Solid;
CellsColor cc = chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor;
cc.ThemeColor = new ThemeColor(ThemeColorType.Accent6, 0.6);
chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor = cc;
workbook.Save(filePath + “.out.xlsx”);
The issues you have found earlier (filed as CELLSNET-40829) have been fixed in this update.
This message was posted using Notification2Forum from Downloads module by aspose.notifier.
Applying the Theme color correctly colors the chart now. However, it appears these colors are static and do not dynamically change with Theme changes in Excel. If I go to Page Layout > Themes in Excel and change to various different Themes, the chart is no different. The rows I have in alternating color to the theme, those are changing according to which theme I hover over. Now, If I manually select the chart then under Chart Tools > Design > Chart Styles, clicking on one of these then going and selecting different themes does provide intended behavior.
Hi,
Thanks for your feedback.
We will look into this issue and update you asap.
Pleas also provide the following things that will help us quickly look into your issue.
1 - Sample project replicating the issue.
2 - Actual and expected output xls/xlsx files, you can create these manually.
3 - Screenshot highlighting your problems with red circles.
Hi Philip,
Thank you for the feedback.
philip.betts:
Applying the Theme color correctly colors the chart now. However, it appears these colors are static and do not dynamically change with Theme changes in Excel. If I go to Page Layout > Themes in Excel and change to various different Themes, the chart is no different.
I have tried to reproduce this issue at my end with the lines of code shared by Shakeel in the earlier post, but couldn't get success. If I go to PageLayout >> Themes, I can visualize the effect of hovering over different themes on the Chart. Please have a look at the attached file.
If the issue still persists, please help us investigate the issue by providing your Excel file and, if possible, some screen shots to identify the issue.
Hi Philip,Thank you for the feedback.Applying the Theme color correctly colors the chart now. However, it appears these colors are static and do not dynamically change with Theme changes in Excel. If I go to Page Layout > Themes in Excel and change to various different Themes, the chart is no different.I have tried to reproduce this issue at my end with the lines of code shared by Shakeel in the earlier post, but couldn't get success. If I go to PageLayout >> Themes, I can visualize the effect of hovering over different themes on the Chart. Please have a look at the attached file.If the issue still persists, please help us investigate the issue by providing your Excel file and, if possible, some screen shots to identify the issue.
I was able to find the issue with my chart themes after looking at your reply. The file you are exporting is an xlsx, the file I have been generating is xls. Here is the test case I generated:
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
cells["A1"].PutValue("High");
cells["B1"].PutValue("Medium");
cells["C1"].PutValue("Low");
cells["D1"].PutValue("Informational");
cells["E1"].PutValue("New");
cells["F1"].PutValue("Reopened");
cells["G1"].PutValue("False Positive");
cells["H1"].PutValue("Fixed");
cells["A2"].PutValue(17);
cells["B2"].PutValue(9);
cells["C2"].PutValue(41);
cells["D2"].PutValue(6);
cells["E2"].PutValue(7);
cells["F2"].PutValue(31);
cells["G2"].PutValue(11);
cells["H2"].PutValue(88);
int chartIndex = sheet.Charts.AddFloatingChart(Aspose.Cells.Charts.ChartType.Bar, 5, 150, 500, 300);
Aspose.Cells.Charts.Chart chart = sheet.Charts[chartIndex];
chart.Title.Text = "test";
string nSeriesData = "A2:H2";
string categoryFieldData = "A1:H1";
chart.NSeries.Add(nSeriesData, false);
chart.NSeries.CategoryData = categoryFieldData;
for (int i = 0; i < chart.NSeries.Count; i++)
{
if (cells[0, i + 1].Value == null)
{
chart.NSeries[i].Name = "";
}
else
{
chart.NSeries[i].Name = cells[0, i + 1].Value.ToString();
}
}
//Format Chart
chart.Title.Font.Color = Color.Black;
chart.Title.Font.IsBold = true;
chart.Title.Font.Size = 12;
Aspose.Cells.Charts.Series series = chart.NSeries[0];
//Testing themeing
chart.NSeries[0].Area.FillFormat.Type = FillType.Solid;
CellsColor cc = chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor;
cc.ThemeColor = new ThemeColor(ThemeColorType.Accent1, 0.15);
chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor = cc;
/*
series.Area.ForegroundColor = Color.FromArgb(171, 197, 228);
series.Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.Solid;
*/
series.Border.IsVisible = false;
series.DataLabels.Font.Size = 10;
//chart.NSeries.IsColorVaried = false;
DataLabels dataLabels = series.DataLabels;
dataLabels.Position = LabelPositionType.OutsideEnd;
dataLabels.ShowCategoryName = false;
dataLabels.ShowValue = true;
dataLabels.ShowPercentage = false;
chart.CategoryAxis.AxisLine.IsVisible = false;
chart.CategoryAxis.MajorGridLines.IsVisible = false;
chart.CategoryAxis.TickLabels.Font.Size = 10;
chart.ValueAxis.MinValue = 0;
chart.ValueAxis.MaxValue = 110;
chart.ValueAxis.IsVisible = false;
chart.ValueAxis.MinorGridLines.IsVisible = false;
chart.ValueAxis.MajorGridLines.IsVisible = false;
chart.PlotArea.Border.IsVisible = false;
//chart.PlotArea.Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.None;
//chart.ChartArea.Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.None;
//chart.ChartArea.Area.ForegroundColor = Color.White;
chart.ShowLegend = false;
MemoryStream ms = new MemoryStream();
workbook.Save(ms, SaveFormat.Xlsx);
byte[] downloadBytes = ms.ToArray();
string title = "test";
string _format = "xlsx";
HttpContext.Current.Response.AddHeader("Content-Type", "application/"+_format);
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + title + "." + _format);
HttpContext.Current.Response.BinaryWrite(downloadBytes);
HttpContext.Current.Response.End();
[Note: I am using the MemoryStream method of saving specifically for my product. This can be changed if needed on your end for testing]
If I were to change the bolded lines to xls format instead of xlsx, the themeing is not applied in this case. I'm assuming to get these rich formatting of themes applied to charts, the files must be generated in the xlsx format, is this correct?
Hi Philip,
Thank you for the detailed feedback.
After an initial test with your supporting code, I can observe the issue as you have mentioned. The hovering reflects the effects dynamically in xlsx file, but in xls, it doesn’t change until you go to Design>>Chart Styles and change its style atleast once.
We will look into this and assist you further as soon as possible.
Hi,
"If I were to change the bolded lines to xls format instead of xlsx, the themeing is not applied in this case. I’m assuming to get these rich formatting of themes applied to charts, the files must be generated in the xlsx format, is this correct?"
Yes, this is the limitation of XLS file format (Excel 97 - 2003). If you need to use or apply themes you have to use XLSX or other Excel 2007/2010 file formats as Themes/Colors are the advanced features of MS Excel 2007/2010.
Thank you.
Hi,"If I were to change the bolded lines to xls format instead of xlsx, the themeing is not applied in this case. I'm assuming to get these rich formatting of themes applied to charts, the files must be generated in the xlsx format, is this correct?"Yes, this is the limitation of XLS file format (Excel 97 - 2003). If you need to use or apply themes you have to use XLSX or other Excel 2007/2010 file formats as Themes/Colors are the advanced features of MS Excel 2007/2010.Thank you.
Why is it then that rows exported in the XLS format will still have themeing information tied to them?
Example:
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
cells["A1"].PutValue("High");
cells["B1"].PutValue("Medium");
cells["C1"].PutValue("Low");
cells["D1"].PutValue("Informational");
cells["E1"].PutValue("New");
cells["F1"].PutValue("Reopened");
cells["G1"].PutValue("False Positive");
cells["H1"].PutValue("Fixed");
cells["A2"].PutValue(17);
cells["B2"].PutValue(9);
cells["C2"].PutValue(41);
cells["D2"].PutValue(6);
cells["E2"].PutValue(7);
cells["F2"].PutValue(31);
cells["G2"].PutValue(11);
cells["H2"].PutValue(88);
RowCollection rows = cells.Rows;
Row cellRow = rows[0];
Aspose.Cells.Style s = cellRow.Style;
s.ForegroundThemeColor = new ThemeColor(ThemeColorType.Accent1, 0.75);
s.Pattern = BackgroundType.Solid;
StyleFlag sf = new StyleFlag();
sf.All = true;
cellRow.ApplyStyle(s, sf);
rows = cells.Rows;
cellRow = rows[1];
s = cellRow.Style;
s.ForegroundThemeColor = new ThemeColor(ThemeColorType.Accent1, 0.95);
s.Pattern = BackgroundType.Solid;
sf = new StyleFlag();
sf.All = true;
cellRow.ApplyStyle(s, sf);
int chartIndex = sheet.Charts.AddFloatingChart(Aspose.Cells.Charts.ChartType.Bar, 5, 150, 500, 300);
Aspose.Cells.Charts.Chart chart = sheet.Charts[chartIndex];
chart.Title.Text = "test";
string nSeriesData = "A2:H2";
string categoryFieldData = "A1:H1";
chart.NSeries.Add(nSeriesData, false);
chart.NSeries.CategoryData = categoryFieldData;
for (int i = 0; i < chart.NSeries.Count; i++)
{
if (cells[0, i + 1].Value == null)
{
chart.NSeries[i].Name = "";
}
else
{
chart.NSeries[i].Name = cells[0, i + 1].Value.ToString();
}
}
//Format Chart
chart.Title.Font.Color = Color.Black;
chart.Title.Font.IsBold = true;
chart.Title.Font.Size = 12;
Aspose.Cells.Charts.Series series = chart.NSeries[0];
//Testing themeing
chart.NSeries[0].Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.Solid;
CellsColor cc = chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor;
cc.ThemeColor = new ThemeColor(ThemeColorType.Accent1, 0.15);
chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor = cc;
/*
series.Area.ForegroundColor = Color.FromArgb(171, 197, 228);
series.Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.Solid;
*/
series.Border.IsVisible = false;
series.DataLabels.Font.Size = 10;
//chart.NSeries.IsColorVaried = false;
DataLabels dataLabels = series.DataLabels;
dataLabels.Position = LabelPositionType.OutsideEnd;
dataLabels.ShowCategoryName = false;
dataLabels.ShowValue = true;
dataLabels.ShowPercentage = false;
chart.CategoryAxis.AxisLine.IsVisible = false;
chart.CategoryAxis.MajorGridLines.IsVisible = false;
chart.CategoryAxis.TickLabels.Font.Size = 10;
chart.ValueAxis.MinValue = 0;
chart.ValueAxis.MaxValue = 110;
chart.ValueAxis.IsVisible = false;
chart.ValueAxis.MinorGridLines.IsVisible = false;
chart.ValueAxis.MajorGridLines.IsVisible = false;
chart.PlotArea.Border.IsVisible = false;
//chart.PlotArea.Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.None;
//chart.ChartArea.Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.None;
//chart.ChartArea.Area.ForegroundColor = Color.White;
chart.ShowLegend = false;
MemoryStream ms = new MemoryStream();
workbook.Save(ms, SaveFormat.Excel97To2003);
byte[] downloadBytes = ms.ToArray();
string title = "test";
string _format = "xls";
HttpContext.Current.Response.AddHeader("Content-Type", "application/" + _format);
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + title + "." + _format);
HttpContext.Current.Response.BinaryWrite(downloadBytes);
HttpContext.Current.Response.End();
If you look again at the bolded code, I added in a few lines to apply a theme to the Row 1 and Row 2. I also changed the SaveFormat to Excel97To2003 and the _format to "xls" instead of "xlsx". Now, opening this file in excel (2010 in my case) you can see the first two rows have a theme applied and when you change the Theme of the excel document, the colors change accordingly.
Hi,
Thanks for your feedback.
I was able to observe the issue with xls format. For xlsx format, everything is working fine, but when the workbook is exported to xls format and then you hover the mouse on the themes, the colors do not change.
It should be fixed. Also I manually created xls file and when I hover mouse on themes, it gets changed. So it is a bug and should be fixed in Aspose.
We will look into it and fix the problem and update you asap.
This issue has been logged as CELLSNET-40852.
Below is a test code, I have attached the output xls file and the screenshot for a reference.
C#
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
Cells cells = sheet.Cells;
cells[“A1”].PutValue(“High”);
cells[“B1”].PutValue(“Medium”);
cells[“C1”].PutValue(“Low”);
cells[“D1”].PutValue(“Informational”);
cells[“E1”].PutValue(“New”);
cells[“F1”].PutValue(“Reopened”);
cells[“G1”].PutValue(“False Positive”);
cells[“H1”].PutValue(“Fixed”);
cells[“A2”].PutValue(17);
cells[“B2”].PutValue(9);
cells[“C2”].PutValue(41);
cells[“D2”].PutValue(6);
cells[“E2”].PutValue(7);
cells[“F2”].PutValue(31);
cells[“G2”].PutValue(11);
cells[“H2”].PutValue(88);
int chartIndex = sheet.Charts.AddFloatingChart(Aspose.Cells.Charts.ChartType.Bar, 5, 150, 500, 300);
Aspose.Cells.Charts.Chart chart = sheet.Charts[chartIndex];
chart.Title.Text = “test”;
string nSeriesData = “A2:H2”;
string categoryFieldData = “A1:H1”;
chart.NSeries.Add(nSeriesData, false);
chart.NSeries.CategoryData = categoryFieldData;
for (int i = 0; i < chart.NSeries.Count; i++)
{
if (cells[0, i + 1].Value == null)
{
chart.NSeries[i].Name = “”;
}
else
{
chart.NSeries[i].Name = cells[0, i + 1].Value.ToString();
}
}
//Format Chart
chart.Title.Font.Color = Color.Black;
chart.Title.Font.IsBold = true;
chart.Title.Font.Size = 12;
Aspose.Cells.Charts.Series series = chart.NSeries[0];
//Testing themeing
chart.NSeries[0].Area.FillFormat.Type = FillType.Solid;
CellsColor cc = chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor;
cc.ThemeColor = new ThemeColor(ThemeColorType.Accent1, 0.15);
chart.NSeries[0].Area.FillFormat.SolidFill.CellsColor = cc;
/*
series.Area.ForegroundColor = Color.FromArgb(171, 197, 228);
series.Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.Solid;
*/
series.Border.IsVisible = false;
series.DataLabels.Font.Size = 10;
//chart.NSeries.IsColorVaried = false;
DataLabels dataLabels = series.DataLabels;
dataLabels.Position = LabelPositionType.OutsideEnd;
dataLabels.ShowCategoryName = false;
dataLabels.ShowValue = true;
dataLabels.ShowPercentage = false;
chart.CategoryAxis.AxisLine.IsVisible = false;
chart.CategoryAxis.MajorGridLines.IsVisible = false;
chart.CategoryAxis.TickLabels.Font.Size = 10;
chart.ValueAxis.MinValue = 0;
chart.ValueAxis.MaxValue = 110;
chart.ValueAxis.IsVisible = false;
chart.ValueAxis.MinorGridLines.IsVisible = false;
chart.ValueAxis.MajorGridLines.IsVisible = false;
chart.PlotArea.Border.IsVisible = false;
//chart.PlotArea.Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.None;
//chart.ChartArea.Area.FillFormat.Type = Aspose.Cells.Drawing.FillType.None;
//chart.ChartArea.Area.ForegroundColor = Color.White;
chart.ShowLegend = false;
workbook.Save(“output.xls”, SaveFormat.Excel97To2003);
Screenshot: