Where to get the jexcelapi?
Get the API download form --> http://jexcelapi.sourceforge.net/
What is jexcelapi ?
A Java API to read, write, and modify Excel spreadsheets.
Now java developers can read Excel spreadsheets, modify them with a convenient and
simple API, and write the changes to any output stream (e.g. disk, HTTP, database, or
any socket).
Because it is Java, the API can be invoked from within a servlet, thus giving access to
Excel spreadsheets over internet and intranet web applications.
| Reads data from Excel 95, 97, 2000, XP, and 2003 workbooks Reads and writes formulas (Excel 97 and later only) Generates spreadsheets in Excel 2000 format Supports font, number and date formatting Supports shading, bordering, and coloring of cells Modifies existing worksheets Is internationalized, enabling processing in almost any locale, country, language, or character encoding (formulas are currently only supported in English, French, Spanish, and German, but more can be added if translated) Supports copying of charts Supports insertion and copying of images into spreadsheets Supports logging with Jakarta Commons Logging, log4j, JDK 1.4 Logger, etc ...and much more. |
Technical notes ==> http://www.andykhan.com/jexcelapi/technotes.html
JExcelApi JavaDoc ==> http://jexcelapi.sourceforge.net/resources/javadocs/index.html
- Should be knowing basic concepts of Java.
- Should know how to set class path to use third party Api's.
Now we will see how to read a spread sheet using this API:
First of all we will create a spread sheet like the one below and store it in our local folder.
I am saving this file in "D:testmyFile.xls"
To read the spread sheet content using jxl Api, first we have to create an object called
Workbook. Once you create the Workbook then you will get access to individual sheets.
Note that these sheets are Zero indexed.
So you have to use some thing like workbook.getSheet(0);
Once you get the sheet then you can easily get the cells and their content as string.
If you want it is also possible to get the data with out changing the type as it is.
See the sample code below (SpreadsheetReader.java).
package com.jxl.dhanago;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
/**
* This Java program is used to read the spread sheet and print it in
* console output.
*
* @author dhanago
*/
public class SpreadsheetReader
{
/**
* This method is used to read a spread sheet and print it in console.
*
* @param xlsPath
*/
public void readSpreadSheet( String xlsPath )
{
try
{
/*
* To read the spread sheet , first we have to create a workbook
* object like one shown below.
*/
Workbook workbook = Workbook.getWorkbook( new File( xlsPath ) );
/*
* then get the sheet index 0. Note the index starts with 0.
*/
Sheet sheet = workbook.getSheet( 0 );
/*
* get the cell form the sheet object like below.
*/
Cell cell00 = sheet.getCell( 0, 0 );
Cell cell01 = sheet.getCell( 0, 1 );
Cell cell02 = sheet.getCell( 0, 2 );
/*
* now we will display the cell values as string in console output.
*/
System.out.println( "Cell00 value: " + cell00.getContents() );
System.out.println( "Cell01 value: " + cell01.getContents() );
System.out.println( "Cell02 value: " + cell02.getContents() );
// free up memory
workbook.close();
}
catch (BiffException e)
{
e.printStackTrace();
}
catch (IOException e)
{
e.printStackTrace();
}
}
/**
* This is the main executable method used to test the spread sheet
* reader.
*
* @param args
*/
public static void main( String[] args )
{
SpreadsheetReader readSpreadsheet = new SpreadsheetReader();
String xlsPath = "D:\test\myFile.xls";
readSpreadsheet.readSpreadSheet( xlsPath );
}
}
OutPut for the above code:
Cell00 value: Name
Cell01 value: Muthukumar Dhanagopal
Cell02 value: Krish
The above code displays the cell values as string. How to get the same
data type and display them with out converting them to string. Is that
possible using this API?
Yes, it is possible.
Here is the code which does the same for you.
It checks the cell type for LABEL, NUMBER or DATE and then gets the value from
the cell type cast the value to that particular data type and displays it on the console.
package com.jxl.dhanago;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
/**
* This java program is used to get and display the xls data according to
* the type.
*
* @author dhanago
*/
public class ReadXLWithExactDataType
{
/**
* This method is used to display the xls data with exact type.
*
* @param xlsPath
*/
public void readDataWithType( String xlsPath )
{
try
{
/*
* To read the spread sheet , first we have to create a workbook
* object like one shown below.
*/
Workbook workbook = Workbook.getWorkbook( new File( xlsPath ) );
/*
* then get the sheet index 0. Note the index starts with 0.
*/
Sheet sheet = workbook.getSheet( 0 );
/*
* get the cell form the sheet object like below.
*/
Cell cell00 = sheet.getCell( 0, 0 );
if (cell00.getType() == CellType.LABEL)
{
System.out.println( "Type LABEL" );
LabelCell labelCell = (LabelCell) cell00;
System.out.println( "Label Cell: " + labelCell.getString() );
}
else if (cell00.getType() == CellType.NUMBER)
{
System.out.println( "Type NUMBER" );
NumberCell numberCell = (NumberCell) cell00;
System.out.println( "Number Cell: " + numberCell.getValue() );
}
else if (cell00.getType() == CellType.DATE)
{
System.out.println( "Type DATE" );
DateCell dateCell = (DateCell) cell00;
System.out.println( "Date Cell: " + dateCell.getDate() );
}
else
{
System.out.println( "Type not supported." );
}
/*
* now we will display the cell values as string in console output.
*/
System.out.println( "Cell00 value: " + cell00.getContents() );
// free up memory
workbook.close();
}
catch (BiffException e)
{
e.printStackTrace();
}
catch (IOException e)
{
e.printStackTrace();
}
}
/**
* This is the main executable method used to test the spread sheet
* reader.
*
* @param args
*/
public static void main( String[] args )
{
ReadXLWithExactDataType readXL = new ReadXLWithExactDataType();
String xlsPath = "D:\test\myFile.xls";
readXL.readDataWithType( xlsPath );
}
}
The Output for the above code is:
Type LABEL
Label Cell: Name
Cell00 value: Name
Workbook. Once you create the Workbook then you will get access to individual sheets.
Note that these sheets are Zero indexed.
So you have to use some thing like workbook.getSheet(0);
Once you get the sheet then you can easily get the cells and their content as string.
If you want it is also possible to get the data with out changing the type as it is.
See the sample code below (SpreadsheetReader.java).
package com.jxl.dhanago;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
/**
* This Java program is used to read the spread sheet and print it in
* console output.
*
* @author dhanago
*/
public class SpreadsheetReader
{
/**
* This method is used to read a spread sheet and print it in console.
*
* @param xlsPath
*/
public void readSpreadSheet( String xlsPath )
{
try
{
/*
* To read the spread sheet , first we have to create a workbook
* object like one shown below.
*/
Workbook workbook = Workbook.getWorkbook( new File( xlsPath ) );
/*
* then get the sheet index 0. Note the index starts with 0.
*/
Sheet sheet = workbook.getSheet( 0 );
/*
* get the cell form the sheet object like below.
*/
Cell cell00 = sheet.getCell( 0, 0 );
Cell cell01 = sheet.getCell( 0, 1 );
Cell cell02 = sheet.getCell( 0, 2 );
/*
* now we will display the cell values as string in console output.
*/
System.out.println( "Cell00 value: " + cell00.getContents() );
System.out.println( "Cell01 value: " + cell01.getContents() );
System.out.println( "Cell02 value: " + cell02.getContents() );
// free up memory
workbook.close();
}
catch (BiffException e)
{
e.printStackTrace();
}
catch (IOException e)
{
e.printStackTrace();
}
}
/**
* This is the main executable method used to test the spread sheet
* reader.
*
* @param args
*/
public static void main( String[] args )
{
SpreadsheetReader readSpreadsheet = new SpreadsheetReader();
String xlsPath = "D:\test\myFile.xls";
readSpreadsheet.readSpreadSheet( xlsPath );
}
}
OutPut for the above code:
Cell00 value: Name
Cell01 value: Muthukumar Dhanagopal
Cell02 value: Krish
The above code displays the cell values as string. How to get the same
data type and display them with out converting them to string. Is that
possible using this API?
Yes, it is possible.
Here is the code which does the same for you.
It checks the cell type for LABEL, NUMBER or DATE and then gets the value from
the cell type cast the value to that particular data type and displays it on the console.
package com.jxl.dhanago;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
/**
* This java program is used to get and display the xls data according to
* the type.
*
* @author dhanago
*/
public class ReadXLWithExactDataType
{
/**
* This method is used to display the xls data with exact type.
*
* @param xlsPath
*/
public void readDataWithType( String xlsPath )
{
try
{
/*
* To read the spread sheet , first we have to create a workbook
* object like one shown below.
*/
Workbook workbook = Workbook.getWorkbook( new File( xlsPath ) );
/*
* then get the sheet index 0. Note the index starts with 0.
*/
Sheet sheet = workbook.getSheet( 0 );
/*
* get the cell form the sheet object like below.
*/
Cell cell00 = sheet.getCell( 0, 0 );
if (cell00.getType() == CellType.LABEL)
{
System.out.println( "Type LABEL" );
LabelCell labelCell = (LabelCell) cell00;
System.out.println( "Label Cell: " + labelCell.getString() );
}
else if (cell00.getType() == CellType.NUMBER)
{
System.out.println( "Type NUMBER" );
NumberCell numberCell = (NumberCell) cell00;
System.out.println( "Number Cell: " + numberCell.getValue() );
}
else if (cell00.getType() == CellType.DATE)
{
System.out.println( "Type DATE" );
DateCell dateCell = (DateCell) cell00;
System.out.println( "Date Cell: " + dateCell.getDate() );
}
else
{
System.out.println( "Type not supported." );
}
/*
* now we will display the cell values as string in console output.
*/
System.out.println( "Cell00 value: " + cell00.getContents() );
// free up memory
workbook.close();
}
catch (BiffException e)
{
e.printStackTrace();
}
catch (IOException e)
{
e.printStackTrace();
}
}
/**
* This is the main executable method used to test the spread sheet
* reader.
*
* @param args
*/
public static void main( String[] args )
{
ReadXLWithExactDataType readXL = new ReadXLWithExactDataType();
String xlsPath = "D:\test\myFile.xls";
readXL.readDataWithType( xlsPath );
}
}
The Output for the above code is:
Type LABEL
Label Cell: Name
Cell00 value: Name
