PhpSpreadsheet/tests/PhpSpreadsheetTests/Reader/Ods/NumberFormatCallbackTest.php

<?php

declare(strict_types=1);

namespace PhpOffice\PhpSpreadsheetTests\Reader\Ods;

use PhpOffice\PhpSpreadsheet\Reader\Ods as OdsReader;
use PhpOffice\PhpSpreadsheet\Shared\File;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Writer\Ods as OdsWriter;
use PhpOffice\PhpSpreadsheet\Writer\Ods\Cell\Style;
use PHPUnit\Framework\TestCase;

class NumberFormatCallbackTest extends TestCase
{
    private string $tempfile = '';

    protected function tearDown(): void
    {
        if ($this->tempfile !== '') {
            unlink($this->tempfile);
            $this->tempfile = '';
        }
    }

    public function testCallbacks(): void
    {
        $spreadsheetOld = new Spreadsheet();
        $sheet = $spreadsheetOld->getActiveSheet();
        $sheet->getCell('A1')->setValue(1.23);
        $sheet->getCell('A2')->setValue(2.34);
        $sheet->getStyle('A2')->getNumberFormat()
            ->setFormatCode('¤#,##0.000');
        $sheet->getCell('A3')->setValue(3.45);
        $sheet->getStyle('A3')->getNumberFormat()
            ->setFormatCode(NumberFormat::FORMAT_CURRENCY_USD);
        $this->tempfile = File::temporaryFileName();
        $writer = new OdsWriter($spreadsheetOld);
        $writer->useAdditionalNumberFormats([
            '¤#,##0.000' => $this->genericCurrencyWrite(...),
        ]);
        $writer->save($this->tempfile);
        $spreadsheetOld->disconnectWorksheets();
        $reader = new OdsReader();
        $reader->setFormatCallback(
            $this->genericCurrencyRead(...)
        );
        $spreadsheet = $reader->load($this->tempfile);
        $newSheet = $spreadsheet->getActiveSheet();
        self::assertSame('1.23', $newSheet->getCell('A1')->getFormattedValue());
        self::assertSame('¤2.340', $newSheet->getCell('A2')->getFormattedValue(), 'needs writer and reader callbacks');
        self::assertSame('$3.45 ', $newSheet->getCell('A3')->getFormattedValue());
        $spreadsheet->disconnectWorksheets();
    }

    private function genericCurrencyWrite(Style $obj, string $name): void
    {
        $writer = $obj->getWriter();
        $writer->startElement('number:currency-style');
        $writer->writeAttribute('style:name', $name);
        $writer->writeElement('number:text', '¤');
        $writer->startElement('number:number');
        $writer->writeAttribute('number:decimal-places', '3');
        $writer->writeAttribute('number:min-decimal-places', '3');
        $writer->writeAttribute('number:min-integer-digits', '1');
        $writer->endElement(); // number:number
        $writer->endElement(); // number:currency-style
    }

    private function genericCurrencyRead(string $type, string $text): string
    {
        if ($type === 'float' && mb_substr($text, 0, 1) === '¤') {
            return '¤#,##0.000';
        }

        return '';
    }

    public function testMmmdyyyy(): void
    {
        $spreadsheetOld = new Spreadsheet();
        $sheet = $spreadsheetOld->getActiveSheet();
        $sheet->getCell('A1')->setValue(46000); // Dec 9, 2025
        $sheet->getStyle('A1')
            ->getNumberFormat()
            ->setFormatCode('mmm d, yyyy');
        $sheet->getCell('A2')->setValue(46000); // Dec 9, 2025
        $sheet->getStyle('A2')
            ->getNumberFormat()
            ->setFormatCode(
                NumberFormat::FORMAT_DATE_DATETIME_BETTER
            );
        $writer = new OdsWriter($spreadsheetOld);
        $this->tempfile = File::temporaryFileName();
        $writer = new OdsWriter($spreadsheetOld);
        $writer->useAdditionalNumberFormats([
            'mmm d, yyyy' => self::mmmdyyyy(...),
        ]);
        $writer->save($this->tempfile);
        $spreadsheetOld->disconnectWorksheets();
        $reader = new OdsReader();
        $spreadsheet = $reader->load($this->tempfile);
        $newSheet = $spreadsheet->getActiveSheet();
        // no formatCallback used, so we can't duplicate custom format,
        // but we will use a similar format,
        // with same number of year digits as was written
        self::assertSame('9-Dec-2025', $newSheet->getCell('A1')->getFormattedValue());
        self::assertSame('2025-12-09 00:00', $newSheet->getCell('A2')->getFormattedValue());
        $spreadsheet->disconnectWorksheets();
    }

