File encryption with GPG method for EBS

Most of the company are exchanging PII (personal identification information) data (it could be routing number, credit card number, employee information etc) so to exchange files security we have to do 2 things:

Use gpg (or pgp) encrypt the file.

Use sftp send the file to the target system.

We will discuss more on how to encrypt file using gpg software.

Create encrypted file:

Source system: Generate file e.g.: CTX

Target system: Receive the file e.g.: Bank

a. First step is to create key

Go to your application server and run below syntax from the command line: 

————————————————

gpg --gen-key

————————————————

Note: Check with your admin if gpg software has been installed at your environment else they might need before we further go through the following steps.

A series of prompts directs you through the process. Press the Enter key to assign a default value if desired

——————————————————————————————————

Please select what kind of key you want:

   (1) DSA and Elgamal (default)

   (2) DSA (sign only)

   (5) RSA (sign only)

Your selection? 

——————————————————————————————————

Choose the key size, by default it would be 2048.

——————————————————————————————————

RSA keys may be between 1024 and 4096 bits long.

What keysize do you want? (2048) 

——————————————————————————————————

Specify key expiration period. For testing lets go with no expire.

——————————————————————————————————

Please specify how long the key should be valid.

         0 = key does not expire

      <n>  = key expires in n days

      <n>w = key expires in n weeks

      <n>m = key expires in n months

      <n>y = key expires in n years

Key is valid for? (0) 

——————————————————————————————————

Specify user ID, email address and the comments for the key.

——————————————————————————————————

You need a user ID to identify your key; the software constructs the user ID

from the Real Name, Comment and Email Address in this form:

    "Heinrich Heine (Der Dichter) <heinrichh@duesseldorf.de>"

——————————————————————————————————

You can give passphrase to the key. This is done for extra caution.

——————————————————————————————————

You need a Passphrase to protect your secret key.

——————————————————————————————————

key has been created.

b. We are done with key generation. once the key is generated use list keys command to list the key details.

——————————————————————————————————

gpg --list-keys

——————————————————————————————————

e.g.: It would look like below

pub   1024D/7FFAD482 2015-10-02

uid                  article (test key for the article) <article@abc.com>

sub   2048g/80859349 2015-10-02

c. Export public key.

Public key need to be exported so that it can be shared with target system.

Key can be exported in 2 formats.

1. gibberish format 

2. Alphanumeric (commonly used) format. armor command in below syntax.

——————————————————————————————————

gpg --output article.gpg.export --export article@abc.com

gpg --armor --export article@abc.com > article.gpg.export

——————————————————————————————————

d. Transfer key to target system.

After executing above command article.gpg.export file will be created. You now have to send the key to target system. You have 2 options.

1. Using gpg —send-key option you can send the key 

2. FTP the file from server to local and send the file to the contact person who is in-charge of target system through outlook email. 

e. Install gpg key at target system.

——————————————————————————————————

gpg --import article.gpg.export

——————————————————————————————————

The log should look like below. If you do not see "imported as 1" then you export of public key was not done properly.

gpg: Total number processed: 1

gpg:               imported: 1

f. encrypt file using the generated key

Lets assume article.txt is the original file and that need to be encrypted so using below command article.txt will be encrypted using public key.

——————————————————————————————————

gpg -s --no-tty --always-trust --passphrase "Testing" -u article@abc.com "article.txt"

——————————————————————————————————

when you define your key in the source system whatever passpharse was given the same as to be given in above command.

e.g.: “Testing” is the passpharse used.

send encrypted file to the target system.

Target system need to decrypt the file

——————————————————————————————————

gpg --no-tty --passphrase "Testing" -u article@abc.com "article.txt.gpg"

——————————————————————————————————

After executing decrypt command the encrypted file will be decrypted and the decrypted file will be processed.

g. How the encrypted files are exchanged between the system:

The steps are explained in below article.

http://oracleappssuren.blogspot.com/2015/11/ssh-key-for-ebs-environment.html

Other scenario:

Lets take an example of travel card process:

In travel card process where source organization would send the public key to bank and bank encrypt and send the transaction file so now source organization will decrypt and load the transactions into Internet expense.

Source system could generate public key, share the key with target system and target system will generate file with applying encryption using the key shared by source system. Now source system get the encrypted file from target and decrypt the file and process it.

What ever steps we have followed above is going to be same.

The command for encrypt and decrypt going to be

——————————————————————————————————

Target system:
gpg --encrypt --recipient article@abc.com article.gpg.export

Source system:
gpg --no-tty -u article@abc.com article.txt.gpg 

——————————————————————————————————

gpg: decryption failed: secret key not available

When you get above error that means the you have shared the public key only to other party and system is expecting to share private key. So you have got 2 options. 

1. Share private key so that other party can import and then can encrypt the file.

2. or instead of encrypt sign the document.

Thanks for reading my blog. Have a wonderful day!!!

SSH key for EBS environment

Establish SSH connection between 2 servers to exchange files:

In Oracle EBS you may come across scenario to exchange files between 2 systems. 

Example: it could be sending CTX file to bank or receiving credit card transaction file from bank.

We will go over basic steps which is required to achieve the file transmission functionality.

Create SSH key

a. To start with we need to SSH Key in the source system.

Go to application server (middle tier) and type below command from command line. 

——————————————————————————————————

ssh-keygen

——————————————————————————————————

You will be prompted to supply file name (for saving the key pair) and passphrase.

Preferred location to store your key pair is ~/.ssh

after completion of generating key, now you see 2 files created 

> private key (without extension)

> Public key (with extension .pub)

b. Download public key to your local using scp or ftp.

c. Transfer public key to remote (target) system through email.

d. In the remote system: the key need to be installed.

Add the public key to authorized_key file located in ~/.ssh folder

Lets say your public key file name is id_rsa.pub then using below command the public key content will be added to authorized key file.

Authorized file will lets the server authenticate the client, if the public key is not added to this file then client can not connect to remote server.

——————————————————————————————————

cat ~/id_rsa.pub >> ~/.ssh/authorized_key 

——————————————————————————————————

e. Since we are all set with the remote server settings, now it is time to test the connection.

ssh username@remotehost.com or

sftp username@remotehost.com

When you try to connect first time it will prompt to add the known_host file.

This file is located in ~/.ssh/known_host in the client (source) machine. This file will keep the record of different connection you establish. 

Known host will lets the client authenticate the server

If you have set passphrase while creating key then during connection you will be prompted to enter passphrase. 

If you have not set passphrase then you can connect without passphrase

Note: Since we are making secure FTP connection using key pair most of the time it is good to create key without passphrase so that it will be easy to programmatically connect and ftp the files between client and remote machine.

Thanks for reading my blog. Have a wonderful day!!!

How to get access to user management responsibility

While working in Oracle applications you may need to add roles and responsibility to user account.

How do you do that?

First thing which you can think of is, go to system administrator responsibility navigate to security, user, define.

Query the user and add responsibility. But how do you add role to the user?

Example: Access Approval management Engine (AME) role to work in building an approval process.

So how do you do that?

Login as SYSADMIN, go to user management responsibility, choose 'role' menu.
Query the user to which you want to access AME.

Click on update and add role called 'Security Administrator'

Click update once done. 

Now logout from SYSADMIN and login as the user (to which you have added the role in previous step). You should be able to access user managment responsibility.

Using user management you can even add responsibility.

Security administrator role added to user account

Oracle Internet Expense (OIE) - Policy

Objective:

Oracle Internet expense (OIE) module is used to enter employee personal and business expense so it can be reimbursed from the employer.

Today we will look how to implement company policy.

Policy:

Policy is implemented to enforce employee to flow company rule while creating expense report.

