Laravel excel not exporting in correct format

Question

I currently have an array that I've built that dumps like this:

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

I'm using laravel excel in order to export that as an excel file, but it's not quite working the way I intend

When it exports to excel I only get the top level info:

123  |  Testing  |  This is a test category

But I want to get that info as a header and then each subsequent product for that category as a row, so with the example above it would look like:

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

Here's the excel code with the array I'm using, which is dumped above:

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

Tom - everything you've posted looks like it is correct code to me. I think the problem might lie in how you've instructed LaravelExcel to spit the info out to the spreadsheet, but you haven't posted that bit of code. If you stick that up, perhaps someone can help you out. — Watercayman, Jan 17 '19 at 22:35
Maybe that's where there's some confusion on my part. What I have above is everything except the line where I download to excel format. But I'm using the function fromArray to just dump it. Should I still be building a specific form even using that function? — Geoff_S, Jan 17 '19 at 22:44
Shoot, I see. I just looked at that package and agree. Looks like the package is super high level (one line does it all!). I've used PHPExcel for years, and as much of a pain as it is, it is totally granular in control. I assume there is some way in LaravelExcel to get more granular and tell it the N level of the array is what you need to display... but it doesn't look like it is set up for that type of complexity out of the box. Sorry I can't help more. — Watercayman, Jan 17 '19 at 22:51
Please post the exact version of the library you use. It looks like `laravel excel 2.1` to me, is that correct? Have you turned the auto header generation off? If not change the last line to `$sheet->fromArray($allCategoryResult, null, 'A1', false, false);` else it will try to set the headers based on the array keys and will therefore not add any data other than with the corresponding indexes. Not sure about the last part, but will help debugging in any case — Doktor OSwaldo, Jan 18 '19 at 06:27

Doktor OSwaldo · Accepted Answer · 2019-01-18T07:12:00.853

1

I guess (and it is only a guessing) the header generation fails you here. Try to manipulate your data to have the same indexes for every column (NOTE: CODE IS UNTESTED, you may have to correct it):

$allCategoryResult= array();

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

    array_push($allCategoryResult, $categoryItem);    

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

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

        /* We leave that one out for the start
        $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["column4"] = get_object_vars($sku->prices);

        array_push($allCategoryResult, $skuItem);    
    }
}

This should give you a array with data like that:

Array(
  Array(
    ['column1'] = ...
    ['column2'] = ...
    ... 
  ),
  Array(
    ['column1'] = ...
    ['column2'] = ...
    ... 
  )
)

Please inform me if that does any change to your excel. That would be a basic understanding of the library which will help us to help you.

To answer your comment, it is possible to call the native phpExcel function on your sheet and excel object. So you could use that to format a row bold:

$sheet->->getStyle('A1:'.$sheet->getHighestColumn().'1')->getFont()->setBold(true);

Please read into phpExcel to understand what laravel excel really does, it will help you a lot

edited Jan 18 '19 at 07:12

answered Jan 18 '19 at 06:39

Doktor OSwaldo

5,732
20
41

I believe that did work but I will try to pull in the other data now and see. Do you know if there is a way to count so that I can set any row that starts with the category_code as bold using laravel excel? – Geoff_S Jan 18 '19 at 07:05
see the new part of my answer – Doktor OSwaldo Jan 18 '19 at 07:12
That doesn't seem to do the trick though, only setting the first row bold. I was hoping to set each row with category_code, category_name and category_description as bold – Geoff_S Jan 18 '19 at 07:16
Of course, this was only an example. You will have to track the position of this rows. Just add a counter which increases with every `array_push` and then save the counter value in an array, then set all the rows saved in that array to bold. – Doktor OSwaldo Jan 18 '19 at 07:18
oh I'm not sure how but I will try – Geoff_S Jan 18 '19 at 07:21
I'm a bit stuck, how do I increase with each array_push? And would it only be on the array_push to the categoryItem I guess? – Geoff_S Jan 18 '19 at 07:38
no, just have an integer `$row_count` do `$row_count++` everytime you push something to `$allCategoryResult`. then have an array `$bold_rows` and push your `$row_count` to it, everytime you process a `categoryItem` – Doktor OSwaldo Jan 18 '19 at 08:12
Ok can you check my question update? I added what I think should accomplish that but I'm still not sure how to apply ```$boldRows``` to the bold style? – Geoff_S Jan 18 '19 at 13:56

Laravel excel not exporting in correct format

1 Answers1

Linked