SQL to XLS utility

A simple but useful utility program that I coded to convert results of SQL queries directly into MS Excel files.

Two prerequisites:

1. gem install spreadsheet-excel (if not already installed)
2. set up a DSN or install appropriate database drivers that Ruby DBI can use

Download code: sql_to_xls.rb.gz

require 'dbi'
require 'spreadsheet/excel'
 
class SqlToXls
	def connect(dsn=nil, username=nil, password=nil)
		@dsn=dsn
		@username=username
		@password=password
 
		disconnect
 
		@con=DBI.connect(@dsn, @username, @password)
	end
 
	def disconnect
		@con.disconnect unless (@con.nil? || !@con.connected?)
	end
 
	def convert(sql, filename)
		begin
			workbook = Spreadsheet::Excel.new(filename)
			worksheet = workbook.add_worksheet("SQL Result")
			@con.execute(sql) do |stmt|
				stmt.column_names.each_with_index do |col_name, index|
					worksheet.write(0, index, col_name)
				end
				stmt.each_with_index do |row, row_index|
					row.each_with_index do |val, col_index|
						worksheet.write(row_index+1, col_index, val.to_s)
					end
				end
			end
			worksheet.close
			worksheet = workbook.add_worksheet("SQL Query")
			worksheet.write(0,0,"SQL used")
			worksheet.write(1,0,sql)
			worksheet.close
		rescue
			puts "ERROR: #{$!} at #{$@}"
		ensure
			workbook.close if workbook
			return workbook			
		end
	end
 
end

Tags: technology:ruby, technology:database, technology:coding Last modified 10:21 Mon, 18 Jun 2007 by AmanKing. Accessed 840 times Children What Links Here Share Except where expressly noted, this work is licensed under a Creative Commons License.