Update or edit existing Excel files in Java using Apache POI

Requirement -Programatically update an Excel spreadsheet in Java.

Solutions – Here an example of Apache POI to update a XLS workbook. Apache POI is a very good API , that can be handy in manipulating Excel documents.This tutorial focuses on XLS documents (Office 97 – 2003).

The existing excel file like below –

sdsdsdsdsdsdsd

Below is code to update the excel file

import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.*;
import java.util.Iterator;
public class updateExcels {  

        public static void main(String[] args) throws Exception{
                
                FileInputStream fsIP= new FileInputStream(new File("C:\\TechartifactExcel.xls")); //Read the spreadsheet that needs to be updated
                 
                HSSFWorkbook wb = new HSSFWorkbook(fsIP); //Access the workbook
                 
                HSSFSheet worksheet = wb.getSheetAt(0); //Access the worksheet, so that we can update / modify it.
                 
                Cell cell = null; // declare a Cell object
               
                cell = worksheet.getRow(2).getCell(2);   // Access the second cell in second row to update the value
                 
                cell.setCellValue("OverRide Last Name");  // Get current cell value value and overwrite the value
                 
                fsIP.close(); //Close the InputStream
                
                FileOutputStream output_file =new FileOutputStream(new File("C:\\TechartifactExcel.xls"));  //Open FileOutputStream to write updates
                 
                wb.write(output_file); //write changes
                 
                output_file.close();  //close the stream    
        }
}

after updating excel file would look like below –

1updateexcel2

happy coding with Vinay in techartifact ….

Modify or Extending the Shell Template in Oracle ADF – techartifact

Requirment – To modify the template that can be UI shell template or Dynamic Shell template or Any other template which in form of jar in your application

Solution- In one of the requirment i have to create binding for panelspiltter in the template.jspx which in template.jar . I have unjar it modify and implemented the changes and again made to jar.

XXX.jspx in oracle-page-templates-ext.jar. Below is the step by step procedure to do this:

Copy the oracle-page-templates-ext.jar from %MiddlewareHome%\jdeveloper\adfv\jlib\ folder
Create a new folder in drive C like C:\temp\jdev\ and paste the copied jar.
Extract the contents of the jar.Open command prompt and navigate to the C:\temp\jdev folder

You can change the folder by “cd” command
For going to parent folder you can use “cd ..” .
Enter the below command for unjar :

 
jar xf oracle-page-templates-ext.jar 

or 

jar xf JarName.jar 

 

If you that jar command cant recogzied then we need to add the java bin folder path into the “Path” system variables. Ex. “C:\Oracle\Middleware\jdk160_24\bin;”
You can do by right the my computer and go to properties ,go to advance tab .Seach path variable and edit and add this above path as well

If this command work well. then you need to delete the existing jar from temp folder you created. We do this so that we can easily repack a jar later.
Open the jdevloper and open that XXX.jspx . modify or whatever changes you want to make do it.save and close it.Repackage the file in a jar. Open a command prompt navigate to the temp folder we created earlier then run the following command:
Now you have to pack as a jar .Use this command

 
jar cfM0 oracle-page-templates-ext.jar *    
0 (Zero)

or
or 

jar cfM0 JarName.jar 

You can give the jar a different name if you like.Now you will Modify libraries and classpath. Open your application in JDeveloper.Right-click your ViewController project and click project properties.Remove the existing Templates in the classpath entries.Add the jar file that we created in the step above.

Happy Learning with Techartifact