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:

Solution

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.

Comments

[mafortis]

sorry for very late answer bro, but this doesn't works. there is some issues in you code that concern me: 1 where is this coming from? $allRows = array(); . 2 my products are already looped i don't need to use this foreach($products as $product), 3 this does not provide true relation $specs = Spec::where('product_id', $product->id)->get();

[mafortis]

I have shared update in my question please check it. PS for this part i still have problem $data[] = $product->field1; as my columns coming by selection $getCustomInput[$key] = $input['customname'][$key]; i cannot use static method as you shared in your answer is there other way to achieve that? as you see my csv is empty because of that. THANK YOU.

[mafortis]

i get Undefined offset: 0 on $sheet->fromArray($products, null, 'A1', false, false);

[Faraz Irfan]

Can you please dd($products) ?

[mafortis]

thanks bro, here is issues with your method: 1 as i said before, i cannot have my products table data statically like $data[] = product->id; why? because i'm using filter on my columns by foreach ($input['cb'] as $key => $value) { that's why i have $sheet->row(1, $getCustomInput);. 2 your recent code shows all products in row 2 except shows each product detail in one row 3 i get : between tags except , .

[mafortis]

i assume specifications must add to each product and not end of my collection right?

[Faraz Irfan]

I have updated the code above please run now and let me know.

[mafortis]

preg_match() expects parameter 2 to be string, array given

[mafortis]

OK, I took some screenshots for you to see what exactly happened: 1 ibb.co/daZ4KH 2 ibb.co/cwDyeH

[mafortis]

still getting preg_match() expects parameter 2 to be string, array given

[mafortis]

bro are you there? any idea?

[Faraz Irfan]

can you please do echo "<pre>"; print_r($products); so that I can understand better ?

[mafortis]

in which part i add that pre ?

[Faraz Irfan]

before this line $sheet->fromArray($products, null, 'A1', false, false); add the above statements.

[mafortis]

the result become so messy! something between white page and laravel black error page, really can't get anything out of it.

[mafortis]

ok bro i just acted fast before my view broke i got copy the codes here i shared php file including what i could copy from your pre code ufile.io/oyq42

[mafortis]

Bro I got my specifications, please see my update. thanks.

[Faraz Irfan]

Updated answer please try now.

[mafortis]

hi bro, o answer your update i have to say: no, i have more than id,title,specification (i can have all my table columns) so i really can't use header part and $mergedProducts = array_merge($headers, $products); you provided, But except headers and merge part, your function works perfectly just no have heading ibb.co/i3amOc

[mafortis]

what if, we use their parents as heading? currently we are getting subspecifications like core i7 what if we get specifications as heading like cpu (as heading) and core i7 (as data of it) ?

[Faraz Irfan]

Then you have to define all you db columns in header plus custom specifications column at the last.

[mafortis]

i do have all my columns as header i just have ability to filter them so i can only get id and title or all of them, but for specification matter if you see my first update (no.1) in there i have both specifications and subspecifications in header that time i did it like foreach($specs as $spec){ $data[] = $spec->specification->title; $data[] = $spec->title; } now we need the same approach but different $data_name for sub and specs to define which one goes where.

[Faraz Irfan]

Disucssing is getting long, your main issue is solved, closed it and start another one if you have other queries thanks

[mafortis]

is the same query bro "need header for results i'm getting", if you no longer able to help i understand and appreciate all your helps

[mafortis]

thanks for answer, those problems are solved, the only last issue is my specifications header and why your answer won't work here i explained it stackoverflow.com/questions/49809945/… please see if you can help with that.