Данное сообщение будет полезно всем, кто занимается экспортом в Excel, особенно при выгрузке больших объемов данных.

Сам принцип довольно прост - формируем двухмерный вариантный массив, указываем область в Excel, куда необходимо его разместить и выгружаем. Самой проблемной остается задача заполнения массива. Для выгрузки из Delphi есть множество примеров/ Привожу один из них:

Но как сделать то же самое, только на языке JavaScript в конфигурации Terrasoft CRM и какая при этом будет скорость?

Привожу пример кода:

function Main() {
      var Excel = new ActiveXObject('Excel.Application');
      var Workbook = Excel.Workbooks.Add();
      var Sheet = Workbook.Sheets.Add();
      var StartRowNumber = 1;
      var StartColNumber = 1;
      var RowsCount = 10000;
      var ColumnsCount = 200;
      var Range = GetRangeObject(RowsCount, ColumnsCount);
      for (var X = 1; X = RowsCount; X++) {
            for (var Y = 1; Y = ColumnsCount; Y++) {
                  Range.Value(X, Y) = X * Y;
            }
      }
      var TopLeftRangeValue = GetRangeValue(StartRowNumber, StartColNumber);
      var BottomRightRangeValue = GetRangeValue(StartRowNumber + RowsCount - 1,
            StartColNumber + ColumnsCount - 1);
      Log.Write(1, 'Начало вставки записей в диапазон "' + TopLeftRangeValue +
            ':' + BottomRightRangeValue + '" = ' + RowsCount * ColumnsCount);
      Sheet.Range(TopLeftRangeValue, BottomRightRangeValue) =
            Range.ValuesAsArray;
      Log.Write(1, 'Конец вставки записей');
      Excel.Visible = true;
}

Для запуска в скрипт необходимо включить скрипт scr_UserReportCommon в котором находятся функции GetRangeValue() и GetRangeObject().
Функцию GetRangeValue нужно заменить на новую:
function GetRangeValue(RowNumber, ColumnNumber) {
      if ((RowNumber 1) || (ColumnNumber 1)) {
          return false;
      }
      var MaxCharNumber = 26;
      if (ColumnNumber > MaxCharNumber) {
            var FloorValue = Math.floor((ColumnNumber - 1)/MaxCharNumber);
            var FirstChar = String.fromCharCode(64 + FloorValue);
            var ColumnNumber = ColumnNumber - FloorValue * MaxCharNumber;
      } else {
            var FirstChar = '';
      }
      var RangeColumn = FirstChar + String.fromCharCode(64 + ColumnNumber);
      return RangeColumn + RowNumber;
}

Принцип действия следующий:
1) Создаем массив необходимых размеров:
      var Range = GetRangeObject(RowsCount, ColumnsCount);

2) Заполняем массив данными:
      for (var X = 1; X = RowsCount; X++) {
            for (var Y = 1; Y = ColumnsCount; Y++) {
                  Range.Value(X, Y) = X * Y;
            }
      }

3) Определяем адрес левой верхней и правой нижней ячеек по номерам строки и колонки (например, получаем следующие значения "A1", "GR10000"):
      var TopLeftRangeValue = GetRangeValue(StartRowNumber, StartColNumber);
      var BottomRightRangeValue = GetRangeValue(StartRowNumber + RowsCount - 1,
            StartColNumber + ColumnsCount - 1);

4) И самое главное - запускаем вставку данных из массива:
      Sheet.Range(TopLeftRangeValue, BottomRightRangeValue) =
            Range.ValuesAsArray;

При запуске выводит в лог следующее сообщение:
[09.03.04 09.50.32.342] (W) Начало вставки записей в диапазон "A1:GR10000" = 2000000
[09.03.04 09.50.36.628] (W) Конец вставки записей

При запуске тестового примера оказалось, что данный способ заполняет 2 000 000 ячеек не более чем за 5 секунд.

При выгрузке старайтесь, чтобы объем размер области совпадал с размером массива.
И не забывайте, что в Excel существуют ограничения на количество строк и колонок в листе - строк не более 65536, а колонок - не более 256.

Нравится

Поделиться

5 комментариев

Не совсем так. В последней версии Excel колонок и строк может быть больше. Отсюда важный момент - при работе с очень большими файлами вариантный массив может не создасться, если создавать его по всей используемой области. Пишу просто потому что сам один раз на такое нарвался :)

Думаю, что при работе с большими объемами данных могут быть проблемы, которые, связаны с ограниченными ресурсами машины (например, памяти). В этом случае можно разделять массив данных на несколько более мелких блоков, каждый из которых необходимо будет вставлять в Excel отдельно.

Одним из быстрых способов также будет простой экспорт в csv формат

Интересно, а как можно организовать если на этапе создание объекта Range не известно количество строк?

И все же одним из быстрых способов будет простой экспорт в csv формат. Или же можно формировать результат непосредственно в формате Open XML для последних версий MS Office. Хотя с Open XML конечно сложнее.

Показать все комментарии