Excel read and write for all Formats xls xlsm xlsx

This Program helps you to read and write the excel sheet (all format xls/xlsx/xlsm) efficiently and user friendly using POI. This code snippet will read the data in excel sheet and update the excel(write).One have to download poi jars to implement this functionality.

import java.util.Iterator;

import jcifs.smb.NtlmPasswordAuthentication;
import jcifs.smb.SmbFile;
import jcifs.smb.SmbFileInputStream;
import jcifs.smb.SmbFileOutputStream;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;


public class Test
{

 public static SmbFile connect(String smbServer, String userName, String pwd) throws Exception {
  // TODO Auto-generated method stub
  NtlmPasswordAuthentication ntml = new NtlmPasswordAuthentication("NW",userName,pwd);
  SmbFile client = new SmbFile(smbServer, ntml);
  return client;
 }

 public static void main(String [] args)
 {

  SmbFileInputStream smbFileInputStream=null;
  SmbFileOutputStream fileOut = null;
  String filepath = "";
  String extension = "pdf";
  try
  {

   SmbFile smbExcelFile = connect("smb://172.16.27.205/kbe$/Deliverables/Item Number/WIP/kbe-kmapi/SDT/Testing Binder Database1.xlsm", "317573", "00000");
   smbFileInputStream=new SmbFileInputStream(smbExcelFile);
   fileOut = new SmbFileOutputStream(smbExcelFile);



   //Create the workbok & Read the first sheet

    Workbook workbook = WorkbookFactory.create(smbFileInputStream);
    Sheet sheet = workbook.getSheetAt(0);
    Iterator rowIterator = sheet.rowIterator();


   int intRowNumber = 0;

   //Iterating through the excel sheet
   while (rowIterator.hasNext())
   {

    //Read the row number
    Row row =  (Row) rowIterator.next();
    intRowNumber = row.getRowNum();

    //Get the Fileshare path from 3rd row
    if (intRowNumber == 3) {
     Cell  cellfilepath = row.getCell(5);
     filepath = cellfilepath.toString();
    }

    //Read the document details - starting from 7th row
    if (intRowNumber > 7 && intRowNumber <= sheet.getLastRowNum()) {

     Cell cellbindername = row.getCell(0);
     Cell cellupload = row.getCell(10);
     Cell cellfilelocation = row.getCell(11);

     // if Document name or upload status empty , then continue the next row
     if(cellbindername == null ||  cellupload == null || cellfilelocation ==null)
      break;

     String uploadstatus = cellupload.toString();

     // Reading the excel row details only the upload status is 'ready'
     if (uploadstatus.equalsIgnoreCase("ready")) {

      try {

       Cell cellkeywords = row.getCell(2);


       String filelocationpath="";
       String temp_filelocationpath="";
       if (cellfilelocation != null && !"".equalsIgnoreCase(cellfilelocation.toString()))
        temp_filelocationpath = cellfilelocation.toString();

       int mid= temp_filelocationpath.lastIndexOf('.');

       if(mid==-1)
       {
        filelocationpath =temp_filelocationpath;
       }
       else
       {
        filelocationpath=temp_filelocationpath.substring(0,mid);
       }

       String docName = cellbindername.toString();
       String sourcepath = filepath + filelocationpath + "\\";




       // Validate the document is present in the file path location or not
       String sourceFilePath = "smb:" + sourcepath;
       sourceFilePath = sourceFilePath.replace("\\", "/") +docName+ "."+extension;



       if(cellkeywords == null)
        continue;

       cellupload.setCellValue("Done");

       System.out.println("The value of cell  : "+cellupload.getStringCellValue());
      }catch (Exception e) {

       e.printStackTrace();

      }
     }
    }
   }


   System.out.println("before write");
   workbook.write(smbExcelFile.getOutputStream());
   fileOut.close();
   smbFileInputStream.close();
            System.out.println("after write");
  }
  catch (Exception e)
  {
   e.printStackTrace();
  }
                finally {
                           if(smbFileInputStream != null) {
                               try {
                                smbFileInputStream.close();
                                smbFileInputStream = null;
                               }
                               catch(Exception ex) {
                                   // Nothing can be done now.
                               }
                           }
                           if(fileOut != null) {
                               try {
                                fileOut.close();
                                fileOut = null;
                               }
                               catch(Exception ex) {
                                   // Nothing can be done now.
                               }
                           }
                }
 }
}