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.