ColorIndex: Can I still use it in any way?

I realize that the ColorIndex is no longer needed in Excel 2007 and later. However it is still available in Excel (can be set in VBA, or through Interops). We have old macros and add-ins which rely on specific ColorIndex attributes in cells. The macros/add-ins can not be modified at this point so we don't have the option of moving away from their use.


Unfortunately there seem to be 2 ColorIndex values that correspond to Yellow and if we simply set a cell color to yellow, when Excel loads the file it picks the ColorIndex as 6, and we need it as 27 (in the add-in we can't change)... 

So currently we are stuck using Excel Interops which is slow. Would like to use FlexCel which is fast (would be looking to buy soon). But I can't see how I could force the ColorIndex issue. Or maybe I am missing something

TIA
Hi,
FlexCel fully supports colorindexes, and you can indeed set a cell to a given colorindex. You can also change the color palette so colorindex 6 is not yellow, or colorindex 27 is, etc. (by the way, you can also change the color palette by going to File->Options->Save and clicking in "Colors" at the bottom of the dialog).

As usual, APIMate will tell you how to change the palette. If you load in APIMate a file with a modified color palette, APIMate will tell you how to do the same with FlexCel. But it won't help in using colorindexes by design, because in the normal case we don't really want to make people use colorindexes at all. But all support is there if you need it for cases like this one.

Basically, whenever you set a .Color property in FlexCel, you are setting a TExcelColor struct, which can contain themed colors, rgb colors, or indexed colors. So you can do something like this:

           var xls = new XlsFile(1, true);
            var fmt = xls.GetCellVisibleFormatDef(1, 1);
            fmt.FillPattern.Pattern = TFlxPatternStyle.Solid;
            fmt.FillPattern.FgColor = TExcelColor.FromIndex(27);
            xls.SetCellFormat(1, 1, xls.AddFormat(fmt));





In a similar way, when you read a "Color" property it is a TExcelColor struct and you can read the color index if the color is an indexed color. Also note that this is supported in both xls and xlsx files, you don't need to save as xls to have indexed colors.

I've written about colors more in depth a (long) time ago:
https://www.tmssoftware.com/site/blog.asp?post=135

While the article will actually try to convince you to not use indexed colors because except for cases like this where an addin needs them they are really not needed today, it explains a little more in depth how colors work in Excel and FlexCel.

Adrian,


Thanks for the super-quick reply. 

I think I am going to pull the trial version and see if I can get this to work. If I can then we may have a good way forward....

Thanks Again.

Sent you an email on Friday. I was wondering if there was any information on my question?


Thanks

Hi,

The mail was received, yes. It is just that the licensing stuff might go slower because there is more people involved. I've been notified that your license should have been swapped now.

Great thanks. 

Just a quick follow up. I was able to do what I needed with the ColorIndex. This allowed me to replace the code using Excel as an Automation server to create the spreadsheets we need. 


The excellent news is that the time taken to create a typical one has dropped from about 30 seconds to about 0.7 seconds. This has made the end users very happy.

It is also now opening up the chance to suggest new features that just were not feasible because the time taken would have prevented their use.