Hi,
I’ve discovered an issue when creating sheets containing colours, and the problem seems to go back to version 1.6.0 which is the last version that appears to work as expected?
This is the code that sets up the style:
styleIndex = styles.Add();
_styleDescriptions = styles[styleIndex];
_styleDescriptions.ForegroundColor = System.Drawing.ColorTranslator.FromHtml(PGEN_GREEN);
_styleDescriptions.Font.IsBold = true;
_styleDescriptions.Font.Color = System.Drawing.Color.White;
_styleDescriptions.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left;
and this is the code that applies it
for(int i = 0;i < years.Count;i++)
{
ws.Cells[startRow,startColumn].PutValue(years[i].ToString());
ws.Cells[startRow,startColumn].Style = _styleDescriptions;
startColumn++;
}
I have tried changing the code in another part of the application to use BackgroundColor and set the Pattern property to Solid but this gives the same results. This code to open the sheet is as follows
outFile = new Excel(AsposeLicenseFilePath);
outFile.Open(templateFile);
Any ideas please?
Cheers,
Andy
I can add to this explanation that in my case color and font style information seems
to be lost as soon as the text gets rotated by any number of degrees.
It has worked fine with 1.6.3.3. Now I have tested 1.7.3.0 and the problem popped up.
Dear Andy,
From 1.7.0, the custom color set routine is changed.
_styleDescriptions.ForegroundColor = System.Drawing.ColorTranslator.FromHtml(PGEN_GREEN);
I think the foreground color is not in the MS Excel standard color palette. To use it, please add a line of code like this:
excel.ChangePalette(System.Drawing.ColorTranslator.FromHtml(PGEN_GREEN), 55);
Please refer to Excel.ChangePalette API.
Dear Kai,
In my machine, rotation works fine.
If you rotate the text, does the text disappear?
If yes, please extend the row heigh to see if the style information is lost.
Otherwise, please post your code here. You can also download fix 1.7.3.1 and have a try.
Thank you.
Laurence,
it does rotate but looses font bold settings, i.e. the font will not be shown bold.
With 1.6.3.0 this works just fine.
Regards
Kai
Dear Kai,
Sorry for the inconvenience. But in my machine, it also works fine.
The following is my test code:
Excel excel = new Excel();
Cell cell = excel.Worksheets[0].Cells[“A1”];
cell.PutValue(“hello”);
cell.Style.Font.Name = “Tahoma”;
cell.Style.Font.IsBold = true;
cell.Style.Rotation = 25;
excel.Save(“book1.xls”, SaveType.OpenInExcel, FileFormatType.Default, this.Response);
Have you tried the fix 1.7.3.1?
Laurence,
I have now tried it using 1.7.3.1 and it does not work. However, even though I’m using Orientation rather than Rotation to rotate the text this wasn’t the problem. What I’m doing is reading a grid’s cell style data and create a Style object out of this for use within Aspose.Excel. I have traced down my code and it generates/assigns the style object fine, but the bold and color settings for the cell seem to be lost.
When generating the styles I create a hash string that identifies the style I’m creating in order to find/reuse it the next time the same cell style is required.
So basically what I’m doing is creating styles for each differing cell style and then reuse these styles on subsequent cells where applicable. I first check the styles collection, if a style with a given name already exists. If it does, I simply pick it and assign it to the cell in questions. Otherwise I create a new style set its name to my hash/search string and add it to the Styles collection/cell.
Maybe this can be of some help; this is the source that will generate styles
int orientation = gridCell.Font.Orientation;
object cellValue = gridCell.CellValue;
Type cellType = gridCell.CellValueType;
string cellFormat = gridCell.Format;
Excel.TextOrientationType xlOrientation = Excel.TextOrientationType.NoRotation;
// Adjust angles to excel compatible angles
if(orientation > 0 && orientation <= 90)
{
xlOrientation = Excel.TextOrientationType.ClockWise;
}
else if(orientation >= 270 && orientation < 360)
{
xlOrientation = Excel.TextOrientationType.CounterClockWise;
}
// Get the Cell Style
string styleName =
gridCell.BackColor.ToString() + “" +
gridCell.TextColor.ToString() + "” +
gridCell.Font.Bold.ToString() + “" +
gridCell.Font.Italic.ToString() + "” +
xlOrientation.ToString() + “" +
(cellType == null ? “none” : cellType.ToString()) + "” +
(cellFormat == null || cellFormat.Length == 0 ? “none” : cellFormat) + “_” +
gridCell.HorizontalAlignment.ToString();
Excel.Style style = null;
try { style = excel.Styles[styleName]; }
catch {}
if(style == null)
{
Color backColor = gridCell.BackColor;
Color textColor = gridCell.TextColor;
// Need to perform color mapping?
if(_colorMap.ContainsKey(backColor) == true)
{
ColorMap map = (ColorMap)_colorMap[backColor];
backColor = map.BackgroundColor;
textColor = map.ForegroundColor;
}
style = excel.Styles[excel.Styles.Add()];
style.Name = styleName;
style.ForegroundColor = backColor;
style.Font.Color = textColor;
style.Font.IsBold = gridCell.Font.Bold;
style.Font.IsItalic = gridCell.Font.Italic;
style.Orientation = xlOrientation;
// Is it a number format?
if((cellType != null && cellType == typeof(double)) || cellFormat == “F” || cellFormat == “P”)
{
if(cellFormat == String.Empty || cellFormat == null || cellFormat == “F”)
{
style.Number = 2;
}
else if(cellFormat == “P”)
{
style.Number = 10;
}
}
// Is it a date?
else if(cellType != null && cellType == typeof(DateTime))
{
style.Number = 14;
}
// Set the horizontal alignment
switch(gridCell.HorizontalAlignment)
{
case GridHorizontalAlignment.Right :
{
style.HorizontalAlignment = Excel.TextAlignmentType.Right;
break;
}
case GridHorizontalAlignment.Center :
{
style.HorizontalAlignment = Excel.TextAlignmentType.Center;
break;
}
}
}
// Assign the Cell Style
xlCell.Style = style;
if(cellValue != null)
{
try
{
if(cellType != null && cellType == typeof(double))
{
xlCell.PutValue(Convert.ToDouble(cellValue));
}
else if(cellType != null && cellType == typeof(DateTime))
{
xlCell.PutValue(((DateTime)cellValue).ToOADate());
}
else
{
xlCell.PutValue(cellValue.ToString());
}
}
catch
{
xlCell.PutValue(cellValue.ToString());
}
}
Regards
Kai
Dear Kai,
Thanks for your great help. Yes, that’s a bug and I fixed it. Please download fix 1.7.3.2.
Laurence,
thanks for this, it is working fine again now. Looking forward to using the new Chart APIs you have added for the 1.7 build.
Regards
Kai
Thanks but I’m still having problems. We’ve narrowed it down to one line described in the following code:
using System;
using Aspose.Excel;
using System.Drawing;
namespace AsposeTest
{
class Class1
{
static Excel outFile;
static Style _styleDescriptions;
static Style _styleHeader;
static Style _styleRoundNumber;
static Style _stylePercent;
static void Main(string[] args)
{
try
{
outFile = new Excel(@“c:\Aspose.Excel.lic”);
// The template file below is a blank spreadsheet (file->new then save)
// but if the following line is commented out, the styles are correct!
outFile.Open(@“c:\template.xls”);
CreateCellStyles();
Worksheet outWS = outFile.Worksheets[0];
outWS.Name = “Pricing”;
outWS.Cells[1,1].PutValue(“Test”);
outWS.Cells[1,1].Style = _styleDescriptions;
//Create Currency
int startRow = 5;
outWS.Cells[startRow,0].PutValue(“Currency”);
outWS.Cells[startRow,0].Style = _styleHeader;
outWS.Cells[startRow,1].PutValue(“Fred”);
outWS.Cells[startRow,1].Style = _styleRoundNumber;
outFile.Save(@“c:\ABTest.xls”, FileFormatType.ExcelXP);
// This second saved file is completely blank!!
outFile.Save(@“c:\ABTest2.xls”, FileFormatType.ExcelXP);
}
catch(Exception ex)
{
Console.WriteLine(ex.Message);
}
Console.ReadLine();
}
private static void CreateCellStyles()
{
string PGEN_GOLD = “#ffcc00”;
string PGEN_GREEN = “#006666”;
outFile.ChangePalette(System.Drawing.ColorTranslator.FromHtml(PGEN_GREEN), 55);
Styles styles = outFile.Styles;
int styleIndex = styles.Add();
_styleDescriptions = styles[styleIndex];
_styleDescriptions.Font.IsBold = true;
_styleDescriptions.Font.Color = System.Drawing.Color.White;
_styleDescriptions.HorizontalAlignment = Aspose.Excel.TextAlignmentType.Left;
_styleDescriptions.ForegroundColor = System.Drawing.ColorTranslator.FromHtml(PGEN_GREEN);
styleIndex = styles.Add();
_styleRoundNumber = styles[styleIndex];
_styleRoundNumber.Number = 2;
_styleRoundNumber.ForegroundColor = System.Drawing.ColorTranslator.FromHtml(PGEN_GOLD);
styleIndex = styles.Add();
_styleHeader = styles[styleIndex];
_styleHeader.Font.Color = System.Drawing.Color.White;
_styleHeader.Font.IsBold = true;
_styleHeader.ForegroundColor = System.Drawing.Color.Black;
styleIndex = styles.Add();
_stylePercent = styles[styleIndex];
_stylePercent.ForegroundColor = System.Drawing.ColorTranslator.FromHtml(PGEN_GOLD);
_stylePercent.Number = 10;
}
}
}
Dear Andy,
Thanks for your help.
Have you tried fix 1.7.3.2?
1. If you save the file, the data in Excel object is re-initialized. If you want to re-use it, try the following code:
Excel excel1 = new Excel();
Excel excel2 = new Excel();
excel1.Open(@“c:\template.xls”);
excel2.Copy(excel1);
…
excel1.Save(@“c:\ABTest.xls”, FileFormatType.ExcelXP);
excel2.Save(@“c:\ABTest2.xls”, FileFormatType.ExcelXP);
2. About the style, I have not found the problem yet.
“Test” cell’s font is bold and font color is white. Its foreground color is PGEN_GREEN.
“Currency” cell’s font is bold and font color is white and foreground color is black.
“Fred” cell’s foreground color is PGEN_GOLD and number format is “0.00”.
Could you elaborate the problem existing in your machine?
Laurence,
On my machine (with version 1.7.0.0) the “Currency” cell, the font colour is black as well as the foreground colour. In other words, you cannot see the text, the whole cell is black.
Just this minute tried 1.7.3.2 and everything is OK, so thanks, problem solved.
Andy