Fast way to retrieve data from Tstringgrid and to populate the retrieved data in chart containing in word

Question

I have Stored a record containing TStringgrid table in a long blob field in a table with in a DB. I am retrieving the long blob stored in a table with in a DB using the code below. But however it is very slow. Could some one suggest the fast way to print tstringgrid data stored as a longblob in DB to a chart in word using delphi.

Field := mySQLQuery1.FieldByName('Table');  //Accessing the table field in DB
blob := mySQLQuery1.CreateBlobStream(Field, bmRead); 
F := TStringList.Create;
try
  F.LoadFromStream(blob); //To load blob into string list
  try
    rowCount:= StrToInt(F[0])-1; //To get the total count of rows in string grid
    colCount:= StrToInt(F[1]); //To get the total count of columns in string grid

    aShape := WordApplication1.ActiveDocument.InlineShapes.Item(1); //To access the excel embedded chart in word
    ashape.OLEFormat.Activate;
    control2 := aShape.OLEFormat.Object_ as ExcelWorkBook;
    AWorkSheet := control2.sheets['Table1'] as ExcelWorkSheet; //To access the sheet in word
  except
    on E: Exception do
      MessageDlg(E.Message, mtInformation, [mbOk], 0);
  end; { try }

  i:= 2;           
  while i <= rowCount do
  begin
    str:=F[i + 2];  
    //The values of each row stored in Tstringgrid for example are of the followingorder 
    //',,,,,,"0,00011","13,6714","0,00023","13,5994"'

    for j := 1 to colCount do
    begin
      a:=pos('"',str);
      b:=pos(',',str);
      if (b<a) OR (a=0) then //To get and remove all null values by using searching for , delimiter
      begin
        if b=0 then substring:=str
        else
        begin
          substring:=copy(str,0,pos(',',str)-1);
          str:=copy(str,pos(',',str)+1,length(str));
        end; {if}
      end {if}
      else
      begin   //To get all values by using searching for " delimiter
        str:=copy(str,pos('"',str)+1, length(str));
        substring:=copy(str,0,pos('"',str)-1);
        str:=copy(str,pos('"',str)+2,length(str));
      end; {else}
      if substring<> '' then
      begin
        AWorkSheet.Cells.Item[i, (j-6)].value := StrToFloat(substring);
      end;
    end; {for j}
    i := i + 1;
  end;{ while i}
finally
  F.Free;
  freeandnil(blob);
end; {try}

I have more than 10000 records stored. It is fast when i go with fewer records — mdel, Sep 20 '16 at 21:25
At a guess, the bottleneck is probably the calls to `AWorkSheet.Cells.Item[i, (j-6)].value := StrToFloat(substring);`, but you really need to profile to find out which part is slow. There are half a dozen things you could rewrite or redesign in this code above to improve the performance in different places but unless you fix the slow part you're just stabbing blindly in the dark and will likely not succeed. — J..., Sep 20 '16 at 21:36
@J... you were absolutely correct. I just commented out your mentioned line and code runs very fast. Then could you suggest me a better way to fill items quickly in a worksheet to generate the chart? — mdel, Sep 20 '16 at 21:49
@DavidHeffernan posted a good place to start in his answer - typically with Excel you want to transfer array data to a range for this type of operation. There are plenty of examples around of how to do this. — J..., Sep 20 '16 at 22:27

score 4 · Accepted Answer · edited May 23 '17 at 12:00

According to the comments the bottleneck is the assignment to

AWorkSheet.Cells.Item[i, (j-6)].value

in a loop. This is a common mistake made when automating Excel. Each call to automate Excel incurs a significant cost because you are performing a COM call into a different process. There is serious overhead in doing this.

Instead of setting the data with N distinct calls, one for each cell, set the data with one call that assigns to all N cells. Do that by selecting a range representing the entire target area of the worksheet, and assign a variant array containing all N items in one single call.

Some example code in that vein can be found here: c++ Excel OLE automation. Setting the values of an entire cell-range 'at once'

Found it thanks http://stackoverflow.com/questions/16641897/export-delphi-stringgrid-to-excel — mdel, Sep 20 '16 at 23:08

Fast way to retrieve data from Tstringgrid and to populate the retrieved data in chart containing in word

1 Answers1