linkedinfacebooktwittermenuarrow-up

GemBox Support Forum

Html to excel date format always append with time

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);

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

yes, that’s works perfect, thank you!

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?

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

thanks, that’s a solution