62
6
Suppose you have an excel sheet on which you want to perform some processing. Now it would be much easy if you drag the data from excel and apply the logic on the data obtained to obtain results.
First we will include all the import classes.
Note: You need to download the apache poi library for availing the excel functionality. The site for the same is poi.apache.org
import java.io.BufferedInputStream; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.util.Iterator; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFRow;
BufferedInputStream is used for obtaining the buffered input, IOException is for reporting of input output exceptions, Iterator is used to iterate over the values of the result obtained from the sheet, POIFSFileSystem is the main class of the POIFS system; it manages the entire life cycle of the filesystem.
HSSFCell is representation of a cell in a row of a spreadsheet, HSSFSheet is representation of a worksheet, HSSFWorkbook is representation of a workbook and HSSFRow is representation of a row of a spreadsheet.
Now we will define the class along with main method:
public static void main( String [] args ) {
try {
InputStream input = new BufferedInputStream(
new FileInputStream("sample.xls"));
POIFSFileSystem fs = new POIFSFileSystem( input );
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
In the code above we defined the main method and make an object of InputStream which points to the excel file which we want to read.
We define an object of POIFSFileSystem which takes the input as the InputStream object.Now we define a new workbook. This workbook will contain the sheets from the excel file.
Now we make the HSSFSheet object called sheet and pass the first sheet from the excel file and we will iterate through each row and column of the sheet and then display the result.
Iterator rows = sheet.rowIterator();
while( rows.hasNext() ) {
HSSFRow row = (HSSFRow) rows.next();
System.out.println("\n");
Iterator cells = row.cellIterator();
while( cells.hasNext() ) {
HSSFCell cell = (HSSFCell) cells.next();
if(HSSFCell.CELL_TYPE_NUMERIC==cell.getCellType())
System.out.print( cell.getNumericCellValue()+" " );
else
if(HSSFCell.CELL_TYPE_STRING==cell.getCellType())
System.out.print( cell.getStringCellValue()+" " );
else
if(HSSFCell.CELL_TYPE_BOOLEAN==cell.getCellType())
System.out.print( cell.getBooleanCellValue()+" " );
else
if(HSSFCell.CELL_TYPE_BLANK==cell.getCellType())
System.out.print( "BLANK " );
else
System.out.print("Unknown cell type");
}
}
} catch ( IOException ex ) {
ex.printStackTrace();
}
}
}
We iterate through each row of the sheet and store that information in variable called rows.
Now we define a while function to read all the rows obtained and use the rows.next() function to obtain the next row and iterate through each column of the row and obtain the column using the next method.
Now we obtain the type of data in that particular column like HSSFCell.CELL_TYPE_NUMERIC is for the numeric data in that particular column. HSSFCell.CELL_TYPE_STRING is for the string data in that particular column.
HSSFCell.CELL_TYPE_Boolean is for the boolean value in that particular column. HSSFCell.CELL_TYPE_BLANK is for the blank data in that particular column.
We put the whole code inside a try catch block to handle the exceptions.
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFRow;
public class ReadExcel {
public static void main( String [] args ) {
try {
InputStream input = new BufferedInputStream(
new FileInputStream("sample.xls"));
POIFSFileSystem fs = new POIFSFileSystem( input );
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Iterator rows = sheet.rowIterator();
while( rows.hasNext() ) {
HSSFRow row = (HSSFRow) rows.next();
System.out.println("\n");
Iterator cells = row.cellIterator();
while( cells.hasNext() ) {
HSSFCell cell = (HSSFCell) cells.next();
if(HSSFCell.CELL_TYPE_NUMERIC==cell.getCellType())
System.out.print( cell.getNumericCellValue()+" " );
else
if(HSSFCell.CELL_TYPE_STRING==cell.getCellType())
System.out.print( cell.getStringCellValue()+" " );
else
if(HSSFCell.CELL_TYPE_BOOLEAN==cell.getCellType())
System.out.print( cell.getBooleanCellValue()+" " );
else
if(HSSFCell.CELL_TYPE_BLANK==cell.getCellType())
System.out.print( "BLANK " );
else
System.out.print("Unknown cell type");
}
}
} catch ( IOException ex ) {
ex.printStackTrace();
}
}
}
Hope you like the article. See you next time.
See the prices for this post in Mr.Bool Credits System below: