Java Code to Read XLS and XLSX Files
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 rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
row = rowIterator.next();
// 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;
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 rowIterator = sheet.iterator();
while (rowIterator.hasNext())
{
row = rowIterator.next();
// 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;
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
This is really great example. After searching a lot I got this example. Thanks a lot buddy!!
Most welcome :)
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.
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.*)
hi i am getting this Exception :java.lang.reflect.InvocationTargetException
Hi, have you solved this issue yet? Getting the same error.
Hi I have tried your code and its working for me!! But I want to read specific row in one sheet from xlsx file.
Dear Sir, .xls file can read easily but .xlsx file can not read.
Hi Jamil,
I am getting following error .
Exception in thread “main” java.lang.NoClassDefFoundError: org/apache/xmlbeans/XmlException
kindly reply.
Thanx in advance
You have to include the following jar.
xmlbeans-2.3.0.jar
Hi Jamil,
I need to read an xlsx and save it in mySQL db.Please can you help?
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///
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.
Hi It can be fixed by changing the VM values in Eclipse.ini. Visit http://stackoverflow.com/questions/5943712/conversion-to-dalvik-format-failed-unable-to-execute-dex-java-heap-space
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
Yes you can…
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.
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:)