Di java kadang - kadang kita membutuhkan fitur membaca file yang di buat di luar java. Kali ini saya ingin membahas "bekerja" dengan MS Excel dan Java
Saya membuat dokumen ini di basiskan dengan user yang sudah memiliki "working environment", karena ini, saya tidak membahas installation eclipse/netbeans atau IDE lainnya di mesin masing - masing.
Untuk fitur ini ada beberapa Third party libraries di butuhkan, yaitu:
  • dom4j-1.6.1.jar
  • xmlbeans-2.3.0.jar
  • poi-3.8-20120326.jar
  • poi-ooxml-3.8-20120326.jar
  • poi-ooxml-schemas-3.8-20120326.jar

Anda bisa meng-download libraries di atas melalui: http://poi.apache.org/download.html
Code di bawah ini akan memberi contoh pembuatan / pembacaan MS Excel file dengan java:
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 
public class ReadWriteExcelFile {
 
public static void readXLSFile() throws IOException
{
InputStream ExcelFileToRead = new FileInputStream("C:/Test.xls");
HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);
 
HSSFSheet sheet=wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;
 
Iterator rows = sheet.rowIterator();
 
while (rows.hasNext())
{
row=(HSSFRow) rows.next();
Iterator cells = row.cellIterator();

while (cells.hasNext())
{
cell=(HSSFCell) cells.next();

if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
{
System.out.print(cell.getStringCellValue()+" ");
}
else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
{
System.out.print(cell.getNumericCellValue()+" ");
}
else
{
//U Can Handel Boolean, Formula, Errors
}
}
System.out.println();
}

}

public static void writeXLSFile() throws IOException {

String excelFileName = "C:/Test.xls";//name of excel file
 
String sheetName = "Sheet1";//name of sheet
 
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetName) ;
 
//iterating r number of rows
for (int r=0;r < 5; r++ )
{
HSSFRow row = sheet.createRow(r);

//iterating c number of columns
for (int c=0;c < 5; c++ )
{
HSSFCell cell = row.createCell(c);

cell.setCellValue("Cell "+r+" "+c);
}
}

FileOutputStream fileOut = new FileOutputStream(excelFileName);

//write this workbook to an Outputstream.
wb.write(fileOut);
fileOut.flush();
fileOut.close();
}

public static void readXLSXFile() throws IOException
{
InputStream ExcelFileToRead = new FileInputStream("C:/Test.xlsx");
XSSFWorkbook wb = new XSSFWorkbook(ExcelFileToRead);

XSSFWorkbook test = new XSSFWorkbook();

XSSFSheet sheet = wb.getSheetAt(0);
XSSFRow row;
XSSFCell cell;
 
Iterator rows = sheet.rowIterator();
 
while (rows.hasNext())
{
row=(XSSFRow) rows.next();
Iterator cells = row.cellIterator();
while (cells.hasNext())
{
cell=(XSSFCell) cells.next();

if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
{
System.out.print(cell.getStringCellValue()+" ");
}
else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
{
System.out.print(cell.getNumericCellValue()+" ");
}
else
{
//U Can Handel Boolean, Formula, Errors
}
}
System.out.println();
}

}

public static void writeXLSXFile() throws IOException {

String excelFileName = "C:/Test.xlsx";//name of excel file
 
String sheetName = "Sheet1";//name of sheet
 
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet(sheetName) ;
 
//iterating r number of rows
for (int r=0;r < 5; r++ )
{
XSSFRow row = sheet.createRow(r);
 
//iterating c number of columns
for (int c=0;c < 5; c++ )
{
XSSFCell cell = row.createCell(c);

cell.setCellValue("Cell "+r+" "+c);
}
}
 
FileOutputStream fileOut = new FileOutputStream(excelFileName);
 
//write this workbook to an Outputstream.
wb.write(fileOut);
fileOut.flush();
fileOut.close();
}
 
