CODEWITHSUNDEEP
delphidelphi-2007
mdel
mdel

Reputation: 43

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

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}

Upvotes: 2

Views: 1115

Answers (1)

David Heffernan
David Heffernan

Reputation: 612794

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'

Upvotes: 4

Related Questions