[eluser]Brad K Morse[/eluser]
I converted this to to_excel plugin to a helper: http://codeigniter.com/wiki/Excel_Plugin/
helper:
Code: <?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
if ( ! function_exists('to_excel')) {
function to_excel($query, $filename='exceloutput') {
$headers = ''; // just creating the var for field headers to append to below
$data = ''; // just creating the var for field data to append to below
$objt& get_instance();
$fields = $query->field_data();
if ($query->num_rows() == 0) {
echo '<p>The table appears to have no data.</p>';
} else {
foreach ($fields as $field) {
$headers .= $field->name . "\t";
}
foreach ($query->result() as $row) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=$filename.xls");
echo "$headers\n$data";
}
}
}
?>
report controller:
Code: $this->load->helper('to_excel');
$sql = $this->report_model->getEntriesByDepartmentQuery($id);
to_excel($sql, "export_filename");
A PHP Error was encountered
Severity: Notice
Message: Undefined variable: objt
Filename: helpers/to_excel_helper.php
Line Number: 8
A PHP Error was encountered
Severity: Notice
Message: Object of class Report could not be converted to int
Filename: helpers/to_excel_helper.php
Line Number: 8
Line 8 is: Code: $objt& get_instance();
model function getEntriesByDepartmentQuery:
Code: function getEntriesByDepartmentQuery($id) {
$q = '
SELECT
s.id, s.description, s.improvement, s.previous_year_id, s.current_year_id,
s.first_name, s.last_name, s.username, s.finding, s.action, s.share,
s.learned, s.timestamp, d.title as department_title,
group_concat(DISTINCT g.title SEPARATOR " | ") as strategic_goals,
group_concat(DISTINCT m.statement SEPARATOR " | ") as mission_references,
group_concat(DISTINCT meas.statement SEPARATOR " | ") as measure_statement,
group_concat(DISTINCT o.statement SEPARATOR " | ") as outcome_statement,
group_concat(DISTINCT i.title SEPARATOR " | ") as ilo_title,
group_concat(DISTINCT cv.title SEPARATOR " | ") as core_value_title,
y1.year as current_year_title, y2.year as previous_year_title,
u.summary_id, u.file_name as file_name
FROM summary s
INNER JOIN year y1 ON s.current_year_id = y1.id
INNER JOIN year y2 ON s.previous_year_id = y2.id
INNER JOIN strategic_goal_entries sge ON s.id = sge.summary_id
INNER JOIN goal g ON sge.goal_id = g.id
INNER JOIN outcome o ON s.id = o.summary_id
INNER JOIN measure meas ON o.id = meas.outcome_id
INNER JOIN department d ON s.department_id = d.id
LEFT JOIN uploads u ON s.id = u.summary_id
INNER JOIN mission_entries me ON s.id = me.summary_id
INNER JOIN mission m ON me.mission_id = m.id
INNER JOIN ilo_entries ie ON s.id = ie.summary_id
INNER JOIN ilo i ON ie.ilo_id = i.id
INNER JOIN core_value_entries cve ON s.id = cve.summary_id
INNER JOIN core_value cv ON cve.core_value_id = cv.id
WHERE s.department_id = '.$id.'
GROUP BY s.id
';
return $q;
}
Any help is appreciated.
[eluser]danmontgomery[/eluser]
should be
Code: $objt =& get_instance();
Although you're not using that variable anywhere, so you can just delete that line.
[eluser]Brad K Morse[/eluser]
Thanks!
That cleared up that error, now checking my error log and see this error:
PHP Fatal error: Call to a member function field_data() on a non-object in /web/internal-applications/assessment/application/helpers/to_excel_helper.php on line 10
Line 10: Code: $fields = $query->field_data();
[eluser]zac[/eluser]
That error means that $query is not an object. Your to_excel() helper function is fine, but when you call it, it's expecting you to pass it a DB query object as its first parameter.
[eluser]danmontgomery[/eluser]
to_excel() is expecting the query to have already been run. From the model,
Code: function getEntriesByDepartmentQuery($id) {
$this->load->database();
$q = '...';
return $this->db->query($q);
}
[eluser]Brad K Morse[/eluser]
database library is set in autoload
model:
Code: function getEntriesByDepartmentQuery($id) {
$this->load->database();
$q = '
SELECT
s.id, s.description, s.improvement, s.previous_year_id, s.current_year_id,
s.first_name, s.last_name, s.username, s.finding, s.action, s.share,
s.learned, s.timestamp, d.title as department_title,
group_concat(DISTINCT g.title SEPARATOR " | ") as strategic_goals,
group_concat(DISTINCT m.statement SEPARATOR " | ") as mission_references,
group_concat(DISTINCT meas.statement SEPARATOR " | ") as measure_statement,
group_concat(DISTINCT o.statement SEPARATOR " | ") as outcome_statement,
group_concat(DISTINCT i.title SEPARATOR " | ") as ilo_title,
group_concat(DISTINCT cv.title SEPARATOR " | ") as core_value_title,
y1.year as current_year_title, y2.year as previous_year_title,
u.summary_id, u.file_name as file_name
FROM summary s
INNER JOIN year y1 ON s.current_year_id = y1.id
INNER JOIN year y2 ON s.previous_year_id = y2.id
INNER JOIN strategic_goal_entries sge ON s.id = sge.summary_id
INNER JOIN goal g ON sge.goal_id = g.id
INNER JOIN outcome o ON s.id = o.summary_id
INNER JOIN measure meas ON o.id = meas.outcome_id
INNER JOIN department d ON s.department_id = d.id
LEFT JOIN uploads u ON s.id = u.summary_id
INNER JOIN mission_entries me ON s.id = me.summary_id
INNER JOIN mission m ON me.mission_id = m.id
INNER JOIN ilo_entries ie ON s.id = ie.summary_id
INNER JOIN ilo i ON ie.ilo_id = i.id
INNER JOIN core_value_entries cve ON s.id = cve.summary_id
INNER JOIN core_value cv ON cve.core_value_id = cv.id
WHERE s.department_id = '.$id.'
GROUP BY s.id
';
return $this->db->query($q);
}
controller: Code: to_excel($this->report_model->getEntriesByDepartment($id));
still receiving error: PHP Fatal error: Call to a member function field_data() on a non-object in /web/internal-applications/assessment/application/helpers/to_excel_helper.php on line 10
Line 10: Code: $fields = $query->field_data();
Did a print_r($query); within the to_excel helper and it printed:
Array ( [0] => stdClass Object ( [id] => 1 [description] => Installing new computer lab in student housing building A [improvement] => Students in building A, average increased by 5% [previous_year_id] => 1 [current_year_id] => 2 [first_name] => Brad [last_name] => Morse [username] => morsebk [finding] => Students spend time in computer lab [action] => Create proposal to include labs in other buildings. [share] => Finance and FSA committees [learned] => Budget more for more lab proctors. Installing security cameras as well. [timestamp] => 2011-04-15 13:42:41 [department_title] => Advisement Center [strategic_goals] => Campus Life | Institutional Culture [mission_references] => Serve diverse learners by providing high quality, accessible educational opportunities. | Serve diverse learners by providing high quality, accessible services [measure_statement] => Banner reports of student grades | Advisement degree-works reports [outcome_statement] => Student grades increased by 5% [ilo_title] => Communication | Knowledge Management [core_value_title] => Community | Integrity [current_year_title] => 2011-2012 [previous_year_title] => 2010-2011 [summary_id] => [file_name] => ) )
[eluser]Brad K Morse[/eluser]
Got it working. Thank you noctrum and Zac.
[eluser]carvingCode[/eluser]
Could you post corrected code?
TIA
[eluser]Brad K Morse[/eluser]
Not sure, but here is an xls file I generated from to_excel helper: http://cl.ly/6A7m - hope that helps.
[eluser]Brad K Morse[/eluser]
Corrected code, for application/helper/to_excel_helper.php
Code: <?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
if ( ! function_exists('to_excel')) {
function to_excel($query, $filename='exceloutput') {
$headers = ''; // just creating the var for field headers to append to below
$data = ''; // just creating the var for field data to append to below
$objt =& get_instance();
$fields = $query->field_data();
if ($query->num_rows() == 0) {
echo "<p>No data for that report, please use your browser's back button</p>";
return FALSE;
} else {
foreach ($fields as $field) {
$headers .= $field->name . "\t";
}
foreach ($query->result() as $row) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace("\n", " ", $value); // for stupid line breaks that mess up the spreadsheet
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r","",$data);
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=$filename.xls");
echo "$headers\n$data";
}
}
}
?>
|
