poi 사용 엑셀 계산식 사용하기

복잡한 엑셀 수식을 거쳐서 나온 결과값 데이터가 필요한 경우가 있다.

이런 경우 그 복잡하고 많은 수식을 로직으로 옮기기 힘들때가 많다.

그럴때 수식이 담긴 엑셀 파일을 불러와서 셀 값만 변경하여 결과값을 받아올 수 있게 만든 소스.

// 엑셀파일

File file = new File(servletContext.getRealPath("/WEB-INF/excel/test.xlsx"));

// 엑셀 파일 오픈

XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(file));

FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();

List list = loop 돌리면서 계산해야할 데이터 list;

for(int i=0; i<list.size(); i++){    

    // index는 0부터 시작

    Row row = wb.getSheetAt(1).getRow(3);

    

    OxygenDto data = list.get(i);

    

    String serial = data.getSerial();

    String name = CmmnUtil.getString(data.getName(),"");

    float temper = Float.valueOf(data.getTemper());

    

    // 셀값 변경 - 일반적인 값은 그냥 이렇게 바꾸면 된다.

    row.getCell(15).setCellValue(temper);//온도

    

    // 입력한 온도(row.getCell(15)) 에 따라 효율(row.getCell(17)) 이 바뀌는 엑셀 파일이 있다고 가정하면

    // 수식이 들어가 있는 셀(효율 구하는 셀~)의 경우는 아래와 같이 해야 계산이 된다.

    // evaluateInCell 메소드의 경우 계산하고 나온 result값으로 그 셀을 덮어써버린다.

    // 따라서 계산해야할 데이터가 여러개일 경우 이렇게 쓰면 첫번째 놈만 계산되고 두번째부터는 계산이 안된다.

    //evaluator.evaluateInCell(row.getCell(17)); 

    

    //evaluateAll 메소드로 모든 formula 셀 자동 계산.. loop 돌리려면 이 메소드를 사용해야한다. 결과값으로 셀을 덮어쓰지 않는다.

    evaluator.evaluateAll();

    

     // 콘솔 출력

    logger.debug("serial["+ serial +"] 계산후 온도 : " + row.getCell(15) + " 효율: " + row.getCell(17).getNumericCellValue());

    

    //DB 데이터 입력

}

메이븐

<!-- 엑셀 작업용 library 시작-->

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi</artifactId>

    <version>3.9</version>

</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi-ooxml</artifactId>

    <version>3.9</version>

</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi-ooxml-schemas</artifactId>

    <version>3.9</version>

</dependency>

<!-- https://mvnrepository.com/artifact/dom4j/dom4j -->

<dependency>

    <groupId>dom4j</groupId>

    <artifactId>dom4j</artifactId>

    <version>1.6.1</version>

</dependency>

<!-- 엑셀 작업용 library 끝 -->