Apache POI ve Excel

Aşağıda Apache POI ile ilgili aldığım notlar bulunuyor.

• XSSFWorkbook xlsx formatındaki dosyaları okumak/yazmak için kullanılıyor.
• org.apache.poi.ss.usermodel  paketi çoğu sınıf için arayüzleri içeriyor. Bu arayüzleri kullanan diğer sınıflar org.apache.poi.hssf.usermodel veya org.apache.poi.xssf.usermodel paketlerinde bulunuyor.

Bir çok örnek te http://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/ss/examples/ConditionalFormats.java sayfasında mevcut.

WorkbookFactory
Dosya okuma veya yazma için bir Workbook açmak lazım. Bu HSSFWorkbook veya XSSFWorkbook olabilir.

• POIFSFileSystem veya NPOIFSFileSystem sınıfları HSSFWorkbook formatında dosya açıyor. HSSFWorkbook eski Excel formatı olduğu için artık pek kullanılmıyor.
• OPCPackage ise XSSFWorkbook formatında dosya açıyor.

Close Filehandle for Workbook (apache poi) sorusunda her bilinmeyen bir formattaki excel dosyasını açma örneği var.

Workbook
constructor
Workbook nesnesi yaratmak için stream vermemiz yeterli.

FileInputStream fin = new FileInputStream(new File("C:\\A2015.xlsx"));
XSSFWorkbook wb = new XSSFWorkbook(fin);

removeSheetAt
Örnekte ismi kritere uymayan sheet'ler siliniyor. Silmeye tersten başlamak lazım.

void removeOtherSheets(String sheetName, XSSFWorkbook book) {       
  for(int i=book.getNumberOfSheets()-1;i>=0;i--){
    XSSFSheet tmpSheet =book.getSheetAt(i);
    if(!tmpSheet.getSheetName().equals(sheetName)){
      book.removeSheetAt(i);
    }
  }       
}

XSSFSheet
Bu nesne WorkBook tarafından verilir.

XSSFSheet sheet = wb.getSheetAt(0);

Iterator
Bir sheet şöyle dolaşılır.

Iterator<Row> rowIt = sheet.rowIterator();
while (rowIt.hasNext()) {
  XSSFRow row = (XSSFRow) rowIt.next();

  // iterate through Columns
  Iterator<Cell> colIt = row.cellIterator();
  while (colIt.hasNext()) {
    Cell cell = colIt.next();
    System.out.println(cell.toString());
  }
  System.out.println();

}

Şöyle de dolaşılabilir. Bu dolaşmada null satırları atlamış oluruz.

for (Row row : sheet) {
  Cell cell = row.getCell(0);
  if (cell != null) {...}
}

Cell
toString metodu
Cell'in taşıdığı değeri string olarak döndürür.

Dosya Okuma 
xlsx Formatındaki Dosyayı Okumak
1. WorkbookFactory.create(OPCPackage pkg) metodunu kullanmak
Burada WorkBookFactory aslında XSSFWorkbook sınıfını yaratıyor.

FileInputStream fileInputStream = new FileInputStream(fileName);
//OPCPackage unzips .xlsx in order to read the xml
Workbook workBook = WorkbookFactory.create(OPCPackage.open(fileInputStream));
Sheet sheet = workBook.getSheetAt (0);

2. WorkbookFactory.create(InputStream inp) metodunu kullanmak

File inputFile = new File(fileName);
Workbook wb = WorkbookFactory.create(new FileInputStream(inputFile));

3. Arayüz kullanmadan XSSFWorkbook constructor metodlarından birisini kullanmak.

Şifre ile Korunan Bir Dosyayı Okumak
Burada bir örnek var.

xlsx Formatındaki Büyük Dosyayı SAX Parser Kullanarak Okumak
Gördüğüm en iyi örnek burada. XSSFReader sınıfını açıklayan örnek ise burada.

Okurken dikkat edilmesi gereken nokta ReadOnlySharedStringsTable sınıfı kullanılarak XSSFSheetXMLHandler sınıfına XML içindeki paylaşılan stringlerin geçilmesi. Bu tablo xlsx dokümaındaki \xl dizini altında bulunan sharedStrings.xml dokümanını kullanıyor.

 
xls Formatındaki Dosyayı Okumak
How do you get a cell's content in an Excel spreadsheet using Java? Apache POI  sorusuna verilen cevap aşağıda.

getNumberOfSheets metodu
Bu metod ile tüm sayfaları dolaşma imkanı var.
 

getCell metodu
Bu metodu kullanırken boş hücreler için aşağıdaki gibi Row.RETURN_BLANK_AS_NULL kullanabiliriz.

Dosyayı CellReference ile Okumak
getRow() ve getCell() metodları indeks olarak 0'dan başlayan sayılar alırlar. Ancak Excel'deki hücreler 1'den başlayan sayı alırlar. Örneği buradan aldım.

İsim verilen CellReference Aralıkları
Excel'de bazı hücre aralıklarına isim verilebiliyor. Bu isimleri görmek için aşağıdaki örneği kullanabiliriz.

