Exporting Excel file using PHPExcel

exaple audiance profile export .csv

Filter
"Show all respondents."

Sample
Brand,Size,"% of Original",MOE,Quality
"Smith and Wesson Owners",1155,100%,2.88%,High
"Survey Takers",4534,100%,1.46%,High
"Taurus Owners",1740,100%,2.35%,High

Demographics
Gender
Responses,"Smith and Wesson Owners","Survey Takers","Taurus Owners"
Male,90.9%,92.6%,94.0%
Female,9.1%,7.4%,6.0%

Age
Responses,"Smith and Wesson Owners","Survey Takers","Taurus Owners"
"13 - 17",0%,0%,0.1%
"18 - 24",8.4%,9.9%,7.2%
"25 - 34",15.4%,16.8%,17.7%
"35 - 44",19.2%,21.9%,23.3%
"45 - 54",26.3%,25.4%,26.8%
"55 - 64",20.7%,17.7%,16.4%
65+,10.0%,8.2%,8.4%

Ethnicity
Responses,"Smith and Wesson Owners","Survey Takers","Taurus Owners"
White/Caucasian,82.8%,83.0%,84.0%
"African American",1.5%,1.6%,1.3%
Hispanic,2.5%,2.8%,2.9%
Asian,0.9%,0.8%,0.5%
Indian,0%,0.1%,0.2%
"Native American",1.7%,1.9%,2.0%
Other,2.0%,1.5%,1.4%
"I prefer not to answer",8.5%,8.2%,7.8%

excel_ap_export.php

<?

namespace LoudDoor\App\FanSatisfaction\Models;
include 'PHPOffice\PHPExcel/...';


class ExcelApExport extends ReportExport
{

public function export($Sets, $disp_filter)
	{
		$excel_out  = new PHPExcel();
		$excel_out->setActiveSheetIndex(0);

		var_dump($excel_out);

		$csv_out = $this->outputHumanReadableFilter($disp_filter);
		$csv_out []= array();

		$csv_out = array_merge($csv_out, $this->outputSampleDetails($Sets));
		$csv_out []= array();

		$csv_out = array_merge($csv_out, $this->outputQuestions($Sets));

		$fp = fopen('php://output', 'w');
		ob_start();
		$rowCount = 1;
		foreach ($csv_out as $line)
		{
			$colCount = 'A';
			$col_values = explode(',', $line);
			foreach($col_values as $col_value){
				$excel_out->getActiveSheet()->setCellValue($colCount.$rowCount, $col_value);
				$colCount++;
			}
			$rowCount++;
		}

		return $excel_out;

	}


protected function outputHumanReadableFilter($disp_filter)
	{
		$csv_out = array();

		/* Generate the filter header */
		$csv_out []= array("Filter");

		if (!empty($disp_filter))
		{
			$csv_out []= array("Show only respondents who meet the following criteria:");

			foreach ($disp_filter as $disp_filter_question)
			{
				$line = array($disp_filter_question['name']);

				if ($disp_filter_question['responses'])
				{
					$line []= implode(", ", array_map(function($r)
					{
						return $r['description'];
					}, $disp_filter_question['responses']));
				}
				else
				{
					$line []= "Any valid response";
				}

				$csv_out []= $line;
			}
		}
		else
		{
			$csv_out []= array("Show all respondents.");
		}
		//end filter

		return $csv_out;
	}

	protected function outputSampleDetails($Sets)
	{
		/* show sample info */
		$csv_out []= array("Sample"); //section header
		$csv_out []= array("Brand", "Size", "% of Original", "MOE", "Quality"); //sample column headers

		foreach ($Sets as $Set)
		{
			$CT = $Set['crossTabs']['base'];
			$line = array(
				$Set['name'],
				$CT['sample']['size'],
				(round($CT['sample']['size'] / $CT['sample']['unfiltered_size'], 3) * 100) . '%',
				$CT['sample']['moe'] . '%',
				ucwords($CT['sample']['quality'])
			);

			$csv_out []= $line;
		}
		//end sample info

		return $csv_out;
	}

	protected function outputQuestions($Sets)
	{
		$csv_out = array();

		foreach ($this->group_definitions as $section => $section_question_ids)
		{
			if ($section == 'nps') continue;
			if (count($section_question_ids) == 0) continue;

			$csv_out []= array($this->sections[ $section ]);
			$csv_out = array_merge($csv_out, $this->outputQuestionSection($section_question_ids, $Sets));
			$csv_out []= array();
		}

		return $csv_out;
	}

	protected function outputQuestionSection($question_ids, $Sets)
	{
		$csv_out = array();

		foreach ($question_ids as $qid)
		{
			$question_id = $qid;
			$subquestion_id = null;

			if (strpos($question_id, '.') !== false)
			{
				list($question_id, $subquestion_id) = explode('.', $question_id, 2);
			}

			$question = $this->questions[ $question_id ];

			$line = array($question['name']);
			if ($subquestion_id)
			{
				$line []= $question['subquestions'][ $subquestion_id ]['description'];
			}

			$csv_out []= $line;

			$csv_out = array_merge($csv_out, $this->outputQuestion($qid, $question, $Sets));
			$csv_out []= array();
		}

		array_pop($csv_out); //remove the last blank space;

		return $csv_out;
	}

	protected function outputQuestion($qid, $question, $Sets)
	{
		$csv_out = array();
		$csv_out = $this->outputQuestion_MultiSet($Sets, $qid, $question);

		return $csv_out;
	}

	protected function outputQuestion_MultiSet($Sets, $qid, $question)
	{
		$csv_out = array();

		$question_id = $qid;
		$subquestion_id = null;

		if (strpos($question_id, '.') !== false)
		{
			list($question_id, $subquestion_id) = explode('.', $question_id, 2);
		}

		$line = array("Responses");
		foreach ($Sets as $Set)
		{
			$line []= $Set['name'];
		}
		$csv_out []= $line;

		foreach ($question['responses'] as $response)
		{
			$line = array($response['description']);

			foreach ($Sets as $Set)
			{
				$CT = $Set['crossTabs']['base'];
				$metric = isset($CT['metrics'][ $qid ]['responses'][ $response['id'] ])? $CT['metrics'][ $qid ]['responses'][ $response['id'] ] : false;

				$BaseCT = $Set['crossTabs']['no_filter_base'];
				$base_metric = isset($BaseCT['metrics'][ $qid ]['responses'][ $response['id'] ])? $BaseCT['metrics'][ $qid ]['responses'][ $response['id'] ] : false;

				if ($metric && !empty($metric['pct']))
				{
					$line []= sprintf("%.1f", $metric['pct']) . '%';
				}
				else
				{
					$line []= '0%';
				}
			}

			$csv_out []= $line;
		}

		return $csv_out;
	}



}

?>

graph_survey_controller.js

		exportReportClicked: function(evt)
		{
			var $target = $(evt.target),
				type = 'pdf';

			if ($target.hasClass('csv_export'))
			{
				type = 'csv';
			}
			else if ($target.hasClass('excel_export'))
			{
				type = 'excel';
			}
			else if ($target.hasClass('raw_csv_export'))
			{
				type = 'raw_csv';
			}

			evt.preventDefault();

			this.exportReport(type);

			return false;
		},

gs_analytics_controller.php

<?php

use \LoudDoor\App\FanSatisfaction\Models\AudienceProfile;
use \LoudDoor\App\FanSatisfaction\Models\AudienceProfileReport;
use \LoudDoor\App\FanSatisfaction\Models\CustomSurvey;
use \LoudDoor\App\FanSatisfaction\Models\UserActionLog;
use \LoudDoor\App\FanSatisfaction\Models\Prospector;

use \LoudDoor\App\FanSatisfaction\Models\ReportExport;
use \LoudDoor\App\FanSatisfaction\Models\CsvRawGsExport;
use \LoudDoor\App\FanSatisfaction\Models\CsvApExport;
use \LoudDoor\App\FanSatisfaction\Models\ExcelApExport;

use \LoudDoor\App\FanSatisfaction\Models\PdfReportExport;
use \LoudDoor\App\FanSatisfaction\Models\Reperio;

Library::load("lessphp");
require 'gordon/gordon.php';

class GsAnalyticsController extends DashboardController
{
	public $SurveyType = "standalone";