e.g.:

Meals expense should not exceed 50$ per person

Similar to above scenario there could be various other rule which customer might want to implement.

All these can be implemented through:

• Policy Schedule
• OIE Client extension.

We will go over each type and discuss in detail.

Policy Schedule:

·         Oracle has given standard method to create rule through setup navigation.

·         Go to Internet expense setup and administration responsibility

·         Internet expense setup

·         Click policy tab, decide the expense type to which you need to create policy

·         Click on policy schedule button to create new policy.

·         Select the rule, currency.

·         Create rule period (this is needed because in some company rule keep changing for each quarter/annual so multiple rule periods can be created with start and end date).

·         Active the rule

·         Assign the policy to expense type. (go to parables manager, setup, invoice, expense report template)

·         Query for the expense you want to apply policy, enter the policy and save the transaction.

Setup navigation with example:

a. 

Go to Internet expense setup and administrator responsibility, Internet expense Setup menu.
Navigate to Policy tab and choose the expense fields for which you want to create schedule.

In below example let us see how to create policy schedule for Meals expense type and the policy not to exceed more than 50$ per person, if more than 50$ then warning message should be displayed.

Warning message is define in Application message: OIE_POL_VIOLATION_REV_WRN
You may modify this message as per client needs.

Click on Create button to create policy schedule

b. 

Give name to policy and start date.
If the rule is to be for multiple currency then you may choose multiple, but for our demo purpose let us use Single Reimbursement currency.
You may apply your rule based on daily limit (limit per day), Tolerance limit (for each expense report) etc.

In our demo, we want to apply policy on daily limit.
i.e.: Requester can submit meals expense up to 50$ per day.

c. 

Oracle gives 3 different daily limit options: 

• Individual meal
• Daily Sum
• Both

Individual Meal:
When expense report is created with Breakfast meal and lunch meal.

The rule will be applied for each meal type independently.

e.g.:

If breakfast meal is set to 50$ limit and lunch meal is set to 75$ limit.
In this example assume there is only 1 participant in the expense.

The policy will be applied to individual meal type.

If you did not choose this option then how oracle works is it will sum both breakfast and lunch together and use minimum set limit.

i.e.:
Break fast - 45$
Lunch 65$
Since both breakfast and lunch are in same expense, by setting Daily limit to "individual" rule will be applied separately. In above example breakfast and lunch is within limit so no policy violation.

Daily Sum:
If you do not chose daily limit by default oracle choose daily sum.
This option works by combining all the expense type expenses and calculate the value based on lowest limit.

e.g.:
lets take same example listed above.
limit for breakfast is 50 and lunch is 75

when expense is created with
breakfast - 45$
lunch - 65$

