How to copy a row?

FaceHoof · October 3, 2023, 12:21pm

Hello. I write code in Lua using the luacom library. I need to open a file for LibreOfficeCalc and copy a row with a certain index. This is how I was able to get the sheet I needed:

self._myLOCdoc = luacom.CreateObject("com.sun.star.ServiceManager")
self._desktop = self._myLOCdoc:createInstance("com.sun.star.frame.Desktop")
self._workbook = self._desktop:loadComponentFromURL("file:///" .. file_path, "_blank", 0, {})
self._worksheet = _workbook:getSheets():getByIndex(0)

It seems to me that I can get a row object that looks like this:

local sourceRow = self._worksheet.Rows(row_num)

row_num - row index
How can I copy this row to the next one (row_num+1)?

Lupp · October 3, 2023, 12:43pm

I can’t tell anything specific for “Lua”, but the API method you need to use is described here. (Generally use the LibreOffice SDK API Referenc in such cases.)
The CellAddress for the target you can get based on the CellAddress of the leftmost cell of your sourceRow: sourceRow.getCellByPosition(0, 0).CellAddress
In this structure you then increase the .Row value by one.
sourceRow.RangeAddress is what you ned to place on the second parameter position of sourceRow.Spreadsheet.copyRange()
Of course, this will throw an error if your sourceRow is the bottom row of the sheet.

Villeroy · October 3, 2023, 12:47pm

I don’t know how to perform a clipboard copy from one document to another. Most people want to transfer raw data only:

da = sourceRow.getDataArray()
targetRow.setDataArray(da)

FaceHoof · October 5, 2023, 7:36am

Unfortunately, nothing works out for me. I tried to get cell addresses from the range and register them myself. Like that:

local CellAddress = {

            Sheet = 0,

            Row = 6,

            Column = 0,

            }

        local CellRangeAddress = {

            Sheet = 0,

            StartColumn = 0,

            StartRow = 3,

            EndColumn = 3,

            EndRow = 3,

        }

        self._worksheet:copyRange(CellAddress, CellRangeAddress)

This does not work. Maybe you could show me an example in Python or another language?

JohnSUN · October 5, 2023, 8:12am

Please tell us in more detail what you mean by this.
Does the source row have formulas? If so, what cells are they referring to? Do you need to change these formulas when copying? If this is not the case and you only need to copy the values and formats, then perhaps you should use the autofill mechanism? This will allow you to simply select the range “original row + next row” (or several next rows) and perform the task with one command.

I’m not a Lua expert, but it seems to me that no programming language requires a comma before the closing parenthesis.

FaceHoof · October 5, 2023, 8:22am

Formulas don’t matter to me. Only text and format need to be copied. I can attach the file with which I conduct experiments

TestFile.ods (10.4 KB)

I want to copy line number 4 to line number 5

Can you give an example of using the autocomplete mechanism in Python or another language?

And yes, you are right. The comma was unnecessary.

JohnSUN · October 5, 2023, 8:35am

Like this?

Sub FillRngAUTO(nRow As Long)
Dim oSheet As Variant, oCellRangeByName As Variant
	oSheet = ThisComponent.getSheets().getByIndex(0)
	oCellRangeByName = oSheet.getCellRangeByName("A" & nRow &":AXJ" & (nRow+1))
	oCellRangeByName.fillAuto(com.sun.star.sheet.FillDirection.TO_BOTTOM, 1)
End Sub

Sub tst
	FillRngAUTO(5)
	FillRngAUTO(6)
	FillRngAUTO(4)
End Sub

gaetanopr · October 5, 2023, 8:46am

In Python

from com.sun.star.sheet.FillDirection import TO_BOTTOM
def fill(*_):
    doc=XSCRIPTCONTEXT.getDocument()
    cellrange = doc.Sheets[0]["A4:C5"]        
    cellrange.fillAuto(TO_BOTTOM, 1)

karolus · October 5, 2023, 9:09am

@FaceHoof: I have tried:

luarocks install luacom --local 
Installing https://luarocks.org/luacom-1.4-1.src.rock

Error: Rockspec error: build type not specified

Q: is »luacom« only for windows? ( I am on linux with arm64 architectur )

FaceHoof · October 5, 2023, 9:12am

Yes, I’m sure that COM technology works exclusively in Windows

Lupp · October 5, 2023, 9:16am

FaceHoof:

local CellAddress = {
            Sheet = 0,
            Row = 6,
            Column = 0,
            }
… …
This does not work. Maybe you could show me an example in Python or another language?

