Заполнение листа MS Excel одной командой

	"А чё, так можно было, что ли?!?"
	© Уральские пельмени

    Недавно, правя баги в чужом проекте, я в методе генерации отчета в шаблон MS Excel обнаружил то, о чем сам никогда не задумывался... Среди сотни строк присвоения значений ячейкам, я заметил, что одна таблица из сотни ячеек копируется на лист MS Excel одной строкой...
    Алгоритм очень прост: создаем динамический двумерный вариантный массив, заполняем его значениями и присваиваем диапазону ячеек:

var
  ea: TExcelApplication;
  v: Variant;
  iRow, iCol: Integer;
begin
  ...
  ea := TExcelApplication.Create(nil);
  try
    ...
    // присваиваем значения ячейкам
    ea.Cells.Item[1, 2] := 'Integer';
    ea.Cells.Item[1, 3].Value := 'Float';
    ea.Range['D1', 'D1'].Value := 'Text';
    ...
    // создаем двумерный массив
    // элементы которого могут содержать значения разных типов
    v := VarArrayCreate([1, 5, 1, 3], varVariant); // 5 строк, 3 столбца
    try
      // заполняем двумерный массив значениями 
      for iRow := 1 to 5 do
        for iCol := 1 to 3 do
          Case iCol of
            1: v[iRow, iCol] := iRow; // целое число
            2: v[iRow, iCol] := iCol / iRow; //вещественное число
            3: v[iRow, iCol] := 'Row = ' + IntToStr(iRow); // строка
          End;
      // экспортируем двумерный массив в Excel
      ea.Range['B2', 'D6'].Value := v;
    finally
      VarClear(v);
    end;
    ...
  finally
    ea.Free
  end;

На выходе имеем:

Теоретически это должно работать быстрее. А практически? Сделаем небольшой тест:

program ExcelArray;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, System.Variants, System.Win.ComObj,
  Winapi.Windows, Winapi.ActiveX;

function InitEA: OleVariant;
begin
  Result := CreateOleObject('Excel.Application');
  Result.DisplayAlerts := False; // отключаем сообщения
  Result.EnableEvents := False; // отключаем обработку событий
  Result.ScreenUpdating := False; // отключаем перерисовку объектов на экране
  Result.DisplayStatusBar := False; // отключаем вывод в строку статуса
  Result.Workbooks.Add('');
  Result.Calculation := $FFFFEFD9;//xlCalculationManual; // отключаем авторасчет
end;

procedure CloseEA(var ovEA: OleVariant);
begin
  ovEA.Quit;
  ovEA := Unassigned;
end;

const
  ciColCount = 26; //16384;

procedure Test(const iRowCount: Integer);
var
  ovEA: OleVariant;
  iRow,
  iCol: Integer;
  v: Variant;
  dStart,
  dTotal: TDateTime;

  procedure WriteTime(const sTitle: String);
  var
    dDelta: TDateTime;
  begin
    dDelta := Now - dStart;
    dTotal := dTotal + dDelta;
    WriteLn(sTitle +  ': ', FormatDateTime('nn:ss.zzz', dDelta));
  end;
begin
  WriteLn('RowCount: ', iRowCount, ' ColCount: ', ciColCount);
  try
    // Экспорт по ячейкам
    ovEA := InitEA;
    try
      dStart := Now;
      for iRow := 1 to iRowCount do
        for iCol := 1 to ciColCount do
          ovEA.Cells[iRow, iCol] := 'Row = ' + IntToStr(iRow) + 
                                    ' Col = ' + IntToStr(iCol);
      WriteTime('Экспорт по ячейкам');
    finally
      CloseEA(ovEA);
    end;

    // Экспорт листа с использованием массива
    WriteLn('Экспорт с использованием массива');
    dTotal := 0;
    dStart := Now;

    v := VarArrayCreate([1, iRowCount, 1, ciColCount], varVariant);
    try
      for iRow := 1 to iRowCount do
        for iCol := 1 to ciColCount do
          v[iRow, iCol] := 'Row = '+ IntToStr(iRow)+' Col = '+IntToStr(iCol);
      WriteTime('1. Создание массива');

      ovEA := InitEA;
      try
        dStart := Now;
        ovEA.Range['A1', Chr($40 + ciColCount) + IntToStr(iRowCount)] := v;
        WriteTime('2. Экспорт массива');
      finally
        CloseEA(ovEA);
      end;
    finally
      dStart := Now;
      VarClear(v);
      WriteTime('3. Удаление массива');
    end;
    WriteLn('Общее время: ', FormatDateTime('nn:ss.zzz', dTotal));

    WriteLn;
  except
    on E: Exception do
      Writeln(E.Message);
  end;
end;

begin
  Try
    CoInitialize(nil);
    Test(10);
    Test(100);
    Test(1000);
    Test(10000);
    Test(100000);
  Finally
    CoUninitialize;
  End;
end.

Результаты:

RowCount: 10 ColCount: 26
Экспорт по ячейкам: 00:00.060
Экспорт с использованием массива
1. Создание массива: 00:00.000
2. Экспорт массива: 00:00.000
3. Удаление массива: 00:00.000
Общее время: 00:00.000

RowCount: 100 ColCount: 26
Экспорт по ячейкам: 00:00.598
Экспорт с использованием массива
1. Создание массива: 00:00.000
2. Экспорт массива: 00:00.026
3. Удаление массива: 00:00.000
Общее время: 00:00.026

RowCount: 1000 ColCount: 26
Экспорт по ячейкам: 00:06.223
Экспорт с использованием массива
1. Создание массива: 00:00.000
2. Экспорт массива: 00:00.279
3. Удаление массива: 00:00.015
Общее время: 00:00.294

RowCount: 10000 ColCount: 26
Экспорт по ячейкам: 01:04.324
Экспорт с использованием массива
1. Создание массива: 00:00.063
2. Экспорт массива: 00:03.034
3. Удаление массива: 00:00.016
Общее время: 00:03.113

RowCount: 100000 ColCount: 26
Экспорт по ячейкам: 10:21.632
Экспорт с использованием массива
1. Создание массива: 00:00.583
2. Экспорт массива: 00:32.691
3. Удаление массива: 00:00.163
Общее время: 00:33.437

Как видно по результатам теста, разница в скорости экспорта начинает хорошо ощущается только при экспорте 1000 строк, т.е. при экспорте нескольких десятков тысяч ячеек, и резко увеличивается при большом количестве ячеек. А значит небольшие отчеты можно создавать в MS Excel и по ячейкам, а вот для экспорта в MS Excel большого количества данных нужно обязательно использовать присваивание массива диапазону ячеек.

    Но есть и "обратная сторона медали" экспорта используя вариантный массив. Во-первых - этот способ требуется больше оперативной памяти. Хотя кого это волнует в современных реалиях? Во-вторых - существуют лимиты на количество элементов в массиве (как со стороны VarArrayCreate, так и со стороны Excel/COM). Поэтому экспортировать все возможные для MS Excel 1048576 строк по 16384 столбцов используя вариантный массив не получится.

P.S. Не знание о возможности присвоить диапазону ячеек MS Excel массива - это позор на мои седины... Но у меня есть оправдание. Прошло много лет с моего последнего использования TExcelApplication/TOleServer и CreateOleObject('Excel.Application'), т.к. если я и работал с файлами MS Excel, то только "напрямую" используя XLSReadWriteII от Axolot Data и TMS FlexCel от TMS Software.