none
import excel spreadsheet in to datagridview

    Question

  • Hi All,

    I wanting to try and develop a form that will allow me to import an excel spreadsheet in to a datagrid view, can do that bit with this, Vb.net Information. what i want to be able to do is select which columns in the spreadsheet go where so say i have a spreadsheet with;

    ProductCode, ProductName, ProductDescription, ProductCost and ProductMaker

    In my DataGridView i have ProductName, ProductCost and ProductDescription.

    Is it possible to get a list of the columns in a spreadsheet that have text in and then either have a combobox that you select a value from and then after pressing a button it then imports the selected details in to the correct format, (Like mail merge)

    Regards

    Aidan

    Friday, November 25, 2011 9:43 AM

Answers

  • Here is a simple example of reading sheet data using OleDb where the sheet is known not to have column names in the first row. When a sheet does not contain data in the first row each column of data is named Fx i.e., A would be F1, B would be F2 etc. You can alias them as shown below. Of course this may not matter for your column mapping but worth mentioning just in case.

    cn.ConnectionStringEx(ExcelFileName, ExcelProvider.XLS, ImportExportMode.AsText, UseHeader.No)
    
    cmd.CommandText = _
    <SQL>
            SELECT 
                F1 As AccountNumber,
                F2 As FirstName, 
                F3 As LastName, 
                F4 As State 
            FROM 
                [Names$]
        </SQL>.Value
    cn.Open()
    Dim dr As System.Data.IDataReader = cmd.ExecuteReader
    
    Dim dtNames As New DataTable
    dtNames.Load(dr)
    
    

     


    KSG
    Monday, November 28, 2011 5:49 AM
  • I have done exactly what you are looking to do and would like to share the following. First off, I used the same model as MS-Access does to import data. They open an Excel file with an open file dialog, which then places all worksheet names into a ComboBox. The user then selects a worksheet along with checking or not checking a checkbox indicating whether the sheet’s first column is data or column names. The worksheet is then displayed by pressing a ‘worksheet open’ button, if they selected headers for the first row and it data then can simply press the ‘worksheet open’ button again this time indicating the first row is data.

    Next, if the data looks okay they press another button, which brings up a form similar to your image prompting column mapping. Having the data displayed while mapping Excel columns to database table columns is easier for users as they can visually see the data so there is no guesswork involved. Once the mapping has been completed, the data from the selected sheet is pushed to the appropriate database table. Once this has been done, they cannot select this worksheet again insuring no duplication of data in the backend database.

    Note 1 When displaying sheet names using OleDbConnection method GetOleDbSchemaTable if working with XLSX files it may be possible that you will not only pickup sheet names but also defined names which are not in XLS file. The code below strips them out.

    Get sheet names (see second code block for ConnectionStringEx code used in this code block)

     
    Module ExcelSchemaInformation
       Public Function ListOfWorkSheetNames( _ 
          ByVal ExcelFileNameToWork As String, _ 
          ByVal sender As ExcelProvider) As List(Of String)
          
          Dim Names As New List(Of String)
    
          Dim cn As New OleDbConnection
          cn.ConnectionStringEx( _ 
          ExcelFileNameToWork, _ 
          sender, _ 
          ImportExportMode.Normal, _ 
          UseHeader.No)
    
          cn.Open()
    
          Dim dt As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() _ 
             {Nothing, Nothing, Nothing, "TABLE"})
    
          cn.Close()
    
          Dim Entry As String = ""
          For i As Integer = 0 To dt.Rows.Count - 1
             Entry = CType(dt.Rows(i)(2), String)
             If Not Entry.EndsWith("_") Then
                Names.Add(CleanWorkSheetName(Entry))
             End If
          Next
    
          Return Names
    
       End Function
       Private Function CleanWorkSheetName(ByVal Name As String) As String
          Dim tmp As String = Name
    
          If Name.StartsWith("'") Then
             tmp = Name.Substring(1)
          End If
    
          If Name.EndsWith("'") Then
             tmp = tmp.Substring(0, tmp.Length - 1)
          End If
    
          If tmp.EndsWith("$") Then
             tmp = tmp.Substring(0, tmp.Length - 1)
          End If
    
          Return tmp
    
       End Function
    End Module
    
    
    


    Connection to Excel code used in above code block

    ''' <summary>
    ''' Methods for connection to Excel files. There are multiple 
    ''' configuration combinations which are provided as there are 
    ''' many possible combinations. Please note that passwords
    ''' are not covered so if there is a password it should be 
    ''' removed prior to using this code.
    ''' </summary>
    ''' <remarks>
    ''' * Contains three overloaded methods to open Excel files via OleDb
    ''' * Works only for 32-bit version of OleDb drivers
    '''   Reference http://www.connectionstrings.com/Articles/Show/using-jet-in-64-bit-environments
    ''' * There are several console WriteLine methods used to show the composed 
    '''   connection strings which should be commented out for production.
    ''' 
    ''' WARNING: 
    ''' Making any changes to this code should be fully tested before implementing
    ''' </remarks>
    Module ExcelConnectionHelpers
       ''' <summary>
       ''' Indicates to the provider how to translate column data.
       ''' </summary>
       ''' <remarks></remarks>
       Public Enum ImportExportMode
          Normal = 0
          ''' <summary>
          ''' Read "intermixed" (numbers, dates, strings etc) data columns as text. 
          ''' </summary>
          ''' <remarks></remarks>
          AsText = 1
       End Enum
       <System.Diagnostics.DebuggerStepThrough()> _
       <Runtime.CompilerServices.Extension()> _
       Public Function ToEnum(ByVal sender As UseHeader) As Boolean
          Return If(sender = UseHeader.Yes, True, False)
       End Function
       ''' <summary>
       ''' Convert Boolean to UseHeader Enum
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <returns></returns>
       ''' <remarks></remarks>
       <System.Diagnostics.DebuggerStepThrough()> _
       <Runtime.CompilerServices.Extension()> _
       Public Function ToUseHeader(ByVal sender As Boolean) As UseHeader
          If sender Then
             Return UseHeader.Yes
          Else
             Return UseHeader.No
          End If
       End Function
       ''' <summary>
       ''' First row in sheet contain column name or data indicator
       ''' </summary>
       ''' <remarks></remarks>
       Public Enum UseHeader
          ''' <summary>
          ''' Indicates that the first row contains columnnames, no data
          ''' </summary>
          ''' <remarks></remarks>
          Yes
          ''' <summary>
          ''' Indicates that the first row does not contain columnnames
          ''' </summary>
          ''' <remarks></remarks>
          No
       End Enum
       ''' <summary>
       ''' OLEDB Provider version
       ''' </summary>
       ''' <remarks></remarks>
       Public Enum ExcelProvider
          ''' <summary>
          ''' Microsoft.Jet.OLEDB.4.0
          ''' </summary>
          ''' <remarks></remarks>
          XLS
          ''' <summary>
          ''' Microsoft.ACE.OLEDB.12.0
          ''' </summary>
          ''' <remarks></remarks>
          XLSX
       End Enum
    
       Private OleExelVersion As String = "8.0"
       Private Provider As String = "Microsoft.Jet.OLEDB.4.0"
    
       <System.Diagnostics.DebuggerStepThrough()> _
       Private Sub SetVersions(ByVal TheProvider As ExcelProvider)
          If TheProvider = ExcelProvider.XLSX Then
             OleExelVersion = "12.0"
             Provider = "Microsoft.ACE.OLEDB.12.0"
          Else
             OleExelVersion = "8.0"
             Provider = "Microsoft.Jet.OLEDB.4.0"
          End If
    
       End Sub
       ''' <summary>
       ''' Sets the connection string for the OleDb connection read-only
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <param name="DataSource">File name to open including path if needed</param>
       ''' <param name="TheProvider"></param>
       ''' <remarks></remarks>
       <System.Diagnostics.DebuggerStepThrough()> _
       <Runtime.CompilerServices.Extension()> _
       Public Sub ConnectionStringEx(ByVal sender As OleDb.OleDbConnection, _
                                     ByVal DataSource As String, _
                                     ByVal TheProvider As ExcelProvider)
    
          SetVersions(TheProvider)
    
          sender.ConnectionString = _
          <Connection>
             provider=<%= Provider %>;  
             data source='<%= DataSource %>';
             Extended Properties=Excel <%= OleExelVersion %>;
          </Connection>.Value
    
       End Sub
       ''' <summary>
       ''' Sets the connection string for the OleDb connection read-only 
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <param name="DataSource">File name to open including path if needed</param>
       ''' <param name="TheProvider"></param>
       ''' <param name="MixType"></param>
       ''' <remarks></remarks>
       <System.Diagnostics.DebuggerStepThrough()> _
       <Runtime.CompilerServices.Extension()> _
       Public Sub ConnectionStringEx(ByVal sender As OleDb.OleDbConnection, _
                                     ByVal DataSource As String, _
                                     ByVal TheProvider As ExcelProvider, _
                                     ByVal MixType As ImportExportMode)
    
          SetVersions(TheProvider)
    
          sender.ConnectionString = _
          <Connection>
             provider=<%= Provider %>; 
             data source='<%= DataSource %>'; 
             Extended Properties="Excel <%= OleExelVersion %>; IMEX=<%= CInt(MixType) %>;"
          </Connection>.Value
    
       End Sub
       ''' <summary>
       ''' Sets the connection string for the OleDb connection read-only
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <param name="DataSource">File name to open including path if needed</param>
       ''' <param name="TheProvider"></param>
       ''' <param name="MixType"></param>
       ''' <param name="Header"></param>
       ''' <remarks></remarks>
       <System.Diagnostics.DebuggerStepThrough()> _
       <Runtime.CompilerServices.Extension()> _
       Public Sub ConnectionStringEx(ByVal sender As OleDb.OleDbConnection, _
                                     ByVal DataSource As String, _
                                     ByVal TheProvider As ExcelProvider, _
                                     ByVal MixType As ImportExportMode, _
                                     ByVal Header As UseHeader)
    
          SetVersions(TheProvider)
    
          sender.ConnectionString = _
          <Connection>
             provider=<%= Provider %>; 
             data source='<%= DataSource %>'; 
             Extended Properties="Excel <%= OleExelVersion %>; IMEX=<%= CInt(MixType) %>;HDR=<%= Header.ToString %>;"
          </Connection>.Value
    
       End Sub
       ''' <summary>
       ''' Sets the connection string for the OleDb connection as read/write
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <param name="DataSource"></param>
       ''' <param name="TheProvider"></param>
       ''' <param name="Header"></param>
       ''' <remarks></remarks>
       <System.Diagnostics.DebuggerStepThrough()> _
       <Runtime.CompilerServices.Extension()> _
       Public Sub ConnectionStringExUpdatable(ByVal sender As OleDb.OleDbConnection, _
                                     ByVal DataSource As String, _
                                     ByVal TheProvider As ExcelProvider, _
                                     ByVal Header As UseHeader)
    
          SetVersions(TheProvider)
    
          sender.ConnectionString = _
          <Connection>
             provider=<%= Provider %>; 
             data source='<%= DataSource %>'; 
             Extended Properties="Excel <%= OleExelVersion %>; IMEX=<%= 2 %>"
          </Connection>.Value
    
       End Sub
    End Module
    
    


    Note 2 Using OleDb GetOleDbSchemaTable method will display sheet names in a-z order, not the natural order they appear in the workbook. For XLSX files you can use Open XML SDK to get sheet names in natural order and avoid cleaning names to exclude defined names.

    At the following page, last link I have a solution which includes methods to return XLSX sheet names in natural order along with reading sheet data w/o Excel being installed. http://kevininstructor.home.comcast.net/~kevininstructor/ProjectsDotNet.html

    The following is taken from the link above which shows the function to get a list of sheet names for Excel 2007/2010

    Imports DocumentFormat.OpenXml
    Imports DocumentFormat.OpenXml.Packaging
    Imports DocumentFormat.OpenXml.Spreadsheet
    
    Public Module ReadExcelDataRoutines
        Public Enum ReasonCodes
            FailedToOpen
            HasRows
            NoRows
        End Enum
        Public Function WorkSheetExists(ByVal FileName As String, ByVal SheetName As String) As Boolean
            Using document As SpreadsheetDocument = SpreadsheetDocument.Open(FileName, True)
                Dim wbPart As WorkbookPart = document.WorkbookPart
                Dim theSheet As Sheet = wbPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = SheetName).FirstOrDefault()
                If theSheet IsNot Nothing Then
                    Return True
                Else
                    Return False
                End If
            End Using
        End Function
        Public Function GetSheetColumnNames(ByVal FileName As String, ByVal RelationshipId As String, ByVal HasHeader As Boolean) As DataTable
            Dim dt As New DataTable()
            If SheetContainsData(FileName, RelationshipId) = ReasonCodes.FailedToOpen Then
                'My.Dialogs.ExceptionDialog("Failed to open sheet because it is open by another process.")
                Return dt
            End If
    
            Using spreadSheetDocument As SpreadsheetDocument = spreadSheetDocument.Open(FileName, False)
                Dim WorkBookPart As WorkbookPart = spreadSheetDocument.WorkbookPart
                Dim Sheets As IEnumerable(Of Sheet) = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)()
                Dim WorksheetPart As WorksheetPart = CType(spreadSheetDocument.WorkbookPart.GetPartById(RelationshipId), WorksheetPart)
                Dim WorkSheet As Worksheet = WorksheetPart.Worksheet
                Dim SheetData As SheetData = WorkSheet.GetFirstChild(Of SheetData)()
                Dim Rows As IEnumerable(Of Row) = SheetData.Descendants(Of Row)()
    
                If Rows.Count = 0 Then
                    Return dt
                End If
    
                Dim HeaderIndex As Integer = 1
                Dim UseData As Boolean = True
                If HasHeader Then
                    For Each cell As Cell In Rows.ElementAt(0)
                        dt.Columns.Add(TryGetCellValue(spreadSheetDocument, cell, UseData), GetType(System.String))
                        ' NEED Cell index
                    Next cell
                Else
                    For Each cell As Cell In Rows.ElementAt(0)
                        dt.Columns.Add("Column" & HeaderIndex.ToString, GetType(System.String))
                        HeaderIndex += 1
                    Next cell
                End If
    
            End Using
    
            Return dt
    
        End Function
        Public Function SheetContainsData(ByVal FileName As String, ByVal RelationshipId As String) As ReasonCodes
            Dim CanOpen As Boolean = True
    
            Dim dt As New DataTable()
    
            '---------------------------------------------------------------------------------
            ' Brute force method to see if FileName can be opened in read-only mode which will
            ' fail when Excel is already open via Excel Office product.
            '---------------------------------------------------------------------------------
            Try
                Using spreadSheetDocument As SpreadsheetDocument = spreadSheetDocument.Open(FileName, False)
                End Using
            Catch ex As Exception
                CanOpen = False
            End Try
            '---------------------------------------------------------------------------------
            ' Return a blank DataTable when failing to open from above attempt.
            '---------------------------------------------------------------------------------
            If Not CanOpen Then
                Return ReasonCodes.FailedToOpen
            End If
    
            Using spreadSheetDocument As SpreadsheetDocument = spreadSheetDocument.Open(FileName, False)
                Dim WorkBookPart As WorkbookPart = spreadSheetDocument.WorkbookPart
                Dim Sheets As IEnumerable(Of Sheet) = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)()
                Dim WorksheetPart As WorksheetPart = CType(spreadSheetDocument.WorkbookPart.GetPartById(RelationshipId), WorksheetPart)
                Dim WorkSheet As Worksheet = WorksheetPart.Worksheet
                Dim SheetData As SheetData = WorkSheet.GetFirstChild(Of SheetData)()
                Dim Rows As IEnumerable(Of Row) = SheetData.Descendants(Of Row)()
    
                If Rows.Count = 0 Then
                    Return ReasonCodes.NoRows
                Else
                    Return ReasonCodes.HasRows
                End If
    
            End Using
    
        End Function
        ''' <summary>
        ''' Used to get a cell value
        ''' </summary>
        ''' <param name="document"></param>
        ''' <param name="cell"></param>
        ''' <param name="UseValue"></param>
        ''' <returns></returns>
        ''' <remarks>
        ''' UseData parameter is only needed for PopulateSheet routine
        ''' but rather than optimize it stays as is since I am still 
        ''' experimenting with Open XML methods
        ''' </remarks>
        Public Function TryGetCellValue(ByVal document As SpreadsheetDocument, ByVal cell As Cell, ByRef UseValue As Boolean) As String
            Dim TablePart As SharedStringTablePart = document.WorkbookPart.SharedStringTablePart
    
            If cell.CellValue IsNot Nothing Then
                Dim Value As String = cell.CellValue.InnerXml
    
                If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
                    Dim Result = TablePart.SharedStringTable.ChildElements(Int32.Parse(Value)).InnerText
                    UseValue = True
                    Return Result
                Else
                    UseValue = False
                    Return Value
                End If
            Else
                Return ""
            End If
        End Function
        Public Function CanOpenExcelFile(ByVal FileName As String, ByRef ErrorMessage As String) As Boolean
            Try
                Using spreadSheetDocument As SpreadsheetDocument = spreadSheetDocument.Open(FileName, False)
                End Using
                Return True
            Catch ex As Exception
                ErrorMessage = ex.Message
                Return False
            End Try
        End Function
        Public Function CanOpenExcelFile(ByVal FileName As String) As Boolean
            Dim ErrorMessage As String = ""
            Return CanOpenExcelFile(FileName, ErrorMessage)
        End Function
        ''' <summary>
        ''' Returns all worksheets in an Excel file
        ''' </summary>
        ''' <param name="fileName"></param>
        ''' <returns></returns>
        ''' <remarks>
        ''' We could have a function to see if the file is open by another process
        ''' but this will do.
        ''' </remarks>
        Public Function GetSheets(ByVal fileName As String, ByRef ReasonCode As ReasonCodes) As DataTable
            Using dtSheetData As New DataTable()
                dtSheetData.Columns.AddRange(New DataColumn() _
                    { _
                        New DataColumn("OrdinalPosition", GetType(System.Int32)), _
                        New DataColumn("SortedPosition", GetType(System.Int32)), _
                        New DataColumn("RelationshipId", GetType(System.String)), _
                        New DataColumn("SheetName", GetType(System.String)) _
                    } _
                )
    
                Dim CanOpen As Boolean = True
                Try
                    Using spreadSheetDocument As SpreadsheetDocument = spreadSheetDocument.Open(fileName, False)
                    End Using
                Catch ex As Exception
                    ReasonCode = ReasonCodes.FailedToOpen
                    CanOpen = False
                End Try
    
                If Not CanOpen Then
                    Return dtSheetData
                End If
                Using document As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, False)
                    Dim WorkBookPart As WorkbookPart = document.WorkbookPart
                    Dim Sheets As IEnumerable(Of Sheet) = WorkBookPart.Workbook.Descendants(Of Sheet)()
                    For Each Sheet In Sheets
                        dtSheetData.Rows.Add(New Object() _
                            { _
                                CInt(Regex.Replace(Sheet.Id.ToString, "[^0-9]", "")), _
                                CInt(Sheet.SheetId.ToString), _
                                Sheet.Id.ToString, _
                                Sheet.Name _
                            })
                    Next
                End Using
    
                If dtSheetData.Rows.Count > 0 Then
                    ReasonCode = ReasonCodes.HasRows
                Else
                    ReasonCode = ReasonCodes.NoRows
                End If
    
                Return dtSheetData
    
            End Using
        End Function
    
    End Module
    
    

     

     

    Using automation to get sheetnames

        ''' <summary>
        ''' Automation method of getting names of sheets in an Excel file
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub cmdNative_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles cmdNative.Click
    
            OpenFileDialog1.FileName = ""
    
            If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
    
                ListBox1.DataSource = Nothing
                Dim SheetList As New List(Of String)
                Dim App As Excel.Application
                Dim WorkBook1 As Excel.Workbook
                Dim Sheet1 As Excel.Worksheet = Nothing
    
                App = New Excel.Application
                WorkBook1 = App.Workbooks.Open(OpenFileDialog1.FileName)
    
                Dim n As Microsoft.Office.Interop.Excel.Name
                Try
                    For Each n In App.ActiveWorkbook.Names
                        Console.WriteLine(n.Name)
                    Next
                Finally
                    n = Nothing
                End Try
    
    
                For x As Integer = 1 To WorkBook1.Sheets.Count
                    Sheet1 = CType(WorkBook1.Sheets(x), Excel.Worksheet)
                    SheetList.Add(Sheet1.Name)
                Next
    
                WorkBook1.Close()
    
                App.UserControl = True
                App.Quit()
    
                If Not Sheet1 Is Nothing Then
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(Sheet1)
                    Sheet1 = Nothing
                End If
    
                If Not WorkBook1 Is Nothing Then
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(WorkBook1)
                    WorkBook1 = Nothing
                End If
    
                If Not App Is Nothing Then
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(App)
                    App = Nothing
                End If
    
                GC.WaitForPendingFinalizers()
                GC.Collect()
                GC.WaitForPendingFinalizers()
                GC.Collect()
    
                ListBox1.DataSource = SheetList
    
            End If
    
        End Sub
    
    


    Note 3 If you data contains mixed types i.e. strings, dates, numerics mixed together or even just two of these then if you are using OleDb methods to get data back you may need to tweak IMEX settings.

    Note 4 The code used to extract data using Open XML SDK is slow when dealing with say over 20,000 rows per sheet vs OleDb.

     

     

     


    KSG
    • Marked as answer by Aidan Monday, November 28, 2011 1:20 PM
    Monday, November 28, 2011 5:43 AM

All replies

  • I would Suggest that you Look at  the Class: Microsoft Office 12.0 Object Library(This is office 2010) office 2007 is: 6.0 You can add this Dll File Going to your Projects Property's --> References --> add

    as for importing the Data i would use a Simple SQL database than imports Everything from the columns then Filerover the Columns with null values with Passing it through a variable Using somthing like this:

    if Col = nothing then 
    
    else
    
      Dim cmd As New SqlCommand
       dim cnn as new sqlconnection
            cnnConnection.Open()
    
            cmd.Connection = cnnConnection
    
            cmd.CommandText = "Insert INTO........"
            cmd.ExecuteNonQuery()
    
            cnnConnection.Close()
    
    end if
    

      Then you can apply formatting to display the data easily with any formatting you like. Then once you have the data in SQL you don't need to load and extract values from Excel again and again as this is much slower than querying a database

    • Proposed as answer by Ben Denness - Director Friday, November 25, 2011 4:44 PM
    • Unproposed as answer by Aidan Saturday, November 26, 2011 9:34 AM
    Friday, November 25, 2011 4:43 PM
  • Hi Dennis,

     

    thank you for the reply, i have that done already the database part what i want to do is, suppliers send products through by excel spreadsheet and i want to import these in to a DataGridView but select which column from the excel spreadsheet goes where like this.

    so say column A in the spreadsheet contains the product code i want this to go in the ProductCode column 2 in the datagridview.

    I am wanting to put in to the datagridview and the INSERT INTO  the database I can do this just the matching fields part if it can be done.

    Regards

    Aidan

    Friday, November 25, 2011 9:18 PM
  • Hi Aidan,

    Welcome to the MSDN Forum.

    >>Is it possible to get a list of the columns in a spreadsheet that have text in and then either have a combobox that you select a value from and then after pressing a button it then imports the selected details in to the correct format,

    First of all, it is possible.

    Based on your description, it seems that you have known how to import whole excel spreadsheet to datagridview. So, you can just follow these steps below:

    1. Import all the excel spreadsheet data into a datatable instead of datagridview.

    2. Populate your list with all the datatable column name: http://msdn.microsoft.com/en-us/library/system.data.datatable.columns.aspx

    3. Retrieve the default dataview of your datatable: http://msdn.microsoft.com/en-us/library/system.data.datatable.defaultview.aspx

    4. Convert the dataview to a datatable with the selected column in your list: http://msdn.microsoft.com/en-us/library/wec2b2e6.aspx

    5. Set the datasource of your datagridview to the new datatable: http://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.datasource.aspx

    I hope this will be helpful.

    Based regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, November 28, 2011 3:01 AM
    Moderator
  • I have done exactly what you are looking to do and would like to share the following. First off, I used the same model as MS-Access does to import data. They open an Excel file with an open file dialog, which then places all worksheet names into a ComboBox. The user then selects a worksheet along with checking or not checking a checkbox indicating whether the sheet’s first column is data or column names. The worksheet is then displayed by pressing a ‘worksheet open’ button, if they selected headers for the first row and it data then can simply press the ‘worksheet open’ button again this time indicating the first row is data.

    Next, if the data looks okay they press another button, which brings up a form similar to your image prompting column mapping. Having the data displayed while mapping Excel columns to database table columns is easier for users as they can visually see the data so there is no guesswork involved. Once the mapping has been completed, the data from the selected sheet is pushed to the appropriate database table. Once this has been done, they cannot select this worksheet again insuring no duplication of data in the backend database.

    Note 1 When displaying sheet names using OleDbConnection method GetOleDbSchemaTable if working with XLSX files it may be possible that you will not only pickup sheet names but also defined names which are not in XLS file. The code below strips them out.

    Get sheet names (see second code block for ConnectionStringEx code used in this code block)

     
    Module ExcelSchemaInformation
       Public Function ListOfWorkSheetNames( _ 
          ByVal ExcelFileNameToWork As String, _ 
          ByVal sender As ExcelProvider) As List(Of String)
          
          Dim Names As New List(Of String)
    
          Dim cn As New OleDbConnection
          cn.ConnectionStringEx( _ 
          ExcelFileNameToWork, _ 
          sender, _ 
          ImportExportMode.Normal, _ 
          UseHeader.No)
    
          cn.Open()
    
          Dim dt As DataTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() _ 
             {Nothing, Nothing, Nothing, "TABLE"})
    
          cn.Close()
    
          Dim Entry As String = ""
          For i As Integer = 0 To dt.Rows.Count - 1
             Entry = CType(dt.Rows(i)(2), String)
             If Not Entry.EndsWith("_") Then
                Names.Add(CleanWorkSheetName(Entry))
             End If
          Next
    
          Return Names
    
       End Function
       Private Function CleanWorkSheetName(ByVal Name As String) As String
          Dim tmp As String = Name
    
          If Name.StartsWith("'") Then
             tmp = Name.Substring(1)
          End If
    
          If Name.EndsWith("'") Then
             tmp = tmp.Substring(0, tmp.Length - 1)
          End If
    
          If tmp.EndsWith("$") Then
             tmp = tmp.Substring(0, tmp.Length - 1)
          End If
    
          Return tmp
    
       End Function
    End Module
    
    
    


    Connection to Excel code used in above code block

    ''' <summary>
    ''' Methods for connection to Excel files. There are multiple 
    ''' configuration combinations which are provided as there are 
    ''' many possible combinations. Please note that passwords
    ''' are not covered so if there is a password it should be 
    ''' removed prior to using this code.
    ''' </summary>
    ''' <remarks>
    ''' * Contains three overloaded methods to open Excel files via OleDb
    ''' * Works only for 32-bit version of OleDb drivers
    '''   Reference http://www.connectionstrings.com/Articles/Show/using-jet-in-64-bit-environments
    ''' * There are several console WriteLine methods used to show the composed 
    '''   connection strings which should be commented out for production.
    ''' 
    ''' WARNING: 
    ''' Making any changes to this code should be fully tested before implementing
    ''' </remarks>
    Module ExcelConnectionHelpers
       ''' <summary>
       ''' Indicates to the provider how to translate column data.
       ''' </summary>
       ''' <remarks></remarks>
       Public Enum ImportExportMode
          Normal = 0
          ''' <summary>
          ''' Read "intermixed" (numbers, dates, strings etc) data columns as text. 
          ''' </summary>
          ''' <remarks></remarks>
          AsText = 1
       End Enum
       <System.Diagnostics.DebuggerStepThrough()> _
       <Runtime.CompilerServices.Extension()> _
       Public Function ToEnum(ByVal sender As UseHeader) As Boolean
          Return If(sender = UseHeader.Yes, True, False)
       End Function
       ''' <summary>
       ''' Convert Boolean to UseHeader Enum
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <returns></returns>
       ''' <remarks></remarks>
       <System.Diagnostics.DebuggerStepThrough()> _
       <Runtime.CompilerServices.Extension()> _
       Public Function ToUseHeader(ByVal sender As Boolean) As UseHeader
          If sender Then
             Return UseHeader.Yes
          Else
             Return UseHeader.No
          End If
       End Function
       ''' <summary>
       ''' First row in sheet contain column name or data indicator
       ''' </summary>
       ''' <remarks></remarks>
       Public Enum UseHeader
          ''' <summary>
          ''' Indicates that the first row contains columnnames, no data
          ''' </summary>
          ''' <remarks></remarks>
          Yes
          ''' <summary>
          ''' Indicates that the first row does not contain columnnames
          ''' </summary>
          ''' <remarks></remarks>
          No
       End Enum
       ''' <summary>
       ''' OLEDB Provider version
       ''' </summary>
       ''' <remarks></remarks>
       Public Enum ExcelProvider
          ''' <summary>
          ''' Microsoft.Jet.OLEDB.4.0
          ''' </summary>
          ''' <remarks></remarks>
          XLS
          ''' <summary>
          ''' Microsoft.ACE.OLEDB.12.0
          ''' </summary>
          ''' <remarks></remarks>
          XLSX
       End Enum
    
       Private OleExelVersion As String = "8.0"
       Private Provider As String = "Microsoft.Jet.OLEDB.4.0"
    
       <System.Diagnostics.DebuggerStepThrough()> _
       Private Sub SetVersions(ByVal TheProvider As ExcelProvider)
          If TheProvider = ExcelProvider.XLSX Then
             OleExelVersion = "12.0"
             Provider = "Microsoft.ACE.OLEDB.12.0"
          Else
             OleExelVersion = "8.0"
             Provider = "Microsoft.Jet.OLEDB.4.0"
          End If
    
       End Sub
       ''' <summary>
       ''' Sets the connection string for the OleDb connection read-only
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <param name="DataSource">File name to open including path if needed</param>
       ''' <param name="TheProvider"></param>
       ''' <remarks></remarks>
       <System.Diagnostics.DebuggerStepThrough()> _
       <Runtime.CompilerServices.Extension()> _
       Public Sub ConnectionStringEx(ByVal sender As OleDb.OleDbConnection, _
                                     ByVal DataSource As String, _
                                     ByVal TheProvider As ExcelProvider)
    
          SetVersions(TheProvider)
    
          sender.ConnectionString = _
          <Connection>
             provider=<%= Provider %>;  
             data source='<%= DataSource %>';
             Extended Properties=Excel <%= OleExelVersion %>;
          </Connection>.Value
    
       End Sub
       ''' <summary>
       ''' Sets the connection string for the OleDb connection read-only 
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <param name="DataSource">File name to open including path if needed</param>
       ''' <param name="TheProvider"></param>
       ''' <param name="MixType"></param>
       ''' <remarks></remarks>
       <System.Diagnostics.DebuggerStepThrough()> _
       <Runtime.CompilerServices.Extension()> _
       Public Sub ConnectionStringEx(ByVal sender As OleDb.OleDbConnection, _
                                     ByVal DataSource As String, _
                                     ByVal TheProvider As ExcelProvider, _
                                     ByVal MixType As ImportExportMode)
    
          SetVersions(TheProvider)
    
          sender.ConnectionString = _
          <Connection>
             provider=<%= Provider %>; 
             data source='<%= DataSource %>'; 
             Extended Properties="Excel <%= OleExelVersion %>; IMEX=<%= CInt(MixType) %>;"
          </Connection>.Value
    
       End Sub
       ''' <summary>
       ''' Sets the connection string for the OleDb connection read-only
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <param name="DataSource">File name to open including path if needed</param>
       ''' <param name="TheProvider"></param>
       ''' <param name="MixType"></param>
       ''' <param name="Header"></param>
       ''' <remarks></remarks>
       <System.Diagnostics.DebuggerStepThrough()> _
       <Runtime.CompilerServices.Extension()> _
       Public Sub ConnectionStringEx(ByVal sender As OleDb.OleDbConnection, _
                                     ByVal DataSource As String, _
                                     ByVal TheProvider As ExcelProvider, _
                                     ByVal MixType As ImportExportMode, _
                                     ByVal Header As UseHeader)
    
          SetVersions(TheProvider)
    
          sender.ConnectionString = _
          <Connection>
             provider=<%= Provider %>; 
             data source='<%= DataSource %>'; 
             Extended Properties="Excel <%= OleExelVersion %>; IMEX=<%= CInt(MixType) %>;HDR=<%= Header.ToString %>;"
          </Connection>.Value
    
       End Sub
       ''' <summary>
       ''' Sets the connection string for the OleDb connection as read/write
       ''' </summary>
       ''' <param name="sender"></param>
       ''' <param name="DataSource"></param>
       ''' <param name="TheProvider"></param>
       ''' <param name="Header"></param>
       ''' <remarks></remarks>
       <System.Diagnostics.DebuggerStepThrough()> _
       <Runtime.CompilerServices.Extension()> _
       Public Sub ConnectionStringExUpdatable(ByVal sender As OleDb.OleDbConnection, _
                                     ByVal DataSource As String, _
                                     ByVal TheProvider As ExcelProvider, _
                                     ByVal Header As UseHeader)
    
          SetVersions(TheProvider)
    
          sender.ConnectionString = _
          <Connection>
             provider=<%= Provider %>; 
             data source='<%= DataSource %>'; 
             Extended Properties="Excel <%= OleExelVersion %>; IMEX=<%= 2 %>"
          </Connection>.Value
    
       End Sub
    End Module
    
    


    Note 2 Using OleDb GetOleDbSchemaTable method will display sheet names in a-z order, not the natural order they appear in the workbook. For XLSX files you can use Open XML SDK to get sheet names in natural order and avoid cleaning names to exclude defined names.

    At the following page, last link I have a solution which includes methods to return XLSX sheet names in natural order along with reading sheet data w/o Excel being installed. http://kevininstructor.home.comcast.net/~kevininstructor/ProjectsDotNet.html

    The following is taken from the link above which shows the function to get a list of sheet names for Excel 2007/2010

    Imports DocumentFormat.OpenXml
    Imports DocumentFormat.OpenXml.Packaging
    Imports DocumentFormat.OpenXml.Spreadsheet
    
    Public Module ReadExcelDataRoutines
        Public Enum ReasonCodes
            FailedToOpen
            HasRows
            NoRows
        End Enum
        Public Function WorkSheetExists(ByVal FileName As String, ByVal SheetName As String) As Boolean
            Using document As SpreadsheetDocument = SpreadsheetDocument.Open(FileName, True)
                Dim wbPart As WorkbookPart = document.WorkbookPart
                Dim theSheet As Sheet = wbPart.Workbook.Descendants(Of Sheet)().Where(Function(s) s.Name = SheetName).FirstOrDefault()
                If theSheet IsNot Nothing Then
                    Return True
                Else
                    Return False
                End If
            End Using
        End Function
        Public Function GetSheetColumnNames(ByVal FileName As String, ByVal RelationshipId As String, ByVal HasHeader As Boolean) As DataTable
            Dim dt As New DataTable()
            If SheetContainsData(FileName, RelationshipId) = ReasonCodes.FailedToOpen Then
                'My.Dialogs.ExceptionDialog("Failed to open sheet because it is open by another process.")
                Return dt
            End If
    
            Using spreadSheetDocument As SpreadsheetDocument = spreadSheetDocument.Open(FileName, False)
                Dim WorkBookPart As WorkbookPart = spreadSheetDocument.WorkbookPart
                Dim Sheets As IEnumerable(Of Sheet) = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)()
                Dim WorksheetPart As WorksheetPart = CType(spreadSheetDocument.WorkbookPart.GetPartById(RelationshipId), WorksheetPart)
                Dim WorkSheet As Worksheet = WorksheetPart.Worksheet
                Dim SheetData As SheetData = WorkSheet.GetFirstChild(Of SheetData)()
                Dim Rows As IEnumerable(Of Row) = SheetData.Descendants(Of Row)()
    
                If Rows.Count = 0 Then
                    Return dt
                End If
    
                Dim HeaderIndex As Integer = 1
                Dim UseData As Boolean = True
                If HasHeader Then
                    For Each cell As Cell In Rows.ElementAt(0)
                        dt.Columns.Add(TryGetCellValue(spreadSheetDocument, cell, UseData), GetType(System.String))
                        ' NEED Cell index
                    Next cell
                Else
                    For Each cell As Cell In Rows.ElementAt(0)
                        dt.Columns.Add("Column" & HeaderIndex.ToString, GetType(System.String))
                        HeaderIndex += 1
                    Next cell
                End If
    
            End Using
    
            Return dt
    
        End Function
        Public Function SheetContainsData(ByVal FileName As String, ByVal RelationshipId As String) As ReasonCodes
            Dim CanOpen As Boolean = True
    
            Dim dt As New DataTable()
    
            '---------------------------------------------------------------------------------
            ' Brute force method to see if FileName can be opened in read-only mode which will
            ' fail when Excel is already open via Excel Office product.
            '---------------------------------------------------------------------------------
            Try
                Using spreadSheetDocument As SpreadsheetDocument = spreadSheetDocument.Open(FileName, False)
                End Using
            Catch ex As Exception
                CanOpen = False
            End Try
            '---------------------------------------------------------------------------------
            ' Return a blank DataTable when failing to open from above attempt.
            '---------------------------------------------------------------------------------
            If Not CanOpen Then
                Return ReasonCodes.FailedToOpen
            End If
    
            Using spreadSheetDocument As SpreadsheetDocument = spreadSheetDocument.Open(FileName, False)
                Dim WorkBookPart As WorkbookPart = spreadSheetDocument.WorkbookPart
                Dim Sheets As IEnumerable(Of Sheet) = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().Elements(Of Sheet)()
                Dim WorksheetPart As WorksheetPart = CType(spreadSheetDocument.WorkbookPart.GetPartById(RelationshipId), WorksheetPart)
                Dim WorkSheet As Worksheet = WorksheetPart.Worksheet
                Dim SheetData As SheetData = WorkSheet.GetFirstChild(Of SheetData)()
                Dim Rows As IEnumerable(Of Row) = SheetData.Descendants(Of Row)()
    
                If Rows.Count = 0 Then
                    Return ReasonCodes.NoRows
                Else
                    Return ReasonCodes.HasRows
                End If
    
            End Using
    
        End Function
        ''' <summary>
        ''' Used to get a cell value
        ''' </summary>
        ''' <param name="document"></param>
        ''' <param name="cell"></param>
        ''' <param name="UseValue"></param>
        ''' <returns></returns>
        ''' <remarks>
        ''' UseData parameter is only needed for PopulateSheet routine
        ''' but rather than optimize it stays as is since I am still 
        ''' experimenting with Open XML methods
        ''' </remarks>
        Public Function TryGetCellValue(ByVal document As SpreadsheetDocument, ByVal cell As Cell, ByRef UseValue As Boolean) As String
            Dim TablePart As SharedStringTablePart = document.WorkbookPart.SharedStringTablePart
    
            If cell.CellValue IsNot Nothing Then
                Dim Value As String = cell.CellValue.InnerXml
    
                If cell.DataType IsNot Nothing AndAlso cell.DataType.Value = CellValues.SharedString Then
                    Dim Result = TablePart.SharedStringTable.ChildElements(Int32.Parse(Value)).InnerText
                    UseValue = True
                    Return Result
                Else
                    UseValue = False
                    Return Value
                End If
            Else
                Return ""
            End If
        End Function
        Public Function CanOpenExcelFile(ByVal FileName As String, ByRef ErrorMessage As String) As Boolean
            Try
                Using spreadSheetDocument As SpreadsheetDocument = spreadSheetDocument.Open(FileName, False)
                End Using
                Return True
            Catch ex As Exception
                ErrorMessage = ex.Message
                Return False
            End Try
        End Function
        Public Function CanOpenExcelFile(ByVal FileName As String) As Boolean
            Dim ErrorMessage As String = ""
            Return CanOpenExcelFile(FileName, ErrorMessage)
        End Function
        ''' <summary>
        ''' Returns all worksheets in an Excel file
        ''' </summary>
        ''' <param name="fileName"></param>
        ''' <returns></returns>
        ''' <remarks>
        ''' We could have a function to see if the file is open by another process
        ''' but this will do.
        ''' </remarks>
        Public Function GetSheets(ByVal fileName As String, ByRef ReasonCode As ReasonCodes) As DataTable
            Using dtSheetData As New DataTable()
                dtSheetData.Columns.AddRange(New DataColumn() _
                    { _
                        New DataColumn("OrdinalPosition", GetType(System.Int32)), _
                        New DataColumn("SortedPosition", GetType(System.Int32)), _
                        New DataColumn("RelationshipId", GetType(System.String)), _
                        New DataColumn("SheetName", GetType(System.String)) _
                    } _
                )
    
                Dim CanOpen As Boolean = True
                Try
                    Using spreadSheetDocument As SpreadsheetDocument = spreadSheetDocument.Open(fileName, False)
                    End Using
                Catch ex As Exception
                    ReasonCode = ReasonCodes.FailedToOpen
                    CanOpen = False
                End Try
    
                If Not CanOpen Then
                    Return dtSheetData
                End If
                Using document As SpreadsheetDocument = SpreadsheetDocument.Open(fileName, False)
                    Dim WorkBookPart As WorkbookPart = document.WorkbookPart
                    Dim Sheets As IEnumerable(Of Sheet) = WorkBookPart.Workbook.Descendants(Of Sheet)()
                    For Each Sheet In Sheets
                        dtSheetData.Rows.Add(New Object() _
                            { _
                                CInt(Regex.Replace(Sheet.Id.ToString, "[^0-9]", "")), _
                                CInt(Sheet.SheetId.ToString), _
                                Sheet.Id.ToString, _
                                Sheet.Name _
                            })
                    Next
                End Using
    
                If dtSheetData.Rows.Count > 0 Then
                    ReasonCode = ReasonCodes.HasRows
                Else
                    ReasonCode = ReasonCodes.NoRows
                End If
    
                Return dtSheetData
    
            End Using
        End Function
    
    End Module
    
    

     

     

    Using automation to get sheetnames

        ''' <summary>
        ''' Automation method of getting names of sheets in an Excel file
        ''' </summary>
        ''' <param name="sender"></param>
        ''' <param name="e"></param>
        ''' <remarks></remarks>
        Private Sub cmdNative_Click( _
            ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles cmdNative.Click
    
            OpenFileDialog1.FileName = ""
    
            If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
    
                ListBox1.DataSource = Nothing
                Dim SheetList As New List(Of String)
                Dim App As Excel.Application
                Dim WorkBook1 As Excel.Workbook
                Dim Sheet1 As Excel.Worksheet = Nothing
    
                App = New Excel.Application
                WorkBook1 = App.Workbooks.Open(OpenFileDialog1.FileName)
    
                Dim n As Microsoft.Office.Interop.Excel.Name
                Try
                    For Each n In App.ActiveWorkbook.Names
                        Console.WriteLine(n.Name)
                    Next
                Finally
                    n = Nothing
                End Try
    
    
                For x As Integer = 1 To WorkBook1.Sheets.Count
                    Sheet1 = CType(WorkBook1.Sheets(x), Excel.Worksheet)
                    SheetList.Add(Sheet1.Name)
                Next
    
                WorkBook1.Close()
    
                App.UserControl = True
                App.Quit()
    
                If Not Sheet1 Is Nothing Then
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(Sheet1)
                    Sheet1 = Nothing
                End If
    
                If Not WorkBook1 Is Nothing Then
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(WorkBook1)
                    WorkBook1 = Nothing
                End If
    
                If Not App Is Nothing Then
                    Runtime.InteropServices.Marshal.FinalReleaseComObject(App)
                    App = Nothing
                End If
    
                GC.WaitForPendingFinalizers()
                GC.Collect()
                GC.WaitForPendingFinalizers()
                GC.Collect()
    
                ListBox1.DataSource = SheetList
    
            End If
    
        End Sub
    
    


    Note 3 If you data contains mixed types i.e. strings, dates, numerics mixed together or even just two of these then if you are using OleDb methods to get data back you may need to tweak IMEX settings.

    Note 4 The code used to extract data using Open XML SDK is slow when dealing with say over 20,000 rows per sheet vs OleDb.

     

     

     


    KSG
    • Marked as answer by Aidan Monday, November 28, 2011 1:20 PM
    Monday, November 28, 2011 5:43 AM
  • Here is a simple example of reading sheet data using OleDb where the sheet is known not to have column names in the first row. When a sheet does not contain data in the first row each column of data is named Fx i.e., A would be F1, B would be F2 etc. You can alias them as shown below. Of course this may not matter for your column mapping but worth mentioning just in case.

    cn.ConnectionStringEx(ExcelFileName, ExcelProvider.XLS, ImportExportMode.AsText, UseHeader.No)
    
    cmd.CommandText = _
    <SQL>
            SELECT 
                F1 As AccountNumber,
                F2 As FirstName, 
                F3 As LastName, 
                F4 As State 
            FROM 
                [Names$]
        </SQL>.Value
    cn.Open()
    Dim dr As System.Data.IDataReader = cmd.ExecuteReader
    
    Dim dtNames As New DataTable
    dtNames.Load(dr)
    
    

     


    KSG
    Monday, November 28, 2011 5:49 AM
  • As alternative to the already given replies, you can right click on your datagridview, do edit columns, add 4 columns, click on those columns and now give them the correct names in columnheader, name and especially datapropertyname,

    Probably much more simpler to fulfil your wish with that which you already have as code.

     


    Success
    Cor
    Monday, November 28, 2011 7:08 AM
  • Hi All,

    Thank you for the tips and code i will get through it and mark the appropriate one that did as i need the answer will be one evening this week as quite busy through the day.

    thank you again.

    Aidan

    Monday, November 28, 2011 8:54 AM
  • Hi KSG,

    I have downloaded your sample and i think it will do the job i just need to figure out how to get this,

    to have a combo box on with all the required fields in them and then the user selects where they want the items to go. (Its a start) thanks

    Aidan

    Monday, November 28, 2011 1:24 PM
  • My code to show both Excel and table columns is well embedded into an application where if I gave you the code it would not be of much use (uses several specialized classes which make use of IBM-DB2 and internal security routines) , otherwise I would give you the code. If nothing else the following might give you ideas for the UI.

    Figure one shows a ComboBox populated with worksheet names from a file (file name directly above the ComboBox). Under the "Open Sheet" button is a CheckBox which when checked indicates that the selected sheet has column headers and the Excel connection extended properties is set using this. The user presses "Open Sheet", does a quick visual check then presses "Map" button which brings up the window in figure 2 snapped to the right side of the window in figure 2.

    In figure 2, the first column allows the user to show/hide columns from figure 1 window which assist with mapping data in figure 2 window. Both windows are modal so I setup in the figure 2 window keys to allow the user to traverse the window in figure 1 i.e. CTRL+Up moves up one row while Up moves the current DataGridView up one row etc. Column two represents column from the opened Excel file while the third column represents field names from two database tables to map the Excel columns.

    NOTE the small button in the lower left corner of figure 2 (above "show/hide"). When I am viewing this screen that button shows but for nobody else. It's purpose is to show me results from what was done visually in the DataGridView above so if something was not working properly (there are a lot of rules under the covers other than simply mapping columns together i.e. if a specific column was mapped it must be of type double which is checked or that the user could not pick a table column twice (this is important I believe in any business solution). The lightbulb button displays an HTML help explaining to the user about how to traverse both DataGridView controls from one window.

    Figure 1

    Figure 2

     

     


    KSG
    Monday, November 28, 2011 3:27 PM
  • Hi KSG,

    Thank you for the reply back the mapping bit is exactly what i want to be able to do, and i think with the sample code you provided and the sample you posted i can recreate something that will do the trick. one question if you reorder the datagridview would it then put put it in to a datatable that same way so say on your screen shots i wanted first name to be the 2nd column and i moved it would it through the .SetOrdinal(), would it be put as column 2 or would it still be represented as column 3 (hope that makes sense).

    Regards

     

    Monday, November 28, 2011 4:09 PM
  • Worksheets in my solution come from many sources thus the columns can be in any order so the second column may have FirstName and LastName or just one of them or it might have First Name, Last Name etc or may not have header columns which would make it impossible or convoluted to have a perfect solution. In your project if you can always count on “first name” to exists then it would be possible but at what cost when dealing with this? Now if you want to provide a method to dynamically reorder rows by the user where the DataSource for the DataGridView is data bound I have code for that.

    The following code reads xml into a DataTable which becomes the DataSource of a BindingSource which becomes the DataSource of a DataGridView. To reorder rows the user single clicks the left mouse button and drags the row to the desired position and releases the mouse. The main caveat is if there is a current sort it must be removed otherwise it would appear the reorder did not work but the sort hides the fact it did work.

    Public Class frmReorder
       WithEvents bsPeople As New BindingSource
       Private Identifier As String = ""
    
       Private MouseDownRectangle As Rectangle
       Private rowIndexFromMouseDown As Integer
       Private rowIndexOfItemUnderMouseToDrop As Integer
    
       <System.Diagnostics.DebuggerStepThrough()> _
        Private Sub dataGridView1_MouseMove(ByVal sender As Object, ByVal e As MouseEventArgs) Handles DataGridView1.MouseMove
          If (e.Button And MouseButtons.Left) = MouseButtons.Left Then
             If MouseDownRectangle <> Rectangle.Empty AndAlso (Not MouseDownRectangle.Contains(e.X, e.Y)) Then
                Dim dropEffect As DragDropEffects = DataGridView1.DoDragDrop(DataGridView1.Rows(rowIndexFromMouseDown), DragDropEffects.Move)
             End If
          End If
       End Sub
       <System.Diagnostics.DebuggerStepThrough()> _
       Private Sub dataGridView1_MouseDown(ByVal sender As Object, ByVal e As MouseEventArgs) Handles DataGridView1.MouseDown
          rowIndexFromMouseDown = DataGridView1.HitTest(e.X, e.Y).RowIndex
          If rowIndexFromMouseDown <> -1 Then
             Dim dragSize As Size = SystemInformation.DragSize
             MouseDownRectangle = New Rectangle(New Point(e.X - (dragSize.Width \ 2), e.Y - (dragSize.Height \ 2)), dragSize)
          Else
             MouseDownRectangle = Rectangle.Empty
          End If
       End Sub
       <System.Diagnostics.DebuggerStepThrough()> _
       Private Sub dataGridView1_DragOver(ByVal sender As Object, ByVal e As DragEventArgs) Handles DataGridView1.DragOver
          e.Effect = DragDropEffects.Move
       End Sub
       Private Sub dataGridView1_DragDrop(ByVal sender As Object, ByVal e As DragEventArgs) Handles DataGridView1.DragDrop
          Dim OriginalIdentifier As String = ""
          Dim clientPoint As Point = DataGridView1.PointToClient(New Point(e.X, e.Y))
    
          rowIndexOfItemUnderMouseToDrop = DataGridView1.HitTest(clientPoint.X, clientPoint.Y).RowIndex
    
          If e.Effect = DragDropEffects.Move Then
             OriginalIdentifier = bsPeople.CurrentRow(PrimaryIdentifier)
             Dim rowToMove As DataGridViewRow = TryCast(e.Data.GetData(GetType(DataGridViewRow)), DataGridViewRow)
    
             Dim celldata(rowToMove.Cells.Count - 1) As Object
             For col As Integer = 0 To rowToMove.Cells.Count - 1
                celldata(col) = rowToMove.Cells(col).Value
             Next
    
             Dim row As DataRow = bsPeople.DataTable.NewRow()
             row.ItemArray = celldata
    
             bsPeople.DataTable.Rows.InsertAt(row, rowIndexOfItemUnderMouseToDrop)
             rowToMove.DataGridView.Rows.Remove(rowToMove)
    
             If Not String.IsNullOrEmpty(bsPeople.Sort) Then
                bsPeople.Sort = ""
                cmdSort.Text = "Sort"
             End If
    
             If Not String.IsNullOrEmpty(OriginalIdentifier) Then
                bsPeople.Locate(PrimaryIdentifier, OriginalIdentifier)
             End If
    
          End If
       End Sub
       Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
          Dim ds As New DataSet
          ds.ReadXml("People.xml")
          bsPeople.DataSource = ds.Tables("Person")
          DataGridView1.DataSource = bsPeople
    
          bsPeople.Sort = "LastName"
    
          DataGridView1.Columns(4).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
          For Each col As DataGridViewColumn In DataGridView1.Columns
             col.SortMode = DataGridViewColumnSortMode.NotSortable
          Next
       End Sub
       Private Sub bsPeople_PositionChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles bsPeople.PositionChanged
          If bsPeople.Current IsNot Nothing Then
             Identifier = bsPeople.PrimaryKey
             Label1.Text = String.Format("BindingSource Primary key [{0}]", bsPeople.PrimaryKey)
          End If
       End Sub
       Private Sub Sorter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSort.Click
          If String.IsNullOrEmpty(bsPeople.Sort) Then
             bsPeople.Sort = "LastName"
             cmdSort.Text = "unsort"
          Else
             bsPeople.Sort = ""
             cmdSort.Text = "Sort"
          End If
       End Sub
       Private Sub DataGridView1_Sorted(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.Sorted
          bsPeople.Locate(PrimaryIdentifier, Identifier)
       End Sub
    End Class
    
    

    People xml file contents for above example

    <?xml version="1.0" encoding="utf-8"?>
    <?xml-stylesheet type="text/css" href="people.css"?>
    <People>
        <Person>
        <ID>246-12-5645</ID>
        <FirstName>Kevin</FirstName>
        <LastName>Gallagher</LastName>
        <Age>40</Age>
        <TimeStamp>20 March 2009</TimeStamp>
      </Person>
      <Person>
        <ID>246-12-5646</ID>
        <FirstName>Kevin</FirstName>
        <LastName>Willson</LastName>
        <Age>41</Age>
        <TimeStamp>12 September 2009</TimeStamp>
      </Person>
      <Person>
        <ID>246-12-5647</ID>
        <FirstName>Jon</FirstName>
        <LastName>Smith</LastName>
        <Age>40</Age>
        <TimeStamp>20 March 2009</TimeStamp>
      </Person>
      <Person>
        <ID>246-12-5648</ID>
        <FirstName>Brian</FirstName>
        <LastName>Jones</LastName>
        <Age>43</Age>
        <TimeStamp>20 June 2004</TimeStamp>
      </Person>
      <Person>
        <ID>246-12-5649</ID>
        <FirstName>Sue</FirstName>
        <LastName>Lebow</LastName>
        <Age>34</Age>
        <TimeStamp>20 December 1999</TimeStamp>
      </Person>
      <Person>
        <ID>246-12-5650</ID>
        <FirstName>Ardythe</FirstName>
        <LastName>Nelson</LastName>
        <Age>30</Age>
        <TimeStamp>20 March 2009</TimeStamp>
      </Person>
      <Person>
        <ID>246-12-5651</ID>
        <FirstName>Greg</FirstName>
        <LastName>Rogers</LastName>
        <Age>33</Age>
        <TimeStamp>01 April 2001</TimeStamp>
      </Person>
      <Person>
        <ID>246-12-5652</ID>
        <FirstName>Cleve</FirstName>
        <LastName>Hall</LastName>
        <Age>55</Age>
        <TimeStamp>20 March 2009</TimeStamp>
      </Person>
    </People>
    




    KSG
    Monday, November 28, 2011 5:28 PM
  • Hi KSG,

    I have got the code working to allow me to import the data (.xslx only though) how do i go about getting the data from .xls?

    Regards

    Aidan
    Tuesday, November 29, 2011 5:29 PM
  • Hi KSG,

    I have stuck in a similar situation, I am writing an import wizard wherein I need to different type of data to be imported ex. Customers, products, stock etc., i am stuck in after reading the data from excel sheet to a datagridview or to a datatabe, I am unable to put it in a grid view so user can map it with respective fields (Convert Customer Dataset fields in to rows of column1 & Excel Columns to rows of column2)

    I have idea on how to convert excel columns to rows of datatabe by adding new column 

      AvailableCols = New DataTable()
            AvailableCols.Columns.Add("SourceColumn")
            For Each column As System.Data.DataColumn In Mytable.Columns
                Dim rows As DataRowCollection = Me.AvailableCols.Rows
                Dim columnName() As Object = {column.ColumnName}
                rows.Add(columnName)                       
            Next

    Regards,

    Vinay Suryawomshi




    Thanks & Regards, Vinay Suryawomshi

    Friday, January 31, 2014 5:41 AM
  • Hello,

    I am going to be very high level here as I have done this before. There is zero code as my solution can not be shown in one or two code files, I have things called from satellite utility projects/dll's and code spanning three modal forms. Also, by no means is mapping when done properly a trivial task.

    In my application vendors send Excel files in where they can be xls or xlsx, first row is data or column name. So right from the start you need to be alert. I used a custom language extension to handle the connection string. Then would prompt the user "Is the first row data or header", once they reply I show the data in a DataGridView. If they made a mistake I allow them to reload the file w/o closing the form (forgot to mention this process is done in a child form). Once all this has been done I show another child form which shows raw data from the selected sheet while the primary child form shows column names, if no column headers F1, F2 etc. which can make is difficult for users to determine how to match columns to field in a DataTable (see comments at end of my reply) where the DataTable eventually gets uploaded to a temp backend database where the users sanitize the data if need be.

    While the form is up they map data. Now since I have done this here are things to consider.

    • Users do the darn-est things like map strings to fields which are numeric
    • Duplicate a column one or more times.
    • Try to violate constraints
    • etc.

    This means in the mapping process you most

    • Check if the mapping is valid type wise. In my code I check all values as just checking 8 or say 20 cells may not be enough if we are dealing with mixed types. Let's not forget dates, first off never assume they are stored as us humans view dates nuff said there.
    • Duplicate columns, I handled this in real time meaning the user selected a field to map via a DataGridView ComboBox column where I waited for the selection to be made then checked to see if it was in use.
    • Simple example on constraints, a start date can not be greater than a end date.

    Now suppose it is Friday, time to leave for the weekend and the user is not finished and most power down their PC. I thought of this and provided a method to 'remember' the state of the mapping. That in and of itself is not a trivial task.

    During the data exchange from Excel to table the process was coded that if there was an exception it would not stop the exchange but wrote to a log that was presented to the user. Remember I indicated a temp table? I present another form which allows the user to examine the issues, fix and continue or abort. Continue pushes data to a working table (this is all per business logic). Another part of the program allows this data to be worked over then finally pushes the data to production where my program stops as it is only responsible to importing Excel, CSV and raw PDF documents.

    Comments:

    • I mentioned that if the columns don't have column names it is difficult for users to map. So I allow another child form to display but deny them the ability to activate the form yet provide hot-keys in the current form to traverse the other forms Excel data. This in and of itself greatly assisted users to map and was not a trivial task.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Friday, January 31, 2014 12:09 PM
  • There is already a long while a sample on our website, how to convert a excel spreadsheet to a table.

    It uses a datagridview to show.

    Less then 20 rows of code.

    http://www.vb-tips.com/Excel2007.aspx


    Success
    Cor

    Friday, January 31, 2014 12:29 PM