How to correctly shift a cell so that the formulas inside it are corrected

[veb86]

I have such a task. I am creating a calculation book. It already works, and helps me. Thank you!
But there is a problem with the execution time.

There are two sheets.
On the first sheet, I have a list of values ​​and copy keys.
On the second sheet, there is a line that will be sequentially copied depending on the copy key on the 1st sheet. The copied line on the second sheet contains formulas that refer to the first sheet.

It is necessary to duplicate the line for copying on the second sheet so that the formulas refer to the correct line of the first sheet. See the picture.

How I do it now. First, I copy all the lines to the second page according to the copy keys on the first sheet.
After that, I delete those lines whose copy key value is not to be copied. Thus causing the process of changing the formulas of the lines I need. As a result, I get a sequential list of only the rows I need, with formulas that refer to the rows I need on the first sheet

As a result, copying: 760 rows
Copying the necessary rows: 0.33 seconds
Removing empty rows to create a sequential list: 11.88 seconds
I have a fairly powerful computer.
The problem is that there can be 10,000-500,000 rows to copy.
The copying time becomes unreasonably large

What I would like is to copy the row I need sequentially at once, but with the formulas already changed.
As if:
So that they refer to the necessary cells.
Maybe there is a function that will allow me to do this right away?
See the pictures

If it is not clear what I mean, I will make a small example

[wp]

I do not fully understand what you want to do. But when you have speed issues with copying/deleting/editing formulas you should turn off calculation during these operations: Workbook.Options := Workbook.Options - [boAutoCalc]. The following code copies 1000 cells with formulas in sheet 2 (referencing cells in sheet 1) to sheet3, and this takes about 10 seconds with the AutoCalc options, but only a few millseconds without it.

Code: Pascal  [Select][+][-]

1. program Project1;
2. uses
3.   SysUtils, FPSpreadSheet, FPSTypes, xlsxOOXML;
4. var
5.   b: TsWorkbook;
6.   sh1, sh2, sh3: TsWorksheet;
7.   r, firstRow, lastRow: Cardinal;
8.   t: TDate;
9. begin
10.   b := TsWorkbook.Create;
11.   try
12.     t := Now();
13.     //b.Options := [boAutoCalc];    // comment out to improve speed
14.  
15.     sh1 := b.AddWorksheet('Sheet1');
16.     sh2 := b.AddWorksheet('Sheet2');
17.     sh3 := b.AddWorksheet('Sheet3');
18.  
19.     for r := 0 to 1000 do
20.     begin
21.       sh1.WriteNumber(r, 0, r*10);
22.       sh2.WriteFormula(r, 0, '=Sheet1!A' + IntToStr(r+1));
23.     end;
24.  
25.     firstRow := sh2.GetFirstRowIndex;
26.     lastRow := sh2.GetLastrowIndex;
27.  
28.     for r := firstRow to lastRow do
29.       sh3.CopyCell(r, 0, r, 0, sh2);
30.  
31.     if not (boAutoCalc in b.Options) then b.CalcFormulas;
32.  
33.     t := Now() - t;
34.  
35.     b.WriteToFile('test.xlsx', true);
36.     WriteLn('Time to create the workbook: ', FormatDateTime('s.zzz "seconds"', t));
37.  
38.   finally
39.     b.Free;
40.   end;
41.  
42.   Write('Press OK to close...');
43.   ReadLn;
44. end.

[veb86]

I have copying without autocalculation of formulas. The problem is different.
I will prepare a simple example. I need some time

[veb86]

Hello. Sorry for not writing for a long time, a lot of work.
I made an example of the principle by which I currently organize the calculation module (sample_copycell_old.zip). After copying, I have to delete the lines that do not suit me, in all cells the formula indication changes. Everything works fine, but very slowly. Because I have a lot of formulas.
I tried to do it in a new way now (new_not_works.zip), I used the moveRow function. The function works correctly, but does not suit me.  I need the same effect as the deleteRow function. If the pointer inside the formula points to a cell that is on another sheet, then the pointer does not change. But if it points inside the sheet, then it changes. Tell me, maybe there is some other function.
Look at the calculation results of both examples. I think you will notice that it does not match, look at the Winner sheet.

sample_copycell_old.zip - works correctly, but slowly.

new_not_works.zip - does not work correctly

PS
I think I need a formula that can delete many rows at once. Delete rows from and to.
Or some special kind of row moving

[veb86]

Here is an example of my typical situation.
It is necessary to delete more than 1000 lines at once.
Now I delete one by one as in the example.

Is it possible to add group deletion of many lines at once?
Or please tell me how to speed up the transfer of the desired line to the beginning of the list. So that the formulas are transformed in the same way as if we were deleting lines?

[wp]

It is necessary to delete more than 1000 lines at once.

Did you ever consider to use a database?

Now I delete one by one as in the example.

Is it possible to add group deletion of many lines at once?

I am afraid this does not make sense. With every row deleted, every cell needs to be checked whether it contains a formula which then must be deleted or whether any other cell of the workbook (!) contains a formula with points to this cell which then must get a #REF! error. This takes some time. The FPSpreadsheet formula engine is probably too simple for this complex task.

[veb86]

It is necessary to delete more than 1000 lines at once.

Did you ever consider to use a database?

Of course I tried. I am an amateur programmer, so I have little time for programming. I have tried many times to solve the problem of speeding up calculations in my profession.
1. There were attempts to write my own GUI, failed, it takes a lot of time
2. Studied third-party software. Noticed that all calculations come down to a tabular form of editing and obtaining results. Knowing that there is a lot of data, I immediately thought about a DB
3. Tried to master the DB, failed. I have no knowledge of DB, I need to study a lot. But I saw the calculations in the DB, they look terrible. The resulting tables are difficult to edit, they are not intuitively understandable. It is difficult to carry out complex calculations, everything comes down to a request. Experts said to look towards graph-based DBs, it takes a lot of time
4. Returned to Excel, I know it very well. I understand that Excel is not a DB. In attempts to make calculations in Excel, I received a new language for generating calculations using a template. I am delighted. I have 1000 lines of code for the entire generator, I can attach it to any program!

I am afraid this does not make sense. With every row deleted, every cell needs to be checked whether it contains a formula which then must be deleted or whether any other cell of the workbook (!) contains a formula with points to this cell which then must get a #REF! error. This takes some time. The FPSpreadsheet formula engine is probably too simple for this complex task.

You have written a great library, like all open source, it has bugs, errors, etc. But it is developing! I work in an open source program and write code for it. I know what bugs and errors are. I also know how funny very complex user wishes look (I know roughly what is inside our program). Your library, I know little. So, forgive me if I ask to add something difficult code to the library. So far, I only have wishes on my list that add convenience to work. There is nothing critically important.