Html to excel date format always append with time

GemBox.Spreadsheet
1

Hi,
I’m trying to convert html to excel, one column is date for example, 7/20/2022, it’s always been translated to 7/20/2022 0:00, is there a way to not show time?
my code:

var htmlOptions = new Excel.HtmlLoadOptions();
            //var html = GenerateHTML(data, columns);
            var html = @"<table>
                <thead>
                <tr>
                <th style=""width:200px"">
                Date
                </th>
                </tr>
                </thead>
                <tbody>
                <tr><td>7/20/2022</td></tr>
                </tbody>
                </table>";
            var htmlStream = new MemoryStream(htmlOptions.Encoding.GetBytes(html));

            var workbook = Excel.ExcelFile.Load(htmlStream, htmlOptions);
            var xlsxStream = new MemoryStream();
            workbook.Save(xlsxStream, Excel.SaveOptions.XlsxDefault);
2

Hi Chandler,

Try specifying the ‘mso-number-format’, like this:

<tr><td style="mso-number-format:M/dd/yyyy">7/20/2022</td></tr>

Does it solve your issue?

Regards,
Mario

3

yes, that’s works perfect, thank you!

4

I got one issue:
value is 05/09/2022, then excel will show as 09/05/2022, but 7/20/2022 is show correct value in excel

            var html = @"<table>
                <thead>
                <tr>
                <th style=""width:200px"">
                Date
                </th>
                </tr>
                </thead>
                <tbody>
                <tr><td style=""mso-number-format:M/dd/yyyy"">5/09/2022</td></tr>
                </tbody>
                </table>";

by the way, is there a way to specify wrap text in html?

5

Hi Chandler,

The problem occurs because HtmlLoadOptions.DateTimeFormats contains date formats where the day can come before the month and where the month can come before the day.

However, the “day-before-month” formats are placed before the “month-before-day” formats and thus that value ends up being successfully parsed with the “day-before-month” format.
So, one way to resolve this would be to insert the “M/dd/yyyy” value at the beginning of the HtmlLoadOptions.DateTimeFormats collection.

Or you could use this latest bugfix:
https://www.gemboxsoftware.com/spreadsheet/nightlybuilds/GBS49v1067.zip
Or this latest NuGet package:
Install-Package GemBox.Spreadsheet -Version 49.0.1067-hotfix

This latest version will first try to parse the value with the provided ‘mso-number-format’.

Regarding the wrap text, the easiest way would be to place the <br> element inside the cell.

I hope this helps, let me know if you need anything else.

Regards,
Mario

6

thanks, that’s a solution