Strange date format when converting to mhtml?

[rozeboosje]

Trying to convert an xlsx spreadsheet to MHTML, and finding strange issues with date formatting.

The code is trivial:

            GetCellsLicence()
            Dim oDoc As Aspose.Cells.Workbook = Nothing
            Try
                oDoc = New Aspose.Cells.Workbook(sFile)
                oDoc.Save(sToFileName, Aspose.Cells.SaveFormat.MHtml)
            Catch
            Finally
                If Not oDoc Is Nothing Then
                    Try
                        oDoc.Dispose()
                    Catch ex As Exception

                    End Try
                End If
            End Try
            oDoc = Nothing

Please use your own code to set the Licence where I put “GetCellsLicence()”
sFile should contain wherever you put the xlsx file (see Reply)
sToFileName should contain the location\filename.mhtml where you want to output the result

addendum
I just tried the same code but using Aspose.Cells.SaveFormat.Pdf - The same issue happens when saving the spreadsheet as a Pdf. So I think the issue - whatever it is - may be in the way it reads spreadsheet itself rather than in the Save function.

[rozeboosje]

test.zip (7.8 KB)

Please find attached a zip file containing an example spreadsheet.

The spreadsheet contains a number of dates formatted as follows:
10/10/2010
25/11/2011
25/10/2015
11/11/2012

However, when the MHTML is produced and I view it, I see the dates represented in a strange format. It would be ok if they were still valid date formats, but they don’t appear to be:

10/10 Oct 2010/2010

[rozeboosje]

I’m thinking this has something to do with date formats. I was able to get a slightly better result by first saving the document as an old format XLS, and then converting it the MHTML, but while the resulting dates are better, they are now presented in an American date format, e.g. 25/11/2011 (25 November 2011) is now presented as 11/25/2011 which makes no sense on this side of the Pond.

Added the code between KEYD-6367 Start and End

                oDoc = New Aspose.Cells.Workbook(sFile)

                'KEYD-6367 Start.
                Try
                    'This handles https://forum.aspose.com/t/strange-date-format-when-converting-to-mhtml/207765
                    'If Aspose ever fix this, we can stop doing this
                    oDoc.Save(sToFileName + ".xls", Aspose.Cells.SaveFormat.Excel97To2003)
                    Try
                        oDoc.Dispose()
                    Catch

                    End Try
                    oDoc = Nothing
                    oDoc = New Aspose.Cells.Workbook(sToFileName + ".xls")
                Catch

                Finally

                End Try
                If oDoc Is Nothing Then
                    'In case saving it as XLS didn't work
                    oDoc = New Aspose.Cells.Workbook(sFile)
                End If
                'KEYD-6367 End.

                oDoc.Save(sToFileName, Aspose.Cells.SaveFormat.MHtml)

[Amjad_Sahi]

@rozeboosje,

Please try our latest version/fix: Aspose.Cells for .NET v20.1 .

I have tested your scenario/ case a bit using the following sample code and it works fine, I do not see your mentioned issue in the output MHtml file (attached):
e.g
Sample code:

Workbook oDoc = new Aspose.Cells.Workbook("e:\\test2\\test.xlsx");
            oDoc.Save("e:\\test2\\out1.mhtml", Aspose.Cells.SaveFormat.MHtml);

If you still find the issue with v20.1.x, kindly do provide more details with environment info, output Mhtml, etc., we will check it soon.
files1.zip (5.7 KB)

[rozeboosje]

Thanks Amjad, I will try it with the latest version.

In the meantime, though, I wonder whether the issue only manifests itself in a Locale other than English US. Try switching the PC to British or Irish english… We use a dd/mm/yyyy format for dates. It could be that the issue is only present in such environments.

[Amjad_Sahi]

@rozeboosje,

Yes, we did try and checked with different region/datetime formats but similar Mhtml file is created which resembles with the source Excel file (when opened with MS Excel).

Please take your time to evaluate the issue using the latest version/fix. If you still find the issue, kindly do provide more details and output Mhtml, we will check it soon.

[rozeboosje]

@Amjad_Sahi

Ok… I have some very good news and a little bit of bad news.

The very good news is that with Aspose.Cells build 20.1 the output now shows the date in dd MMM yyyy format, e.g. 25 Nov 2010 - that is acceptable to us.

