From: Tej <emailtej@...>
Date: 2008-04-06T06:20:34+09:00
Subject: SQL Database to MS Excel sheet.

------=_Part_15453_27433002.1207430432404
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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.

-- 
Light and Love
Tej

------=_Part_15453_27433002.1207430432404--