| "А чё, так можно было, что ли?!?" | |
| © Уральские пельмени |
Недавно, правя баги в чужом проекте, я в методе генерации отчета в шаблон 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.Как видно по результатам теста, разница в скорости экспорта начинает хорошо ощущается только при экспорте 1000 строк, т.е. при экспорте нескольких десятков тысяч ячеек, и резко увеличивается при большом количестве ячеек. А значит небольшие отчеты можно создавать в MS Excel и по ячейкам, а вот для экспорта в MS Excel большого количества данных нужно обязательно использовать присваивание массива диапазону ячеек.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
Но есть и "обратная сторона медали" экспорта используя вариантный массив. Во-первых - этот способ требуется больше оперативной памяти. Хотя кого это волнует в современных реалиях? Во-вторых - существуют лимиты на количество элементов в массиве (как со стороны 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.
Комментариев нет:
Отправить комментарий