Parse spreadsheet into PHP array and return a nested MLM-like table with parent child relationships

Question

I am currently working on a project that requires me to take an uploaded spreadsheet file (XLSX, XLS, etc), place it into an array, then based off of the parent child relationship, build an HTML table structured to display the MLM referral setup.

For example:

First Name | Last Name | Parent | Child
John        Johnson        0        1 
Mary        Sue            1        2 
Harold      Fineberg       1        3 
Gerald      George         2        4

(Sorry about the lame structuring to explain this)

I want to show whoever is a child of someone, their information is placed in a table relative to the parent.

I have attempted using PhpSpreadsheet but to no avail. I am not using a database as this is going to be a simple upload your spreadsheet and have it display in a table with the respective relationships grouped together.

Any assistance would be much appreciated as this is a first time post for me, as well as a first time MLM tree/excel project!

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

if(isset($_POST['upload'])) {
    $filename = $_FILES['sheet']['tmp_name'];
    //$data = csv_to_array($filename);
    $spreadsheet = PhpOffice\PhpSpreadsheet\IOFactory::load($filename);
    $worksheet = $spreadsheet->getActiveSheet();
    $rows = [];
    foreach ($worksheet->getRowIterator() AS $row) {
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,
        $cells = [];
        foreach ($cellIterator as $cell) {
            $cells[] = $cell->getValue();
        }
        $rows[] = $cells;
    }

    // echo '<pre>';
    // print_r($rows);
    // echo '</pre>';
    // $output = fopen('test.xlsx', 'w');
    // foreach ($rows as $file) {
    //     $result = [];
    //     array_walk_recursive($file, function($item) use (&$result) {
    //         $result[] = $item;
    //     });
    // }

    // $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($result, "Xlsx");
    // $writer->save("05featuredemo.xlsx");
    function buildTree(array $elements, $parentId = 0) {
        $branch = array();

        foreach ($elements as $element) {
            if ($element[0] == $parentId) {
                $children = buildTree($elements, $element[0]);
                if ($children) {
                    $element[1] = $children;
                }
                $branch[] = $element;
            }
        }

        return $branch;
    }

    $tree = buildTree($rows);
}
?>

  <!DOCTYPE html>
  <html>

  <head>
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <title>Page Title</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
  </head>

  <body>
    <div class="form-container">
      <form enctype="multipart/form-data" method="POST">
        <div class="form-box">
          <input type="file" name="sheet" id="sheet" accept=".xls,.xlsx">
        </div>

        <div align="center">
          <input type="submit" name="upload" value="Upload &amp; Convert">
        </div>
      </form>
    </div>
  </body>

  </html>

This is what I've been working with. I have found out how to place it into an array nicely, but I cant seem to convert it into a linear treeview :

Parent
    Child
        Child
            Child
        Child
    Child
        Child

The commented code is things I have tried but haven't deleted due to possible usefulness. If I don't need to use PhpSpreadhseet that'd be great, but I can compromise.

Can you provide a sample spreadsheet with minimum number of rows to see its structure. Also best on this site to show what code you have tried so far. In general, reading a spreadsheet (easiest if converted to CSV with titles if no formulas) and making a HTML table from contents is relatively straightforward. Send me a comment when you update your questions. — bcperth, Sep 06 '18 at 12:51
@bcperth I have updated with some source code and a simple layout of what I need to do. The Excel sheet matches the first snippet at the top with the minimal in mind. — serikmccoy, Sep 07 '18 at 02:19
Ok its not so easy to do this, and for myself I always struggle a bit trying to understand recursive functions! Here is a similar example, that you may find useful - the technique I mean. If I get time later I will try to help some more. — bcperth, Sep 07 '18 at 05:24
If you have "managed to place it into an array nicely," then step one of creating a [mcve] should be to remove the spreadsheet code and just put a static array into the code. — miken32, Sep 07 '18 at 21:23

bcperth · Accepted Answer · 2018-09-09T03:21:55.487

Here is some non_recursive code to get you started (if you have not already solved it) that will build a tree based on the $rows array loaded from the spreadsheet.

The idea is that every node has a name and an array of children.. So the code just creates a node for each person (parent and children) in step 1 and then fills in the links in step 2 working from the bottom up.

The code is not robust, and would probably give unwanted results if the rows of $rows were repeated, were not in the right order in the list, or showed child and parent in the wrong place! Good production code would need to take care of these possibilities, probably by examining and repairing $rows before building the tree.

$rows = [];
$rows[] = array(0,1);
$rows[] = array(1,2);
$rows[] = array(1,3);
$rows[] = array(1,4);
$rows[] = array(2,5);
$rows[] = array(3,6);
$rows[] = array(6,7);

// Build the required tree
$tree = makeTree($rows);
print_r($tree);

function makeTree(array $rows){
    //----------------------
    // Step 1. Make a list of nodes 
    // -----------------------
    // make the parent node
    $nodeList =[];
    $nodeList[0]['name'] = "parent:";
    $nodeList[0]['Children'] = [];

    // make the child nodes
    foreach ($rows as $cells) 
    {
         $nodeList[$cells[1]]['name'] = "child:".$cells[1];
         $nodeList[$cells[1]]['Children'] = [];
    }
    //----------------------
    // Step 2. link each child node to its parent node
    // -----------------------
    for ($n = count($rows)-1; $n>=0; $n--)
    {   // do this from the bottom up
        $nodeParent = &$nodeList[$rows[$n][0]];
        $nodeChild = &$nodeList[$rows[$n][1]];
        $nodeParent['Children'][$rows[$n][1]]= $nodeChild;  
    }

    // pick out the parent node (which by now should have all links in place)
    $tree[0] = $nodeList[0];
    return($tree);
}

It outputs as below, which may or not be close to what you need.

 Array
(
    [0] => Array
        (
            [name] => parent:
            [Children] => Array
                (
                    [1] => Array
                        (
                            [name] => child:1
                            [Children] => Array
                                (
                                    [4] => Array
                                        (
                                            [name] => child:4
                                            [Children] => Array
                                                (
                                                )

                                        )

                                    [3] => Array
                                        (
                                            [name] => child:3
                                            [Children] => Array
                                                (
                                                    [6] => Array
                                                        (
                                                            [name] => child:6
                                                            [Children] => Array
                                                                (
                                                                    [7] => Array
                                                                        (
                                                                            [name] => child:7
                                                                            [Children] => Array
                                                                                (
                                                                                )

                                                                        )

                                                                )

                                                        )

                                                )

                                        )

                                    [2] => Array
                                        (
                                            [name] => child:2
                                            [Children] => Array
                                                (
                                                    [5] => Array
                                                        (
                                                            [name] => child:5
                                                            [Children] => Array
                                                                (
                                                                )

                                                        )

                                                )

                                        )

                                )

                        )

                )

        )

)

@serikmccoy If you have any questions about this, feel free to ask. I guess you still have the problem to traverse the tree and emit the correct HTML at each place and properly nested? — bcperth, Sep 08 '18 at 02:14
@serikmccoy Also suggest to remove the working spreadsheet code from your question as suggested by miken32. When I asked you to put it in, it was not working! — bcperth, Sep 08 '18 at 02:17

Parse spreadsheet into PHP array and return a nested MLM-like table with parent child relationships

1 Answers1