<?php

namespace PhpOffice\PhpSpreadsheet\Cell;

use PhpOffice\PhpSpreadsheet\Calculation\Calculation;
use PhpOffice\PhpSpreadsheet\Calculation\Engine\FormattedNumber;
use PhpOffice\PhpSpreadsheet\RichText\RichText;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Shared\StringHelper;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class AdvancedValueBinder extends DefaultValueBinder implements IValueBinder
{
    /**
     * Bind value to a cell.
     *
     * @param Cell $cell Cell to bind value to
     * @param mixed $value Value to bind in cell
     *
     * @return bool
     */
    public function bindValue(Cell $cell, $value = null)
    {
        if ($value === null) {
            return parent::bindValue($cell, $value);
        } elseif (is_string($value)) {
            // sanitize UTF-8 strings
            $value = StringHelper::sanitizeUTF8($value);
        }

        // Find out data type
        $dataType = parent::dataTypeForValue($value);

        // Style logic - strings
        if ($dataType === DataType::TYPE_STRING && !$value instanceof RichText) {
            //    Test for booleans using locale-setting
            if (StringHelper::strToUpper($value) === Calculation::getTRUE()) {
                $cell->setValueExplicit(true, DataType::TYPE_BOOL);

                return true;
            } elseif (StringHelper::strToUpper($value) === Calculation::getFALSE()) {
                $cell->setValueExplicit(false, DataType::TYPE_BOOL);

                return true;
            }

            // Check for fractions
            if (preg_match('/^([+-]?)\s*(\d+)\s?\/\s*(\d+)$/', $value, $matches)) {
                return $this->setProperFraction($matches, $cell);
            } elseif (preg_match('/^([+-]?)(\d*) +(\d*)\s?\/\s*(\d*)$/', $value, $matches)) {
                return $this->setImproperFraction($matches, $cell);
            }

            $decimalSeparatorNoPreg = StringHelper::getDecimalSeparator();
            $decimalSeparator = preg_quote($decimalSeparatorNoPreg, '/');
            $thousandsSeparator = preg_quote(StringHelper::getThousandsSeparator(), '/');

            // Check for percentage
            if (preg_match('/^\-?\d*' . $decimalSeparator . '?\d*\s?\%$/', preg_replace('/(\d)' . $thousandsSeparator . '(\d)/u', '$1$2', $value))) {
                return $this->setPercentage(preg_replace('/(\d)' . $thousandsSeparator . '(\d)/u', '$1$2', $value), $cell);
            }

            // Check for currency
            if (preg_match(FormattedNumber::currencyMatcherRegexp(), preg_replace('/(\d)' . $thousandsSeparator . '(\d)/u', '$1$2', $value), $matches, PREG_UNMATCHED_AS_NULL)) {
                // Convert value to number
                $sign = ($matches['PrefixedSign'] ?? $matches['PrefixedSign2'] ?? $matches['PostfixedSign']) ?? null;
                $currencyCode = $matches['PrefixedCurrency'] ?? $matches['PostfixedCurrency'];
                $value = (float) ($sign . trim(str_replace([$decimalSeparatorNoPreg, $currencyCode, ' ', '-'], ['.', '', '', ''], preg_replace('/(\d)' . $thousandsSeparator . '(\d)/u', '$1$2', $value)))); // @phpstan-ignore-line

                return $this->setCurrency($value, $cell, $currencyCode); // @phpstan-ignore-line
            }

            // Check for time without seconds e.g. '9:45', '09:45'
            if (preg_match('/^(\d|[0-1]\d|2[0-3]):[0-5]\d$/', $value)) {
                return $this->setTimeHoursMinutes($value, $cell);
            }

            // Check for time with seconds '9:45:59', '09:45:59'
            if (preg_match('/^(\d|[0-1]\d|2[0-3]):[0-5]\d:[0-5]\d$/', $value)) {
                return $this->setTimeHoursMinutesSeconds($value, $cell);
            }

            // Check for datetime, e.g. '2008-12-31', '2008-12-31 15:59', '2008-12-31 15:59:10'
            if (($d = Date::stringToExcel($value)) !== false) {
                // Convert value to number
                $cell->setValueExplicit($d, DataType::TYPE_NUMERIC);
                // Determine style. Either there is a time part or not. Look for ':'
                if (strpos($value, ':') !== false) {
                    $formatCode = 'yyyy-mm-dd h:mm';
                } else {
                    $formatCode = 'yyyy-mm-dd';
                }
                $cell->getWorksheet()->getStyle($cell->getCoordinate())
                    ->getNumberFormat()->setFormatCode($formatCode);

                return true;
            }

            // Check for newline character "\n"
            if (strpos($value, "\n") !== false) {
                $cell->setValueExplicit($value, DataType::TYPE_STRING);
                // Set style
                $cell->getWorksheet()->getStyle($cell->getCoordinate())
                    ->getAlignment()->setWrapText(true);

                return true;
            }
        }

        // Not bound yet? Use parent...
        return parent::bindValue($cell, $value);
    }

    protected function setImproperFraction(array $matches, Cell $cell): bool
    {
        // Convert value to number
        $value = $matches[2] + ($matches[3] / $matches[4]);
        if ($matches[1] === '-') {
            $value = 0 - $value;
        }
        $cell->setValueExplicit((float) $value, DataType::TYPE_NUMERIC);

        // Build the number format mask based on the size of the matched values
        $dividend = str_repeat('?', strlen($matches[3]));
        $divisor = str_repeat('?', strlen($matches[4]));
        $fractionMask = "# {$dividend}/{$divisor}";
        // Set style
        $cell->getWorksheet()->getStyle($cell->getCoordinate())
            ->getNumberFormat()->setFormatCode($fractionMask);

        return true;
    }

    protected function setProperFraction(array $matches, Cell $cell): bool
    {
        // Convert value to number
        $value = $matches[2] / $matches[3];
        if ($matches[1] === '-') {
            $value = 0 - $value;
        }
        $cell->setValueExplicit((float) $value, DataType::TYPE_NUMERIC);

        // Build the number format mask based on the size of the matched values
        $dividend = str_repeat('?', strlen($matches[2]));
        $divisor = str_repeat('?', strlen($matches[3]));
        $fractionMask = "{$dividend}/{$divisor}";
        // Set style
        $cell->getWorksheet()->getStyle($cell->getCoordinate())
            ->getNumberFormat()->setFormatCode($fractionMask);

        return true;
    }

    protected function setPercentage(string $value, Cell $cell): bool
    {
        // Convert value to number
        $value = ((float) str_replace('%', '', $value)) / 100;
        $cell->setValueExplicit($value, DataType::TYPE_NUMERIC);

        // Set style
        $cell->getWorksheet()->getStyle($cell->getCoordinate())
            ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_PERCENTAGE_00);

        return true;
    }

    protected function setCurrency(float $value, Cell $cell, string $currencyCode): bool
    {
        $cell->setValueExplicit($value, DataType::TYPE_NUMERIC);
        // Set style
        $cell->getWorksheet()->getStyle($cell->getCoordinate())
            ->getNumberFormat()->setFormatCode(
                str_replace('$', '[$' . $currencyCode . ']', NumberFormat::FORMAT_CURRENCY_USD)
            );

        return true;
    }

    protected function setTimeHoursMinutes(string $value, Cell $cell): bool
    {
        // Convert value to number
        [$hours, $minutes] = explode(':', $value);
        $hours = (int) $hours;
        $minutes = (int) $minutes;
        $days = ($hours / 24) + ($minutes / 1440);
        $cell->setValueExplicit($days, DataType::TYPE_NUMERIC);

        // Set style
        $cell->getWorksheet()->getStyle($cell->getCoordinate())
            ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_TIME3);

        return true;
    }

    protected function setTimeHoursMinutesSeconds(string $value, Cell $cell): bool
    {
        // Convert value to number
        [$hours, $minutes, $seconds] = explode(':', $value);
        $hours = (int) $hours;
        $minutes = (int) $minutes;
        $seconds = (int) $seconds;
        $days = ($hours / 24) + ($minutes / 1440) + ($seconds / 86400);
        $cell->setValueExplicit($days, DataType::TYPE_NUMERIC);

        // Set style
        $cell->getWorksheet()->getStyle($cell->getCoordinate())
            ->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_TIME4);

        return true;
    }
}

