How to get number of cells in LibreOfficeCalc workspace using COM-object?

English
,
#1

I need to write a script in Lua that can open the required file for LibreOfficeCalc, find the required sheet in it, and count the number of cells in the working range. I was able to get the desired sheet this way:

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, {ReadOnly = false})
self._worksheet = _workbook:getSheets():getByIndex(0)

local sheetName = self._worksheet:getName() --Check sheet name

--Trying to count cells
local cursor = self._worksheet:createCursor()
cursor:gotoEndOfUsedArea(true)
--cursor:gotoStartOfUsedArea(false)


local nCountCells = cursor:getRows():getCount() * cursor:getColumns():getCount()
local RCount = cursor:getRows():getCount() --always 1
local CCount = cursor:getColumns():getCount() --always 1

Why do I always only get one row and one column? How to do it right?
I am attaching my document with which I am experimenting.
TestFile.ods (11.6 KB)

#2

Are you sure it would work to create a PropertyValue? Not that it should change something in this question, but still…

#3

This parameter was in the app that I found. Its presence or absence does not change anything. As I understand it, it is for some special situations and in my case it is optional.

#4

Indeed. I just tell you that it is most likely not correct - I doubt that it would automagically map some identifier and a boolean into specific members of a 4-member struct required in this case … (and “ReadOnly” is expected to be a name of the property, so a string)

#5

Your code should work. Using an equivalent code in VBS works for me locally:

objServiceManager = CreateObject("com.sun.star.ServiceManager")
desktop = objServiceManager.createInstance("com.sun.star.frame.Desktop")
UrlTransformer = objServiceManager.createInstance("com.sun.star.util.URLTransformer")
Url = objServiceManager.Bridge_GetStruct("com.sun.star.util.URL")
Url.Complete = "D:\Downloads\TestFile.ods"
UrlTransformer.parseSmart Url, ""
workbook = desktop.loadComponentFromURL(Url.Complete, "_blank", 0, Array())
worksheet = workbook.getSheets().getByIndex(0)
sheetName = worksheet.getName()
cursor = worksheet.createCursor()
cursor.gotoEndOfUsedArea(true)

nCountCells = cursor.getRows().getCount() * cursor.getColumns().getCount()
RCount = cursor.getRows().getCount()
CCount = cursor.getColumns().getCount()

MsgBox nCountCells & " " & RCount  & " " & CCount

image

1 Like
#6

In order to open an ODF-document “just normally”, pass an empty array as last argument to loadComponentFromURL.
Even if your code worked, it would return the used area including any blank formatted cells.
The following Python code returns the rectangle that includes all non-empty cells regardless of formatting:

def getUsedRange(oSheet):
	oCursor1 = oSheet.createCursorByRange(oSheet)
	oCursor1.gotoStart()
	addr = oCursor1.getRangeAddress()
	oCursor2 = oSheet.createCursorByRange(oSheet)
	oCursor2.gotoEnd()
	addr.EndColumn = oCursor2.RangeAddress.EndColumn
	addr.EndRow = oCursor2.RangeAddress.EndRow
	return oSheet.getCellRangeByPosition(addr.StartColumn, addr.StartRow, addr.EndColumn,  addr.EndRow)

therefore you may try out this:

desktop = objServiceManager.createInstance("com.sun.star.frame.Desktop")
UrlTransformer = objServiceManager.createInstance("com.sun.star.util.URLTransformer")
Url = objServiceManager.Bridge_GetStruct("com.sun.star.util.URL")
Url.Complete = "D:\Downloads\TestFile.ods"
UrlTransformer.parseSmart Url, ""
workbook = desktop.loadComponentFromURL(Url.Complete, "_blank", 0, Array())
worksheet = workbook.getSheets().getByIndex(0)
oCursor1 = worksheet.createCursorByRange(worksheet)
addr = oCursor1.getRangeAddress()
oCursor2 = worksheet.createCursorByRange(worksheet)
oCursor2.gotoEnd()
addr.EndColumn = oCursor2.RangeAddress.EndColumn
addr.EndRow = oCursor2.RangeAddress.EndRow
oRange = oSheet.getCellRangeByPosition(addr.StartColumn, addr.StartRow, addr.EndColumn,  addr.EndRow)
Msgbox oRange.AbsoluteName
1 Like
#7

Is it possible that the cursor is not positioned correctly? I did this:

local cursor = self._worksheet:createCursor()
cursor:gotoEndOfUsedArea(true)
--cursor:gotoStartOfUsedArea(false)
local sheet_used_range = cursor:getRangeAddress()
print( sheet_used_range:StartColumn(), sheet_used_range:EndColumn())
print( sheet_used_range:StartRow(), sheet_used_range:EndRow())

And I got this output:
12 12
10 10

#8

It could - if the call to createCursor() returned a cursor already at the end of the range. However, that would be a bug. Possibly there is in the (unspecified) version of LibreOffice that you use?

1 Like
#9

Please provide version numbers for Lua, LibreOffice and Windows. Sometimes these numbers allow you to quickly find the cause of the misunderstanding.

#10

I’m using LibreOfficeCalc version 7.6.1.2, Windows 10 and Lua version 5.2

#11

Me too; so doesn’t look like that issue. No idea why could this happen. Of course, you still may use the end row/column to get the needed numbers (0-based index). If you find out the reason, please don’t hesitate to file a respective bug report (or at least, mention it here). Thanks!

#12

There is a suspicion that this line is not executed as intended. What is TRUE equal to in Lua? Perhaps the cursor perceives the command as FALSE? Try specifying a number parameter. For example, 1 or -1

2 Likes
#13

You are absolutely right! Everything starts working correctly if you pass the number 1 as a parameter

Find replace Line end \n in a macro

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.