Monday, 7 July 2014

How to create, modify and save Excel spread sheets in Java using Apach POI

    Java doesn't have built in support for accessing excel files. Various libraries are avialable in java for this purpose. In this turoial we are using Apache PoI Api.
you can download the Api from apache poi website http://poi.apache.org/download.html .The latest version is POI 3.10 . Don't forget to add these files to your build path in eclipse. 

    Using Apache POI, you can create both Xls and Xlsx files. Prior to 2007, the format of all excel files is .xls and from 2007 the format is .xlsx . 

    The base class that represents a excel work book in Apache POI is Workbook, for creation of .xls files the required class is HSSFWorkbook, and for .xlsx files it is XSSFWorkbook. 


Now lets go into the details

1. How to create .xls/.xlsx work books ?

use HSSFWorkbook, XSSFWorkbook for creating xls and xlsx files respectively. 

Code Snippet 1 : 

creating xls workbook, with default settings. 

Workbook xlsWb = new HSSFWorkbook();

creating xls workbook, with default settings.

Workbook xlsxWb = new XSSFWorkbook();

2. How to create a sheet ?

The prerequisite to create a sheet is the creation of workbook. Once the workbook has been created, use HSSFSheet, XSSFSheet to create sheets for xls, xlsx files respectively. 



Code Snippet 2 : 

creating sheet for xls workbooks -

Sheet xlsSheet = xlsWb.createSheet("xls sheet"); // pass sheet's name as an argument to the createSheet().

creating sheet for .xlxs workbooks - 

Sheet xlsxSheet = xlsxWb.createSheet("xlsx sheet");// pass sheet's name as an argument to the createSheet().

3. How to create a Row in Sheet ?

The prerequisite to create a Row is creation of workbook and sheet. Once both were created, use HSSFRow, XSSFRow to create rows for xls, xlsx sheets respectively. 



Code snippet 3 :

creating row in a xls sheet - 

Row xlsRow = xlsSheet.createRow(0); // creates first row - row index starts from 0. 

creatign row in a xlsx sheet - 

Row xlsxRow = xlsxSheet.createRow(0) // creates first row  

4. How to create cell and set it's value ? 

After creation of a row, use createCell(param1) and setCellValue(param1) methods for creation of cell and setting cell value respectively. 


Code snippet 4: 

creating cell and setting value in xls row: 

Cell xlsCell = xlsRow.createCell(0); 

setting cell value : 

xlsCell.setCellValue("speakingcs"); // you can pass a number, string, date, boolean variable as parameter. 

creating cell and setting value in xlsx row :

Cell xlsxCell = xlsxRow.createCell(0); // cell index starts from '0'.

setting cell Value : 

xlsxCell.setCellValue("speakingcs"); // you can pass a number, string, date, boolean variable as parameter. 


5. How to save Excel Workbook ?

You can save the Created excel file any where in your local system. if you want to save the file in "D" drive, specify the path as below . 

String path = "D:\\speakingcs.xls";

for workbooks created using HSSFWorkbook, the file extension should be ".xls", for workbooks of type XSSFWorkbook , the file extension is ".xlsx".

Use FileOutputStream class to save the file to specified path. 

FileOutputStream fop = new FileOutputStream(path);

call write(param1) method of the Workbook instance for eg. xlsWb by passing the OutputStream object. 

xlsWb.write(fop);

finally close OutputStream by calling it's close method. 

fop.close();

These are the basic artifacts to create a simple excel file with plain data. You can make use of these artifacts to create multiple rows, columns, sheets easily.

Complete Source Code :

package practice;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

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.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelCreation {

Workbook xlswb;
Workbook xlsxwb;
Sheet sh;
Row row;
Cell cell;
public static void main(String[] args) {
ExcelCreation ec = new ExcelCreation();
ec.createHssfExcel();
ec.createXssfExcel();
ec.saveExcels();
}

private void saveExcels() {
String xlsPath = "speakingcs.xls"; // for saving file to the current working                                                                              directory
String xlsxPath = "speakingcs.xlsx"; // for saving file to the current working                                                                             directory
try {
FileOutputStream fos = new FileOutputStream(xlsPath); // saves xls file to                                                                                                             disk
xlswb.write(fos);
fos.close();
fos = new FileOutputStream(xlsxPath); // saves xlsx file to disk
xlsxwb.write(fos);
fos.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

private void createXssfExcel() {
xlswb = new XSSFWorkbook();
sh = xlswb.createSheet();
row = sh.createRow(0);
cell = row.createCell(0);
cell.setCellValue("speaking cs");

}

private void createHssfExcel() {
xlsxwb = new HSSFWorkbook();
sh = xlsxwb.createSheet();
row = sh.createRow(0);
cell = row.createCell(0);
cell.setCellValue("speaking cs");
}


}

6. How to read xls/xlsx files using Apach POI ?

      Reading both xls & xlsx files from disk is very much simillar. For reading .xls files, you have to use Apach POIFSFileSystem and for .xlsx files Java's FileInputStream is enough. POIFSFileSystem would automatically closes the InputStream you have provided to it.

Reading xls files :

1. Create FileInputStream object and pass it as an argument to POIFSFileSystem's     constructor. Below is the code snippet.

// xlsPath represents the location of xls file on disk.
InputStream fis = new FileInputStream(xlsPath);
POIFSFileSystem poiFs = new POIFSFileSystem(fis);


2. Now open the Workbook instance by passing pOIFSFileSystem instance as an argument. Use HSSFWorkbook's constructor for creation of object.

Workbook Wb = new HSSFWorkbook(poiFs);

3. Now you have created workbook instance, you can see total no of sheets the workbook contains as below.

 int totalNoOfSheets = wb.getNumberOfSheets();

 Use the code snippet mentioned below to access first sheet.

// Reading first sheet.
 Sheet sheet1 = wb.getSheetAt(0); // pass sheet index as an argument.

 
4. You can get the total no of rows & access first row as shown below.

//Counting total no of rows.
int totalNoOfRows = sheet1.getPhysicalNumberOfRows();

// Accessing row 1
Row row1 = sheet1.getRow(0); // pass row index as an argument.

5. Use getPhysicalNumberOfCells() & getCell() methods to get number of cells the row contains, and a cell respectively.

// finding total no of cells in a row
int totalNoOfCols = row1.getPhysicalNumberOfCells();

// Accessing a column
Cell cell1 = row1.getCell(0); // pass cell index as an argument

6. if you want to find cell type, use getCellType() method on a Cell instance, just like below.

//finding the cell type.
cell1.getCellType(); // returns a numeric value.

0 represents type as numeric
1 represents type as String
2 represents type as formula
3 represents type as blank
4 represents type as boolean.

7. Always check cell type before getting values from cell.

if(cell1.getCellType() == 0) {
   System.out.println(cell1.getNumericCellValue());
 }


 8. If you want to read each cell value as a String type, call toString() method on cell instance.

 System.out.println(cell1.toString());

 Reading xlsx files :

 
 Reading xlsx files is quite simple, you don't need to use Apache's POIFSFileSystem. Below is the code snippet to access workbook instance.

// pass location of xlsx file as an argument
 InputStream fis = new FileInputStream(xlsxFile);

// use XSSFWorkbook for reading xlsx files.
Workbook wb = new XSSFWorkbook(fis);

The procedure for accessing sheets, rows, cells is just like above.




1 comment:

  1. This comment has been removed by a blog administrator.

    ReplyDelete

Note: only a member of this blog may post a comment.