Reading and writing data from Excel sheet using JExcel API

Using Java we can read the data from Excel sheets by creating type1 driver , but here we are taking help of JExcelapi we can able to read data directly from the Excel sheets.
Steps to be follow:
Step 1:
download JExcel api from here

Step2:
after downloading extract the zip file.
make jxl.jar available to your class path.

Step 3:
create a Excel sheet with some data

Step 4:
in this step we are reading data from the Excel sheet.
use the below java code:

package com.vaani.excel.jexcel;

import java.io.File;

import java.io.IOException;

import java.util.Date;

import jxl.*;

import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.Number;

public class ReadWriteInExcel {


 public static void readExcelFile() throws IOException, BiffException {

  Workbook workbook = Workbook.getWorkbook(
    new File("---/././file.xls"));

  // my excel sheet name is contacts, iam given the complete path.

// iam reading data from the sheet1

  Sheet sheet = workbook.getSheet(0); 

  // here iam reading the data of 1st column data up three cells

  Cell a1 = sheet.getCell(0,0);
  Cell b2 = sheet.getCell(0,1);
  Cell c2 = sheet.getCell(0,2);

  //getting  the data from cells
  String stringa1 = a1.getContents();
  String stringb2 = b2.getContents();
  String stringc2 = c2.getContents();

  //printing the data
  System.out.println("a1–>"+stringa1);
  System.out.println("b2–>"+stringb2);
  System.out.println("c3–>"+stringc2);


 }

 public static void writeInExcelFile(String args[]) 

throws IOException, WriteException
 {
  // STEP 1:
  // the first step is to create a writable workbook using the 

factory method on the Workbook class.
  WritableWorkbook workbook = Workbook.createWorkbook(

new File("/some file.xls"));
  //test.xls is my work book
  // STEP 2:
  //// sheet name 

WritableSheet sheet = workbook.createSheet("First Sheet",0); 
  //STEP 3:
  //adding(inserting) name to the sheet at location (0,2)
  Label label = new Label(0,2,"A label record");
  sheet.addCell(label);
  //adding number to the sheet at location (1,2)
  Number number = new Number(1,2,3.1459);
  sheet.addCell(number);
  //Step 4:
  //close the all opened connections
  workbook.write();
  workbook.close();
 }


}

Reading password protected excel file from jexcel java api

Microsoft Excel allows users to password protect spreadsheets in a workbook. Protected spreadsheets can only be modified by entering the password (selected by the person who protected the spreadsheet).
Sometimes these passwords are lost, and we are left without the ability to modify said spreadsheets. There are utilities that can be used to either unlock or recover the password, but sometimes these tools are not available. Luckily, with a little bit of programming expertise and the help of a Java library called JExcelApi, we can fairly easily unlock an Excel spreadsheet.

The Code

The following code fragment illustrates how to read a Microsoft Excel spreadsheet into memory, unprotect any protected sheets, and write back an unprotected version of the Excel spreadsheet back to disk.

import java.io.File;
import java.io.IOException;

import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class JExcelApiTest
{
  public static void main(String[] args)
  {
    try
    {
      Workbook workbook = Workbook.
          getWorkbook(new File("/path/to/protected.xls"));
      WritableWorkbook copy = Workbook.
          createWorkbook(new File("/path/to/unprotected.xls"), workbook);

      WritableSheet[] sheets =  copy.getSheets();

      for (WritableSheet sheet : sheets)
      {
        sheet.getSettings().setProtected(false);
      }

      copy.write();
      copy.close();
    }
    catch (BiffException e)
    {
      e.printStackTrace();
    }
    catch (IOException e)
    {
      e.printStackTrace();
    }
    catch (WriteException e)
    {
      e.printStackTrace();
    }

  }

}

As can be seen in the above example, we can read an existing Excel spreadsheet into memory by calling the Workbook.getWorkbook(). method. This method takes an instance of java.io.File as its only parameter, and returns an instance of the jxl.Workbook class, this object contains an in-memory representation of the spreadsheet.
Once we have an in-memory representation of the Excel spreadsheet, we can create an in-memory copy of it by calling the Workbook.createWorkbook() method, passing an instance of java.io.File containing the location where we want to write the unlocked spreadsheet, and the instance of jxl.Workbook we obtained earlier as parameters. This method call will return an instance of jxl.write.WritableWorkbook, wich is basically a modifiable in-memory representation of the Excel spreadsheet.

To unlock or unprotect any password protected sheets in the workbook, we call the WriteableWorkbook.getSheets() method. This method will return an array of jxl.write.WritableSheet objects. These objects represent sheets in the workbook. To unprotect any protected sheets, we traverse the array (note: the example code above uses the enhanced for loop introduced in JDK 1.5, it will not compile under earlier versions of the JDK) and call the getSettings.getProtected(false) on each element of the array. This will effectively unprotect any protected sheets in the workbook. Finally, we write the unprotected workbook to disk by calling the WriteableWorkbook.write() method, and close the workbook by invoking its close() method.