Excel cell Format Changes in text

Question

I have use microsoft Excel Sheet ..I've fell different Values in excel sheet there is no problem.. but i've fell some numeric Columns like this (00023785678).. in this columns first zero is not get it.. so i went to change the column in cells format of text..

how to create in Vb.net code.. already i have fell excel sheet this method...

       Dim oXL As Excel.Application
            Dim oWB As Excel.Workbook
            Dim oSheet As Excel.Worksheet
            Dim columnrange = oSheet.Columns
            Dim therange = oSheet.UsedRange
            ' Dim wb As Microsoft.Office.Interop.Excel.Workbook
            ''Dim style As Microsoft.Office.Interop.Excel.Style
            oXL = CreateObject("Excel.Application")
            oXL.Visible = True
            oWB = oXL.Workbooks.Add
            oSheet = oWB.ActiveSheet
            'oXL.Selection.num()
            oXL.Selection.NumberFormat = "Text"

            For c As Integer = 0 To dt.Columns.Count - 1
                oSheet.Cells(1, c + 1).Value = dt.Columns(c).ColumnName

            Next
            For rCnt As Int16 = 2 To therange.Rows.Count
                Dim rowArray(therange.Columns.Count) As String
                For cCnt As Int16 = 1 To therange.Columns.Count
                    Dim Obj = CType(therange.Cells(rCnt, cCnt), Excel.Range)
                    Dim celltext As String
                    celltext = Obj.Value.ToString
                    rowArray((cCnt - 1)) = celltext

                Next
            Next

            For r As Integer = 0 To dt.Rows.Count - 1
                For c As Integer = 0 To dt.Columns.Count - 1
                    oSheet.Cells(r + 2, c + 1).Value = dt.Rows(r)(c)
                    oSheet.Cells(r + 2, c + 1).numberformat = "0"
                Next
            Next
            'With oWB
            '    .Cells(seriesName.GetUpperBound(0) + 7, 3).numberformat = "#.00"
            '    .Cells(seriesName.GetUpperBound(0) + 7, 5).numberformat = "0"
            'End With


            'With oSheet.Range("A1", "ZZ1")
            '    .Font.Bold = True
            '    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
            '    .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
            '    .EntireColumn.AutoFit()
            '    .EntireRow.AutoFit()
            'End With

            Dim FirstRow As Long
            Dim SecentRow As Long
            Dim ThirdRow As Long
            With oSheet.Range("A1", "BD1")
                FirstRow = 1
                SecentRow = 2
                ThirdRow = 3
                .Rows(0 + 1).EntireRow.Insert()
                .Rows(0 + 1).EntireRow.Insert()
                .Rows(0 + 1).EntireRow.Insert()
                .Font.Bold = True
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter
                .EntireColumn.AutoFit()
                .EntireRow.AutoFit()

                '.EntireRow.TextToColumns()


                .Offset.Justify()
                .Offset.BorderAround()
                .Offset.WrapText = True
                .Offset.Select()
            End With

But right now idon't get the ans... to pls help Me ...

Ed Harper Ed Harper · Accepted Answer · 2009-12-14T12:51:18

Prefix the value in the cells you want to treat as text with a leading single quote '. This forces Excel to treat the cell as text, and the single quote will not be visible in the sheet.

Excel cell Format Changes in text

2 Answers