retrieving data from excel and showing in the page

Question

Hi guys I have a excel sheet that I want to pull some info and publish it... it looks like

so far I have done putting them into option tab the company names like drop down menu.

I want to do the selected ones information like the one in the picture:

But I couldnt make it dynamic to make it appear.... can you give a clue about how to make it dynamic?( not asking about ajax... I am asking to show it from choosing from array and showing available one) here is code

<?php
    set_include_path(implode(PATH_SEPARATOR, [
        realpath(__DIR__ . '/Classes'), // assuming Classes is in the same directory as this script
        get_include_path()
    ]));
    require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';
    require_once 'PHPExcel.php';
    $file= "./uploads/".$_GET["filename"];
    $inputFileName = ($file);
    //  Read your Excel workbook
    try {
        $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objPHPExcel = $objReader->load($inputFileName);
    } 
    catch(Exception $e) {
        die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
    }
    //  Get worksheet dimensions
    $sheet = $objPHPExcel->getSheet(0); 
    $highestRow = $sheet->getHighestRow(); 
    $highestColumn = $sheet->getHighestColumn();

    /*
    $total=array();
    //  Loop through each row of the worksheet in turn

    for ($row = 1; $row <= $highestRow; $row++) { 

    //  Read a row of data into an array
        $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
    //  echo "-----------------as rowData---------------";


    //  var_dump($rowData); //  Insert row data array into your database of choice here
    //    echo "-----------------VAR_DUMP total!---------------";


        array_push($total, $rowData);
    //  var_dump($total);
        $myFlatArray = PHPExcel_Calculation_Functions::flattenArray($total);    
        echo    "<br>";
        echo "----------------- total as json encode---------------";
        var_dump(json_encode($myFlatArray));
    }
    */
    $foundInCells = array();
    $searchValue = 'Company';

    $wscounter=0; //worksheet counter
    foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
        echo "<hr><p> Worksheet count: " . $wscounter . " name: " . $worksheet->getTitle() . "</p>";
        ///START Sirket isimlerini listele




        $column = 'B';
        $lastRow = $worksheet->getHighestRow();
        echo '<p> Company name: <select id="my_select">';
        for ($row = 1; $row <= $lastRow; $row++) {

            if($worksheet->cellExists($column.$row))
                {
                echo "<option value='".$column.$row."'>". $worksheet->getCell($column.$row)->getValue() . "</option>";

            }
        }

        echo "</select> </p>";
        ///END Sirket isimlerini listele 

        $rowCompanyInfoStart = 4;
        $rowCompanyInfoEnd = 9;
        $colCompanyInfoStart = 'C';
        $colCompanyInfoEnd = 'L';
        echo "<table border='1'>";

//      echo " print row ";
//  echo $_POST["getCell($column.$row)"];


        for ($rowcount = $rowCompanyInfoStart; $rowcount <= $rowCompanyInfoEnd; $rowcount++) { 

            //$data = $objWorksheet->rangeToArray('A1:' . $maxCell['column'] . $maxCell['row']);
            $rangeCoordinates = $colCompanyInfoStart . $rowcount . ':' . $colCompanyInfoEnd . $rowcount;
            $rowData = $sheet->rangeToArray($rangeCoordinates, NULL, TRUE, FALSE);

            //fazla bosluk olursa bunları aç ya da hucre bos mu kontrol et (cellExists ile) 
            //rowData = array_map('array_filter', $rowData);
            //$rowData = array_filter($rowData);
            echo "<tr>";
            foreach($rowData[0] as $result) {
                echo "<td>".$result." </td>";
            }
            echo "</tr>";
        }
        echo "</table>";


    /* GB01 START

        //try {
        /// Y 0 değil 1 den basliyor dikkat
            $rowcounter=1;       
            foreach ($worksheet->getRowIterator() as $row) {
                echo "Worksheet " . $wscounter . " Row " . $rowcounter . "<br/>";
                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(true);

                $cellcounter=0; 
                $hitcounter=0;
                foreach ($cellIterator as $cell) {
                    echo "Worksheet " . $wscounter . " Row " . $rowcounter . " Cell " . $cellcounter . "<br/>";
                    if ($cell->getValue() == $searchValue) {
                        $hitcounter++;
                        echo "hitcounter" . $hitcounter . "<br/>";
                        $foundInCells[] = $worksheet->getTitle() . '!' . $cell->getCoordinate();
                    }
                    $cellcounter++;
                }
                $rowcounter++;
            }
        /*
        }
        // catch (Exception $exc) {
        //   echo "<div style='padding:5px; background-color:#fc8888'> Exception caught on Worksheet " . $wscounter . " Row " . $rowcounter . "<br/>" . $exc->getMessage() . "</div>";
        // }
        $wscounter++;
        //GB01 END */   
    }
    echo "-----found in cells----------------------------------------------------------------";
    var_dump($foundInCells);

