I'm trying to export data from leads table to excel using PHP/Laravel, the leads table caמ be customized to show specific columns and the export columns should be same as the table columns. the problem is - if column not exists in a specific row the column not shown empty and the 'xls' file is exported really messy... my code looks like this:
public function excelExport(Request $request, $client_id=null)
{
$client_id = is_null($client_id) ? \Auth::client()->id : $client_id;
if(is_null($this->client_users)){
$this->client_users = $this->clientsController->listClientUsers($client_id);
}
$columns = $this->account_settings->getColumnsDef($client_id);
$query = Lead::select(array_keys($columns))->where('client_id',strval($client_id))->where('deleted',0);
$query = $this->setQueryDates($request,$query,$client_id);
$query = $this->leadsFiltersController->setExportQuery($request,$query);
$arr = $query->get()->toArray();
Excel::create('leads' , function($excel) use ($arr,$columns){
$excel->sheet('Leads' , function($sheet) use ($arr,$columns){
$rows = [];
$count = 0;
foreach($arr as $lead){
$row = $this->setExportRowData($lead,$count,$columns);
$rows[] = $row;
$count++;
}
$sheet->fromArray($rows);
});
})->export('xls');
private function setExportRowData($lead,$count,$columns,$order = true)
{
$row = [];
$order = null;
foreach($lead as $k => $v) {
if (is_array($v)) {
switch (strtolower($k)) {
case "ga_details":
if (count($v) > 2) {
foreach ($v as $key => $ga_detail) {
if ($key != "realTime_data" && $key != "uacid") {
$row[$key] = $ga_detail;
}
}
}
break;
case "status":
if (isset($v['name']) && count($v['name'])) {
$row['status'] = $v['name'];
} else {
$row['status'] = "no status";
}
break;
case "owner":
if (isset($v['owner_id']) && count($v)) {
$row['owner'] = $this->getClientOwnerUserName($this->client_users, $v['owner_id']);
} else {
$row['owner'] = "no owner";
}
break;
case "prediction":
if (isset($v['score']) && count($v)) {
$row['prediction'] = $v['score'];
} else {
$row['prediction'] = "no prediction";
}
break;
case "the_lead":
foreach ($v as $key => $lead_detail) {
$row[$key] = $lead_detail;
}
break;
case "quality":
if (isset($v['name'])) {
$row['quality'] = $v['name'];
} else {
$row['quality'] = "no quality";
}
break;
case "feeds":
if (isset($v['feeds']) && count($v['feeds'])) {
$row['feeds'] = array_pop($v['feeds'])['message'];
}
break;
default :
$row[$k] = $v;
break;
}
} else {
if ($k != "_id") {
$row[$k] = $v;
}
}
}
return $order == true ? sortArrayByArrayValues($this->order,$row) : $row;
}
sortArrayByArrayValues function looks like this:
function sortArrayByArrayValues(Array $array, Array $orderArray)
{
$rtn = [];
foreach($array as $arr){
if(isset($orderArray[$arr])){
$rtn[$arr] = $orderArray[$arr];
}else{
$rtn[$arr] = '';
}
}
return $rtn;
}
I really have no clue how to solve it, appreciate any help!!! :)
