preload
Nov 28

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
SociBook del.icio.us Digg Facebook Google Yahoo Buzz StumbleUpon
Tagged with: