Post Reply 
 
Thread Rating:
  • 1 Votes - 5 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Java API to write data in MS Excel Files using Apache POI with example
02-06-2011, 09:00 AM
Post: #1
Java API to write data in MS Excel Files using Apache POI with example
This article explains how to write a very basic java code to write into MS Excel files using with Apache POI API.

The POI project consists of APIs for manipulating various file formats based upon Microsoft's OLE 2 Compound Document format using pure Java. In short, you can read and write MS Excel files using Java.

But, this writeup will be more useful for the beginners who haven't use Apache POI API to write data into MS Excel

Before start using Apache POI ,lets look into how to install the Apache POI in your machine.

Install Apache POI

Download the Apache POI from the link: click here

Extract the archived files to some suitable directory into your machine.
Add the file ../poi-3.1-FINAL-20080629.jar to your CLASSPATH environment variable
Set up is done. Let's look into our first simple example program using Apache POI.
Create a Java file named WriteTOExcel.java with the following content:

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import org.apache.poi.hpsf.HPSFException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class WriteTOExcel {

public static void main(String[] args) throws HPSFException {
ArrayList data = new ArrayList();
ArrayList headers = new ArrayList();

File file123 = new File("C:\\samplexmls\\sample.xls");

headers.add("Name");
headers.add("Email");
headers.add("EN0");

for (int i = 0; i <= 5; i++) {
ArrayList cells = new ArrayList();
cells.add("NAME" + i);
cells.add("test_" + i + "@yahoo.com");
cells.add("ENO" + i);
data.add(cells);
}

exportToExcel("Test", headers, data, file123);
}

public static void exportToExcel(String sheetName, ArrayList headers,
ArrayList data, File outputFile) throws HPSFException {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName);

int rowIdx = 0;
short cellIdx = 0;

// Header
HSSFRow hssfHeader = sheet.createRow(rowIdx);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
for (Iterator cells = headers.iterator(); cells.hasNext(); ) {
HSSFCell hssfCell = hssfHeader.createCell(cellIdx++);
hssfCell.setCellStyle(cellStyle);
hssfCell.setCellValue((String) cells.next());
}
// Data
rowIdx = 1;
for (Iterator rows = data.iterator(); rows.hasNext(); ) {
ArrayList row = (ArrayList) rows.next();
HSSFRow hssfRow = sheet.createRow(rowIdx++);
cellIdx = 0;
for (Iterator cells = row.iterator(); cells.hasNext(); ) {
HSSFCell hssfCell = hssfRow.createCell(cellIdx++);
hssfCell.setCellValue((String) cells.next());
}
}

wb.setSheetName(0, sheetName, HSSFWorkbook.ENCODING_COMPRESSED_UNICODE);
try {
FileOutputStream outs = new FileOutputStream(outputFile);
wb.write(outs);
outs.close();
} catch (IOException e) {
throw new HPSFException(e.getMessage());
}

}
}


Output of the above code will be as follow:

A excel file named sample.xls will be created in the following folder C:\\samplexmls\\sample.xls as per the code .If you dont have the following path in your machine .You have to change file path in code .Change the below line in the code if you want the file path to be changed to ouput the excel.

File file123 = new File("C:\\samplexmls\\sample.xls");
Find all posts by this user
Quote this message in a reply
Post Reply 


Forum Jump:



Send your comments, Suggestions or Queries regarding this site at info@tutorials4u.net

Copyright © 2010 Tutorials4u.net All Rights Reserved