InsertAndCopyRange for many rows

Hi!

Help, please.

I streach formulas on many rows:

for Acol := 4 to ColWithFormulaCount do
begin
   RealColIdx := xlsIn.ColFromIndex(FirstStartRow, ACol);
   if (xlsIn.GetCellValue(FirstStartRow , RealColIdx)).IsFormula then
      xlsIn.InsertAndCopyRange(TXlsCellRange.Create(FirstStartRow , RealColIdx,FirstStartRow,                     RealColIdx), FirstStartRow + 1, aCol, Records.RecordCount -1,                      TFlxInsertMode.NoneDown);
end;

If  RecordCount is about 370 000 then application begin load huge volume of RAM.

How i may fixed it?

Hi,

Formulas consume much more ram than normal cells. They have to store the formula result, there is a cache for recalculation, they have to store the tokens themselves and many extra information (like for example a flag to know if it is recalculating a circular reference). If you have 370,000 rows, I would expect it will use a lot of ram. How much memory are we speaking of?

There is a feature planned for the future of compressing formulas so they consume less memory, but they will always use more than a normal cell. They also slow down the full spreadsheet, because they need to be recalculated, and recalculating 370,000 formulas takes some time. Normally the idea is that the bigger the spreadsheet, the less formulas/fancy stuff it should use. Up to 30,000 rows you should be able to use as much stuff as you need. Up to 100,000 rows, it still should work fine, but it is a good idea to use less fancy stuff. Between 100,000 ans 1,000,000 (the maximum Excel allows) both Excel and FlexCel (and whatever you use) will start to slow down, and you should use simpler stuff.

A simple calculation: To store a double precision number it takes 8 bytes. You also need to store the format of the cell, which is 2 bytes more, and a pointer to the cell which is 4 bytes (in 32 bits). So the minimum to store a number on the sheet is 14 bytes. If you have 370,000 rows and say 30 columns, we are speaking about 14370,00030 = 148 mb. But this is the bare minimum, if the file had only numbers. If you have strings, formulas, etc, the used memory will go up.

I speak about 2GB RAM.

Thank you very much. Topic is closed.