Excelling in
Excel with Java
Tutorial Details:
Excelling in Excel with Java
Excelling in Excel with Java
By: By Elango Sundaram
Learn how to use the Jakarta POI
hether you have balance sheets, account information downloads, tax calculations, or pay slips, they all tend to come in Microsoft Excel. Non-IT professionals feel comfortable using Microsoft Excel as a data exchange technology. The Jakarta POI (Poor Obfuscation Implementation) API is a fantastic way for Java programmers to access Microsoft document formats. The most mature API from Jakarta POI is the HSSF (Horrible Spreadsheet Format) API, which accesses Microsoft Excel documents.
In this article, I walk you through the steps for creating and reading Excel documents, and for using fonts and cell styling?all using Java.
Note: You can download the source code for all the examples in this article from Resources .
POI terminology
The key terms associated with Jakarta POI are as follows:
POIFS (Poor Obfuscation Implementation File System): Java APIs for reading and writing OLE (Object Linking and Embedding) 2 compound document formats
HSSF (Horrible Spreadsheet Format): Java API to read Microsoft Excel
HDF (Horrible Document Format): Java API to read and write Microsoft Word 97
HPSF (Horrible Property Set Format): Java API for reading property sets using (only) Java
Create an Excel document
The Jakarta POI API can be used to create an Excel document programmatically. The important steps involved are:
Create a workbook: HSSFWorkbook workbook = new HSSFWorkbook();
Create a new worksheet in the workbook and name the worksheet "Java Excels": HSSFSheet sheet = workbook.createSheet("Java Excels");
Create a new row in the sheet: HSSFRow row = sheet.createRow((short)0);
Create a cell in the row: HSSFCell cell = row.createCell((short) 0);
Put some content in the cell: cell.setCellValue("Have a Cup of XL");
Write the workbook into the filesystem: workbook.write(fileOutputStream);
Read data from the Excel document
In this example, you'll see how to read values from an Excel document.
Let's assume this is our Excel sheet:
Employee Name
Specialization
Designation
Anbu
Programming
Senior Programmer
Jason
Banking Industry
Business Analyst
Ramesh
Databases
DBA
MackyB
Accounting
Delivery Head
The key steps in reading the Excel sheet are as follows:
Create a new Excel document reference: HSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead)); .
Refer to the sheet: By default, the first sheet in the Excel document is at reference 0: HSSFSheet sheet = workbook.getSheetAt(0); . A sheet can also be referred to by name. Let's assume that the Excel sheet has the default name "Sheet1". It can be referred to as follows: HSSFSheet sheet = workbook.getSheet("Sheet1"); .
Refer to a row: HSSFRow row = sheet.getRow(0); .
Refer to a cell in the row: HSSFCell cell = row.getCell((short)0); .
Get the values in that cell: cell.getStringCellValue(); .
A practical example
Now let's assume that we want to see the list of all declared methods and member variables in a jar file. It would be ideal to have a consolidated list of all information in one single file. We would like to view the information so that the class names are in the first column, declared fields in the second column, and declared methods in the third column, with the column headings appearing in red.
The program will have to complete the following activities:
Unzip the jar file
Read all classfiles in the jar file
Load the classes in the jar file
Using reflection, get the declared methods and fields
Write the class information into an Excel sheet using Jakarta POI
Let's concentrate on just the interesting steps of Jakarta POI usage:
Create a new Excel document: workbook = new HSSFWorkbook();
Make a worksheet in that document and give the worksheet a name: sheet = workbook.createSheet("Java Class Info");
Set the first three columns' widths: sheet.setColumnWidth((short)0,(short)10000 );
Create the header line: HSSFRow row = sheet.createRow((short)0);
Create and set font and cell style:
HSSFFont font = workbook.createFont();
font.setColor(HSSFFont.COLOR_RED);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Create the style
HSSFCellStyle cellStyle= workbook.createCellStyle();
cellStyle.setFont(font);
Use the cell style:
HSSFCell cell = row.createCell((short) 0);
cell.setCellStyle(cellStyle);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellValue("Class Name ");
Write the output file:
FileOutputStream fOut = new FileOutputStream(outputFile);
// Write the Excel sheet
workbook.write(fOut);
fOut.flush();
// Done deal. Close it.
fOut.close();
Summary
As demonstrated in this article, Java developers no longer need to wince at data in Excel sheets. We can programmatically access Excel documents. Have a cup of Java, and excel in Excel!
This page formated for crawlers and browsers that don't support scripts and tables.
Home
EZone
Read
Tutorial at: Click here to view the tutorial
Rate Tutorial: Excelling in
Excel with Java
View Tutorial: Excelling in
Excel with Java
Related
Tutorials:
 Accelerate your Java apps! - JavaWorld - September 1998
Accelerate your Java apps! - JavaWorld - September 1998
|
Java Tip 77: Enable copy and paste functionality
between Swing's JTables and Excel - JavaWorld
Java Tip 77: Enable copy and paste functionality
between Swing's JTables and Excel - JavaWorld
|
JavaWorld -
Java Tips
index
JavaWorld -
Java Tips
index
|
Web services hits
the Java scene,
Part 1
Web services hits
the Java scene,
Part 1
|
Think small: Java on Compaq's iPAQ
Think small: Java on Compaq's iPAQ
|
Interface Tool for Java
Interface Tool for Java
Interface Tool for Java is a tool that allows Java programs to communicate with ActiveX objects. It allows easy integration of ActiveX objects into a Java Environment
|
Interesting
concept ...
Interesting
concept ...
|
Excelling in
Excel with Java
Excelling in
Excel with Java
|
The HTML Renderer Shootout, Part 1
The HTML Renderer Shootout, Part 1
In this article, we will review 11 different HTML renderers, comparing their features, compliance, and speed; searching for the best one for any project.
|
Java 2D imaging for the Standard Widget Toolkit
Java 2D imaging for the Standard Widget Toolkit
Bring the power of 2D imaging to your Eclipse plug-ins
In this article, however, you'll learn how to have the best of both worlds. I'll demonstrate a simple technique that will allow you to paint Java
|
Java Mime Magic Library
Java Mime Magic Library
jMimeMagic is a Java library for determining the MIME type of files or streams.
|
They just work
Writing CSV files: CSVPrinter
This class makes it easy to output CSV. Given values, it will automatically determine if they need to be quoted and escape special characters. Comments can easily be written and correct line beginnings will be added.
|
Object-Oriented Language: Java / APIs (Classes & Libraries)
The Java Platform APIs are a set of essential interfaces that developers need to build their Java applications and applets. All Java Platfrom APIs are open and extensible, and are created by JavaSoft and industry-wide specialists in each target technology
|
Tiger and Beyond, the Future of the Java Platform
Part Two of an interview with Sun Microsystems' Sun Fellow, Graham Hamilton, explores Java 2 Platform, Standard Edition 5.0 (J2SE 5.0) and the future of the Java language.
|
Manage Database Metadata with POI and Excel
Set up quick and easy database metadata housekeeping with the Java Apache POI libraries and Microsoft Excel.
|
Welcome to Java Developers paradise!
Welcome to Java Developers paradise!
T his site contains many quality Java, JSP, RMI, MySQL downloads, tutorials, source codes and links to other java resources. We have large number of links to the tutorials on java which will help you learn java
|
Collection of Large Number of Java Interview Questions!
Collection of Large Number of Java Interview Questions!
Collection of Large Number of Java Interview Questions
The Core Java Interview Questions
The Jakarta Struts Interview Questions
|
Collection of Large Number of Java Sample Programs and Tutorials
Collection of Large Number of Java Sample Programs and Tutorials
Collection of Large Number of Java Sample Programs and Tutorials
HelloWorld Java Program
Simple Java Program for beginners that prints HelloWorld! on console.
|
Buy SuSe 9.1 Personal CDs in India from us. Suse 9.1 Personal is available with us.
Buy SuSe 9.1 Personal CDs in India from us. Suse 9.1 Personal is available with us.
SuSe 9.1 Personal Linux
Now Available SuSe 9.1 Personal CD's
SuSE 9.1 Personal Edition was based on the x86 port of the Personal Edition, and it includes all of
|
JPackIt JPackIt is a Java application for packaging a Java project into single executable package
Java Project in single jar, class or exe containing all java application resources and referenced libraries.
|
|
|
|
