ADF Desktop Integration – showing table data in Excel workbook

This post explains how to to display table data in the Excel sheet using ADF Desktop Integration.

We’ll be discussing below items

  • Installing ADF- DI extension
  • Creating ADF Application
  • Working with Excel workbook

Installing ADF- DI extension:

Prerequisites: Before proceeding with ADF-DI extension installation first your system should have below soft wares installed, otherwise you end up getting an error when you try to install ADF-DI extension.

  • Microsoft .NET Framework 4
  • Microsoft Visual Studio 2010 Tools for Office Runtime
  • MS Office [I’ve used 2013 version]

Now go to JDeveloper -> ‘Tools’ -> Install ADF Desktop Integration.

Creating ADF Application:

  • Create Fusion Web Application (ADF). Application Name: ADFApp2, project names (keep default as model and viewcontroller)
  • Right click on Model project and create ‘Business Components from Tables’, crete DB connection and select ‘Departments’ table. Drag this table to right pane for updatableVO.
  • Right click on ViewController project and create JSPX page (dept.jspx)
  • Drag and drop the Deaprtments datacontrols from datacontrol section on to the dept.jspx page as table. (which will creates deptPagDef.xml file and along with tree binding in it)
  • Right click on viewcontroller project and create Microsoft Excel Workbook from client tier. (give Name: dept-DT)

6

7

8

Working with Excel workbook:

  • Double click on the excel file and it’ll prompt to select a page definition file from the available all page definition files in the project. Select view_deptPageDef file and click OK.

9

  • You can see a tree binding (DepartmentsView1) under Binidngs section of the Excel workbook.

10

  • Drag and drop the tree binding onto the cell and select ‘ADF Read-only Table’ -> OK.

11

  • It’ll open workbook properties window, there just click on OK. (we’ll configure it later)

12

  • Now you can see all the tree bindings in the Excel file.

13

  • in Jdev Right click on ViewController project -> project properties -> Java EE Application -> set ‘Java EE WebContext Root’ as ‘departments’ -> ok.

14

15

  • In excel work book click on worksheet properties and configure there to display data.

Worksheet properties -> Events […] -> Add -> StartUp -> ActionSet -> Actions […] -> Add button (Component Action) -> select Download -> OK -> OK -> OK.

16

17

18

19

20

21

  • In Jdev Right click on dept.jspx page and click on Run.
  • In excel workbook under oracle ADF menu tab, click on Run.

22

  • You can see the table data in the excel sheet as below. After review click on Stop and close the excel.

23

Hope You enjoyed the post!

In future will come up with more posts related to ADF-DI ‘update records in excel and upload to DB’ , ‘WebService Integration’ etc.

Advertisements

Disable few items in Select One Choice LOV in Oracle ADF

This post covers how to disable certain items in selectOneChoice LOV based on certain condition.

Existing code:

<af:selectOneChoice value="#{bindings.DepartmentId.inputValue}" 
label="#{bindings.DepartmentId.label}"
required="#{bindings.DepartmentId.hints.mandatory}"
shortDesc="#{bindings.DepartmentId.hints.tooltip}" id="soc1">
<f:selectItems value="#{bindings.DepartmentId.items}" id="si1"/>
</af:selectOneChoice>

Replace with:

<af:selectOneChoice value="#{bindings.DepartmentId.inputValue}" 
label="#{bindings.DepartmentId.label}"
required="#{bindings.DepartmentId.hints.mandatory}"
shortDesc="#{bindings.DepartmentId.hints.tooltip}" id="soc1">
<af:forEach items="#{bindings.DepartmentsView1.rangeSet}" var="list">
<af:selectItem label="#{list.DepartmentName}" id="si1" value="#{list.DepartmentId}"
disabled="#{list.DepartmentId eq 10 or list.DepartmentId eq 20 or list.DepartmentId eq 30}"/>
</af:forEach>
</af:selectOneChoice>

 

Reference:

https://mjabr.wordpress.com/2012/01/23/how-to-make-some-lov-items-non-selectable/

 

 

Using Partial Triggers in Oracle Applications Cloud, Oracle Fusion Applications

This post explains how can we refresh a component when another component value is changed. You must be already aware that this can be achieved easily in ADF code by (a) setting the partial Triggers property of target component to the ID of source component & (b) setting the autoSubmit property of source component to ‘true‘.

But in Oracle Applications Cloud, Oracle Fusion Applications we won’t be having access to the source code and we need to implement it from the browser itself using customization feature.

partial triggers property is available for customization, but we need to find the ID of the source component which is not displayed anywhere to us. So to get the ID of the source component click on F12 (browser Inspect Element) then navigate to the source component and click on it. You can find the ID of that component over there.

What ever discussed above, let’s see step by step.

  • Login to Oracle Applications Cloud

1

  • You’ll be taken to the home page.

2

  • Select the sandbox if not selected any from the ‘Manage Sandboxes’ link (you can create a new sandbox also and its mandatory to select one sandbox before we proceed doing the customization to the application)

3

  • After selecting sandbox, click on ‘Set as Active’

4

  • Now navigate to the page where you wanted to do customization (eg: like in Order Management -> Orders on Hold page). Here let’s take a requirement that on selecting some value in ‘Hold Name’ LOV, the ‘Release comments’ component should display some custom message.

5

  • Now click on Inspect element (F12) and click on source attribute to find the ID of it. Copy the ID  [if source and destination components present inside same parent component then the last part if ID will be sufficient].

6

  • Click on Customize Pages link which you can get from user drop down.

7

  • Go to the Structure tab  and click on required component to customize

8

Note: If component is present inside a popup then click on button which brings the popup and in the component structure scroll down little where you can see multiple popups. Click on each popup and edit and read the ‘popup fetch listener’ property to understand why that popup is being used and check it is the correct one that we need to edit. After you find required popup if the content inside that is not visible, then set the ‘Content Delivery’ property from ‘lazyUncached’ to ‘Immediate’, then the content inside popup will be available to edit.

  • Click on source component -> If any popup comes select Edit -> Click Edit icon or right click on component at source code and click on Edit -> Set the ‘autoSubmit’ property to ‘true’

