Imports System.Data
Imports System.Data.OleDb
Imports System.IO
Imports System.Linq
Public Class frmCustosCT
Dim StrarquivoExcel As String
Dim StrCountReg As String = ""
Dim connectionString As String
' Valor de la celda
Dim StrValorTexto As Object
Private Sub CarregaDadosExcel()
Try
Dim dt As DataTable = GetTabelaExcel(StrarquivoExcel)
dgvDadoImport.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
dgvDadoImport.DataSource = dt
StrCountReg = (dgvDadoImport.Rows.Count - 1).ToString()
GroupBox2.Text = GroupBox2.Text + " ( " + StrCountReg + " ) Registros"
Dim listaNomeColunas As String() = dt.Columns.OfType(Of DataColumn)().[Select](Function(x) x.ColumnName).ToArray()
Catch ex As Exception
MessageBox.Show("Erro :" & ex.Message) End Try
End Sub
Private Function GetTabelaExcel(ByVal arquivoExcel As String) As DataTable
Dim dt As DataTable = New DataTable()
Try
arquivoExcel = txtArquivoExcel.Text
Dim Ext As String = Path.GetExtension(arquivoExcel)
If Ext = ".xls" Then
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" & arquivoExcel & "; Extended Properties = 'Excel 8.0;HDR=YES'"
ElseIf Ext = ".xlsx" Then
connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" & arquivoExcel & "; Extended Properties = 'Excel 8.0;HDR=YES'"
End If
Dim conn As OleDbConnection = New OleDbConnection(connectionString)
Dim cmd As OleDbCommand = New OleDbCommand()
Dim dataAdapter As OleDbDataAdapter = New OleDbDataAdapter()
cmd.Connection = conn
conn.Open()
Dim dtSchema As DataTable
dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
Dim nomePlanilha As String = dtSchema.Rows(0)("TABLE_NAME").ToString() conn.Close()
conn.Open()
cmd.CommandText = "SELECT * From [" & nomePlanilha & "]"
dataAdapter.SelectCommand = cmd
dataAdapter.Fill(dt)
conn.Close()
Catch ex As Exception
Throw ex
End Try
Return dt
End Function
Private Sub BtnLocalizaArquivoExcel_Click(sender As Object, e As EventArgs) Handles btnLocalizaArquivoExcel.Click
Dim drResult As DialogResult = ofd1.ShowDialog()
Try
If drResult = System.Windows.Forms.DialogResult.OK Then txtArquivoExcel.Text = ofd1.FileName
If Not String.IsNullOrEmpty(txtArquivoExcel.Text) AndAlso File.Exists(txtArquivoExcel.Text) Then
StrarquivoExcel = txtArquivoExcel.Text
CarregaDadosExcel()
Else
CarregaDadosExcel()
End If
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Private Sub DgvDadoImport_CellDoubleClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvDadoImport.CellDoubleClick
If dgvDadoImport.Rows.Count = 0 Then
Exit Sub
Else
Dim currentCell As DataGridViewCell = dgvDadoImport.CurrentCell
If (Not currentCell Is Nothing) Then
StrValorTexto = currentCell.Value
End If
Dim valor As String = StrValorTexto
txtvalor_de.Text = valor.Replace(",", ".")
'Dim format As System.Globalization.NumberFormatInfo = New System.Globalization.NumberFormatInfo()
'format.NumberDecimalSeparator = "."
'Dim valor As Decimal = StrValorTexto
'' MessageBox.Show(valor.ToString(format))
'txtvalor_de.Text = valor.ToString(format)
End If
End Sub
End Class
vea en mi primera imagen donde localizo la ruta de conexion donde está el archivo excel.
en la segunda imagen muestra claramente quien está recibiendo el camino atravez del string "StrarquivoExcel" mi datatable recibe el archivo y mi objeto "dgvDadoImport " entiende que existe un valor positivo atravez del recurso "AutoSizeColumnsMode"
al recibir mi datatable quien realmente cargar las informaciones es mi datagrid, y mi string listaNomeColunas, consigue dibujar las tablas en el datagrid,