PHPOffice/PhpSpreadsheet · error · PhpOffice\PhpSpreadsheet\Writer\Exception

Syntax error: comma expected in function $function, arg #{$n

Error message

Syntax error: comma expected in function $function, arg #{$num_args}

What it means

Thrown by the Xls (Excel 5/BIFF8) writer's formula parser while it converts a cell formula into the binary token stream the .xls format requires. In Parser::func(), every argument after the first must be preceded by a ',' or ';' token before the closing ')' is reached; any other token at that position aborts the save. It means the formula string assigned to the cell is syntactically malformed - the error surfaces at save() time, not when the formula is set.

Source

Thrown at src/PhpSpreadsheet/Writer/Xls/Parser.php:1535

    /**
     * It parses a function call. It assumes the following rule:
     * Func -> ( Expr [,Expr]* ).
     *
     * @return mixed[] The parsed ptg'd tree on success
     */
    private function func(): array
    {
        $num_args = 0; // number of arguments received
        $function = strtoupper($this->currentToken);
        $result = ''; // initialize result
        $this->advance();
        $this->advance(); // eat the "("
        while ($this->currentToken !== ')') {
            if ($num_args > 0) {
                if ($this->currentToken === ',' || $this->currentToken === ';') {
                    $this->advance(); // eat the "," or ";"
                } else {
                    throw new WriterException("Syntax error: comma expected in function $function, arg #{$num_args}");
                }
                $result2 = $this->condition();
                $result = $this->createTree('arg', $result, $result2);
            } else { // first argument
                $result2 = $this->condition();
                $result = $this->createTree('arg', '', $result2);
            }
            ++$num_args;
        }
        if (!isset($this->functions[$function])) {
            throw new WriterException("Function $function() doesn't exist");
        }
        $args = $this->functions[$function][1];
        // If fixed number of args eg. TIME($i, $j, $k). Check that the number of args is valid.
        if (($args >= 0) && ($args != $num_args)) {
            throw new WriterException("Incorrect number of arguments in function $function() ");
        }

View on GitHub (pinned to 65b080eef4)

Solutions

  1. Fix the formula so every argument after the first is comma-separated, e.g. '=IF(A1>10,"big","small")'
  2. Check parenthesis balance and separator count in programmatically built formula strings before save
  3. Log which cell triggers it (cells are written in sorted coordinate order) to locate the malformed formula
  4. If the formula only matters to your app, write the precomputed value instead of the formula when exporting .xls

Example fix

// before
$sheet->setCellValue('A1', '=IF(A1>10 "big" "small")');

// after
$sheet->setCellValue('A1', '=IF(A1>10,"big","small")');
Defensive patterns

Strategy: validation

Validate before calling

/** Cheap lint: strip string literals, then every top-level argument
 * inside parentheses must be non-empty and comma-separated. */
function formulaSeparatorsLookValid(string $formula): bool
{
    if (!str_starts_with($formula, '=')) {
        return true; // not a formula, parser not involved
    }
    $clean = preg_replace('/"(?:[^"]|"")*"/', '', $formula) ?? $formula;
    // no two adjacent identifiers/references without an operator or comma between them
    return !preg_match('/[A-Za-z0-9_)\]]\s+[A-Za-z0-9_$]/', substr($clean, 1));
}

if (!formulaSeparatorsLookValid($cellFormula)) {
    throw new InvalidArgumentException("Malformed formula: $cellFormula");
}
$spreadsheet->getActiveSheet()->setCellValue('A1', $cellFormula);

Try / catch

try {
    $writer = IOFactory::createWriter($spreadsheet, 'Xls');
    $writer->save($path);
} catch (\PhpOffice\PhpSpreadsheet\Writer\Exception $e) {
    if (str_contains($e->getMessage(), 'comma expected')) {
        // message names the function and arg#, e.g. arg #2 of IF(...)
        throw new UserInputException('Formula syntax error: ' . $e->getMessage(), 0, $e);
    }
    throw $e;
}

Prevention

When it happens

Trigger: Saving to .xls a workbook containing a formula with missing argument separators, e.g. setCellValue('A1', '=IF(A1>10 "big" "small")'); dynamically concatenated formula strings that drop a comma; unbalanced parentheses that make the internal condition() parser stop before an argument.

Common situations: Formulas assembled from user input or templates that use locale-specific separators; typos in hand-built formula strings; formulas that work in the Calculation engine but are written in a shape the Xls parser cannot tokenize.

Related errors

AI-assisted analysis of PHPOffice/PhpSpreadsheet@65b080eef4 (2026-08-17). Data as JSON: /api/errors/da1982e22805ca82. Report an issue: GitHub.