Pl SQL Developer has problems with export. It does export cell by cell which takes a lot of time on big number of rows or big number of fields. Why don't you do export through temprorary buffer. Using buffer in 10 rows makes export in 100 times faster. I mean buffer is an array of variants.
Problems with export to Excel
- Thread starter Delphist
- Start date
The current interface uses OLE, which I believe might be cell by cell by nature. In any case, version 6.0 will save the results directly to an Excel file, which is a lot faster.
Thank you for reply.
But well you are not quite right, here is sample how to do it using buffer.
var XLApp, XLSheet, wb, r : Variant;
i, RowNo : integer;
begin
XLApp := CreateOleObject('Excel.Application');
wb := XLApp.WorkBooks.Add;
XLApp.SheetsInNewWorkBook := 1;
XLSheet := wb.WorkSheets[1];
XLSheet.Name := 'sheet1';
r := VarArrayCreate([1, 9], varVariant);
table.First;
RowNo := 1;
while NOT table.Eof do begin
for i:=1 to 9
do r := table.Fields[i-1].AsVariant;
XLSheet.Range[Format('A%d:J%d', [RowNo, RowNo])] := r;
Inc(RowNo);
Table.Next;
end;
......................
But well you are not quite right, here is sample how to do it using buffer.
var XLApp, XLSheet, wb, r : Variant;
i, RowNo : integer;
begin
XLApp := CreateOleObject('Excel.Application');
wb := XLApp.WorkBooks.Add;
XLApp.SheetsInNewWorkBook := 1;
XLSheet := wb.WorkSheets[1];
XLSheet.Name := 'sheet1';
r := VarArrayCreate([1, 9], varVariant);
table.First;
RowNo := 1;
while NOT table.Eof do begin
for i:=1 to 9
do r := table.Fields[i-1].AsVariant;
XLSheet.Range[Format('A%d:J%d', [RowNo, RowNo])] := r;
Inc(RowNo);
Table.Next;
end;
......................
Thanks, we'll look into this as well!
Similar threads
- Discussion
- Discussion