Formulas এবং Function Management

Apache POI লাইব্রেরি ব্যবহার করে আপনি Excel ফাইলের Formulas (সূত্র) এবং Functions পরিচালনা করতে পারেন। Excel এ বিভিন্ন ধরণের সূত্র থাকে যা ডেটাকে গাণিতিক বা যুক্তিসম্মতভাবে প্রসেস করে, এবং এই সূত্রগুলো Apache POI এর মাধ্যমে আপনার Java প্রোগ্রাম থেকে ব্যবহার করা সম্ভব।

1. Formula ব্যবহার করা

Formula হল সেই সূত্র যা Excel সেলে গাণিতিক বা লজিক্যাল হিসাব বা প্রসেসিং করতে ব্যবহৃত হয়। Apache POI আপনাকে Excel ফাইলের সেলে Formula সেট করতে এবং পরবর্তী সময়ে সেই Formula এর ফলাফল গ্রহণ করতে সাহায্য করে।

1.1 Formula সেট করা

এখানে একটি উদাহরণ দেওয়া হল, যেখানে একটি সেলে Formula হিসেবে একটি গাণিতিক হিসাব নির্ধারণ করা হয়েছে:

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

import java.io.FileOutputStream;
import java.io.IOException;

public class ExcelFormulaExample {
    public static void main(String[] args) throws IOException {
        // Excel Workbook তৈরি করা
        XSSFWorkbook workbook = new XSSFWorkbook();
        
        // Sheet তৈরি করা
        Sheet sheet = workbook.createSheet("Sheet1");
        
        // Row এবং Cell তৈরি করা
        Row row = sheet.createRow(0);
        Cell cell1 = row.createCell(0);
        Cell cell2 = row.createCell(1);
        Cell cell3 = row.createCell(2);
        
        // কিছু ডেটা যোগ করা
        cell1.setCellValue(10);  // A1 সেলে মান 10
        cell2.setCellValue(20);  // B1 সেলে মান 20
        
        // Formula ব্যবহার করা
        cell3.setCellFormula("A1+B1");  // C1 সেলে A1 এবং B1 এর যোগফল
        
        // Excel ফাইল সেভ করা
        FileOutputStream out = new FileOutputStream("formulaExample.xlsx");
        workbook.write(out);
        out.close();
        
        workbook.close();
        System.out.println("Formula সেট করা সম্পন্ন!");
    }
}

এখানে:

• cell3.setCellFormula("A1+B1"); – এখানে Formula সেট করা হয়েছে যেখানে A1 এবং B1 সেলের মান যোগ করা হবে।

1.2 Formula রেজাল্ট পড়া

Excel ফাইলটি খুলে যখন আপনি Formula এর সেলটি দেখবেন, তখন সেই সেলের মান হিসাব অনুযায়ী অটোমেটিক্যালি আপডেট হয়ে যাবে। তবে আপনি যদি Java কোডে Formula এর ফলাফল চান, তবে আপনাকে FormulaEvaluator ব্যবহার করতে হবে:

import org.apache.poi.ss.formula.FormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;

public class ReadFormulaResult {
    public static void main(String[] args) throws IOException {
        // Excel ফাইল লোড করা
        FileInputStream file = new FileInputStream("formulaExample.xlsx");
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        
        // Sheet এবং Row পাওয়া
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        
        // Formula Evaluator তৈরি করা
        FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
        
        // Formula সেলের ফলাফল পাওয়া
        Cell formulaCell = row.getCell(2);  // C1 সেল
        System.out.println("Formula Result: " + formulaEvaluator.evaluate(formulaCell).getNumberValue());
        
        workbook.close();
        file.close();
    }
}

এখানে:

• FormulaEvaluator ব্যবহার করে Formula সেলের হিসাবকৃত ফলাফল বের করা হয়েছে।

2. অন্য ধরনের Functions এবং Formulas

Excel এ অনেক ধরনের বিল্ট-ইন Functions রয়েছে যেমন SUM, AVERAGE, IF, VLOOKUP, ইত্যাদি। আপনি এই Functions গুলোকেও Apache POI এর মাধ্যমে ব্যবহার করতে পারেন। নিচে কিছু সাধারণ Function এর উদাহরণ দেওয়া হল:

2.1 SUM Function

// C1 সেলে A1 এবং B1 এর যোগফল (SUM Function)
cell3.setCellFormula("SUM(A1:B1)");

2.2 AVERAGE Function

// C1 সেলে A1 থেকে A5 পর্যন্ত মানের গড় (AVERAGE Function)
cell3.setCellFormula("AVERAGE(A1:A5)");

2.3 IF Function

// C1 সেলে A1 এর মান যদি 10 এর বেশি হয় তবে "Pass" নয়তো "Fail" লিখবে (IF Function)
cell3.setCellFormula("IF(A1>10, \"Pass\", \"Fail\")");

2.4 VLOOKUP Function

// C1 সেলে A1 এর মানের সাথে মেলানো B1 থেকে B5 পর্যন্ত মানের সাথে মিলিয়ে দেখবে (VLOOKUP Function)
cell3.setCellFormula("VLOOKUP(A1, B1:B5, 1, FALSE)");

3. ফর্মুলার জন্য রেফারেন্স ব্যবস্থাপনা

যখন আপনি ফর্মুলা ব্যবহার করবেন, তখন সেলের মধ্যে সঠিকভাবে রেফারেন্স দেওয়ার বিষয়টি খুব গুরুত্বপূর্ণ। Excel ফরম্যাটে, সেলের রেফারেন্স একটি নির্দিষ্ট প্যাটার্ন অনুসরণ করে (যেমন A1, B2 ইত্যাদি)। POI এর মাধ্যমে আপনি ডাইনামিক্যালি সেলের রেফারেন্সের পরিবর্তন করতে পারেন এবং সেই রেফারেন্সগুলিকে Formula এর মধ্যে ব্যবহার করতে পারেন।

4. Error Handling for Formulas

Excel ফাইলের মধ্যে Formula নির্ধারণ করতে গিয়ে ভুল রেফারেন্স বা ইনভ্যালিড ফর্মুলা থাকলে, আপনি FormulaEvaluator এর মাধ্যমে এর ফলাফল চেক করতে পারবেন এবং প্রয়োজনে ত্রুটি ধরতে পারবেন।

FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();
Cell cell = row.getCell(2);  // Formula সেল
try {
    double result = formulaEvaluator.evaluate(cell).getNumberValue();
    System.out.println("Formula Result: " + result);
} catch (Exception e) {
    System.out.println("Error in Formula: " + e.getMessage());
}

সারাংশ

Apache POI লাইব্রেরি দিয়ে আপনি Excel ফাইলের মধ্যে Formula এবং Function ব্যবহার করে ডেটার উপর গণনা বা প্রক্রিয়াকরণ করতে পারেন। আপনি Formula এর মাধ্যমে গাণিতিক হিসাব যেমন যোগফল, গড়, IF স্টেটমেন্ট, VLOOKUP ইত্যাদি ব্যবহার করতে পারবেন। এছাড়া, FormulaEvaluator ব্যবহার করে Formula এর ফলাফলও পাওয়া যাবে।

• Formula এবং Function ব্যবহার করতে setCellFormula মেথড ব্যবহার করা হয়।
• FormulaEvaluator ব্যবহার করে Formula এর ফলাফল পড়া যায়।

এটি আপনাকে Excel ফাইলের শক্তিশালী গাণিতিক ও লজিক্যাল প্রসেসিং করতে সক্ষম করবে।