Pages Navigation Menu

Coding is much easier than you think

Java Code to Read XLS and XLSX Files

java excel csv
 

Note : This Program uses XSSFWorkbook for xlsx and HSSFWorkbook for xlx.

 

package com.simplecode.excel;

import java.io.*;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;

class ExcelReader 
{
static void readXlsx(File inputFile) 
{
try 
{
	// Get the workbook instance for XLSX file
	XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));

	// Get first sheet from the workbook
	XSSFSheet sheet = wb.getSheetAt(0);

	Row row;
	Cell cell;

	// Iterate through each rows from first sheet
	Iterator<Row> rowIterator = sheet.iterator();

	while (rowIterator.hasNext()) 
	{
		row = rowIterator.next();

		// For each row, iterate through each columns
		Iterator<Cell> cellIterator = row.cellIterator();
		
		while (cellIterator.hasNext()) 
		{
		cell = cellIterator.next();

		switch (cell.getCellType()) 
		{

		case Cell.CELL_TYPE_BOOLEAN:
			System.out.println(cell.getBooleanCellValue());
			break;

		case Cell.CELL_TYPE_NUMERIC:
			System.out.println(cell.getNumericCellValue());
			break;

		case Cell.CELL_TYPE_STRING:
			System.out.println(cell.getStringCellValue());
			break;

		case Cell.CELL_TYPE_BLANK:
			System.out.println(" ");
			break;

		default:
			System.out.println(cell);

		}
		}
	}
}
catch (Exception e) 
{
	System.err.println("Exception :" + e.getMessage());
}
}

static void readXls(File inputFile) 
{
try 
{
	// Get the workbook instance for XLS file
	HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
	// Get first sheet from the workbook
	HSSFSheet sheet = workbook.getSheetAt(0);
	Cell cell;
	Row row;

	// Iterate through each rows from first sheet
	Iterator<Row> rowIterator = sheet.iterator();
	
	while (rowIterator.hasNext()) 
	{
		row = rowIterator.next();

		// For each row, iterate through each columns
		Iterator<Cell> cellIterator = row.cellIterator();
		
		while (cellIterator.hasNext()) 
		{
		cell = cellIterator.next();

		switch (cell.getCellType()) 
		{

		case Cell.CELL_TYPE_BOOLEAN:
			System.out.println(cell.getBooleanCellValue());
			break;

		case Cell.CELL_TYPE_NUMERIC:
			System.out.println(cell.getNumericCellValue());
			break;

		case Cell.CELL_TYPE_STRING:
			System.out.println(cell.getStringCellValue());
			break;

		case Cell.CELL_TYPE_BLANK:
			System.out.println(" ");
			break;

		default:
			System.out.println(cell);
		}
		}
	}

} 

catch (FileNotFoundException e) 
{
	System.err.println("Exception" + e.getMessage());
}
catch (IOException e) 
{
	System.err.println("Exception" + e.getMessage());
}
}

public static void main(String[] args) 
{
	File inputFile = new File("C:\input.xls");
	File inputFile2 = new File("C:\input.xlsx");
	readXls(inputFile);
	readXlsx(inputFile2);
}
}

 

To execute the above code you must have the following libraries  

  • dom4j-1.1.jar
  • poi-3.7-20101029.jar
  • poi-ooxml-3.7-20101029.jar
  • poi-ooxml-schemas-3.7-20101029.jar
  • xmlbeans-2.3.0.jar

About Mohaideen Jamil