	public function profile($request)
	{
		@list($nil, $survey_id, $current_section) = func_get_args();
		if ($current_section == null)
		{
			$current_section = "demographics";
		}

		$request->set('survey_id', $survey_id);

		$CustomSurvey = CustomSurvey::findSurvey($survey_id, $this->User->get('id'));

		if (false == $CustomSurvey)
		{
			Session::setFlash("You do not have access to this survey.", "flash_error");
			$request->redirect('/graph-surveys');
		}

		$questions = $CustomSurvey->getQuestions( $this->UserLogin->get('locale') );
		$ReportSchema = $CustomSurvey->loadSchema( $this->UserLogin->get('locale') );
		$schema = $ReportSchema->getSchema(false, $this->UserLogin->get('locale'));
		unset($schema['nps']); //graph surveys don't have the nps values
		$options = $ReportSchema->getOptions($this->UserLogin->get('locale'));

		$group_definition = $schema;
		$sections = $CustomSurvey->getSectionMap();

		$request->set('section_names', $sections);

		// do we need both of these?
		$request->set('group_definition', $group_definition);
		$request->set('filter_questions', $group_definition);

		$request->set('questions', $questions);
		$request->set('CustomSurvey', $CustomSurvey);

		if ($survey_id == 1343 && $this->User->get('id') == 1519)
		{
			$request->set('sample_correction', 'ld:17648521247');
		}

		if ($survey_id == 1480 && $this->User->get('id') == 1694)
		{
			$request->set('sample_correction', 'ld:200969413280005');
		}

		if ($survey_id == 1180)
		{
			$request->set('sample_correction', 'ld:379272436002');
		}

		if ($survey_id == 1548)
		{
			$request->set('sample_correction', 'ld:11936081183');
		}

		return $request;
	}

	public function export($request, $survey_id)
	{
		if (false == isset($request->p['data'], $request->p['filter'], $request->p['report'], $request->p['type']))
		{
			Session::setFlash("Invalid request for report export. Please contact support if this continues.", "flash_error");
			$request->redirect('/dashboard/reports');
		}

		$CustomSurvey = CustomSurvey::findSurvey($survey_id, $this->User->get('id'));

		if (false == $CustomSurvey)
		{
			Session::setFlash("You do not have access to this survey.", "flash_error");
			$request->redirect('/graph-surveys');
		}

		$questions = $CustomSurvey->getQuestions( $this->UserLogin->get('locale') );
		$ReportSchema = $CustomSurvey->loadSchema( $this->UserLogin->get('locale') );
		$schema = $ReportSchema->getSchema(false, $this->UserLogin->get('locale'));
		unset($schema['nps']); //graph surveys don't have the nps values
		$options = $ReportSchema->getOptions($this->UserLogin->get('locale'));

		$group_definition = $schema;
		$sections = $CustomSurvey->getSectionMap();

		$sample_corrections = array(
			'none' => "None (raw)",
			'insights' => "Facebook Insights",
			'fb_census' => "Facebook Census",
			'us_census' => "US Census",
			'ld' => "LoudDoor Graph",
		);

		if ($survey_id == 1343 && $this->User->get('id') == 1519)
		{
			$request->set('sample_correction', 'ld:17648521247');
		}


		$sets = json_decode($request->p['data'], true);
		$filter_meta = json_decode($request->p['filter'], true);
		$filter = $filter_meta['filter'] ?: array();
		$disp_filter = ReportExport::generateFilterDescription($filter, $questions);

		$report = lower($request->p['report']);
		$report_name = $CustomSurvey->get('name') . (stripos($CustomSurvey->get('name'), 'survey') === false? " Survey" : "");

		if ($request->p['type'] == 'csv')
		{
			switch ($report)
			{
				case 'gs':
					$report_type_name = "Graph Survey Report";
					$CSVReport = new CsvApExport($sections, $group_definition, $questions, $sample_corrections);
					$csv_out = $CSVReport->export($sets, $disp_filter);
				break;

				case 'gs-raw':
					$report_type_name = "Raw Graph Survey Export";
					$report = AudienceProfile::findBySurvey($CustomSurvey, $this->User);
					$report->load();
					$CSVReport = new CsvRawGsExport($sections, $group_definition, $questions);

					$report_contents = json_decode($report->get('contents'), true);

					// $CSVReport->setDebug(true);
					$csv_out = $CSVReport->export($report_contents);
				break;

				default:
					Session::setFlash("Invalid report type requested.", "flash_error");
					$request->redirect('/dashboard/reports');
					exit;
				break;
			}

			header("Content-type: text/csv");
			header("Content-length:" . strlen($csv_out));
			header("Content-disposition: attachment; "
				. escape_for_content_disposition("{$report_name} - {$report_type_name} - " . date("Y-m-d.H.i") . ".csv"));

			echo $csv_out;
			exit;
		}

		else if($request->p['type'] == 'excel'){

			$report_type_name = "Graph Survey Report";
			$ExcelReport = new ExcelApExport($sections, $group_definition, $questions, $sample_corrections);
			$excel_out = $ExcelReport->export($sets, $disp_filter);
			header('Content-Type: application/excel');
			header("Content-Disposition: attachment; "
				. escape_for_content_disposition("{$report_name} - {$report_type_name} - " . date("Y-m-d.H.i") . ".xlsx"));
			header('Cache-Control: max-age=0');
			$objWriter = PHPExcel_IOFactory::createWriter($excel_out, 'Excel5');
			$objWriter->save('php://output');
			var_dump($excel_out);
			exit;
		}

		else
		{
			$template = 'export/pdf/aud_an.tpl.php';
			$css_file = 'less/audience_profile_print.less';
			$pdfOptions = array(
				'reportTitle' => $report_name . " Report",
				'pageHeaderLeft' => "Graph Survey Report",
				'pageHeaderRight' => $report_name
			);

			$PDFReport = new PDFReportExport($sections, $group_definition, $questions, $sample_corrections);

			$Gordon = new \LoudDoor\Lib\Gordon\Gordon;
			$lessc = new lessc;

			$PDFReport->setLessFile($css_file);
			$PDFReport->setTemplateFile($template);
			$PDFReport->setLessCompiler($lessc);

			$PDFReport->set('Account', $this->Account);
			$PDFReport->set('filter_meta', $filter_meta);

			$html_content = $PDFReport->export($sets, $disp_filter);

			if (1)
			{
				$pdfContents = $PDFReport->generatePDF($Gordon, $html_content, $pdfOptions);

				header("Content-type: application/pdf");
				header("Content-length:" . strlen($pdfContents));
				header("Content-disposition: attachment; "
					. escape_for_content_disposition("{$pdfOptions['reportTitle']} - " . date('Y-m-d.H.i') . ".pdf"));

				echo $pdfContents;
				exit;
			}
			else
			{
				echo $html_content;
				exit;
			}
		}
	}

	public function ajax_filters($request, $survey_id)
	{
		$CustomSurvey = CustomSurvey::findSurvey($survey_id, $this->User->get('id'));

		if (false == $CustomSurvey)
		{
			Session::setFlash("You do not have access to this survey.", "flash_error");
			$request->redirect('/graph-surveys');
		}

		$question_map = $CustomSurvey->getQuestionsBySection( $this->UserLogin->get('locale') );

		$output = json_encode($question_map);
		$content_length = strlen($output);

		header("X-Content-Length: {$content_length}");
		header("Content-type: text/javascript");

		echo $output;
		exit;
	}

	public function ajax_panels($request, $survey_id)
	{
		$out = '';

		$CustomSurvey = CustomSurvey::findSurvey($survey_id, $this->User->get('id'));

		$panels = array(array(
			'type' => 'default',
			'name' => 'Survey Takers',
			'pic_square' => '',
			'id' => 'report',
			'filter' => array()
		));

		if ($survey_id == 1617 || $survey_id == 1723)
		{
			$panels = array();
		}

		if ($CustomSurvey->get('flag_dep_as_personas') == 1)
		{
			$deployments = $CustomSurvey->get("deployments");

			foreach ($deployments as $deployment)
			{
				$panels []= array(
					'type' => 'default',
					'name' => $deployment->get('name'),
					'hidden' => false,
					'pic_square' => '',
					'id' => 'csd.' . $deployment->get('id'),
					'filter' => $deployment->convertToProspectorTargeting(),
				);
			}
		}

		$panels = array('custom' => array(), 'default' => $panels);

		$out = json_encode($panels);
		$clen = strlen($out);

		header("X-Content-Length: {$clen}");
		header("Content-type: text/javascript");

		echo $out;
		exit;
	}

