Getting F1 on Datagridview header when Importing from Excel (VB.Net)

Question

I have in my project 2 buttons to import from Excel and export to Excel and a datagridview. I can import Excel files with no problem but when I export from datagridview to Excel and try to re-import that file I only get just one column header with "F1" in it:

This is the code for export button:

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    'Creamos las variables
    Dim exApp As New Excel.Application
    Dim exLibro As Excel.Workbook
    Dim exHoja As Excel.Worksheet

    Try
        'Añadimos el Libro al programa, y la hoja al libro
        exLibro = exApp.Workbooks.Add
        exHoja = exLibro.Worksheets.Add()
        ' ¿Cuantas columnas y cuantas filas?
        Dim NCol As Integer = DataGridView1.ColumnCount
        Dim NRow As Integer = DataGridView1.RowCount
        'Aqui recorremos todas las filas, y por cada fila todas las columnas

        'y vamos escribiendo.
        For i As Integer = 1 To NCol
            exHoja.Cells.Item(1, i) = DataGridView1.Columns(i - 1).Name.ToString
        Next
        For Fila As Integer = 0 To NRow - 1
            For Col As Integer = 0 To NCol - 1
                exHoja.Cells.Item(Fila + 2, Col + 1) = DataGridView1.Item(Col, Fila).Value
            Next
        Next
        'Titulo en negrita, Alineado al centro y que el tamaño de la columna
        'se ajuste al texto
        exHoja.Rows.Item(1).Font.Bold = 1
        exHoja.Rows.Item(1).HorizontalAlignment = 3
        exHoja.Columns.AutoFit()

        'Aplicación visible
        exApp.Application.Visible = True
        exHoja = Nothing
        exLibro = Nothing
        exApp = Nothing
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

What can I do to solve it??

Thanks.

Best guess is that you will need in the first row of Excel column names. Example, if you were to use OleDb to import and there is no column names in the first row and under extended properties of the connection you set HDR= "Yes" the first row would be seen as data if no column headers while HDR="No" the reverse. I have not seen the F1 issue except for OleDb and is recommended as shown here SELECT F1 As FirstName instead of say SELECT * which causes the Fn where n is incremented for each column in the sheet — Karen Payne
– Karen Payne, Commented Jan 19, 2016 at 20:15

Karen Payne · Accepted Answer · 2016-01-19 23:47:38Z

For an import from Excel where the top image depicts what happens using SELECT *

The bottom image shows using field aliasing as shown in the code below

Code to read sheet data using aliasing

Dim dt As New DataTable
Dim FileName As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "SampleData.xlsx")

Using cn As New OleDb.OleDbConnection With
    {
        .ConnectionString = ConnectionHelper.ConnectionString(FileName, "No")
    }
    Console.WriteLine(cn.ConnectionString)
    ' .CommandText = "SELECT F1 As FirstName, F2 As MiddleName, F3 As LastName FROM [PeopleData$] ORDER BY F3",

    Using cmd As New OleDb.OleDbCommand With
        {
            .CommandText = "SELECT F1 As FirstName, F2 As MiddleName, F3 As LastName FROM [Sheet1$] ORDER BY F3",
            .Connection = cn
        }
        cn.Open()
        dt.Load(cmd.ExecuteReader)
    End Using
End Using
DataGridView1.DataSource = dt

Code for above to set connection

Public Module ConnectionHelper
    Public Function ConnectionString(ByVal FileName As String) As String
        Dim Builder As New OleDb.OleDbConnectionStringBuilder
        If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
            Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
            Builder.Add("Extended Properties", "Excel 8.0;IMEX=2;HDR=No;")
        Else
            Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
            Builder.Add("Extended Properties", "Excel 12.0;IMEX=2;HDR=No;")
        End If

        Builder.DataSource = FileName

        Return Builder.ConnectionString
    End Function
    Public Function ConnectionString(ByVal FileName As String, ByVal Header As String) As String
        Dim Builder As New OleDb.OleDbConnectionStringBuilder
        If IO.Path.GetExtension(FileName).ToUpper = ".XLS" Then
            Builder.Provider = "Microsoft.Jet.OLEDB.4.0"
            Builder.Add("Extended Properties", String.Format("Excel 8.0;IMEX=1;HDR={0};", Header))
        Else
            Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
            Builder.Add("Extended Properties", String.Format("Excel 12.0;IMEX=1;HDR={0};", Header))
        End If

        Builder.DataSource = FileName

        Return Builder.ConnectionString
    End Function
End Module

Note the sheet has no column name, ony data. If the first row had column names the connection would have Yes as argument 2 rather than No as above .ConnectionString = ConnectionHelper.ConnectionString(FileName, "Yes")

user3822492 · Accepted Answer · 2016-01-21 02:22:24Z

I found the solution, the problem wasn´t the Import button, instead it seems to be the Export button because it export the file with some kind of damage. I tried with other code I found (to export) and worked, This is the working code:

 Dim stRuta As String = ""
    Dim openFD As New OpenFileDialog()
    With openFD
        .Title = "Seleccionar archivos"
        .Filter = "Archivos Excel(*.xls;*.xlsx)|*.xls;*xlsx|Todos los archivos(*.*)|*.*"
        .Multiselect = False
        .InitialDirectory = My.Computer.FileSystem.SpecialDirectories.Desktop
        If .ShowDialog = Windows.Forms.DialogResult.OK Then
            stRuta = .FileName
        End If
    End With
    Try
        'Dim stConexion As String = ("Provider=Microsoft.ACE.OLEDB.12.0;" & ("Data Source=" & (stRuta & ";Extended Properties=""Excel 12.0;Xml;HDR=YES;IMEX=2"";")))
        Dim stConexion As String = ("Provider=Microsoft.ACE.OLEDB.12.0;" & ("Data Source=" & (stRuta & ";Extended Properties=""Excel 12.0;Xml;HDR=YES;IMEX=2"";")))
        Dim cnConex As New OleDbConnection(stConexion)
        Dim Cmd As New OleDbCommand("Select * From [Hoja1$]")
        Dim Ds As New DataSet
        Dim Da As New OleDbDataAdapter
        Dim Dt As New DataTable
        cnConex.Open()
        Cmd.Connection = cnConex
        Da.SelectCommand = Cmd
        Da.Fill(Ds)
        Dt = Ds.Tables(0)
        Me.DataGridView1.Columns.Clear()
        Me.DataGridView1.DataSource = Dt
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
    End Try

Thank you all :)

Collectives™ on Stack Overflow

Getting F1 on Datagridview header when Importing from Excel (VB.Net)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related