We help IT Professionals succeed at work.

How to load data from excel using PHP?

wakawaka500
wakawaka500 asked
on
Is there a way to do this? If so, how would you approach this problem and are there any resources out there that may have already done this or something similar that can be used?

I am trying to build a simple php page that can query certain coordinates on an excel sheet. I know this can be done using asp and excel, not sure if this is possible with php.

Thanks in advance.
Comment
Watch Question

Absolutely there is a way.  See http://phpexcel.codeplex.com/
Most Valuable Expert 2011
Top Expert 2016

Commented:
You can save simple excel worksheets in the form of ".csv" extensions (which is almost universally associated with Excel).  You can upload those CSV files to your server and parse them with PHP.  An easy way to do this would be to use fgetcsv() to read each row into an array.  You might load the data into a data base table if you wanted query capabilities.

If you have an excel file, please post it here so we can see what you're working with.  Then we can maybe give more concrete suggestions about how you might proceed.  The code snippet is from another question here at EE.  It illustrates how you might go about reading and processing a CSV file.
<?php // RAY_temp_raz.php
error_reporting(E_ALL);
echo "<pre>";
echo PHP_EOL;



// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";



// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES


// TEST DATA, SAVED FROM THE POST AT EE
$csv = 'RAY_temp_raz.csv';
$fpo = fopen($csv, 'r');
if (!$fpo) die("FOPEN FAILED: $csv");



// GET THE FIELD NAMES FROM THE TOP OF THE CSV FILE
$top = fgetcsv($fpo);
$cnt = count($top);

// SET UP KEY NAMES FOR USE IN OUR QUERY
$query_cols = implode(',', $top);

// SET A ROW COUNTER
$counter = 0;

// KEEP TRACK OF ROWS THAT HAVE THE WRONG NUMBER OF FIELDS
$errors = array();

// LOOP THROUGH THE CSV RECORDS PERFORMING CERTAIN TESTS
while (!feof($fpo))
{
    $counter++;

    // GET A RECORD
    $csvdata = fgetcsv($fpo);

    // SKIP OVER EMPTY ROWS
    if (empty($csvdata)) continue;

    // CHECK THE NUMBER OF FIELDS
    if ($cnt != count($csvdata))
    {
        $errors[] = $counter;
        continue;
    }

    // MAYBE ASSIGN KEYS TO THE ROW OF FIELDS - ACTIVATE THIS TO SEE THE ASSOCIATIVE ARRAY
    //  $csvdata = array_combine($top, $csvdata);
    //  var_dump($csvdata);

    // ESCAPE THE INFORMATION FOR USE IN THE QUERY
    foreach ($csvdata as $ptr => $value)
    {
        $csvdata[$ptr] = mysql_real_escape_string($value);
    }

    // SET UP VALUE FIELDS
    $query_data = "'" . implode("', '", $csvdata) . "'";

    // SET UP A QUERY
    $sql = "REPLACE INTO myTable ( $query_cols ) VALUES ( $query_data )";

    // IN REAL LIFE YOU WOULD RUN THE QUERY HERE....
    var_dump($sql);

}


// SHOW THE NUMBER OF ROWS PROCESSED
echo "<br/>RECORDS PROCESSED $counter \n";

// SHOW THE NUMBERS OF THE ROWS WITH THE WRONG NUMBER OF FIELDS
if (count($errors))
{
    echo "<br/>ROWS WITH THE WRONG NUMBER OF FIELDS: \n";
    var_dump($errors);
}

Open in new window

Commented:
Another option you might like, only if these files are version 2007 or newer, XLSX format, is this:

Unzip the xlsx file: you will get a bunch of XML files.

Parse those files (the format is documented on the Web) to get what you want.
Most Valuable Expert 2011
Top Expert 2016

Commented:
@pgorod: Can you please post a link to the documentation for the XML files?  Thanks!

Author

Commented:
Here is the csv I will be using  Example.csv

I would like to grab a specific cell and be able to display the number, ID or comments. For example, I would call CELL G2 in php to grab the data in that cell. I don't believe I would require database functionality. As for the version, I am limited to using 2003 at the moment.
Most Valuable Expert 2011
Top Expert 2016
Commented:
I stowed that CSV on my server here (Image attached).
http://www.laprbass.com/RAY_temp_waka.csv

You can see the script in action here.
http://www.laprbass.com/RAY_temp_waka.php?cell=B1

HTH, ~Ray
<?php // RAY_temp_waka.php
error_reporting(E_ALL);
echo "<pre>";

// CREATE AN ARRAY OF COLUMN DESIGNATORS
$alphabet = range('A', 'Z');

// POPULATE THE ZERO POSITION (NOT USED IN EXCEL)
$spread = array( NULL );


// OPEN THE CSV FILE
$fp = fopen('RAY_temp_waka.csv', 'r');
if (!$fp) die('Fooey');

// READ THE ROWS OF THE CSV FILE
while (!feof($fp))
{
    if ($arr = fgetcsv($fp))
    {
        // ASSIGN LETTER KEYS TO EACH COLUMN
        foreach ($arr as $key => $data)
        {
            $new[$alphabet[$key]] = $data;
        }

        // ASSIGN NUMBER KEYS TO EACH ROW
        $spread[] = $new;
    }
}

// ACTIVATE THIS TO SEE THE MATRIX
// var_dump($spread);

// IF THERE IS A URL PARAMETER FOR THE CELL
$cell = (isset($_GET['cell'])) ? trim(strtoupper($_GET['cell'])) : NULL;
if ($cell)
{
    // SEPARATE THE VALUES (EXAMPLE: B2 OR G32)
    $col = substr($cell,0,1);
    $row = substr($cell,1);

    // LOCATE THE ROW AND COLUMN IN THE MATRIX
    if (isset($spread[$row]))
    {
        if (is_array($spread[$row]))
        {
            if (isset($spread[$row][$col]))
            {
                echo "THE VALUE IN CELL $cell IS <b>{$spread[$row][$col]}</b>";
            }
            else
            {
                echo "THERE IS NOT A COLUMN $col";
            }
        }
        else
        {
            echo "THIS MESSAGE SHOULD NOT OCCUR";
        }
    }
    else
    {
        echo "THERE IS NOT A ROW NUMBER $row";
    }
}

// CREATE THE QUERY FORM IN HEREDOC SYNTAX
$form = <<<FORM
<form>
ENTER A CELL LIKE B2 OR G16
<input name="cell" />
<input type="submit" />
</form>
FORM;
echo $form;


// RENDER THE SPREADSHEET IN A TABLE
if (empty($col)) $col = '?';
if (empty($row)) $row = '?';
echo "<table>" . PHP_EOL;

// COLUMN LETTERS
echo "<tr>";
echo "<td>&nbsp;</td>";
$arr = $spread[1];
foreach ($arr as $key => $data)
{
    $style = NULL;
    if ($key == $col) $style = ' style="background-color:gold;"';
    echo "<td$style>$key</td>";
}
echo "</tr>" . PHP_EOL;

// ROWS OF DATA
unset($spread[0]);
foreach ($spread as $num => $arr)
{
    echo "<tr>";
    $style = NULL;
	if ($num == $row) $style = ' style="background-color:gold;"';

    echo "<td$style>$num. &nbsp;</td>";
    foreach ($arr as $key => $data)
    {
        echo "<td>$data</td>";
    }
    echo "</tr>" . PHP_EOL;
}

echo "</table>" . PHP_EOL;

Open in new window

waka.png

Commented:
@Ray Paseur:

Sure, it was an easy Google, I remembered it had the word "open" in it:

http://msdn.microsoft.com/en-us/library/aa338205%28v=office.12%29.aspx

BTW, I never actually used these Zipped-XML files, but I bet it'll be useful someday...
Most Valuable Expert 2011
Top Expert 2016

Commented:
@pgorod: Thanks very much.  Reading it now.

Author

Commented:
This is very neat and well written. Is there a way to grab just one cell value from different sheets and load them all in one table and then calculating the total?
Most Valuable Expert 2011
Top Expert 2016

Commented:
Thanks!  I think that part about using different sheets is a totally different line of question.  You might consider getting a developer to help you with it.  Sounds like it may have a lot of moving parts.

Author

Commented:
Good idea. Although, is it possible to load just one cell from excel into a single variable from your code?
Most Valuable Expert 2011
Top Expert 2016

Commented:
Sure!  See line 50.  The value for your variable would be in $spread[$row][$col] -- just set $row and $col to the right values.

Author

Commented:
Very helpful and polite!