Showing posts from December, 2011

Export to Excel with spreadsheet gem

While using the spreadsheet gem to export data to a Microsoft Excel file I encountered various problems and lack of proper documentation forced me to dig pretty deep. So here are a few tips you might find useful. The gem works with a workbook object and worksheets in this workbook. To get started: workbook = sheet1 = workbook.create_worksheet name: worksheetname To write a row of data as usually is done in reports you can use: sheet1.row(1).replace [ 'col1', 'col2', 'col3'] Rows and Columns are 0 based, so col1 would be printed on A2 (Excel coords). Now to the beauty of it all, formatting, first of all, create a format: bold = :weight => :bold bold20 = :weight => :bold, size: 20 nobold20 = size: 20 bold_grey_bg = pattern_bg_color: :cyan, weight: :bold, pattern: 1, pattern_fg_color: :black Use :weight to set bold,