Tutorial Writer & Reader Excel (Apache POI)

Untuk membuat maupun membaca file excel pada JAVA, salah satu framework yang bisa digunakan adalah Apache POI. Disini akan dibuat sedikit aplikasi sederhana untuk membuat file excel, dengan spesifikasi :

• Jdk-7u4-windows-x64
• Netbeans 7.1.1
• Apache POI 3.8

Menambahkan Apache POI di netbeans

• Pada tab projects, klik kanan folder libraries, pilih Add JAR/Folder

• Pilih file dalam folder Apache POI seperti pada gambar.

• Pilih file dalam folder Apache POI folder ooxml-lib

• Apabila berhasil, maka folder libraries akan bertambah sesuai file & folder yang ditambahkan tadi.

Membuat file EXCEL

Untuk membuat file xlsx, yang dibutuhkan adalah atribut :

• FileOutputStream (java.io.FileOutputStream)
• Workbook (org.apache.poi.ss.usermodel.Workbook)
• Sheet (org.apache.poi.ss.usermodel.Sheet)
• Row (org.apache.poi.ss.usermodel.Row)

Adapun perbedaan dalam pembuatan file xls atau xlsx, terletak ketika deklarasi workbook :

• Workbook workbook = new HSSFWorkbook (untuk XLS)
• Workbook wb = new XSSFWorkbook() (untuk XLSX)

[sourcecode language=”java”]
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelWriter {
public static void main(String args[]) {
FileOutputStream fileOutputStream = null;
try {
fileOutputStream = new FileOutputStream("excel_writer.xls";
Workbook workbook = new HSSFWorkbook();
// Workbook workbook = new XSSFWorkbook(); -> for xlsx
// create sheet with name sample_sheet
Sheet sheet = workbook.createSheet("sample_sheet";
// create row at 0 index
Row sampleRow = sheet.createRow(0);
// sampleRow.createCell(); will create cell at cell (at row 0)
sampleRow.createCell(0).setCellValue("string value";
sampleRow.createCell(1).setCellValue(new Date());
sampleRow.createCell(2).setCellValue(true);
sampleRow.createCell(3).setCellValue(2000.00);
workbook.write(fileOutputStream);
} catch (FileNotFoundException ex) {
Logger.getLogger(ExcelWriter.class.getName()).log(Level.SEVERE, null, ex);
} catch (IOException ex) {
Logger.getLogger(ExcelWriter.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
fileOutputStream.close();
} catch (IOException ex) {
Logger.getLogger(ExcelWriter.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
[/sourcecode]

Membaca file EXCEL

Untuk membuat file excel, yang dibutuhkan adalah atribut :

• InputStream (java.io.InputStream)
• Workbook (org.apache.poi.ss.usermodel.Workbook)
• Sheet (org.apache.poi.ss.usermodel.Sheet)
• Row (org.apache.poi.ss.usermodel.Row)

[sourcecode language=”java”]
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;

/**
*
* @author arif
*/
public class ExcelReader {

public static void main(String args[]) {
InputStream inputStream = null;
try {
// for xls
String excelPath = "excel_writer.xls";

// for xlsx
// String excelPath = "excel_writer.xlsx";
inputStream = new FileInputStream(excelPath);

Workbook workbook = WorkbookFactory.create(inputStream);

// get sheet
Sheet sheet = workbook.getSheetAt(0);
for (Row row : sheet) {
for (Cell cell : row) {
CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());
System.out.print(cellRef.formatAsString());
System.out.print(" – ";

switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
System.out.println(cell.getRichStringCellValue().getString());
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
System.out.println(cell.getDateCellValue());
} else {
System.out.println(cell.getNumericCellValue());
}
break;
case Cell.CELL_TYPE_BOOLEAN:
System.out.println(cell.getBooleanCellValue());
break;
case Cell.CELL_TYPE_FORMULA:
System.out.println(cell.getCellFormula());
break;
default:
System.out.println();
}
}
System.out.println("";
}
} catch (FileNotFoundException ex) {
Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex);
} catch (IOException | InvalidFormatException ex) {
Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
inputStream.close();
} catch (IOException ex) {
Logger.getLogger(ExcelReader.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
}
[/sourcecode]

download project disini