dim op as new OpenFileDialog
With op
.Filter = "Excel File(*.xlsx*)|*.xlsx*|Excel File(*.xls*)|*.xls*"
.Title = "Select Excel file"
If .ShowDialog = DialogResult.Cancel Then
MsgBox("User cancelled", vbInformation, "Excel")
openfilecancel = "filecancelled"
Exit Sub
Else
opfilename = op.FileName
End If
End With
use this function to open an excel file(.net)
Public Function GetExcel(ByVal fileName As String) As DataSet
Dim oXL As Microsoft.Office.Interop.Excel.Application
Dim oWB As Microsoft.Office.Interop.Excel.Workbook
Dim oSheet As Worksheet
Dim oRng As Range
Try
oXL = New Microsoft.Office.Interop.Excel.Application
oWB = oXL.Workbooks.Open(fileName)
' get WorkSheet object
oSheet = DirectCast(oWB.Sheets(1), Microsoft.Office.Interop.Excel.Worksheet)
For Each ws As Worksheet In oWB.Sheets
Next
Dim dt As New System.Data.DataTable("dtExcel")
Dim ds As New DataSet()
ds.Tables.Add(dt)
Dim dr As DataRow
Dim sb As New StringBuilder()
Dim jValue As Integer = oSheet.UsedRange.Cells.Columns.Count
Dim iValue As Integer = oSheet.UsedRange.Cells.Rows.Count
' get data columns
Dim j1 As Integer = 1
While j1 <= jValue
dt.Columns.Add("column" & j1, System.Type.GetType("System.String"))
System.Math.Max(System.Threading.Interlocked.Increment(j1), j1 - 1)
End While
'string colString = sb.ToString().Trim();
'string[] colArray = colString.Split(':');
' get data in cell
Dim i As Integer = 1
While i <= iValue
dr = ds.Tables("dtExcel").NewRow()
Dim j As Integer = 1
While j <= jValue
oRng = DirectCast(oSheet.Cells(i, j), Microsoft.Office.Interop.Excel.Range)
Dim strValue As String = oRng.Text.ToString()
dr("column" & j) = strValue
System.Math.Max(System.Threading.Interlocked.Increment(j), j - 1)
End While
ds.Tables("dtExcel").Rows.Add(dr)
System.Math.Max(System.Threading.Interlocked.Increment(i), i - 1)
ProgressBar1.PerformStep()
End While
Return ds
ds.Dispose()
dt.Dispose()
Catch ex As Exception
MsgBox(ex.Message, vbInformation, "ERROR")
Return Nothing
Finally
oWB.Close()
End Try
End Function
im a using datagrid to get its cell content
Comment