Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Getting data from a database into Excel spreadsheet
- Thread starter GigaG
- Start date
You can easily create a CSV file and have that opened in excel.
You would need to use the fopen and fwrite functions to write the information, and then deliver that to the browser:
Something like that will take the contents of a variable write it into a file and deliver it to the browser. It can then be opened by Excel.
Its just a matter of getting your database records ordered and comma separated into the variable
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
You would need to use the fopen and fwrite functions to write the information, and then deliver that to the browser:
Code:
$filename="filename.csv";
$handle=fopen($filename,"w");
fwrite($handle,$commaseparatedcontents]);
fclose($handle);
header('Content-type: application/xls');
header('Content-Disposition: attachment; filename="filename.csv"');
readfile($filename);Something like that will take the contents of a variable write it into a file and deliver it to the browser. It can then be opened by Excel.
Its just a matter of getting your database records ordered and comma separated into the variable
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
- Thread starter
- #3
When you read your information for your DB you take each row value and insert and concatenate it into your variable for example:
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
Code:
while (you are running through your DB){
$myvariable.="$row['fieldname1'][red],[/red]$row['fieldname2'][red],[/red]...";
}----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
- Thread starter
- #6
- Thread starter
- #7
Who's method are you referring to, mine or jpadie's?
And can you describe in more detail what you did?
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
And can you describe in more detail what you did?
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
- Thread starter
- #9
Yours.... ok I just tested a few things... my exact programing is below:
<?
$commaseparatedcontents='1,2,3';
$filename="filename.csv";
$handle=fopen($filename,"w");
fwrite($handle,$commaseparatedcontents);
fclose($handle);
header('Content-type: application/xls');
header('Content-Disposition: attachment; filename="filename.csv"');
readfile($filename);
?>
While running this script... it echos it out the browser, but then there is nothing that pops up that it wll download it as a file... Not sure if it is possible to do this or not... also when i do a save as... it saves it as filename_csv.txt file . If I rename this to filename.csv.... it works fine, but i would rather the user not to have to do this
MCP ACA-I CTP
<?
$commaseparatedcontents='1,2,3';
$filename="filename.csv";
$handle=fopen($filename,"w");
fwrite($handle,$commaseparatedcontents);
fclose($handle);
header('Content-type: application/xls');
header('Content-Disposition: attachment; filename="filename.csv"');
readfile($filename);
?>
While running this script... it echos it out the browser, but then there is nothing that pops up that it wll download it as a file... Not sure if it is possible to do this or not... also when i do a save as... it saves it as filename_csv.txt file . If I rename this to filename.csv.... it works fine, but i would rather the user not to have to do this
MCP ACA-I CTP
you can mix the two methods. the automation is caused by the header content-type to application/vnd.ms-excel (or other valid excel related content-type) and the formatting is done either by a comma delimited file (make sure you are using string delimiters too) or my outputting a formatted html table. either way, if excel is properly installed, the browser should launch it or open a file save prompt.
I just copied and pasted your code,and ran it as it was, and I get a download prompt in both Firefox ands IE7.
There might be something wrong with your excel installation, or your browser is configured to know what to do with files of type CSV and opens them directly.
The question being does it open it just like text, or does it open the excel in-browser plug in?
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
There might be something wrong with your excel installation, or your browser is configured to know what to do with files of type CSV and opens them directly.
The question being does it open it just like text, or does it open the excel in-browser plug in?
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
- Thread starter
- #12
- Thread starter
- #13
Not really, you can just concatenate the new row to the new variable, making sure to use an end of line character. such as "\r\n" to terminate a row.
Using the while loop, will automatically concatenate the following row. but don't forget the period before the equal sign and don;t forget to initialize your variable before suing it inside your loop.
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
Using the while loop, will automatically concatenate the following row. but don't forget the period before the equal sign and don;t forget to initialize your variable before suing it inside your loop.
Code:
$contents[red].[/red]="$row['fieldname1'],$row['fieldame2']...\r\n";----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
I meant the new row to the same variable. Not new variable.
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
- Thread starter
- #16
- Thread starter
- #17
And use double quotes, around your contents.
In other words:
Code:
$commaseparatedcontents.="1,2,3[red]\r\n[/red]";
$commaseparatedcontents.="4,5,6,7";And sorry no fancy things. if you want bolding or color, you'll need to use HTML and send that to excel.
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
- Thread starter
- #19
Try this:
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
Code:
$commaseparatedcontents.="<table><tr><td><font color=#FF0000>Red</font></td><td><font color=#0000FF>Blue</font></td></tr><tr><td><b>Bold</bold></td><td>Normal</td></tr></table>";----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.
- Status
Similar threads
