Hi,
I upgraded to Aspose.Cells v4.4.3.1 from v.4.4.1.0 and I’ve had reports from my users that the date formats are broken.
Using the following code, the date format is no longer displayed properly. But reverting back to 4.4.1.0 solves the problem…
xlsWorkbook.Worksheets[0].Cells.Columns[(byte)idxDateCreated].Style.Custom = “yyyy-mm-dd”;
The release notes for 4.4.2 and 4.4.3 don’t provide any sign that the custom format to use has changed thought.
Can you confirm my observation and provide a fix?
Thanks.
Dominic.
Hi again,
After some verifications, it’s not just the column date format that’s broken. All number formatting is not applied to columns cells. But bold character’s are applied…
Exemples :
Real date : 2008-05-05 19:35:19
Date displayed using 4.4.1.0 : 2008-05-05
Date displayed using 4.4.3.1 : 39573.8161866898
Real number : 250
Number displayed using 4.4.1.0 : 250.00
Number displayed using 4.4.3.1 : 250
Code for formatting columns :
// Format columns
xlsWorkbook.Worksheets[0].Cells.Columns[(byte)idxDateCreated].Style.Custom = “yyyy-mm-dd”;
xlsWorkbook.Worksheets[0].Cells.Columns[(byte)idxAmount].Style.Number = 39;
xlsWorkbook.Worksheets[0].Cells.Columns[(byte)idxTax1].Style.Number = 39;
xlsWorkbook.Worksheets[0].Cells.Columns[(byte)idxTax2].Style.Number = 39;
Aspose.Cells.StyleFlag sf = new Aspose.Cells.StyleFlag();
sf.All = true;
xlsWorkbook.Worksheets[0].Cells.Rows[0].ApplyStyle(xlsWorkbook.Styles[mHeaderStyleIndex], sf);
xlsWorkbook.Worksheets[0].AutoFitColumns();
Thanks
Dominic.
Hi Dominic,
Thanks for considering Aspose.
Yes, we did change some internal structure of the Aspose.Cells component related formattings since version 4.4.2. We have to sacrifice this for some optimization and performance measures and we did modify the way some for some of the tasks performed, we did add some APIs with more enhanced shape. You may utilize some newer APIs of Cells class e.g.., ApplyRowStyle() and ApplyColumnStyle methods for your task.
Well just for your info., in the newer version, we add a new method Cells.ApplyStyle() to apply style to the whole worksheet cells which are initiated. For better performance, you should create style object(s), set its attributes for your desired formattings, and apply this style to a cell using Cell.SetStyle(styleobject), to worksheet cells using Cells.ApplyStyle(styleobject, styleflag), to a row using Cells.ApplyRowStyle(), to a column using Cells.ApplyColumnStyle(), to apply style to range Range.ApplyStyle and to modify an existing style object using Style.Update() methods. All these are new enhancements.
Now come towards your issue, kindly consult the following code which shows how to apply style formattings to a row / column in the worksheet, so you may adjust it in your code.
Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
Style style1;
StyleFlag flag1;
style1 = wb.Styles[wb.Styles.Add()];
style1.Custom = "yyyy-mm-dd";
flag1 = new StyleFlag();
flag1.NumberFormat = true;
//Apply style to the first column.
sheet.Cells.ApplyColumnStyle(0,style1, flag1);
Style style2;
StyleFlag flag2;
style2 = wb.Styles[wb.Styles.Add()];
style2.Number = 39;
flag2 = new StyleFlag();
flag2.NumberFormat = true;
//Apply style to the 11th row.
sheet.Cells.ApplyRowStyle(10,style2, flag2);
wb.Save("d:\\test\\rowandcolumn_formattings.xls");
Feel free to contact us any time if you find any issue.
Thank you.
Thanks for the info.
I’ll convert to ApplyRowStyle() and ApplyColumnStyle().
One last thing, do you know why when I only set the style’s number or custom format but use StyleFlag.All, my formatted column are also forced left-aligned even though I never set HorizontalAlignment?
When I use the StyleFlag.Number only, it’s ok. But, I would have assumed that if the style only defines the number format, nothing else would be impacted even when using StyleFlag.All. Otherwise, not only do I have to define styles somewhere, but I also have to memorize from each style what flags should be use to apply it…
My code is this:
mCashStyleIndex = styles.Add();
style = xlsWorkbook.Styles[mCashStyleIndex];
style.Number = 39;
…
Aspose.Cells.StyleFlag sf = new Aspose.Cells.StyleFlag();
sf.All = true;
xlsWorkbook.Worksheets[0].Cells.ApplyColumnStyle(idxDateCreated, xlsWorkbook.Styles[mDateStyleIndex], sf);
Thanks.
Dominic.
Hi，
Please try this fix.
Thanks a lot, that solves it.
I do have some more questions or observations if I may.
1) With the new API functions, what is the difference between Cell.Style, Cell.SetStyle()? And the difference between Row.Style, Row.SetStyle() and Cells.ApplyRowStyle()? Which one should we use?
2) You are probably aware of this, but the documentation is out of
date. The Cell class doesn’t mention the SetStyle() method, etc. I would suggest adding to the page footer the version for which the
documentation was generated, this would prevent us from having to compare the
doc’s generation date and the version’s release date.
3) Concerning the StyleFlag class to use when applying a style, when using StyleFlag.All, is the existing cell format overriden with the new format OR are the two combined ? If overriden, is there an easy way to combine two styles by “appending” the format defined by a second style while keeping the format defined by the first style.
Let’s pretend for exemple that I need my total row to be in style TotalRowStyle, and the date columns need to be in DateColumnStyle, how can I know what StyleFlag to set when applying my styles ?
I would like to see a function such as Cells.AppendRowStyle() or an optional “bool appendToExistingStyle” parameter to Cells.ApplyRowStyle(). If the two styles define the same format property, the latest style applied would win for that property.
Without that, I need to know in advance that the DateColumnStyle requires me to use StyleFlag.Number, StyleFlag.TextWrapped and StyleFlag.IsUnderline. Is not very practical in my opinion.
4) I can’t seem to understand why StyleFlag is a class instead of an enum type with FlagsAttribute ? Wouldn’t it be simpler to use Cells.ApplyRowStyle(someStyle, StyleFlag.NumberFormat | StyleFlag.FontBold); ?
Thanks for your great help and your time,
Dominic.
Hi Dominic,
Thanks for considering Aspose.
- With the new API functions, what is the difference between Cell.Style, Cell.SetStyle()? And the difference between Row.Style, Row.SetStyle() and Cells.ApplyRowStyle()? Which one should we use?
Well, Cell.SetStyle() is more optimized related performance, it will consume less memory and enhance the performance in the long run, if you got a large range of cells and you want some formattings to those cells, you should define / add a style object, set its attributes and use Cells.SetStyle(style object) method instead of Cell.Style property. For further reference, please check the article: http://www.aspose.com/community/blogs/aspose.cells/archive/2008/04/10/cell-style-property-vs-cell-getstyle-setstyle-method.aspx Similarly Cells.ApplyRowStyle() / Cells.ApplyColumnStyle() are optimized than Row.ApplyStyle / Column.ApplyStyle methods to do the job. Row / Column.Style property is normally used to get the style of a row or column.
- You are probably aware of this, but the documentation is out of date. The Cell class doesn’t mention the SetStyle() method, etc. I would suggest adding to the page footer the version for which the documentation was generated, this would prevent us from having to compare the doc’s generation date and the version’s release date.
Yes, we know that, we have introduced some newer APIs since version 4.4.2. We will update the API Reference section in the documentation soon.
- Concerning the StyleFlag class to use when applying a style, when using StyleFlag.All, is the existing cell format overriden with the new format OR are the two combined ? If overriden, is there an easy way to combine two styles by “appending” the format defined by a second style while keeping the format defined by the first style.
If you want to combine two styles or update an existing style, you had better use / call Style.Update method after retrieving and modifying a style object.
E.g…,
Workbook workbook = new Workbook();
//In the book1.xls,the range "A1:C8" has been applied Named style "Percent"
workbook.Open(@"d:\test\StyleDemo\Book1.xls");
Style style = workbook.Styles["Percent"];
style.Number = 14;
style.Font.Color = System.Drawing.Color.Red;
style.Update();//the style of range "A1:C8" will be changed too.
workbook.Save(@"d:\test\Book_styledemo.xls");
- I can’t seem to understand why StyleFlag is a class instead of an enum type with FlagsAttribute ? Wouldn’t it be simpler to use Cells.ApplyRowStyle(someStyle, StyleFlag.NumberFormat | StyleFlag.FontBold); ?
Well, StyleFlag is not a class but a struct which has different set of fields related different style attributes you want to implement.
Thank you.
Hi, it’s been some time so maybe I should have started a new thread…
The answer you provided does not exactly answer my question. I really want to combine two styles to format one cell, not update a style definition. All other cells that use the same individual styles must not be updated…
Let’s say I have four styles : Total, SubTotal, Cash and Date. Each style is used on its own from time to time, but some may be combined (like SubTotal and Cash). How can I combine two styles to format one cell? I’d like to avoid defining all style combinations like SubTotalCash, SubTotalDate, TotalDate, TotalCash.
I’m trying to work something out with xlsCell.SetStyle(), but since is doesn’t take a StyleFlag parameters, I always end up overriding my previous formatting.
Can this be done ? Thanks
Dominic.
using Aspose.Cells v4.5.1.0
Hi,
Thank you for considering Aspose.
Well, I am afraid you have to combine the two style formats into a new style and then apply that to your cell.
Thank You & Best Regards,
Hi Dominic,
Well, I think you may try to achieve it as described in the following if it suits your need. May the following sample codes help you for your need, kindly refer to it to combine two styles to format the cells.
Sample code:
1).
Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
cells["A1"].PutValue("abc");
cells["D1"].PutValue("def");
Style style1;
style1 = workbook.Styles[workbook.Styles.Add()];
style1.Name = "Style1";
style1.Font.Name = "Calibri";
style1.Borders.SetStyle(Aspose.Cells.CellBorderType.Thin);
cells["A1"].SetStyle(style1);
Style stl = workbook.Styles[workbook.Styles.Add()];
stl.Name = "Stl";
stl.Copy(style1);
stl.Font.IsItalic = true;
stl.ForegroundColor = Color.Yellow;
stl.Pattern = BackgroundType.Solid;
cells["D1"].SetStyle(stl);
workbook.Save(@"f:\test\cstyles_test1.xls");
2).
Workbook workbook = new Workbook();
Cells cells = workbook.Worksheets[0].Cells;
Range range = cells.CreateRange("A1", "D3");
Style style;
style = workbook.Styles[workbook.Styles.Add()];
style.Font.Name = "Calibri";
style.Borders.SetStyle(Aspose.Cells.CellBorderType.Thin);
StyleFlag flag1 = new StyleFlag();
flag1.FontName = true;
flag1.Borders = true;
range.ApplyStyle(style,flag1);
Range range2 = cells.CreateRange("C10", "E13");
Style stl;
stl = workbook.Styles[workbook.Styles.Add()];
stl.Font.IsItalic = true;
stl.ForegroundColor = Color.Yellow;
stl.Pattern = BackgroundType.Solid;
StyleFlag flag2 = new StyleFlag();
flag2.FontItalic = true;
flag2.CellShading = true;
range2.CopyStyle(range);
range2.ApplyStyle(stl, flag2);
workbook.Save(@"f:\test\combinestyles_test.xls");
Thank you.
Thanks, I’ll try to work something out with all this.
1.
Why does Cell.SetStyle() doesn’t take a StyleFlag parameter like Cells.ApplyStyle() ?
2.
This brings me back to the another point about StyleFlag. I like to keep my style defintion separate from my data outputing code. How can I know what StyleFlag to use for a particular style ?
For example, my SubTotal style has a border and IsBold = true, but I may very well decide to add a ForegroundColor later on. Using ApplyStyle() and StyleFlag, I need to know in my “data outputing code” exactly how the final style will look like in order to know what StyleFlag to use. This is not very practical. I’d like to do something like this :
Workbook workbook = new Workbook();
Style style1;
style = workbook.Styles[workbook.Styles.Add()];
style.Font.Name = “Calibri”;
style.Borders.SetStyle(Aspose.Cells.CellBorderType.Thin);
Style style2;
style2= workbook.Styles[workbook.Styles.Add()];
style2.Font.IsItalic = true;
style2.ForegroundColor = Color.Yellow;
style2.Pattern = BackgroundType.Solid;
// …
Cells cells = workbook.Worksheets[0].Cells;
Range range = cells.CreateRange(“A1”, “D3”);
range.ApplyStyle(style1, style1.GetStyleFlag()); // returns the minimal style flags for this style
range.ApplyStyle(style2, style2.GetStyleFlag());
workbook.Save(@“f:\test\combinestyles_test.xls”);
Take it as a suggestion to forward to the dev team.Thanks.
Hi,
Thanks for further queries.
1). Well, you may use Range.ApplyStyle() instead which does have styleflag struct as an argument.
The Cells.ApplyStyle() method is used to apply the formattings for all the cells in the worksheet, If you want to apply formattings for a single cell, use Cell.GetStyle() to get its previous style object and then set your desired formattings for it (it will replace the previous formattings, mind you.) to apply the style to the cell using Cell.SetStyle(style).
2). I think since you should know which property you want to set for formattings, which further attributes you want to add/apply, so, set the StyleFlag accordingly, it is easy to use, is not it?
Thanks for the reply.
1) In my opinion, it’s a matter of consistency across the API. If using StyleFlag is the prefered method, it should be the used in Cell.SetStyle(), in Range.ApplyStyle(), Row.ApplyStyle(), etc. Using a range to modify a single cell’s style is not very intuitive, doing at Get/Update isn’t consistent with the StyleFlag concept use elsewhere.
2) Again, in my opinion, this does not promote clean and maintainable code. For one style, you have to carry over the style name or index and the StyleFlag to apply that style. There is no way to know what StyleFlag to use for a particular style. If my styles are setup by the user via some config menu, I cannot know what style flags to use except by memorizing them and passing them along from the config menu to each function using the style.
Your code samples often assume that the style defintion is done a few lines before its usage. This is not true in most real-world scenario. My style definition is done in its own function and the usage is spread out over the whole document. I end up doing wrapper classes that hold the Style and the StyleFlag associated with that style and pass that along to each function…
Take it as a suggestion to improve your users experience with your product. I like Aspose very much, but there is always room for improvements.
dstj.
When you set a style to a cell, we apply all settings in the Style object. It's very fast for our product and you don't need to care about the flag issues.
However, applying a range of cells is a totally different thing. For example, there are 3 cells in a worksheet: A1,B1,C1. A1 is Arial, normal, B1 is Arial Bold and C1 is Time New Roman normal. In your program, you want to change the background color to yellow.
We provide Range.ApplyStyle method to allow to make it. You use StyleFlag to notify our component to only apply background color settings.
We don't know what settings you want to change. It totally depends on your decision.
For you program, I think you can make a wrapper class like this:
public class StyleSetting
{
private Style m_Style;
public Style Style
{
get
{
return this.m_Style;
}
set
{
this.m_Style = value;
}
}
private StyleFlag m_Flag;
public StyleFlag Flag
{
get
{
return this.m_Flag;
}
set
{
this.m_Flag = value;
}
}
}
Then you can encapsulate style settings with its flag.