SQL Database to MS Excel sheet

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.

What goes wrong when you try to run the code?

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs