How to quickly fill a sheet with data?

[FaceHoof]

Hello. I am writing code in Lua using the luacom library. I need to fill out a document with data using a template. I am attaching an example template.
TestT.ods (10.3 KB)

At the moment I am doing everything according to the following algorithm:

1. Filling in the column headings (line 3). Everything is fine here.

2. I find a line containing the value %table%. This means that what follows is the data for the table.

3. I copy the data line the required number of times.

4. I insert data into the resulting rows.

It works, but is very slow. If there are 100 or more lines, the whole process takes a lot of time.
Is there a way to create a range in advance with the required number of rows and insert them in one step?
Can you show me an example of this?

P.S. It is not necessary to give an example in Lua, Python will suit me, I can translate it.

[Lupp]

Don’t understand:

• What’s the actual data? Are the data literal cell contents following the key?
• How will the algorithm detect the “required number of lines”?
• Where are the “resulting rows”?
• Shall all these rows actually get the same data?
• What if more than one cell in column A contains the key %table% ?

[JohnSUN]

@Villeroy has already shown you how to get a range of cells from the beginning row of the table to the required number of rows. He also showed that for quick filling it is better to use an array. So, get a range of cells of the desired size and read it into an array using .getDataArray(), change the elements of this array to the values ​​you need and with a single .setDataArray() statement put them back into the range of cells.

[FaceHoof]

It seems to me that all this is not very important. My question only concerns the ability to insert a large amount of data at once.
Okay… let me simplify the question:

Let’s say I have an array of strings, each element of which contains a value for one cell. There are one hundred elements in this array. I create a for loop in which the row index changes in ascending order, and in the body of the loop I assign a value to the cells. Like that:

local col = 1
		for row = 0, 100 do
			local cell = self._worksheet:getCellByPosition(col, row)
			cell:setString(res[row])
		end

The result is a column of values ​​in the document. Can I do the same in one action?

[Villeroy]

Don’t loop. Fill an array and dump it to a cell range.
97004.ods (366.3 KB)

[mikekaganski]

@FaceHoof

Is there a way to create a range in advance with the required number of rows and insert them in one step?

@Lupp

Asking some questions

@JohnSUN

Provides some pointers

@FaceHoof

It seems to me that all this is not very important (so I show that I don’t understand the replies, but don’t appreciate them anyway…).

@FaceHoof please make an effort, and check the links @JohnSUN gave you. Also acknowledge, that @Lupp had a point - you might need to use different array-getting/setting functions, based on what you try to do…

[JohnSUN]

@FaceHoof

and in the body of the loop I assign a value to the cells

For this example of yours, the code that I described in words will be like this:

Sub Fill100CellsWithStrOfNum()
Dim oSheet As Variant
Dim oCellRangeByPosition As Variant
Dim oDataArray As Variant
Dim i As Long 
	oSheet = ThisComponent.getSheets().getByIndex(0)
	oCellRangeByPosition = oSheet.getCellRangeByPosition(1, 0, 1, 100)
	oDataArray = oCellRangeByPosition.getDataArray()
	For i = 0 To 100
		oDataArray(i)(0) = Cstr(i)
	Next i
	oCellRangeByPosition.setDataArray(oDataArray)
End Sub

[FaceHoof]

Thank you very much!

[mikekaganski]

Note also, that there is get/setFormulaArray.

[Lupp]

@FaceHoof

for row = 0, 100 do

101 rows needed?