How to set Theme colors in Excel spreadsheet using XML files

Aspose.Cells Product Family
1

Hello,

I’m looking for a way to use themes in Aspose.Cells. For our Excel reports we have different xml-files. With Aspose (.NET, C#) I want to use the excel templates and the xml files to fill the report and save it as PDF.

I have read that themes are (partly?) supported but I didn’t find out how to include e.g. the xml Files for the Theme Colors.

Is it possible? If yes, how can I do it?

Regards
Ringo

2

Hi,


Please see the document for your reference on using Excel Themes and Colors:
Excel Themes and Colors|Documentation

Also, check:
http://docs.aspose.com/display/cellsnet/Approaches+to+Format+Data+in+Cells

Thank you.
3

Thank you for the answer.

So I cannot use our already existing xml files for themes, but I can create themes with .NET. Is it right?

4

Hi,

Yes, your understanding is correct.

I have also logged a ticket with an id: CELLSNET-40541. We will investigate and check the feasibility if we can support it or not.

Thank you.
5

Hi,

What’s the content of your xml file? Does it define theme colors according with your own specification or the file is in same structure with the /xl/theme/theme1.xml file? If your xml file is just same with the theme1.xml in XLSX file, we think you can update the XLSX file(replacing the theme1.xml file with your own xml file by zip related apis) at first and then use new Workbook(Stream) to load the updated XLSX file.

Or, you can generated your report with existing template file at first, and then update the generated XLSX file by replacing the theme1.xml file with your own xml file.

Otherwise, we are afraid you have to get defined theme colors from your xml file and set them to the loaded workbook(such as by workbook.SetThemeColor(ThemeColorType type, Color color)) by yourself.

6

The content are the design colors. I have some xml files which I want to change dynamically, depending of the customer who use it.

So I have created a small class which load the xml file and update the theme colors:

public class Design
{
public void load(string filename, Workbook workbook)
{
XmlDocument doc = new XmlDocument();
XmlElement root = null;
Color dColor;

doc.Load(filename);

root = doc.DocumentElement;

foreach (XmlNode node in root.ChildNodes)
{
if (node.ChildNodes[0].Name == "a:sysClr")
dColor = ColorTranslator.FromHtml(node.ChildNodes[0].Attributes["val"].InnerText);
else
dColor = ColorTranslator.FromHtml("#" + node.ChildNodes[0].Attributes["val"].InnerText);

switch (node.Name)
{
case "a:dk1":
workbook.SetThemeColor(ThemeColorType.Text1, dColor);
break;
case "a:lt1":
workbook.SetThemeColor(ThemeColorType.Background1, dColor);
break;
case "a:dk2":
workbook.SetThemeColor(ThemeColorType.Text2, dColor);
break;
case "a:lt2":
workbook.SetThemeColor(ThemeColorType.Background2, dColor);
break;
case "a:accent1":
workbook.SetThemeColor(ThemeColorType.Accent1, dColor);
break;
case "a:accent2":
workbook.SetThemeColor(ThemeColorType.Accent2, dColor);
break;
case "a:accent3":
workbook.SetThemeColor(ThemeColorType.Accent3, dColor);
break;
case "a:accent4":
workbook.SetThemeColor(ThemeColorType.Accent4, dColor);
break;
case "a:accent5":
workbook.SetThemeColor(ThemeColorType.Accent5, dColor);
break;
case "a:accent6":
workbook.SetThemeColor(ThemeColorType.Accent6, dColor);
break;
case "a:hlink":
workbook.SetThemeColor(ThemeColorType.Hyperlink, dColor);
break;
case "a:folHlink":
workbook.SetThemeColor(ThemeColorType.FollowedHyperlink, dColor);
break;
}
}
}
}

That works for me.

7

Hi,

Thanks for your feedback and providing your code example.

It will be helpful for others too.

It is good to know your problem is resolved now. We have closed this ticket.

If you get any other question or face any problem, please let us know. We will help you asap.