php Laravel Excel，从模型导出，样式问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25998397/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-08-25 18:05:52 来源:igfitidea点击:
Laravel Excel , exporting from a model, styling issues
提问by Zanshin13
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:
我试图从我的模型中导出一些数据到带有“Laravel excel”的 excel scheet，我已经做到了，但我的结果与我真正需要的相去甚远
这就是我在下载文件中得到的：
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 all
$filters数组由来自 DB 的字段名称组成，因此它基本上决定了导出中包含哪些列。这意味着我$main_arr的内部字段的长度可能从 1 到 5。每个代理可以在 DB 中有很多行或根本没有行
Example of $main_arr dump with 4 filters set:
设置了 4 个过滤器的 $main_arr 转储示例：
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:
Sooo...我的问题是：
- 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)?
- 如何设置主标题（示例 img 中的“标题内容”）？
- 为什么我的列标题（date_start、ts_category、ts_make、ts_model）在第一个数组元素中消失了？（您可以在第一个 img 中看到我没有第一个子数组的“日期开始”和“ts_category”标题。顺便说一句，有时我根本没有第一个子数组的标题！）
- 我可以使我的子数组的标题加粗（ts_make 等）吗？如何？
- 如何为每个代理制作副标题（我打算使用子数组键来显示多个代理）？
UPDATEPosted working code as answer.
更新发布工作代码作为答案。
回答by Marcin Nabia?ek
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文件的代码如下：
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');
回答by Wojciech Mleczek
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 :)
如何设置主标题（示例 img 中的“标题内容”）？
看文档，很简单：
$sheet->prependRow(1, array( 'Example header' ))->cell('A1', function($cell) { $cell->setFontWeight('bold'); $cell->setFontSize(18); });我可以使我的子数组的标题加粗（ts_make 等）吗？如何？
计算哪些行必须加粗：
$count = 2; foreach($main_arr as $one){ $sheet->fromArray($one, null, 'A2'); $sheet->row($count, function($row) { $row->setFontWeight('bold'); }); $count += count( $one ) + 1; }如何为每个代理制作副标题（我打算使用 sub-arraykey 来显示多个代理）？
以与上述类似的方式:)
回答by Zanshin13
With help of Marcinand r4xzI`ve come 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');
回答by David Tataje
I recommend you create an excel file with the style that you want, and then attach your data to this template file, follow this example:
我建议你用你想要的样式创建一个 excel 文件，然后将你的数据附加到这个模板文件中，按照这个例子：
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 array and then attach using the method setCellValueByColumnAndRow
这个想法是 make 和 array 然后使用 setCellValueByColumnAndRow 方法附加
