# SQL Database to MS Excel sheet

**URL:** <https://www.ruby-forum.com/t/sql-database-to-ms-excel-sheet/133977>\
**Category:** Ruby\
**Created:** [April 5, 2008, 11:21pm UTC](https://www.ruby-forum.com/t/sql-database-to-ms-excel-sheet/133977 "2008-04-05T23:21:08Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tej](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/48db29/32.png) [@Tej](https://www.ruby-forum.com/u/Tej)\
**Post date:** [April 5, 2008, 11:21pm UTC](https://www.ruby-forum.com/t/sql-database-to-ms-excel-sheet/133977/1 "2008-04-05T23:21:08Z")

</div>

Hey,  
Well I have been trying to query from a SQL server directly to a Excel  
spreadsheet where i can configure Ruby to directly give me a Chart as an  
output.But, am unable to do so.

Can any one let me know of a way by which i can directly get data into  
an  
open workbook || worksheet, and generate charts.

I have been trying the following coe, but it doesn’t help much.

The Code:

require ‘win32ole’  
require ‘oci8’  
require ‘ftools’

# -4100 is the value for the Excel constant xl3DColumn.

ChartTypeVal = -4100;  
EXCEL\_FORMAT= -4143

# Creates OLE object to Excel

# Create and rotate the chart

conn = OCI8.new(‘scott’, ‘tiger’)  
cursor = conn.exec(‘SELECT OMR FROM tworkorder’)

excel = WIN32OLE.new(“excel.application”)  
excel.DisplayAlerts = FALSE;  
excel[‘Visible’] = TRUE;  
workbook = excel.Workbooks.open(‘worksheet.xls’)  
#workbook.Activate  
xls=open(‘Book1.xls’,‘w’)  
while r= cursor.fetch()  
xls.puts(r.join("\t"))  
#excel.Range(“a1:a5”).Value=r.join(’,’)

end  
xls.close  
#excel.Range(“A2”).Value = r.join("\t")  
cursor.close  
conn.logoff  
workbook.SaveAs ‘worksheet.xls’  
File.copy(“Book1.xls” “worksheet.xls”)

excel.Range(“a1:a5”).Select();  
excelchart= workbook.Charts.Add();  
excelchart[‘Type’] = ChartTypeVal;  
30.step(180, 10) do |rot|  
excelchart[‘Rotation’] = rot  
end

excelchart2 = workbook.Charts.Add();  
excelchart3 = workbook.Charts.Add();

charts = workbook.Charts  
charts.each { |i| puts i }  
excel.ActiveWorkbook.Close(5);  
excel.Quit();

Would be Glad if you could help me out here.

---

<div class="post-metadata">

**Author:** ![Tej](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/48db29/32.png) [@Tej](https://www.ruby-forum.com/u/Tej)\
**Post date:** [April 6, 2008, 10:28pm UTC](https://www.ruby-forum.com/t/sql-database-to-ms-excel-sheet/133977/2 "2008-04-06T22:28:08Z")

</div>

What goes wrong when you try to run the code?