AdvGridWorkbook memory allocation for second sheet


I have got a problem when creating a second sheet with the AdvGridWorkbook:

My first sheet is quite large (about 500 columns and 5000 rows). Creating this one works perfectly fine. However, when creating a second worksheet which is to be quite small (about 2 columns and 20 rows), the program is busy for quite some time.

When looking at the resource monitor I recognized that my program is allocating as much memory as previously for the first work sheet (about 150mb). It seems like the memory allocation occurs when setting 'AdvGridWorkbook.ActiveSheet'. There is a code example following.

Is there a possibility of setting the default size of a new sheet before setting the active sheet?


I am running the TMS Component Pack at version


Thank you in advance

Niklas Schelten

  startTime := MillisecondOfTheDay(Now);
  ASGWB.ActiveSheet := 0;
    ASGWB.Grid.ColCount := 500;
    ASGWB.Grid.RowCount := 5000;

    for i_R := 0 to ASGWB.Grid.RowCount -1 do
      for i_C := 0 to ASGWB.Grid.ColCount - 1 do
        if i_R = 0 then
          ASGWB.Grid.Cells[i_C, i_R] := inttostr(i_C)
          ASGWB.Grid.Cells[i_C, i_R] := inttostr(i_C) + '/' + inttostr(i_R);
        if  i_R = i_C then
          ASGWB.Grid.Colors[i_C, i_R] := clGreen;

  ShowMessage(IntToStr(MillisecondOfTheDay(Now)-startTime));// until here it takes about 500ms
  startTime := MillisecondOfTheDay(Now);
  ASGWB.ActiveSheet := 1;
  ShowMessage(IntToStr(MillisecondOfTheDay(Now)-startTime));// this outputs something about 18000ms

<!--if gte mso 9>
<m:mathFont m:val="Cambria Math"/>
<m:brkBin m:val="before"/>
<m:brkBinSub m:val="&#45;-"/>
<m:smallFrac m:val="off"/>
<m:lMargin m:val="0"/>
<m:rMargin m:val="0"/>
<m:defJc m:val="centerGroup"/>
<m:wrapIndent m:val="1440"/>
<m:intLim m:val="subSup"/>
<m:naryLim m:val="undOvr"/>

When you use TAdvGridWorkbook, what happens when you change the active sheet is that the data of the current active sheet grid is swapped with the data of the new active sheet grid. So, there is actually only one grid and its data is swapped in and out but this data of course stays in memory. The data comprises the cell values and cell attributes. So, if you have 500x5000 cells, this means there is in memory 2.500.000 strings & 2.500.000 cell attributes (objects with color, font, alignment, image, checkbox... properties). This approach is chosen for situation where it is not desirable to have as much grid instances as there are sheets as the grid instance by itself already consumes quite a bit of memory not taking the data in account. If you have a situation where you have few sheets, it is perhaps not beneficial to use this concept and its faster to use for example 2 grid instances placed on a page control where swap of sheets will be instant.

Thank you for the reply.
Too bad. However, I will describe my problem in a more general way.

As previously said, I am having two sheets of pretty different sizes. I need to export those sheets to Excel. Previously I had the possibiliy of exporting two AdvStringGrids in two sheets in the same Excel file with the component AdvGridExcelIO.
However, as my data sets grew I came to the point were I needed more columns than the old Excel format (.xls) support (255) and recognized that AdvGridExcelIO doesn't support the new Excel format.
Therefore, I am searching for a way to create an Excel file in the new Excel format: I need two sheets, the possibility of exporting the cells as strings and exporting the format (e.g. background color etc.) of the cells.
Is there a possibility without using the Workbook which works similiar to the AdvGridExcelIO?


TMS Grid Filters is the bridge between our grids and TMS Flexcel to import/export from the new .XLSX format.

Okay thank you very much, then I will go with Flexcel :)