Oracle COM Automation Excel Charts

[Guest]

Hi All

I read and manged to get all the COM Demos working from the COM Automation
Feature Developer's Guide
<http://www.oracle.com/pls/db102/to_toc?pathname=win.102/b14310/toc.htm&remark=portal+(Windows)>.

I have changed the Excel Demo to match what I want to use in my Database.
Everything works great except the Excel Chart portion. I what to have Chart
Type line but when I use xlLine, the chart still produces a bar chart.

Does anyone know how to produce a Line Chart in Excel using Oracle COM
Automation?

I am using Windows Server 2003, Office Standard Edition 2003, and Oracle 10.2

My modified sql is

<pre>


set serveroutput on;
declare

CURSOR c1 IS
SELECT time, flow
FROM RT_FLOW_TEST
WHERE time > to_date('31-dec-2006','dd-mon-yyyy')
ORDER BY time;

error_message varchar2(1200);
n binary_integer:=2;
i binary_integer;
filename varchar2(255);
cellIndex varchar2(40);
cellValue varchar2(40);
cellColumn varchar2(10);
returnedTime varchar2(20);
currencyvalue double precision;
datevalue DATE;


looptext varchar2(20);

error_src varchar2(255);
error_description varchar2(255);
error_helpfile varchar2(255);
error_helpID binary_integer;

begin
filename:='c:\reports\2007\overnight\meadowlandsflow';
i:=ORDExcel.CreateExcelWorkSheet('');
i:=ORDExcel.InsertData('A1', 'Day', 'BSTR');
i:=ORDExcel.InsertData('B1', 'Flow', 'BSTR');


For c1_rec IN c1 LOOP

cellColumn:=TO_CHAR(n);

cellIndex:=CONCAT('A',cellColumn);
dateValue:=c1_rec.time;
i:=ORDExcel.InsertData(cellIndex, dateValue, 'DATE');


cellIndex:=CONCAT('B',cellColumn);
cellValue:=c1_rec.flow;
i:=ORDExcel.InsertData(cellIndex, cellValue, 'BSTR');



n:=n+1;
END LOOP
i:=ORDExcel.InsertChart(100,10,400,400,'Sheet2!$B$2:$B$365,Sheet2!$A$2:$A$365','xlLine');

select TO_CHAR(SYSDATE, 'DD-MON-YYYY') into returnedTime from dual;
filename:=CONCAT(filename, returnedTime);

i:=ORDExcel.SaveExcelFile(filename);
i:=ORDExcel.ExitExcel();
end;
/
</pre>

Any help is much appreciated.

Cheers

Brad

 

[Peter T]

Type line but when I use xlLine, the chart still produces a bar chart.

I haven't followed the link but if you are using late binding try replacing
xlLine with its constant value 4.

Regards,
Peter T

 

[Jon Peltier]

but when I use xlLine, the chart still produces a bar chart

What did you replace with xlLine? Was it xlColumn, or something else?

xlLine is a named VBA constant from Excel's object library, with a value (as
Peter pointed out) of 4. According to the page you linked, the type
parameter of the InsertChart function in the Oracle COM utility must be of
type VARCHAR2, whatever that is.

- Jon