Première version publique
[auf_paf.git] / php_excel.php
CommitLineData
98ff4c8e 1<?php\r
2\r
3/**\r
4 * @author sapta\r
5 * @copyright 2009\r
6 */\r
7\r
8$cdate = date("Y-m-d"); // get current date\r
9\r
10// configuration\r
11$mysql_host = 'localhost';\r
12$mysql_user = 'root';\r
13$mysql_pass = 'root';\r
14$mysql_db = 'pafauf_bdd';\r
15$query_string = "select * from v_excel";\r
16$export_filename = "exported_tableau_on_$cdate.xls";\r
17\r
18// connect to mysql server\r
19$mysql_link = mysql_connect($mysql_host,$mysql_user,$mysql_pass);\r
20mysql_select_db($mysql_db,$mysql_link);\r
21\r
22// query from table\r
23$result = mysql_query($query_string);\r
24\r
25$count = mysql_num_fields($result);\r
26\r
27// fetch table header\r
28$header = '';\r
29for ($i = 0; $i < $count; $i++){\r
30 $header .= mysql_field_name($result, $i)."\t";\r
31}\r
32\r
33// fetch data each row, store on tabular row data\r
34while($row = mysql_fetch_row($result)){\r
35 $line = '';\r
36 foreach($row as $value){\r
37 if(!isset($value) || $value == ""){\r
38 $value = "\t";\r
39 }else{\r
40 # important to escape any quotes to preserve them in the data.\r
41 $value = str_replace('"', '""', $value);\r
42 # needed to encapsulate data in quotes because some data might be multi line.\r
43 # the good news is that numbers remain numbers in Excel even though quoted.\r
44 $value = '"' . $value . '"' . "\t";\r
45 }\r
46\r
47 $line .= $value;\r
48 }\r
49 $data .= trim($line)."\n";\r
50}\r
51\r
52# this line is needed because returns embedded in the data have "\r"\r
53# and this looks like a "box character" in Excel\r
54$data = str_replace("\r", "", $data);\r
55\r
56# Nice to let someone know that the search came up empty.\r
57# Otherwise only the column name headers will be output to Excel.\r
58if ($data == "") {\r
59 $data = "\nno matching records found\n";\r
60}\r
61\r
62// create table header showing to download a xls (excel) file\r
63header("Content-type: application/octet-stream");\r
64header("Content-Disposition: attachment; filename=$export_filename");\r
65header("Cache-Control: public");\r
66header("Content-length: ".strlen($data)); // tells file size\r
67header("Pragma: no-cache");\r
68header("Expires: 0");\r
69\r
70// output data\r
71echo $header."\n".$data;\r
72\r
73?>\r