Creating excel file for download

Posted by mkhairul - June 15, 2008 at 03:06:33 pm -

Categories: blog



There is a problem with CI's excel plugin. It generates a tab delimited file and download it as an excel file. But when I open it up using Excel, it says the file is in a different format. So it is NOT an excel file.

The problem of it not being an Excel file is that, the file that is to be downloaded is supposed to be fed on to another application (written in ASP.NET) and the application just outright refuses it. So, a colleague suggest to me that he will copy the contents into an excel file and uploaded it, which defeats the purpose of a system.

I found a way to create an excel file from Appserv Open Project.

PLAIN TEXT
PHP:
  1. function xlsBOF() {
  2.     echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0); 
  3.     return;
  4. }
  5.  
  6. function xlsEOF() {
  7.     echo pack("ss", 0x0A, 0x00);
  8.     return;
  9. }
  10.  
  11. function xlsWriteNumber($Row, $Col, $Value) {
  12.     echo pack("sssss", 0x203, 14, $Row, $Col, 0x0);
  13.     echo pack("d", $Value);
  14.     return;
  15. }
  16.  
  17. function xlsWriteLabel($Row, $Col, $Value ) {
  18.     $L = strlen($Value);
  19.     echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
  20.     echo $Value;
  21. return;
  22. }

And send the header to the browser, for it to be download.

PLAIN TEXT
PHP:
  1. // Query Database
  2.     $result=mysql_db_query($dbname,"select id,prename,name,sname,grade from appdata where course='$courseid' and sec='$section'")
  3.  
  4.     // Send Header
  5.     header("Pragma: public");
  6.     header("Expires: 0");
  7.     header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
  8.     header("Content-Type: application/force-download");
  9.     header("Content-Type: application/octet-stream");
  10.     header("Content-Type: application/download");;
  11.     header("Content-Disposition: attachment;filename=$courseid-$sec.xls ");
  12.     header("Content-Transfer-Encoding: binary ");
  13.  
  14.     // XLS Data Cell
  15.  
  16.     xlsBOF();
  17.     xlsWriteLabel(1,0,"Student Register $semester/$year");
  18.     xlsWriteLabel(2,0,"COURSENO : ");
  19.     xlsWriteLabel(2,1,"$courseid");
  20.     xlsWriteLabel(3,0,"TITLE : ");
  21.     xlsWriteLabel(3,1,"$title");
  22.     xlsWriteLabel(4,0,"SETION : ");
  23.     xlsWriteLabel(4,1,"$sec");
  24.     xlsWriteLabel(6,0,"NO");
  25.     xlsWriteLabel(6,1,"ID");
  26.     xlsWriteLabel(6,2,"Gender");
  27.     xlsWriteLabel(6,3,"Name");
  28.     xlsWriteLabel(6,4,"Lastname");
  29.     $xlsRow = 7;
  30.     while(list($id,$prename,$name,$sname,$grade)=mysql_fetch_row($result)) {
  31.       ++$i;
  32.       xlsWriteNumber($xlsRow,0,"$i");
  33.       xlsWriteNumber($xlsRow,1,"$id");
  34.       xlsWriteLabel($xlsRow,2,"$prename");
  35.       xlsWriteLabel($xlsRow,3,"$name");
  36.       xlsWriteLabel($xlsRow,4,"$sname");
  37.       $xlsRow++;
  38.     }
  39.     xlsEOF();
  40.     exit();

Works like a charm.

No Comments yet »

RSS feed for comments on this post. TrackBack URI

Leave a comment

XHTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Powered by WordPress with GimpStyle Theme design by Horacio Bella.
Entries and comments feeds. Valid XHTML and CSS.