From: Bob Gustafson <bobgus@...>
Date: 2008-04-07T05:27:30+09:00
Subject: Re: SQL Database to MS Excel sheet.

What goes wrong when you try to run the code?

On Sun, 2008-04-06 at 06:20 +0900, Tej wrote:
> 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.
>