You cannot create a LibO API structure as something like a “sequence” or “array” or “recordtype” in any programming language.
You need to use the API means. Any language usable in programming for LibO documents needs a “bridge” giving access to the fundamental services of the LibO API thus enabling you to create the structures, services, interfaces (and sometimes to access the ConstantGroups) used by the API.
Though LibreOffice Basic is a very poor language, it’s the only one I know coming with a very wide and short bridge in the mentioned sense. It has a few predefined variables (StarDesktop e.g.) and methods (CreateUNOService e.g.), in short: means functionally belonging to the API.
This is the reason for what many contributors here use it (LibO Basic) to present their suggested approaches to solutions.
If you are using/preferring a different language, this is your choice, of course, and if you need to rely on means simply not supported by the API and not implemented in an efficient way in Basic, the usage of a different language may be unavoidable.
Nonetheless you will need to learn how to “transfer” suggestions presented in Basic to the different basis, if the wealth of well considered suggestions accessible via this site shall be useful for you.

JohnSUN · October 5, 2023, 9:18am

By the way, if the result of cellrange.fillAuto() does not meet your expectations, you can also try another method cellrange.fillSeries(). Something like
cellrange.fillSeries(FD_TO_BOTTOM, FM_SIMPLE, FDM_FILL_DATE_DAY, 0.0, 0.0)

Lupp · October 5, 2023, 9:37am

Unfortunately “don’t matter to me” doesn’t clearly tell if they may be copied or must be suppressed or are assured to not occur in the source row.
Information of this kind is , however, important for somebody trying to suggest a usable, clear, and simple solution.

(The example file attached above doesn’t help insofar. It’s generally too poor to extract generalising information -as informed guesses- from it.)

FaceHoof · October 5, 2023, 9:41am

Formulas do not need to be copied. Only the text and format should be copied. If there is a formula in the source line, it should be ignored.

Lupp · October 5, 2023, 9:49am

The API doesn’t offer specific means to “ignore” something.

You can copy the formula. (Regard automatic reference adaptions!)
You can replace it by its result (using get/set DataArray() e.g.)
You can replace it by an “empty” variant.
You may have an additional idea what to do.

In every case you need to be precise about what you want/need to achieve.

FaceHoof · October 5, 2023, 9:50am

I want to replace with an empty variant

Villeroy · October 5, 2023, 9:58am

self._myLOCdoc = luacom.CreateObject("com.sun.star.ServiceManager")

If you can use CreateObject, may be you can use it with “com.sub.star.table.CellRangeAddress” in order to get a new range address.
Or simply use self.myAddress = any_range.getRangeAddress() and then manipulate that structure.
When moving, copying, expanding ranges in StarBasic, I use the following helper functions:

Function getOffsetRange(oRg, nRowOffset&, nColOffset&, nRowResize&, nColResize&)
Dim addr
'calls: getRangeByAddress
	addr = oRg.getRangeAddress()
	addr.StartRow = addr.StartRow + nRowOffset
	addr.EndRow = addr.EndRow + nRowOffset
	addr.StartColumn = addr.StartColumn + nColOffset
	addr.EndColumn = addr.EndColumn + nColOffset
	if nRowResize > 0 then addr.EndRow = addr.StartRow + nRowResize -1
	if nColResize > 0 then addr.EndColumn = addr.StartColumn + nColResize -1
	getOffsetRange = getRangeByAddress(oRg.getSpreadsheet(), addr)
End Function

'pass a spreadsheet-document, sheet or range  together with a c.s.s.table.CellRangeAddress
'return empty if oAddr out of bounds or wrong obj
Function getRangeByAddress(obj, oAddr as com.sun.star.table.CellRangeAddress)
on error goto nullErr:
Dim oSheet
	If obj.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
		REM use the sheet specified by given address
		oSheet = obj.getSheets.getByIndex(oAddr.Sheet)
	else
		REM use given object (range/sheet) as parent range
		oSheet = obj
	endif
	getRangeByAddress = oSheet.getCellRangeByPosition(oAddr.StartColumn,oAddr.StartRow,oAddr.EndColumn,oAddr.EndRow)
exit function
nullErr:
	getRangeByAddress = Null
End Function

FaceHoof · October 5, 2023, 11:24am

I did it! Here’s an example that copies row number 4 to row number 5:

local cellrange = self._worksheet:getCellRangeByPosition(0, 3, 2, 3)
		local cursor1 = self._worksheet:createCursorByRange(cellrange)
		local sheet_used_range = cursor1:getRangeAddress()
	
		cellrange = self._worksheet:getCellRangeByName("A1")
		local cellAddress = cellrange:getCellAddress()
		cellAddress:setColumn(0)
		cellAddress:setRow(4)

		self._worksheet:copyRange(cellAddress, sheet_used_range)

I think this is not very good code and I still need to figure out how it works. But now I have something to work with. Thank you all very much for your help!

karolus · October 5, 2023, 12:07pm

FaceHoof:

    local cellrange = self._worksheet:getCellRangeByPosition(0, 3, 2, 3)
    local cursor1 = self._worksheet:createCursorByRange(cellrange)
    local sheet_used_range = cursor1:getRangeAddress()

sofar it should work the shortway:

        local cellrange = self._worksheet:getCellRangeByPosition(0, 3, 2, 3)
        local sheet_used_range = cellrange:getRangeAddress()