PHPSpreadsheet isn't doing all the calculations after editing some cell values

58 views Asked by At

Using PHPSpreadsheet I am editing an existing spreadsheet. The sheet contains 7 cells that I change the value of. After that I save the spreadsheet to a separate file.

However, after opening the file, all the values are changed to the expected values, but the cells where a calculation should be done isn't correct. The number is too high.

This is my code:

<?php

function get_calculation_annuiteit_with_user_input()
{
    $user_input = $_POST['user_input'];
    if (!$user_input) {
        wp_send_json_error();
    }

    $reader             = new Reader();
    $spreadsheet        = $reader->load(plugin_dir_path(__FILE__) . '../assets/sheet-annuiteit.xlsx');
    $user_input_decoded = json_decode(stripslashes($user_input), true);

    setCellValue($spreadsheet, 'D11', $user_input_decoded['loan-amount-text']);
    setCellValue($spreadsheet, 'D12', $user_input_decoded['calculate-annuity-per-select']);
    $interest_rate = validateAndSanitize($user_input_decoded['interest-amount-text']);
    $spreadsheet->getSheet(1)->getStyle('D13')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00);
    setCellValue($spreadsheet, 'D13', $interest_rate);
    setCellValue($spreadsheet, 'E13', $user_input_decoded['interest-type-select']);
    setCellValue($spreadsheet, 'D14', (int) $user_input_decoded['term-payment-duration-number']);
    setCellValue($spreadsheet, 'E14', $user_input_decoded['term-payment-period-select']);
    setCellValue($spreadsheet, 'D15', $user_input_decoded['payment-moment-select']);

    $activeSheet = $spreadsheet->getSheet(1);
    $cellRanges  = ['D11', 'D14', 'G26'];
    foreach ($cellRanges as $cell) {
        $spreadsheet->getSheet(1)->getStyle($cell)->getNumberFormat()->setFormatCode('0.00');
    }

    $cellRangesPerGroup = [
        'E20:E22', 'F21:F22', 'G26:G26', 'H26:H26', 'I26:I26',
    ];

    $data = [];
    foreach ($cellRangesPerGroup as $group) {
        array_push($data, $activeSheet->rangeToArray($group, null, true, true, true));
    }

    Calculation::getInstance($spreadsheet)->clearCalculationCache();

    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save(plugin_dir_path(__FILE__) . 'foobar.xlsx');

    $formatted_data = format_annuiteit_data($data);

    wp_send_json($formatted_data);
    wp_die();
}

I tried clearing the Calculation Engine and check if all cells are formatted the same as before.

0

There are 0 answers