Creating a hyphen list inside a cell

Question

I'm trying to figure out how I can insert a hyphen list into a cell with phpexcel like this:

-Value A
-Value B

I tried it with following snippet:

// input value can be something else
$value = "-ValueA\n-Value B";

if (strpos($value, '-') === 0 || strpos($value, '=') === 0) {
    $value = '\'' . $value;
    $cell->setValueExplicit($value, \PHPExcel_Cell_DataType::TYPE_FORMULA);
} else {
    $cell->setValueExplicit($value, \PHPExcel_Cell_DataType::TYPE_STRING);
}

$cell->getStyle()->getAlignment()->setWrapText(true);

But with this code phpexcel adds an equal sign (=) before the string.

When I skip the part to escape the hyphen sign (-) excel shows the apostroph in the cell like:

'-Value A
-Value B

Until I click into and out the cell.

What can I do to escape the hyphen correctly? I'm using PHPExcel 1.8.1 with PHP 5.6.8. Creating an xlsx file and opening it with Excel 2007.

Mark Baker · Accepted Answer · 2015-08-06T14:31:07.460

You simply want a string containing newline characters.... but don't try to set it as a formula.... it isn't a formula

$value = "-ValueA\n-Value B\n-Value C";
$objPHPExcel->getActiveSheet()
    ->setCellValue('A10', $value);
$objPHPExcel->getActiveSheet()
    ->getRowDimension(10)
    ->setRowHeight(-1);
$objPHPExcel->getActiveSheet()
    ->getStyle('A10')
    ->getAlignment()
    ->setWrapText(true);

EDIT

If you want to prefix the cell value with a quote to avoid MS Excel trying to treat the value as a formula, don't prefix the actual value with a quote, but use the quotePrefix style setting:

$objPHPExcel->getActiveSheet()
    ->getStyle('A10')
    ->setQuotePrefix(true);

Excel2007 (.xlsx) only

Creating a hyphen list inside a cell

1 Answers1