public static void main(String[] args) throws IOException {

writeXLSFile();
readXLSFile();

writeXLSXFile();
readXLSXFile();
 
}
 
}

Agar bisa membaca "Embeded" file di dalam MS Excel file, code berikutnya bisa di gunakan:
HSSF:

  POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream("excel_with_embeded.xls"));
  HSSFWorkbook workbook = new HSSFWorkbook(fs);
  for (HSSFObjectData obj : workbook.getAllEmbeddedObjects()) {
      //the OLE2 Class Name of the object
      String oleName = obj.getOLE2ClassName();
      if (oleName.equals("Worksheet")) {
          DirectoryNode dn = (DirectoryNode) obj.getDirectory();
          HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(dn, fs, false);
          //System.out.println(entry.getName() + ": " + embeddedWorkbook.getNumberOfSheets());
      } else if (oleName.equals("Document")) {
          DirectoryNode dn = (DirectoryNode) obj.getDirectory();
          HWPFDocument embeddedWordDocument = new HWPFDocument(dn, fs);
          //System.out.println(entry.getName() + ": " + embeddedWordDocument.getRange().text());
      }  else if (oleName.equals("Presentation")) {
          DirectoryNode dn = (DirectoryNode) obj.getDirectory();
          SlideShow embeddedPowerPointDocument = new SlideShow(new HSLFSlideShow(dn, fs));
          //System.out.println(entry.getName() + ": " + embeddedPowerPointDocument.getSlides().length);
      } else {
          if(obj.hasDirectoryEntry()){
              // The DirectoryEntry is a DocumentNode. Examine its entries to find out what it is
              DirectoryNode dn = (DirectoryNode) obj.getDirectory();
              for (Iterator entries = dn.getEntries(); entries.hasNext();) {
                  Entry entry = (Entry) entries.next();
                  //System.out.println(oleName + "." + entry.getName());
              }
          } else {
              // There is no DirectoryEntry
              // Recover the object's data from the HSSFObjectData instance.
              byte[] objectData = obj.getObjectData();
          }
      }
  }

 XSSF:

  XSSFWorkbook workbook = new XSSFWorkbook("excel_with_embeded.xlsx");
  for (PackagePart pPart : workbook.getAllEmbedds()) {
      String contentType = pPart.getContentType();
      // Excel Workbook - either binary or OpenXML
      if (contentType.equals("application/vnd.ms-excel")) {
          HSSFWorkbook embeddedWorkbook = new HSSFWorkbook(pPart.getInputStream());
      }
      // Excel Workbook - OpenXML file format
      else if (contentType.equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")) {
          OPCPackage docPackage = OPCPackage.open(pPart.getInputStream());
          XSSFWorkbook embeddedWorkbook = new XSSFWorkbook(docPackage);
      }
      // Word Document - binary (OLE2CDF) file format
      else if (contentType.equals("application/msword")) {
          HWPFDocument document = new HWPFDocument(pPart.getInputStream());
      }
      // Word Document - OpenXML file format
      else if (contentType.equals("application/vnd.openxmlformats-officedocument.wordprocessingml.document")) {
          OPCPackage docPackage = OPCPackage.open(pPart.getInputStream());
          XWPFDocument document = new XWPFDocument(docPackage);
      }
      // PowerPoint Document - binary file format
      else if (contentType.equals("application/vnd.ms-powerpoint")) {
          HSLFSlideShow slideShow = new HSLFSlideShow(pPart.getInputStream());
      }
      // PowerPoint Document - OpenXML file format
      else if (contentType.equals("application/vnd.openxmlformats-officedocument.presentationml.presentation")) {
          OPCPackage docPackage = OPCPackage.open(pPart.getInputStream());
          XSLFSlideShow slideShow = new XSLFSlideShow(docPackage);
      }
      // Any other type of embedded object.
      else {
          System.out.println("Unknown Embedded Document: " + contentType);
          InputStream inputStream = pPart.getInputStream();
      }
  }
 


Comments




Leave a Reply