OWC10 - Formatting issues

[KLomax]

I am attempting to format some columns as text.

The method below writes a simple excel sheet to disk from
an aspx web page. It uses the "Office Web Components"
object.
The problem is that I am writing "0012" to Cell(A2). If
you open the sheet that is written out, the formating has
been applied, but the leading zeros are truncated. For
some reason the values appear to be entered into the sheet
before the formatting is applied.

Any ideas?

Public Sub Test()
Try
Dim sFile As String = moWebServer.MapPath(".")
& "\Files\MyExcel.xls"
Dim XLS As New OWC10.SpreadsheetClass()
Dim Sheet As OWC10.Worksheet = XLS.ActiveSheet

Sheet.Range(Sheet.Cells(1, 1), Sheet.Cells(1,
1)).EntireColumn.NumberFormat = "#,##0.00"
Sheet.Range(Sheet.Cells(1, 2), Sheet.Cells(1,
2)).EntireColumn.NumberFormat = "@"

Sheet.Cells(1, 1) = "99.95"
Sheet.Cells(1, 2) = "0012"

XLS.Export(sFile, ssExportActionNone, ssExportHTML)

Catch exp As Exception
Dim sErr As String = exp.Message
sErr = exp.ToString
End Try
End Sub

 

[Sundeep Phatak]

Hi,
I have the encountered the same problem as you. Apparently, the OWC10
component doesn't support the '@' custom format. One way around is to
prefix the value with a single quote. so 00012 will go in as '00012.
This preserves the formatting.