Hi,
I’ve got a situation where I need to create a workbook using COM, add a chart with custom colour palette, and finally save the workbook. Once this is done, I need to re-open the workbook with Aspose component and make modifications.
The first issue I have is that the custom colour palette is gone when I -re-open the workbook with Aspose component. So I wrote a function to re-create the palette, but the chart does not have the right colours (in fact it is build all in purple !).
The function is the one below. I just loop through a Hashtable to recreate the palette (line 1890). It looks all fine, but the chart is not properly coloured… Any idea what is missing ?
1878 Private Function CreateColorPalette()
1879 Try
1880 Dim color As Color
1881
1882 Dim values As ICollection = _hColorIndexes.Values
1883 Dim valuesArray(_hColorIndexes.Count - 1) As Integer
1884 values.CopyTo(valuesArray, 0)
1885 Array.Sort(valuesArray)
1886 For Each v As Integer In valuesArray
1887 For Each de As DictionaryEntry In _hColorIndexes
1888 If de.Value = v Then
1889 color = ColorTranslator.FromHtml(de.Key)
1890 _asposeWb.Colors(v - 1) = color
1891 End If
1892 Next
1893 Next
1894
1895 _asposeWb.Colors(23) = Drawing.Color.White
1896 _asposeWb.Colors(24) = Drawing.Color.LightGray
1897
1898 _paletteIndex = 24
1899 Catch ex As Exception
1900 EEError.HandleError(ex, "Error creating the palette for Aspose workbook")
1901 End Try
1902 End Function
Actually i’ve realised two things :
-I could use ChangePalette function, but it doesn’t make any differences…
-When
I re-open the workbook with Aspose, the palette is actually already
correct ! I shouldn’t even have to re-create the palette.
Yet,
the chart is all purple, as if the colours in the palette were lost,
but they are not… Maybe the colours of the chart are lost then. What
can I do ?
Hi,
Well, your need to add your desired custom colors to MS Excel Standard Color Palette first, before setting it as color for different objects. Since the Excel color palette has only 56 colors (0-55 indexed) on it, so if a color is not there, you will add the color to the palette replacing any existing color on a specified index position using Workbook.ChangePalette() method.
e.g..,
........
Dim workbook As New Workbook()
................
workbook.ChangePalette(Color.LightYellow,55)
workbook.ChangePalette(Color.FromArgb(255, 206, 206), 54)
workbook.ChangePalette(Color.FromArgb(206,255,206), 53)
.........
.........
For further reference, please check the document: http://www.aspose.com/documentation/file-format-components/aspose.cells-for-.net-and-java/colors-and-palette.html
If you still find the issue, kindly create a simple console app for demonstration, zip it and post it here with the template file(s), we will check it soon.
Thank you.
Hi,
I have created a sample for you.
relevant parts are :
1. source.xml is opened at line 107 (this file is in bin/debug)
2. chart is created with COM object
3. workbook is saved on C: drive with a random name at line 249
at this stage you can try to open the file and you will see chart with
correct colours (dark green, light green, dark red and light red)
4. From line 271 come the Aspose stuff and you will see I just re-open the
chart, change the colour palette and save the chart again.
Now the chart has wrong colours.
Thanks for helping !
Hi again,
I don’t know if you had a chance to look at my sample yet, but please do.
I have tried something even more straightforward : I’ve opened Microsoft Excel, added a custom colour and filled a cell with this colour. Then I saved the workbook.
Next, I’ve used Aspose cells component to open this workbook and save it under another name.
Guess what ? The cell is now filled with a different colour from the original one…
Surely it should remain the same custom colour, but opening an existing excel workbook with Aspose and saving it, seems to discard any custom palette colour. I don’t think this is right.
Hi,
Thank you for sharing the project.
We will look into your issue and get back to you soon.
Thank You & Best Regards,
Hello,
I was wondering if you had a chance to look at my project ? I’m still having a hard time trying to get this to work 
Thanks
Hi,
Thank you for considering Aspose.
After an initial test, we have found your mentioned issue using your provided application. We will try to provide a fix soon.
Thank You & Best Regards,
Ok thanks.
On top of that I’d like to add that there are other issues to this.
When I create a workbook with COM and then open it with Aspose, some information is lost, such as the color (as mentionned earlier) but also the formatting of the dates and the text size.
I’ve attached two spreadsheet. First one is created with COM. Second one is the first one opened with Aspose and saved with Aspose.
Let me know your thoughts about this…
Hi,
Which version of Aspose.Cells for .NET component you are using? Could you give it a try the attached version if it works.
Your sample template "saved with COM.xls" you attached in your previous post does not open fine in MS Excel, so, I opened it into MS Excel 2007 and saved it as "MS Excel 97-2003... xls" format. Then, I tried (open and save the file) using the the attached version with the file, the generated file is fine without any issue (bar colors, dates label formats etc.)
Kindly give us your feedback using the attached version.
Thank you.
Let me recap here… You’ve tried to open the file ‘saved with COM.xls’ with Excel 2003 but it didn’t work. That makes sense because I’ve saved it with COM for Excel 2007. So you converted it to 2003 and re-opened it and it was fine. I’m happy with all that and there’s no issue with this file, I agree.
However the problem is with the other one, named ‘opened with Aspose.xls’. The way I created this file is :
-Create ‘saved with COM.xls’ with COM and save it.
-Open ‘saved with COM.xls’ with Aspose component and save it as ‘opened with Aspose.xls’
-Check if the two files have same colour, text format, etc,etc… -> FAILED
So once the first file is opened and saved using Aspose, the issue occurs.
The version of the dll I’m using is 4.7.4.19 from 21/07/2009
Hi,
Well, When I try to use MS Excel 2007 to open your 'saved with COM.xls' file, I got the error, see the attached image. After clicking the 'Yes' button, the file is opened into MS Excel 2007, then I re-save it to MS Excel 97-2003 file format before using Aspose.Cells for .NET to load/save it.
Thank you.
Yes you are right. If I convert it to 97-2003 before I open it with Aspose, it is fine. I don’t know why though… Maybe it’s related to the Interop.Excel version …
Hi,
After further investigation, we come with the following points.
1) Please check <A href="https://forum.aspose.com/t/101743,then</FONT></U></FONT></A><FONT face=Arial><FONT size=2> and try to change/set the color as Aspose.Words team suggested. You may use JavaScript/Vbscript programming structs to write your code accordingly but the steps would be the same.</FONT></P></FONT>
<P><FONT face=Arial size=2>2) The file "saved with COM.xls" is not Excel 97-2003 file, it's Excel 2007 xlsx file. So please change the file name extension as ".xlsx",or MS Excel could not open it.
3) We are checking the issue that some color and formats are lost in converting xlsx to xls file. And by the way, could you still save the file as Excel 2007 xlsx file.
Thank you.
Hi
Thanks a lot for you help. This is very appreciated.
I think the main issue with my code was that I was using Interop.Excel v.12 in order to create the spreadsheet with COM. For that reason, the only way to open it with Excel was to use Excel 2007 in order to convert it to 2003 and in the process, the colour information was lost.
So, I’ve decided to install and use Interop.Excel v. 11, and now everything works like a treat. I believe this was the root of the problem. Seems solve now.
Thanks !