Saturday, February 17, 2007

Simple Output to Excel-Compatible Files

Need to output data to an Excel-compatible file -- but don't need to do any formatting of the resulting worksheet? You could simply write the data to a tab-delimited text file and give it the ".xls" filename extension...

# where recordset is a database recordset,
# array of arrays, etc:
xls = open('worksheet.xls', 'w')
recordset.each do |row|

There are, of course, several alternatives involving CSV, XML, or WIN32OLE. Sometimes I will output the data to a tab-delimited text file, then use WIN32OLE to open the file in Excel, apply formatting, and then save in native Excel format...

xl ='Excel.Application')
wb = xl.Workbooks.Open('worksheet.xls')
# insert formatting code here...
xl.DisplayAlerts = 0
wb.SaveAs('worksheet.xls', EXCEL_FORMAT)
xl.DisplayAlerts = 1

This is often much faster than writing the data directly to a new Excel workbook via WIN32OLE.

No comments: