Oracle Form: Export to Microsoft Excel

5 pts.
Tags:
Excel import/export
Microsoft Excel
Oracle
Oracle Forms
Oracle Forms 10g
Oracle import/export
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Here is code that I call from a button on a form. It uses the DDE built-in package. It also uses the D2KWUTIL library for the WIN_API_UTILITY.DELETE_FILE package.

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 Excellent 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 Excellent.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Hajisab
    Great job
    10 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following