Example of Read Excel File with jxl.jar

Q

Where can I find an example Java code that uses jxl.jar to read an existing Excel file?

✍: FYIcenter.com

A

You can follow these suggestions and example to read an existing Excel file with jxl.jar:

JExcelApi can read an Excel spreadsheet from a file stored on the local filesystem or from some input stream. The first step when reading a spreadsheet from a file or input stream is to create a Workbook. The code fragment below illustrates creating a workbook from a file on the local filesystem.

Workbook workbook = Workbook.getWorkbook(new File("myfile.xls"));

(NOTE: when creating a spreadsheet from a ServletInputStream you must remove the HTTP header information before creating the Workbook object.)

Once you have accessed the workbook, you can use this to access the individual sheets. These are zero indexed - the first sheet being 0, the second sheet being 1, and so on. (You can also use the API to retrieve a sheet by name).

Sheet sheet = workbook.getSheet(0);

Once you have a sheet, you can then start accessing the cells. You can retrieve the cell's contents as a string by using the convenience method getContents(). In the example code below, A1 is a text cell, B2 is numerical value and C2 is a date. The contents of these cells may be accessed as follows

Cell a1 = sheet.getCell(0,0); 
Cell b2 = sheet.getCell(1,1); 
Cell c2 = sheet.getCell(2,1); 

String stringa1 = a1.getContents(); 
String stringb2 = b2.getContents(); 
String stringc2 = c2.getContents(); 

// Do stuff with the strings etc 
...

When you have finished processing all the cells, use the close() method. This frees up any allocated memory used when reading spreadsheets and is particularly important when reading large spreadsheets.

// Finished - close the workbook and free up memory 
workbook.close();

Here is the complete source code of example program JxlReadExcel.java:

// Copyright (c) FYIcenter.com
import java.io.*;
import java.util.*;
import jxl.*; 
import jxl.write.*;
import jxl.write.Number;

// Example of reading data from an existing Excel file
public class JxlReadExcel {
   public static void main(String [] args) throws Exception {
	  create();

	  jxl.Workbook workbook = jxl.Workbook.getWorkbook(new File("output.xls")); 
      jxl.Sheet sheet = workbook.getSheet(0);
  
      Cell a7 = sheet.getCell(0,6);
      String stringA7 = a7.getContents();
	  System.out.println("Content of A7: "+stringA7);

      Cell a9 = sheet.getCell(0,8);
      String stringA9 = a9.getContents();
	  System.out.println("Content of A9: "+stringA9);

      workbook.close();
   }

// Create a new Excel file 
   public static void create() throws Exception {
	  jxl.write.WritableWorkbook workbook 
	     = jxl.Workbook.createWorkbook(new java.io.File("output.xls"));
      jxl.write.WritableSheet sheet = workbook.createSheet("First Sheet", 0);

Date now = new Date(0);
DateFormat customDateFormat = new DateFormat ("dd MMM yyyy hh:mm:ss"); 
WritableCellFormat dateFormat = new WritableCellFormat (customDateFormat); 
DateTime dateCellA7 = new DateTime(0, 6, now, dateFormat); 
sheet.addCell(dateCellA7); 

// Create a cell format for Times 16, bold and italic 
WritableFont times16font = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD, true); 
WritableCellFormat times16format = new WritableCellFormat (times16font); 

Date later = new Date(now.getTime()+1000*60*60*24);
WritableCellFormat dateFormat16 = new WritableCellFormat (times16font, customDateFormat); 
DateTime dateCellA9 = new DateTime(0, 8, later, dateFormat16); 
sheet.addCell(dateCellA9);

      workbook.write(); 
      workbook.close();
   }
}

You can compile and run the above example in a command window as shown below:

C:\fyicenter>c:\local\jdk-1.8.0\bin\javac 
   -cp .;\fyicenter\jexcelapi\jxl.jar JxlReadExcel.java

C:\fyicenter>c:\local\jdk-1.8.0\bin\java 
   -cp .;\fyicenter\jexcelapi\jxl.jar JxlReadExcel
Content of A7: 31 Dec 1969 07:00:00
Content of A9: 01 Jan 1970 07:00:00

The above example creates Excel file, output.xsl, then reads cell data back from it.

 

Java Source Code Example for jxl.jar

⇒⇒FAQ for Java Excel API jxl.jar

2018-02-28, 320👍, 0💬