Home Products Support Corporate

Support Knowledge Base, Article 198

Product

ExcelWriter

Version

All

Title

Preserving leading zeros with ExcelWriter COM

Problem

How do I preserve leading zeros in my data when populating a spreadsheet using ExcelWriter COM?

Solution

The recommended method for retaining leading "0"s in "string" data, where required, is to format the cell or cells that contain these "strings" as "text". This can be accomplished by setting the "Format.Number" or "Style.Number" properties to the pre-defined format for "text" (value of 49 or "@").

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

<%
Set xlw = Server.CreateObject("Softartisans.ExcelWriter")
Set ws = xlw.Worksheets(1)	
   ws.name = "My Worksheet"
	
   ws.Cells("A1").Value = "012345"
	
   ws.Cells("A1").Format.Number = "@" 'or use the value of 49 for 
                                      'pre-defined "text" formatting
%>

Here is a sample which demonstrates how to use "Style.Number" to retain the leading "0"s in a whole column (A or 1) of cells:

<%
Set xlw = Server.CreateObject("Softartisans.ExcelWriter")
Set ws = xlw.Worksheets(1)	
   ws.name = "My Worksheet"

'--- Create a TextStyle which will retain leading "0"s in "string" data
Set TextStyle = xlw.CreateStyle
   TextStyle.Number = "@" 'or use the value of 49 for pre-defined "text" formatting
		
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
%>