Laravel Excel , exporting from a model, styling issues
25,785
Solution 1
I won't answer all your questions because you will need to look at documentation on your own. However I will show you how to get such effect:
and I think it will help you more than explaining what you did wrong
The code for 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');
Solution 2
How do I set main title ("Header stuff" in example img)?
Look at documentation, it's very easy:
$sheet->prependRow(1, array( 'Example header' ))->cell('A1', function($cell) { $cell->setFontWeight('bold'); $cell->setFontSize(18); });Can I make my sub-array's headers bold(ts_make, etc)? How?
Count what rows must be bolded:
$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 make sub-titles for every agent(I planning to use sub-arraykey to display a number of agent)?
In a similar way as above :)
Author by
Zanshin13
Updated on June 07, 2020Comments
-
Zanshin13 almost 4 years
I am trying to export some data from my model to excel scheet with "Laravel excel", I have done it, but my result is far away from what I really need
That is what I`ve got in downloaded file:
And that 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); }$filtersarray consist of fields names from DB, so it basically decides which columns include in export. That means that my$main_arrmay have length of inner fields from 1 to 5. Every agent can have a lot of rows in DB or none at allExample of $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 do I set main title ("Header stuff" in example img)?
- why do I have my columns headers(date_start, ts_category,ts_make,ts_model) disappear in first array element? (You can see at the first img that I don't have "date start" and "ts_category" headers for first sub-array. BTW sometimes I don`t have headers at all for first sub-array!)
- Can I make my sub-array's headers bold(ts_make, etc)? How?
- How can I make sub-titles for every agent(I planning to use sub-array key to display a number of agent)?
UPDATE Posted working code as answer.