preload
Date Formatting in Excel using Ruby Highlighting rows in Excel using Ruby
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

Leave a Reply