The following code fragment throws NullReferenceException:
Style style = new CellsFactory().CreateStyle();
style.SetCustom("@", true);To check whether one given custom pattern can be taken as built-in, we need to know some global settings of the Workbook. For example, for different regions, the formatting patterns of the same built-in number are different. There is no such kind of region information in the Style object got from CellsFactory.
So, please create the Style object from Workbook instance instead of the CellsFactory. In fact, it is recommended that user should use the Style object got from other models such as Workbook, Cell, … always if possible. It is because many settings of the Style require other global settings.
It’s just a blank Workbook created using “new Workbook()”. There is nothing special.
I still don’t think it’s appropriate to throw a NullReferenceException in this case.
Also, how do I get a style object without any explicit style properties?
You can get one Style object from a blank Workbook as below:
Workbook wb = new Workbook();
Style style = wb.CreateStyle();
style…
Is a style created using wb.CreateStyle() based on wb.DefaultStyle or something else?
Yes, the created Style is based on the default style of corresponding Workbook.
For example, for different regions, the formatting patterns of the same built-in number are different.
I’m actually having trouble to get region formats to work correctly. Using the following code, I’m not getting the “Accounting” format for Japanese. The number format still uses $ like an en-US document.
How do I correctly change the region of the workbook?
var culture = new System.Globalization.CultureInfo("ja-JP");
Thread.CurrentThread.CurrentCulture = culture;
var wb = new Workbook();
// Not sure which one is needed, but none of them seem to work.
wb.Settings.LanguageCode = CountryCode.Japan;
wb.Settings.Region = CountryCode.Japan;
wb.Settings.CultureInfo = culture;
Worksheet sheet = wb.Worksheets[0];
Cell cell = sheet.Cells["B2"];
cell.PutValue(1.23);
Style good = wb.CreateBuiltinStyle(BuiltinStyleType.Good);
cell.SetStyle(good);
Style cellStyle = cell.GetStyle();
cellStyle.Number = 44;
cell.SetStyle(cellStyle);
wb.Save("Demo2.xlsx");Please note, ms excel always formats numbers according to the regional settings of the environment where it is in, no matter what kind of region you have specified to the workbook, or saved to the template file.
Changing the regional settings for Workbook mainly affects the output in your application with Cells component, such as when you getting Cell.StringValue, or rendering the content of the workbook to other formats such as pdf, images, …etc.
For your code, if you output Cell.StringValue:
Console.WriteLine(cell.StringValue);
You will find the differences for different regions.
Thanks Johnson, but I’m not sure if I’m following your answer. I suppose it makes sense for decimal points and thousand separators, but it does not make sense for Currency and Accounting formats.
I opened the workbook in a Japanese Excel on a Japanese Windows, but the number still shows $ instead of ¥. On the other hand, the Japanese document I created in Japanese Excel, shows ¥ in my English (U.S.) Excel.
I don’t think Excel will all of a sudden show a $500 cell as ¥500 just because the workbook has been opened on a different machine.
Basically, I need to create workbooks for different regions based on the client’s request. It’s not realistic to change the machine region after serving each request.
@bingxie,
We cannot find the issue with the generated xlsx file by your code. Please see screenshots when we opening it in ms excel with different region settings:
When we set the system region as en_US, ms excel shows:
When we set the system region as jp_JP, ms excel shows:
@johnson.shi Did you by any chance re-run the code (which generated a new file) after changing your region? We just tested this again. What you described doesn’t match the behavior I’m seeing.
I have attached two files to this post.
One file was generated in Aspose.Cells when my region was set to en-US. Even if I change my machine to ja-JP, the file still shows $1.23 in Excel. If you open this file in Japanese-region Excel, it will show the dollar sign.
The other was generated under the region ja-JP. The file shows ¥1.23 in Excel even if the region is changed back to en-US. If you open this file in U.S.-region Excel, it still shows the yen symbol
Demo2.zip (13.5 KB)
@bingxie,
It is so strange that you can get different views for those two files. Please see the screenshots here I got in ms excel:
When the region of my machine is en-US:
And when the region of my machine is jp-JP:
Please make sure you are using the same machine and same ms excel instance to open those two files?
Thanks for the update. We are indeed seeing inconsistent behavior on different machines. On one machine, we are seeing the behavior we reported earlier. On the other machine, we are seeing the behavior you described.
We decided to work around this issue using custom formats with explicit regional settings. Although it’s cumbersome to manage region-specific formats for 15 regions (since Aspose does not have those built-in settings), it should solve our problems. I have attached two workbooks for your reference.
Demo4.zip (13.7 KB)
@bingxie,
Yes, for your two files in Demo4.zip, we saw different formatted result in ms excel and it is reasonable because they have been set with different regions and patterns for the cell’s Style.
However, for the two files in Demo2.zip, we cannot find any difference in ms excel. And even when we checking the saved data in the two files, we cannot find any difference for the styles set to cell B2.
If you saw different results on different machine, maybe on some machine the settings of the system or ms excel are special or even incorrect. But it still seems too strange or even impossible to us that the same ms excel instance can give different result for the two files in Demo2.zip.
Anyways, pleased to know you find a solution to solve such kind of issues for your current requirement.