SoftArtisans OfficeWriter

When Excelwriter is used to add a numeric string value to a single cell or to add numeric strings to an entire column in a worksheet, these values will be displayed in Excel's "General" format unless otherwise specified. This format will align values to the right and trim leading zeros. If, for example, you enter zip codes in a column of cells when the cell format is "General," leading zeros will be lost.

The recommended method for retaining leading zeros in numeric strings is to format cells that contain numeric strings as text. This can be accomplished by setting:

If you set Cells.PreserveStrings to true, all numeric strings in the generated spreadsheet will be formatted as text. To format an individual cell value as text, set the cell's Style.Number or Format.Number property to 49 or @.

Here is an example which demonstrates how to use Format.Number to retain the leading zero in a single cell ("A1"):

<%
Set xlw = Server.CreateObject("Softartisans.ExcelWriter")
Set ws = xlw.Worksheets(1)	
...	
ws.Cells("A1").Value = "012345"	
'--- Set Format.Number to "@" to format the cell 
'--- value as text.  Alternatively set Format.Number 
'--- to 49.
ws.Cells("A1").Format.Number = "@" 
...%>

Here is an example which demonstrates how to use Style.Number to retain the leading zeros in a whole column (A or 1) of cells:

<%
Set xlw = Server.CreateObject("Softartisans.ExcelWriter")
Set ws = xlw.Worksheets(1)	
...
'--- Create a TextStyle which will retain leading "0"s in 
'--- numeric strings.
Set TextStyle = xlw.CreateStyle
'--- Set TextStyle's Number property to "@" to format the cell 
'--- values as text.  Alternatively set Format.Number 
'--- to 49.
TextStyle.Number = "@" 
	
FirstRow = 1
FirstCol = 1
 
'--- Paste the Recordset into the Worksheet.
NumRows = cells.CopyFromRecordset(oRs, TRUE, FirstRow, FirstCol)
For r = FirstRow+1 to NumRows+1
	Cells(r, FirstCol).Style = TextStyle 
Next
...%>

Preserving Leading Zeros in Numeric Strings