In calc code using optimalWidth for columns is incorrect

[RussR]

I am having problems with OptimalWidth for columns in calc sheets. I found that the code Sub test() below does not work. As the column 0 gets longer the number of characters not displayed increases.

sub test()
	dim colnum as integer
	dim oColumns as object
	dim col as object
		colnum = 0
		oColumns = FileList.Columns
    	Col = oColumns.getByIndex(colnum)
    	Col.OptimalWidth = True
end sub

However, if I select the longest cell ($A$0 for this example) in the column and execute the code sub autoadjustcol() below the cell and the column are the correct length

sub autoadjustcol()
	dim document   as object
	dim dispatcher as object
	document = ThisComponent.CurrentController.Frame
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	dim args1(0) as new com.sun.star.beans.PropertyValue
	dim args2(0) as new com.sun.star.beans.PropertyValue	
	'the next two lines moves the focus to a cell 
	args1(0).Name = "ToPoint" 
	args1(0).Value = "$A$0
	'the next two lines set the variables that are in the dialog for Optimal Width
	args2(0).Name = "aExtraWidth"
	args2(0).Value = 0
	dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
	dispatcher.executeDispatch(document, ".uno:SetOptimalColumnWidth", "", 0, args2())
end sub

Do I have a mistake in Sub test()?

[newOOo3]

Hi,

I think that the “oColumns = FileList.Columns” is incorrect .

I can not understand “FileList”.

I think it is Ok if you change following;

oColumns = FileList.Columns → oColumns = ThisComponent.getsheets().getByIndex(0)

You can see sample code following URL ;

URL : http://openoffice3.web.fc2.com/OOoBasic_Calc.html#OOoCCR07a

OptimalWidth : 行高さ＆列幅の最適化(1) ⇔ Optimize hight of rows and width of columns(1)

.uno:SetOptimalColumnWidth : 行高さ＆列幅の最適化(2) ⇔ Optimize hight of rows and width of columns(2)

-------- [ PS ] -------

2th answer write;

＞My code ran as does yours and both give the same incorrect result.

⇒

I can not understand why both have the same incorrect result.

I think that Code " OptimalWidth : 行高さ＆列幅の最適化(1) ⇔ Optimize hight of rows and width of columns(1)" to be optimized all height and width in the sheet without error.

2th answer write;

＞・・・simpler code does not get the same result as the UNO code.

⇒

I think that following code give exactly the same result as the UNO code

Sub ColRowOptimaize()
    Dim oDoc as Object, oSheet as Object
    Dim oCol as Object, oRow as Object
	    oDoc = ThisComponent
	    oSheet = oDoc.getSheets().getByIndex(0)
	    oCol = oSheet.getColumns().getByIndex(0)
	    oCol.OptimalWidth = true
	    for i = 0 to 1
		    oRow = oSheet.getRows().getByIndex(i)
		    oRow.OptimalHeight = true
	    next i
    msgbox "Success"
End Sub

[RussR]

I just constructed a column which has one cell that has a string 169 characters long. When I run your code (or my simple code) the column is adjusted to a width that is 8 characters short. What is displayed is not the entire string. When I run the UNO code the column is adjusted to exactly the correct width. All characters are displayed. Maybe my PC is different then yours. The font in the spreadsheet is the standard Arial 10.

[RussR]

I just downloaded LO 4.1.4.2 and the results are the same.

[RussR]

First, I am sorry that I failed to fix ‘Filelist’ in my sample code. It was dimensioned outside this sub routine and is the equivalent of ‘ThisComponent’. My code ran as does yours and both give the same incorrect result. The column is not set to the optimal width. It is close but misses by a few characters. A few characters on the end are missing. As the column gets wider the number of characters missing increases.

On the other hand the code in the link you provided does work correctly. It is:

Sub UnoOptimalColRow()
    Dim oDoc as Object, oCtrl as Object, oFrame as Object
    Dim oDispatcher as Object
	Dim oProp(0) as new com.sun.star.beans.PropertyValue
		oFrame = ThisComponent.CurrentController.Frame
		oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
		'
		oProp(0).Name = "ToPoint"
		oProp(0).Value = "A:A" 
		oDispatcher.executeDispatch(oFrame,  ".uno:GoToCell", "", 0, oProp())
		' 引数を省略すると最適な列幅Dialogが表示される。
		oProp(0).Name = "aExtraWidth"
		oProp(0).Value = 0
		oDispatcher.executeDispatch(oFrame,  ".uno:SetOptimalColumnWidth", "", 0, oProp())
		msgbox "Optimal Colwmn Width",0,"Optimize"
		'
		oProp(0).Name = "ToPoint"
		oProp(0).Value = "1:2" 
		oDispatcher.executeDispatch(oFrame,  ".uno:GoToCell", "", 0, oProp())
		oProp(0).Name = "aExtraHeight"
		oProp(0).Value = 0
		oDispatcher.executeDispatch(oFrame,  ".uno:SetOptimalRowHeight", "", 0, oProp())
		msgbox "Optimal Row Height",0,"Optimize"
End Sub

It correctly sets the column width to exactly the right number of characters. I did not test the row height part but I suspect it also works correctly.
Many thanks this solves my problem.
Maybe somebody knows why the simpler code does not get the same result as the UNO code.