Questions on manipulating the color palette

[spygordon]

Hi,

We need to use custom colors in our files, with support for Excel 97-2003 format. Therefor we are editing the palette using Workbook.changePalette(Color, index). To get everything right, I have a couple of questions in this regard:

1. Does the method Workbook.getMatchingColor(Color) always return Colors from the standard palette or will it operate on my modified palette? Need to know if this method might return Colors that I have replaced during palette modification.

2. Sometimes we add new sheets to existing files, therefor it would be great to know which colors are already used in the file to avoid replacing them in the palette. Do you have a method that returns all colors that is used in different styles in a Workbook? Or their indices in the palette?

3. When I want to use a custom color that does not exist in the palette, do I have to update the palette before using this color in a Style object, or is it ok if I set all my Styles with custom colors and only update the palette once before saving the file?

4. Do you have a method that ensures that the Colors used in the Styles in a Workbook are placed in the palette automatically (as long as they are not too many of course)? I can easily implement this myself but if such a method already exists it would be redundant.

Thanks and best regards

Claes

[Amjad_Sahi]

Hi,

1) Yes, the method returns the updated colors in color palette.

2) I think you may use Workbook.isColorInPalette to check if a color is there in the color palette. To get all the colors in the color palette, you may use Workbook.getColors(), it will give you Color array. So, you may loop through all the colors (0-55 indexed) to get them and set the shading colors in the worksheet cells if you want.

3) Well, you should update the color palette with your custom colors before using them.

4) check 2)

Thank you.

[spygordon]

Thank you,

There is still a problem with question nbr 2.

What normally happens is that files are created in Excel 2007, with some custom colors, and then stored as Excel 97-2003. This is done without any changes being made to the palette by the user, meaning that the custom colors actually does not exist in the palette. So if anyone views the file in Excel 2003, they will get the closest matching color which often is way off.

So, what I was looking for was not a way to check if a given Color is in the palette, instead I wanted to know which Colors from the palette is actually used in the file. That way I can update the palette to ensure all colors found in the styles that does not exist in the palette are added to the palette, and also I can avoid changing a color that was already used in the template file.

I assume that I can just go through the entire StyleCollection for the Workbook when I open it and add colors that are not in the palette, but I was hoping you already had some code for doing this, or at least to get the list of colors used in the styles.

Best Regards

Claes

[Amjad_Sahi]

Hi,

Well, I am not sure if there is a direct way or method to check the custom colors used by the Style objects in the worksheets. I think you have to do it manually by using your code, e.g, you may loop through the Style collection / formattings to evaluate and check the existing colors.

Thank you.

[spygordon]

Hi,

I’ve added code to go through the Styles in the workbook and as a result I can avoid altering colors in the palette that are in use. However, in files stored by Excel the amount of Styles seems very large and I was hoping you could shed some light on this.

As an example you can see that in the attached file called Simple.xls, which was created by the latest Aspose.Cells version, there are 6 Styles in the workbook. But if I re-save this file through Excel (attached file: Simple Saved in Excel.xls) the nbr of Styles is all of a sudden 47.

Do you know why Excel behaves this way, are they all really needed? Is there some way for me to separate between the original 6 Styles and all the extra that has been added by Excel? The biggest problem is that I receive a big amount of different Colors meaning that I lock up almost the entire palette and can add nothing new myself.

I know that I could use the cell iterators you provide to look at Styles applied to actual cells, but it is not uncommon that we receive huge template files to add info into, and this process must be as fast as possible so I really want to avoid that. The big difference in the amount of Styles returned by Workbook.getStyles() also makes me think that there might be something crucial I am missing here.

I’d be very grateful for any help/guidance on this matter

Best Regards

Claes

[shakeel.faiz]

Hi,
We think you must have re-saved the Simple.xls by Excel2007 or later versions. From excel2007 there are more pre-defined named styles automatically created. The extra styles in the re-saved xls are just those new named styles imported from excel2007.
We are afraid Workbook.getStyles() cannot fit your requirement here. The StyleCollection returned by this method only contains named styles, not includes the styles used by cells. If you required, we may consider to provide a new method for you to get all styles that used by cells. However, colors are used not only by cells, also they can be used by other objects such as shapes, charts, …etc. If your template file contains only cells data with formattings, your current way of checking and replacing colors can work. But if there are other objects such as chart, it is not enough for the used colors by checking colors used by style objects only.

[spygordon]

Hi,

Thank you for your reply. Yes, I was using Excel 2007 when saving the file.

For the future, it would indeed be great if you could provide a method that returns all colors used by cell Styles, Charts, Shapes etc. For now, I will manage without.

Best Regards

Claes

[shakeel.faiz]

Hi,

Thanks for your feedback and future request. We will look into it and consider adding it.

[shakeel.faiz]

Hi,

In fact if you are using excel2007 to view the generated xls file, there should be no color lost even if the color is not in the palette. When one color is not in the palette, we will choose the nearest color in palette to represent the custom color for excel2003 and prior versions for display. At the same time the information of the custom color also be saved into the xls file and the custom color can be shown correctly for excel2007 and later versions. So please confirm whether you need to make so complicate logic to change the palette. To check the whether one color has been used by any object in workbook is complicated and time-consumed. Finally if you decide you really need to change palette in this way, we will look into this requirement to decide how to provide you some APIs for this purpose(provide style list for you to check by yourself or check it at our end for you).

[spygordon]

Hi,

Yes, I am using Excel 2007, however we have users with varying versions of Excel so we need to support custom colors in both Excel 2007 and older versions.

What I am doing now is the following:

- I do nothing special with colors if I am creating a 2007 file and, as you stated, all custom colors come out great anyway.

- When generating 2003 files I need to update the palette to use custom colors and therefor I am still interested of a good method to get all colors used in a file. This is needed when I add info into a file that someone else created and I do not want to replace colors in the palette that has already been used in the file. Currently I check this myself once in a process that is allowed to be time consuming, however, if you could provide a quick method for getting these colors which I could use more often it would indeed be great.

Thank you

Claes

[Amjad_Sahi]

Hi,

Thanks for providing further details.

We will look into your requirement and update you soon.

Thank you.

[Amjad_Sahi]

Hi,

We will consider to provide you some way to check whether one color has been used by objects in current workbook. But I am afraid we need some more time to implement this feature. A ticket is already logged with an id: CELLSJAVA-40011. Once we have any update, we will update you here.

Thank you.

[spygordon]

That’s good news!

Thank you

Claes

[Amjad_Sahi]

Hi,

Please try the new fix v7.0.2.4. We have provided a new method:

Color[] com.aspose.cells.CellsHelper.getUsedColors(Workbook workbook)

This method will return all Colors used by the objects in a given Workbook. You can re-arrange those colors in the palette as per your requirement.

Thank you.

[spygordon]

Many thanks!

Claes

[Amjad_Sahi]

The issues you have found earlier (filed as CELLSJAVA-40011) have been fixed in this update.

[aspose.notifier]

The issues you have found earlier (filed as CELLSJAVA-40011) have been fixed in this update.

---

This message was posted using Notification2Forum from Downloads module by aspose.notifier.

[aspose.notifier]

The issues you have found earlier (filed as ) have been fixed in this update. This message was posted using BugNotificationTool from Downloads module by MuzammilKhan