如何查询MS Access数据库表,并使用Ruby和win32ole将信息导出到Excel? [英] How do I query a MS Access database table, and export the information to Excel using Ruby and win32ole?
本文介绍了如何查询MS Access数据库表,并使用Ruby和win32ole将信息导出到Excel?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我是Ruby的新手,我正在尝试查询现有的MS Access数据库以获取报告信息.我希望此信息存储在Excel文件中.我该怎么办?
I'm new to Ruby, and I'm trying to query an existing MS Access database for information for a report. I want this information stored in an Excel file. How would I do this?
推荐答案
尝试以下方法之一:
OLE:
require 'win32ole'
class AccessDbExample
@ado_db = nil
# Setup the DB connections
def initialize filename
@ado_db = WIN32OLE.new('ADODB.Connection')
@ado_db['Provider'] = "Microsoft.Jet.OLEDB.4.0"
@ado_db.Open(filename)
rescue Exception => e
puts "ADO failed to connect"
puts e
end
def table_to_csv table
sql = "SELECT * FROM #{table};"
results = WIN32OLE.new('ADODB.Recordset')
results.Open(sql, @ado_db)
File.open("#{table}.csv", 'w') do |file|
fields = []
results.Fields.each{|f| fields << f.Name}
file.puts fields.join(',')
results.GetRows.transpose.each do |row|
file.puts row.join(',')
end
end unless results.EOF
self
end
def cleanup
@ado_db.Close unless @ado_db.nil?
end
end
AccessDbExample.new('test.mdb').table_to_csv('colors').cleanup
ODBC:
require 'odbc'
include ODBC
class AccessDbExample
@obdc_db = nil
# Setup the DB connections
def initialize filename
drv = Driver.new
drv.name = 'AccessOdbcDriver'
drv.attrs['driver'] = 'Microsoft Access Driver (*.mdb)'
drv.attrs['dbq'] = filename
@odbc_db = Database.new.drvconnect(drv)
rescue
puts "ODBC failed to connect"
end
def table_to_csv table
sql = "SELECT * FROM #{table};"
result = @odbc_db.run(sql)
return nil if result == -1
File.open("#{table}.csv", 'w') do |file|
header_row = result.columns(true).map{|c| c.name}.join(',')
file.puts header_row
result.fetch_all.each do |row|
file.puts row.join(',')
end
end
self
end
def cleanup
@odbc_db.disconnect unless @odbc_db.nil?
end
end
AccessDbExample.new('test.mdb').table_to_csv('colors').cleanup
这篇关于如何查询MS Access数据库表,并使用Ruby和win32ole将信息导出到Excel?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文