การเรียกใช้สูตร (formula) ใน Excel ด้วย Apache POI

นอกจากการกำหนด font จัดรูปแบบต่างๆให้กับข้อความแล้ว ตัว Apache POI ยังสามารถเรียกใช้สูตรคำนวณต่างๆได้อีกด้วย ยกตัวอย่างสูตรที่เรียกใช้การบ่อยๆ เช่น :

• การรวม = SUM(Loc1:Locn) or = SUM(n1,n2,)
• การนับจำนวน  = COUNT(Loc1:Locn) or = COUNT(n1,n2,)
• การยกกำลัง = POWER(Loc1,Loc2) or = POWER(number, power)
• หาค่ามากสุด = MAX(Loc1:Locn) or = MAX(n1,n2,)
• การคูณ = PRODUCT(Loc1:Locn) or = PRODUCT(n1,n2,)
• ค่า Factorial = FACT(Locn) or = FACT(number)
• ค่า Absolute = ABS(Locn) or = ABS(number)
• วันปัจจุบัน =TODAY()
• ทำเป็นตัวเล็ก  = LOWER(Locn) or = LOWER(text)
• ค่า Square root = SQRT(locn) or = SQRT(number)

จากโคดตัวอย่างเราจะสร้างไฟล์ .xlsx ดังนั้น ผู้เขียนจึงใช้ Package XSSF แต่หากใครจะสร้างไฟล์ .xls เฉยๆให้เรียกใช้ Package HSSF นะครับ

ตัวอย่างโคด

package com.java.poi.excel;

import java.io.FileOutputStream;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class FormulaExcel {
    public static void main(String[] args) {
        try {
            // สร้าง object ของ excel
            XSSFWorkbook wb = new XSSFWorkbook();
            // สร้าง sheet
            XSSFSheet spreadsheet = wb.createSheet("Font Excel");
            // สร้างแถวแรก การนับแถวเริ่มจาก 0,1,2....
            XSSFRow row = spreadsheet.createRow(0);
            XSSFCell cell = row.createCell(1);
            cell.setCellValue("A =" );
            cell = row.createCell(2);
            cell.setCellValue(2);
            row = spreadsheet.createRow(1);
            cell = row.createCell(1);
            cell.setCellValue("B =");
            cell = row.createCell(2);
            cell.setCellValue(4);
            
            // สร้างศูตร SUM formula
            row = spreadsheet.createRow(3);
            cell = row.createCell(1);
            cell.setCellValue("Total =");
            cell = row.createCell(2);
            cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
            cell.setCellFormula("SUM(C2:C3)" );
            cell = row.createCell(3);
            cell.setCellValue("SUM(C2:C3)");
            
            // สร้างศูตร POWER formula
            row = spreadsheet.createRow(4);
            cell=row.createCell(1);
            cell.setCellValue("POWER =");
            cell = row.createCell(2);
            cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
            cell.setCellFormula("POWER(C2,C3)");
            cell = row.createCell(3);
            cell.setCellValue("POWER(C2,C3)");
            
            // สร้างศูตร MAX formula
            row = spreadsheet.createRow(5);
            cell = row.createCell(1);
            cell.setCellValue("MAX =");
            cell = row.createCell(2);
            cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
            cell.setCellFormula("MAX(C2,C3)");
            cell = row.createCell(3);
            cell.setCellValue("MAX(C2,C3)");
            
            // สร้างศูตร FACT formula
            row = spreadsheet.createRow(6);
            cell = row.createCell(1);
            cell.setCellValue("FACT =");
            cell = row.createCell(2);
            cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
            cell.setCellFormula("FACT(C3)");
            cell = row.createCell(3);
            cell.setCellValue("FACT(C3)");
            
            // สร้างศูตร SQRT formula
            row = spreadsheet.createRow(7);
            cell = row.createCell(1);
            cell.setCellValue("SQRT =");
            cell = row.createCell(2);
            cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
            cell.setCellFormula("SQRT(C5)");
            cell = row.createCell(3);
            cell.setCellValue("SQRT(C5)");
            
            // สร้างศูตร TODAY() formula
            row = spreadsheet.createRow(8);
            cell = row.createCell(1);
            cell.setCellValue("TODAY =");
            cell = row.createCell(2);
            XSSFCellStyle dateCellStyle = wb.createCellStyle(); // date format
            short df = wb.createDataFormat().getFormat("dd/MM/yyyy");
            dateCellStyle.setDataFormat(df);
            cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
            cell.setCellFormula("TODAY()");
            cell.setCellStyle(dateCellStyle);
            cell = row.createCell(3);
            cell.setCellValue("TODAY()");
            
            // รันสูตรทั้งหมด 
            wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
            
            // path ของไฟล์
            FileOutputStream out = new FileOutputStream("C:\\poi\\FormulaExcel.xlsx");
            wb.write(out);
            wb.close();
            out.close();
            System.out.println("Excel created successfully");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

ไฟล์ที่ถูกสร้าง

 ผลลัพธ์ที่ได้ 

About Nop

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.