If you're going to automate the generation of an Excel worksheet, you might as well make it look good. There are a near-infinite number of methods that can be called upon to format rows, columns, ranges, and cells. Let's take a look at some of the most common.
To format the first row of a worksheet as bold-font, set the Font.Bold value:
worksheet.Rows(1).Font.Bold = true
To format numbers in a range, set the NumberFormat value.
To format column D as Currency:
worksheet.Columns(4).NumberFormat = "$###,##0.00"
To format column A as a date (mm/dd/yy):
worksheet.Column("A").NumberFormat = "mm/dd/yy"
Alternatively, you can set the Style value:
worksheet.Column("A").Style = "Currency"
worksheet.Column("B").Style = "Percent"
To set the alignment on a range, set its HorizontalAlignment value:
worksheet.Rows(1).HorizontalAlignment = 2 # Left
worksheet.Columns("A:F").HorizontalAlignment = 4 # Right
worksheet.Cells(3, 5).HorizontalAlignment = -4108 # Center
To auto-fit the width of a column, or the height of a row, call its AutoFit method:
To set the width of a column, set its ColumnWidth value:
worksheet.Columns(4).ColumnWidth = 25.0
To set the height of a row, set its RowHeight value:
worksheet.Rows.RowHeight = 15.0
To apply highlighting to a range, set its Interior.ColorIndex value:
worksheet.Rows(10).Interior.ColorIndex = 6 # Yellow
That's all for now, but feel free to post a comment here or send me email with questions, comments, or suggested topics.
Thanks for stopping by!