Hi Team,
We are currently facing a strange issue when working with dates in excel.
So while exporting data to excel we are applying ‘dd/mm/yyyy’ custom format on the column so that it displays in the same format on every machine having different culture. Now we have a functionality where we Import the sheet and insert the binary content in table.
When we are trying to create the workbook using the binary content again, the cell format is getting changed to the machine’s culture. This happens only when the culture format on machine itself is ‘dd/mm/yyyy’ and with other cultures it works fine. Could you guys please help with this issue?
Thanks for your query.
Please provide sample code (runnable) and sample files to reproduce the issue on our end. Also, elaborate your issue further and provide details about your environment including OS, locale settings, culture info and MS Excel version that you are using. All this will help us to analyze your issue properly and to assist you better to figure out your issue soon.
Sure, will provide you all details in some time. thanks
Please take your time to create and provide the sample to reproduce the issue, we will check it soon.
Dear Amjad,
Please find below the required details, hope it is sufficient for you to check.
We are looping thorugh every cell to set the below custom format.
var style = cell.GetStyle();
case CellStyleType.Date:
style.Custom = “dd/MM/yyyy”;
break;
The exported sheet is displaying the data in correct format, but there is a difference is the cell style format.
When the culture setting is anything except ‘dd/mm/yyyyy’ the format is shwoing Custom - ‘dd/mm/yyyy’.
But when the culture setting is kept as ‘dd/mm/yyyy’ the cell format style is ‘Date’–> *14/03/2012 instead of custom - ‘dd/mm/yyyy’.
Given below detailed culture settings(local machine)
Format Tab
Format -> English united states
ShortDate --> dd/mm/yyyy
LongDate -->dddd,d MMMM,yyyy
HomeLocation --> Belgium
Server settings -->
Format --> English(united states)Match windows display language
ShortDate -->m/d/yyyy
HomeLocation --> United states
So after exporting we save the sheet’s binary content in Database.Then using the below code to generate the worksheet again from binary content.
using (var stream = new MemoryStream(blob.BinaryContent))
using (var workbook = new Workbook(stream))
{
var worksheet = workbook.Worksheets[0];
var worksheetCells = worksheet.Cells;
}
So when I check the cell value now where we had date it behaves differently.
For sheets exported in culture different then ‘dd/mm/yyyy’ it gives the desired output. But if it is exported with local settings ‘dd/mm/yyyy’ as mentioned in detail above. It changes the value as per the server’s culture format.
So 29/04/2020 is getting changed to 04/29/2020 in this case.
Excel version --> 2016(16.0.4966.1000)64-bit
Thanks for the details.
We evaluated our scenario/ case a bit. Well, it is MS Excel’s behavior for setting custom pattern and nothing to do with Aspose.Cells by any means. You may manually confirm your issue using only MS Excel. For example, if you set the custom pattern as the short date format, it always takes the format as built-in format.
However, we also think, for your situation, you may try to use code like:
style.Custom = "dd\"/\"MM\"/\"yyyy";
to accomplish your task and it may work for your needs.
In the event of any further query or other issue, do let us know with details, we will be happy to assist you soon.
Ok, Thanks for looking into this. 
Hi Amjad,
Thanks for your suggestion, I used the given custom format and it did work.
Would it be possible for you to explain how does it make a difference as compared to previous custom setting style.Custom = “dd/MM/yyyy”.
Because I could not find anywhere why style.Custom = “dd/MM/yyyy” gives issue and
style.Custom = “dd”/“MM”/“yyyy”;should be used.
Thanks in advance already.
Good to know that your issue is sorted out by the suggested line of code.
Moreover, we will provide you more details why you should use the suggested custom format instead. We will get back to you soon.
Thanks Amjad,
I would be really glad to get some explanation around it as I also need to discuss this within.
For your question about the difference of dd/MM/yyyy and dd”\”MM”\”yyyy, well it is also MS Excel’s behavior. We think it is because the pattern dd/MM/yyyy is just same with the built-in pattern. If you specify the number format as built-in date, and then press the “Custom”, you will find the pattern is just dd/MM/yyyy.