	public function ajax_load_profile($request, $survey_id, $type = "survey")
	{
		if (isset($request->g['head']))
		{
			$load_full_report = false;
		}
		else
		{
			$load_full_report = true;
		}

		$compress = stripos($request->server['HTTP_ACCEPT_ENCODING'], 'gzip') >= 0;

		$CustomSurvey = CustomSurvey::findSurvey($survey_id, $this->User->get('id'));

		if (false == $CustomSurvey)
		{
			Session::setFlash("You do not have access to this survey.", "flash_error");
			$request->redirect('/graph-surveys');
		}

		$report = $CustomSurvey->loadReport();

		if ($report->isEmpty())
		{
			header("Content-type: application/json");
			if ($load_full_report === false)
			{
				$payload = array(
					'success' => false,
					'message' => $report->getMessage(),
					'code' => $report->getCode(),
				);

				echo json_encode($payload);
			}
			else
			{
				echo json_encode(array());
			}
			exit;
		}

		$report->headers($compress, false);
		if (!empty($report['headers']))
		{
			$header = $report->get('headers')->header;
		}

		if ($load_full_report === false)
		{
			$payload = array(
				'success' => true,
				'id' => $report['id'],
				'response' => array(
					'type' => 'report',
					'id' => $CustomSurvey->get('id'),
					'length' => (int)$header['content-length'],
					'encoding' => isset($header['content-encoding'])? $header['content-encoding'] : false,
				),
			);

			header("Content-Type: application/json");
			echo json_encode($payload);
			exit;
		}
		else
		{
			header("Content-Type: application/json");
			if (isset($header['content-encoding']))
			{
				header("Content-Encoding: {$header['content-encoding']}");
			}
			ob_end_clean();
			$report->stream($compress);
			exit;
		}
	}

	public function print_profile($request, $survey_id)
	{
		$request->set_layout('default');

		$industry = $this->CurrentIndustryGroup;
		$schema = $industry->getReportSchema(false, $this->UserLogin->get('locale'));
		$options = $industry->getReportOptions($this->UserLogin->get('locale'));

		$group_definition = array();
		$group_definition = array_merge($group_definition, $schema);

		$sections = array(
			'nps' => "Likelihood to Recommend Score",
			'demographics' => "Demographics",
			'purchase_behavior' => "Purchase Behavior",
			'decision_influencers' => "Decision Influencers",
		);

		$sample_corrections = array(
			'none' => "None (raw)",
			'insights' => "Facebook Insights",
			'census' => "Facebook Census",
			'ld' => "LoudDoor Graph",
		);

		$request->set('sample_correction', $sample_corrections[$request->post['sample_correction']]);

		$CustomSurvey = CustomSurvey::findSurvey($survey_id, $this->User->get('id'));
		$CustomSurvey->loadQuestions(false);

		$sections['custom'] = $CustomSurvey->get('name');

		$request->set('CustomSurvey', $CustomSurvey);

		$this->SurveyQuestion = new SurveyQuestion;
		$questions = $this->SurveyQuestion->loadFanSatisfactionQuestions($CustomSurvey->get('questions')->pluck('id'), $this->UserLogin->get('locale'));

		$request->set('section_names', $sections);
		$request->set('questions', $questions);

		$group_definition['custom'] = $CustomSurvey->get('questions')->pluck('id');
		if (!is_array($group_definition['custom']))
		{
			$group_definition['custom'] = array();
		}

		$request->set('group_definition', $group_definition);

		$filter = json_decode($request->post['filter'], true);
		$profile = json_decode($request->post['profile'], true);

		$request->set('profile', $profile);

		$disp_filter = array();
		foreach ($filter as $qid => $question)
		{
			@list($question_id, $subquestion_id) = explode(".", $qid);

			if ($subquestion_id == NULL)
			{
				$disp_filter[$qid] = array(
					'name' => $questions[$question_id]['name'],
					'type' => $question['type'],
					'responses' => array()
				);

				foreach ($question['responses'] as $response => $val)
				{
					if ($val)
					{
						$disp_filter[$question_id]['responses'] []= $questions[$question_id]['responses'][$response]['description'];
					}
				}
			}
			else
			{
				$disp_filter[$qid] = array(
					'name' => $questions[$question_id]['name'] . ' - ' . $questions[$question_id]['subquestions'][$subquestion_id]['description'],
					'type' => $question['type'],
					'responses' => array(),
				);

				foreach ($question['responses'] as $response => $val)
				{
					if ($val)
					{
						$disp_filter[$qid]['responses'] []= $questions[$question_id]['responses'][$response]['description'];
					}
				}
			}
		}

		$request->set('filter', $disp_filter);

		$page = Session::get('user.current_report_page');
		$request->set('page', $page);

		$less = new lessc;
		$request->set('css', $less->compileFile(DOC_ROOT . '/less/audience_profile_print.less'));

		ob_start();
		extract($request->data);
		require APP_ROOT . '/views/gs_analytics/print_profile.tpl.php';

		$contents = ob_get_clean();
		ob_end_clean();

		if (false)
		{
			echo $contents;
			exit;
		}

		$gordon = new \LoudDoor\Lib\Gordon\Gordon;

		$result = $gordon->post('/pdf/create', array(
			'html' => $contents,
			'globalOptions' => json_encode(array(
				'image-quality' => 100,
				'title' => hsc($CustomSurvey['name']) ." : Graph Survey",
				'page-size' => 'Letter',
			)),
			'pageOptions' => json_encode(array(
				'header-font-size' => 8,
				'header-font-name' => 'HelveticaNeue',
				'header-left' => "Graph Survey",
				'header-right' => hsc($CustomSurvey['name']),
				'header-spacing' => 5,

				'footer-font-size' => 8,
				'footer-font-name' => 'HelveticaNeue',
				'footer-left' => "[section]",
				'footer-center' => "Page [page] of [topage]",
				'footer-right' => "Generated ".date("F j, Y \a\\t g:i A T"),
			))
		));

		header("Content-type: application/pdf");
		header("Content-disposition: attachment; ".escape_for_content_disposition("Graph Survey - {$CustomSurvey['name']}.pdf"));
		echo $result;
		exit;
	}

