Once the values are all loaded in, its time to make the spreadsheet look pretty. Here’s how you draw a single, continuous, thin line around each cell in a range of cells. This function can be used an any number of cells…but only in one row. If you give it a range of more than one row, then it will draw a box around the rows, and add the vertical lines. As I am adding one row at a time in my applications, my function works fine for single rows. The ExcelConst:: constants were address in a previous post.
def boxRange(ws, range)
b = range.Borders(ExcelConst::XlEdgeBottom)
b.LineStyle = ExcelConst::XlContinuous
b.Weight = ExcelConst::XlThin
b.ColorIndex = 1
t = range.Borders(ExcelConst::XlEdgeTop)
t.LineStyle = ExcelConst::XlContinuous
t.Weight = ExcelConst::XlThin
t.ColorIndex = 1
l = range.Borders(ExcelConst::XlEdgeLeft)
l.LineStyle = ExcelConst::XlContinuous
l.Weight = ExcelConst::XlThin
l.ColorIndex = 1
r = range.Borders(ExcelConst::XlEdgeRight)
r.LineStyle = ExcelConst::XlContinuous
r.Weight = ExcelConst::XlThin
r.ColorIndex = 1
iv = range.Borders(ExcelConst::XlInsideVertical)
iv.LineStyle = ExcelConst::XlContinuous
iv.Weight = ExcelConst::XlThin
iv.ColorIndex = 1
end
...
boxRange(ws, ws.Range("a3:q3"))
UPDATE!: The version above would only box in one row at the time. At the time it was not a huge issue for me, since my then current project was only working on one row at a time. if you try using the xlInsideHorizontal parameter on just one row, it will error out. Projects since then required multi-row borders.My new version handles single or multi-row ranges:
def boxRange(ws, range)
b = range.Borders(ExcelConst::XlEdgeBottom)
b.LineStyle = ExcelConst::XlContinuous
b.Weight = ExcelConst::XlThin
b.ColorIndex = 1
t = range.Borders(ExcelConst::XlEdgeTop)
t.LineStyle = ExcelConst::XlContinuous
t.Weight = ExcelConst::XlThin
t.ColorIndex = 1
l = range.Borders(ExcelConst::XlEdgeLeft)
l.LineStyle = ExcelConst::XlContinuous
l.Weight = ExcelConst::XlThin
l.ColorIndex = 1
r = range.Borders(ExcelConst::XlEdgeRight)
r.LineStyle = ExcelConst::XlContinuous
r.Weight = ExcelConst::XlThin
r.ColorIndex = 1
iv = range.Borders(ExcelConst::XlInsideVertical)
iv.LineStyle = ExcelConst::XlContinuous
iv.Weight = ExcelConst::XlThin
iv.ColorIndex = 1
### xlInsideHorizontal will barf if the range is only one row!!
if range.Rows.count > 1
ih = range.Borders(ExcelConst::XlInsideHorizontal)
ih.LineStyle = ExcelConst::XlContinuous
ih.Weight = ExcelConst::XlThin
ih.ColorIndex = 1
end
end