A little bit of bad news is:
Firstly, the format still doesn’t match the format in Excel itself. In excel the date is shown as 25/11/2010 not 25 Nov 2010. But since we are only using this to create a “preview” of the document, this is not a serious problem for us; the output, while not 100% perfect, is acceptable to us now

Secondly, I can now remove the additional code (see comment number 3) which is great, but I did notice that when that code is still in place I still get the American date format in the output. This is of no concern to us as we have now removed that code; we no longer need to first convert it from xlsx to xls before then converting it to mhtml, but you may want to investigate this as I suspect there may still be an issue with xls files.

So long story short: the move to 20.1 resolved the issue to our satisfaction, so we are happy, but the issue is not completely resolved and you should still investigate it further for the sake of other users.

In case this helps your investigation:

AsposeCellsBuild201.zip (10.4 KB)

Further to this: My PC’s language is set to English (Ireland) with an Irish keyboard. Region setting is “Match Windows display language”, short date is dd/MM/yyyy, long date is dd MMMM yyyy, all of these are default settings for Ireland.

Kind regards

Pino

[ahsaniqbalsidiqui]

@rozeboosje,
We are looking into this issue and will share our feedback soon.

[Amjad_Sahi]

@rozeboosje

For this your issues/problems:

1. In excel the date is shown as 25/11/2010 not 25 Nov 2010
   We could not find the issue at our end in the generated MHTML. Here, the date was shown as “25/11/2010” which is same with what is shown in MS Excel.

2. when that code is still in place I still get the American date format in the output
   When saving XLS file, the regional settings will be saved into the file data. If the regional settings have not been specified by you then the default region of the environment will be used. So, after saving and re-opening the XLS file into Workbook, the regional settings of the workbook will be the one saved into the file and is not changed according to the environment.

Thanks for your understanding!

[rozeboosje]

@Amjad_Sahi

To your comment 1. The thing is that in the original .xlsx the date is shown in dd/mm/yyyy format, but in the generated .mhtml the date is converted to dd MMM yyyy format… See the two files in the attached AsposeCellsBuild201.zip file in comment number 7.

If I look at the content of the .mhtml (using notepad) I see things like:

<td class=3d'x19' width=3d'247' x:num=3d"42302" style=3d'width:185.25pt;'>25 Oct 2015 </td>

and css styles

mso-number-format:"dd\/MMM\/yyyy";

In the .xlsx file I find values such as

<c r="C1" s="7"><v>40461</v></c>

I’m not sure how Aspose extracted the format dd MMM yyyy from the content of the .xlsx file

[Amjad_Sahi]

@rozeboosje,

Thanks for sharing further details.

We are still unable to reproduce the issue on our end. Could you take different screenshots to highlight your complete regional settings and language settings, OS details and MS Excel version with language specification, etc., we will log an investigation ticket for further evaluation of your issue.

Also, please confirm if you are using the same file (you attached in this thread) or some other file? If you are using some other file, kindly attach that file as well.

[rozeboosje]

@Amjad_Sahi

SystemSettings.zip (978.6 KB)

I used the .xlsx file included in comment number 7 on this thread, the one with the “good news” and the “bad news”

cheers

[ahsaniqbalsidiqui]

@rozeboosje,

mso-number-format:“dd/MMM/yyyy”;

We have tried this scenario at our end but MHtml file generated here, with all the possible settings according to the system settings in your video description, contains following information.

mso-number-format:“dd/MM/yyyy”;

We are still not able to observe this issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix(if required). Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-47103 – Different date format rendered while converting Excel to MHtml

[rozeboosje]

Thanks @ahsaniqbalsidiqui … it is a mystery alright

Do you know where it gets mso-number-format from when it generates the MSHTML file? Maybe it’s something in the registry? I’d be happy to look such values up in my own system if you wish.

[rozeboosje]

or perhaps it picks up a funky style sheet from somewhere? Something to do with CSS files?

See, for example: http://www.niallodoherty.com/post.cfm/basic-html-to-excel-formatting

[ahsaniqbalsidiqui]

@rozeboosje,
Thank you for providing more information. Please spare us little time to investigate this issue as right now there are few other tasks in the queue for analysis. We will analyse this issue on its turn and share our feedback.