How it works is, sum both the expense 45+65/2=55 and take lowest limit (55/50 (breakfast limit).
With this calculation, the expense report violates the policy.

But in this case there is no policy violation when you look at break fast and lunch seperatly but with daily sum option it works differently.

This is oracle standard functionality.
you can find more details in 

Two Meal Policy Schedules On One Expense Report Template Uses Lower Limit (Doc ID 580066.1)

Both: 
It takes both above options.

d.

Create period. This is used to create policy for each period as the policy keeps changing so you can have all the policy period created upfront and give start date so the policy period is activated accordingly.

e.g.: for Quarter one, meals limit is 50$ per person.

for quarter four, meals limit is 60$ per person.

So no need to create 2 different policy schedule for above case, you can have 1 policy schedule and 2 period. You need to give the limit value when you define period.

e.

You need to give the limit value in the detail section

Expense template:
Once the set up is done. Now it is time to attach policy to expense type.

Go to Payables manager, setup, invoice, expense report templates.

Query the expense template and attached the policy to the expense type.

OIE Client extension:

Oracle has given custom function to validate the expense report.

ap_web_cust_dflex_pkg.CustomValidateLine  

Merritt and demerits between the two options:

Policy schedule	OIE client extension
Easy to use/configure	Requires technical skill
Easy to maintain rules	Difficult to maintain rules
No special skill required	Requires PLSQL skill
Business can create and maintain without IT involvement	IT involvement is required as it requires coding skill
Can display warning or error message based on profile	Will display only error message
Complex validation is not possible	Easy to implement complex logic and validation
Can display only 1 custom message	Can display various message based on expense type

Approval Management Engine (AME) - Query to find Rule/condition/approval type/approval group

Agenda:
This article will help to extract AME data from database.



-- Rule
select * from AME_RULES_TL where rule_id=10023;
--Condition
select * from AME_CONDITION_USAGES where rule_id=10023;
select * from AME_CONDITIONS where condition_id in (12013,12023);
-- Condition value
select * from ame_string_values where condition_id in (12013,12023);
-- Attribute
select * from AME_ATTRIBUTES where attribute_id in (15265,15264,15264);
--Action
select * from AME_ACTION_USAGES where rule_id=10023;
select * from AME_ACTIONS where action_id=16322;
--approval group
select * From hr.ame_approval_groups f where approval_group_id='15019';
--appgroval goroup type
select * from AME_ACTION_TYPES where action_type_id in (10013,10007,10006);
--approval group member
select * from ame_approval_group_members where approval_group_id=;



select distinct a.description rule, e.name attribute,c.PARAMETER_ONE condition_value,c.PARAMETER_two condition_value, c.PARAMETER_THREE condition_value, d.string_value condition_value,k.name approval_type,f.name approval_group
from AME_RULES_TL a, AME_CONDITION_USAGES b,  AME_CONDITIONS c, ame_string_values d ,AME_ATTRIBUTES e, hr.ame_approval_groups f, AME_ACTIONS g, AME_ACTION_USAGES h,AME_ACTION_TYPES k--, ame_approval_group_members i, hr.per_all_people_f j
where 1=1
--and a.description='Req Source - EDC Exception'
and f.creation_date > '01-Jan-2010'
and a.rule_id=b.rule_id
and b.condition_id=c.condition_id
and b.condition_id=d.condition_id(+)
and c.attribute_id=e.attribute_id
and g.PARAMETER = to_char(f.APPROVAL_GROUP_ID)
and g.action_id=h.action_id
and h.RULE_ID=a.rule_id
and h.rule_id=b.rule_id
and k.action_type_id=g.action_type_id
order by 1,2;

--and f.approval_group_id = i.approval_group_id
--  and i.orig_system = 'PER'
--  and j.person_id = i.orig_system_id
--  AND trunc(sysdate) BETWEEN j.effective_start_date AND j.effective_end_date;

Oracle Patch Impact analysis

Objective:

Oracle always provides patch to fix bugs, add more features, improvements and so on.
So while implementing patch in development environment everyone wants to know the impact of the patch, what objects are impacted, how many files are added or changed etc.

Various method:

There are 2 methods this can be achieved.

1. adpatch
2. Patch wizard

1. adpatch:

You can install patch with apply mode=no (by default it is yes).
apply=no mode will provide a snap shot of what the patch will do (like introducing new file, changing exisitng files and so on) but it will not apply the patch.

The syntax for using this mode is: 

$ adpatch apply=no 

Problem with this method:

The major issue with this method is lets say if you are applying patch set which involves 500+ objects then reading log file (log file which generated from adpatch) will be a big hassle because you will have to go through each object one by one and get the complete list of impacted objects. Also it will not tell you whether it has modified or creating.

2. Patch wizard:

This method is easy to use and efficient way to get impact analyzed.

Steps to follow:

a. You can analyze patch either having Patch wizard itself download patch from metalink.oracle.com for you and then do the analysis [or] you can download patch manually and then have patch wizard do analyze for you.

We will talk more about how to analyze patch after downloading manually patch from metallic.oracle.com

a. Login as SYSADMIN and navigate to patch wizard

b. Naviage to site map tab --> maintenance (sub tab) -->  patch wizard



c. Change preference (this is one time setup)

d. Provide staging directory in the preference.
Remember this location should have full access.

e. create ad directory within staging location. <staging directory>/ad
Move patch.zip file to <staging directory>/ad
e.g.: /patches12/ad

f. Move InfoBundleR12.zip file to staging directory. Patch wizard requires this file because this stores the meta data for analyzes.

g. Give patch # and perform the analysis.

h. Once analyse is done you can click on the details to find more about the impacts like
       a. No of file changed
       b. No of files newly added
       c. Object type
       e. Impacted modules, etc...

Using below query you can get the list of patches analyzed and other details:

select
adp.bug_number patch_name,
adp.product_family_abbreviation ,
ad_pa_validate_criteriaset.get_cs_prod_fam_abbr(adp.product_abbreviation) product_family_abbreviation,
nvl(adp.product_abbreviation, ' ') ,
adp.bug_description ,
INITCAP(NVL(adp.patch_type,'User Request')) Patch_Type ,
(SELECT COUNT(*) FROM ad_pa_anal_run_bug_prereqs aparbp
WHERE adb.analysis_run_bug_id = aparbp.analysis_run_bug_id ) prereq_cnt,
DECODE (adh.bug_number, NULL, 'N', 'Y'),
adt.name ,
adb.analysis_run_id ,
analysis_run_bug_id ,
ads.snapshot_id ,
ad_pa_validate_criteriaset.get_cs_prod_fam_name(adp.product_abbreviation) product_family_name ,
appi.product_name ,
(SELECT COUNT(*) FROM ad_pa_anal_run_bug_codelevels aparbc
WHERE adb.analysis_run_bug_id = aparbc.analysis_run_bug_id ) level_cnt ,
decode (adb.analysis_status,'MISSING','Missing','READY','Unapplied','APPLIED','Applied',adb.analysis_status) patch_status ,
nvl(decode(adp.has_msi_steps,'Y','Yes','N','No',adp.has_msi_steps), ' ') hasMsiSteps ,
adp.baseline ,
adp.entity_abbr ,
adp.patch_id
from
ad_pm_patches adp ,
ad_appl_tops adt ,
ad_pa_analysis_run_bugs adb ,
ad_pm_product_info appi ,
ad_snapshots ads
,ad_hidden_patches adh
where
1=1--adb.analysis_run_id = 402352 --<request_id>
--and adb.analysis_status in ('MISSING','READY','APPLIED')
--and ads.SNAPSHOT_TYPE = 'G'
--and adt.NAME ='GLOBAL'
and adb.appl_top_id = adt.APPL_TOP_ID
and ads.appl_top_id = adt.APPL_TOP_ID
and appi.product_abbreviation = adp.product_abbreviation
and adb.bug_number = adp.BUG_NUMBER
and adb.baseline = adp.baseline
--and adp.patch_metadata_key = 'DEFAULT'
--and adp.is_code_level = 'Y'
and adh.bug_number(+) = adp.BUG_NUMBER
order by patch_name;

Oracle Purchasing: Buyer Work Center (Bulkload /update feature in Blanket Agreements BPA)

Objective:

In this article we will see what is buyer work center and its benefits and talk more on "Upload feature in  Agreements"

What is buyer work center:

Buyer work center is a web application application. it is a new feature in R12.

It can be accessed through Purchasing super user responsibility.

Benefits:

1. Gives buyer handy way to access demand workbench (PO, Requisition) in a simple view and easy access.
2. Can create standard PO, Blanket Purchase agreements, etc.
3. Easy to create and maintain day-to-day activities.

Bulload or Update Agreements:

Prior to R12 if buyer want to update 100+ lines on the agreement he had to either update each line manually or IT had to create some custom upload functionality and use standard APIs to create upload feature to correct multiple lines on the agreement.

In R12 oracle has given bulk feature to create or update multiple lines on an agreements.

How can this be achieved?

Detail information can be found in Oracle document ID: 1309351.1

OAF - Startup (Setup Jdeveloper and configure tutorial workspace)

Objective:

Once you install JDeveloper there are various questions raised on how to configure JDev and how to run sample (tutorial) workspace to make sure install is right.

Steps to follow:

1. Install JDeveloper (See Note 416708.1 How to find the correct version of JDeveloper to use with eBusiness Suite)
2. Create JDEV_USER_HOME environment variable (JDEV_USER_HOME=C:\Suren\JDEV 10.1.3.3 OAF\jdevbin\jdev) (See image below A)
1. Oracle has recommended to use jdevhome directory to set environment but while i was working in 10.1.3.3.03 jdeveloper myproject was in jdevbin and all other lib are in jdevbin so i have use jdevbin to set in environment variable and it worked fine.
3. Assigned below responsibility to my user account
1. OA Framework ToolBox Tutorial (responsibility key is FWK_TBX_TUTORIAL)
2. OA Framework ToolBox Tutorial Labs (responsibility key is FWK_TOOLBOX_TUTORIAL_LABS)
4. Get .dbc file from server ($FND_SECURE)
5. FTP .dbc file to C:\Suren\JDEV 10.1.3.3 OAF\jdevhome\jdev\dbc_files\secure\
1. Note: you can find dbc_files folder in multiple locations like (C:\Suren\JDEV 10.1.3.3 OAF\jdevbin\oaext\dbc_files\secure) but do not get diverted use the one mentioned above.
6. Enabled Tools->change 'Embedded OC4J Server Preferences' to 'Default Local IP Address' (see image below B).

Run sample (tutorial workspace):

1. By default JDeveloper comes with tutorial workspace and its dependent files.
2. You can find them under jdevbin/Tutorial.zip
1. You will find 3 folder under Tutorial.zip 
1. dbc_files
2. myhtml
3. myprojects
2. unzip myhtml file under \jdevbin\jdev\myhtml
3. unzip myproject folder under \jdevbin\jdev\myprojects
3. Open JDeveloper and click file open >> toolbox.jws 
4. once you open the file you will get popup message saying "Open warning window" "You are about to migrate the application to new version of JDeveloper". 
5. Click Yes/ Ok.
6. Expand the workspace and click on project property (2 options are there, 1. you can double click on project or right click and say project property)
7. 3 basic things to be done to the project property. (see Image below C)
1. DB connection
2. Run time options
3. Run time connection 
8. Right click on Tutorial project to Rebuilt
9. Run "test_fwktutorial.jsp" you will see window like shown in image D.
10. If you see '500 page does not display" error make sure you delete myclasses folder and rebuit the projects.

Image A

Image B

Image C

Image D

OAF export data from VO in xlsx format

Posted: 16-Mar-2015

Purpose:

In this article we are going to see how to export view object in Microsoft office excel xlsx format

To export with xlsx format few basic things needed are

1. Jar files (List of jar i've used is shown in below screen shot)

For better understanding I’ll illustrated steps to follow.

Sample Workspace and the jar files can be downloaded at the end of the article

Steps to create data module to be used in the page:

a.       Create table

create table suren_employee (emplno number, empl_name varchar2(100),LAST_UPDATE_DATE          DATE,LAST_UPDATED_BY           NUMBER (15),LAST_UPDATE_LOGIN         NUMBER (15),CREATION_DATE             DATE,CREATED_BY                NUMBER (15));

b.      Insert data to table

insert into suren_employee (emplno,empl_name) values (10, 'SURENDRANATH');

insert into suren_employee (emplno,empl_name) values (20, 'SUBRAMANI');

Steps to design the page:

1. Create project
2. Create EO
3. Create VO
4. Create application module
5. Create page
6. Assign AM to the page
7. Create a item with style as ‘exportButton’
8. Set controller on the page layout
9. Create button in the page layout

After following above steps your project should look like this

Copy paste below code in the controller:

/* This is a sample code which should be used in the controller

writeXLSXFile is the method which will export data

Call writeXLSXFile method in the PFR (Process Form Request)

*/

import java.io.ByteArrayOutputStream;

import java.io.IOException;

import java.io.OutputStream;

import javax.servlet.http.HttpServletResponse;

import oracle.apps.fnd.common.VersionInfo;

import oracle.apps.fnd.framework.OAViewObject;

import oracle.apps.fnd.framework.webui.OAControllerImpl;

import oracle.apps.fnd.framework.webui.OAPageContext;

import oracle.apps.fnd.framework.webui.beans.OAWebBean;

import org.apache.poi.xssf.usermodel.XSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import suren.oracle.apps.ak.server.SurenEmployeeVORowImpl;

import oracle.jbo.domain.Number ;

  public void processFormRequest(OAPageContext pageContext, OAWebBean webBean)

  {

    super.processFormRequest(pageContext, webBean);

    if(pageContext.getParameter("Download")!=null)

                    {

                      try{

                        System.out.println("Before writeXLSXFile");

                        writeXLSXFile(pageContext, "SurenEmployeeVO1");

                        System.out.println("After writeXLSXFile");

                      }catch(IOException e){

                        e.printStackTrace();

                      }

                        

                    }  

  }

  public  void writeXLSXFile(OAPageContext pageContext,String view_inst_name) throws IOException {

     

    OAViewObject vo = (OAViewObject) pageContext.getRootApplicationModule().findViewObject(view_inst_name);           

    String sheetName = "Export code comb";//name of sheet

    HttpServletResponse response = (HttpServletResponse) pageContext.getRenderingContext().getServletResponse();

    

    // Set excel property

    response.setHeader("Header-Name", "Codecombination");

    response.setHeader("Content-Length", "100000");

    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    response.setHeader("Content-Disposition","attachment; filename=" + "SurenEmp" + ".xlsx");

    response.setHeader("Cache-Control","max-age=0");

    XSSFWorkbook wb = new XSSFWorkbook();

    XSSFSheet sheet = wb.createSheet(sheetName);

    

    // Create header

    

    XSSFRow row12 = sheet.createRow(0);

        XSSFCell celh0 = row12.createCell(0);

        celh0.setCellValue("Employee Number");      

        XSSFCell celh1 = row12.createCell(1);

        celh1.setCellValue("Employee Name");        

    int f=1;

        for(SurenEmployeeVORowImpl row = (SurenEmployeeVORowImpl) vo.first(); row != null;row = (SurenEmployeeVORowImpl) vo.next())

    {

    // Create line

        XSSFRow row1 = sheet.createRow(f);

                 for (int i = 0; i < vo.getAttributeCount(); i++)

                 {

                         

                         if (i==0) {

                         XSSFCell cell = row1.createCell(i);

                         Number empno = (Number)row.getEmplno();

                         String empno1=empno.toString();

                           cell.setCellValue(empno1);

                          System.out.println(row.getEmplno());

                         }

                         if (i==1) {

                         XSSFCell cell = row1.createCell(i);

                         cell.setCellValue(row.getEmplName());    // Dept

                         }

                   }

        System.out.println("row2:" + f);

        f=f+1;

    }

    ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();

    wb.write(outByteStream);

    byte[] outArray = outByteStream.toByteArray();

    response.setContentLength(outArray.length);
    ServletOutputStream outStream = response.getOutputStream();

    outStream.write(outArray);

    outStream.flush();

    outStream.close();

  }

You will see errors in the controller saying missing “XSSFCell not found” etc.

To fix this go to your project property and add poi library to the project.

Download workspace:

Download workspace and jar files