Hi Randy I have actually been working on the same type if thing and have a few functions that seem to work fine in my app. The first will import data from excel to a dataset.The second will export a data table to a delimted file. Anyways hope they help
Paul
Function Readxls(ByVal strfile As String, ByVal sheet As String)
'Imports Excel Sheet to Data Set
'Verify that the workbook to write to does exist.
If Dir(strfile) = "" Then
MsgBox("Please create the workbook test.xls and try again.")
Exit Function
End If
'Establish a connection to the data source.
Try
Dim sConnectionString, strcmd, strvals, strtbl As String
sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
strfile & " ;Extended Properties=Excel 8.0;"
'Here we can put more capabilities such as multiple sheets to multiple tables etc
Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
objConn.Open()
Dim dsexcel As New DataSet
Dim Da As New OleDbDataAdapter("SELECT * FROM [" & sheet & "$]", objConn)
Da.Fill(dsexcel)
objConn.Close()
Return dsexcel
Catch exp As Exception
Throw New Exception("An error has occured while attempting to Import XLS File" + vbLf + "Please Check The Sheet Name" + vbLf + vbLf + "If this does not work, please notify IT of the following error:" + vbLf + vbLf + exp.Message)
End Try
End Function
'And This Exports Data table to delimited File
Public Sub ExportDsToDelim(ByVal path As String, ByVal table As DataTable, ByVal delim As String)
Dim output As New StreamWriter(path, False, UnicodeEncoding.Default)
'****************************************************************************************************
'Exports Data Set to Delimited File
'
'****************************************************************************************************
Dim n As Integer = 0
' Write out the header row
Try
For Each col As DataColumn In table.Columns
If n = 0 Then
output.Write("")
Else
output.Write(delim)
End If
output.Write(col.ColumnName)
' delim = ","
n += 1
Next
output.WriteLine()
' write out each data row
For Each row As DataRow In table.Rows
' delim = ""
n = 0
For Each value As Object In row.ItemArray
If n = 0 Then
output.Write("")
Else
output.Write(delim)
End If
If TypeOf value Is String Then
output.Write(""""c) ' thats four double quotes and a c
output.Write(value)
output.Write(""""c) ' thats four double quotes and a c
Else
output.Write(value)
End If
' delim = ","
n += 1
Next
output.WriteLine()
Next
output.Close()
Catch exp As Exception
Throw New Exception("An error has occured while attempting to Create a Delimited File." + vbLf + "Please attempt to complete your previous action again." + vbLf + vbLf + "If this does not work, please notify IT of the following error:" + vbLf + vbLf + exp.Message)
End Try
End Sub
Précédent
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement