java POi 이용해서 피벗차트 만들기

java 에서 피벗차트를 만드려면 우선 POI 라이브러리를 설피해야한다
jar파일을 다운받아 빌드패스에 추개해주던지, 다음과 같이 maven, gradle 설정을 추가해주자

pom.xml

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>5.2.0</version>
</dependency>
<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>5.2.0</version>
</dependency>

gradle -----

implementation 'org.apache.poi:poi:5.2.0'
implementation 'org.apache.poi:poi-ooxml:5.2.0'

데이터를 List로 받아와 피벗차트를 만드는 샘플소스이다.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

public void exportDataToExcel(List<Ccsjumgum> dataList, String filePath) {
    try (Workbook workbook = new XSSFWorkbook()) {
        Sheet sheet = workbook.createSheet("Data");

        // Create header row
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("Date");
        headerRow.createCell(1).setCellValue("Process");
        headerRow.createCell(2).setCellValue("Keyword");
        headerRow.createCell(3).setCellValue("Count");

        // Create data rows
        for (int i = 0; i < dataList.size(); i++) {
            Ccsjumgum entry = dataList.get(i);
            Row row = sheet.createRow(i + 1);

            row.createCell(0).setCellValue(entry.getDate().toString());
            row.createCell(1).setCellValue(entry.getProcess());
            row.createCell(2).setCellValue(entry.getKeyword());
            row.createCell(3).setCellValue(entry.getCount());
        }

        // Auto size columns
        for (int i = 0; i < 4; i++) {
            sheet.autoSizeColumn(i);
        }

        // Create pivot table
        createPivotTable(sheet);

        // Write the output to a file
        try (FileOutputStream fileOut = new FileOutputStream(filePath)) {
            workbook.write(fileOut);
        }

    } catch (IOException e) {
        e.printStackTrace();
    }
}

 

아래는 사용예제

import org.apache.poi.ss.usermodel.PivotTable;
import org.apache.poi.xssf.usermodel.XSSFSheet;

public void createPivotTable(Sheet sheet) {
    CellRangeAddress dataRange = new CellRangeAddress(0, sheet.getLastRowNum(), 0, 3);
    PivotTable pivotTable = sheet.createPivotTable(dataRange, new CellReference("F5"));

    // Configure pivot table
    pivotTable.addRowLabel(0); // Date
    pivotTable.addRowLabel(1); // Process
    pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 3, "Keyword Count"); // Count
}

 

POI는 뭐든 할 수 있다

고고!