excel dump
Ik heb onderstaand scriptje op het internet gevonden (http://fundisom.com/phparadise/php/databases/mySQL_to_excel) voor een excel dump uit mysql.
Ik ondervind het probleem dat bij het uitvoeren vh script ik het resultaat in mijn internetbrowser te zien krijg, en dus niet in excel.
Weet er iemand waaraan het zou kunnen liggen?
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
<?php
//EDIT YOUR MySQL Connection Info:
$DB_Server = "localhost"; //your MySQL Server
$DB_Username = ""; //your MySQL User Name
$DB_Password = ""; //your MySQL Password
$DB_DBName = ""; //your MySQL Database Name
$DB_TBLName = ""; //your MySQL Table Name
//$DB_TBLName, $DB_DBName, may also be commented out & passed to the browser
//as parameters in a query string, so that this code may be easily reused for
//any MySQL table or any MySQL database on your server
//DEFINE SQL QUERY:
//you can use just about ANY kind of select statement you want -
//edit this to suit your needs!
$sql = "Select * from $DB_TBLName";
//Optional: print out title to top of Excel or Word file with Timestamp
//for when file was generated:
//set $Use_Titel = 1 to generate title, 0 not to use title
$Use_Title = 1;
//define date for title: EDIT this to create the time-format you need
$now_date = date('m-d-Y H:i');
//define title for .doc or .xls file: EDIT this if you want
$title = "Dump For Table $DB_TBLName from Database $DB_DBName on $now_date";
/*
Leave the connection info below as it is:
just edit the above.
(Editing of code past this point recommended only for advanced users.)
*/
//create MySQL connection
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect)
or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
//execute query
$result = @mysql_query($sql,$Connect)
or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());
//if this parameter is included ($w=1), file returned will be in word format ('.doc')
//if parameter is not included, file returned will be in excel format ('.xls')
if (isset($w) && ($w==1))
{
$file_type = "msword";
$file_ending = "doc";
}else {
$file_type = "vnd.ms-excel";
$file_ending = "xls";
}
//header info for browser: determines file type ('.doc' or '.xls')
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment; filename=database_dump.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");
/* Start of Formatting for Word or Excel */
if (isset($w) && ($w==1)) //check for $w again
{
/* FORMATTING FOR WORD DOCUMENTS ('.doc') */
//create title with timestamp:
if ($Use_Title == 1)
{
echo("$title\n\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\n"; //new line character
while($row = mysql_fetch_row($result))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
//define field names
$field_name = mysql_field_name($result,$j);
//will show name of fields
$schema_insert .= "$field_name:\t";
if(!isset($row[$j])) {
$schema_insert .= "NULL".$sep;
}
elseif ($row[$j] != "") {
$schema_insert .= "$row[$j]".$sep;
}
else {
$schema_insert .= "".$sep;
}
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
//end of each mysql row
//creates line to separate data from each MySQL table row
print "\n----------------------------------------------------\n";
}
}else{
/* FORMATTING FOR EXCEL DOCUMENTS ('.xls') */
//create title with timestamp:
if ($Use_Title == 1)
{
echo("$title\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
echo mysql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names
//start while loop to get data
while($row = mysql_fetch_row($result))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]".$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
//following fix suggested by Josue (thanks, Josue!)
//this corrects output in excel when table fields contain \n or \r
//these two characters are now replaced with a space
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
}
}
?>
//EDIT YOUR MySQL Connection Info:
$DB_Server = "localhost"; //your MySQL Server
$DB_Username = ""; //your MySQL User Name
$DB_Password = ""; //your MySQL Password
$DB_DBName = ""; //your MySQL Database Name
$DB_TBLName = ""; //your MySQL Table Name
//$DB_TBLName, $DB_DBName, may also be commented out & passed to the browser
//as parameters in a query string, so that this code may be easily reused for
//any MySQL table or any MySQL database on your server
//DEFINE SQL QUERY:
//you can use just about ANY kind of select statement you want -
//edit this to suit your needs!
$sql = "Select * from $DB_TBLName";
//Optional: print out title to top of Excel or Word file with Timestamp
//for when file was generated:
//set $Use_Titel = 1 to generate title, 0 not to use title
$Use_Title = 1;
//define date for title: EDIT this to create the time-format you need
$now_date = date('m-d-Y H:i');
//define title for .doc or .xls file: EDIT this if you want
$title = "Dump For Table $DB_TBLName from Database $DB_DBName on $now_date";
/*
Leave the connection info below as it is:
just edit the above.
(Editing of code past this point recommended only for advanced users.)
*/
//create MySQL connection
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect)
or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
//execute query
$result = @mysql_query($sql,$Connect)
or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());
//if this parameter is included ($w=1), file returned will be in word format ('.doc')
//if parameter is not included, file returned will be in excel format ('.xls')
if (isset($w) && ($w==1))
{
$file_type = "msword";
$file_ending = "doc";
}else {
$file_type = "vnd.ms-excel";
$file_ending = "xls";
}
//header info for browser: determines file type ('.doc' or '.xls')
header("Content-Type: application/$file_type");
header("Content-Disposition: attachment; filename=database_dump.$file_ending");
header("Pragma: no-cache");
header("Expires: 0");
/* Start of Formatting for Word or Excel */
if (isset($w) && ($w==1)) //check for $w again
{
/* FORMATTING FOR WORD DOCUMENTS ('.doc') */
//create title with timestamp:
if ($Use_Title == 1)
{
echo("$title\n\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\n"; //new line character
while($row = mysql_fetch_row($result))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
//define field names
$field_name = mysql_field_name($result,$j);
//will show name of fields
$schema_insert .= "$field_name:\t";
if(!isset($row[$j])) {
$schema_insert .= "NULL".$sep;
}
elseif ($row[$j] != "") {
$schema_insert .= "$row[$j]".$sep;
}
else {
$schema_insert .= "".$sep;
}
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
//end of each mysql row
//creates line to separate data from each MySQL table row
print "\n----------------------------------------------------\n";
}
}else{
/* FORMATTING FOR EXCEL DOCUMENTS ('.xls') */
//create title with timestamp:
if ($Use_Title == 1)
{
echo("$title\n");
}
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++)
{
echo mysql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names
//start while loop to get data
while($row = mysql_fetch_row($result))
{
//set_time_limit(60); // HaRa
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]".$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
//following fix suggested by Josue (thanks, Josue!)
//this corrects output in excel when table fields contain \n or \r
//these two characters are now replaced with a space
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
}
}
?>
Gelieve Niet Bumpen::
Twee of meer keer achter elkaar in een topic posten heet bumpen. Bumpen is pas na 24 uur toegestaan en kan een reden zijn voor de admins en moderators om een topic te sluiten. Gebruik indien nodig de 
knop om je tekst aan te passen.
SanThe.
knop om je tekst aan te passen.SanThe.
Gewijzigd op 01/01/1970 01:00:00 door Jozef