Read Excel file using Apache POI in Java

Requirment – How to read an excel file in Java

Solutions– Well Apache POI is answer.This API will help you a lot.you have to download and add the POI JAR file to your project’s class path. The Apache POI JAR file can be found http://poi.apache.org/download.html
Please note

HSSF (Horrible SpreadSheet Format) – reads and writes Microsoft Excel (XLS) format files.
XSSF (XML SpreadSheet Format) – reads and writes Office Open XML (XLSX) format files.

Following snippet we will use

//..
FileInputStream file = new FileInputStream(new File("C:\\VinayTest.xls"));
             
//Get the workbook instance for XLS file 
HSSFWorkbook workbook = new HSSFWorkbook(file);
 
//Get first sheet from the workbook
HSSFSheet sheet = workbook.getSheetAt(0);
 
//Get iterator to all the rows in current sheet
Iterator<Row> rowIterator = sheet.iterator();
 
//Get iterator to all cells of current row
Iterator<Cell> cellIterator = row.cellIterator();

Following is code to read from excel file

public static void main(String[] args) throws IOException {
         
        // Location of the source file
        String sourceFilePath = "C:/Vinay/ApachePoi/TestFile.xls";
         
        FileInputStream fileInputStream = null;
         
        // Array List to store the excel sheet data
        List excelData = new ArrayList();
         
        try {
             
            // FileInputStream to read the excel file
            fileInputStream = new FileInputStream(sourceFilePath);
  
            // Create an excel workbook
            HSSFWorkbook excelWorkBook = new HSSFWorkbook(fileInputStream);
             
            // Retrieve the first sheet of the workbook.
            HSSFSheet excelSheet = excelWorkBook.getSheetAt(0);
  
            // Iterate through the sheet rows and cells. 
            // Store the retrieved data in an arrayList
            Iterator rows = excelSheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                Iterator cells = row.cellIterator();
  
                List cellData = new ArrayList();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    cellData.add(cell);
                }
  
                excelData .add(cellData);
            }
             
            // Print retrieved data to the console
            for (int rowNum = 0; rowNum < excelData.size(); rowNum++) {
                 
                List list = (List) excelData.get(rowNum);
                 
                for (int cellNum = 0; cellNum < list.size(); cellNum++) {
                     
                    HSSFCell cell = (HSSFCell) list.get(cellNum);
                     
                    if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
                        System.out.print(cell.getRichStringCellValue().getString() + " ");
                    } else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
                        System.out.print(cell.getNumericCellValue() + " ");
                    } else if(cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
                        System.out.println(cell.getBooleanCellValue() + " ");
                    }
                }
                System.out.println("");
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fileInputStream != null) {
                fileInputStream.close();
            }
        }
    }
}}

Happy coding with Vinay in techartifact

Using WebLogic Server Embedded LDAP

LDAP is widely used in webapplication for authorization .we have to set embedded weblogic LDAP server for setting up development enviroment.
First question we have – What is LDAP ?

LDAP is Lightweight Directory Access Protocol.LDAP is a protocol for accessing a directory. A directory contains objects; generally those related to users, groups, computers, printers and so on; company structure information (although frankly you can extend it and store anything in there).
LDAP gives you query methods to add, update and remove objects within a directory (and a bunch more, but those are the central ones).

-Connect to a directory (with varying levels of security)
-Read the entries in a directory
-Write entries in a directory
-Search a directory
-Rename entries in a directory
-Delete entries in a directory

IF you are reading about LDAP the you should also know –

what is directory
A directory is a type of hierarchical database. It is made up of entries, that have a globally unique name, and contain attributes that are named collections of data values. directories are optimised for fast look up, they have a strong security model and they scale well. Because they are a tree structure, different parts of the tree can be maintained by different directories and different administrators. The tree data structure also fits some cases much better than a relational database. (BTW – If you’re familiar with relational databases, ldap is conceptually similar to SQL.)

CONNECT TO WEBLOGIC SERVER EMBEDDED LDAP USING LDAP BROWSER-

WebLogic Server includes an embedded LDAP server that acts as the default security provider data store for the Default Authentication, Authorization, Credential Mapping, and Role Mapping providers.embedded LDAP server contains user, group, group membership, security role, security policy, and credential map information. By default, each WebLogic Server domain has an embedded LDAP server configured with the default values set for each type of information. The Default Authentication, Authorization, Credential Mapping, and Role Mapping providers use the embedded LDAP server as their data store.

In the WebLogic Server Administration Console, change the credential for the embedded LDAP server:

Expand Domain > Security > Embedded LDAP.

– In the Credential field, enter the new credential.

– In the Confirm Credential field, enter the new credential again.

– Click Save.

– Restart WebLogic Server.

Now we will be needing the LDAP browser to access it.There are multiple LDAP browser like

– OpenLDap -http://www.openldap.org/
– jxplorer – jxplorer.org

Following is connection parameter as below –

Hostname: Hostname of the WebLogic Server.
Port: WebLogic Admin Server port.
Base DN: This is the WebLogic domain name.
User DN: By default the Admin user DN is cn=Admin.
Password: Admin user password

1 In the LDAP browser, configure a new connection in the LDAP browser:

-Select the QuickConnect tab.

-Set the host field to localhost.

-Set the port field to 7001 (7002 if SSL is being used).

Set the Base DN field to dc=mydomain where mydomain is the name of the WebLogic Server domain you are using.

– Uncheck the Anonymous Bind option.

– Set the User DN field to cn=Admin.

– Set the Password field to the credential you specified in Step 2.

2 Click the new connection.

– Use the LDAP browser to navigate the hierarchy of the embedded LDAP server.

Now You can create users/groups .

Hope it helps ..happy coding with Vinay in techartifact ……

Attach sources & javadocs in maven project

Maven allows to publish project source code and javadoc with complied artifact. Maven source plugin and javadoc can do it easily for you.

Here is pom configuration for it.

Attaching source

<plugin>
  <groupId>org.apache.maven.plugins</groupId>
  <artifactId>maven-source-plugin</artifactId>
  <executions>
    <execution>
      <id>attach-sources</id>
      <goals>
        <goal>jar</goal>
      </goals>
    </execution>
  </executions>
</plugin>

Maven source plugin also allows to package test source codes

Attaching test source code

<plugin>
  <groupId>org.apache.maven.plugins</groupId>
  <artifactId>maven-source-plugin</artifactId>
  <executions>
    <execution>
      <id>attach-test-sources</id>
      <goals>
        <goal>test-jar</goal>
      </goals>
    </execution>
  </executions>
</plugin>

Attaching javadocs

<plugin>
  <groupId>org.apache.maven.plugins</groupId>
  <artifactId>maven-javadoc-plugin</artifactId>
  <executions>
    <execution>
      <id>attach-javadocs</id>
      <goals>
        <goal>jar</goal>
      </goals>
    </execution>
  </executions>
</plugin>

you achieve it by issuing any of following maven commands

mvn package
mvn install

Please refer to maven plugin website for more information

source-plugin  http://maven.apache.org/plugins/maven-source-plugin/

javadoc-plugin http://maven.apache.org/plugins/maven-javadoc-plugin/