Ask Your Question
0

in calc code using optimalWidth for columns is incorrect [closed]

asked 2013-12-23 18:27:26 +0200

RussR gravatar image

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()?

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-16 18:32:00.346303

2 Answers

Sort by » oldest newest most voted
1

answered 2013-12-24 10:49:07 +0200

newOOo3 gravatar image

updated 2013-12-25 11:55:13 +0200

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
edit flag offensive delete link more

Comments

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 gravatar imageRussR ( 2013-12-25 14:42:24 +0200 )edit

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

RussR gravatar imageRussR ( 2013-12-26 13:56:20 +0200 )edit
0

answered 2013-12-24 15:55:51 +0200

RussR gravatar image

updated 2013-12-24 15:58:06 +0200

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.

edit flag offensive delete link more

Question Tools

1 follower

Stats

Asked: 2013-12-23 18:27:26 +0200

Seen: 851 times

Last updated: Dec 25 '13