Search code examples
phparraysexcellaravellaravel-excel

Laravel excel not exporting in correct format


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

Solution

  • 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