CELL FORMATING

One of the most problematic areas that you will face when creating Excel files from ODS is with cell formatting. The problems are the same whether using the CSV or the HTML destinations. The problem occurs because Excel uses a General format to import cell values. The General format reads the cell values as they are typed, however, there are some common problems that you should be aware of. Both numeric and character variables will lose leading and trailing zeroes when creating Excel files with the ODS HTML and CSV destinations. You will not realize the problem until the leading or trailing zeroes are omitted from an account number, an ID, or a zip code.

NUMBER FORMATS

Importing the cells as text using the Text format for the cell values allow the cell values to come over without any interpretation and does not strip the leading or trailing zeroes. Using the mso-number-format:\@ style property allows the cell values to be imported using the Text format for Excel 2000 and above. For Excel 97, the style property is vnd.ms-excel.numberformat:@ . Below are examples of applying the Text format and the more common number formats.

<td style='mso-number-format:"\@"'>00123</td>

Print "<tr>" & _
"<td>" & pdoc.TaxYear(0) & "</td>" & _
"<td>" & StatusDisp & "</td>" & _
"<td>" & pdoc.Contractor(0) & "</td>" & _
"<td>" & pdoc.EmpFullname(0) & "</td>" & _
"<td style='mso-number-format:\@'>" & pdoc.EmployeeID(0) & "</td>" & _
"<td>" & pdoc.Service(0) & "</td>" & _
"</tr>"

Text Formatting for Excel 2000 and up
"<td style='mso-number-format:\@'>" & pdoc.EmployeeID(0) & "</td>" & _

Text Formatting for Excel 97 and up
"<td style='vnd.ms-excel.numberformat:@'>" & pdoc.EmployeeID(0) & "</td>" & _

COMMON NUMBER FORMATS
mso-number-format:0
mso-number-format:"0\.000"
mso-number-format:"\#\,\#\#0\.000"
mso-number-format:"mm\/dd\/yy"
mso-number-format:"mmmm\\ d\\\,\\ yyyy"
mso-number-format:"m\/d\/yy\\ h\:mm\\ AM\/PM"
mso-number-format:"Medium Date"	
mso-number-format:"d\\-mmm\\-yyyy"
mso-number-format:"Short Time"
mso-number-format:"Medium Time"
mso-number-format:"Long Time"
mso-number-format:Percent;
mso-number-format:0%
mso-number-format:"0\.E+00";
mso-number-format:"\@"