How to read MS Excel file in Java

Read MS Excel file in Java:

HSSF

Here Excel 97 file format is called "HSSF," which stands for, you guessed it, Horrible SpreadSheet Format. (We admire their method of making simple things complicated and oversimplifying things that should have been done with more flexibility.) HSSF may have a comical name, but is a very serious API. HSSF lets you read, write, and modify Excel files using nothing but Java.

HSSF APIs

Go to the Jakarta.apache.org/poi site and download the latest binary for the POI project.

HSSF has two APIs for reading: usermodel and eventusermodel. The former is most familiar, and the latter is more cryptic but far more efficient. The usermodel consists primarily of the classes in the org.apache.poi.hssf.usermodel package, as well as org.apache.poi.hssf.eventusermodel. (In earlier versions of HSSF, this was in the eventmodel package.) The usermodel package maps the file into familiar structures like Workbook, Sheet, Row, and Cell. It stores the entire structure in memory as a set of objects. The eventusermodel package requires you to become more familiar with the actual low-level structures of the file format. It operates in a manner similar to XML's SAX APIs or the AWT event model (the origin of the name)--and can be trickier to use. It is also read-only, so you cannot modify files using the eventusermodel.

code:

import java.util.*;
import java.io.*;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.util.Region;


public class ReadExcelSheet
{

public static void main(String []args)
{
ReadExcelSheet readExcel=new ReadExcelSheet();
String path="D:/TestExcel.xls"; //name of Excel file.
try
{
readExcel.readSheet(path);
}
catch (Exception e)
{
e.printStackTrace();
}
}
public void readSheet(String path)
{
try
{
InputStream inp = new FileInputStream(path);
HSSFWorkbook wb = new HSSFWorkbook(inp);
for (int k = 0; k < wb.getNumberOfSheets(); k++)
{
HSSFSheet sheet = wb.getSheetAt(k);
int rows = sheet.getPhysicalNumberOfRows();
System.out.println("\nSheet " + k + " \""+ wb.getSheetName(k) + "\" has "+ rows + " row(s).");
for (int r = 0; r < rows; r++)
{
HSSFRow row = sheet.getRow(r);
if (row == null) {
continue;
}
int cells = row.getPhysicalNumberOfCells();
System.out.println("\nROW " + row.getRowNum()+ " has " + cells + " cell(s).");
for (int c = 0; c < cells; c++)
{
HSSFCell cell = row.getCell(c);
String value = null;
//System.out.println("cell.getCellType()= "+cell.getCellType());
switch (cell.getCellType())
{

case HSSFCell.CELL_TYPE_FORMULA :
value = "FORMULA value="+ cell.getCellFormula();
break;

case HSSFCell.CELL_TYPE_NUMERIC :
value = "NUMERIC value="+ cell.getNumericCellValue();
break;

case HSSFCell.CELL_TYPE_STRING :
value = "STRING value="+ cell.getStringCellValue();
break;

default :
}
System.out.println("CELL col="+ cell.getColumnIndex()+ " VALUE=" + value);
}
}
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
}