add custom column to laravel excel

Question

I am using maatwebsite/excel, I want to know if it's possible to add custom column when I export my data as CSV or not?

Explanation

I am successfully exporting my products data, but my products have other option which is not stored in my products table such as: specification.

my specifications are stored in 2 different tables named specifications where is parent like CPU and subscpecifications where child's are stored like: Core i5.

another table i am using to store child's id and products id in order to relate each product to their subspecifications.

Sounds Complecated right? :) here i provide ugly map to get the logic :)

Now, What I try to do is:

Add extra column to my csv file and include all specifications of each product.

sample:

Codes

here is my current export function

public function export(Request $request) {
      $input = $request->except('_token');
      foreach ($input['cb'] as $key => $value) {
        if ($value== 'on') {
          $getRealInput[$key] = $input['defaultname'][$key];
        }
      }

      $products = Product::select($getRealInput)->get();


      Excel::create('products', function($excel) use($products, $request) {
        $excel->sheet('sheet 1', function($sheet) use($products, $request){

          $input = $request->except('_token');
          foreach ($input['cb'] as $key => $value) {
            if ($value== 'on') {
              $getCustomInput[$key] = $input['customname'][$key];
            }
          }

          $sheet->fromArray($products, null, 'A1', false, false);
          $sheet->row(1, $getCustomInput);
        });
      })->export('csv');
      return redirect()->back();
    }

Questions

1. Is that possible?
2. If yes, Base on my function above, how do I do it?

Thanks in advance.

UPDATE 1

I have added this code to my function

$allRows = array();
  $data = array();
  foreach($products as $product){
  $specs = $product->subspecifications;
  foreach($specs as $spec){
    $data[] = $spec->specification->title;
    $data[] = $spec->title;
  }
}
array_push($allRows , $data);

and changed this line:

$sheet->fromArray($products, null, 'A1', false, false);

to

$sheet->fromArray($allRows, null, 'A1', false, false);

now here is what I have:

here is my full function currently:

public function export(Request $request) {
      $input = $request->except('_token');
      foreach ($input['cb'] as $key => $value) {
        if ($value== 'on') {
          $getRealInput[$key] = $input['defaultname'][$key];
        }
      }

      $products = Product::select($getRealInput)->get();


      Excel::create('products', function($excel) use($products, $request) {
        $excel->sheet('sheet 1', function($sheet) use($products, $request){

          $input = $request->except('_token');
          foreach ($input['cb'] as $key => $value) {
            if ($value== 'on') {
              $getCustomInput[$key] = $input['customname'][$key];
            }
          }


          // test code of adding subspacifications
          $allRows = array();
          $data = array();
          foreach($products as $product){
              $specs = $product->subspecifications;
              foreach($specs as $spec){
                    $data[] = $spec->specification->title;
                    $data[] = $spec->title;
              }
          }
          array_push($allRows , $data);
          $sheet->fromArray($allRows, null, 'A1', false, false);
          //
          // $sheet->fromArray($products, null, 'A1', false, false);
          $sheet->row(1, $getCustomInput);
        });
      })->export('csv');
      return redirect()->back();
    }

UPDATE 2

Well tonight I've played with my codes a lot and FINALLY :) I got what I needed, here is how:

//codes...

// Here is you custom columnn logic goes
          foreach($products as $product){
            $specifications = DB::table('products')
            ->where('products.id', $product->id)
            ->join('product_subspecification', 'product_subspecification.product_id', '=', 'products.id')
            ->join('subspecifications', 'subspecifications.id', '=', 'product_subspecification.subspecification_id')
            ->select('subspecifications.title')
            ->pluck('title');

            $product['specifications'] = rtrim($specifications,',');
          }
          //


          $sheet->fromArray($products, null, 'A1', false, false);
          $sheet->row(1, $getCustomInput);

//... rest of the codes

This will give me my products specifications, however there is 3 little issues:

1. I do not have heading for my specifications in CSV file
2. Products without specification shows [] instead of nothing
3. products with specification also covers them with [] and ""

Here I provided screenshot for better understanding:

Answer 1

You need to prepare custom column Specifications by looping through products. Here is your fix,

public function export(Request $request) {

  $headers[] = [
                'Id',
                'Title',
                'Specifications',
            ];


  $input = $request->except('_token');
  foreach ($input['cb'] as $key => $value) {
    if ($value== 'on') {
      $getRealInput[$key] = $input['defaultname'][$key];
    }
  }

  $products = Product::select($getRealInput)->with('subspecifications')->get()->toArray();

  Excel::create('products', function($excel) use($headers,$products, $request) {
    $excel->sheet('sheet 1', function($sheet) use($headers,$products, $request){

      $input = $request->except('_token');
      foreach ($input['cb'] as $key => $value) {
        if ($value== 'on') {
          $getCustomInput[$key] = $input['customname'][$key];
        }
      }
      // Here is you custom columnn logic goes
          foreach($products as $product){
            $specs = "";
            $specifications = DB::table('products')
            ->where('products.id', $product->id)
            ->join('product_subspecification', 'product_subspecification.product_id', '=', 'products.id')
            ->join('subspecifications', 'subspecifications.id', '=', 'product_subspecification.subspecification_id')
            ->select('subspecifications.title')
            ->pluck('title');
            foreach($specifications as $spec){
              $specs = $specs .','.$spec;
            }
            $product['specifications'] = ltrim($specs,',');
          }
          //
      $mergedProducts = array_merge($headers, $products);
      $sheet->fromArray($mergedProducts, null, 'A1', false, false);
      $sheet->row(1, $getCustomInput);
    });
  })->export('csv');
  return redirect()->back();
}

Update

As per your sheet image I can assume you have only three columns Id, Title and Specifications, you can change header array according to the columns you are getting from DB.

Answer 2

Yes its possible. create array for row ie. data = array(); push cell data to array

you can fetch relation data using eloquent or join as well, here I am fetching inside loop.

Updated Function as below:

I tried to match with your data structure

  public function export(Request $request) {
  $input = $request->except('_token');
  foreach ($input['cb'] as $key => $value) {
    if ($value== 'on') {
      $getRealInput[$key] = $input['defaultname'][$key];
    }
  }

  $products = Product::select($getRealInput)->get();


  Excel::create('products', function($excel) use($products, $request) {
    $excel->sheet('sheet 1', function($sheet) use($products, $request){


      // test code of adding subspacifications
      $allRows = array();
      array_push($allRows , ['id', 'title', 'specifications']); // Added title row
      $data = array();
      foreach($products as $product){
          $data[] = $product->id;    // Added product fields 
          $data[] = $product->title;
          $specs = $product->subspecifications;
          $spec_details = "";
          foreach($specs as $spec){                    
                $spec_details .= $spec->specification->title.':'. $spec->title. ' '; // appended specification:subspecification 
          }
          $data[] = $spec_details;
      }
      array_push($allRows , $data);
      $sheet->fromArray($allRows, null, 'A1', false, false);
      //
      // $sheet->fromArray($products, null, 'A1', false, false);
      //$sheet->row(1, $getCustomInput);   // commented
    });
  })->export('csv');
  return redirect()->back();
}

Answer 3

1. I do not have heading for my specifications in CSV file

To solve the issue, you can define header and use use array_merge(). For I.E.

$headers[] = [
                'Title',
                'Specifications',
            ];
$products= array_merge($headers, $products);

2. Products without specification shows [] instead of nothing
3. products with specification also covers them with [] and ""

For 2nd and 3rd point you can use implode() to get rid of []

$product['specifications'] = implode(',', $specifications);

Hope this helps

Answer 4

It works for me. very simple

// Headings//
$headers[] = ['Id', 'Name'];

// 2 Rows //
$data[0] = ['1', 'John'];
$data[1] = ['2', 'Roger'];

Excel::create('report', function($excel) use($headers, $data) {

    $excel->sheet('sheet 1', function($sheet) use($headers, $data){
        $merged_records = array_merge($headers, $data);
        $sheet->fromArray($merged_records, null, 'A1', false, false);
    });
})->export('csv');