VS 2015 Formatting problems when importing Excel to DatagridView-VBForums
Results 1 to 12 of 12

Thread: Formatting problems when importing Excel to DatagridView

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    31

    Formatting problems when importing Excel to DatagridView

    Im developing a windows app that imports data from external sources into an unbound data grid, applies calculations and manipulations to the data and then exports the output. Since the structure of the source varies, there is no formatting set to the grid columns.

    The problem: sometimes, when I import from Excel, the representation of certain data types changes. For example, a DateTime column that looks like 2017-01-01 11:23:14 in Excel, will be shown as 2017/01/01 11:23:14 in the grid. This is not a normal representation of a DateTime for the country settings on my machine.

    Im using connection string as follows:

    Code:
    strCon = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source='" & p & "'; " & "Extended Properties='Excel 12.0 Xml;HDR=NO;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text'")
    Experimenting, Ive made some observations:

    1. If I save the Excel file as .csv and import the .csv-file there is no problem (no OleDB involved, I just read the text file line by line and split it at the delimiter)

    2. If there is no header in the source file there is no problem. My guess is that the problem arises when it is determined that the column contains mixed datatypes (string header and the rest DateTime)

    3. It only happens to some source files. I can have two Excel files with identical formatting and identical content in the cells, still with one there is no problem but with the other one there is. I receive the files from external clients and wonder if the settings on the computer that the file is created on may play a role, but then Ive read that no Excel formatting is recognized by DatagridView, but everything is treated as string.

    Does anyone have a solution, or an idea why I have this problem?

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    97,103

    Re: Formatting problems when importing Excel to DatagridView

    Quote Originally Posted by Mzungo View Post
    If there is no header in the source file there is no problem. My guess is that the problem arises when it is determined that the column contains mixed datatypes (string header and the rest DateTime)
    Are you changing "HDR=NO" to "HDR=YES" in the connection string when there is a header row?
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    31

    Re: Formatting problems when importing Excel to DatagridView

    I am not. Its an unbound data grid with columns already in it. If I set HDR=YES, the row with column names is simply omitted from the import. I want the column headers to be in the first row of the grid, not appear as the actual column names (they won't since columns already exists).

    I guess what I would like is for the testing for mixed data types to start at the second row. Is this impossible to accomplish? The fact that I have no problem with most source files suggest my problem is solvable.

  4. #4
    Junior Member
    Join Date
    May 2012
    Posts
    26

    Re: Formatting problems when importing Excel to DatagridView

    Does the column containing the dates have other data types, such as simple text? The Excel data access driver makes a guess as to the data types of each column depending upon the contents of the first eight rows, by default. It sounds like the column is being treated as text instead of date. This can be changed through the Registry, but depending upon how mixed the data is you may need to format the data before loading the DataGridView in order to obtain the desired results.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    31

    Re: Formatting problems when importing Excel to DatagridView

    Thanks for your reply. I agree that the problem is that the column is treated as text. Since i have HDR=NO (because I want the headers in the first row, not being column names), the header is a string, there is mixed types and since I use IMEX=1 (so as to not lose any data) everything is treated as text. And for my purposes, thats fine.

    What I cannot grasp is why the text representation of a DateTime is sometimes YYYY-MM-DD hh:mm:ss and sometimes YYYY/MM/DD hh:mm. Anything would be fine, really, but since I allow users of my app to save anything they do to a source file in a database and repeat it all with a click, I need the import to be consistent given that the source file is the same.

  6. #6
    Junior Member
    Join Date
    May 2012
    Posts
    26

    Re: Formatting problems when importing Excel to DatagridView

    Kind of difficult to answer since I cannot see the data in your Excel Workbook. Could that be where the format differs? Like I said, you can format the data when adding it to the DataGridView, but relying on all of the data to be formatted the same way when the data is mixed will be a crapshoot because you're asking the Excel driver to figure it out for you.
    Paul ~~~~ Microsoft MVP (Visual Basic)

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    31

    Re: Formatting problems when importing Excel to DatagridView

    No, the excel formatting is exactly the same, thats what puzzles me. Anyways, thanks for your input, Ill keep tryin stuff and post any solution here.

  8. #8
    Addicted Member
    Join Date
    Sep 2017
    Posts
    213

    Re: Formatting problems when importing Excel to DatagridView

    Are you using an oledbReader to fill the grid? Are you creating the columns in the DGV dynamically? Have you tried using a Datatable? I would probably be trying to force an exception by reading the date into a datetime type var/object. Maybe try the excel or Jet drivers also. I feel I have been down every road piddling with excel datasources.

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    31

    Re: Formatting problems when importing Excel to DatagridView

    Im using the OleDbDataDataAdapter to create a datatable and then I add the rows from the table manually, i.e. dgv.Rows.Add(.Tables(0).Rows(i).Item(0).ToString. Since I use the grid to import different files with different structures, I dont know in advance what format each column will have (strings are fine, I just want consistency.

    Ill try with the jet driver.

  10. #10
    Fanatic Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    732

    Re: Formatting problems when importing Excel to DatagridView

    Hi,

    this will fill to Listview, you would have to alter it to Datagridview.
    see if it helps

    Code:
    Public Class excelToListview
        Private objConnection As OleDbConnection
        Private objCommand As OleDbCommand
        Private objDataAdapter As OleDbDataAdapter
        Private objDataTable As System.Data.DataTable
       
        Private PrmPathExcelFile As String = "C:\TestExcelToAccess.xls"
        Private strConnectionString As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & PrmPathExcelFile & ";" & "Extended Properties=""Excel 8.0;"""
        '--------------------------------------------------------
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            objConnection = New OleDbConnection(strConnectionString)
            'select sheet
            objCommand = New OleDbCommand("SELECT * FROM [Sheet1$]", objConnection)
            'select Range
            'objCommand = New OleDbCommand("SELECT * FROM [Sheet1$A1:D10]", objConnection)
    
            'select with Letter
            'objCommand = New OleDbCommand("SELECT Artikelname, Liefereinheit FROM [Sheet1$] Where Artikelname Like 'p%'", objConnection)
    
            objDataAdapter = New OleDbDataAdapter
            objDataAdapter.SelectCommand = objCommand
            objDataTable = New System.Data.DataTable
            objDataAdapter.Fill(objDataTable)
            ListViewFillFromDataTable(ListView1, objDataTable)
    
            'Clean(up)
            objDataAdapter.Dispose()
            objDataAdapter = Nothing
            objCommand.Dispose()
            objCommand = Nothing
            objConnection.Dispose()
            objConnection = Nothing
    
        End Sub
        Public Sub ListViewFillFromDataTable(ByVal Lvw As ListView, ByVal Dt As System.Data.DataTable, _
                                            Optional ByVal UseItemStyleForSubItems As Boolean = False, _
                                            Optional ByVal ColumnsAutoSize As Boolean = True, _
                                            Optional ByVal FormatDecimal As String = "0.00")
    
            With Lvw
                .View = View.Details
                .GridLines = True
                .HideSelection = False
                .FullRowSelect = True
                .LabelEdit = False
                .Items.Clear()
                .Columns.Clear()
                'Header
                For i As Integer = 0 To Dt.Columns.Count - 1
                    Dim Key As String = Dt.Columns(i).ColumnName
                    .Columns.Add(Key)
                    Select Case Dt.Columns(i).DataType.Name
                        Case "String", "DateTime", "Char", "Boolean"
                        Case Else
                            .Columns(i).TextAlign = HorizontalAlignment.Right
                    End Select
                Next
                .BeginUpdate()
                .SuspendLayout()
                'fill Listview
                For i As Integer = 0 To Dt.Rows.Count - 1
                    Dim Li As New ListViewItem
                    Dim Row As DataRow = Dt.Rows(i)
                    Li.Tag = Row
                    Li.UseItemStyleForSubItems = UseItemStyleForSubItems
                    For j As Integer = 0 To Row.ItemArray.GetUpperBound(0)
                        Dim s As String = Nothing
                        If Not Row.IsNull(j) Then
                            s = CStr(Row.Item(j))
                            If Row.Item(j).GetType.Name = "Decimal" Then
                                If Not String.IsNullOrEmpty(FormatDecimal) Then
                                    s = Convert.ToDecimal(s).ToString(FormatDecimal)
                                End If
                            End If
                        End If
                        If j = 0 Then
                            Li.Text = s
                        Else
                            Li.SubItems.Add(s)
                        End If
                    Next
                    .Items.Add(Li)
                Next
    
                If ColumnsAutoSize Then
                    .AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize)
                End If
                .ResumeLayout()
                .EndUpdate()
            End With
        End Sub
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  11. #11
    Junior Member
    Join Date
    May 2012
    Posts
    26

    Re: Formatting problems when importing Excel to DatagridView

    Quote Originally Posted by Mzungo View Post
    Im using the OleDbDataDataAdapter to create a datatable and then I add the rows from the table manually, i.e. dgv.Rows.Add(.Tables(0).Rows(i).Item(0).ToString. Since I use the grid to import different files with different structures, I dont know in advance what format each column will have (strings are fine, I just want consistency.

    Ill try with the jet driver.
    Did you check the Registry setting for the OLEDB provider you are working with?

    http://dougbert.com/blog/post/import...ata-types.aspx
    Paul ~~~~ Microsoft MVP (Visual Basic)

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Sep 2013
    Posts
    31

    Re: Formatting problems when importing Excel to DatagridView

    Sorry for not replying, ran into another problem that needs fixing. Chris, Ill try your code and report if it works better.

    I dont think theres a problem with the registry settings. Everything works as expected if I, for example, explicitly format everything in the worksheet to text (=Text(Cell;"YYYY-MM-DD etc..) or If I lose the headers. Pretty sure there are mixed data types and that the problem occurs when whatever data excel sends is interpreted as text.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.