require "win32ole" require 'pathname' require 'mysql2' excel = WIN32OLE.new('excel.application') excel.visible = true # in case you want to see what happens client = Mysql2::Client.new(:host => "localhost", :username => "root", :password => "pwd", :port =>"3306", :database => "mop6" ) #获取win下当前执行脚本的路径 $pwd = Pathname.new(__FILE__).realpath.dirname.to_s.gsub('/','\\') workbook = excel.workbooks.add workbook.saveas("#{$pwd}\\sqlresult.xlsx") workSheet = workbook.Worksheets(1) sql = "SELECT * FROM epg_media_publish_epgs_66_test2utv limit 3;" results = client.query(sql) columnName = ['idx','column_id','media_id','sort','status','operator_id','order_date','pub_date'] #设置a1:h1的颜色 workSheet.Range('a1:h1').Interior.ColorIndex = 36 workSheet.Range("a1:h1").Value = columnName num = 2 results.each(:as => :array) do |row| workSheet.Range("a#{num}:h#{num}").Value = row num += 1 end workbook.save workbook.close excel.Quit
------------------------------------------------------------------------------
excel数据效果:
将mysql数据输出到表格:
require 'mysql2' require 'terminal-table' client = Mysql2::Client.new(:host => "23.236.xxx.xxx", :username => "root", :password => "xxxxxx", :port =>"3306", :database => "mop6" ) sql = "SELECT * FROM epg_media_publish_epgs_66_utvmo limit 3"; results = client.query(sql) table = Terminal::Table.new :headings => ['idx','column_id','sort','status','operator_id','order_date','pub_date','media_id',] do |t| results.each do |row| t << row.values end end puts table
执行效果: