How do you embed images into Excel through PHP

[Francis Bolap]

Right now I'm making a little test page to see how well I could export data from a MySQL database into an Excel spreadsheet. This is a dynamically created .xls and there could be almost no rows or even thousands of rows created, it all depends on what the person wants to get.

I have outputting basic data well. Right now it gets up to ~65k rows of output. What I need now is to be able to embed images into these Excel files. I've been searching since yesterday morning and have yet to find anything all that useful.

Here is the code:
(A part of the top has been omitted, it's just the database information)

<?php
    $filename = 'Dump_For_Table_'. $tblname;
    $title = '';
 
    $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
    $sql = 'SELECT * FROM ' . $tblname;
    $result = mysqli_query($mysqli, $sql);
 
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename='. $filename .'.xls');
    header('Pragma: no-cache');
    header('Expires: 0');
 
    echo($title."\n");
 
    $sep = "\t";
 
    //column names
    while($row = mysqli_fetch_field($result))
    {
        echo $row->name . $sep;
    }
 
    echo("\n");
    //end column names
 
    while($row = mysqli_fetch_array($result))
    {
        //set_time_limit(60); // HaRa
        $schema_insert = "";
        for($i=0; $i<mysqli_num_fields($result);$i++)
        {
            if(!isset($row[$i]))
                    $schema_insert .= " ".$sep;
            elseif ($row[$i] != "")
                    $schema_insert .= "$row[$i]".$sep;
            else
                    $schema_insert .= "".$sep;
        }
        $schema_insert = str_replace($sep."$", "", $schema_insert);
        $schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
        $schema_insert .= "\t";
        echo(trim($schema_insert));
        echo "\n";
    }
?>
 

I know that there is PHPExcel and PEAR but I'd rather not have to install those just to be able to insert images. PHPExcel I know isn't able to handle the largest amount of rows we might have, another programmer before me tried it and it didn't work. I tried looking at some of the code for PHPExcel but I can't follow it well enough to see what code they're actually sending to Excel to make it accept images. I don't know about PEAR though.

[johny10151981]

What software are you using then? I need to know that too.

I worked with PHPExcell a little it can handle image very well you can follow example14. its a small file.

And I am not sure about 65K rows. but are you sure that Excel itself can handle this amount data. If so then you should try to increase your memory limit using php.ini file

[Francis Bolap]

I'm just using the code above. I'm not using any extra software or libraries from anywhere else.

Although after searching through the files I learned that PHPExcel makes an xml file that Excel can read. Mind you I'm fairly certain that that's what it does. It was a little hard to follow it all. I'm going to try just making an xml file and see what happens.

Excel has a maximum amount of rows that's somewhere around 65k. I got it to output that many from a table I have but no more, which is fine.

Edit: I'm caving. I'm just going to try and use PEAR. If I manage to get images to go into Excel images properly using PEAR I'll edit my post with the information here in case anyone else asks.