Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Public Class ImportService
- Public Function ImportsFiles(ByVal files as list(Of String))
- Dim rowEffect As Integer = 0
- For j as Integer=0 to files.Count-1
- dim fileAddress=files(j)
- Dim app As New Application
- Dim Wbook As Workbook
- Try
- Wbook = app.Workbooks.Open(fileAddress, [ReadOnly]:=True)
- For i As Integer = 1 To Wbook.Sheets.Count
- If Not Wbook.Sheets(i).Name.ToString.ToLower.Contains("partial read of load profile") Then
- Dim con As New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & fileAddress & "; Extended Properties=""Excel 12.0 Xml;HDR=No;IMEX=1"";")
- Dim cmd As New OleDbCommand
- Dim s As String = "SELECT * FROM [" & Wbook.Sheets(i).Name & "$]"
- cmd.CommandText = s
- cmd.Connection = con
- Dim da As New OleDbDataAdapter
- da.SelectCommand = cmd
- Dim dt As New System.Data.DataTable
- da.Fill(dt)
- Dim err As New Dictionary(Of String, String)
- Dim fileDate As String = Wbook.Sheets(i).Name.ToString.Trim.Replace(" ", "/")
- rowEffect += InsertLps(Path.GetFileName(fileAddress), fileDate, dt.Rows, err)
- dt.Dispose()
- da.Dispose()
- con.Dispose()
- cmd.Dispose()
- End If
- ReleaseCOMObject(Wbook.Sheets(i))
- Next
- Wbook.Close(SaveChanges:=False)
- Catch ex As Exception
- summary.Add(fileAddress, ex.Message)
- Finally
- GC.Collect()
- GC.WaitForPendingFinalizers()
- GC.Collect()
- GC.WaitForPendingFinalizers()
- If app.Workbooks IsNot Nothing Then
- For Each wb In app.Workbooks
- For Each ws In wb.Worksheets
- ReleaseCOMObject(ws)
- Next
- wb.Close(False)
- ReleaseCOMObject(wb)
- Next
- End If
- app.Workbooks.Close()
- app.Quit()
- ReleaseCOMObject(app)
- GC.WaitForFullGCComplete()
- Next
- End Function
- End Class
- Public Class ImportJob
- Implements IJob
- Public Sub Execute(context As IJobExecutionContext) Implements IJob.Execute
- Dim importHelpers As New ImportFilesHelpers
- Dim exts As New List(Of String)
- exts.Add(".xls")
- Dim files = importHelpers.GetFiles(ImportFilesHelpers.BaseAssress, exts)
- Dim import = New ImportService()
- import.ImportsFiles(files)
- End Sub
- End Class
- Dim fileDate As String = Wbook.Sheets(i).Name.ToString.Trim.Replace(" ", "/")
Add Comment
Please, Sign In to add comment