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();
}
}
Comments
re:
Thank you for bringing up this article, i've been looking for information similar to this for a while now.
hii everyone
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,
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.
Read and Write
Can i get the code to read from one excel file and write it into another excel file
Writing Data into Excel
Can any one help how to write data into Excel.
Want to add new line in 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?
Compare excel sheet data
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 ?
If anyone encounters NullPointerException
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'
How to write data in xls file using Groovy scripting
Hi., I am new to Groovy scripting any body help me how to write in xls file using Groovy scripting
Add new comment