Macros: Text Utilities Part I

It is straightforward to enhance the capabilities of OOo Calc with your home grown functions and features. In this tip, we add the ability to change the case of the text in a range of selected cells.(UPPER CASE, lower case or Proper Case).

As you can in the screenshot below, we have added a new top level menu item TextUtils with 3 new functions, UpperC, LowerC, and ProperC. The last function capitalizes the first character only.

We could have invoked these functions with Tools - Macros etc but from a user's standpoint, it is much more convenient to invoke them the way we have implemented it.

Pretty straightforward - the results of applying UpperC to the selected cells.

First - the source code...

Sub UpperC oDesktop = createUnoService("com.sun.star.frame.Desktop") oDocument = ThisComponent oSelectedCells = oDocument.CurrentSelection oActiveCells = oSelectedCells.RangeAddress oSheets = oDocument.Sheets oSheet = oSheets.getByIndex(oActiveCells.Sheet) ' active table For nRow = oActiveCells.StartRow To oActiveCells.EndRow For nCol = oActiveCells.StartColumn To oActiveCells.EndColumn oCell = oSheet.getCellByPosition(nCol,nRow) CellVal = oCell.getString() oCell.setString(UCase(CellVal)) Next Next End Sub Sub LowerC oDesktop = createUnoService("com.sun.star.frame.Desktop") oDocument = ThisComponent oSelectedCells = oDocument.CurrentSelection oActiveCells = oSelectedCells.RangeAddress oSheets = oDocument.Sheets oSheet = oSheets.getByIndex(oActiveCells.Sheet) ' active table For nRow = oActiveCells.StartRow To oActiveCells.EndRow For nCol = oActiveCells.StartColumn To oActiveCells.EndColumn oCell = oSheet.getCellByPosition(nCol,nRow) CellVal = oCell.getString() oCell.setString(LCase(CellVal)) Next Next End Sub Sub ProperC oDesktop = createUnoService("com.sun.star.frame.Desktop") oDocument = ThisComponent oSelectedCells = oDocument.CurrentSelection oActiveCells = oSelectedCells.RangeAddress oSheets = oDocument.Sheets oSheet = oSheets.getByIndex(oActiveCells.Sheet) ' active table For nRow = oActiveCells.StartRow To oActiveCells.EndRow For nCol = oActiveCells.StartColumn To oActiveCells.EndColumn oCell = oSheet.getCellByPosition(nCol,nRow) CellVal = oCell.getString() oCell.setString(UCase(Left(CellVal,1)) & Right(CellVal,Len(CellVal) - 1)) Next Next End Sub

Creating the new menu entries is simple. Invoke the Menu Customization dialog with Tools - Customize as shown below...

Select the Menus tab. Add a new top level menu item - TextUtils and then a seperate entry for each of our new functions.

Here is the Add Commands dialog. We select the module in the left hand box and the macro subroutine on the right. By default, the menu entry is given the same name as the subroutine but hat can be changed.

In the next installment, we will add a dialog to enhance out new functionality even further.

Дата добавления: 2015-08-03