Hello, i have an excel file with a column containing some dates in european format DD/MM/YYYY, when convert it into images or pdf this column values are changed into format MM/DD/YYYY
(test file in attachment)
why? is there an option to avoid date reviews leaving original values unchanged?
thanks a lot
Hi Valerio,
Thank you for sharing the sample spreadsheet. Please note, your provided sample is using region based format for the data values, that is; the format will change according to operating system’s region/country. For instance, if I load this sample in Excel with region of the operating system set to US, the date shows in a format MM/DD/YYYY whereas the same value will appear as DD/MM/YYYY if operating system’s region is set to Italy. Please check attached snapshots for your reference.
That said, if you are seeing the date format as MM/DD/YYYY in the resultant PDF or images, that means your operating system is set to use US as your country (or any other country where similar data format is being followed). If you wish to display the date format as DD/MM/YYYY, you have to set the WorkbookSettings.Region property to Italy as demonstrated below.
C#
var workbook = new Workbook(dir + “test.xlsx”);
workbook.Settings.Region = CountryCode.Italy;
workbook.Save(dir + “US-locale-Italy-Settings.pdf”);
var worksheet = workbook.Worksheets[0];
var render = new SheetRender(worksheet, new ImageOrPrintOptions());
render.ToImage(0, dir + “US-locale-Italy-Settings.png”);
workbook.Settings.Region = CountryCode.Italy;
workbook.Save(dir + “US-locale-Italy-Settings.pdf”);
var worksheet = workbook.Worksheets[0];
var render = new SheetRender(worksheet, new ImageOrPrintOptions());
render.ToImage(0, dir + “US-locale-Italy-Settings.png”);
hum this is a problem 'cause our customers can upload documents in any coutry but the server is in one location…
any option that leave data in the original format?
in this scenario a customer in, let’s say, Italy uploads a document with DD/MM, than views a preview made with aspose online with MM/DD and then again download the original document with DD/MM
Hi again,
Thank you for your response. Unfortunately, I cannot suggest any other solution to deal with this problem. Reason being, the Excel’s built-in number formats are supposed to change according to the region. This is also mentioned in Excel’s Format Cell dialog for all formats having asterisk at the start. Please check attached snapshot for your reference. One solution could be to use custom formats while formatting contents in a spreadsheet, as custom formats remain the same regardless of the region. I believe this is not in your control as you cannot force your customers to use only custom formats. However, you can somehow determine the region from where a particular customer belongs, and assign the WorkbookSetting.Region property accordingly while converting his samples. This will ensure that the converted document contains the same formatting as shown to the customer while viewing the spreadsheet in Excel application on his side.