Asked  7 Months ago    Answers:  5   Viewed   166 times
$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!

 Answers

39

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");
Friday, May 28, 2021
 
Wilk
answered 7 Months ago
42
function set_val(array &$arr, $path,$val)
{
   $loc = &$arr;
   foreach(explode('.', $path) as $step)
   {
     $loc = &$loc[$step];
   }
   return $loc = $val;
}
Wednesday, March 31, 2021
 
laurent
answered 9 Months ago
75

toArray() supports the following arguments:

/**
 * @param  mixed    $nullValue          Value returned in the array entry if a cell doesn't 
 *                                      exist
 * @param  boolean  $calculateFormulas  Should formulas be calculated?
 * @param  boolean  $formatData         Should formatting be applied to cell values?
 * @param  boolean  $returnCellRef      False - Return a simple array of rows and 
 *                                      columns indexed by number counting from zero
 *                                      True - Return rows and columns indexed by their 
 *                                      actual row and column IDs
 */

so

toArray(NULL,TRUE,TRUE);

will return all the cell values in the worksheet (calculated and formatted) exactly as they appear in Excel itself.

So dates will be returned as (for example) 21-Dec-2011 07:30 rather than 47239.318 (depending on the format mask for the cell). Other numeric cells could be returned as 21,357.00 (depending on the format masking for that cell) so it is a two-edged sword.

Wednesday, March 31, 2021
 
max_
answered 9 Months ago
47

There is currently no method in PHPExcel that will allow you to set the data type for a range of cells, only for an individual cell.

There are a couple of options... the harder would be to write a custom Cell Value Binder that automatically sets the data type for those cells to string when the cell value is set by fromArray().

What you could do instead (and is far easier), is to leave the datatype as a number, and the value as a numeric, but to set a number format mask which tells Excel to display the numbers with leading zeroes.

$objPHPExcel->getActiveSheet()->getStyle('L3:N2048')
                              ->getNumberFormat()->setFormatCode('0000');
Saturday, May 29, 2021
 
Guesser
answered 7 Months ago
23

I am not sure if this will be useful. I have noticed that the ArrayObject class is 'interesting'...

I am not sure that this is even an 'answer'. It is more an observation about this class.

It handles the 'multidimensional array' stuff correctly as standard.

You may be able to add methods to make it do more of what you wish?

<?php //

class Config extends ArrayObject
{

//    private $data = array();

    public function __construct(array $data = array())
    {
        parent::__construct($data);
    }
}

$conf = new Config(array('a' => 'foo', 'b' => 'bar', 'c' => array('sub' => 'baz')));
$conf['c']['sub'] = 'notbaz';
$conf['c']['sub2'] = 'notbaz2';

var_dump($conf, $conf['c'], $conf['c']['sub']);

unset($conf['c']['sub']);

var_dump('isset?: ', isset($conf['c']['sub']));

var_dump($conf, $conf['c'], $conf['c']['sub2']);

Output:

object(Config)[1]
  public 'a' => string 'foo' (length=3)
  public 'b' => string 'bar' (length=3)
  public 'c' => 
    array
      'sub' => string 'notbaz' (length=6)
      'sub2' => string 'notbaz2' (length=7)

array
  'sub' => string 'notbaz' (length=6)
  'sub2' => string 'notbaz2' (length=7)

string 'notbaz' (length=6)

string 'isset?: ' (length=8)

boolean false

object(Config)[1]
  public 'a' => string 'foo' (length=3)
  public 'b' => string 'bar' (length=3)
  public 'c' => 
    array
      'sub2' => string 'notbaz2' (length=7)

array
  'sub2' => string 'notbaz2' (length=7)

string 'notbaz2' (length=7)
Saturday, May 29, 2021
 
Jubair
answered 7 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share