Excel file reading in Java(Simple JSP and Servlet) [closed]

Question

how to read an Excel sheet in java without treating it as a database, and without using any other external api's.

Answer 1

There is no direct way to deal with Excel sheet in Java. You should use Apache POI Java API.

Apache POI is a Java library for reading and writing various Microsoft file formats, especially Office related ones, based on OLE2 and OOXML, such as XLS and DOCX.

Let's see one example of reading an Excel sheet. It supports for both xls and xlsx file format.

import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

private Vector importExcelSheet(String fileName)
{
    Vector cellVectorHolder = new Vector();
    try
    {
        Workbook workBook = WorkbookFactory.create(new FileInputStream(fileName));
        Sheet sheet = workBook.getSheetAt(0);
        Iterator rowIter = sheet.rowIterator();

        while(rowIter.hasNext())
        {
            XSSFRow row = (XSSFRow) rowIter.next();
            Iterator cellIter = row.cellIterator();
            Vector cellStoreVector=new Vector();

            while(cellIter.hasNext())
            {
                XSSFCell cell = (XSSFCell) cellIter.next();
                cellStoreVector.addElement(cell);
            }
            cellVectorHolder.addElement(cellStoreVector);
        }
    }
    catch (Exception e)
    {
        System.out.println(e.getMessage());
    }
    return cellVectorHolder;
}

Call the above method which will return a Vector as follows.

Vector dataHolder=importExcelSheet("Excel_file.xlsx");

---

Note : the Vector used here is just for a demonstration. One should not use it, since it is obsolete in Java. Use other types of Collections available in the Java Collection framework.