RE:LLENAR ARRAYS DESDE EXCEL
'Suponto ya hiciste la referencia a Excel.....
'Este eejmplo abre excel sin mostrarlo, coge los datos de las columnas usadas, filas usdadas, llena el arreglo con la informacion de las celdas de excel, llena un datagrid en vb.net...
espero te sirva...............
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim numFusadas, numCusadas As Integer
Dim file As String
Dim Myarray(0, 0)
Dim myTable As DataTable
Dim myRow As DataRow
Dim infarray As Integer
Private Sub cmdFillArray_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdFillArray.Click
file = "C:\Documents and Settings\Laptop\Mis documentos\ExceltoVB.xls"
'xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
xlBook = xlApp.Workbooks.Open(file) ')',, Excel.Workbook)
xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
'MsgBox(xlApp.ActiveWorkbook.Worksheets(1).Count.ToString)
xlApp.ActiveWorkbook.Worksheets(1).Select() 'activa la hoja que deseas
'xlapp.ActiveWorkbook.CustomDocu
numFusadas = xlSheet.UsedRange.Rows.Count - 1
numCusadas = xlSheet.UsedRange.Columns.Count
ReDim Myarray(numFusadas, numCusadas)
myTable = MakeNamesTable()
'Llena el arreglo Myarray
For i = 1 To numFusadas
For x = 1 To numCusadas '
Myarray(i, x) = xlSheet.Cells(i, x).Value
Next
Next
infarray = Myarray.GetUpperBound(0)
For i = 2 To infarray
myRow = myTable.NewRow()
myRow("IdPedido") = CInt(Myarray(i, 1))
myRow("IdEmpleado") = CInt(Myarray(i, 2))
myRow("FechaPedido") = Format(Myarray(i, 3), "Short Date")
myRow("Destinatario") = Myarray(i, 4)
myTable.Rows.Add(myRow)
Next
'llenar grid
DataGrid1.DataSource = myTable
'internamente cierra excel y lo libera
xlBook.Close(False)
xlApp.Quit()
xlApp = Nothing
End Sub
Private Function MakeNamesTable() As DataTable
' Cre un datatable llamado 'Pedidos.'
Dim namesTable As DataTable = New DataTable("Names")
' inserta 4 columnas a la tabla.
Dim IdPedidoCol As DataColumn = New DataColumn
IdPedidoCol.DataType = System.Type.GetType("System.Int32")
IdPedidoCol.ColumnName = "IdPedido"
namesTable.Columns.Add(IdPedidoCol)
Dim IdEmpleadoCol As DataColumn = New DataColumn
IdEmpleadoCol.DataType = System.Type.GetType("System.Int32")
IdEmpleadoCol.ColumnName = "IdEmpleado"
namesTable.Columns.Add(IdEmpleadoCol)
Dim FechaPedCol As DataColumn = New DataColumn
FechaPedCol.DataType = System.Type.GetType("System.DateTime")
FechaPedCol.ColumnName = "FechaPedido"
namesTable.Columns.Add(FechaPedCol)
Dim DestinatarioCol As DataColumn = New DataColumn
DestinatarioCol.DataType = System.Type.GetType("System.String")
DestinatarioCol.ColumnName = "Destinatario"
namesTable.Columns.Add(DestinatarioCol)
' Crea un arreglo para el datacolumn
Dim keys(0) As DataColumn
keys(0) = IdPedidoCol
namesTable.PrimaryKey = keys
'devuelve el datatable
MakeNamesTable = namesTable
End Function
