Export data from Excel to Table using custom Web ADI Integrator

Case: User needs a function XXFunc in responsiblity XXResp which creates Excel document and allows user to upload data from Excel sheet to custom Table.

Pre-Req

For Microsoft Excel 2002 or 2003:
1.From the Tools menu, select Macro, then Security, and then Security Level.
2.In the Security Level tab, select the High option.
3.In the Trusted Sources or Trusted Publishers tab, select the Trust access to Visual Basic Project option.

For Microsoft Excel 2007:
1.Choose the Microsoft Office button, and then choose Excel Options. In the Trust Center category, choose Trust Center Settings, and then the Macro Settings category.
2.Select the Disable all macros except digitally signed macros option.

3.Select the Trust access to the VBA project object model option.

Set profile option 'BNE Allow No Security Rule' to Yes at user level. The default value is No.

Add responsibilities 'Desktop Integration Manager', 'Desktop Integrator' to the current user.

Steps

1.Create custom table 'XX_ONT_WEBADI_TEST'. Create custom API 'XX_ONT_WEBADI_TEST_PKG' which validates and inserts data into custom table.
2.Create Integrator 'XX ONT ADI Test Integrator'.
3.Create Interface 'XX ONT ADI Test Interface' to the above integrator and set custom API 'XX_ONT_WEBADI_TEST_PKG' to the interface.
4.Create Contents(Optional).
5.Create Layout 'XX ONT ADI Test Layout' and set to integrator 'XX ONT ADI Test Integrator'.
6.You can test at this stage from Desktop Integrator responsiblity.

7.Create Function 'XX ONT ADI Test Func' and set integrator to the function. Attach this function to the menu under resposibility.

8.Navigate to Resposibility->Menu->XX ONT ADI Test Func. Opens OA Page. Click on Create Document button opens Excel Sheet.

 

Detailed Steps

1.Create custom table 'XX_ONT_WEBADI_TEST'. Create custom API 'XX_ONT_WEBADI_TEST_PKG' which validates and inserts data into custom table. Paremeters in procedure Load_Prc() should be the columns in the table which you want to update using Web ADI integrator. 

2.Create Integrator 'XX ONT ADI Test Integrator'.

Navigation: Desktop Integration Manager(R)->Create Integrator
Enter name, internal name and application.

Must select 'Display In Create Document Page' checkbox which allows current integrator available for creating document(Excel). Click Next.

3.Create Interface 'XX ONT ADI Test Interface' to the above integrator and set custom API 'XX_ONT_WEBADI_TEST_PKG' to the interface. Click Apply.

In next screen, click Select radio button of interface 'XX ONT ADI Test Interface' and wait. This reads API given in the interface and loads procedure parameters as attributes. Click Next.

4.Create Contents(Optional). Click Submit.

5.Create Layout 'XX ONT ADI Test Layout' and set to integrator 'XX ONT ADI Test Integrator'.

Navigation: Desktop Integrator(R)->Define Layout

Select integrator 'XX ONT ADI Test Integrator' from dropdown. Click Go.

This screen shows exisitng layouts if there are any. Click on Create button to create one.

Provide layout name as 'XX ONT ADI Test Layout' and select no.of headers 1. Click Next.

This picks integrator attributes as layout fields. Select Placement as 'Line' and provide default values if there are any required. Click Apply.

Layout created successfully.

6.Test from Desktop Integrator responsiblity.

Navigation: Desktop Integrator(R)->Create Document

Select integrator as XX ONT ADI Test Integrator. Click Next.

In Review stage, Click Create Document button. This opens popup window to Open/Save a file WebADI.xls. Click Open button.

This opens Excel sheet with format given in layout 'XX ONT ADI Test Layout'. Close Download OA window.

Enter Data into excel sheet. To upload data into table, navigate to Add-Ins(M)->Oracle->Upload from excel sheet menu on top. This opens Upload window. Click on Upload button. Once data is successully uploaded, excel sheet shows green icon for each line indicates no errors in upload.

Check same from backend.

7.Create Function 'XX ONT ADI Test Func' and set integrator to the function. Attach this function to the menu under resposibility.

Navigation: Application Developer(R)->Application->Function

Create function as shown in below screens.

In the next screen you need to set form parameter. Modify below string and set correct integrator, layout and content details.

bne:page=BneCreateDoc&bne:language=US&bne:reporting=N&bne:viewer=BNE:EXCEL2000,2003,2007&bne:integrator=XXONT:XXGF_TEST1_INTG_INTG&bne:layout=XXONT:XXGF_TEST1_INTG_DFLT&bne:content=XXONT:XXGF_TEST1_INTG_CNT

Add function 'XX ONT ADI Test Func' to menu. Prompt given as 'ADI Test Load'.

8.Navigate to Resposibility->Menu->ADI Test Load. This opens OA page. Click on Create Document button to open new excel sheet with layout.