Am currently working as a Struts 2 Developer in a reputed IT Organisations. I can help you with teaching Core java and Struts 2. Follow me on Facebook or Google Plus. If you like my tutorials, consider making a donation to this charity, thanks.

  • ravi beli

    This is really great example. After searching a lot I got this example. Thanks a lot buddy!!

    • http://www.simplecodestuffs.com Mohaideen Jamil

      Most welcome :)

  • Shamshad Shiragiri

    hi i need an android app which has to display the excel file after clicking a button.I have tried with lot many codes but am getting error.can u please help me out.what will be the source code to display excel sheet which will be present in external storage that is in memory card.

  • GS

    I hope you can help. I am also having problem – problem is with xmlbeans which has been retired – these are my errors
    first it says

    Error:Class org.apache.xmlbeans.xml.stream.Location has already been added to output. Please remove duplicate copies.

    1 error; aborting

    trouble writing output: Too many method references: 76374; max is 65536.

    You may try using –multi-dex option…..

    and then further down it says

    trouble processing “javax/xml/stream/events/StartElement.class”:

    when not building a core library.

    Ill-advised or mistaken usage of a core class (java.* or javax.*)

  • chaitanya

    hi i am getting this Exception :java.lang.reflect.InvocationTargetException

    • Marinda0013

      Hi, have you solved this issue yet? Getting the same error.

  • sathyapria

    Hi I have tried your code and its working for me!! But I want to read specific row in one sheet from xlsx file.

  • Sajedur Rahman Tareq

    Dear Sir, .xls file can read easily but .xlsx file can not read.

  • Yogiraj

    Hi Jamil,
    I am getting following error .
    Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException

    kindly reply.
    Thanx in advance

    • http://www.simplecodestuffs.com Mohaideen Jamil

      You have to include the following jar.

      xmlbeans-2.3.0.jar

  • Preshita

    Hi Jamil,

    I need to read an xlsx and save it in mySQL db.Please can you help?

    • Sajedur Rahman Tareq

      try {
      Class.forName(“com.mysql.jdbc.Driver”);
      Connection con = (Connection) DriverManager.getConnection(“jdbc:mysql://localhost/databasename”, “”, “”);
      System.out.println(“Connection successful”);
      con.setAutoCommit(false);

      FileInputStream fis = new FileInputStream(new File(file.toString()));

      XSSFWorkbook workbook = new XSSFWorkbook(fis);

      XSSFSheet sheet = workbook.getSheetAt(0);

      Cell cell;
      Row row;

      String str = txtfield.getText();
      String str1 = txtfield.getText();
      txtfield.getText();

      File file = new File(chooser.toString());
      String str3 = file.getName();

      stmt = con.createStatement();
      con.setAutoCommit(false);

      Iterator rowIterator = sheet.iterator();
      String select_sql = “SELECT * ”
      + “FROM fsibl ”
      + “WHERE file_name = ? “;
      pstm = con.prepareStatement(select_sql);
      pstm.setString(1, file.getName());
      res = pstm.executeQuery();

      if (res.next()) {
      System.out.println(“Connected selected database”);
      String fileName = res.getString(“file_name”);

      System.out.println(“%s” + fileName);
      JOptionPane.showMessageDialog(null, “File Name Already Exist”);
      successMessage = null;
      stmt.close();
      } else {
      while (rowIterator.hasNext()) {

      row = (XSSFRow) rowIterator.next();

      for (int i = 1; i <= sheet.getLastRowNum(); i++) {
      row = sheet.getRow(i);

      Cell bank = row.getCell(0);

      Cell account_no = row.getCell(1);

      Cell customer_name = row.getCell(2);

      Cell lvs_qty = row.getCell(3);

      Cell delivery_br = row.getCell(4);

      Cell tc = row.getCell(5);

      Cell routing_no = row.getCell(6);

      String sql = "insert into tablename(value,value, value, value, value,value, value,value,value,value,value) values (?, ?, ?, ?, ?, ?,?,?,?,?,?)";
      pstm = (PreparedStatement) con.prepareStatement(sql);
      pstm.setString();
      pstm.setString();
      pstm.setString();
      pstm.setString();
      pstm.setString();
      pstm.setString);
      pstm.setString();

      pstm.setDate()));
      pstm.setString();
      pstm.setString();
      pstm.setString();
      pstm.executeUpdate();
      System.out.println("Import rows " + i);

      }
      // con.commit();
      pstm.close();
      //workbook.close();
      con.close();

      System.out.println("Success import excel to mysql table");
      JOptionPane.showMessageDialog(null, "Data insert Successfully");

      // For each row, iterate through each columns
      Iterator cellIterator = row.cellIterator();

      while (cellIterator.hasNext()) {
      cell = cellIterator.next();

      switch (cell.getCellType()) {

      case Cell.CELL_TYPE_BOOLEAN:
      // System.out.println(cell.getBooleanCellValue());
      break;

      case Cell.CELL_TYPE_NUMERIC:
      // System.out.println(cell.getNumericCellValue());
      break;

      case Cell.CELL_TYPE_STRING:
      // System.out.println(cell.getStringCellValue());
      break;

      case Cell.CELL_TYPE_BLANK:
      //System.out.println(” “);
      break;
      case Cell.CELL_TYPE_ERROR:
      break;

      default:
      // System.out.println(cell);
      }
      }
      fis.close();
      }
      }
      /* }catch(SQLException e){
      // e.printStackTrace();
      System.out.println(“SQLException………:”+e);
      }*/
      // JOptionPane.showMessageDialog(null, “Data insert Successfully”);

      } catch (ClassNotFoundException | IOException e) {
      System.out.println(e);
      } catch (SQLException ex) {
      // Logger.getLogger(FsibleUI.class.getName()).log(Level.SEVERE, null, ex);
      }

      txtFsiblName.setText(“”);
      txtjDateChooser.setCalendar(null);
      txtLvsPrice.setText(“”);
      txtCoverPrice.setText(“”);

      // You can insert data easily///

  • http://ivycloudtech.com sai

    hi i am also trying like that but it does not read the .xlsx file it given error in console like Conversion to Dalvik format failed: Unable to execute dex: GC overhead limit exceeded. please help me.

  • inDiscover

    Ok…Thanks for this. I will try.

    But can we achieve this with the concept of java collection. Basically I dont have any idea about java programming , I am very new here.
    Any way I want to use this java code in one of my application hence I am asking this .

    Thanks you

    • Jamil

      Yes you can…

  • InDiscover

    Hi I tried this code to read an xlsx file which contains 72,000 rows. But this code is throwing an error saying that “Exception in thread “main” java.lang.OutOfMemoryError: Java heap space”.
    Any idea on this or do i need to modify any thing on this code for a work around.

    • Jamil

      Hi Latif,
      If Java program requires a large amount of memory, then JVM will begin to throw OutOfMemoryError.

      To avoid this error you have to increase the heap size of JVM.
      The details about Increase JVM heap size is available in this post

      Hope it helps you:)