Thursday, July 12, 2007

Easy way to create XLS Excel file from PHP

Everybody knows phpMyAdmin can export file to Excel format but phpMyAdmin just export .csv file,not real Excel file format. If you are interest in PHP programming and need to export to the real Excel format please check it out !

Example PHP export to XLS file format.

1. Create Function for XLS

function xlsBOF() {
echo
pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
return;
}

function
xlsEOF() {
echo
pack("ss", 0x0A, 0x00);
return;
}

function
xlsWriteNumber($Row, $Col, $Value) {
echo
pack("sssss", 0x203, 14, $Row, $Col, 0x0);
echo
pack("d", $Value);
return;
}

function
xlsWriteLabel($Row, $Col, $Value ) {
$L = strlen($Value);
echo
pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
echo
$Value;
return;
}



2. Send Header to Browser in download format.

// Query Database
$result=mysql_db_query($dbname,"select id,prename,name,sname,grade from appdata where course='$courseid' and sec='$section'")

// Send Header
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=$courseid-$sec.xls ");
header("Content-Transfer-Encoding: binary ");

// XLS Data Cell

xlsBOF();
xlsWriteLabel(1,0,"Student Register $semester/$year");
xlsWriteLabel(2,0,"COURSENO : ");
xlsWriteLabel(2,1,"$courseid");
xlsWriteLabel(3,0,"TITLE : ");
xlsWriteLabel(3,1,"$title");
xlsWriteLabel(4,0,"SETION : ");
xlsWriteLabel(4,1,"$sec");
xlsWriteLabel(6,0,"NO");
xlsWriteLabel(6,1,"ID");
xlsWriteLabel(6,2,"Gender");
xlsWriteLabel(6,3,"Name");
xlsWriteLabel(6,4,"Lastname");
$xlsRow = 7;
while(list(
$id,$prename,$name,$sname,$grade)=mysql_fetch_row($result)) {
++
$i;
xlsWriteNumber($xlsRow,0,"$i");
xlsWriteNumber($xlsRow,1,"$id");
xlsWriteLabel($xlsRow,2,"$prename");
xlsWriteLabel($xlsRow,3,"$name");
xlsWriteLabel($xlsRow,4,"$sname");
$xlsRow++;
}
xlsEOF();
exit();

source from www.appservnetwork.com

8 comments:

piya said...

ไม่ทราบว่าจะตั้งชื่อ Function ในการ สร้างไฟล์ XLS ว่าอย่างไร ได้โปรด

superkeng said...

ถ้าจะสร้างเป็นฟังก์ชั่น ก็ตั้งชื่อฟังก์ชั่นว่าอะไรก็ได้ครับ เพียงแค่ส่งค่าเข้ามามันก็จะใช้ได้แล้ว

แต่ในตัวอย่างเป็นการเรียกฟังก์ชั่น excel ด้านบนมาใช้เลยนะครับ แค่ก้อปมาไว้ให้อยู่ในหน้าเดียวกันเท่านั้นเอง

marker said...

You might want to try this article from zedwood.com which has some sample code on how to generate a multi-worksheeted xls spreadsheet with formatting. It uses the PHP Pear package, but you can just download the code and run it. Its great.

Your code generates spreadsheets in excel 2.0 format. The zedwood code generates xls in excel 95 format which allows you to do all kinds of formatting etc.

J&D said...

情趣用品情趣用品情趣


視訊聊天室辣妹視訊視訊美女視訊視訊交友網視訊聊天室視訊交友視訊美女免費視訊免費視訊聊天視訊交友90739免費視訊聊天室成人聊天室

視訊聊天


A片下載成人影片下載免費A片下載日本A片情色A片免費A片成人影城成人電影線上A片A片免費看


煙火影音視訊聊天室情趣用品情趣用品情趣用品

pandorasell said...

Silver pandora jewelry store comprises replacement atomic number 79 equally the metal by choice in that pandora jewelry website flavors manners. .925 alright superlative Ag Jewelry checks 92.5% complete silver pandora jewelry online and comprises assorted with extra debases to beef up them and abbreviate maculating pandora style jewelry coming through a hard-nosed alloy as pandora bracelet beads . The rise in metal prices, especially gold, have begun to alter pandora leather bracelet dashes. Designers are applying sterling pandora style bracelet a lot of today because of its gold pandora bracelet appeal and affordability. Designers care Jacques Louis pandora charm necklace David Yurman and those from pandora’s cause changed their focus and are making A-list assembles by .925 Fine Sterling pandora necklace charms Although Silver is affordable its price has quietly risen 300% inch the last 5 years and is astir across 50% pandora silver beads these class entirely. Many investment advisers* recommend a portion of your wealth be inward Ag and amber. Trend setters who comprised the first to begin wearing pandora necklace leather metal to match tatto last word and additional electric current styles as well do good along accepting pandora bracelet charms worth more than they paid for it.

h4ns said...

Thank you for another essential article. Where else could anyone get that kind of information in such a complete way of writing? I have a presentation incoming week, and I am on the lookout for such information.
UEFA Live Stream | Live Stream Today | Live Streaming Free TV Channels Online | Technology News | EPL Live Stream

Pritpal said...

I like the blog, thanks for giving the wonderful information.PHP Development

Dagdgsd Dffbd said...

The article posted was very informative and useful
thanks for sharing..
jaring futsal | jaring golf | jaring kassa / jaring polynet | jaring pengaman proyek | jaring pengaman bangunan | jaring pengaman gedung | jaring gawang | jaring paranet / jaring tanaman | rumput sintetis / rumput futsal |
tangga darurat | jaring cargo | agen jaring | jaring outbound | jaring truk | tali tambang
http://jaringfutsal.wordpress.com
http://jualjaringfutsal.wordpress.com
http://tokojaring.wordpress.com
http://jualtambangmurah.wordpress.com
http://pasangjaringfutsal.wordpress.com
http://pancasamudera.wordpress.com