Asked  9 Months ago    Answers:  5   Viewed   128 times

I have an array of arrays of data.

so the basic format is

$sheet = array(
  array(
    'a1 data',
    'b1 data',
    'c1 data',
    'd1 data',
  ),
  array(
    'a2 data',
    'b2 data',
    'c2 data',
    'd2 data',
  ),
  array(
    'a3 data',
    'b3 data',
    'c3 data',
    'd3 data',
  )
);

When I am passed the array I have no idea how many columns or rows there will be. What I want to do is using php excel create an excel sheet out of the array.

from what I have seen, the only way to set data is to use $objPHPExcel->getActiveSheet()->setCellValue('A1', $value);

So my question is

How would you loop over the cells?

remembering that there could be say 30 columns and say 70 rows which would be AD70 So, how do you loop that?

Or is there a built in function to turn an array to a sheet?

 Answers

92

You can set the data from an array like so:

$objPHPExcel->getActiveSheet()->fromArray($sheet, null, 'A1');

fromArray works with 2D arrays. Where the 1st argument is the 2D array, the second argument is what to use if there is a null value, and the last argument is where the top left corner should be.

Otherwise you would need to loop through the data:

$worksheet = $objPHPExcel->getActiveSheet();
foreach($sheet as $row => $columns) {
    foreach($columns as $column => $data) {
        $worksheet->setCellValueByColumnAndRow($column, $row + 1, $data);
    }
}
Wednesday, March 31, 2021
 
Optimus
answered 9 Months ago
18

As told by questioning user Tiny on his own comment:

Finally, it worked. I just added exit at the end of my PHP script (at the end of the first code snippet in the question). Thanks very much all of you for giving me useful hints.

To give some constructive additional tips on this kind of problems:

  • A good tip is that you can omit the closing tag ?> on all your PHP script files, that way you know that you're not sending any aditional invisible whitespace at the end of it.
  • Also enconding PHP script files on UTF-8 on wrongfully configured web server can send an undesirable couple bytes at the begining of the script. Open the PHP file on Notepad++, check if it's UTF-8, and in that case, convert it to ANSI. If that makes it work, check your webserver/php configuration.
  • Just before the header calls, you can check if headers has been wrongfully sent with:

    if ( headers_sent() ) die("**Error: headers sent");

  • If you can't prevent that some function call sends undesirable strings to the browser, you can "erase" all of it using at the very beginning of your script:

    ob_start();

    and then, just before the first headers call, use:

    ob_clean();

    Be careful that with doing so will prevent you for receiving error feedback.

  • And lastly, as already said, if nothing has to be executed afterwards some point on the script, call exit or die.

Wednesday, March 31, 2021
 
ajaybc
answered 9 Months ago
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
35

Try removing the first json_encode(). There's no need to encode it twice.

include_once('DB_Handler.php');

$result = mysqli_query($mysqli, "SELECT DocentID FROM gebruiker");

$rows = array();
while ( $row = mysqli_fetch_array($result))
{
    $rows[] = $row;
}

echo json_encode($rows);
Saturday, May 29, 2021
 
Farnabaz
answered 7 Months ago
13

There's a lot been written about PHPExcel and memory use, and I'm not going to repeat it all here.

Try reading some of the threads on the PHPExcel discussion board discussing the issue, such as this one; or previous answers here on SO such as this one or this one

Tuesday, July 6, 2021
 
Slinky
answered 5 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