mb_substr() expects parameter 3 to be integer, string given

[mail2nisam]

This is:

- [X] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

Get calculated value from a cell

What is the current behavior?

Getting the Error - mb_substr() expects parameter 3 to be integer, string given when I am trying to get values as array using toArray()

What are the steps to reproduce?

I am trying to read the content from a sheet using this code

 $spreadsheet = IOFactory::load('file_path');
 $activeSheet=$spreadsheet->getActiveSheet();
 $activeSheet->toArray();

The sheet having a cell with this formula where the error occurs,

=IF(ISERROR(VALUE(SUBSTITUTE(OFFSET(A9,-1,0,1,1),".",""))),"1",IF(ISERROR(FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))),TEXT(VALUE(OFFSET(A9,-1,0,1,1))+1,"#"),TEXT(VALUE(LEFT(OFFSET(A9,-1,0,1,1),FIND("`",SUBSTITUTE(OFFSET(A9,-1,0,1,1),".","`",1))-1))+1,"#")))

Which versions of PhpSpreadsheet and PHP are affected?

PHP 7.2
PHPOffice/PhpSpreadsheet - ^1.4

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[Dazeddotdnc]

I'm experiencing a similar error:
Warning: mb_substr() expects parameter 3 to be integer, string given in [...]\PhpSpreadsheet\Calculation\TextData.php on line 338

It seems to have something to do with this formula, which has worked fine when working with physical excel files. However, PHPspreadsheet throws this message if it is present.
=MID(MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1),1,FIND(" = ",MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1),1))

It sets the cell contents based on whatever the file name is. There is meant to be a part number before the text " = BOM (Costed).xls". Some files are named a little different, but the "=" is generally reliably located...so the formula searches for this and pulls a substring.

[matheusb-comp]

I also experienced this error.
It happened in PhpOffice\PhpSpreadsheet\Calculation\TextData, in function LEFT.

It was caused by this function, where the result of the FIND function was #VALUE!:

=LEFT(A901,FIND(" ",A901,1)-1)

The TextData's LEFT function then received $chars with value "#VALUE!", and the call to mb_string produced the Exception.

[sky93]

I have this issue too :(

[Dazeddotdnc]

Sky93,
Do you have some example code that replicates what you're seeing?
Alternatively... what are the cell's contents? Is the formula working in a normal excel file?

[sky93]

Sky93,
Do you have some example code that replicates what you're seeing?
Alternatively... what are the cell's contents? Is the formula working in a normal excel file?

I'm trying to create a simple excel with one formula. Like below:

$spreadsheet = new Spreadsheet();
$spreadsheet->getActiveSheet()->setCellValue('A1', '=LEFT(CELL("filename", A1), FIND("[",CELL("filename",A1))-1)');
$writer = new Xlsx($spreadsheet);
$writer->save('sky.xlsx');

This formula works fine when you use it in an EXISTING excel file. I also tried to load an excel file and used setCellValue there but no luck. This is the error I get:

PhpOffice\PhpSpreadsheet\Calculation\Exception: Worksheet!P2 -> mb_substr() expects parameter 3 to be int, string given in vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Cell/Cell.php:272
Stack trace:
#0 vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Worksheet/Worksheet.php(753): PhpOffice\PhpSpreadsheet\Cell\Cell->getCalculatedValue()
#1 vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(369): PhpOffice\PhpSpreadsheet\Worksheet\Worksheet->calculateColumnWidths()
#2 vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx/Worksheet.php(67): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeCols()
#3 vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx.php(274): PhpOffice\PhpSpreadsheet\Writer\Xlsx\Worksheet->writeWorksheet()
#4 example.php(10): PhpOffice\PhpSpreadsheet\Writer\Xlsx->save()

[Dazeddotdnc]

What does "FIND("[",CELL("filename",A1))" evaluate too?
Can you hard-code that to a known legit value for testing purposes?

It's been a while since I poked at this issue...I don't remember if I resolved my original problem or maybe I found another solution. But...I kinda feel like "FIND" might be a common link. I don't have access to that project at the moment to verify what was happening with mine. Just kinda...trying to reconnect stale memories and guessing at my original train of thought.

If your FIND evaluates to #VALUE!, maybe there is some difference in the way excel handles #VALUE! compared to phpoffice?
IE: Maybe excel just takes it to mean 0 or -1 whereas maybe phpoffice is saying "this is a string that reads #VALUE!"