fpSpreadsheet export speed lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- Hansvb · 2014-12-12 21:42 With the next code i export data to a xlsx file. Exporting 8000 records (in 15 columns) takes more then 5 minutes before the xls file ready. Is seems a bit slow to me. What's wrong? Code: [Select]procedure TExport_To_File.Export_To_xlsx(Adbgrid: TDBGrid); var   workbook  : TsWorkbook;   Worksheet : TsWorksheet;   i, j        : Integer; begin   //http://wiki.lazarus.freepascal.org/FPSpreadsheet#Grid_and_Charting _Controls_with_FPSpreadsheet   //http://sourceforge.net/p/lazarus- ccr/svn/2782/tree/components/fpspreadsheet/fpspreadsheet.pas   screen.Cursor:=crHourglass;   Application.ProcessMessages;   if Adbgrid.DataSource.DataSet.IsEmpty = True then     begin       MessageDlg('De DbGrid is leeg. Export is niet mogelijk.', mtWarning,[mbOk],0);     end   else     begin  //Export start         try           workbook := TsWorkbook.Create;           try             worksheet := workbook.AddWorksheet('Sheet1');             worksheet.WriteFontStyle(0, 1, [fssBold]);             workbook.Options := [boVirtualMode, boBufStream];             workbook.VirtualColCount := Adbgrid.DataSource.DataSet.FieldCount;             workbook.VirtualRowCount := Adbgrid.DataSource.DataSet.RecordCount +1; //plus 1 = header             workbook.OnWriteCellData := @WriteCellDataHandler;             workbook.WriteToFile(SaveDialog.FileName + '.xlsx' , sfOOXML, true);             //workbook.WriteToFile('test_virtual_02.xls', sfExcel2, true);           finally             workbook.Free;           end;         finally           screen.Cursor:=crDefault;         end;     end; end;  procedure TExport_To_File.WriteCellDataHandler(Sender: TObject;   ARow, ACol: Cardinal; var AValue: variant; var AStyleCell: PCell); var   s: String; begin  if Frm_Main.PageControl1.ActivePageIndex = 6 then      begin       if ARow = 0 then         begin           AValue :=  Frm_Main.DBGrid_TEST.DataSource.DataSet.Fields[ACol].FieldName;           Frm_Main.DBGrid_TEST.DataSource.DataSet.First;         end       else         begin           s  := UTF8Encode(Frm_Main.DBGrid_TEST.DataSource.DataSet.Fie lds[ACol].AsWideString);           if s = '' then             AValue := NULL           else             AValue := s;           if ACol = Frm_Main.DBGrid_TEST.DataSource.DataSet.FieldCount-1 then             begin               Frm_Main.DBGrid_TEST.DataSource.DataSet.Next;             end;         end;       Frm_Main.DBGrid_TEST.DataSource.DataSet.First;     end;  end; ---------------------------------------------------------------------- wp · 2014-12-12 21:56 Quote//http://sourceforge.net/p/lazarus- ccr/svn/2782/tree/components/fpspreadsheet/fpspreadsheet.pasDoes this mean that you use fpspreadsheet from revision 2782? This is fairly old. Use trunk, or wait a few days until I'll upload a new release. Something can't be wrong with your posting because r2782 did not have VirtualMode yet which you are using in your code... ---------------------------------------------------------------------- Hansvb · 2014-12-12 22:00 No, the link is just a reminder for me. I just got  r3837 ---------------------------------------------------------------------- wp · 2014-12-12 22:55 OK, this is current. Then fpspreadsheet is definitely fast. I think the problem is that the dataset is linked to a dbgrid. This means while you iterate through all records during exporting the dbgrid scrolls from the first to the last record. This results in permanent repainting which is very expensive. As a solution you should call DisableControls for the exported dataset before, and EnableControls after the export which prevents these updates: Code: [Select]... Frm_Main.DBGrid_TEST.DataSource.DataSet.DisableControls; try   workbook.WriteToFile(SaveDialog.FileName + '.xlsx' , sfOOXML, true); finally   Frm_Main.DBGrid_TEST.DataSource.DataSet.EnableControls; end; ... ---------------------------------------------------------------------- Hansvb · 2014-12-13 19:17 That helps. The time is now 63 seconds instead of 149 seconds. I didn't noticed it yesterday, but there must be someting verry wrong in the code in my first post. When the dbgrid shows 8000 records, i see in Excel 8000 times the first record. ---------------------------------------------------------------------- wp · 2014-12-13 20:16 Quotei see in Excel 8000 times the first recordThe last command in the WriteCellDataHandler is "(dataset).First" which undos the previous "(dataset).Next". Remove it. QuoteThat helps. The time is now 63 seconds instead of 149 seconds.This is still way to slow. The fpsspeedtest (in the "fpspreadsheet/examples" folder) writes 10000 records with 100 fields to xlsx in about 7 seconds on my machine. These are 1 million cells. You have 8000x15 = 120000 cells. The speedtest would take less than 1 second! The records of the speed test are in a local database, though. Which database are you using? Is the database connection configured correctly? I do remember that my first experiences with MS Access connected to Delphi via ADO were painfully slow because the incorrect CursorType was used. Are there any event handlers for OnBeforeScroll/OnAfterScroll? Set them temporarily to nil during the export. All this slows down the export. My recommendation would be to use a separate dataset which is not linked to any datasource and data controls. QuoteThe time is now 63 seconds instead of 149 secondsIn the first posting you wrote that it took 5 minutes ?! ---------------------------------------------------------------------- Hansvb · 2014-12-13 21:38 The 5 minutes where an indication. It took a long time. I allready removed a connection to a progresbar and some old code which made is faster. Now i removed the "first" and i see the right result AND the export is a lot faster (about 3 seconds for 8000 records). Really fast! (I am logged  in in on a Oracle database via Citrix). ---------------------------------------------------------------------- wp · 2014-12-13 22:58 Great! 3 seconds sound reasonable because a remote database will certainly be slower than a local one. ---------------------------------------------------------------------- Hansvb · 2014-12-15 20:13 is it possible to implement a progressbar? i tried this, but thas in not working right Code: [Select]procedure TExport_To_File.Export_To_xlsx(Adbgrid: TDBGrid); var   workbook  : TsWorkbook;   Worksheet : TsWorksheet;   i, j        : Integer; begin   screen.Cursor:=crHourglass;   if Adbgrid.DataSource.DataSet.IsEmpty = True then     begin       MessageDlg('De DbGrid is leeg. Export is niet mogelijk.', mtWarning,[mbOk],0);     end   else     begin  //Export starten         try           workbook := TsWorkbook.Create;           try             worksheet := workbook.AddWorksheet('Sheet1');             worksheet.WriteFontStyle(0, 1, [fssBold]);             workbook.Options := [boVirtualMode, boBufStream];             workbook.VirtualColCount := Adbgrid.DataSource.DataSet.FieldCount;             workbook.VirtualRowCount := Adbgrid.DataSource.DataSet.RecordCount +1;             Frm_Main.ProgressBar1.Min:=0;             Frm_Main.ProgressBar1.Max:=Frm_Main.DBGrid_TEST.DataSource .DataSet.RecordCount;             Frm_Main.ProgressBar1.Position:=0;             workbook.OnWriteCellData := @WriteCellDataHandler;             try               Adbgrid.DataSource.DataSet.DisableControls;               workbook.WriteToFile(SaveDialog.FileName + '.xlsx' , sfOOXML, true);             finally               Adbgrid.DataSource.DataSet.First;  //back to the first record in the dataset               Adbgrid.DataSource.DataSet.EnableControls;             end;             //workbook.WriteToFile('test_virtual_02.xls', sfExcel2, true);           finally             workbook.Free;           end;         finally           screen.Cursor:=crDefault;         end;     end; end; procedure TExport_To_File.WriteCellDataHandler(Sender: TObject;   ARow, ACol: Cardinal; var AValue: variant; var AStyleCell: PCell); var   s: String; begin   if Frm_Main.PageControl1.ActivePageIndex = 0 then      begin       if ARow = 0 then         begin           AValue :=  Frm_Main.DBGrid_TEST.DataSource.DataSet.Fields[ACol].FieldName;           Frm_Main.DBGrid_TEST.DataSource.DataSet.First;         end       else         begin           s  := UTF8Encode(Frm_Main.DBGrid_TEST.DataSource.DataSet.Fie lds[ACol].AsWideString);           if s = '' then             AValue := NULL           else             AValue := s;           if ACol = Frm_Main.DBGrid_TEST.DataSource.DataSet.FieldCount-1 then             begin               Frm_Main.DBGrid_TEST.DataSource.DataSet.Next;               Frm_Main.Logging.WriteToLog(inttostr(Frm_Main.ProgressBa r1.Position));  //goes like 29, 44, 59, etc  <---------------?               Application.ProcessMessages;             end;         end;         Frm_Main.ProgressBar1.Position := Frm_Main.ProgressBar1.Position + 1;     end; ---------------------------------------------------------------------- wp · 2014-12-15 21:00 You have to set the max of the progressbar in the main export routine to the record count, and in the event handler you advance the position of the progress bar after each record has been exported completely (i.e. where the dataset.Next is). You also have to call Application.Processmessages to enforce a repainting of the progressbar. Your code is basically correct, but you advance the Progressbar with every *field* and call ProcessMessages after every *record*. Therefore the display jumps in steps of your fieldcount (15), and the Progressbar would be full too early. This should work (not tested, though): Code: [Select]procedure TExport_To_File.WriteCellDataHandler(Sender: TObject;   ARow, ACol: Cardinal; var AValue: variant; var AStyleCell: PCell); var   s: String; begin   if Frm_Main.PageControl1.ActivePageIndex = 0 then    begin     if ARow = 0 then     begin       AValue :=  Frm_Main.DBGrid_TEST.DataSource.DataSet.Fields[ACol].FieldName;       Frm_Main.DBGrid_TEST.DataSource.DataSet.First;     end     else     begin       s := UTF8Encode(Frm_Main.DBGrid_TEST.DataSource.DataSet.Fields[A Col].AsWideString);       if s = '' then         AValue := NULL       else         AValue := s;       if ACol = Frm_Main.DBGrid_TEST.DataSource.DataSet.FieldCount-1 then       begin         Frm_Main.DBGrid_TEST.DataSource.DataSet.Next;         Prm_Main.Progressbar1.Position := FrmMain.Progressbar1.Position + 1;         Application.ProcessMessages;       end;     end;   end; end; Note that calling Application.ProcessMessages after every record will cause a slow-down of the export. When the progress bar is - say - 200 pixels wide and you need an update only every pixel then you can skip 8000/200 = 40 records before you update the progressbar and call ProcessMessages again. So, here's some better code (again, not tested): Code: [Select]var   counter: Integer;    step_size: Integer; procedure TExport_To_File.Export_To_xlsx(Adbgrid: TDBGrid); var   workbook  : TsWorkbook;   Worksheet : TsWorksheet;   i, j        : Integer; begin   screen.Cursor:=crHourglass;   if Adbgrid.DataSource.DataSet.IsEmpty = True then     begin       MessageDlg('De DbGrid is leeg. Export is niet mogelijk.', mtWarning,[mbOk],0);     end   else     begin  //Export starten         try           workbook := TsWorkbook.Create;           try             worksheet := workbook.AddWorksheet('Sheet1');             worksheet.WriteFontStyle(0, 1, [fssBold]);             workbook.Options := [boVirtualMode, boBufStream];             workbook.VirtualColCount := Adbgrid.DataSource.DataSet.FieldCount;             workbook.VirtualRowCount := Adbgrid.DataSource.DataSet.RecordCount +1;             Frm_Main.ProgressBar1.Min:=0;             Frm_Main.ProgressBar1.Max:=Frm_Main.DBGrid_TEST.DataSource .DataSet.RecordCount;             Frm_Main.ProgressBar1.Position:=0;             if Fmt_Main.Progressbar1.Max < Frm_Main.Progessbar1.Width then  // this case would cause a divide-by-0 later               step_size := 1             else               step_size := Frm_Main.ProgressBar1.Max div Frm_Main.Progressbar1.Width;  // if the progress bar is vertical use "Height"             counter := 0;             workbook.OnWriteCellData := @WriteCellDataHandler;             try               Adbgrid.DataSource.DataSet.DisableControls;               workbook.WriteToFile(SaveDialog.FileName + '.xlsx' , sfOOXML, true);             finally               Adbgrid.DataSource.DataSet.First;  //back to the first record in the dataset               Adbgrid.DataSource.DataSet.EnableControls;             end;             //workbook.WriteToFile('test_virtual_02.xls', sfExcel2, true);           finally             workbook.Free;           end;         finally           screen.Cursor:=crDefault;         end;     end; end; procedure TExport_To_File.WriteCellDataHandler(Sender: TObject;   ARow, ACol: Cardinal; var AValue: variant; var AStyleCell: PCell); var   s: String; begin   if Frm_Main.PageControl1.ActivePageIndex = 0 then    begin     if ARow = 0 then     begin       AValue :=  Frm_Main.DBGrid_TEST.DataSource.DataSet.Fields[ACol].FieldName;       Frm_Main.DBGrid_TEST.DataSource.DataSet.First;     end     else     begin       s := UTF8Encode(Frm_Main.DBGrid_TEST.DataSource.DataSet.Fields[A Col].AsWideString);       if s = '' then         AValue := NULL       else         AValue := s;       if ACol = Frm_Main.DBGrid_TEST.DataSource.DataSet.FieldCount-1 then       begin         Frm_Main.DBGrid_TEST.DataSource.DataSet.Next;         inc(counter);         if (counter mod step_size = 0) then begin           Prm_Main.Progressbar1.Position := counter;           Application.ProcessMessages;         end;       end;     end;   end; end; ---------------------------------------------------------------------- Hansvb · 2014-12-16 19:38 It works great Thanks!