extracting grades to Excel

Topics: User Forum
Nov 6, 2009 at 8:05 PM

Greetings,

I have one course with two "Team Learning" sessions where about 12 test students took 3 exams, (IRAT, GRAT, and AppEx).

I am trying to pull this info and write it to Excel.

The dump of my main session_grade table looks like this.

tl_session_grades` (
  `Session_ID` int(11) NOT NULL default '0',
  `SOMS_KEY` int(11) NOT NULL default '0',
  `UID` varchar(9) default NULL,
  `Group_ID` int(11) NOT NULL default '0',
  `IRAT_Raw` decimal(3,1) NOT NULL,
  `GRAT_Raw` decimal(3,1) NOT NULL,
  `AppEx_Raw` decimal(4,2) NOT NULL,
  `IRAT_Grade` decimal(5,2) default NULL,
  `GRAT_Grade` decimal(5,2) default NULL,
  `AppEx_Grade` decimal(5,2) default NULL,
  `TL_Avg` decimal(5,2) default NULL,
  `Academic_Year` varchar(5) NOT NULL default '',
  KEY `SOMS_KEY` (`SOMS_KEY`),
  KEY `UID` (`UID`),
  KEY `Group_ID` (`Group_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


I want to pull all the results for a single course for whatever year appears in the drop down listbox on the form.

$Course_Number = ( isset($_GET['Course_Number']) ) ? ($_GET['Course_Number']) : ( ( isset($_POST['Course_Number']) ) ? ($_POST['Course_Number']) : '') ;
$query_Year = ( isset($_GET['query_Year']) ) ? ($_GET['query_Year']) : ( ( isset($_POST['query_Year']) ) ? ($_POST['query_Year']) : '') ;


For now I'm just trying to pull the names and get those written to the Excel spreadsheet.

$result = $db->sql_query("SELECT b.Name_Last, b.Name_First FROM ".$prefix."_tl_session_grades a
JOIN ".$prefix."_tl_session s ON (a.Session_ID = s.Session_ID)
JOIN ".$prefix."_tl_students b ON (a.SOMS_KEY = b.SOMS_KEY)
JOIN ".$prefix."_tl_group_students c ON (a.SOMS_KEY = c.SOMS_KEY)
JOIN ".$prefix."_tl_groups g ON (c.Group_ID = g.Group_ID)
WHERE s.Course_Number = '$Course_Number'
AND s.Academic_Year = '$query_Year'
ORDER BY b.Name_Last ASC");

while ($row = $db->sql_fetchrow($result)) {
$column = 'C';
foreach($row as $columnData) {
$cellRef = $column.$row;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellRef, $columnData);
$column++;
}
$row++;
}

$objPHPExcel->getActiveSheet()->setTitle('Simple');
$objPHPExcel->setActiveSheetIndex(0);

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="test-'.date('dMY').'.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');


All I get is garbage data.

<!-- table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} .font0 {color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0;} .font5 {color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:"小塚ゴシック Pro H"; mso-generic-font-family:auto; mso-font-charset:128;} .font6 {color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:"Marker Felt"; mso-generic-font-family:auto; mso-font-charset:0;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignore:padding; color:windowtext; font-size:10.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} ruby {ruby-align:left;} rt {color:windowtext; font-size:8.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Verdana; mso-generic-font-family:auto; mso-font-charset:0; mso-char-type:none; display:none;} -->

<!--StartFragment--> <!--EndFragment-->
ÃB=º%r8X"1êCalibri‡ıˇ ¿


This is on a Mac OXS server running 10.5.4, running PHP 5.2.5 '--with-xml'

Ultimately I'd like to get one medical student record with all sessions grades in a single line.

Example

Student - Scott Smith
Course_Name - Molecular Basis of Medicine
Course Number - 571
Session Name - Sickle Cell Anemia
IRAT_Raw -
GRAT_Raw -
Appex_Raw -
Irat_Grade -
Grat_Grade -
AppEx_Grade -

Session Name - Hyperuriciemia
IRAT_Raw -
etc
etc

If anybody can get me on the right track I'd really appreciate it. Been searching the forums and can't find any examples.

 

Developer
Nov 6, 2009 at 10:17 PM

>> All I get is garbage data.

It is wise to first try to save Excel file on disk rather than sending output to browser.

Try to change this

 

header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="test-'.date('dMY').'.xls"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');


to this

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('test-'.date('dMY').'.xls');

 

When you do that, how does the Excel file look when you open it? Still garbage data?

Nov 10, 2009 at 2:56 PM

It wont open.

 

Warning: fopen(test-10Nov2009.xls) [function.fopen]: failed to open stream: Permission denied in /Library/WebServer/Documents/xxxx/modules/Team_Learning/admin/PHPExcel/Shared/OLE/OLE_Root.php on line 109

Fatal error: Uncaught exception 'Exception' with message 'Can't open test-10Nov2009.xls. It may be in use or protected.' in /Library/WebServer/Documents/xxxx/modules/Team_Learning/admin/PHPExcel/Shared/OLE/OLE_Root.php:111 Stack trace: #0 /Library/WebServer/Documents/xxxx/modules/Team_Learning/admin/PHPExcel/Writer/Excel5.php(224): PHPExcel_Shared_OLE_PPS_Root->save('test-10Nov2009....') #1 /Library/WebServer/Documents/xxxx/modules/Team_Learning/admin/TLViewSpreadsheet.php(131): PHPExcel_Writer_Excel5->save('test-10Nov2009....') #2 /Library/WebServer/Documents/xxxx/modules/Team_Learning/admin/index.php(141): include('/Library/WebSer...') #3 /Library/WebServer/Documents/xxxx/modules/Team_Learning/admin/case.php(138): include('/Library/WebSer...') #4 /Library/WebServer/Documents/atlas/admin.php(491): include_once('/Library/WebSer...') #5 {main} thrown in /Library/WebServer/Documents/xxxx/modules/Team_Learning/admin/PHPExcel/Shared/OLE/OLE_Root.php on line 111

What privileges should my db user need to run PHPExcel? I have SELECT, INSERT, UPDATE, DELETE, FILE

Does PHPExcel try to create a temp directory OLE_PPS_Root on my Mac server? Could this be a permission issue at the server level?

Developer
Nov 11, 2009 at 1:17 AM

Make sure that the server has write permission for the directory where you are saving test-10Nov2009.xls

Try using chmod, I think this is the problem.

http://phpexcel.codeplex.com/Thread/View.aspx?ThreadId=47209

The database user privileges has nothing to do with PHPExcel. There cannot be any problem there.

PHPExcel sometimes makes use of the system temp directory so if it is not set up correctly there will be problems, but it doesn't try to create new temp directories.

Dec 16, 2014 at 10:00 AM
How can I give permission to "save ()" to keep the server?
$objWriter = PHPExcel_IOFactory::createWriter($entitat, 'Excel2007'); 
$objWriter->save($rutaFinal.$data.".xls");
He gives me 644, and I can change and give you 755?