Oracle COM Automation Excel Charts
 

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%2Fb14310%2Ftoc.htm&remark= portal+%28Windows%29.

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\meadowlandsfl ow';
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

Oracle COM Automation Excel Charts
 

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

"scada" wrote in message
...
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_t...310%2Ftoc.htm&
remark=portal+%28Windows%29.

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\meadowlandsfl ow';
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$36
5','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

Oracle COM Automation Excel Charts
 

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"scada" wrote in message
...
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%2Fb14310%2Ftoc.htm&remark= portal+%28Windows%29.

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\meadowlandsfl ow';
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