	public function profile_report($request, $type = ".csv")
	{
		$sets = json_decode($request->g['sets'], true);
		$profile = array();

		// temp fix
		if (is_array($sets))
		{
			$set = $sets[0];
		}

		try
		{
			$ap = AudienceProfile::findBySurveyID($set['id']);
			if ($ap)
			{
				$CustomSurvey = CustomSurvey::findSurvey($set['id'], $this->User->get('id'));
				$CustomSurvey->loadQuestions(false);

				$profile['page'] = array(
					'name' => $CustomSurvey->get('name'),
					'data' => $ap->get('contents')
				);

				$schema = $this->CurrentIndustryGroup->getReportSchema();
				$options = $this->CurrentIndustryGroup->getReportOptions();

				// Set up the report schema
				$sections = array(
					'nps' => array(
						'name' => "Likelihood to Recommend Score",
						'questions' => array(11280),
					),
					'demographics' => array(
						'name' => "Demographics",
						'questions' => $schema['demographics'],
					),
					'purchase_behavior' => array(
						'name' => "Purchase Behavior",
						'questions' => $schema['purchase_behavior'],
					),
					'decision_influencers' => array(
						'name' => "Decision Influencers",
						'questions' => $schema['decision_influencers']
					),
				);

				$sections['custom'] = array(
					'name' => $CustomSurvey->get('name'),
					'questions' => $CustomSurvey->get('questions')->pluck('id'),
				);

				// Load the question text
				$this->SurveyQuestion = new SurveyQuestion;
				$questions = $this->SurveyQuestion->loadFanSatisfactionQuestions($CustomSurvey->get('questions')->pluck('id'));

				$filename = "Graph Survey Results - {$CustomSurvey['name']} - ".date("Y-m-d His").$type;

				// Instanciate, prepare the report
				$Report = new AudienceProfileReport;
				$result = $Report->generate("text/csv", $profile, $sections, $questions, $filename);

				exit;
			}
		}
		catch (Exception $e)
		{
			header("HTTP/1.1 400 Bad Request", true, 400);
			return $request;
		}
	}
}