how to merge all the cells of an excel file in an array and how to make autocomplete search box works

Question

I have two problems:

1) I am using PHPExcel for retrieving data from cells, I have simple code two print it out.

<?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"];

    //if you want to try it you can use this and comment line on the above
// $inputFileName = './sampleData/yourexcelname.xls';
        $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();

    //  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);

        var_dump($rowData); //  Insert row data array into your database of choice here


        echo "-----------------as json encode---------------";
        var_dump(json_encode($rowData));

        }
    ?>
    <!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 =<?php echo json_encode($rowData); ?>;
        $( "#tags" ).autocomplete({
          source: availableTags
        });
      });
      </script>
    </head>
    <body>

    <div class="ui-widget">
      <label for="tags">Tags: </label>
      <input id="tags">
    </div>


    </body>
    </html>

however what I want to do is....

instead of this ....

            $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row,
                                            NULL,
                                            TRUE,
                                            FALSE);

i tried to use this:

$total = $sheet->mergeCells('A' . $row . ':' . $highestColumn . $row,  NULL,
                                    TRUE,
                                    FALSE);
 echo "-----------------as Total---------------";
         var_dump($total);

however, it just echos every detail related with document security and property cache info etc. etc. everything but what I want. how can I merge data in cells?

2) I try to turn the php array into js array so that I can put my array into autocomplete search box (autocomplete jquery ) as I used here:

$(function() {
    var availableTags = $.parseJSON('<?php echo json_encode($rowData); ?>');
    $( "#tags" ).autocomplete({

    source: availableTags
    });
  });

however, my suggested options are not appearing when I put this json_encode convertion. question: how can I make this converted array echo my data?

Useful handy helps will be appreciated.

Two problems require two questions. Please [edit] and split them. — Jan Doggen, May 29 '15 at 14:42
@JanDoggen, questions have been highlighted in "question title" however because you didnt get it. I copied and pasted into context again. Secondly, second question depends on first one I asked both of them all together. — gobo, May 29 '15 at 14:55
What do you expect to happen when you merge the cells? You do realise that a call to `mergeCells()` returns the Worksheet object? And what it does is turn your specified range into one single cell, exactly the same as merging cells does in MS Excel. — Mark Baker, May 29 '15 at 18:07
hi @MarkBaker, thank you for your comment. acctually I wrote according to your answer as it is written here: http://stackoverflow.com/questions/6820250/merge-cell-values-with-phpexcel-php — gobo, Jun 03 '15 at 07:09
Well if you really want to merge the cells, you're doing exactly the right thing, and getting exactly what you should get in return.... but it seems a pretty pointless exercise to merge every cell in a worksheet row into a single cell.... especially when you seem to expect an array of values for the next part of your question.... what's wrong with rangeToArray() to return an array? What do you believe merging cells will give you? — Mark Baker, Jun 03 '15 at 07:25
@MarkBaker as you said, it returns Worksheet object... anyways, what I wanted to do is as a final json_encode: string '["john","elizabetta","Ramon","Pedro","nigel","george","Aaron","Jesus","pietro","michael","alice","alison","Steven","Giroud","Tony","Xavier"]' (length=156) however because `for ($row = 1; $row <= $highestRow; $row++) { $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE); var_dump($rowData); }` ...I have nested arrays... — gobo, Jun 03 '15 at 07:31
@MarkBaker then result of this `$kova=array(); for ($row = 1; $row <= $highestRow; $row++) { $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE); var_dump($rowData); array_push($kova,$rowData); var_dump($kova); echo "
"; var_dump(json_encode($kova)); }` I have a json_encode which looks like:**string '[[["john","elizabetta","Ramon","Pedro"]],[["nigel","george","Aaron","Jesus"]],[["pietro","michael","alice","alison"]]]'** — gobo, Jun 03 '15 at 07:35
Yes, you have a nested array.... you can simply flatten it. PHPExcel even provide a method for you to do this: `$myFlatArray = PHPExcel_Calculation_Functions::flattenArray($my2dArray);` — Mark Baker, Jun 03 '15 at 07:35
@MarkBaker the reason why I want to do is I wanna throw this all data into [autocomplete](https://jqueryui.com/autocomplete/) so it can give suggestion about the searching functionality for every data in cells — gobo, Jun 03 '15 at 07:37
Then flatten the array; it'll give you a single element for each cell, but will be 1-dimensional rather than 2-dimensional — Mark Baker, Jun 03 '15 at 08:02

score 0 · Answer 1 · answered Jun 03 '15 at 08:44

as @MarkBaker suggested I found a solution which works pretty fine:

$kova=array();

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 "-----------------returning into single array---------------";

    array_push($kova,$rowData);
    var_dump($kova);
    $myFlatArray = PHPExcel_Calculation_Functions::flattenArray($kova); 
    echo    "<br>";

    echo "----------------- converting into json encode---------------";
    var_dump(json_encode($myFlatArray));




}

?>
<!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
    });
  });
  </script>
</head>
<body>
 <br>
 <br>
 <br>
<div class="ui-widget">
  <label for="tags">Tags: </label>
  <input id="tags">
  <br>
  <br>
  <br>
  <br>

</div>

how to merge all the cells of an excel file in an array and how to make autocomplete search box works

1 Answers1