Format Text with leading apostrophe

Question

Need to format the header row of my file to be formatted as that when you view the cell in the Excel spreadsheet it shows as HEADER but when you click that cell in the formula bar it shows 'HEADER.

I've tried seemingly just about everything with no luck. Whatever i try displays it in the cell as 'HEADER. However if I double click that cell in Excel it autoformats the way I want and strips the apostrophe out of the cell and leaves it in the formula bar. And excel does that as well in a new cell where I type 'WHATEVER it will just display WHATEVER in the cell and still display 'WHATEVER in the formula bar.

How can I do this so it's already displayed that way when I create my Excel file with phpExcel?

This is how I'm inserting the values right now.

$objPHPExcel
    ->getActiveSheet()
    ->setCellValueExplicit(
        $currentColumn.$currentRow, 
        $data, 
        PHPExcel_Cell_DataType::TYPE_STRING
    );

$data would just be a string preceded by an apostrophe.

Answer 1

This isn't actually a feature that PHPExcel supports (incredibly, nobody has ever asked for it before), though you could modify the code to support it by adding a quotePrefix attribute to the cell style (with appropriate getter and setter), and then modifying the writer to write a quotePrefix="1" attribute to the xf record when it writes the style to the styles.xml file (for Excel2007)

EDIT

Might need a bit of tweaking, but should be fairly straightforward

Classes/PHPExcel/Style.php

add a new attribute of $_quotePrefix (type Boolean, default false) and provide getter setter methods for it.

Classes/Reader/Excel2007.php

somewhere between lines 500 and 533, test for the quotePrefix xf attribute value; and add a call to $objStyle (defined in line 577) to call the quotePrefix setter if the xf attribute value was "1".

Classes/Writer/Excel2007/Style.php

Modify the xf writer (around line 399) to write a quotePrefix attribute with a value of "1" if the $pStyle quotePrefix property is true