9

  • Click on Destination component and click on Edit and set the ‘partialTriggers’ to the ID which we copied earlier. [don’t set the entire path of ID, if source and destination components present inside same parent component then the last part if ID will be sufficient]. For the Value property set the EL logic as [#{bindings.HoldNameHeader.inputValue eq ‘CUSTOM HOLD’? ‘This is a custom Hold.’: bindings.HoldReleaseComments.inputValue] which means when ever ‘CUSTOM HOLD’ is selected in the LOV then below target component shows the message as ‘This is a custom Hold.’, and whenever other value is selected it won’t show any message and user can enter text over there. 

10

  • Now close the customization and you are ready to test. Select ‘CUSTOM HOLD’ from Hold Name LOV, then Release Comments shows the message as ‘This is a custom Hold‘. If you select other value then input text will be empty and editable. Now exit from the sandbox.

11

Hope you enjoyed the post 🙂

 

Working with blob data in Java

In this post let’s take a requirement that we read a pdf file from java code and store it into database and then from another java class we read that blob data from database and store it as a pdf file.

A little introduction about how java handles io streams: In Java it has two variety of streams to handle io, one for binary data(image, pdf, video, audio) and another for text data.  When we are handling with binary data then we should use binary stream classes to handle them (the class names which ends with Stream are used to handle binary data).

Classes to use when working with binary data: InputStream, OutputStream, BufferedInputStream, BufferedOutputStream, FileInputStream, FileOutputStream.

Classes to use when working with text data & inputs from keyboard: InputStreamReader, OutputStreamWriter, BufferedReader, BufferedWriter, FileReader, FileWriter.

1. Create table with blob column:

create table emps(empId int primary key,empFile blob);

2. Insert data into table (blob column) [Here inserting is done using java code by reading data from a file]

package upload;
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Pdf_insert{
static String url = "jdbc:oracle:thin:@localhost:1521:XE";
static String username = "HR";
static String password = "hr";
public static void main(String[] args) throws Exception{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(url, username, password);
String sql = "insert into emps (empid,empfile) values (?,?)";
//String sql = "update emps set empfile = ? where empid = 100";
File blob = new File("D:\\small.pdf");
FileInputStream in = new FileInputStream(blob);
//InputStream in  = new ByteArrayInputStream(blob.getBytes()); // if blob is a string not a file (eg: blob = "hexdata");
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setInt(1, 100);
stmt.setBinaryStream(2, in, (int)blob.length());
stmt.executeUpdate();
in.close();
conn.close();
}}

3. Read data from table (blob column) and generate pdf file from that data.

package upload;

import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;

import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Pdf_read
{
 static String url = "jdbc:oracle:thin:@punitp406381d:1521:XE";
 static String username = "HR";
 static String password = "hr";
 static int BUFFER_SIZE = 4096;

 public static void main(String[] args) throws Exception
 {
 Class.forName("oracle.jdbc.driver.OracleDriver");
 Connection conn = DriverManager.getConnection(url, username, password);
 String sql = "SELECT empfile FROM emps where empId = '100'";
 PreparedStatement stmt = conn.prepareStatement(sql);
 ResultSet resultSet = stmt.executeQuery();

 if (resultSet.next()) {
 System.out.println("if");
 Blob blob = resultSet.getBlob("empfile");
 InputStream inputStream = blob.getBinaryStream();
 OutputStream outputStream = new FileOutputStream("D:\\ourFile.pdf");

 int bytesRead = -1;
 byte[] buffer = new byte[BUFFER_SIZE];
 while ((bytesRead = inputStream.read(buffer)) != -1) {
 outputStream.write(buffer, 0, bytesRead);
 }
 inputStream.close();
 outputStream.close();
 System.out.println("File is saved");
 }
 conn.close();
 }
}

 

Let’s have a look at few more examples.

Below code used when you have a hex string and you need to generate a file from it.


package upload;

import java.io.FileOutputStream;

public class pdf_insert
{
static String hex = "255044462D312E32200D0A25E2E3CFD30D0A200D0A392030206F626A0D0A3C3C0D0A2F4C656E677468203130203020520D0A2F46696C746572202F466C6174654465636F6465200D0A3E3E0D0A73747265616D0D0A4889CD90D14AC33014869F20EFF07BA7B266E7244D93ED6ED216062D148D82901B115B266A658AE2DBDBA40CF17E822417C9C9FF7DC9C98517AC496A05ABACCC73C0978210C77E8058D6042D5D2CF78224591B97F7D329FC274EAB7508AD31219C84C05619D93F9FC13F0A9E62315262629CD289C948B25BB99954443A239E26D8ADD9CC58AEA6F80F69E7BB24716E66AC83BF1B8605B65DBBC0F5CBEEE361FFB67BFFC2D8A3899BD7717C4AAA4C73115D9951D21449B8AC196AEE8425AF8C39D8E960DFE0A6BABCC555BB691A74658D7ADB249B92DA16BF1A4F6542C6C492D2AFE1B852E5FE407ADC97FAF3FF67AABCF8063CF5A1AA0D0A656E6473747265616D0D0A656E646F626A0D0A31302030206F626A0D0A3234360D0A656E646F626A0D0A342030206F626A0D0A3C3C0D0A2F54797065202F506167650D0A2F506172656E742035203020520D0A2F5265736F7572636573203C3C0D0A2F466F6E74203C3C0D0A2F4630203620302052200D0A2F4631203720302052200D0A3E3E0D0A2F50726F635365742032203020520D0A3E3E0D0A2F436F6E74656E74732039203020520D0A3E3E0D0A656E646F626A0D0A362030206F626A0D0A3C3C0D0A2F54797065202F466F6E740D0A2F53756274797065202F54727565547970650D0A2F4E616D65202F46300D0A2F42617365466F6E74202F417269616C0D0A2F456E636F64696E67202F57696E416E7369456E636F64696E670D0A3E3E0D0A656E646F626A0D0A372030206F626A0D0A3C3C0D0A2F54797065202F466F6E740D0A2F53756274797065202F54727565547970650D0A2F4E616D65202F46310D0A2F42617365466F6E74202F426F6F6B416E74697175612C426F6C640D0A2F4669727374436861722033310D0A2F4C61737443686172203235350D0A2F576964746873205B20373530203235302032373820343032203630362035303020383839203833332032323720333333203333332034343420363036203235302033333320323530200D0A323936203530302035303020353030203530302035303020353030203530302035303020353030203530302032353020323530203630362036303620363036200D0A34343420373437203737382036363720373232203833332036313120353536203833332038333320333839203338392037373820363131203130303020383333200D0A38333320363131203833332037323220363131203636372037373820373738203130303020363637203636372036363720333333203630362033333320363036200D0A353030203333332035303020363131203434342036313120353030203338392035353620363131203333332033333320363131203333332038383920363131200D0A353536203631312036313120333839203434342033333320363131203535362038333320353030203535362035303020333130203630362033313020363036200D0A373530203530302037353020333333203530302035303020313030302035303020353030203333332031303030203631312033383920313030302037353020373530200D0A37353020373530203237382032373820353030203530302036303620353030203130303020333333203939382034343420333839203833332037353020373530200D0A363637203235302032373820353030203530302036303620353030203630362035303020333333203734372034333820353030203630362033333320373437200D0A353030203430302035343920333631203336312033333320353736203634312032353020333333203336312034383820353030203838392038393020383839200D0A34343420373738203737382037373820373738203737382037373820313030302037323220363131203631312036313120363131203338392033383920333839200D0A333839203833332038333320383333203833332038333320383333203833332036303620383333203737382037373820373738203737382036363720363131200D0A363131203530302035303020353030203530302035303020353030203737382034343420353030203530302035303020353030203333332033333320333333200D0A333333203535362036313120353536203535362035353620353536203535362035343920353536203631312036313120363131203631312035353620363131200D0A353536205D0D0A2F456E636F64696E67202F57696E416E7369456E636F64696E670D0A2F466F6E7444657363726970746F722038203020520D0A3E3E0D0A656E646F626A0D0A382030206F626A0D0A3C3C0D0A2F54797065202F466F6E7444657363726970746F720D0A2F466F6E744E616D65202F426F6F6B416E74697175612C426F6C640D0A2F466C6167732031363431380D0A2F466F6E7442426F78205B202D323530202D323630203132333620393330205D0D0A2F4D697373696E675769647468203735300D0A2F5374656D56203134360D0A2F5374656D48203134360D0A2F4974616C6963416E676C6520300D0A2F436170486569676874203933300D0A2F58486569676874203635310D0A2F417363656E74203933300D0A2F44657363656E74203236300D0A2F4C656164696E67203231300D0A2F4D6178576964746820313033300D0A2F4176675769647468203436300D0A3E3E0D0A656E646F626A0D0A322030206F626A0D0A5B202F504446202F5465787420205D0D0A656E646F626A0D0A352030206F626A0D0A3C3C0D0A2F4B696473205B3420302052205D0D0A2F436F756E7420310D0A2F54797065202F50616765730D0A2F4D65646961426F78205B203020302036313220373932205D0D0A3E3E0D0A656E646F626A0D0A312030206F626A0D0A3C3C0D0A2F43726561746F722028313732352E666D290D0A2F4372656174696F6E446174652028312D4A616E2D332031383A3135504D290D0A2F5469746C652028313732352E504446290D0A2F417574686F722028556E6B6E6F776E290D0A2F50726F647563657220284163726F6261742050444657726974657220332E303220666F722057696E646F7773290D0A2F4B6579776F7264732028290D0A2F5375626A6563742028290D0A3E3E0D0A656E646F626A0D0A332030206F626A0D0A3C3C0D0A2F50616765732035203020520D0A2F54797065202F436174616C6F670D0A2F44656661756C7447726179203131203020520D0A2F44656661756C7452474220203132203020520D0A3E3E0D0A656E646F626A0D0A31312030206F626A0D0A5B2F43616C477261790D0A3C3C0D0A2F5768697465506F696E74205B302E39353035203120312E30383931205D0D0A2F47616D6D6120302E32343638200D0A3E3E0D0A5D0D0A656E646F626A0D0A31322030206F626A0D0A5B2F43616C5247420D0A3C3C0D0A2F5768697465506F696E74205B302E39353035203120312E30383931205D0D0A2F47616D6D61205B302E3234363820302E3234363820302E32343638205D0D0A2F4D6174726978205B302E3433363120302E3232323520302E3031333920302E3338353120302E3731363920302E3039373120302E3134333120302E3036303620302E37313431205D0D0A3E3E0D0A5D0D0A656E646F626A0D0A787265660D0A302031330D0A3030303030303030303020363535333520660D0A30303030303032313732203030303030206E0D0A30303030303032303436203030303030206E0D0A30303030303032333633203030303030206E0D0A30303030303030333735203030303030206E0D0A30303030303032303830203030303030206E0D0A30303030303030353138203030303030206E0D0A30303030303030363333203030303030206E0D0A30303030303031373630203030303030206E0D0A30303030303030303231203030303030206E0D0A30303030303030333532203030303030206E0D0A30303030303032343630203030303030206E0D0A30303030303032353438203030303030206E0D0A747261696C65720D0A3C3C0D0A2F53697A652031330D0A2F526F6F742033203020520D0A2F496E666F2031203020520D0A2F4944205B3C34373134393531303433336464343838326630356638633132343232333733343E3C34373134393531303433336464343838326630356638633132343232333733343E5D0D0A3E3E0D0A7374617274787265660D0A323732360D0A2525454F460D0A";
public static void main(String[] args) throws Exception
{
String str = "";
for(int i=0;i<hex.length();i+=2)
{
String s = hex.substring(i, (i + 2));
int decimal = Integer.parseInt(s, 16);
str = str + (char) decimal;
}
System.out.println("str:"+str);
FileOutputStream fos = new FileOutputStream("D:\\sampleFile.pdf");
int i = 0;
while (i < str.length()) {
fos.write(str.charAt(i));
i++;
}
fos.close();
}
}

For this example reference:

https://stackoverflow.com/questions/13990941/how-to-convert-hex-string-to-java-string

Other References:

http://www.codejava.net/java-se/file-io/how-to-read-and-write-binary-files-in-java

 

Note: For online conversion of hex to binary, vice versa you can check below site

http://tomeko.net/online_tools/hex_to_file.php?lang=en

Hope you enjoyed the post!