Read write to excel sheet using JExcelApi

Java Class - read write to excel sheet using JExcelApi

Developed using the tutorial available at http://www.andykhan.com/jexcelapi/tutorial.html

**** Usage******

         String [][] asTestData;
      //* Read the test scenarios into an array
      asTestData = ExcelUtils.ReadExcelData(sTestDataFile, "TestCaseData");


     //** Write result to a single cell
     ExcelUtils.WriteExcelDataSingle("Result",sXLSReport,iTestCase,21,"");

**** Class******

 import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import jxl.*;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.Orientation;
import jxl.format.PageOrder;
import jxl.format.PageOrientation;
import jxl.format.PaperSize;
import jxl.format.ScriptStyle;


 public class ExcelUtils{
   public static void main(String[] args){
    }
 //* Utility function to Read Data from an excel sheet into a multidimensional array       
    public static String[][] ReadExcelData(String sFileName, String sSheet){
             Workbook wb;
          Sheet wsheet;
          int iColumns;
          int iRows;
          String[][] aData = null;
             try {
                  File fp = new File(sFileName);
                        //* Open the workbook
                        wb = Workbook.getWorkbook(fp);
                        //* See if a sheet was referenced in the call. If not, default to the first sheet
                        if ((sSheet.isEmpty()) || (sSheet.equals(""))){ 
                             wsheet = wb.getSheet(0);
                             }
                        else
                        {
                             //* Set up a reference to the sheet in the workbook
                             wsheet = wb.getSheet(sSheet);
                        }
                         //* Get the row and column counts in the sheet and assign dimensions to the output array.                      
                         iColumns = wsheet.getColumns();
                         iRows = wsheet.getRows();
                         aData = new String[iRows][iColumns];   
                         //* Loop through the rows and columns assigning values to the array elements
                         for(int row = 0;row < iRows;row++) {
                              for(int col = 0;col < iColumns;col++) {
                           aData[row][col] = wsheet.getCell(col, row).getContents();
                         }
                       }
                        //* Close the workbook
                        wb.close();
                         wsheet = null;
                         wb = null;
                         fp = null;
             } catch(Exception ioe) {
               System.out.println("Error: " + ioe);
              }
             return aData;
        }
    //* Utility function to Read Data from a single cell in an excel sheet       
 public static String ReadExcelDataSingle(String sFileName, String sCell, String sSheet){
     Workbook wb;
        Sheet wsheet;
        String sData = null;
     try {
               File fp = new File(sFileName);
                        //* Open the workbook
                        wb = Workbook.getWorkbook(fp);
                        //* See if a sheet was referenced in the call. If not, default to the first sheet
                        if ((sSheet.isEmpty()) || (sSheet.equals(""))){ 
                             wsheet = wb.getSheet(0);
                             }
                        else
                        {
                             //* Set up a reference to the sheet in the workbook
                             wsheet = wb.getSheet(sSheet);
                        }
                        //* Make everything uppercase to ease comparision
                        sCell = sCell.toUpperCase();
                        //* Retrieve the value from the cell
                        sData = wsheet.getCell(sCell).getContents();
                        //* Close the workbook
                        wb.close();
                        wsheet = null;
                        wb = null;
                        fp = null;
     } catch(Exception ioe) {
          System.out.println("Error: " + ioe);
      }
     return sData;
   }
//* Utility function to enter data in a single cell in an excel sheet        
    public static void WriteExcelDataSingle(String sValue, String sFileName,int iRow,int iColumn, String sSheet) throws BiffException, IOException, WriteException {
              WritableCellFormat wrappedText = new WritableCellFormat(WritableWorkbook.ARIAL_10_PT);
              wrappedText.setWrap(true);
              //* Get the location of the original xls file 
              String []asMods = sFileName.split("\\\\");
              String sfiledir = "";
              for (int i = 0; i<(asMods.length - 1); i++){
                   sfiledir = sfiledir + asMods[i] + "\\";
              }
              Workbook wb = Workbook.getWorkbook(new File(sFileName)); 
              WritableSheet wsheet;
              //* Create a temporary excel workbook from the original xls file
        WritableWorkbook wwb = Workbook.createWorkbook(new File(sfiledir + "temp.xls"), wb); 
        //* See if a sheet was referenced in the call. If not, default to the first sheet
              if ((sSheet.isEmpty()) || (sSheet.equals(""))){ 
                   wsheet = wwb.getSheet(0);
              }
              else
              {
                   //* Set up a reference to the sheet in the workbook
                   wsheet = wwb.getSheet(sSheet);
              }
              //* Add cell to the sheet at the specified location
              Label l = new Label(iColumn,iRow,sValue,wrappedText);
              wsheet.addCell(l);
              //* Format the Cell
              WritableCell wcell = wsheet.getWritableCell(iColumn,iRow); 
              WritableCellFormat newFormat = new WritableCellFormat(wcell.getCellFormat());
              WritableFont bold = new WritableFont(WritableFont.ARIAL, 
         WritableFont.DEFAULT_POINT_SIZE, 
         WritableFont.BOLD);
              //* For PASS/FAIL result entry , color the background of the cell accordingly
              if (sValue.equals("PASS")){
                   newFormat.setBackground(Colour.GREEN);
                   newFormat.setFont(bold);
                   wcell.setCellFormat(newFormat);
              }
              if (sValue.equals("FAIL")){
                    newFormat.setBackground(Colour.RED);
                    newFormat.setFont(bold);
                    wcell.setCellFormat(newFormat);
              }
              //* write to the temporary workbook
              wwb.write();
              //* Close the temporary workbook
              wwb.close(); 
              wsheet = null;
              wb = null;
              wwb = null;
              //* Delete the original xls file
              FileUtils.Delete(sFileName);
              //* Rename the temporary xls file with the original xls file name
              FileUtils.Rename(sfiledir + "temp.xls",sFileName); 
    }
    //* Utility function to Copy an excel file.
    public static void CopyExcelFile(String sOriginalFile, String sCopyFile) throws BiffException, IOException, WriteException {
              Workbook wb = Workbook.getWorkbook(new File(sOriginalFile)); 
              WritableWorkbook wwb = Workbook.createWorkbook(new File(sCopyFile), wb); 
              wwb.write(); 
              wwb.close(); 
              wb = null;
              wwb = null;
    }
}