1

Scenario: I am trying to export a html table to Excel file in a Web Application (website hosted online) for clients to download to their computers.

All data exported as expected, except 1 problem.

The HTML table web page is saved with file extension of XLS.

Below is the sample html page that saved as XLS file:

<html>
<head>
</head>
<body>
<table>
<tr><td>1/42</td></tr>
</table>
</body>
</html>

After openning this file in Excel (2019 version), the value of

1/42

is changed to

01/01/1942

and appears as

Jan-42

in Excel cell.

I have tried to prefix the value with single quote '.

but then the value displays as

'1/42

in Excel cell.

How to make it appears as

1/42

correctly without the single quote and avoiding the Excel auto-reformatting it into Date, and without the need of changing the Excel cell Value format.

Is there anything that I can do during writing the HTML documents?

Excel auto date formatting

mjb
  • 7,649
  • 8
  • 44
  • 60

3 Answers3

0

I would open the data in notepad first. Then open excel, change column A to a TEXT format. Copy the data from notepad, then paste into excel using PasteSpecial>Values. This should get you what you need.

Mr. Data
  • 71
  • 6
  • Hi, thanks for the solution. But however, due to the scenario where the file has to be pre-written for client to download, it is not ok for requesting the client to manually perform the task in notepad. I have further edited my question to include this scenario. – mjb Jun 17 '19 at 02:24
  • As far as I know excel will always auto-format numbers. You can import the file from text and/or using your '1/42 symbol use vba code upon open to do a find/replace while retaining the format. I can write the vba code for you if you like. – Mr. Data Jun 17 '19 at 02:28
  • Hi, thanks for the tips. I have found another simpler solution, simply by adding CSS class and this will tell Excel to force to format the cell accordingly. I have post it as another answer. Thanks for your help anyway. – mjb Jun 17 '19 at 02:45
0

Answer Taken from:
https://stackoverflow.com/a/4620023/520848
HTML to Excel: How can tell Excel to treat columns as numbers?

Formatting can be applied to the cells for numbers, text, dates, etc.

by adding CSS class:

.num {
  mso-number-format:General;
}
.text{
  mso-number-format:"\@";/*force text*/
}

add the class to the cell

<td class="num">34</td>
<td class="num">17.0</td>
<td class="text">067</td>
mjb
  • 7,649
  • 8
  • 44
  • 60
0

When you import the html, click Transform Data and in the power query editor select the column and change the type to text, and then close&load

Emad Habahbeh
  • 1