crude way to speed up excel buffer

Reinhard

If you have tried to use the excel buffer you have problably seen how slow it is. Recently I noticed someone running a report that was taking several hours...

A big part of the problem lies in the fact that the excel buffer checks every single possible formatting option for every single cell that you write to. Now, most likely you aren't using italics, bold, colors, number formatting, formulas, comments, or line styles. So by simply formatting out those checks it becomes pretty quick:

REPEAT
      InfoRecNo := InfoRecNo + 1;
      Window.UPDATE(1,ROUND((RecNo + InfoRecNo) / TotalRecNo * 10000,1));


//      IF InfoExcelBuf.NumberFormat <> '' THEN
//       XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).NumberFormat :=
//          InfoExcelBuf.NumberFormat;

      IF InfoExcelBuf.Formula = '' THEN
        XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Value :=
          InfoExcelBuf."Cell Value as Text"
      ELSE
        XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Formula :=
          InfoExcelBuf.GetFormula;

{
      IF InfoExcelBuf.Comment <> '' THEN
        XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).AddComment :=
          InfoExcelBuf.Comment;
      IF InfoExcelBuf.Bold THEN
        XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Font.Bold :=
          InfoExcelBuf.Bold;
      IF InfoExcelBuf.Italic THEN
        XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Font.Italic :=
          InfoExcelBuf.Italic;
      XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Borders.LineStyle :=
        XlLineStyleNone;
      IF InfoExcelBuf.Underline THEN
        XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Borders.Item(XlEdgeBottom).LineStyle :=
         XlContinuous;
}
    UNTIL InfoExcelBuf.NEXT = 0;

Again, very crude, but it only takes a moment and you will be able to run a report in a couple minutes instead of a couple hours.

ara3n

You have commented out two places. This is odd that simply checking for a boolean field in a table would make a difference of hours.

I'm guessing the report that calls the excel buffer sets them to true. So I would change report to set them to false.

Savatage

Also check this post - to see if it's better for you.

viewtopic.php?f=5&t=26657

Reinhard

shoot... my bad. I investigated some more and after comparing to Cronus I see that there are some modifications to our excel buffer table that contribute to most of the slowdown :oops: ](*,) #-o