How to get html of a cell using PHPExcel?

Question

I've tried to import an xlsx file to array, but noticed, that it contains simple text whereas if I open it in OpenOffice, I can see some words in the cell are bold.

#items: array:2 [
    "url" => "theurl"
    "meta_title" => "text with one bold word"
]

Is there a way to get somehow text with <b>one</b> bold word?

PHPExcel wiki says nothing about it. Its valueBinder applies only to csv and html files.

Answer 1

With this small adaptions this solution works with the successor PhpSpreadsheet:

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\RichText;
use PhpOffice\PhpSpreadsheet\RichText\Run;
use PhpOffice\PhpSpreadsheet\Style\Font;

class RichTextService {

    /**
     * Takes array where of CSS properties / values and converts to CSS stri$
     *
     * @param array
     * @return string
     */
    private function assembleCSS($pValue = array())
    {
        $pairs = array();
        foreach ($pValue as $property => $value) {
            $pairs[] = $property . ':' . $value;
        }
        $string = implode('; ', $pairs);
        return $string;
    }

    /**
     * Create CSS style (Font)
     *
     * @param    Font        $pStyle            Font
     * @return    array
     */
    private function createCSSStyleFont(Font $pStyle)
    {
        // Construct CSS
        $css = array();
        // Create CSS
        if ($pStyle->getBold()) {
            $css['font-weight'] = 'bold';
        }
        if ($pStyle->getUnderline() != Font::UNDERLINE_NONE && $pStyle->getStrikethrough()) {
            $css['text-decoration'] = 'underline line-through';
        } elseif ($pStyle->getUnderline() != Font::UNDERLINE_NONE) {
            $css['text-decoration'] = 'underline';
        } elseif ($pStyle->getStrikethrough()) {
            $css['text-decoration'] = 'line-through';
        }
        if ($pStyle->getItalic()) {
            $css['font-style'] = 'italic';
        }
        $css['color']       = '#' . $pStyle->getColor()->getRGB();
        $css['font-family'] = '\'' . $pStyle->getName() . '\'';
        $css['font-size']   = $pStyle->getSize() . 'pt';
        return $css;
    }

    /**
     * 
     * @param RichText|string $value
     * @return string
     */
    public function getHTML($value) {
        if(is_string($value)) {
            return $value;
        }
        $cellData = '';
        if ($value instanceof PhpOffice\PhpSpreadsheet\RichText\RichText) {
            // Loop through rich text elements
            $elements = $value->getRichTextElements();
            foreach ($elements as $element) {
                // Rich text start?
                print_r($element);
                if ($element instanceof PhpOffice\PhpSpreadsheet\RichText\Run) {
                    $cellData .= '<span style="' . $this->assembleCSS($this->createCSSStyleFont($element->getFont())) . '">';
                    if ($element->getFont()->getSuperScript()) {
                        $cellData .= '<sup>';
                    } elseif ($element->getFont()->getSubScript()) {
                        $cellData .= '<sub>';
                    }
                }
                // Convert UTF8 data to PCDATA
                $cellText = $element->getText();
                $cellData .= htmlspecialchars($cellText);
                if ($element instanceof PhpOffice\PhpSpreadsheet\RichText\Run) {
                    if ($element->getFont()->getSuperScript()) {
                        $cellData .= '</sup>';
                    } elseif ($element->getFont()->getSubScript()) {
                        $cellData .= '</sub>';
                    }
                    $cellData .= '</span>';
                }
            }
        }

        return str_replace("\n", "<br>\n", $cellData);
    }
}

Answer 2

Thanks to Mark Baker. The HTML Writer code helps to create simple converter. Usage:

$richtextService = new RichTextService();

$value = $reader->getActiveSheet()->getCell('F2')->getValue();
$html = $richtextService->getHTML($value); // html in it

And the class:

namespace App\Services;

use PHPExcel_RichText;
use PHPExcel_RichText_Run;
use PHPExcel_Style_Font;

class RichTextService{

    /**
     * Takes array where of CSS properties / values and converts to CSS stri$
     *
     * @param array
     * @return string
     */
    private function assembleCSS($pValue = array())
    {
        $pairs = array();
        foreach ($pValue as $property => $value) {
            $pairs[] = $property . ':' . $value;
        }
        $string = implode('; ', $pairs);
        return $string;
    }

    /**
     * Create CSS style (PHPExcel_Style_Font)
     *
     * @param    PHPExcel_Style_Font        $pStyle            PHPExcel_Style_Font
     * @return    array
     */
    private function createCSSStyleFont(PHPExcel_Style_Font $pStyle)
    {
        // Construct CSS
        $css = array();
        // Create CSS
        if ($pStyle->getBold()) {
            $css['font-weight'] = 'bold';
        }
        if ($pStyle->getUnderline() != PHPExcel_Style_Font::UNDERLINE_NONE && $pStyle->getStrikethrough()) {
            $css['text-decoration'] = 'underline line-through';
        } elseif ($pStyle->getUnderline() != PHPExcel_Style_Font::UNDERLINE_NONE) {
            $css['text-decoration'] = 'underline';
        } elseif ($pStyle->getStrikethrough()) {
            $css['text-decoration'] = 'line-through';
        }
        if ($pStyle->getItalic()) {
            $css['font-style'] = 'italic';
        }
        $css['color']       = '#' . $pStyle->getColor()->getRGB();
        $css['font-family'] = '\'' . $pStyle->getName() . '\'';
        $css['font-size']   = $pStyle->getSize() . 'pt';
        return $css;
    }

    /**
     * 
     * @param PHPExcel_RichText|string $value
     * @return string
     */
    public function getHTML($value) {
        if(is_string($value)) {
            return $value;
        }

        $cellData = '';
        if ($value instanceof PHPExcel_RichText) {
            // Loop through rich text elements
            $elements = $value->getRichTextElements();
            foreach ($elements as $element) {
                // Rich text start?
                if ($element instanceof PHPExcel_RichText_Run) {
                    $cellData .= '<span style="' . $this->assembleCSS($this->createCSSStyleFont($element->getFont())) . '">';
                    if ($element->getFont()->getSuperScript()) {
                        $cellData .= '<sup>';
                    } elseif ($element->getFont()->getSubScript()) {
                        $cellData .= '<sub>';
                    }
                }
                // Convert UTF8 data to PCDATA
                $cellText = $element->getText();
                $cellData .= htmlspecialchars($cellText);
                if ($element instanceof PHPExcel_RichText_Run) {
                    if ($element->getFont()->getSuperScript()) {
                        $cellData .= '</sup>';
                    } elseif ($element->getFont()->getSubScript()) {
                        $cellData .= '</sub>';
                    }
                    $cellData .= '</span>';
                }
            }
        }

        return str_replace("\n", "<br>\n", $cellData);
    }
}