none
how to set my cell to text in export datagridview to excel in vb RRS feed

  • Question

  • hi every body

    i try set mu celss in excel to text

    but when i want to write '00002' in excel , then i seee '2'

    please help me

    CmdExport.Enabled = False
            pb.Visible = True
            MessageBox.Show("اجرا شدن این دستور نیاز به زمان دارد. لطفا صبر کنید", Text, MessageBoxButtons.OK, MessageBoxIcon.Warning)
    
            Dim oExcel As Object
            Dim oBook As Object
            Dim oSheet As Object
    
            'Start a new workbook in Excel.
            oExcel = CreateObject("Excel.Application")
            oBook = oExcel.Workbooks.Add
    
            'Add data to cells of the first worksheet in the new workbook.
            oSheet = oBook.Worksheets(1)
            oSheet._DisplayRightToLeft = True
            Dim rowsColor As Boolean = False
            'Dim cellColor As Boolean = False
    
            pb.Maximum = dgv.RowCount
            Dim I, j As Integer
            For I = 1 To dgv.ColumnCount
                oSheet.Cells(5, I).Value = dgv.Columns(I - 1).HeaderText
                oSheet.Cells(5, I).Interior.Color = Color.LightGreen
                oSheet.Cells(5, I).Font.Name = "B Zar"
                oSheet.Cells(5, I).borders.LineStyle = 1
                pb.Value = 0
                'rowsColor = False
                For j = 1 To dgv.RowCount
                    
                    oSheet.Cells(j + 5, I).Font.Name = "B Zar"
                    oSheet.Cells(j + 5, I).borders.LineStyle = 1
    'my problem is here iwanto to write like '00002' but it write '2'
                    Select Case I
                        Case 4
                            oSheet.Cells(j + 5, I).cellFormat = "Text" ' iwant set to text format
                        Case 6
                            oSheet.Cells(j + 5, I).cellFormat = "Text"'' iwant set to text format
                        Case Else
                            oSheet.Cells(j + 5, I).NumberFormat = "#,##0" 
                    End Select
    
    
                    If rowsColor = True Then oSheet.Cells(j + 5, I).Interior.Color = Color.AliceBlue
                    rowsColor = Not rowsColor
    
                    Try
                        Dim valval As String = ""
                        valval = dgv.Item(I - 1, j - 1).Value.ToString
                        If dgv.Item(I - 1, j - 1).Value.ToString = "True" Then valval = "*"
                        If dgv.Item(I - 1, j - 1).Value.ToString = "False" Then valval = ""
                        oSheet.Cells(j + 5, I).Value = valval
                    Catch ex As Exception
    
                    End Try
                    pb.Value = j
                Next
            Next
    

    Monday, April 22, 2013 8:00 AM

Answers

  • try writing it as text instead of number. you can check this behavior in excel directly without code. just open sheet and try writing 0002 (this is number, and leading zeros has no value in number) and then try '0002 (this is string/text)

    try '0002


    Faisal Ahmed Farooqui

    Tuesday, April 23, 2013 6:52 AM