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]
Leave a Reply