Saturday, June 13, 2009

How to read Excel file using Java (.xls extension)

** This tutorial will work only for Excel .xls (MS 2003) extension **

1. Download jexcelapi jxl.jar from here. This download contain full project, just take the jxl.jar file and put in your workspace.
2. Create one excel file say Binod.xls and put some data.
3. Write ExcelReader.java and put ExcelReader.java and Binod.xls both file is same folder.

ExcelReader.java

import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ExcelReader {

public static void main(String[] args) throws IOException {
String fileName = "Binod.xls";
File file = new File(fileName);
ExcelReader excelReader = new ExcelReader(); excelReader.read(file);
}

public void read(File inputWorkbook) throws IOException {
Workbook workbook;
try {
workbook = Workbook.getWorkbook(inputWorkbook);
Sheet sheet = workbook.getSheet(0);
// System.out.println("No of Columns :: "+sheet.getColumns());
for (int j = 0; j < sheet.getRows(); j++) {
for (int i = 0; i < sheet.getColumns(); i++) {
Cell cell = sheet.getCell(i, j);
CellType type = cell.getType();
if (cell.getType() == CellType.LABEL) { System.out.print(cell.getContents() + " "); }
else if (cell.getType() == CellType.NUMBER) {System.out.print(cell.getContents() + " "); }
else { System.out.print(cell.getContents() + " "); }
}
System.out.println("\n"); }
} catch (BiffException e) { e.printStackTrace(); }
}
}

16 comments:

  1. Would it work for Excel 2007 (.xlsx extension)?

    ReplyDelete
  2. Hi, The above mentioned code works only for .xls excel file NOT for new format .xlsx.
    Check this link (http://poi.apache.org/) might help you.

    Thanks,

    Binod Suman
    http://binodsuman.blogspot.com

    ReplyDelete
  3. Yesterday when I worked with excel files I saved it and went to bed,next morning I saw that my file corrupted,but in some hours to me came a friend,he advised me-repairing Excel,as he said this tool is reliable and has free status,it repairing Excel files, except the possibility to export recovered data into a new Microsoft Excel file,repairing Microsoft Excel files,will learn more about this problem and about how to repair an Excel file,contains the most features of registered program for repairing Excel files, except the possibility to export recovered data into a new Microsoft Excel file.

    ReplyDelete
  4. i want to create multiple sheet in one file from different xls file please help me how can i do that ""mit.2503@ymail.com

    ReplyDelete
  5. can u help mee with this.I am stuck up with it for quiet a long time.

    1. allow a user to select a excel doc.
    2. store the doc in MYSQL databse.

    awaiting.
    Anish

    ReplyDelete
  6. hi...excellent tutorial..

    but how can i remove the 1st row..which is always the heading.

    also,

    how can i take column 1, row 2 value in array?
    such as
    data[0]--will be column 1 and all

    ReplyDelete
  7. I am getting the following error.pls assist

    java.lang.UnsupportedClassVersionError: Bad version number in .class file
    at java.lang.ClassLoader.defineClass1(Native Method)
    at java.lang.ClassLoader.defineClass(Unknown Source)
    at java.security.SecureClassLoader.defineClass(Unknown Source)
    at java.net.URLClassLoader.defineClass(Unknown Source)
    at java.net.URLClassLoader.access$100(Unknown Source)
    at java.net.URLClassLoader$1.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClass(Unknown Source)
    at java.lang.ClassLoader.loadClassInternal(Unknown Source)
    Exception in thread "main"

    ReplyDelete
  8. Hi,
    I get the error cannot access jxl.Cell
    I extracted the jxl.jar into my working directory and try to run it, but i get the above error.
    Is there any thing i am missing ?

    ReplyDelete
  9. Hello ,

    I am doing my proj in java . We are using the Spring framework . I need to import a excel sheet and read the contents of the imported excel sheet and store it in db. My problem is the java code is not working when i linked with the JSP file(not working when it is included in the scriplet also too).Anyone can give me a suggestion how to do this . I am newbie to java. I don't know where give links and all...

    Thanks in advance

    ReplyDelete
  10. Thanks. This worked well. Earlier when I used same jxl, it did not work for me.

    ReplyDelete
  11. I am using Aspose.Cells for Java for managing my excel files without installing MS Excel and i am very mush satisfied with this library. You should try it also i hope you will like it.

    ReplyDelete

You can put your comments here (Either feedback or your Question related to blog)