How to Set Data into Excel sheet using jxl

The below example program explains how to write / set data in spreadsheet without any formatting such as fonts etc.

In order to write anything we need to first create a writable workbook as below which creates the workbook object.

WritableWorkbook workbook = Workbook.createWorkbook(new File("sampletestfile.xls"));

We can also use the existing excel sheet and write the data into excel sheet. But before doing write operations we need to make sure to take the copy of the original file

and then perform the write operations. The below code is the sample code.

Workbook workbook = Workbook.getWorkbook(new File("testSampleData.xls"));
WritableWorkbook workbookCopy= Workbook.createWorkbook(new File("testSampleDataCopy.xls"), workbook);

Now access the sheet from the workbook which is copied from the original.

WritableSheet wSheet = workbookCopy.getSheet(0);

As now we are ready with the worksheet to which we need to pass the data. We need to mention the location (Column number and Row number) to write the data.

The below is the sample code to write the data into excel sheet.

WritableSheet wshTemp = wwbCopy.getSheet(strSheetName);
Label label= new Label(iColumnNumber, iRowNumber, strData);
wshTemp.addCell(label);   

Please find the below working example program:

import java.io.File;
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.Label;
import jxl.write.WriteException;


public class dataSheet {

    static Workbook wbook;
    static WritableWorkbook wwbCopy;
    static String ExecutedTestCasesSheet;
    static WritableSheet shSheet;
    
    public void readExcel()
    {
    try{
    wbook = Workbook.getWorkbook(new File("path\\testSampleData.xls"));
    wwbCopy = Workbook.createWorkbook(new File("path\\testSampleDataCopy.xls"), wbook);
    shSheet = wwbCopy.getSheet(0);
    }
    catch(Exception e)
    {
        e.printStackTrace();
    }
    }
    
    public void setValueIntoCell(String strSheetName,int iColumnNumber, int iRowNumber,String strData) throws WriteException
    {
        WritableSheet wshTemp = wwbCopy.getSheet(strSheetName);
        Label labTemp = new Label(iColumnNumber, iRowNumber, strData);
                
        try {
            wshTemp.addCell(labTemp);
             } 
            catch (Exception e) 
            {
                e.printStackTrace();
            }
    }
    
    public void closeFile()
    {
        try {
            // Closing the writable work book
            wwbCopy.write();
            wwbCopy.close();

            // Closing the original work book
            wbook.close();
        } catch (Exception e)

        {
            e.printStackTrace();
        }
    }
    
    public static void main(String[] args) throws WriteException
    {
        dataSheet ds = new dataSheet();
        ds.readExcel();
        ds.setValueIntoCell("sheet1", 5, 1, "PASS");
        ds.setValueIntoCell("sheet1", 5, 2, "FAIL");
        ds.setValueIntoCell("sheet1", 5, 3, "PASS");
        ds.closeFile();
    }
}
Excel API: 

Comments

Thank you for bringing up this article, i've been looking for information similar to this for a while now.

hii everyone
how to read the data and write data into the same excel file instead of creating new excel sheet every time...if anyone knows please help me...
thnaks in advance

Hi All,

I am new to selenium webdriver. I want to get input (test data) from excel sheet.

for example: while testing gmail login page userId and password to be capture from excel sheet.

Kindly suggest me the best way of coding with step by step process.

Regards,
Rathod.

Can i get the code to read from one excel file and write it into another excel file

Can any one help how to write data into Excel.

I have to add more than one value in Excel cell with new line in same cell.I am using Jxl.any one suggest how can we do this?

Hi.....i want help how to compare two excel sheet data using selenium ???for example i have two excel sheet with same column name but in column i have different data is there then i want to highlight the differences with the help of selenium ?

For the naive users trying to implement this code you might encounter NullPointerException at line

wshTemp.addCell(labTemp);

As this line is inside function setValueIntoCell which accepts Sheet Name as one of the parameter please provide correct sheet name as of the sheet you want to work on.

I copy pasted this code and it was giving me null pointer exception due to name as 'sheet1' instead of 'Sheet1'

Hi., I am new to Groovy scripting any body help me how to write in xls file using Groovy scripting

Add new comment

Image CAPTCHA