Also known as “Dear Excel, stop fucking changing my data!”.
As you may or may not know, if you need to create an Excel spreadsheet “on the fly” from a web server and the server doesn’t have Excel installed, and the server doesn’t have any Excel generating components installed, you can simply create HTML tables and Excel will parse and load that as an Excel file. Happy days.
Now for the bad news. Excel, just like the rest of Orifice likes to think for you and fuck with what you actually type into the document. Prime example: fractions.
If you send the following document to Excel:
<html>
<head><title></title></head>
<body>
<table>
<tr>
<td>1 1/16</td>
</tr>
</table>
</body>
</html>
Excel will gladly open the document with one column with a value of 1 1/16 in the first cell.
But if you highlight that cell, the data entry box at the top shows 1.0625. !@#$%.
To make matters worse, if you save the file, the value of 1.06525 is saved. If you try and import the file into a
database using an Excel drivers (OLEDB/SQL Server), 1.0625 is the value imported, not 1 1/16.
Needless to say this is frustrating. I just want Excel to leave my shit alone. Do what I tell you damnit!
Luckily after some tinkering, and saving Excel files as HTML documents, I found the answer/hack/solution.
One single CSS style declaration and the addition of a <col> tags can tell Excel to treate every fields as a
Text field rather than trying to “guess” what I want for me.
<html>
<head><title></title></head>
<style>
.leaveitalone {mso-number-format:"\@";}
</style>
<body>
<table>
<col class="leaveitalone">
<tr>
<td class="leaveitalone">1 1/16</td>
</tr>
</table>
</body>
</html>
Open the updated version in Excel, and Right Click → Format Cells on the first cell and whola, the format is Text instead of General. Now Excel will finally leave your data alone.