Atendiendo a la petición de Sefvense en los foros de desarrollo : http://social.msdn.microsoft.com/Forums/es-ES/42c2ade6-a9d8-4b8e-bbe0-79b9d07e794f/estoy-tratando-de-importar-un-documento-csv-a-un-datagridview-en-una-aplicacion-para-windows-form?forum=winformses
os anticipo un helper que estoy preparando para aquellos que necesitan leer de un Excel y presentarlo parcialmente en un DataGridView.
No olvidéis importar el «Microsoft.office.Interop» correspondiente.
Imports Excel = Microsoft.Office.Interop.Excel
Public Class excelHelper
Private workApp As New Excel.Application
Private worksheet As Excel.Worksheet
Private workbook As Excel.Workbook
''' <summary>
''' return a datatable with a customized content from xls sheet
''' </summary>
''' <param name="filePath"></param>
''' <param name="sheetName"></param>
''' <param name="headToFind"></param>
''' <param name="model"></param>
''' <returns></returns>
''' <remarks></remarks>
Function xlsToDataset_CustomModels(filePath As String, sheetName As String, headToFind As Date, model As dsModel) As DataTable
Try
workbook = workApp.Workbooks.Open(filePath, , True)
worksheet = xlsFindSheet(workbook, sheetName)
If worksheet Is Nothing Then Throw New Exception(String.Format("worksheet : {0}, not found in this book.", sheetName))
Dim dt As New DataTable
Select Case model
Case dsModel.model_a
Dim rowSecondColTitle = 7
Dim CellrangeFrom = 1
Dim CellrangeTo = 200
Dim fromColA As Integer = xlsFindColContent(headToFind, rowSecondColTitle, 4, 10)
Dim fromColB As Integer = 3
Dim colAContent As String = "Total"
dt = fillCustomDatasetModel_A("Descripción", CellrangeFrom, CellrangeTo, rowSecondColTitle, fromColA, colAContent, fromColB, fromColA + 1)
Case dsModel.model_b
' whatever call
Case dsModel.model_n
' more whatever
End Select
workApp.ActiveWorkbook.Close(False, filePath)
workApp.Quit()
Return dt
Catch ex As Exception
workApp.Quit()
Return Nothing
End Try
End Function
''' <summary>
''' return a customized datatable Model A
''' </summary>
''' <param name="Title"></param>
''' <param name="rowRangefrom"></param>
''' <param name="rowRangeTo"></param>
''' <param name="rowForSecondColTitle"></param>
''' <param name="fromCol"></param>
''' <param name="whereContain"></param>
''' <param name="fillForCol_A"></param>
''' <param name="fillForCol_B"></param>
''' <returns></returns>
''' <remarks></remarks>
Function fillCustomDatasetModel_A(Title As String, rowRangefrom As Integer, rowRangeTo As Integer, rowForSecondColTitle As Integer, fromCol As Integer, whereContain As String, fillForCol_A As Integer, fillForCol_B As Integer) As DataTable
Dim dt As New DataTable()
Dim val As Double
dt.Columns.Add(Title)
dt.Columns.Add(worksheet.Cells(rowForSecondColTitle, fromCol).Value)
For index = rowRangefrom To rowRangeTo
If Not (worksheet.Cells(index, fillForCol_A).Value Is Nothing) Then
If worksheet.Cells(index, fillForCol_A).Value.ToString.Contains(whereContain) Then
val = Convert.ToDouble(worksheet.Cells(index, fillForCol_B).Value)
dt.Rows.Add(worksheet.Cells(index, fillForCol_A).Value.ToString.Remove(0, whereContain.Length + 1), val.ToString("#000,000"))
End If
End If
Next
Return dt
End Function
''' <summary>
''' find col content into a row range
''' </summary>
''' <param name="findStr"></param>
''' <param name="findRow"></param>
''' <param name="startingAt"></param>
''' <param name="endingAt"></param>
''' <returns></returns>
''' <remarks></remarks>
Function xlsFindColContent(findStr As String, findRow As Integer, Optional startingAt As Integer = 0, Optional endingAt As Integer = 0) As Integer
For index = startingAt To endingAt
If findStr = worksheet.Cells(findRow, index).Value Then
Return index
End If
Next
Return 0
End Function
''' <summary>
''' find sheet by name
''' </summary>
''' <param name="books"></param>
''' <param name="sheetName"></param>
''' <returns></returns>
''' <remarks></remarks>
Function xlsFindSheet(books As Excel.Workbook, sheetName As String) As Excel.Worksheet
Try
For Each wb In workbook.Sheets
If CType(wb, Excel.Worksheet).Name = sheetName Then
Return wb
End If
Next
Return Nothing
Catch ex As Exception
Return Nothing
End Try
End Function
''' <summary>
''' enum for to identify models
''' </summary>
''' <remarks></remarks>
Enum dsModel
model_a
model_b
model_n
End Enum
End Class
En este ejemplo componemos un datagridview con el modelo predefinido como «dsModel.model_a» donde leemos los totales a partir de la columna 7 buscando una fecha concreta, el contenido de los «rows» creados en el datagridview corresponden a los registros que contienen «Total» y su columna coincide con la fecha solicitada.
Dim excelObj = New excelHelper
Dim findDate As New Date(2014,05,21)
Dim sheetName = "SheetName"
dgv.DataSource = excelObj.xlsToDataset_CustomModels(My.Settings.xlsFilePath, sheetName, findDate, excelHelper.dsModel.model_a)
Como digo es un punto de entrada para aportar una idea, pero me comprometo a componer un ejemplo completo que ayude en su comprensión.
Saludos,
PepLluis,
