从Excel VBA到Web服务的高效数据传输

Question

我有一个大工作表(~250K行，22列，~40MB纯数据(，必须将其内容传输到内部网API。格式无关紧要。问题是：当访问数据时，例如

Const ROWS = 250000
Const COLS = 22
Dim x As Long, y As Long
Dim myRange As Variant
Dim dummyString As String
Dim sb As New cStringBuilder
myRange = Range(Cells(1, 1), Cells(ROWS, COLS)).Value2
For x = 1 To ROWS
For y = 1 To COLS
dummyString = myRange(x, y) 'Runtime with only this line: 1.8s
sb.Append dummyString 'Runtime with this additional line 163s
Next
Next

我得到了一个很棒的 2D 数组，但我无法有效地收集数据以进行 HTTP 导出。 阵列和访问myRange[x, y]上的 X/Y 循环的运行时间为>1 分钟。我无法找到有助于获取 2D 数组的内爆/编码内容的数组方法。 我目前的解决方法是错过剪贴板(使用大字符串时内存泄漏的解决方法(，它工作速度很快，但在我眼中是一个肮脏的解决方法，并且有一个主要问题：我得到的值被格式化为".值"而不是"。Value2"，所以我必须在使用前再次转换服务器站点上的数据，例如将货币单元格取消格式化为浮点数。

处理数据数组的另一个想法是什么？

Answer 1

我的想法是你创建两个字符串数组A和B。A的大小可以是1 to ROWS，B可以是1 to COLUMNS的大小。循环遍历myRange数组中的每一行时，B填充每个元素，并使用该行中每一列的值。在该行的最后一列之后，在移动到下一行之前，联接数组B并分配给A中的行。对于这种大小的循环，只需将必要的东西放在循环本身内。最后你会加入A.将项目分配给B时，可能需要使用cstr()。

Matschek(OP(能够基于上述内容编写代码，但为了其他人的利益，代码本身可能是这样的：

Option Explicit
Private Sub concatenateArrayValues()
Const TOTAL_ROWS As Long = 250000
Const TOTAL_COLUMNS As Long = 22
Dim inputValues As Variant
inputValues = ThisWorkbook.Worksheets("Sheet1").Range("A1").Resize(TOTAL_ROWS, TOTAL_COLUMNS).Value2
' These are static string arrays, as OP's use case involved constants.
Dim outputArray(1 To TOTAL_ROWS) As String ' <- in other words, array A
Dim interimArray(1 To TOTAL_COLUMNS) As String ' <- in other words, array B
Dim rowIndex As Long
Dim columnIndex As Long
' We use constants below when specifying the loop's limits instead of Lbound() and Ubound()
' as OP's use case involved constants.
' If we were using dynamic arrays, we could call Ubound(inputValues,2) once outside of the loop
' And assign the result to a Long type variable
' To avoid calling Ubound() 250k times within the loop itself.
For rowIndex = 1 To TOTAL_ROWS
For columnIndex = 1 To TOTAL_COLUMNS
interimArray(columnIndex) = inputValues(rowIndex, columnIndex)
Next columnIndex
outputArray(rowIndex) = VBA.Strings.Join(interimArray, ",")
Next rowIndex
Dim concatenatedOutput As String
concatenatedOutput = VBA.Strings.Join(outputArray, vbNewLine)
Debug.Print concatenatedOutput
' My current machine isn't particularly great
' but the code above ran and concatenated values in range A1:V250000
' (with each cell containing a random 3-character string) in under 4 seconds.
End Sub