Calc Macros: Column Widths and Row Heights

How to Use a Macro to Format an OpenOffice.org Calc Document

Oct 31, 2008

OpenOffice.org Calc cells can soon become hard to read when they have a lot of data in them. This article shows how to optimise column widths and row heights with a macro

OpenOffice.org Calc spreadsheets are designed to hold data in cells; however, these cells are of limited size and data soon becomes crowded - making it hard to read. Obviously the cell sizes can be optimised manually by changing the column widths and row heights - but that can be time consuming and laborious; and that's why it's useful to know that a macro can do this job for the OpenOffice.org Calc user.

Programmatically Entering Calc Data

It's easy to use a macro to load data into a spreadsheet, and the first step is to identify which sheet is to be used:

The variable thisComponent refers to the current document (i.e. the one that's running the macros), and here 'Sheet1' of the current document has been selected; having done that the macro can access individual cells, and those cells can be written to:

cell = sheet.getCellByPosition(0,0) 'Cell A1

cell.String = "This is the Title for the First Column"

cell = sheet.getCellByPosition(1,0) 'Cell B1

cell.String = "This is the Title for the Second Column"

If the spreadsheet is examined at this point then the data will be unreadable, with one cell overwriting another. The next step must be to programmatically optimise the widths (and heights) of the cells containing the data.

Setting the Width of Columns

Or the width of the column can be set to match the contents of any cell with data in them:

With sheet

.Columns(0).OptimalWidth = True

.Columns(1).OptimalWidth = True

End With

Setting the Height of Rows

Sheet.Rows(0).OptimalHeight = True

Sheet.Rows(1).Height = 1000 '10mm High

Hiding Columns and Rows

Occasionally it is useful to hide the data contained in particular columns and rows, and anyone new to OpenOffice.org macros might reasonable expect to do something like:

sheet.Columns(5).Width = 0

Sheet.Rows(5).Height = 0

However, this has no effect on the cell widths or heights; the correct method is to use the invisible property:

Sheet.Columns(5).invisible = False

Sheet.Rows(5).invisible = False

Conclusion

sheet = thisComponent.Sheet(0)

cell = sheet.getCellByPosition(0,0) 'A1

cell.String = "A Macro to Format Spreadsheet Columns and Rows"

cell = sheet.getCellByPosition(1,0) 'B1

cell.String = "A Macro to Format Spreadsheet Columns and Rows"

However, the data soon becomes hard to read - which is not a problem because the columns and rows in the spread sheet have simple properties with which the heights and widths can be changed:

By using these it is easy to programmatically optimise the layout of any OpenOffice.org Calc spreadsheet.

The copyright of the article Calc Macros: Column Widths and Row Heights in Computer Programming is owned by Mark Alexander Bain. Permission to republish Calc Macros: Column Widths and Row Heights in print or online must be granted by the author in writing.