set formulae in phpspreadsheet cause Formula Error: Illegal character '~'

Question

I want to get word bookmark value in excel using phpspreadsheet. I has prepared word document set "test" as bm1 [bm1 is bookmark name]. Then, I use phpspreadsheet to set formulae link to the word bookmark text. It showing error illegal character '~'.But, when I try manually, it works.

<?php
require 'vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
$spreadsheet = new Spreadsheet();
$activeWorksheet = $spreadsheet->getActiveSheet();
$wordReportPath=dirname(__FILE__).'\report_output.docx';
//=Word.Document|'C:\xampp\htdocs\Project\test_excelGetWordBookmark.docx'!'!bm1'
$activeWorksheet->setCellValue('A1', "=Word.Document|'C:\xampp\htdocs\Project\test_excelGetWordBookmark.docx'!'!bm1'");

$writer = new Xlsx($spreadsheet);
$writer->save('excelFormLinkToWord.xlsx');

Error:

Uncaught PhpOffice\PhpSpreadsheet\Calculation\Exception: Worksheet!A1 -> Formula Error: Illegal character '~'

Answer 1

Add $writer->setPreCalculateFormulas(false); and change back slash to forward slash for value passed to setCellValue().

<?php
 require 'vendor/autoload.php';
 use PhpOffice\PhpSpreadsheet\Spreadsheet;
 use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 $spreadsheet = new Spreadsheet();
 $activeWorksheet = $spreadsheet->getActiveSheet();
 $wordReportPath=dirname(__FILE__).'\report_output.docx';
 $activeWorksheet->setCellValue('A1', "=Word.Document|'C:/xampp/htdocs/Project/test_excelGetWordBookmark.docx'!'!bm1'");

 $writer = new Xlsx($spreadsheet);
 $writer->setPreCalculateFormulas(false);//help to solve the error
 $writer->save('excelFormLinkToWord.xlsx');