?>

<!doctype html>
<html lang="en">
    <head>
      <meta charset="utf-8">
      <title>jQuery UI Autocomplete - Default functionality</title>
      <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
      <script src="//code.jquery.com/jquery-1.10.2.js"></script>
      <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
      <script>
     /* $(function() {
        var availableTags = $.parseJSON('<?php echo json_encode($myFlatArray); ?>');
        $( "#tags" ).autocomplete({
        source: availableTags
        });
      });  */

    function todo()
    {

    var xmlhttp;
if (window.XMLHttpRequest)

{// code for IE7+, Firefox, Chrome, Opera, Safari
  xmlhttp=new XMLHttpRequest();
  }
else
  {// code for IE6, IE5
  xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
  }
xmlhttp.onreadystatechange=function()
  {

  if (xmlhttp.readyState==4 && xmlhttp.status==200)
    {
    document.getElementById("my_select").innerHTML=xmlhttp.responseText;
    }
  }

    }



    </script>
    </head>
    <body>
        <br><br><br>
        <div class="ui-widget">
            <label for="tags">Tags: </label>
            <input id="tags">
            <br><br><br><br>
        </div>
    </body>
</html>

Answer 1

Here is my full solution! if you have any question please comment under this solution however it works pretty good!

<!doctype html>
<html lang="en">
    <head>
        <meta charset="utf-8">
        <title>Choose a Company</title>
        <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css">
        <script src="//code.jquery.com/jquery-1.10.2.js"></script>
        <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>
        <script>
        </script>           
    </head>
    <style>
        div.back {
        border:2px solid grey;
        margin:auto;
        position:relative;}
        .button {
        margin-bottom:200 px;
        }
    </style>
    <script>
    </script>
    <body>
        <div id="header">
            <h1><u> Choose a Company </u></h1>
        </div>
        <div class="back">
            <p>For going to the previous excel list, please press to "Back" button </p>
            <a href="http://localhost:81/phpex/list.php">
                <input type="button" class="button"  value=" Back " />
            </a>
        </div>
        <div id="action">
            <p>OR</p>
            <p>Please from "Company name" options choose a name then Press to "Get Selected Values" </p>
        </div>
    </body>
</html>
<?php
    require_once dirname(__FILE__) .'/tcpdf/tcpdf.php';
    set_include_path(implode(PATH_SEPARATOR, [
    realpath(__DIR__ . '/Classes'), // assuming Classes is in the same directory as this script
    get_include_path()
    ]));
    require_once dirname(__FILE__) . '/Classes/PHPExcel/IOFactory.php';
    require_once 'PHPExcel.php';
    $file= "./uploads/".$_GET["filename"];
    $inputFileName = ($file);
    //  Read your Excel workbook
    try {
        $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
        $objReader = PHPExcel_IOFactory::createReader($inputFileType);
        $objPHPExcel = $objReader->load($inputFileName);
    } 
    catch(Exception $e) {
        die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
    }
    //  Get worksheet dimensions
    $sheet = $objPHPExcel->getSheet(0); 
    $highestRow = $sheet->getHighestRow(); 
    $highestColumn = $sheet->getHighestColumn();
    session_start();
//  $_SESSION["color"] = "green";
    $wscounter=0; //worksheet counter
    foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
        //  echo "<hr><p> Worksheet count: " . $wscounter . " name: " . $worksheet->getTitle() . "</p>";
        ///START Sirket isimlerini listele
        $count=0;
        $column = 'C';
        $lastRow = $worksheet->getHighestRow();
        echo '<form action="#" method="post">';
        echo '<p> Company name: <select name="my_select">';
        for ($row = 1; $row <= $lastRow; $row++)
        {       
            if($worksheet->cellExists($column.$row))
            {
                echo "<option value='".$column.'.'.$row."'>". $worksheet->getCell($column.$row)->getValue() . "</option>";
            }
        }   
        echo "</select> </p>";
        echo "<br />";
        echo    '<input type="submit" name="submit" value="Get Selected Values" />';
        echo "</form>";
        echo "<br />";
        $comp = array(100);
        $tempcount=0;
        for ($row = 1; $row <= $lastRow; $row++)
        {   
            if($worksheet->cellExists($column.$row))
            {
                $count++;
                $name=  $worksheet->getCell($column.$row)->getValue();
                $comp[$tempcount] = array($name,$column,$row);
                //var_dump($comp[$tempcount]);
                //  echo "<p><b>rank number $row </b></p>";
                for ($col = 0; $col < 3; $col++)
                {
                    //  echo "<li>".$comp[$cnt][$col]."</li>";
                }
                $tempcount++;
                //  echo"sadece tempcount";
                //  var_dump($tempcount);
            }
        }
        //  echo "tempcount comp burda";
        //  var_dump ($comp[31]);
        //  echo $count;
        if(isset($_POST['submit']))
        {
            $selected_val = $_POST['my_select'];  // Storing Selected Value In Variable
            $only_row = explode('.',$selected_val);
            //echo "You have selected :" .$selected_val. "<br />";  // Displaying Selected Value
            //var_dump($only_row);
            //echo "selected row value :".$only_row[1]. "<br />";
            for($i=0; $i< $count; $i++)
            {
                if ($comp[$i][2]== $only_row[1])
                { 
                    $info_end=($comp[$i+1][2]-1);
                }
            }
        }   
        else{error_reporting(E_ALL ^ E_NOTICE);}    
        $rowCompanyInfoStart = $only_row[1]+2;
        $rowCompanyInfoEnd = $info_end;
        $colCompanyInfoStart = 'D';
        $colCompanyInfoEnd = 'N';



        if($_SERVER['REQUEST_METHOD'] == 'POST') {
            echo '<form action="final.php" method="POST">';
            $tablo="<br />";
            $tablo = $tablo."<table border=\"1\" cellpadding=\"2\" cellspacing=\"2\" align='center'>";
            for ($rowcount = $rowCompanyInfoStart; $rowcount <= $rowCompanyInfoEnd; $rowcount++)
            { 
                //$data = $objWorksheet->rangeToArray('A1:' . $maxCell['column'] . $maxCell['row']);
                $rangeCoordinates = $colCompanyInfoStart . $rowcount . ':' . $colCompanyInfoEnd . $rowcount;
                $rowData = $sheet->rangeToArray($rangeCoordinates, NULL, TRUE, FALSE);
                //fazla bosluk olursa bunları aç ya da hucre bos mu kontrol et (cellExists ile) 
                //rowData = array_map('array_filter', $rowData);
                //$rowData = array_filter($rowData);    
                $tablo= $tablo."<tr >";
                $companyname=$worksheet->getCell($column.$row)->getValue();
                //  echo $companyname;              
                foreach($rowData[0] as $result) 
                {
                    $tablo= $tablo. "<td>".$result. " </td>";
                }
                $tablo= $tablo. "</tr>";
            }
            $tablo= $tablo. "</table>";
            echo $tablo;
            echo "<br />";

            if($_SERVER['REQUEST_METHOD'] != 'POST') {
                echo "SESSION Not AVAIL. first run.";
            }


            else{   
                $_SESSION['varname'] = $tablo;
                }

            echo    '<input type="submit" name="resName" value="Convert into PDF" />';

        }
        echo '</form>';
        echo "<br />";
        echo "<br />";
    }
?>

Answer 2

I don't know php much and i know this won't be efficient but still I would like to explain how it should work,

One solution could be to keep the objects in dropdown in an array, should be something like that:

$array = new array();
<?php
  $record = array($id, $name, $row, $col);
  array_push($array, $record);
?>

then i guess you can populate your dropdownlist using $array[x][0], using x as incremental value and 0 referring to $id. you have keys&rows&cols in a usable form now.

now we have to get the values from excel. You can either keep them in memory if the files are reasonably sized, or parse the excel at each change. In any case, you will need coordinates of your data cells.

once you click to submit button (or on selection change event of that dropdown), you can grab the value (the id) field. Remember the $array ? we have the position of the data.

IF B2 is the $name variable, - [C4-L9] is your data range.

Let's get the coordinates in an automated way:

/*these are just mathematical operations, i wrote just to show what you should look for*/
$col[n]+1 = C
$row[n]+2 = 4

$col[n+1]+1 = C
$row[n+1]-2 = 9

Now we have top left&bottom left corner coordinates. If your data field count is fixed (10 in this case) just increase your column count by 10. Use rangeToArray function you've used in the code, the range should point top left-bottom right corners, let's say $array2.

You can use a similar foreach loop to print out the data from this $array2, without ajax i guess that's what you were asking for.