7.6.1 Column widths and alignment

Column widths and alignment

Let s revisit the spreadsheet we created in Figure 3, using the XLAddData.PRG in the Developer Download files available at www.hentzenwerke.com. The formatting issue that first jumps out at us is the series of cells filled with pound signs, indicating that the column width is too small.

One really cool method is AutoFit, which works on the columns within a given range. In Figure 3, we can AutoFit the column widths for the first three columns. If, however, we ask for the whole range, the title in cell B2, "Alfreds Futterkiste" causes column B to be much wider than necessary, making it hard to read across each row. While the AutoFit method affects all columns in the range, it only calculates the maximum width for those cells actually contained in the calling range. By specifying A5:C14, it calculates the maximum width for columns A, B, and C, but only using those cells in rows 5 through 14. The AutoFit method takes no parameters. Here s an example of using AutoFit:

oSheet.Range("A5:C14").Columns.AutoFit()

As Figure 4 shows, it looks a bit better. While AutoFit generates a column width to fit the longest cell, it s just barely wide enough for the longest cell. You may want to explicitly set a column or two.

Figure 4. AutoFit works wonders on columns. But there s still a lot of formatting left to do!

Set the column width manually through the Columns collection s ColumnWidth property. The units used in the ColumnWidth property are in characters, where one character is equal to the average width of all characters in the default font. The property can be set to fractional characters; in fact, the default column width is 8.43 characters. The following code shows how to add two characters to the width of column B.

WITH oExcel.ActiveSheet.Columns[2]

.ColumnWidth =.ColumnWidth + 2

ENDWITH

Alignment

By default, character data is left-aligned, while numeric and date/time data is right-aligned, and all are aligned at the bottom of the cell. Range s HorizontalAlignment and VerticalAlignment properties store the values for the alignment settings. As with most Office properties, there are a series of constants. Figure 5 shows the constant names, values, and an example cell formatted with the alignment. Both HorizontalAlignment and VerticalAlignment act on the same cell, which allows a large number of possibilities for aligning the text in cells.

Figure 5. Alignment constants. Column A shows the name of the constant, column B shows the value, and column D shows a formatted cell containing the word "Test" (except for the Justify constants, which require a word-wrapped cell to show an effect). Note that the Center Across Selection alignment is centered over three columns.

You ll see that xlHAlignGeneral and xlHAlignLeft look similar; that s because the cell contains text. The General format is the default format, which left-aligns text but right-aligns numbers and dates. xlHAlignLeft and xlHAlignRight force the alignment to the left or right, regardless of the datatype contained in the cell.

Looking again at the TasTrade example, we can center the client name over the first three columns, and then format the column headings using the following code:

#DEFINE xlHAlignCenterAcrossSelection 7

#DEFINE xlHAlignCenter -4108

* Center the client name across the first three columns.

oSheet.Range("A2:C2").HorizontalAlignment = xlHAlignCenterAcrossSelection

* Center each of the column titles.

oSheet.Range("A5:C5").HorizontalAlignment = xlHAlignCenter

If you want to explore using the Justify alignment, the text must wrap within the cell (otherwise, it will simply left-justify the text). Use the WrapText property to set the cell to wrap words:

oSheet.Range("A5").WrapText = .T.

 

Copyright 2000 by Tamar E. Granor and Della Martin All Rights Reserved



Microsoft Office Automation with Visual FoxPro
Microsoft Office Automation with Visual FoxPro
ISBN: 0965509303
EAN: 2147483647
Year: 2000
Pages: 128

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net