Why can't automatically adjust the width of columns to accommodate their content?

luofeiyu1 · August 31, 2025, 7:58am

The raw table is in the attachment.
sample.ods (34.6 KB)

I want to autofit all cells in the sample.ods,to format it as below:

Why my oobasic code can’t work?

Sub Autofit()
For Each oSheet In ThisComponent.Sheets
     oCurs = oSheet.createCursor()
     oCurs.gotoEndOfUsedArea(True)
     num_row = oCurs.Rows.Count
     num_col = oCurs.Columns.Count
     for  i=0 to num_row-1
         oSheet.rows(i).optimalheight = true
     next i
     for  i=0 to num_col-1
         oSheet.columns(i).optimalwidth = true
     next i
Next
End Sub

sokol92 · August 31, 2025, 9:58am

Try

Sub Autofit2()
  For Each oSheet In ThisComponent.Sheets
     oCurs = oSheet.createCursor()
     oCurs.gotoEndOfUsedArea(True)
     
     oCurs.IsTextWrapped = False
     oCurs.Columns.OptimalWidth = True     
     oCurs.Rows.OptimalHeight = True
  Next oSheet        
End Sub

karolus · August 31, 2025, 10:20am

Hallo @sokol92
why not oCurs.Columns.OptimalWidth = True with emphasis on oCurs ? …

sokol92 · August 31, 2025, 10:25am

Hello, @karolus! Probably not awake yet.
Thanks, fixed it!

Regina · August 31, 2025, 8:52am

Because you have wrap enabled for the cells in column A.

schiavinatto · August 31, 2025, 4:55pm

sub Autofit_GS
rem ----------------------------------------------------------------------
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint" : args1(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "Sel" : args2(0).Value = true
dispatcher.executeDispatch(document, ".uno:GoToEndOfData", "", 0, args2())

rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "WrapText" : args3(0).Value = false
dispatcher.executeDispatch(document, ".uno:WrapText", "", 0, args3())

rem ----------------------------------------------------------------------
dim args11(0) as new com.sun.star.beans.PropertyValue
args11(0).Name = "aExtraWidth" : args11(0).Value = 0
CreateUnoService("com.sun.star.frame.DispatchHelper") _
.executeDispatch(ThisComponent.CurrentController _
.Frame, ".uno:SetOptimalColumnWidth", "", 0, args11())

End Sub