XSSFName[] ranges = new XSSFName[workbook.getNumberOfNames()];
for (int i = 0; i < _wb.getNumberOfNames(); i++)
    ranges[i] = workbook.getNameAt(i);
   
AreaReference area = new AreaReference(ranges[0].getRefersToFormula());
//get all the cells within that range
CellReference[] cells = area.getAllReferencedCells();   

Dosyanın Tamamını Dolaşmak
Örneği buradan aldım. Burada dikkat edilmesi gereken nokta boş hücrelerin bazen xls dosyasına yazılmaması. Bu gibi durumlarda iterator yerine index kullanmak daha iyi. Buradaki soruda benzer bir cevap bulabilirsiniz

String fname = "C:\\myExcel.xlsx";
InputStream inp = new FileInputStream(fname);
Workbook wb = WorkbookFactory.create(inp);
Sheet sheet = wb.getSheetAt(0);

Iterator rows = sheet.rowIterator();
StringBuilder sb = new StringBuilder();
while (rows.hasNext()) {
    Row row = (Row) rows.next();
    Iterator<Cell> cells = row.cellIterator();
    while (cells.hasNext()) {
        Cell cell = cells.next();
        sb.append(cell.toString()).append(";");
    }
    sb.append("\n");
}
inp.close();

Dosyadaki Resimleri Okuma
Örnek:

Dosya Yazma
Büyük Dosyaları Yazma
Büyük dosyalar üretmek için http://poi.apache.org/spreadsheet/how-to.html adresinde örnek verildiği gibi SXSSFWorkbook sınıfını kullanmak lazım. Zaten bu sınıfın açıklamasında da "Streaming version of XSSFWorkbook implementing the "BigGridDemo" strategy. " yazıyor.


Eğer SXSSFWorkbook okuma işlemi için kullanılırsa burada gösterildiği gibi exception alırız.

Bir Dosya Oluşturma

Workbook workbook = WorkbookFactory.create(new File("file.xlsx"))

Boş bir dosya oluşturmak için aşağıdaki kod da kullanılabilir. openOrCreate() metodunun kullanıldığına dikkat etmek lazım.

OPCPackage opcPackage = OPCPackage.openOrCreate(file)
wb = new XSSFWorkbook(opcPackage);

Dosyaya AutoFilter Oluşturma
Örneği buradan aldım. İlk hücre başlığın olduğu yerden başlamalı ve son hücreye kadar devam etmeli.

sheet.setAutoFilter(CellRangeAddress.valueOf("A1:D10")); 

Bir Hücreye String Değer Atamak

SXSSFWorkbook workbook;//Yukarıdaki gibi oluştur
Sheet sheet = workbook.createSheet("sheet1");
Row row = sheet.createRow(9);//10. satır
Cell cell = row.createCell(3);//4 sütun
cell.setCellValue("label");

Bir Hücreye Sayısal Değer Atamak

Cell cell = row.createCell(3, Cell.CELL_TYPE_NUMERIC);//4 sütun
cell.setValue (100);

Bir Hücreye Formül Atamak

Cell cell = row.createCell(3, Cell.CELL_TYPE_FORMULA);//4 sütun
cell.setCellFormula ("SUM(D3:D4)");

Formüllerde $ işareti arkasındaki satır/sütun veya sayısı mutlak hale getirir. Örneğin $A1 formülü kopyalansa bile A sütunu sabit kalır. $A$1 şeklindeki kullanımda ise A1 hücresi sabit kalır.

Hücreye Style Atama
Hücreden Workbook'a Erişme
CellStyle nesnesi sadece Workbook tarafından yaratılabiliyor. Eğer Cell arayüzünden Workbook arayüzüne erişmek istersek aşağıdakine benzer bir kod kullanabiliriz.

CellStyle newCellStyle = newCell.getSheet().getWorkbook().createCellStyle();

Style Nesneleri 1 Kere Yaratılmalı
Bunun sebebini bilmiyorum ancak Style nesneleri bir kere yaratılmalı ve gerekirse bir yerde saklanmalı.

Style Nesneleri Hücrelere Atanmalı
Eğer bir satıra style atanırsa ve daha sonra o satırda bir hücre yaratılırsa, yeni hücre içinde bulunduğu satırın style'ını devralmaz. Hücreye style tekrar atanmalıdır.

Bir Hücreye Arka Plan Rengi Atamak

CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());//yeşil renk
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

Eğer istenirse indekslenmemiş bir renk te atanabilir. Örnek :

CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFillForegroundColor(new XSSFColor(new Color(255, 255, 255)));//siyah renk
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

Bir Hücreye Font Atamak

//create font
Font headerFont = workbook.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short) 12);

//create ceyll style
CellStyle headerStyle = workbook.createCellStyle();
style.setFont(headerFont);

Cell cell = //create cell
cell.setCellStyle(headerStyle);

Formüller
FormulaEvaluator
Bir çok hücredeki değerleri değiştirdikten sonra tüm formülleri yeniden hesaplamak için aşağıdaki gibi yapabiliriz.