I’ve created charts which presents the flucuation of values of over. The dates are
displayed correctly with Swedish date formating in Excel, but Aspose always seems
to render the date format according to the US standard. How do I resolve this issue?
Can Aspose.Cells display other date formats the US format in charts?
dateformat.zip (225.8 KB)
@patjep,
You may try setting the desired culture info in the load options and use it while loading the workbook. This same date format will be rendered in the chart as well. Please give it a try and share the feedback.
var culture = new CultureInfo("sv-SE");
culture.NumberFormat.NumberDecimalSeparator = ",";
culture.DateTimeFormat.DateSeparator = "-";
culture.DateTimeFormat.ShortDatePattern = "dd MMMM yyyy";
LoadOptions options = new LoadOptions(LoadFormat.Html);
options.CultureInfo = culture;
Workbook workbook = new Workbook("dateformat_report.xlsx",options);
workbook.Worksheets[0].Charts[0].ToImage("output.png");Thank you for your suggesting, I will try it shortly. Not that it matters too much but I would
prefer examples in C++ since I tagged the thread as a c++ thread. Either way, is there a
way to parse this information from the file itself? I cannot know beforehand if the customer
prefers Swedish, German, US, Russing or Chineese formatting. While it is possible to enter
this information before we parse the Excel file, its not a neat solution. Is it possible to let Aspose
find this information in the file, somehow?
I tried your example, after converting it to C++, and it kind of seems to work. The date pattern format doesn’t function the way I imagined but it seems reasonable to that might be possible to make it function properly for us. Still, another solution that simply reads the settings from the Excel file would be way better.
@patjep,
About the solution related to reading settings from Excel file, we are afraid there is no such information (locale/regional settings) in most of the excel files.
For Xls files, there may be some regional settings data saved in the template file and we do formatting according to that data. However, for other file formats such as Xlsx, there is no such data. And even for Xls files, the regional settings may not always exist.
What I meant is that it is possible to edit the datetime format on most charts and other objects. If Aspose.Cells could read that information, as showed in my screenshots, that would completely solve
my issue. That information is definitely in the Excel file since its configured via Microsoft Excel.
For your information, MS Excel also formats the DateTime according to the environment’s regional settings (same is the case with Aspose.Cells APIs).
Please note, you got to specify the right region, Aspose.Cells can give the expected results (this is where the default region of the environment when running the program with Aspose.Cells is not the right/expected one).
Moreover, please note, Aspose.Cells gets the regional settings when running the program is just the default one (of the OS, or current thread). You have to always specify the expected one by yourselves if it is different from the default one. Also, if your application is client-server based, since Aspose.Cells is server based APIs, so it will count settings of the system (os) where it was processed and not evaluate client-side settings.
By the way, by setting the numbers formatting as Custom string instead of built-in numbers also can solve your issue because custom formattings do not depend upon regional settings/locales, these are independent.
Yes, I can see what it would be necessary to specify the regional settings manually to set a global datetime format standard. What seems to be the best fit for me at the moment is to use the custom string formats instead. Can you update my example file to reflect how you would solve this issue to achieve the datetime format I wish to use?
You got to format axis values using custom formatting string “yyyy-mm-dd;@” for the chart (you may also do it in MS Excel for the template file), it may suit your needs.
See the sample lines of code:
e.g.
Sample code:
...........
//Get the first chart in the first worksheet
Chart chart = workbook.Worksheets[0].Charts[0];
chart.CategoryAxis.TickLabels.NumberFormat = "yyyy-mm-dd;@";
........
Hope, this helps a bit.
Tried entering “yyyy-mm-dd;@” as the number format code from within
Microsoft Excel and it doesn’t seem to have any effect from what I can see.
Could you check and try the attached updated Excel file where I have set the custom formatting accordingly. Please try it and let us know if you still find any issue.
dateformat_report1.zip (109.1 KB)