Read Rich Text from Excel 2007 Cell

Topics: Developer Forum, User Forum
Feb 8, 2013 at 12:41 PM
Edited Feb 8, 2013 at 12:51 PM
Hi.

I have an Excel2007 (xlsx) document and try to read the rich text value from a cell, but only receive the plaintext. Here's the Code:
        $reader = new PHPExcel_Reader_Excel2007();
        $reader->setReadDataOnly(true); // already tried without this..
        $objPHPExcel = $reader->load('descriptions.xlsx');
        $cell = $objPHPExcel->setActiveSheetIndex(0)->getCell("C3");

        $str = '';
        $elements = $cell->getRichTextElements();
        foreach ($elements as $item) {
            $str .= '<font style="color: #'.$item->getFont()->getColor()->getRGB().'; font-weight: '.($item->getFont()->getColor() ? 'bold' : 'normal').'">'.$item->getText().'</font>';
        }
And this ends in a php error:
Fatal error: Call to undefined method PHPExcel_Cell::getRichTextElements() in /index.php on line 96

Line 96 is $elements = $cell->getRichTextElements();


Other Code i've tried from this post:
        $objReader = PHPExcel_IOFactory::createReaderForFile('descriptions.xlsx');
        $objPHPExcel = $objReader->load('descriptions.xlsx');
        $objPHPExcel->setActiveSheetIndex(0);
        $sheet = $objPHPExcel->getActiveSheet();
        $cell = $sheet->getCellByColumnAndRow(3,3);
        if ($cell->getValue() instanceof PHPExcel_RichText) {
            echo "success!";
        }
        echo "no =(";
The output is "no =(".


Any idea? I can read the plaintext from the cell by use $cell->getValue() but i need the richtext-formats from the cell.

Thanks, Tyri
Coordinator
Feb 8, 2013 at 12:54 PM
$cellValue = $cell->getValue();
if ($cell->getValue() instanceof PHPExcel_RichText) {
    $elements = $cellValue()->getRichTextElements();
    // Loop through rich text elements
    foreach ($elements as $element) {
        // ... do your stuff here
    }
}
Look at the HTML WRiter for an example
Feb 11, 2013 at 4:04 PM
Hi.

Thank you, but the code is exactly the same what i'd wrote above.

I created a html file from the excel file with the html writer and there's no formatting.
Coordinator
Feb 11, 2013 at 4:54 PM
It shouldn't be the same. You shouldn't be getting Call to undefined method PHPExcel_Cell::getRichTextElements() because you shouldn't be calling that method against the Cell object.

Retrieve the cell value; test if the returned value is an instance of Rich_Text; if (and only if) the cell value is rich text should you try and call the getRichTextElements() method against that Rich_Text object.


And get rid of $reader->setReadDataOnly(true); which means DON'T READ THE CELL FORMATTING.... you want the formatting, because Rich Text IS formatting
Feb 13, 2013 at 10:42 AM
Edited Feb 13, 2013 at 10:42 AM
Ok, you're right. With this code, there's no fatal error...

My Code from my first post:
        $objReader = PHPExcel_IOFactory::createReaderForFile('descriptions.xlsx');
        $objPHPExcel = $objReader->load('descriptions.xlsx');
        $objPHPExcel->setActiveSheetIndex(0);
        $sheet = $objPHPExcel->getActiveSheet();
        $cell = $sheet->getCellByColumnAndRow(3,3);
        if ($cell->getValue() instanceof PHPExcel_RichText) {
            echo "success!";
        }
        echo "no =(";
The Output is everytime "no =(" and that's my problem. $cell->getValue() is never an instance of PHPExcel_RichText.
Coordinator
Feb 13, 2013 at 11:38 AM
That suggests to me that cell 3,3 (D3) doesn't contain rich text.

The file is definitely an OfficeOpenXML file? Not simply a file of some other format with an .xlsx extension? What does
PHPExcel_IOFactory::identify('descriptions.xlsx');
return?
Feb 13, 2013 at 12:22 PM
If i open the file in Excel there is formatting (some bold text) in cell D3.
var_dump(PHPExcel_IOFactory::identify('descriptions.xlsx'));
is:
string(9) "Excel2007"
Coordinator
Feb 14, 2013 at 9:14 AM
Is it possible to send me a copy of the file? We've never had any problems with Rich Text cells not being read as rich text before (unless you specifically set ReadDataOnly to true).