    private static function mmmdyyyy(Style $obj, string $name): void
    {
        $writer = $obj->getWriter();
        $writer->startElement('number:date-style');
        $writer->writeAttribute('style:name', $name);
        $writer->startElement('number:month');
        $writer->writeAttribute('number:textual', 'true');
        $writer->endElement(); // number:month
        $writer->writeElement('number:text', ' ');
        $writer->startElement('number:day');
        $writer->endElement(); // number:day
        $writer->writeElement('number:text', ', ');
        $writer->startElement('number:year');
        $writer->writeAttribute('number:style', 'long');
        $writer->endElement(); // number:year
        $writer->endElement(); // number:date-style
    }

    public function testUnsupportedFormats(): void
    {
        $spreadsheetOld = new Spreadsheet();
        $sheet = $spreadsheetOld->getActiveSheet();
        $sheet->getCell('A1')->setValue(46000); // Dec 9, 2025
        $sheet->getStyle('A1')
            ->getNumberFormat()
            ->setFormatCode('mmm d, yyyy');
        $sheet->getCell('A2')->setValue(46000.5); // Dec 9, 2025 noon
        $sheet->getStyle('A2')
            ->getNumberFormat()
            ->setFormatCode('mmm d, yyyy hh:mm');
        $sheet->getCell('A3')->setValue(0.5); // noon
        $sheet->getStyle('A3')
            ->getNumberFormat()
            ->setFormatCode(' hh:mm ');
        $writer = new OdsWriter($spreadsheetOld);
        $this->tempfile = File::temporaryFileName();
        $writer = new OdsWriter($spreadsheetOld);
        $writer->save($this->tempfile);
        $spreadsheetOld->disconnectWorksheets();
        $reader = new OdsReader();
        $spreadsheet = $reader->load($this->tempfile);
        $newSheet = $spreadsheet->getActiveSheet();
        // Writer would have used default formats
        self::assertSame('9-Dec-2025', $newSheet->getCell('A1')->getFormattedValue());
        self::assertSame('9-Dec-2025 12:00:00', $newSheet->getCell('A2')->getFormattedValue());
        self::assertSame('12:00:00', $newSheet->getCell('A3')->getFormattedValue());
        $spreadsheet->disconnectWorksheets();
    }

    public function testReadAndWriteDateCallbacks(): void
    {
        $spreadsheetOld = new Spreadsheet();
        $sheet = $spreadsheetOld->getActiveSheet();
        $sheet->getCell('A1')->setValue(46000); // Dec 9, 2025
        $sheet->getCell('A2')->setValue(46000); // Dec 9, 2025
        $sheet->getStyle('A1')
            ->getNumberFormat()
            ->setFormatCode('  mmm d, yyyy  ');
        $sheet->getStyle('A2')
            ->getNumberFormat()
            ->setFormatCode('yyyy-mm-dd');
        $writer = new OdsWriter($spreadsheetOld);
        $this->tempfile = File::temporaryFileName();
        $writer = new OdsWriter($spreadsheetOld);
        $writer->useAdditionalNumberFormats([
            '  mmm d, yyyy  ' => self::mmmdyyyyspacesWrite(...),
        ]);
        $writer->save($this->tempfile);
        $spreadsheetOld->disconnectWorksheets();
        $reader = new OdsReader();
        $reader->setFormatCallback(
            $this->mmmdyyyspacesRead(...)
        );
        $spreadsheet = $reader->load($this->tempfile);
        $newSheet = $spreadsheet->getActiveSheet();
        self::assertSame('  Dec 9, 2025  ', $newSheet->getCell('A1')->getFormattedValue(), 'format set in read callback');
        self::assertSame('2025-12-09', $newSheet->getCell('A2')->getFormattedValue(), 'format not set in read callback');
        $spreadsheet->disconnectWorksheets();
    }

    private static function mmmdyyyyspacesWrite(Style $obj, string $name): void
    {
        $writer = $obj->getWriter();
        $writer->startElement('number:date-style');
        $writer->writeElement('number:text', '  ');
        $writer->writeAttribute('style:name', $name);
        $writer->startElement('number:month');
        $writer->writeAttribute('number:textual', 'true');
        $writer->endElement(); // number:month
        $writer->writeElement('number:text', ' ');
        $writer->startElement('number:day');
        $writer->endElement(); // number:day
        $writer->writeElement('number:text', ', ');
        $writer->startElement('number:year');
        $writer->writeAttribute('number:style', 'long');
        $writer->writeElement('number:text', '  ');
        $writer->endElement(); // number:year
        $writer->endElement(); // number:date-style
    }

    private function mmmdyyyspacesRead(string $type, string $text): string
    {
        if ($type === 'date' && 1 === preg_match('/^  [A-Za-z]+ \d\d?, \d\d\d\d/  ', $text)) {
            return '  mmm d, yyyy  ';
        }

        return '';
    }
}