Why the lastRow keeps 0 after writing data in calc table?

English
,
#1

When Sheet1 is blank,nothing in it,the value of oCurs.RangeAddress.EndRow is 0, why the lastRow keeps 0 after writing data in calc table?

    oDoc = ThisComponent
    Sheet = oDoc.getSheets().getByName("Sheet1")
    oCurs = Sheet.createCursor()
    oCurs.gotoEndOfUsedArea(True)
    lastRow = oCurs.RangeAddress.EndRow 
    print " when sheet is empty ,the row number is " & lastRow
    Cell = Sheet.getCellByPosition(0,0) 
    Cell.value = 100
    oCurs = Sheet.createCursor()
    oCurs.gotoEndOfUsedArea(True)
    lastRow = oCurs.RangeAddress.EndRow 
    print "after insert data in one line ,the row number is " &  lastRow

I get the output

 when sheet is empty ,the row number is 0
after insert data in one line ,the row number is 0

Why can’t get the below output?

 when sheet is empty ,the row number is 0
after insert data in one line ,the row number is 1
#2

Before looking for the difference between an “empty” or “non-empty” UsedArea, let’s try to understand what you’re actually trying to achieve? Do you want the number of the last non-empty row? Or the number of the first empty row after that?

#3

I want the whole line number in the active sheet.
When worksheet is empty ,no line (no data) in it ,the whole line number in usedrange is 0.
When one line data in the first line ,the whole line number in usedrange is 1.
I find that both lastRow = oCurs.RangeAddress.EndRow and lastRow = oCurs.RangeAddress.EndRow +1 can’t get right answer.
How can get the whole line number used in my case then ?What is the exact meaning for RangeAddress.EndRow ?
If an ods worksheet containing one line data in the first line , RangeAddress.EndRow = 0 ,then an ods worksheet containing nothing , RangeAddress.EndRow = -1 ,otherwise it is un-logical.

#4

I will repeat my question again to make sure I understand your intentions correctly.

In other words - what are you going to do next with this number? Add new data to the table (and then you need the number of the empty row) or read the bottom data in the table (and then you need the number of the non-empty row).
There are many solutions for each of these problems, including the approach with the cursor and defining the last row in the “used range”. The problem with this approach is that on a completely empty sheet you still have a “used range”, it just consists of one empty cell A1. So you need to additionally analyze the contents of the cursor after .gotoEndOfUsedArea(True) - in any way you see fit. But I remind you - .gotoEndOfUsedArea(True) is not the only way to solve such problems.
So once again - what problem are you trying to solve?

#5

I feel that it is un-logical to make RangeAddress.EndRow = 0 whether worksheet contains nothing or worksheet contains only one line.Please give an explanation,

#6

Well, I already explained:

This was agreed upon at the very beginning and now no one will change anything.
I hope you understand that RangeAddress.EndRow = 0 means the FIRST row on the sheet, not “zero”?

#7

This method has 2 flaws:

  1. It includes blank ranges having visible formatting attributes, and sometimes it includes ranges that are covered by drawing objects (form controls, charts, annotations, shapes of all kind).
  2. It returns the top-left cell if the sheet is completely untouched.

The following function returns the cell range that includes all cell content (constants and formulas), ignoring anything else.

Function getUsedRange(oSheet)
	oCursor = oSheet.createCursorByRange(oSheet)
	oCursor.gotoStart()
	addr = oCursor.getRangeAddress()
	oCursor.gotoEnd()
	addr.EndColumn = oCursor.RangeAddress.EndColumn
	addr.EndRow = oCursor.RangeAddress.EndRow
	getUsedRange = oSheet.getCellRangeByPosition(addr.StartColumn, addr.StartRow, addr.EndColumn,  addr.EndRow)
End Function

This one has one flaw:

  • If there are no constants nor formulas, it returns some cell in the last row of the sheet.

The following function returns a collection of cell ranges with method getCount(). If the count of ranges is zero, the given range contains no data nor formulas:

Function getContentRanges(oRange)
	With com.sun.star.sheet.CellFlags
		f = .VALUE + .DATETIME + .STRING + .FORMULA
	End WIth
	getContentRanges = oRange.queryContentCells(f)
End Function

Something to play with:
usedRange.ods (15.3 KB)

#8

You surely know that the rows are numbered 1-based in the sheet view while they have 0-based indices in the API.
The term “used area” is ...

  1. somehow misleading because the object actually is a SheetCellRange.
  2. seriously misleading because it’s always at least one cell (A1) even if this cell also isn’t “used”.
  3. not clearly and explicitly specified: Which cells count as “used” and which cells don’t?

The issues are ...

  1. not very serious.
  2. annoying and not easily handled by user code (see attachment).
  3. without a rescue so far. Only authorised developers could give such a specification, and it may be difficult to implemente it consistently if tried.

usedArea_.ods (11.8 KB)

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