将自定义列添加到 laravel excel
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/49809945/
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
add custom column to laravel excel
提问by mafortis
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?
我正在使用maatwebsite/excel，我想知道在将数据导出为 CSV 时是否可以添加自定义列？
Explanation
解释
I am successfully exporting my productsdata, but my products have other option which is not stored in my products table such as: specification.
我成功导出我的products数据，但我的产品有哪些不存储在我的产品表，如其他选择：specification。
my specifications are stored in 2 different tables named specificationswhere is parent like CPUand subscpecificationswhere child's are stored like: Core i5.
我的规格存储在名为2页不同的表specifications在哪里父喜欢CPU和subscpecifications这里的孩子的存储，如：Core i5。
another table i am using to store child's id and products id in order to relate each product to their subspecifications.
我用来存储孩子的 id 和产品 id 的另一个表，以便将每个产品与其子规格相关联。
Sounds Complecated right? :) here i provide ugly map to get the logic:)
听起来很复杂吧？:) 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.
在我的 csv 文件中添加额外的列并包含每个产品的所有规格。
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
问题
- Is that possible?
- If yes,Base on my function above, how do I do it?
- 那可能吗？
- 如果是，根据我上面的功能，我该怎么做？
Thanks in advance.
提前致谢。
UPDATE 1
更新 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
更新 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:
这会给我我的产品规格，但是有 3 个小问题：
- I do not have heading for my specifications in CSV file
- Products without specification shows
[]instead of nothing - products with specification also covers them with
[]and""
- 我在 CSV 文件中没有关于我的规格的标题
- 没有规格的产品显示
[]而不是没有 - 具有规范的产品也涵盖了它们
[]和""
Here I provided screenshot for better understanding:
在这里，我提供了屏幕截图以便更好地理解：
回答by Faraz Irfan
You need to prepare custom column Specificationsby 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.
根据您的工作表图像，我可以假设您只有三列 Id、Title 和 Specifications，您可以根据从 DB 获得的列更改标题数组。
回答by Bhavin Solanki
- I do not have heading for my specifications in CSV file
- 我在 CSV 文件中没有关于我的规格的标题
To solve the issue, you can define header and use use array_merge(). For I.E.
要解决此问题，您可以定义 header 并使用 use array_merge()。浏览器
$headers[] = [
'Title',
'Specifications',
];
$products= array_merge($headers, $products);
- Products without specification shows [] instead of nothing
- products with specification also covers them with [] and ""
- 没有规格的产品显示 [] 而不是没有
- 有规格的产品也用 [] 和 "" 覆盖它们
For 2nd and 3rd point you can use implode()to get rid of []
对于第 2 点和第 3 点，您可以implode()用来摆脱[]
$product['specifications'] = implode(',', $specifications);
Hope this helps
希望这可以帮助
回答by Umar Tariq
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');
回答by Sharad Kale
Yes its possible.
create array for row ie. data = array();push cell data to array
是的，它可能。为行创建数组，即。data = array();将单元格数据推送到数组
you can fetch relation data using eloquent or join as well, here I am fetching inside loop.
您也可以使用 eloquent 或 join 获取关系数据，这里我正在获取内部循环。
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();
}
