Excel Sheet Operations
Excel sheets are used for storing test data. Spreadsheets will be of two extensions. xls and xlsx. xls is older and xlsx is newer version of MS office.
Read and write operation in xls files can be done through jxl jar. And those files with extension xlsx can be read or written by poi jars.
Poi is having more features it also support old formats like xls format to read and write.
Also, we need to know how to work with excel sheet as we will be storing the results of test cases into excel.
Read excel sheet by using jxl. -- xls format
Download jxl.jar any version.
Add that jar into the project.
Create a new Java file and write code inside main method.
Here Workbook represents excel sheet.
The workbook can have many sheets
One sheet can have many cells, rows, columns.
Now we can fetch data from cell object
package com.jbk;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
public class ReadExcel {
public static void main(String[] args) throws BiffException, IOException {
File file = new File("d:\\login.xls");
Workbook workbook = Workbook.getWorkbook(file);
Sheet sheet = workbook.getSheet(0); // Zero is a Sheet Number.
Cell cell = sheet.getCell(0, 0); // Column Row Index
System.out.println(cell.getContents());
}
}
Tasks expected from you:
Explore all different methods in workbook class. Sheet class.
How to get no of rows and no of columns here.
Once you get this how we can print excel sheet by using for loop.
Hint: there will be 2 for loops
One for loop is for rows and inside that for loop
one for loop is for columns.
getCell method will be using both rowno and column no dynamically.
Write excel sheet by using jxl. -- xls format
package com.javabykiran;
import jxl.Workbook;
import jxl.write.*;
import jxl.write.Number;
import java.io.File;
import java.io.IOException;
public class ExcelWr {
private static final String EXCEL_FILE_LOCATION = "JbkExcel. xls";
public static void main(String[] args) {
// 1. Create an Excel file
WritableWorkbook myFirstWbook = null;
try {
myFirstWbook = Workbook.createWorkbook(newFile(EXCEL_FILE_LOCATION));
// create an Excel sheet
WritableSheet excelSheet = myFirstWbook.createSheet("Sheet 1", 0);
// add something into the Excel sheet
Label label = new Label(0, 0, "Test Count");
excelSheet.addCell(label);
Number number = new Number(0, 1, 1);
excelSheet.addCell(number);
label = new Label(1, 0, "Result");
excelSheet.addCell(label);
label = new Label(1, 1, "Passed");
excelSheet.addCell(label);
number = new Number(0, 2, 2);
excelSheet.addCell(number);
label = new Label(1, 2, "Passed 2");
excelSheet.addCell(label);
myFirstWbook.write();
} catch (IOException e) {
e.printStackTrace();
} catch (WriteException e) {
e.printStackTrace();
} finally {
if (myFirstWbook != null) {
try {
myFirstWbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
Write excel sheet by using poi. -- xlsx format
Apache POI is one of them and is well trusted over time. In short, you can read and write MS Excel files using Java. In addition to this you can also read and write MS Word and MS PowerPoint files using Java.
If you are working on a maven project, you can include the POI dependency in pom.xml file using the following index:
Syntax:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.9</version> </dependency>
If you are not using maven, then you can download maven jar files from POI download page. Include following jar files in order to run the sample code:
dom4j-1.6.1.jar
poi-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-ooxml-schemas-3.9-20121203.jar
xmlbeans-2.3.0.jar
Writing an excel file by POI:
We will be taking this example first so that we can reuse the excel sheet created by this code to read back in the next example.
Writing a file using POI is very simple and it involves the following steps:
Create a workbook
Create a sheet in the workbook
Create a row in the sheet
Add cells in the sheet
Repeat step 3 and 4 to write more data
Program for writing into excel sheet by poi is as follow.
package com.javabykiran;
import java.io.File;
import java.io.FileOutputStream;
import java.util.Map;
import java.util.Set;
import java.util.TreeMap;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class WriteExcelDemoPoi {
public static void main(String[] args) {
// Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Employee Data");
// This data needs to be written (Object[])
Map< String, Object[]> data = new TreeMap< String, Object[]>();
data.put("1", new Object[] { "ID", "NAME", "LASTNAME" });
data.put("2", new Object[] { 1, "kiran", "java" });
data.put("3", new Object[] { 2, "kiran", "selenium" });
data.put("4", new Object[] { 3, "good", "selenium" });
data.put("5", new Object[] { 4, "java", "good" });
// Iterate over data and write to sheet
Set< String> keyset = data.keySet();
int rownum = 0;
for (String key : keyset) {
Row row = sheet.createRow(rownum++);
Object[] objArr = data.get(key);
int cellnum = 0;
for (Object obj : objArr) {
Cell cell = row.createCell(cellnum++);
if (obj instanceof String)
cell.setCellValue((String) obj);
else if (obj instanceof Integer)
cell.setCellValue((Integer) obj);
}
}
try {
// Write the workbook in file system
FileOutputStream out = new FileOutputStream(new File("javabykiran_excel_poi_demo.xlsx"));
workbook.write(out);
out.close();
System.out.println("javabykiran_excel_poi_demo.xlsx written successfully on disk.");
} catch (Exception e) {
e.printStackTrace();
}
}
}
Read excel sheet by using poi. -- xlsx format
Program for writing into excel sheet by poi is as follow.
package com.javabykiran;
import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcelDemoPoi {
public static void main(String[] args) {
try {
FileInputStream file = new FileInputStream(new File("javabykiran_excel_poi_demo.xlsx"));
// Create Workbook instance holding reference to .xlsx file
XSSFWorkbook workbook = new XSSFWorkbook(file);
// Get first/desired sheet from the workbook
XSSFSheet sheet = workbook.getSheetAt(0);
// Iterate through each rows one by one
Iterator< Row> rowIterator = sheet.iterator();
while (rowIterator.hasNext()) {
Row row = rowIterator.next();
// For each row, iterate through all the columns
Iterator< Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
// Check the cell type and format accordingly
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
System.out.print(cell.getNumericCellValue() + "\t");
break;
case Cell.CELL_TYPE_STRING:
System.out.print(cell.getStringCellValue() + "\t");
break;
}
}
System.out.println("");
}
file.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
Note:
You can try to read xls format excel sheet by poi.
Always save files properly and path of files needs to be carefully mentioned in the program.
“Unable to recognize OLE stream error” you may face because of wrong format. Save as your file then save into xlsx format. Do not rename.