Laravel Excel, export from model, design problems

Question

I am trying to export some data from my model to excel scheet using "Laravel excel", I did it, but my result is far from what I really need

Here is what I got in the downloaded file:

And this is what I really want to accomplish:

My controller part:

//casting export... Excel::create('ExcelExport', function($excel) use($filters, $agents) { $main_arr = array(); foreach($agents as $value){ $main_arr[] = Card::cardForUser($value, $filters)->toArray(); } $excel->sheet('Sheetshit', function($sheet) use($main_arr) { //You may ask me "why are you using foreach?" // and my answer will be:"I don`t KNOW, because it WORKS!" foreach($main_arr as $one){ $sheet->fromArray($one); } }); })->export('xls'); 

Model part:

 public static function cardForUser($user_id, $filters = array()){ $query = static::UserId($user_id);//just gets 'where user id' foreach($filters['fields'] as $select){ $query->addSelect($select); } return $query->get(); } public function scopeUserId($query, $user_id) { return $query->where('user_id', '=', $user_id); } 

$filters array consists of field names from the database, so it basically decides which columns to include in the export. This means that my $main_arr can have an internal field length of 1 to 5. Each agent can have many rows in the database or nothing

An example of a $ main_arr dump with 4 filters set:

  array (size=8) 0 => array (size=10) //thats will be first agent 0 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Chrysler' (length=8) 'ts_model' => string 'PT CRUISER' (length=10) 1 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Opel' (length=4) 'ts_model' => string 'Corsa' (length=5) 2 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Dodge' (length=5) 'ts_model' => string 'Stratus' (length=7) 3 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string ''ђ—' (length=6) 'ts_model' => string '2112' (length=4) 4 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Mercedes-Benz' (length=13) 'ts_model' => string 'E 270' (length=5) 5 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string ''ђ—' (length=6) 'ts_model' => string '21140 LADA SAMARA' (length=17) 6 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'LADA' (length=4) 'ts_model' => string '213100 LADA 4…4' (length=16) 7 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string ''ђ—' (length=6) 'ts_model' => string '21110' (length=5) 8 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Chevrolet' (length=9) 'ts_model' => string 'Lanos' (length=5) 9 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'VOLKSWAGEN' (length=10) 'ts_model' => string 'PASSAT' (length=6) //thats will be second agent 1 => array (size=10) 0 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Mercedes' (length=8) 'ts_model' => string 'Benz' (length=4) 1 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Chevrolet' (length=9) 'ts_model' => string 'Corvette' (length=8) 2 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Chevrolet' (length=9) 'ts_model' => string 'Corvette' (length=8) 3 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Chevrolet' (length=9) 'ts_model' => string 'Corvette' (length=8) 4 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Chevrolet' (length=9) 'ts_model' => string 'Corvette' (length=8) 5 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Chevrolet' (length=9) 'ts_model' => string 'Corvette' (length=8) 6 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Mazeratti' (length=9) 'ts_model' => string 'M4' (length=2) 7 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Ferrari' (length=7) 'ts_model' => string 'F4' (length=2) 8 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Mazda' (length=5) 'ts_model' => string '5' (length=1) 9 => array (size=4) 'date_start' => string '06.08.2014 10:00:00' (length=19) 'ts_category' => int 2 'ts_make' => string 'Test' (length=4) 'ts_model' => string 'Test' (length=4) etc... 

Sooo ... my questions are :

• How to set the main title ("Title of title" in img example)?
• Why do I have column headings (date_start, ts_category, ts_make, ts_model) disappear in the first element of the array? (You can see on the first img that I don’t have headers “date start” and “ts_category” for the first submatrix. By the way, sometimes I don’t have headers for the first submatrix!)
• Can I make sub-array headers bold (ts_make, etc.)? How?
• How can I make subtitles for each agent (I plan to use the sub-array key to display the number of agents)?

---

UPDATE Sent working code as a response.

Answer 1

I will not answer all your questions, because you will need to look at the documentation yourself. However, I will show you how to get this effect:

and I think this will help you more than explain what you did wrong

The code for the above excel file is below:

 Excel::create('ExcelExport', function ($excel) { $excel->sheet('Sheetname', function ($sheet) { // first row styling and writing content $sheet->mergeCells('A1:W1'); $sheet->row(1, function ($row) { $row->setFontFamily('Comic Sans MS'); $row->setFontSize(30); }); $sheet->row(1, array('Some big header here')); // second row styling and writing content $sheet->row(2, function ($row) { // call cell manipulation methods $row->setFontFamily('Comic Sans MS'); $row->setFontSize(15); $row->setFontWeight('bold'); }); $sheet->row(2, array('Something else here')); // getting data to display - in my case only one record $users = User::get()->toArray(); // setting column names for data - you can of course set it manually $sheet->appendRow(array_keys($users[0])); // column names // getting last row number (the one we already filled and setting it to bold $sheet->row($sheet->getHighestRow(), function ($row) { $row->setFontWeight('bold'); }); // putting users data as next rows foreach ($users as $user) { $sheet->appendRow($user); } }); })->export('xls'); 

Answer 2

• How to set the main title ("Title of title" in img example)?

  Look at the documentation, it is very simple:

   $sheet->prependRow(1, array( 'Example header' ))->cell('A1', function($cell) { $cell->setFontWeight('bold'); $cell->setFontSize(18); }); 

• Can I make sub-array headers bold (ts_make, etc.)? How?

  Count which lines should be bold:

   $count = 2; foreach($main_arr as $one){ $sheet->fromArray($one, null, 'A2'); $sheet->row($count, function($row) { $row->setFontWeight('bold'); }); $count += count( $one ) + 1; } 

• How can I create subtitles for each agent (I plan to use sub-arraykey to display the number of agents)?

  Similarly above :)

Answer 3

With the help of Marcin and r4xz, I came to this working code:

 Excel::create('Filename', function($excel) use($filters, $agents) { $excel->sheet('sheetname', function($sheet) use( $agents, $filters) { // first row with header title $sheet->mergeCells('A1:E1');//merge for title $sheet->row(1, function ($row) { $row->setFontSize(18); }); $sheet->row(1, array('Header Stuff') );//add title $sub_titles = array(/*content*/);//array for agents names $count = 2;//pointer for rows with agents name foreach($agents as $agent_id){ //get agent name. $agent_name = Model::AgentById(Auth::user(), $agent_id); $sheet->appendRow(array($agent_name));//add agent name $sheet->mergeCells('A'.$count.':'.'E'.$count);//merge for agent name $sheet->row($sheet->getHighestRow(), function ($row) {//get last row at the moment and style it //$row->setFontWeight('bold'); $row->setFontSize(14); }); $cards = CardModel::cardForUser($agent_id, $filters)->toArray();//get cards for agent //if there is cards show them, else show nothing message if(count($cards) > 0){ $sheet->appendRow($sub_titles);//add sub-titles of agents fields $sheet->row($sheet->getHighestRow(), function ($row) { $row->setFontWeight('bold'); }); foreach($cards as $card){//add all agent fields $sheet->appendRow($card); } $count += count($cards) + 2; } else{ $sheet->appendRow(array('nothing to show')));//add nothing to show message $sheet->row($sheet->getHighestRow(), function ($row) { $row->setFontWeight('bold'); }); $count += 2; } } }); })->export('xls'); 

Answer 4

I recommend that you create an excel file with the style you want, and then attach your data to this template file, following this example:

  public function export_xls($expense_id, $file_format_id){ $expense = Expense::find($expense_id); $file_format = FileFormat::find($file_format_id); $routes = DB::table('buy_orders') ->join('expenses','expenses.id','=','buy_orders.expense_id') ->join('users','expenses.user_id','=','users.id') ->select( 'buy_orders.code', 'buy_orders.cost_center', 'buy_orders.book_account', 'buy_orders.active', 'buy_orders.expenditure', 'buy_orders.inventory', 'buy_orders.quantity', 'buy_orders.price_unit', 'buy_orders.description', 'buy_orders.estimated_value', 'buy_orders.destination', 'buy_orders.delivery_date' ) ->where('buy_orders.expense_id','=',$expense_id) ->orderBy('buy_orders.created_at','desc') ->get(); $data = json_decode(json_encode((array) $routes), true); Excel::load('/storage/app/template.xls', function($file) use($expense, $data){ $file->setActiveSheetIndex(0)->setCellValue('D8', $expense->user->name); $file->setActiveSheetIndex(0)->setCellValue('L8', $expense->application_date); $file->setActiveSheetIndex(0)->setCellValue('P8', $expense->code); $file->setActiveSheetIndex(0)->setCellValue('D45', $expense->description); $row = 13; foreach($data as $key => $temp) { $col = 1; foreach(array_keys($temp) as $value) { $file->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $temp[$value]); $col++; } $row++; } })->export('xls'); } 

The idea is make and an array, and then attached using the setCellValueByColumnAndRow method