PHPExcel issue with multidimensional array and creating grouped data

Question

My current array that is being looped dumps this structure

0 => array:11 [▼
  "category_code" => "123"
  "category_name" => "Testing"
  "category_description" => "This is a test category"
  19738 => array:5 [▼
    "identifier" => "720368842943"
    "description" => Test Description One
    "count" => 4
    "details" => array:2 [▼
      0 => array:3 [▼
        "detail_code" => "2751"
        "detail_code2" => "43"
        "detail_specifier" => "Detail One"
      ]
      1 => array:3 [▼
        "detail_code" => "2681"
        "detail_code2" => "9"
        "detail_specifier" => "Detail Two"
      ]
    ]
    "prices" => array:1 [▼
      "01" => "1129.00"
    ]
  ]
  19739 => array:5 [▼
    "identifier" => "720368844121"
    "description" => "Test Description Two"
    "count" => 4
    "details" => array:2 [▼
      0 => array:3 [▼
        "detail_code" => "2751"
        "detail_code2" => "43"
        "detail_specifier" => "Detail One"
      ]
      1 => array:3 [▼
        "detail_code" => "2681"
        "detail_code2" => "9"
        "detail_specifier" => "Detail Two"
      ]
    ]
    "prices" => array:1 [▼
      "01" => "1490.00"
    ]
  ]

But when I export to excel it only shows the three top level attributes

123  |  Testing  |  This is a test category

I'm trying to export this in a way so that those top 3 values are one row (like a header) and all related products are listed under it like so:

123  |  Testing  |  This is a test category
====================================================================================================================
19738  |  720368842943  |  Test Description One  |  4  |  2751  |  43  |  Detail One  |  2681  |  9  |  Detail Two  |  1129.00
19739  |  720368844121  |  Test Description Two  |  4  |  2751  |  43  |  Detail One  |  2681  |  9  |  Detail Two  |  1490.00

I'm using Laravel Excel by maatwebsite which is just a wrapper for PHPExcel in laravel, but all i want to do is simply take the category info as a row with the subsequent product info as rows below it.

Here's the excel code with the array I'm using, which is dumped above (item Code is the 19738,19739 values)

$allCategoryResult= array();

foreach($prices->categories as $category){ 
    $categoryItem = array(); 
    $categoryItem["category_code"] = $category->category_code;
    $categoryItem["category_name"] = $category->category_name; 
    $categoryItem["category_desc"] = $category->category_desc;

    foreach($category->skus as $sku){
        $skuItem = array(); 

        $skuItem["identifier"] = $sku->sku_info->identifier;
        $skuItem["description"] = $sku->sku_info->item->description;
        $skuItem["count"] = $sku->sku_info->item->item_type->count;

        $skuItem["details"] = array(); 
        foreach ($sku->sku_info->details as $details) {
            $detailsItem = array(); 
            $detailsItem["detail_code"] = $details->detail_code;
            $detailsItem["detail_code2"] = $details->detail_code2;
            $detailsItem["detail_specifier"] = $details->detail_specifier;
            $skuItem["details"][] = $detailsItem; 
        }

        $skuItem["prices"] = get_object_vars($sku->prices);


        $itemCode = $sku->sku_info->item->item_code;
        $categoryItem[$itemCode] = $skuItem; 
    }
    $allCategoryResult[] = $categoryItem; 
}


$name = 'Test Export';

$build = Excel::create($name, function ($excel) use ($allCategoryResult) {

    $excel->setTitle('Test Export');

    $excel->sheet('Test Export', function ($sheet) use ($allCategoryResult) {

        $sheet->fromArray($allCategoryResult);
})->download('xlsx');

Kenneth · Accepted Answer · 2019-01-18T03:39:07.947

1

The methodfromArray() expects a 2D array

$data=(
  array(2) (
    [0] => array(3) (
      [0] => 19738  
      [1] => ...
      [2] => ...
    )
    [1] => array(4) (
      [0] => 19739
      [1] => ...
      [2] => ...
      [3] => ...

Each element of the array $data is a row. Each sub element is the value of a column. Restructure the creation of your array to fit this structure and you will be in business.

This code is untested, just trying to give an example. I'm not sure what you're doing with the get_object_vars($sku->prices);. I'm sure this will have to change.

$excelRows = [];

foreach($prices->categories as $category){ 

    $excelRows[] = [
      $category->category_code,
      $category->category_name,
      $category->category_desc
    ]

    foreach($category->skus as $sku){

        $row = [
          $sku->sku_info->identifier,
          $sku->sku_info->item->description,
          $sku->sku_info->item->item_type->count
        ]

        foreach ($sku->sku_info->details as $details) {
          $row[] = $details->detail_code;
          $row[] = $details->detail_code2;
          $row[] = $details->detail_specifier; 
        }

        $row[] = get_object_vars($sku->prices);

        $row[] = $sku->sku_info->item->item_code;

        $excelRows[] = $row;
    }
}

edited Jan 18 '19 at 03:39

answered Jan 18 '19 at 01:27

Kenneth

535
2
17

But if I need the category info first then I'm not sure how to do that. So I need category code, name and description in cells A1 A2 and A3, then each item and it's contents would each be a row. So if I have multiple categories then I need each to be a row with all of its products under it. How would I do this @kenneth – Whisou138 Jan 18 '19 at 01:32
Can you help me with restructuring that? – Whisou138 Jan 18 '19 at 02:00
Thank you, I will try this. Basically it will always only have one price but the index could be 01 or 09 so that just guaranteed that I just got the amount – Whisou138 Jan 18 '19 at 03:46
Ok so question: On that line ```$row[] = get_object_vars($sku->prices);``` It's printing "10 => array:1 [ …1]" ....there will always only be one item in that element I just won't always know the key of it. sometimes it's ```"01" : "180.00"``` and sometimes it's ```"08" : "180.00"``` so what would be my best course of action there? – Whisou138 Jan 18 '19 at 05:23
is there a way that I can only style rows coming from the first three category elements? So basically if it's a row being built from category_code, category_name and category_description then make it bold with a background? – Whisou138 Jan 18 '19 at 06:47
It looks like there isn’t a way to do it from the array itself. Look at this answer. Maybe you can keep up with which rows have the info you want to bold, then go back and make them bold. https://stackoverflow.com/questions/14746332/phpexcel-make-first-row-bold – Kenneth Jan 18 '19 at 13:22

PHPExcel issue with multidimensional array and creating grouped data

1 Answers1