POI로 Pivot테이블 만들기

POI를 써 온지는 꽤 오래 되었다. 주로 테이블 데이터를 엑셀 파일로 만드는 정도를 했는데  생각해 보면 그런 기능 정도는 그냥 csv 파일을 만들어도 되는 기능이었던것 같다.

AppNexus Join이후로는 통계 관련 요구사항이 없어 쓸 일이 없었는데 

얼마전에 Carrier Data Integration 모듈(?)을 개발하면서 

Data refresh전에 데이터 변경 내역을 보여주는 Excel을 만드는 게 어떨까 해서 Excel을 만들어 보았는데 

생각해 보니 이 기능을 실제 Integration Module 에 넣는게 어떨까 해서 다시 POI를 쓰게 되었다.

제일 중요한게 Pivot 테이블 생성 기능인데 

마지막으로 POI를 사용할때는 Pivot 기능이 없었던걸로 기억하는데 다행이 Pivot 테이블 생성하는 기능도 추가가 되었다.

다만 Pivot기능을 사용하기 위해서는   poi이 외에 poi-ooxml도 library에 추가해 주어어 햔다.

"org.apache.poi" % "poi" % "3.17",
"org.apache.poi" % "poi-ooxml" % "3.17",

이렇게  poi와 poi-ooxml을 추가한 후 Excel Sheet를 생성해서 데이터를 해당 Sheet에 저장했다.

val wb = new XSSFWorkbook
val sheet = wb.createSheet("Rawdata")
IPRanges.foreach(ipRange => {
  val row = sheet.createRow(rowIndex)
  row.createCell(0).setCellValue(ipRange("country").asInstanceOf[String])
  ...
  row.createCell(6).setCellFormula(s"F${rowIndex + 1}-E${rowIndex + 1}+1")
  rowIndex = rowIndex + 1
})

이렇게 데이터를 생성한 후 Pivot테이블을 위한 Sheet를 생성해서 Pivot테이블의 데이터 영역에 위의 데이터 Sheet를 설정하면 Pivot테이블이 생성된다.
val summarySheet = wb.createSheet("Summary")
val source = new AreaReference(s"Rawdata!A2:G${rowIndex}", SpreadsheetVersion.EXCEL2007)
val position = new CellReference("A3")
val pivot = summarySheet.createPivotTable(source, position)

Pivot테이블의 Rows는 아래와 같이 영역내의 몇 번째 Column인지를 지정하면 된다.

pivot.addRowLabel(0)
pivot.addRowLabel(1)

Pivot테이블의 Values는 아래와 같이 몇 번째 Column에 어떤 Function 을 적용할지를 지정하면 된다.

pivot.addColumnLabel(DataConsolidateFunction.SUM, 6)

헌데 Pivot테이블의 Columns 부분을 적용할 수 있는 함수를 찾을 수가 없었다.
( 내 Pivot테이블은 국가 , Carrier를 Row로 하고, 데이터에 있는 Before/After 데이터를 Column으로 데이터를 보여 주고 싶은데 이 기능은 Pivot의 Columns 을 통해서 적용이 된다.)

이것 저것 해보다가 안 되서 꽤 오랫동안 Googling을 했더니 아래와 같은 해결 책이 나왔다.

pivot.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(3).setAxis(
  org.openxmlformats.schemas.spreadsheetml.x2006.main.STAxis.AXIS_COL);

pivot.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(3).addNewItems();
pivot.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(3).getItems().addNewItem().setT(
  org.openxmlformats.schemas.spreadsheetml.x2006.main.STItemType.DEFAULT);

pivot.getCTPivotTableDefinition().addNewColFields().addNewField().setX(3);
데이터 테이블의 3번째 Column의 데이터를 Pivot Table의 Columns에 적용하려면 위에 처럼 몇 개의 함수를 다 선언해야 한다.

구글링하면서 다른 이의 API Naming이 잘못 되었다는 ( addColumnLable-> addValues ) 의견에 전적으로 동의한다. 
그리고 왜 Pivot 테이블의 Columns 부분을 저렇게 여러개의 함수를 선언하게 만들었는지 솔직히 이해가 가지 않는다.

뭐... 몇 가지 아쉬움이 있지만 어쨋든 Pivot테이블은 완성이 되었다.

한가지 아쉬운 점은... 엑셀을 오픈하면 Pivot테이블이 있는 Sheet을 Default로 보여주려고 했는데...

wb.setActiveSheet() 으로 Pivot테이블의 Sheet을 적용하면 엑셀 오픈할 때 에러가 발생해서 이 부분은 적용해 주지 못했다.

어쨋든... POI 덕분에 수작업도 줄일 수 있게 되어 뭐.. 기분은 나쁘지 않다.
POI Pivot관련 해서는 아래 페이지들을 참조 했다.

http://www.novixys.com/blog/excel-pivot-table-using-apache-poi/
https://stackoverflow.com/questions/35943812/xssfapache-poi-adding-multiple-column-label-from-single-column-value-in-pivo