• [F] AddressHelper.php
  Delete
• [F] AddressRange.php
  Delete
• [F] AdvancedValueBinder.php
  Delete
• [F] Cell.php
  Delete
• [F] CellAddress.php
  Delete
• [F] CellRange.php
  Delete
• [F] ColumnRange.php
  Delete
• [F] Coordinate.php
  Delete
• [F] DataType.php
  Delete
• [F] DataValidation.php
  Delete
• [F] DataValidator.php
  Delete
• [F] DefaultValueBinder.php
  Delete
• [F] Hyperlink.php
  Delete
• [F] IgnoredErrors.php
  Delete
• [F] IValueBinder.php
  Delete
• [F] RowRange.php
  Delete
• [F] StringValueBinder.php
  Delete

Contact Section

Get in Touch with Us for Any Questions or Support

We’d love to hear from you! Whether you have questions, feedback, or need support, our team is here to help. Reach out to us via email, phone, or visit our office. We’re committed to providing you with exceptional service and ensuring your experience with NFC Pay is seamless and satisfying. Let’s connect!

Name*

Email*

Message*

Send Message

Our Location

20-22 Wenlock Road, England, N1 7GU

Call us on: +03601 885399

Our office hours Monday–Friday, 9am - 9pm

Email us directly

support@example.com