Leading 0 is dropped from excel data cells populated from VB

Question

In my vb Windows Application Program I use this code to create excel report:

Public Sub CreateExcelFile(ByVal InputDataTable As DataTable, ByVal FileName As String)

    Dim ExcelApp As New Microsoft.Office.Interop.Excel.ApplicationClass
    Dim ExcelWorkbook As Microsoft.Office.Interop.Excel.Workbook = Nothing
    Dim ExcelWorkSheet As Microsoft.Office.Interop.Excel.Worksheet = Nothing
    Dim ColumnIndex As Integer = 0
    Dim RowIndex As Integer = 1
    Try
        ExcelWorkbook = ExcelApp.Workbooks.Add()
        ExcelWorkSheet = ExcelWorkbook.ActiveSheet()
        For Each c As DataColumn In InputDataTable.Columns
            ColumnIndex += 1
            ExcelApp.Cells(RowIndex, ColumnIndex) = c.ColumnName
        Next
        For Each r As DataRow In InputDataTable.Rows
            RowIndex += 1
            ColumnIndex = 0
            For Each c As DataColumn In InputDataTable.Columns
                ColumnIndex += 1
                ExcelApp.Cells(RowIndex, ColumnIndex) = r(c.ColumnName).ToString
            Next
        Next
        ExcelWorkSheet.Columns.AutoFit()
        ExcelWorkbook.SaveAs(FileName)
        ExcelWorkbook.Close()
        ExcelApp.Quit()

    Catch ex As Exception
        MsgBox("Err", MsgBoxStyle.Information + MsgBoxStyle.MsgBoxRtlReading)
    Finally
        ExcelApp = Nothing
        ExcelWorkbook = Nothing
        ExcelWorkSheet = Nothing
        ColumnIndex = Nothing
        RowIndex = Nothing

    End Try
End Sub

if I have a code or telephone number that has a 0 as the first character, it does not show in excel file . I use this code to solve the problem:

 ExcelWorkSheet.Activate()

      ExcelWorkSheet.Cells().Columns.NumberFormat = "@"
      ExcelWorkSheet.Cells().EntireColumn.NumberFormat = "@"

but it doesn't work. I read this question but cannot solve my problem:

Format an Excel column (or cell) as Text in C#?

Set data type like number, text and date in excel column using Microsoft.Office.Interop.Excel in c#

Insert DataTable into Excel Using Microsoft Access Database Engine via OleDb

Have you tried this? http://stackoverflow.com/a/10002195/934912 — StillLearnin, Jul 25 '15 at 13:05
And did you set the format before adding the data? http://stackoverflow.com/a/20832641/934912 — StillLearnin, Jul 25 '15 at 13:08
In my first comment I was trying to point out the "prefixing apostrophe" approach. — StillLearnin, Jul 25 '15 at 13:10

score 1 · Accepted Answer · answered Jul 25 '15 at 14:00

Setting the format of a cell after you have data in it does not always work because some data may have been lost on entry in the conversion to the original data format. Make sure you set the format of the cell first so that any leading zeros do not get truncated.

Leading 0 is dropped from excel data cells populated from VB

1 Answers1