Q:
Oracle Form: export to Microsoft Excel
I have an oracle form,i need the code of a function that exports a given SQL query into Excel sheet on a button click action.
ASKED:
Jun 8 2009 6:22 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
PROCEDURE LOADINVOICEDATA IS
BEGIN
DECLARE
AppID PLS_INTEGER;
ConvID PLS_INTEGER;
RCount Number := 0;
CURSOR c1 IS SELECT col1, col2, col3 from table;
PROCEDURE INSERTROW (invalue VARCHAR2, incol NUMBER) IS
BEGIN
DDE.POKE(ConvID, 'R'||TO_CHAR(RCount)||'C'||TO_CHAR(incol), invalue, DDE.CF_TEXT, 1000);
END INSERTROW;
BEGIN
--:WORKSHEETPATH is a field on the form that contains the path and name of the Excel worksheet
--:EXCELFILEPATH is a field on the form that contains the name of the Excel.exe ex. c:\program files\microsoft office\office\excel.exe
WIN_API_UTILITY.DELETE_FILE(:WORKSHEETPATH,False);
RCount := 1;
AppID := DDE.APP_BEGIN(:EXCELFILEPATH, DDE.APP_MODE_MAXIMIZED);
ConvID := DDE.INITIATE('EXCEL', 'system');
DDE.EXECUTE(ConvID, '[Save.As("' || :WORKSHEETPATH || '",1)]',10000);
ConvID := DDE.INITIATE('EXCEL', :WORKSHEETPATH);
--Insert row for headings
INSERTROW('Col1', 1);
INSERTROW('Col2', 2);
INSERTROW('Col3', 3);
FOR INV IN c1 LOOP
RCount := RCount + 1;
INSERTROW(NVL(TO_CHAR(INV.col1,'mm/dd/yyyy'),' '), 1); --inserting a date. if null, return a space
INSERTROW(NVL(TO_CHAR(INV.col2),' '), 2); --if a numeric column, convert to text and if null, return a space
INSERTROW(NVL(INV.col3,' '), 3); --if text column, just check for null
END LOOP;
END;
EXCEPTION
WHEN DDE.DDE_APP_FAILURE THEN
MESSAGE('Could not start EXCEL (' || :EXCELFILEPATH || ').');
WHEN DDE.DDE_INIT_FAILED THEN
MESSAGE('Could not initialize conversation with ' || :WORKSHEETPATH || '.');
WHEN DDE.DMLERR_NO_CONV_ESTABLISHED THEN
MESSAGE('Could not initialize conversation with ' || :WORKSHEETPATH || '.');
WHEN DDE.DMLERR_SYS_ERROR THEN
MESSAGE('An internal error has occurred.');
WHEN OTHERS THEN
MESSAGE(TO_CHAR(MESSAGE_CODE) || ' ' || MESSAGE_TEXT || TO_CHAR(ERROR_CODE) || ' ' || ERROR_TEXT);
END;
---------------- jjohannsen
I posted on another, similar topic a couple days ago...
I am using the trial version of a commercial product called Excellant to provide this capability. It installs as a storedproc, and takes a cursor or SQL query, along with an XML-based spreadsheet spec and returns a CLOB representing a valid MS Excel file. The cool thing about it is that we can specify styles, formatting, header/footer info, and formulas in our spreadsheet spec and it all comes out in a format native to Excel.
Our business users are stumbling over themselves to provide us more requirements now that they know they can get their data in a format they are familiar with. The trial version is free, and the license fee is relatively cheap for the capabilities we're allowed to exploit. We've looked at other stuff like BIRT and Crystal Reports, but for the simplicity of use, and the cost of ownership, we're probably going to go with Excellant.
Here is their web site: www.peak42solutions.com.
