PACKAGE BODY PACK_EXCEL IS
workbook OLE2.OBJ_TYPE; -- CHANGE
worksheets OLE2.OBJ_TYPE; -- CHANGE
obj_hnd OLE2.OBJ_TYPE; /* store the object handle */
app OLE2.OBJ_TYPE;
-- Captura celula desejada
--------------------------
FUNCTION NumToRange(r INTEGER, c INTEGER) RETURN VARCHAR2 IS
rw VARCHAR2(5);
nm INTEGER;
BEGIN
nm := ASCII('A') - 1;
rw := CHR(nm + c) || TO_CHAR(r);
RETURN UPPER(rw);
END;
-- Inicializa chamada excel
---------------------------
FUNCTION get_object_handle RETURN OLE2.OBJ_TYPE IS
args OLE2.LIST_TYPE; -- CHANGE
BEGIN
/* If the server is not active, activate the server and get the object handle. */
FORMS_OLE.ACTIVATE_SERVER('planilha');
--workbook := FORMS_OLE.GET_INTERFACE_POINTER('planilha'); -- CHANGE
app := OLE2.CREATE_OBJ('Excel.Application');
worksheets := OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets'); -- CHANGE
args := OLE2.CREATE_ARGLIST; -- CHANGE
OLE2.ADD_ARG(args, 1); -- CHANGE
obj_hnd := OLE2.GET_OBJ_PROPERTY(worksheets, 'Item', args); -- CHANGE
RETURN obj_hnd;
END;
-- Captura dados char do Excel
------------------------------
FUNCTION getcharcell(trow NUMBER, col NUMBER) RETURN VARCHAR2 IS
c OLE2.OBJ_TYPE;
d OLE2.OBJ_TYPE;
n VARCHAR2(15);
lst OLE2.LIST_TYPE;
BEGIN
d := GET_OBJECT_HANDLE;
lst := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(lst, NumToRange(trow, col));
c := OLE2.GET_OBJ_PROPERTY(d, 'Range', lst); -- CHANGE
n := OLE2.GET_CHAR_PROPERTY( c, 'Value'); -- GET_NUM_PROPERTY
/* Destroy the argument list. */
OLE2.DESTROY_ARGLIST(lst);
OLE2.RELEASE_OBJ(c);
RETURN n;
EXCEPTION
WHEN OTHERS THEN
ALERTA1('Erro no getcharcell. Entre em contato com analista responsavel. '||SQLERRM);
END;
-- Captura dados numericos do excel
-----------------------------------
FUNCTION getnumcell(trow NUMBER, col NUMBER) RETURN NUMBER IS
c OLE2.OBJ_TYPE;
d OLE2.OBJ_TYPE;
n NUMBER;
lst OLE2.LIST_TYPE;
BEGIN
d := GET_OBJECT_HANDLE;
lst := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG(lst, NumToRange(trow, col));
c := OLE2.GET_OBJ_PROPERTY(d, 'Range', lst); -- CHANGE
n := OLE2.GET_NUM_PROPERTY( c, 'Value'); -- GET_NUM_PROPERTY
/* Destroy the argument list. */
OLE2.DESTROY_ARGLIST(lst);
OLE2.RELEASE_OBJ(c);
RETURN n;
EXCEPTION
WHEN OTHERS THEN
ALERTA1('Erro no getnumcell. Entre em contato com analista responsavel. '||SQLERRM);
END;
Initialize_Container('PLANILHA',:PARAM.FILENAME);
PACKAGE BODY PACK_EXCEL IS
workbook CLIENT_OLE2.OBJ_TYPE; -- CHANGE
worksheets CLIENT_OLE2.OBJ_TYPE; -- CHANGE
obj_hnd CLIENT_OLE2.OBJ_TYPE; /* store the object handle */
app OLE2.OBJ_TYPE;
-- Captura celula desejada
--------------------------
FUNCTION NumToRange(r INTEGER, c INTEGER) RETURN VARCHAR2 IS
rw VARCHAR2(5);
nm INTEGER;
BEGIN
nm := ASCII('A') - 1;
rw := CHR(nm + c) || TO_CHAR(r);
RETURN UPPER(rw);
END;
-- Inicializa chamada excel
---------------------------
FUNCTION get_object_handle RETURN CLIENT_OLE2.OBJ_TYPE IS
args CLIENT_OLE2.LIST_TYPE; -- CHANGE
BEGIN
/* If the server is not active, activate the server and get the object handle. */
--IF NOT FORMS_OLE.SERVER_ACTIVE('planilha') THEN
--FORMS_OLE.ACTIVATE_SERVER('planilha');
--workbook := FORMS_OLE.GET_INTERFACE_POINTER('planilha'); -- CHANGE
app := OLE2.CREATE_OBJ('Excel.Application');
worksheets := CLIENT_OLE2.GET_OBJ_PROPERTY(workbook, 'Worksheets'); -- CHANGE
args := CLIENT_OLE2.CREATE_ARGLIST; -- CHANGE
CLIENT_OLE2.ADD_ARG(args, 1); -- CHANGE
obj_hnd := CLIENT_OLE2.GET_OBJ_PROPERTY(worksheets, 'Item', args); -- CHANGE
RETURN obj_hnd;
-- END IF;
END;
-- Captura dados char do Excel
------------------------------
FUNCTION getcharcell(trow NUMBER, col NUMBER) RETURN VARCHAR2 IS
c CLIENT_OLE2.OBJ_TYPE;
d CLIENT_OLE2.OBJ_TYPE;
n VARCHAR2(15);
lst CLIENT_OLE2.LIST_TYPE;
BEGIN
d := GET_OBJECT_HANDLE;
lst := CLIENT_OLE2.CREATE_ARGLIST;
CLIENT_OLE2.ADD_ARG(lst, NumToRange(trow, col));
c := CLIENT_OLE2.GET_OBJ_PROPERTY(d, 'Range', lst); -- CHANGE
n := CLIENT_OLE2.GET_CHAR_PROPERTY( c, 'Value'); -- GET_NUM_PROPERTY
/* Destroy the argument list. */
CLIENT_OLE2.DESTROY_ARGLIST(lst);
CLIENT_OLE2.RELEASE_OBJ(c);
RETURN n;
EXCEPTION
WHEN OTHERS THEN
ALERTA1('Erro no getcharcell. Entre em contato com analista responsavel. '||SQLERRM);
END;
-- Captura dados numericos do excel
-----------------------------------
FUNCTION getnumcell(trow NUMBER, col NUMBER) RETURN NUMBER IS
c CLIENT_OLE2.OBJ_TYPE;
d CLIENT_OLE2.OBJ_TYPE;
n NUMBER;
lst CLIENT_OLE2.LIST_TYPE;
BEGIN
d := GET_OBJECT_HANDLE;
lst := CLIENT_OLE2.CREATE_ARGLIST;
CLIENT_OLE2.ADD_ARG(lst, NumToRange(trow, col));
c := CLIENT_OLE2.GET_OBJ_PROPERTY(d, 'Range', lst); -- CHANGE
n := CLIENT_OLE2.GET_NUM_PROPERTY( c, 'Value'); -- GET_NUM_PROPERTY
/* Destroy the argument list. */
CLIENT_OLE2.DESTROY_ARGLIST(lst);
CLIENT_OLE2.RELEASE_OBJ(c);
RETURN n;
EXCEPTION
WHEN OTHERS THEN
ALERTA1('Erro no getnumcell. Entre em contato com analista responsavel. '||SQLERRM);
END;
alguém sabe o q posso fazer?