Excel spreadsheets with XML

Question

Hi, I'm creating excel spreadsheets for reports using the XML method, where you export a valid Microsoft formatted XML, and then export it to Excel.

So far everything works as it should, but I'm having some trouble with the colours.

I've created one style such as:

<Style ss:ID="s64" ss:Parent="s25">
      <Font ss:FontName="Tahoma" x:Family="Swiss" ss:Size="14" ss:Color="#4579B9" ss:Bold="1"/>
      <Interior/>
</Style>

The colours it's supposed to use, should look like the but on the top of this image:

However, the colour it's using the the one on the bottom.

When I generate the report, it's generated with the colour code: #4579B9, which is what I want, but when I open it, it's displaying the colour as the one from the bottom of the image.

If I then save the document, it automatically changes the colour to be #666699, which is exactly what the colour on the bottom (wrong colour) is.

This however only happens with font colours, as if I change the background colour of any cell to be #4579B9, it will work normally.

I'm thinking it's got something to do with the colour selected being part of the "theme colours" as opposed to the "standard colours".

Does anyone here have any experience generating these kind of reports, and applying styles to it correctly? Or am I missing something?

Answer 1

I believe you are using Excel 2007 because of the colors you are using and you mentioned theme. The following color information is for Excel 2003, which is the only version I have experience with using SpreadsheetXML (XMLSS).

Excel 2003 only supports a limited number of colors. Since the specified color is not part of the color palette, the closest matching color is used. What you need to do is specify a different color palette or match your color to one of the default colors.

Following is an example of defining the color palette for a workbook. I reduce the color at Index #1, usually White, to a lighter shade of White (255, 253, 253).

 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <RelyOnVML/>
  <AllowPNG/>
  <Colors>
   <Color>
    <Index>1</Index>
    <RGB>#FFFDFD</RGB>
   </Color>
  </Colors>
 </OfficeDocumentSettings>