Эффективная передача данных из Excel VBA в веб-сервис
У меня есть большой рабочий лист (~ 250 тыс. Строк, 22 столбца, ~ 40 МБ простых данных), который должен передать его содержимое в 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.
Цикл X / Y по массиву и доступ к myRange[x, y] имеет время выполнения> 1 мин. Мне не удалось найти метод массива, который помогает получить сжатое / закодированное содержимое 2D-массива.
Мой текущий обходной путь - это неправильное использование буфера обмена (Обход утечки памяти при использовании большой строки), который работает быстро, но в моих глазах это грязный обходной путь И имеет одну серьезную проблему: значения, которые я получаю, отформатированы как «.Value», а не «.Value2», поэтому я должен снова преобразовать данные на сайте сервера перед использованием, например преобразовать денежные ячейки в числа с плавающей запятой.
Какая может быть еще идея разобраться с массивом данных?
Пожалуйста, покажите ваш существующий код цикла. Существуют реализации VBA класса «построитель строк», который, вероятно, именно то, что вам здесь нужно. Также "250M" строк - вы имеете в виду 250k?
Скотт: Да, это вариант, set был скопирован по ошибке. Тим: 250К, а не 250М, спасибо. Я обновил вопрос полной тестовой функцией
Возможно, вы захотите заменить Dim sb As New cStringBuilder на Dim sb As cStringBuilder: Set sb = new cstringbuilder. Таким образом, экземпляр объекта не нужно проверять каждый раз, когда вы на него обращаетесь.
Я предполагаю, что ваш класс / объект построителя строк использует некоторую форму join для уменьшения накладных расходов на конкатенацию неизменяемых строк - и, следовательно, достаточно эффективен.
@chillin: Класс Stringbuilder - одна из найденных мною реализаций VBA, в которой используется Windows CopyMemory API (vbaccelerator.com/home/VB/Code/Techniques/StringBuilder/…). Я изменил строку Dim, но это не имеет значения.
Возможен прямой вызов API CopyMemor для получения всей структуры массива в строковый / байтовый буфер, но я новичок в этой теме (указатели на массивы, размер блока памяти и т. д., Трудно начать ...).
Извините, если я освещаю старую основу / материал, который вы уже пробовали, но сколько времени это займет с простыми cstr и join?
Можете ли вы предоставить более подробную информацию о своих мыслях? join не работает с 2D-массивами, afaik. Или вы имеете в виду сбор всех значений в одномерный массив, а затем присоединение к строке?
Да, я думаю, что вы создаете два строковых массива A и B. A может иметь размер 1 to ROWS, B может иметь размер 1 to COLUMNS. По мере того, как вы перебираете каждую строку в массиве myRange, заполняйте каждый элемент в B значением каждого столбца в этой строке. После последнего столбца для этой строки и перед переходом к следующей строке присоединитесь к массиву B и назначьте строку в A. С петлей такого размера помещайте только необходимые вещи внутрь самой петли. В конце вы присоединитесь к A. Возможно, вам потребуется использовать cstr при назначении элементов для B. написал бы ответ, но мой комментарий экспериментальный.
@chillin, опубликуйте это как ответ, чтобы я мог дать вам очки. Я пробовал, и он работает отлично, моя обычная среда выполнения, собирающая данные, составляет ~ 3,5 с. Кажется, "двухуровневая конструкция соединения" намного эффективнее других способов.
@Matschek Конечно, я опубликовал это как ответ, а также код на случай, если это принесет пользу кому-то еще.
Ответы 1
Я думаю, что вы создаете два строковых массива A и B. A может иметь размер 1 to ROWS, B может иметь размер 1 to COLUMNS. По мере того, как вы перебираете каждую строку в массиве myRange, заполняйте каждый элемент в B значением каждого столбца в этой строке. После последнего столбца для этой строки и перед переходом к следующей строке присоединитесь к массиву B и назначьте строку в A. С петлей такого размера помещайте только необходимые вещи внутрь самой петли. В конце вы присоединитесь к A. Возможно, вам потребуется использовать cstr() при назначении элементов для B.
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
Какой тип объекта
myRange? Я подозреваю, что вы объявили это как объектRange, а не как реальный вариант. Если вы объявите как вариант, цикл будет НАМНОГО быстрее. См. Пример здесь