none
getting blank collumn in datagridview when i import excel RRS feed

  • Question

  • When i import excel to my VB.net app i only gets blank collumn in the datagrid. can some one please help me?? i need answer fast
    Monday, July 15, 2019 1:24 PM

All replies

  • Hello,

    Without seeing how you are reading Worksheets it's impossible to tell.

    Show your code, if using OleDb we need to see the connection string. Also a screenshot of the worksheet that can easily be shown (meaning not a small image) but you may not be able to post images in post yet.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Monday, July 15, 2019 3:34 PM
    Moderator
  • Hi,

    You can  convert Excel to a datatable,

    'Imports Microsoft.Office.Interop.Excel
    Imports Excel1 = Microsoft.Office.Interop.Excel
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            DataGridView1.DataSource = GetDataFromExcelByCom("D:\test.xlsx", False)
        End Sub
    
        Private Function GetDataFromExcelByCom(ByVal path As String, ByVal Optional hasTitle As Boolean = False) As DataTable
    
            Dim excelFilePath = path
            Dim app As Excel1.Application = New Excel1.Application()
            Dim sheets As Excel1.Sheets
            Dim oMissiong As Object = System.Reflection.Missing.Value
            Dim workbook As Excel1.Workbook = Nothing
            Dim dt As DataTable = New DataTable()
    
            Try
                If app Is Nothing Then Return Nothing
                workbook = app.Workbooks.Open(excelFilePath, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong)
                sheets = workbook.Worksheets
                Dim worksheet As Excel1.Worksheet = sheets(1)
                If worksheet Is Nothing Then Return Nothing
                Dim iRowCount As Integer = worksheet.UsedRange.Rows.Count
                Dim iColCount As Integer = worksheet.UsedRange.Columns.Count
                Dim ischeck As Boolean = False
                For i As Integer = 1 To iColCount
                    Dim name = "column" & i
    
                    If hasTitle Then
                        Dim txt = (CType(worksheet.Cells(1, i + 1), Excel1.Range)).Text.ToString()
                        If Not String.IsNullOrWhiteSpace(txt) Then name = txt
                    End If
    
                    While dt.Columns.Contains(name)
                        name = name & "_1"
                    End While
    
                    dt.Columns.Add(New DataColumn(name, GetType(String)))
                Next
    
                Dim range As Excel1.Range
                Dim rowIdx As Integer = If(hasTitle, 2, 1)
    
                For iRow As Integer = rowIdx To iRowCount
                    Dim dr As DataRow = dt.NewRow()
    
                    For iCol As Integer = 1 To iColCount
                        range = CType(worksheet.Cells(iRow, iCol), Excel1.Range)
                        dr(iCol - 1) = If((range.Value2 Is Nothing), "", range.Text.ToString())
                    Next
    
                    dt.Rows.Add(dr)
                Next
    
                Return dt
            Catch
                Return Nothing
            Finally
                workbook.Close(False, oMissiong, oMissiong)
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook)
                workbook = Nothing
                app.Workbooks.Close()
                app.Quit()
                System.Runtime.InteropServices.Marshal.ReleaseComObject(app)
                app = Nothing
            End Try
        End Function
    End Class
    

    remove empty rows

     Private Sub removeEmpty(ByVal dt As DataTable)
            Dim removelist As List(Of DataRow) = New List(Of DataRow)()
    
            For i As Integer = 0 To dt.Rows.Count - 1
                Dim rowdataisnull As Boolean = True
    
                For j As Integer = 0 To dt.Columns.Count - 1
    
                    If Not String.IsNullOrEmpty(dt.Rows(i)(j).ToString().Trim()) Then
                        rowdataisnull = False
                    End If
                Next
    
                If rowdataisnull Then
                    removelist.Add(dt.Rows(i))
                End If
            Next
    
            For i As Integer = 0 To removelist.Count - 1
                dt.Rows.Remove(removelist(i))
            Next
        End Sub

    remove empty columns

     Private Sub RemoveNULLColumns(ByRef data As DataTable)
            Try
                Dim newData As DataTable = New DataTable()
    
                For n As Integer = 0 To data.Rows.Count - 1 - 1
                    newData.Rows.Add()
                Next
    
                For i As Integer = 0 To data.Columns.Count - 1
    
                    For r As Integer = 0 To data.Rows.Count - 1
    
                        If data.Rows(r)(i).ToString() <> "" Then
                            Dim columnName As String = data.Columns(i).ColumnName
                            newData.Columns.Add(columnName)
    
                            For n As Integer = 0 To data.Rows.Count - 1
                                newData.Rows(n)(newData.Columns.Count - 1) = data.Rows(n)(i)
                            Next
    
                            Exit For
                        End If
                    Next
                Next
    
                data = newData
            Catch
            End Try
        End Sub

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 16, 2019 7:13 AM
    Moderator
  • i got it to work but now i cant search through 

    Tuesday, July 16, 2019 8:00 PM
  • i got it to work but now i cant search through 

    If you have a solution mark this thread as resolved, for other issues such as search start a new thread. And when posting the new question provide code so those who want to assist have a starting point rather than simply a narrative.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, July 16, 2019 11:12 PM
    Moderator
  • i got it to work but now i cant search through 

    Hi,

    How do you want to search?

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 19, 2019 1:35 AM
    Moderator