Is This the Fastest way to do it? Old CodeOld Code
replaced http://codereview.stackexchange.com/ with https://codereview.stackexchange.com/
Imports System.Collections
Imports System.Collections.Generic
Imports System.Collections.Concurrent
Imports System.Data
Imports System.Linq
Imports System.Net
Imports System.Net.Http
Imports System.IO
Imports System.Text
Imports System.Xml.Linq
Imports Microsoft.Office.Interop.Excel
Imports System.Threading.Tasks
Public Class MDI
Public Class ImgThreadParams
Public gtin As String
Public lUrls As List(Of String) = New List(Of String)()
End Class
Public Shared Function GetXlsxFiles(targetFolder As String) As String()
Dim files As String()
If targetFolder = vbNullString Then
files = System.IO.Directory.GetFiles(System.IO.Directory.GetCurrentDirectory(), "Export*.xlsx")
Else
If System.IO.Directory.Exists(targetFolder) = True Then
files = System.IO.Directory.GetFiles(targetFolder, "Export*.xls")
Else
files = System.IO.Directory.GetFiles(System.IO.Directory.GetCurrentDirectory(), "Export*.xlsx")
End If
End If
Return files
End Function
Public Shared Sub ProcessFolder(targetFolder As String)
Dim ListParams As New List(Of ImgThreadParams)()
Dim gtin As String = vbNullString
Dim files = GetXlsxFiles(targetFolder)
Dim excel As Application = New Application()
Try
For Each fileName As String In files
'Dim book = New LinqToExcel.ExcelQueryFactory(System.IO.Path.GetFileName(fileName))
Dim w As Workbook = excel.Workbooks.Open(fileName)
' Get sheet.
Dim sheet As Worksheet = CType(w.Sheets(1), Worksheet)
' Get range.
Dim r As Range = sheet.UsedRange()
Dim rArray(,) As Object = CType(r.Value(XlRangeValueDataType.xlRangeValueDefault), Object(,))
Dim hl As Integer = 0
Dim hn As Integer = 0
Dim rt As Integer = 0
Dim GtinC As Integer = 0
Dim Img1 As Integer = 0
Dim Img2 As Integer = 0
Dim Img3 As Integer = 0
Dim Img4 As Integer = 0
Dim Img5 As Integer = 0
Dim Img6 As Integer = 0
Dim Img7 As Integer = 0
Dim Img8 As Integer = 0
Dim Img9 As Integer = 0
Dim Img10 As Integer = 0
Dim ic As Integer = 0
Dim ic3 As Integer = 0
For j As Integer = 1 To r.Columns.Count()
If rArray(4, j).ToString().StartsWith("GTIN") Then
GtinC = j
ElseIf rArray(4, j).Equals("Record type") Then
rt = j
ElseIf rArray(4, j).ToString.Equals("Hierarchy number") Or rArray(4, j).ToString.Equals("Hierarchy level") Then
ic3 = ic3 + 1
If (ic3 = 1) Then
hn = j
ElseIf ic3 = 2 Then
hl = j
End If
ElseIf rArray(4, j).ToString.StartsWith("Image") And Not rArray(4, j).ToString.EndsWith("Description") And Not rArray(4, j).ToString.Contains("File") Then
ic = ic + 1
Select Case ic
Case 1
Img1 = j
Case 2
Img2 = j
Case 3
Img3 = j
Case 4
Img4 = j
Case 5
Img5 = j
Case 6
Img6 = j
Case 7
Img7 = j
Case 8
Img8 = j
Case 9
Img9 = j
Case 10
Img10 = j
Case Else
Exit Select
End Select
End If
Next
Dim additem As Boolean = False
For i As Integer = 5 To r.Rows.Count()
'check Hierarchy Number Level and record Type to filter for only top level GTIN's to download
If (CType(rArray(i, hn), Integer) = 1 And CType(rArray(i, hl), Integer) = 1 And CType(rArray(i, rt), Integer) = 1) Then
GoTo ContinueLoop
Else
Continue For
End If
ContinueLoop:
Dim itp As ImgThreadParams = New ImgThreadParams()
itp.gtin = CType(rArray(i, GtinC), String)
If (rArray(i, Img1) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img1), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img1), String))
additem = True
End If
End If
If (rArray(i, Img2) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img2), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img2), String))
End If
End If
If (rArray(i, Img3) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img3), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img3), String))
End If
End If
If (rArray(i, Img4) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img4), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img4), String))
End If
End If
If (rArray(i, Img5) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img5), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img5), String))
End If
End If
If (rArray(i, Img6) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img6), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img6), String))
End If
End If
If (rArray(i, Img7) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img7), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img7), String))
End If
End If
If (rArray(i, Img8) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img8), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img8), String))
End If
End If
If (rArray(i, Img9) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img9), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img9), String))
End If
End If
If (rArray(i, Img10) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img10), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img10), String))
End If
End If
If (additem = True) Then
ListParams.Add(itp)
additem = False
End If
Next
rArray = Nothing
w.Close()
Runtime.InteropServices.Marshal.ReleaseComObject(r)
Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
Runtime.InteropServices.Marshal.ReleaseComObject(w)
Runtime.InteropServices.Marshal.ReleaseComObject(excel)
Dim Folder As DirectoryInfo = System.IO.Directory.CreateDirectory(System.IO.Directory.GetCurrentDirectory() + "\Image")
If ListParams.Count > 10 Then
Dim tCount As Integer = CInt(Math.Round(ListParams.Count / 10, MidpointRounding.ToEven))
Dim L1 As New List(Of ImgThreadParams)
For i As Integer = 0 To tCount
L1.Add(ListParams(i))
Next
Dim L2 As New List(Of ImgThreadParams)
For i As Integer = tCount + 1 To tCount * 2
L2.Add(ListParams(i))
Next
Dim L3 As New List(Of ImgThreadParams)
For i As Integer = tCount * 2 + 1 To tCount * 3
L3.Add(ListParams(i))
Next
Dim L4 As New List(Of ImgThreadParams)
For i As Integer = tCount * 3 + 1 To tCount * 4
L4.Add(ListParams(i))
Next
Dim L5 As New List(Of ImgThreadParams)
For i As Integer = tCount * 4 + 1 To tCount * 5
L5.Add(ListParams(i))
Next
Dim L6 As New List(Of ImgThreadParams)
For i As Integer = tCount * 5 + 1 To tCount * 6
L6.Add(ListParams(i))
Next
Dim L7 As New List(Of ImgThreadParams)
For i As Integer = tCount * 6 + 1 To tCount * 7
L7.Add(ListParams(i))
Next
Dim L8 As New List(Of ImgThreadParams)
For i As Integer = tCount * 7 + 1 To tCount * 8
L8.Add(ListParams(i))
Next
Dim L9 As New List(Of ImgThreadParams)
For i As Integer = tCount * 8 + 1 To tCount * 9
L9.Add(ListParams(i))
Next
Dim L10 As New List(Of ImgThreadParams)
For i As Integer = tCount * 9 + 1 To ListParams.Count - 1
L10.Add(ListParams(i))
Next
Threading.ThreadPool.SetMaxThreads(1000, 1000)
Dim t1, t2, t3, t4, t5, t6, t7, t8, t9, t10 As Threading.ParameterizedThreadStart
t1 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t2 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t3 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t4 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t5 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t6 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t7 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t8 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t9 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t10 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t1.Invoke(L1)
t2.Invoke(L2)
t3.Invoke(L3)
t4.Invoke(L4)
t5.Invoke(L5)
t6.Invoke(L6)
t7.Invoke(L7)
t8.Invoke(L8)
t9.Invoke(L9)
t10.Invoke(L10)
Else
Dim t1 As Threading.ParameterizedThreadStart
t1 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t1.Invoke(ListParams)
End If
Next
Catch ex As Exception
Exit Sub
End Try
End Sub
Public Shared Sub ThreadedGetGtinImages(Params As List(Of ImgThreadParams))
Dim wc As HttpClient = New HttpClient()
Const ItemBatch As Integer = 100
Parallel.ForEach(Partitioner.Create(0, Params.Count, ItemBatch), Async Sub(range)
Dim imgCount As Integer = 1
For i As Integer = range.Item1 To range.Item2 - 1
Dim item = Params(i)
imgCount = 1
For Each surl In item.lUrls
Try
Dim response As HttpResponseMessage = Await wc.GetAsync(surl)
If (response.StatusCode = 200) Then
Dim sContentType As String = response.Content.Headers.ContentType.MediaType.ToString()
If (sContentType.Length > 0) Then
Dim sImage As String = sContentType.Split("/")(0).ToLower
Dim sImageType As String = sContentType.Split("/")(1).ToLower
If sImage.Equals("image") = True Or sImage.Equals("text") Then
Dim sFileExtension As String
Select Case sImageType
Case "jpeg"
sFileExtension = ".jpg"
Case "png"
sFileExtension = ".png"
Case "bmp"
sFileExtension = ".bmp"
Case "gif"
sFileExtension = ".gif"
Case "tiff"
sFileExtension = ".Tiff"
Case "html"
sFileExtension = ".html"
Case Else
Continue For
End Select
Dim sFolder = Directory.GetCurrentDirectory() + "\Image"
Using Fi As FileStream = New FileStream(sFolder + "\" + item.gtin + "_" + imgCount.ToString + sFileExtension, FileMode.OpenOrCreate)
Dim d = Await response.Content.ReadAsByteArrayAsync()
Await Fi.WriteAsync(d, 0, d.Length)
Await Fi.FlushAsync()
End Using
imgCount = imgCount + 1
End If
End If
End If
Catch
Continue For
End Try
Next
Next
End Sub)
End Sub
Public Shared Sub Main()
ServicePointManager.DefaultConnectionLimit = 10000
ProcessFolder(System.IO.Directory.GetCurrentDirectory())
Console.ReadLine()
End Sub
End Class
Public Class ImgThreadParams
Public gtin As String
Public lUrls As List(Of String) = New List(Of String)()
End Class
Public Shared Function GetXlsxFiles(targetFolder As String) As String()
Dim files As String()
If targetFolder = vbNullString Then
files = System.IO.Directory.GetFiles(System.IO.Directory.GetCurrentDirectory(), "Export*.xlsx")
Else
If System.IO.Directory.Exists(targetFolder) = True Then
files = System.IO.Directory.GetFiles(targetFolder, "Export*.xls")
Else
files = System.IO.Directory.GetFiles(System.IO.Directory.GetCurrentDirectory(), "Export*.xlsx")
End If
End If
Return files
End Function
Public Shared Sub ProcessFolder(targetFolder As String)
Dim ListParams As New List(Of ImgThreadParams)()
Dim gtin As String = vbNullString
Dim files = GetXlsxFiles(targetFolder)
Dim excel As Application = New Application()
Try
For Each fileName As String In files
'Dim book = New LinqToExcel.ExcelQueryFactory(System.IO.Path.GetFileName(fileName))
Dim w As Workbook = excel.Workbooks.Open(fileName)
' Get sheet.
Dim sheet As Worksheet = CType(w.Sheets(1), Worksheet)
' Get range.
Dim r As Range = sheet.UsedRange()
Dim rArray(,) As Object = CType(r.Value(XlRangeValueDataType.xlRangeValueDefault), Object(,))
Dim hl As Integer = 0
Dim hn As Integer = 0
Dim rt As Integer = 0
Dim GtinC As Integer = 0
Dim Img1 As Integer = 0
Dim Img2 As Integer = 0
Dim Img3 As Integer = 0
Dim Img4 As Integer = 0
Dim Img5 As Integer = 0
Dim Img6 As Integer = 0
Dim Img7 As Integer = 0
Dim Img8 As Integer = 0
Dim Img9 As Integer = 0
Dim Img10 As Integer = 0
Dim ic As Integer = 0
Dim ic3 As Integer = 0
For j As Integer = 1 To r.Columns.Count()
If rArray(4, j).ToString().StartsWith("GTIN") Then
GtinC = j
ElseIf rArray(4, j).Equals("Record type") Then
rt = j
ElseIf rArray(4, j).ToString.Equals("Hierarchy number") Or rArray(4, j).ToString.Equals("Hierarchy level") Then
ic3 = ic3 + 1
If (ic3 = 1) Then
hn = j
ElseIf ic3 = 2 Then
hl = j
End If
ElseIf rArray(4, j).ToString.StartsWith("Image") And Not rArray(4, j).ToString.EndsWith("Description") And Not rArray(4, j).ToString.Contains("File") Then
ic = ic + 1
Select Case ic
Case 1
Img1 = j
Case 2
Img2 = j
Case 3
Img3 = j
Case 4
Img4 = j
Case 5
Img5 = j
Case 6
Img6 = j
Case 7
Img7 = j
Case 8
Img8 = j
Case 9
Img9 = j
Case 10
Img10 = j
Case Else
Exit Select
End Select
End If
Next
Dim additem As Boolean = False
For i As Integer = 5 To r.Rows.Count()
'check Hierarchy Number Level and record Type to filter for only top level GTIN's to download
If (CType(rArray(i, hn), Integer) = 1 And CType(rArray(i, hl), Integer) = 1 And CType(rArray(i, rt), Integer) = 1) Then
GoTo ContinueLoop
Else
Continue For
End If
ContinueLoop:
Dim itp As ImgThreadParams = New ImgThreadParams()
itp.gtin = CType(rArray(i, GtinC), String)
If (rArray(i, Img1) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img1), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img1), String))
additem = True
End If
End If
If (rArray(i, Img2) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img2), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img2), String))
End If
End If
If (rArray(i, Img3) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img3), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img3), String))
End If
End If
If (rArray(i, Img4) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img4), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img4), String))
End If
End If
If (rArray(i, Img5) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img5), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img5), String))
End If
End If
If (rArray(i, Img6) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img6), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img6), String))
End If
End If
If (rArray(i, Img7) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img7), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img7), String))
End If
End If
If (rArray(i, Img8) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img8), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img8), String))
End If
End If
If (rArray(i, Img9) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img9), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img9), String))
End If
End If
If (rArray(i, Img10) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img10), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img10), String))
End If
End If
If (additem = True) Then
ListParams.Add(itp)
additem = False
End If
Next
rArray = Nothing
w.Close()
Runtime.InteropServices.Marshal.ReleaseComObject(r)
Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
Runtime.InteropServices.Marshal.ReleaseComObject(w)
Runtime.InteropServices.Marshal.ReleaseComObject(excel)
Dim Folder As DirectoryInfo = System.IO.Directory.CreateDirectory(System.IO.Directory.GetCurrentDirectory() + "\Image")
If ListParams.Count > 10 Then
Dim tCount As Integer = CInt(Math.Round(ListParams.Count / 10, MidpointRounding.ToEven))
Dim L1 As New List(Of ImgThreadParams)
For i As Integer = 0 To tCount
L1.Add(ListParams(i))
Next
Dim L2 As New List(Of ImgThreadParams)
For i As Integer = tCount + 1 To tCount * 2
L2.Add(ListParams(i))
Next
Dim L3 As New List(Of ImgThreadParams)
For i As Integer = tCount * 2 + 1 To tCount * 3
L3.Add(ListParams(i))
Next
Dim L4 As New List(Of ImgThreadParams)
For i As Integer = tCount * 3 + 1 To tCount * 4
L4.Add(ListParams(i))
Next
Dim L5 As New List(Of ImgThreadParams)
For i As Integer = tCount * 4 + 1 To tCount * 5
L5.Add(ListParams(i))
Next
Dim L6 As New List(Of ImgThreadParams)
For i As Integer = tCount * 5 + 1 To tCount * 6
L6.Add(ListParams(i))
Next
Dim L7 As New List(Of ImgThreadParams)
For i As Integer = tCount * 6 + 1 To tCount * 7
L7.Add(ListParams(i))
Next
Dim L8 As New List(Of ImgThreadParams)
For i As Integer = tCount * 7 + 1 To tCount * 8
L8.Add(ListParams(i))
Next
Dim L9 As New List(Of ImgThreadParams)
For i As Integer = tCount * 8 + 1 To tCount * 9
L9.Add(ListParams(i))
Next
Dim L10 As New List(Of ImgThreadParams)
For i As Integer = tCount * 9 + 1 To ListParams.Count - 1
L10.Add(ListParams(i))
Next
Threading.ThreadPool.SetMaxThreads(1000, 1000)
Dim t1, t2, t3, t4, t5, t6, t7, t8, t9, t10 As Threading.ParameterizedThreadStart
t1 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t2 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t3 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t4 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t5 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t6 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t7 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t8 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t9 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t10 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t1.Invoke(L1)
t2.Invoke(L2)
t3.Invoke(L3)
t4.Invoke(L4)
t5.Invoke(L5)
t6.Invoke(L6)
t7.Invoke(L7)
t8.Invoke(L8)
t9.Invoke(L9)
t10.Invoke(L10)
Else
Dim t1 As Threading.ParameterizedThreadStart
t1 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t1.Invoke(ListParams)
End If
Next
Catch ex As Exception
Exit Sub
End Try
End Sub
Public Shared Sub ThreadedGetGtinImages(Params As List(Of ImgThreadParams))
Dim wc As HttpClient = New HttpClient()
Const ItemBatch As Integer = 100
Parallel.ForEach(Partitioner.Create(0, Params.Count, ItemBatch), Async Sub(range)
Dim imgCount As Integer = 1
For i As Integer = range.Item1 To range.Item2 - 1
Dim item = Params(i)
imgCount = 1
For Each surl In item.lUrls
Try
Dim response As HttpResponseMessage = Await wc.GetAsync(surl)
If (response.StatusCode = 200) Then
Dim sContentType As String = response.Content.Headers.ContentType.MediaType.ToString()
If (sContentType.Length > 0) Then
Dim sImage As String = sContentType.Split("/")(0).ToLower
Dim sImageType As String = sContentType.Split("/")(1).ToLower
If sImage.Equals("image") = True Or sImage.Equals("text") Then
Dim sFileExtension As String
Select Case sImageType
Case "jpeg"
sFileExtension = ".jpg"
Case "png"
sFileExtension = ".png"
Case "bmp"
sFileExtension = ".bmp"
Case "gif"
sFileExtension = ".gif"
Case "tiff"
sFileExtension = ".Tiff"
Case "html"
sFileExtension = ".html"
Case Else
Continue For
End Select
Dim sFolder = Directory.GetCurrentDirectory() + "\Image"
Using Fi As FileStream = New FileStream(sFolder + "\" + item.gtin + "_" + imgCount.ToString + sFileExtension, FileMode.OpenOrCreate)
Dim d = Await response.Content.ReadAsByteArrayAsync()
Await Fi.WriteAsync(d, 0, d.Length)
Await Fi.FlushAsync()
End Using
imgCount = imgCount + 1
End If
End If
End If
Catch
Continue For
End Try
Next
Next
End Sub)
End Sub
Public Shared Sub Main()
ServicePointManager.DefaultConnectionLimit = 10000
ProcessFolder(System.IO.Directory.GetCurrentDirectory())
Console.ReadLine()
End Sub
Imports System.Collections
Imports System.Collections.Generic
Imports System.Collections.Concurrent
Imports System.Data
Imports System.Linq
Imports System.Net
Imports System.Net.Http
Imports System.IO
Imports System.Text
Imports System.Xml.Linq
Imports Microsoft.Office.Interop.Excel
Imports System.Threading.Tasks
Public Class MDI
Public Class ImgThreadParams
Public gtin As String
Public lUrls As List(Of String) = New List(Of String)()
End Class
Public Shared Function GetXlsxFiles(targetFolder As String) As String()
Dim files As String()
If targetFolder = vbNullString Then
files = System.IO.Directory.GetFiles(System.IO.Directory.GetCurrentDirectory(), "Export*.xlsx")
Else
If System.IO.Directory.Exists(targetFolder) = True Then
files = System.IO.Directory.GetFiles(targetFolder, "Export*.xls")
Else
files = System.IO.Directory.GetFiles(System.IO.Directory.GetCurrentDirectory(), "Export*.xlsx")
End If
End If
Return files
End Function
Public Shared Sub ProcessFolder(targetFolder As String)
Dim ListParams As New List(Of ImgThreadParams)()
Dim gtin As String = vbNullString
Dim files = GetXlsxFiles(targetFolder)
Dim excel As Application = New Application()
Try
For Each fileName As String In files
'Dim book = New LinqToExcel.ExcelQueryFactory(System.IO.Path.GetFileName(fileName))
Dim w As Workbook = excel.Workbooks.Open(fileName)
' Get sheet.
Dim sheet As Worksheet = CType(w.Sheets(1), Worksheet)
' Get range.
Dim r As Range = sheet.UsedRange()
Dim rArray(,) As Object = CType(r.Value(XlRangeValueDataType.xlRangeValueDefault), Object(,))
Dim hl As Integer = 0
Dim hn As Integer = 0
Dim rt As Integer = 0
Dim GtinC As Integer = 0
Dim Img1 As Integer = 0
Dim Img2 As Integer = 0
Dim Img3 As Integer = 0
Dim Img4 As Integer = 0
Dim Img5 As Integer = 0
Dim Img6 As Integer = 0
Dim Img7 As Integer = 0
Dim Img8 As Integer = 0
Dim Img9 As Integer = 0
Dim Img10 As Integer = 0
Dim ic As Integer = 0
Dim ic3 As Integer = 0
For j As Integer = 1 To r.Columns.Count()
If rArray(4, j).ToString().StartsWith("GTIN") Then
GtinC = j
ElseIf rArray(4, j).Equals("Record type") Then
rt = j
ElseIf rArray(4, j).ToString.Equals("Hierarchy number") Or rArray(4, j).ToString.Equals("Hierarchy level") Then
ic3 = ic3 + 1
If (ic3 = 1) Then
hn = j
ElseIf ic3 = 2 Then
hl = j
End If
ElseIf rArray(4, j).ToString.StartsWith("Image") And Not rArray(4, j).ToString.EndsWith("Description") And Not rArray(4, j).ToString.Contains("File") Then
ic = ic + 1
Select Case ic
Case 1
Img1 = j
Case 2
Img2 = j
Case 3
Img3 = j
Case 4
Img4 = j
Case 5
Img5 = j
Case 6
Img6 = j
Case 7
Img7 = j
Case 8
Img8 = j
Case 9
Img9 = j
Case 10
Img10 = j
Case Else
Exit Select
End Select
End If
Next
Dim additem As Boolean = False
For i As Integer = 5 To r.Rows.Count()
'check Hierarchy Number Level and record Type to filter for only top level GTIN's to download
If (CType(rArray(i, hn), Integer) = 1 And CType(rArray(i, hl), Integer) = 1 And CType(rArray(i, rt), Integer) = 1) Then
GoTo ContinueLoop
Else
Continue For
End If
ContinueLoop:
Dim itp As ImgThreadParams = New ImgThreadParams()
itp.gtin = CType(rArray(i, GtinC), String)
If (rArray(i, Img1) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img1), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img1), String))
additem = True
End If
End If
If (rArray(i, Img2) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img2), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img2), String))
End If
End If
If (rArray(i, Img3) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img3), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img3), String))
End If
End If
If (rArray(i, Img4) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img4), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img4), String))
End If
End If
If (rArray(i, Img5) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img5), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img5), String))
End If
End If
If (rArray(i, Img6) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img6), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img6), String))
End If
End If
If (rArray(i, Img7) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img7), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img7), String))
End If
End If
If (rArray(i, Img8) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img8), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img8), String))
End If
End If
If (rArray(i, Img9) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img9), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img9), String))
End If
End If
If (rArray(i, Img10) IsNot Nothing) Then
If (Uri.IsWellFormedUriString(CType(rArray(i, Img10), String), UriKind.Absolute)) Then
itp.lUrls.Add(CType(rArray(i, Img10), String))
End If
End If
If (additem = True) Then
ListParams.Add(itp)
additem = False
End If
Next
rArray = Nothing
w.Close()
Runtime.InteropServices.Marshal.ReleaseComObject(r)
Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
Runtime.InteropServices.Marshal.ReleaseComObject(w)
Runtime.InteropServices.Marshal.ReleaseComObject(excel)
Dim Folder As DirectoryInfo = System.IO.Directory.CreateDirectory(System.IO.Directory.GetCurrentDirectory() + "\Image")
If ListParams.Count > 10 Then
Dim tCount As Integer = CInt(Math.Round(ListParams.Count / 10, MidpointRounding.ToEven))
Dim L1 As New List(Of ImgThreadParams)
For i As Integer = 0 To tCount
L1.Add(ListParams(i))
Next
Dim L2 As New List(Of ImgThreadParams)
For i As Integer = tCount + 1 To tCount * 2
L2.Add(ListParams(i))
Next
Dim L3 As New List(Of ImgThreadParams)
For i As Integer = tCount * 2 + 1 To tCount * 3
L3.Add(ListParams(i))
Next
Dim L4 As New List(Of ImgThreadParams)
For i As Integer = tCount * 3 + 1 To tCount * 4
L4.Add(ListParams(i))
Next
Dim L5 As New List(Of ImgThreadParams)
For i As Integer = tCount * 4 + 1 To tCount * 5
L5.Add(ListParams(i))
Next
Dim L6 As New List(Of ImgThreadParams)
For i As Integer = tCount * 5 + 1 To tCount * 6
L6.Add(ListParams(i))
Next
Dim L7 As New List(Of ImgThreadParams)
For i As Integer = tCount * 6 + 1 To tCount * 7
L7.Add(ListParams(i))
Next
Dim L8 As New List(Of ImgThreadParams)
For i As Integer = tCount * 7 + 1 To tCount * 8
L8.Add(ListParams(i))
Next
Dim L9 As New List(Of ImgThreadParams)
For i As Integer = tCount * 8 + 1 To tCount * 9
L9.Add(ListParams(i))
Next
Dim L10 As New List(Of ImgThreadParams)
For i As Integer = tCount * 9 + 1 To ListParams.Count - 1
L10.Add(ListParams(i))
Next
Threading.ThreadPool.SetMaxThreads(1000, 1000)
Dim t1, t2, t3, t4, t5, t6, t7, t8, t9, t10 As Threading.ParameterizedThreadStart
t1 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t2 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t3 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t4 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t5 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t6 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t7 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t8 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t9 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t10 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t1.Invoke(L1)
t2.Invoke(L2)
t3.Invoke(L3)
t4.Invoke(L4)
t5.Invoke(L5)
t6.Invoke(L6)
t7.Invoke(L7)
t8.Invoke(L8)
t9.Invoke(L9)
t10.Invoke(L10)
Else
Dim t1 As Threading.ParameterizedThreadStart
t1 = New Threading.ParameterizedThreadStart(AddressOf ThreadedGetGtinImages)
t1.Invoke(ListParams)
End If
Next
Catch ex As Exception
Exit Sub
End Try
End Sub
Public Shared Sub ThreadedGetGtinImages(Params As List(Of ImgThreadParams))
Dim wc As HttpClient = New HttpClient()
Const ItemBatch As Integer = 100
Parallel.ForEach(Partitioner.Create(0, Params.Count, ItemBatch), Async Sub(range)
Dim imgCount As Integer = 1
For i As Integer = range.Item1 To range.Item2 - 1
Dim item = Params(i)
imgCount = 1
For Each surl In item.lUrls
Try
Dim response As HttpResponseMessage = Await wc.GetAsync(surl)
If (response.StatusCode = 200) Then
Dim sContentType As String = response.Content.Headers.ContentType.MediaType.ToString()
If (sContentType.Length > 0) Then
Dim sImage As String = sContentType.Split("/")(0).ToLower
Dim sImageType As String = sContentType.Split("/")(1).ToLower
If sImage.Equals("image") = True Or sImage.Equals("text") Then
Dim sFileExtension As String
Select Case sImageType
Case "jpeg"
sFileExtension = ".jpg"
Case "png"
sFileExtension = ".png"
Case "bmp"
sFileExtension = ".bmp"
Case "gif"
sFileExtension = ".gif"
Case "tiff"
sFileExtension = ".Tiff"
Case "html"
sFileExtension = ".html"
Case Else
Continue For
End Select
Dim sFolder = Directory.GetCurrentDirectory() + "\Image"
Using Fi As FileStream = New FileStream(sFolder + "\" + item.gtin + "_" + imgCount.ToString + sFileExtension, FileMode.OpenOrCreate)
Dim d = Await response.Content.ReadAsByteArrayAsync()
Await Fi.WriteAsync(d, 0, d.Length)
Await Fi.FlushAsync()
End Using
imgCount = imgCount + 1
End If
End If
End If
Catch
Continue For
End Try
Next
Next
End Sub)
End Sub
Public Shared Sub Main()
ServicePointManager.DefaultConnectionLimit = 10000
ProcessFolder(System.IO.Directory.GetCurrentDirectory())
Console.ReadLine()
End Sub
End Class
So I have a spreed sheet and its something like 550+ columns and I need to pull URLs from it. Previously I was using LinqToExcel when the sheet was less thenthan 255 columns a recent update has produced this monstrosity, so I have moved on to interop with excel to parse the data. So needless
Needless to say I have a list of a string I want to name all the images to, plus the index of that image for this item(uniqueness of file names). Currently I use 10 Threadsthreads to do the queries for the data. 10 HttpClients, 1 on each thread, and then I use a paralle.ForEach and Partitioner to dole out batches of 100 items to each parallel thread. This is not a Image pull from 1 website, many websites may be covered in the items.
Is This the Fastest way to do it? Old Code
So I have a spreed sheet and its something like 550+ columns and I need to pull URLs from it. Previously I was using LinqToExcel when the sheet was less then 255 columns a recent update has produced this monstrosity, so I have moved on to interop with excel to parse the data. So needless to say I have a list of a string I want to name all the images to, plus the index of that image for this item(uniqueness of file names). Currently I use 10 Threads to do the queries for the data. 10 HttpClients, 1 on each thread, and then I use a paralle.ForEach and Partitioner to dole out batches of 100 items to each parallel thread. This is not a Image pull from 1 website, many websites may be covered in the items. Is This the Fastest way to do it? Old Code
I have a spreed sheet and its something like 550+ columns and I need to pull URLs from it. Previously I was using LinqToExcel when the sheet was less than 255 columns a recent update has produced this monstrosity, so I have moved on to interop with excel to parse the data.
Needless to say I have a list of a string I want to name all the images to, plus the index of that image for this item(uniqueness of file names). Currently I use 10 threads to do the queries for the data. 10 HttpClients, 1 on each thread, and then I use a paralle.ForEach and Partitioner to dole out batches of 100 items to each parallel thread. This is not a Image pull from 1 website, many websites may be covered in the items.
Is This the Fastest way to do it? Old Code
lang-vb