0
$objPHPExcel->getActiveSheet()->fromArray($dataArray,null,"A2")

I've the above line of code. The problem is, I am not good at iterating and I want all the cell values to be set as STRING so as to avoid automated modifications of texts leading zeros.

P.S. In Addition, code for setting as STRING for selective columns will be appreciated.

Thanks!

1 Answers1

4

When you set cell values individually, you have the option of setting the datatype explicitly, but when you use the fromArray() method, you don't have this option.

However, by default, PHP uses a default value binder to identify datatypes from the values passed, and set the cell datatype accordingly. This default behaviour is defined in a class /PHPExcel/Cell/DefaultValueBinder.php.

So you can create your own value binder, as described in the PHPExcel Documentation, that would set every value as a string datatype.

Something like:

class PHPExcel_Cell_MyColumnValueBinder extends PHPExcel_Cell_DefaultValueBinder implements PHPExcel_Cell_IValueBinder
{
    protected $stringColumns = [];

    public function __construct(array $stringColumnList = []) {
        // Accept a list of columns that will always be set as strings
        $this->stringColumns = $stringColumnList;
    }

    public function bindValue(PHPExcel_Cell $cell, $value = null)
    {
        // If the cell is one of our columns to set as a string...
        if (in_array($cell->getColumn(), $this->stringColumns)) {
            // ... then we cast it to a string and explicitly set it as a string
            $cell->setValueExplicit((string) $value, PHPExcel_Cell_DataType::TYPE_STRING);
            return true;
        }
        // Otherwise, use the default behaviour
        return parent::bindValue($cell, $value);
    }
}

// Instantiate our custom binder, with a list of columns, and tell PHPExcel to use it
PHPExcel_Cell::setValueBinder(new PHPExcel_Cell_MyColumnValueBinder(['A', 'B', 'C', 'E', 'F']));

$objPHPExcel = new PHPExcel();
$objPHPExcel->getActiveSheet()->fromArray($dataArray,null,"A2");
Mark Baker
  • 209,507
  • 32
  • 346
  • 385
  • This is the perfect solution for some of us that want to use fromArray() to dump data quickly, but also need leading zeros to work right. In my case, we have both product numbers and UPC numbers. All other solutions would not work for me, except this. Although, I could loop my data and set each cell, but that seems super clunky. If you do this, you can omit custom formats like ('000000000000'), and omit the setQuotePrefix(true) method. Note: I have "PHPExcel_Cell_CustomDataBinderForStrings" class, and had to do "new PHPExcel_Cell_CustomDataBinderForStrings(..." – gregthegeek Jun 01 '16 at 20:08
  • Sorry, you are creating a new binder PHPExcel_Cell_MyColumnValueBinder, but after that, you are instantiating the default PHPExcel_Cell_DefaultValueBinder. Am I missing something? – morgar Apr 10 '17 at 17:06
  • @morgar - You're right... surprising that nobody has noticed that error in the 18 months that this post has been here – Mark Baker Apr 10 '17 at 17:08
  • Thanks Mark, with that fixt it works great and solves my problem. – morgar Apr 10 '17 at 17:14