За півтора року мені пропонували купу варіантів того як прискорити читання даних з листа Excel - від використання MSXML і інших готових бібліотек до самопальних процедур і функцій. Що ж, будь-яке завдання можна вирішити кількома способами. Розглянемо кілька варіантів і визначимося який з варіантів виявиться найбільш швидким. Ну, а який варіант виявиться зручнішим - це вже кожен вирішить для себе сам.
Читання даних з Excel
Спочатку розглянемо варіант читання даних використанням якого грішать ті, хто тільки починає своє знайомство з Excel в Delphi - читання даних з кожного осередку окремо. Тестова процедура з таким варіантом читання може виглядати наступним чином:
procedure TForm16.SlowVariant;
var Rows, Cols, i, j: integer;
WorkSheet: OLEVariant;
d: TDateTime;
begin
// відкриваємо книгу
ExcelApp.Workbooks.Open (edFile.Text);
// отримуємо активний лист
WorkSheet: = ExcelApp.ActiveWorkbook.ActiveSheet;
// визначаємо кількість рядків і стовпців таблиці
Rows: = WorkSheet.UsedRange.Rows.Count;
Cols: = WorkSheet.UsedRange.Columns.Count;
StringGrid1.RowCount: = Rows;
StringGrid1.ColCount: = Cols;
// засікаємо час початку читання
d: = Now;
// виводимо дані в таблицю
for I: = 0 to Rows-1 do
for j: = 0 to Cols-1 do
StringGrid1.Cells [J, I]: = WorkSheet.UsedRange.Cells [I + 1, J + 1] .Value;
Label2.Caption: = 'Час читання всього листа:' + FormatDateTime ( 'hh: mm: ss: zzz',
Now () - d);
end;
Лічильник буде в підсумку містити час читання і виведення в StringGrid даних. Можна було б зробити лічильник виключно на читання даних з листа, але я вирішив не перевантажувати исходник зайвими змінними. Якщо буде бажання - можете переписати трохи исходник і отримати "чисте" час читання.
Для тесту цього варіанту був створений лист Excel, що містить 143 рядки і 142 стовпчика з даними, тобто 20306 осередків з даними. На малюнку нижче представлено значення лічильника після читання даних:
12 секунд на читання. а якщо буде 1000 рядків і 1000 стовпців? Так можна і не дочекатися закінчення операції.
Якщо уважно подивитися на процедуру, представлену вище, то можна бачити, що в циклі ми кожен раз при кожній ітерації спочатку отримуємо діапазон, зайнятий даними, потім в цьому діапазоні отримуємо певну комірку і тільки потім зчитуємо значення в осередку. Насправді стільки зайвих операцій для читання даних з листа не потрібно. Тим більше, коли дані розташовуються безперервним масивом. Більш вигідним в цьому випадку варіантом читання буде читання даних відразу з усього діапазону в масив.
На ділі реалізація цього варіанти роботи виявиться навіть простіше, ніж представленого вище. Дивіться самі. Ось варіант читання даних цілим діапазоном:
procedure TForm16.RangeRead;
var Rows, Cols, i, j: integer;
WorkSheet: OLEVariant;
FData: OLEVariant;
d: TDateTime;
begin
// відкриваємо книгу
ExcelApp.Workbooks.Open (edFile.Text);
// отримуємо активний лист
WorkSheet: = ExcelApp.ActiveWorkbook.ActiveSheet;
// визначаємо кількість рядків і стовпців таблиці
Rows: = WorkSheet.UsedRange.Rows.Count;
Cols: = WorkSheet.UsedRange.Columns.Count;
// зчитуємо дані всього діапазону
FData: = WorkSheet.UsedRange.Value;
// засікаємо час початку читання
d: = Now;
// виводимо дані в таблицю
for I: = 0 to Rows-1 do
for j: = 0 to Cols-1 do
StringGrid1.Cells [J, I]: = FData [I + 1, J + 1];
Label2.Caption: = 'Час читання всього листа:' + FormatDateTime ( 'hh: mm: ss: zzz',
Now () - d);
end;
Дивимося на час виконання операції:
Як бачите, приріст швидкості виявився колосальним, враховуючи навіть те, що в лічильник потрапило час поновлення StringGrid'а.
Тут було б доречно показати і зворотний метод роботи з Excel, тобто запис даних на лист Excel з використанням варіантного масиву.
Запис даних в Excel
У разі, якщо нам необхідно записати великий обсяг даних на лист Excel нам необхідно провести зворотну операцію, тобто спочатку створити варіантний масив, потім записати в цей масив дані після чого записати весь масив однією операцією в Excel. Для прикладу я написав процедуру, яка зчитує великий обсяг даних з StringGrid і записує ці дані на другий лист відкритої книги Excel:
procedure TForm16.WriteData;
var i, j: integer;
FData: Variant;
Sheet, Range: Variant;
begin
// створюємо варіантний масив
FData: = VarArrayCreate ([1, StringGrid1.RowCount, 1, StringGrid1.ColCount], varVariant);
// заповнюємо масив даними з StringGrid
for i: = 1 to VarArrayHighBound (FData, 1) do
for j: = 1 to VarArrayHighBound (FData, 2) do
FData [i, j]: = StringGrid1.Cells [J-1, I-1];
// відкриваємо книгу
ExcelApp.Workbooks.Open (edFile.Text);
// активуємо
Sheet: = ExcelApp.ActiveWorkBook.Sheets [2];
Sheet.Activate;
// виділяємо діапазон для вставки даних
Range: = Sheet.Range [Sheet.Cells [1,1], Sheet.Cells [VarArrayHighBound (FData, 1), VarArrayHighBound (FData, 2)]];
// вставляємо дані
Range.Value: = FData;
// показуємо вікно Excel
ExcelApp.Visible: = True;
end;
Тут ми спочатку створюємо двовимірний варіантний масив, використовуючи метод VarArrayCreate, після чого заповнюємо масив даних і передаємо цей масив в Excel. Зверніть увагу, що під час запису в Excel не використовуються ніякі цикли - запис відбувається в 2 простих дії:
виділяємо діапазон, використовуючи в якості меж діапазону першу і останню осередки
присвоюємо діапазону значення з масиву.
Для повноти картини нижче на малюнку представлено значення лічильника, який відрахував час від моменту створення масиву до активації програми Excel включно:
Природно, що з ростом обсягу даних буде рости і час виконання операції. Так, наприклад, лист, який містить 1000 рядків і 256 стовпців з даними заповнювався близько 7 секунд. Якщо для Вас такий час неприйнятно, то представлена вище процедура може бути трохи прискорена використанням пари методів VarArrayLock () і VarArrayUnLock (), але при цьому слід враховувати, що матриця FData буде транспонована.
Що ще варто сказати з приводу читання / запису даних в Excel. Напевно те, що запропоновані вище методи роботи в обов'язковому порядку вимагають наявності встановленого Excel на тому комп'ютері де запускається Ваша програма. У зв'язку з цією обставиною може знадобитися більш універсальний спосіб роботи з Excel. Тут, знову ж таки, може бути кілька варіантів роботи, але я покажу, а точніше вкажу лише на один з них - з використанням бібліотека XLSReadWrite.
Спрощений приклад для Delphi 7
var
IntlXls: TXLSReadWriteII2;
I, J: Integer;
begin
// створюємо об'єкт
IntlXls: = TXLSReadWriteII2.Create (nil);
// назва книги
IntlXls.Sheets [0] .Name: = "Назва мого звіту";
// додаємо необхідну кількість рядків і колонок
IntlXls.Sheets [0] .Rows.AddIfNone (0, 10000);
IntlXls.Sheets [0] .Columns.AddIfNone (0, 100);
// додаємо і заносимо ширини осередків (значення в пікселях)
for I: = 0 to 99 do
IntlXls.Sheets [0] .Columns [I] .PixelWidth: = 150;
// заносимо висоти рядків (значення тут не в пікселях, тому потрібно коригувати)
for I: = 0 to 9999 do
IntlXls.Sheets [0] .Rows [I] .Height: = 20 * 14;
// налаштовуємо
for J: = 0 to 9999 do
for I: = 0 to 99 do
begin
// заносимо числове значення
// якщо потрібно наприклад занести рядок, то використовувати AsString
IntlXls.Sheets [0] .AsFloat [I, J]: = J + I / 100;
// вирівнювання по горизонталі (доступно chaLeft, chaCenter, chaRight)
IntlXls.Sheets [0] .Cell [I, J] .HorizAlignment: = chaLeft;
// вирівнювання по вертикалі (доступно cvaTop, cvaCenter, cvaBottom)
IntlXls.Sheets [0] .Cell [I, J] .VertAlignment: = cvaTop;
// шрифт
IntlXls.Sheets [0] .Cell [I, J] .FontName: = "Arial";
IntlXls.Sheets [0] .Cell [I, J] .FontSize: = 12;
IntlXls.Sheets [0] .Cell [I, J] .FontStyle: = [];
IntlXls.Sheets [0] .Cell [I, J] .FontColor: = TColorToClosestXColor (clBlue);
IntlXls.Sheets [0] .Cell [I, J] .Rotation: = 0;
// жирне накреслення
with IntlXls.Sheets [0] .Cell [I, J] do
FontStyle: = FontStyle + [xfsBold];
// похиле накреслення
with IntlXls.Sheets [0] .Cell [I, J] do
FontStyle: = FontStyle + [xfsItalic];
// колір фону
IntlXls.Sheets [0] .Cell [I, J] .FillPatternForeColor: =
TColorToClosestXColor (clYellow);
// бордюр зліва (аналогічно і інші бордюри)
IntlXls.Sheets [0] .Cell [I, J] .BorderLeftColor: =
TColorToClosestXColor (clBlack);
IntlXls.Sheets [0] .Cell [I, J] .BorderLeftStyle: = cbsThin;
// об'єднання осередків (тут об'єднуються два осередки по горизонталі)
if I = 49 then
IntlXls.Sheets [0] .MergedCells.Add (I, J, I + 1, J);
end;
IntlXls.SaveToFile ( "c: \ demo.xls");
IntlXls.Free;
end;
Повний приклад роботи з бібліотекою:
// вирівнювання по горизонталі
case HorizAlign of
haLeft:
// вирівнювання зліва
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .HorizAlignment
: = ChaLeft;
haCenter:
// вирівнювання по центру
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .HorizAlignment: =
chaCenter;
haRight:
// вирівнювання праворуч
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .HorizAlignment
: = ChaRight;
end; // case
// вирівнювання по вертикалі
case VertAlign of
vaTop:
// вирівнювання зверху
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .VertAlignment
: = CvaTop;
vaCenter:
// вирівнювання в центрі
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .VertAlignment: =
cvaCenter;
vaBottom:
// вирівнювання знизу
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .VertAlignment: =
cvaBottom;
end; // case
// шрифт
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .FontName: = Font.Name;
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .FontSize: = Font.Size;
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .FontCharset: =
Font.Charset;
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .FontStyle: = [];
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .FontColor: =
TColorToClosestXColor (Font.Color);
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .Rotation: = Font.Angle;
// їсти жирне накреслення?
if Font.IsBold then
// є
with IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] do
FontStyle: = FontStyle + [xfsBold];
// є похиле накреслення?
if Font.IsItalic then
// є
with IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] do
FontStyle: = FontStyle + [xfsItalic];
// колір фону
if Color <> clWindow then
// колір заданий
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .FillPatternForeColor: =
TColorToClosestXColor (Color);
end // if
else
// просто активізуємо осередок (інакше нижче неможливо додати бордюри)
IntlXls.Sheets [0] .AsString [IntlCol, IntlRow]: = ";
// бордюр зліва є?
with Borders.Left do
if LineHeight> 0 then
// налаштовуємо
begin
// колір
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderLeftColor: =
TColorToClosestXColor (Color);
// товщина
if LineHeight = 1 then
// тонка
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderLeftStyle
: = CbsThin
else if LineHeight in [1, 2] then
// середня товщина
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderLeftStyle: =
cbsMedium
else
// товста
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderLeftStyle
: = CbsHair;
end; // if, with
// бордюр зверху є?
with Borders.Top do
if LineHeight> 0 then
// налаштовуємо
begin
// колір
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderTopColor: =
TColorToClosestXColor (Color);
// товщина
if LineHeight = 1 then
// тонка
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderTopStyle
: = CbsThin
else if LineHeight in [1, 2] then
// середня товщина
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderTopStyle: =
cbsMedium
else
// товста
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderTopStyle
: = CbsHair;
end; // if, with
// бордюр праворуч є?
with Borders.Right do
if LineHeight> 0 then
// налаштовуємо
begin
// колір
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderRightColor: =
TColorToClosestXColor (Color);
// товщина
if LineHeight = 1 then
// тонка
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderRightStyle
: = CbsThin
else if LineHeight in [1, 2] then
// середня товщина
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderRightStyle: =
cbsMedium
else
// товста
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderRightStyle
: = CbsHair;
end; // if, with
// бордюр знизу є?
with Borders.Bottom do
if LineHeight> 0 then
// налаштовуємо
begin
// колір
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderBottomColor: =
TColorToClosestXColor (Color);
// товщина
if LineHeight = 1 then
// тонка
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderBottomStyle
: = CbsThin
else if LineHeight in [1, 2] then
// середня товщина
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderBottomStyle: =
cbsMedium
else
// товста
IntlXls.Sheets [0] .Cell [IntlCol, IntlRow] .BorderBottomStyle
: = CbsHair;
end; // if, with
// об'єднання потрібно?
if ((Range.Width> 1) or (Range.Height> 1)) and
((IntlMainCol = IntlCol) and (IntlMainRow = IntlRow)) then
// об'єднуємо
IntlXls.Sheets [0] .MergedCells.Add (IntlCol, IntlRow,
IntlCol + Range.Width - 1, IntlRow + Range.Height - 1);
// користувач натиснув кнопку переривання експорту?
if btnCancel.Tag = 2 then
// так, виходимо
Break;
end; // with
end; // for
// оновлюємо статус
prgrbrStatus.Position: = prgrbrStatus.Position + 1;
Application.ProcessMessages;
// користувач натиснув кнопку переривання експорту?
if btnCancel.Tag = 2 then
// так, виходимо
Break;
end; // for
// користувач натиснув кнопку переривання експорту?
if btnCancel.Tag <> 2 then
// нет
begin
// на лівий верхній кут
IntlXls.Sheet [0] .TopRow: = 0;
IntlXls.Sheet [0] .LeftCol: = 0;
IntlXls.Sheet [0] .Selection.ActiveRow: = 0;
IntlXls.Sheet [0] .Selection.ActiveCol: = 0;
// статус
prgrbrStatus.Position: = prgrbrStatus.Max;
Application.ProcessMessages;
// записуємо в файл
IntlXls.FileName: = AFileName;
IntlXls.Write;
// всі успішно
Result: = UNIRPT_OK;
end // if
else
// так
Result: = UNIRPT_GENERATE_ABORT;
finally
// звільняємо пам'ять
IntlXls.Free;
end; // try..finally
end; // function ExportToExcelXls