Export to excel from a query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tintumon
Forum Newbie
Posts: 3
Joined: Sat Jan 01, 2011 7:15 am

Export to excel from a query

Post by tintumon »

I am using Arca Database Browser 2.1, and I'm really new at SQL. I have run a query on a database that has about 10 tables, needing some information from most of them. Is there a way I can now export the returned data to an excel spreadsheet? Is there a better piece of open source software I can be using?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Export to excel from a query

Post by califdon »

I'm not familiar with that software, but it is quite simple to produce an Excel spreadsheet from a query against a MySQL database, using PHP. After sending an appropriate header that tells the browser that what is about to be sent is an Excel spreadsheet (see http://evolt.org/node/26896/, http://www.ibm.com/developerworks/opens ... -phpexcel/ and similar) and connecting to your database and running your query, the browser will open Excel (assuming it's installed and is designated as the default application) and the viewer will have the spreadsheet, which they may save on their computer, or just view.

If your database is other than MySQL, you may need to add a step or two. If you don't want to use PHP, I'm afraid I don't have much information to offer.
Cristopher
Forum Newbie
Posts: 1
Joined: Sun Jan 16, 2011 5:59 am

Re: Export to excel from a query

Post by Cristopher »

you can config example code below

Code: Select all

<?php
header("Content-Type: application/vnd.ms-excel"); # browser known open excel application
header('Content-Disposition: attachment; filename="Appointment.xls"');# file_name
?>
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<HTML>
<HEAD>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</HEAD>
<BODY>
<?php 
      include("config_db.php");
	  $conn  = mysql_connect($dbhost,$dbuser,$dbpass) or die(mysql_error());
      $db    = mysql_select_db($dbname) or die(mysql_error());
	  mysql_query("SET NAMES UTF8");
	  $sql	 = "SELECT B.appoint_date,A.id_card_no,A.cust_full_name,B.contact_type,B.appointment_type,B.ao_name,
	  B.memo,B.flag_update
	  FROM tbank_domicile_customer A,tbank_domicile_appointment B 
	  WHERE A.seq=B.cust_list_owner";
	  $query = mysql_query($sql) or die(mysql_error());

?>
<TABLE  x:str BORDER="1">
<TR>
    <TD align="center"><b>appoint_date</b></TD>
    <TD align="center"><b>id_card_no</b></TD>
    <TD align="center"><b>cust_full_name</b></TD>
    <TD align="center"><b>contact_type</b></TD>
    <TD align="center"><b>appointment_type</b></TD>
    <TD align="center"><b>ao_name</b></TD>
    <TD align="center"><b>memo</b></TD>
    <TD align="center"><b>flag_update</b></TD>
</TR>
<?php
	while($row = mysql_fetch_array($query)){
?>
<TR>
    <TD><?php echo $row['appoint_date']; ?></TD>
    <TD><?php echo $row['id_card_no']; ?></TD>
    <TD><?php echo $row['cust_full_name']; ?></TD>
    <TD><?php echo $row['contact_type']; ?></TD>
    <TD><?php echo $row['appointment_type']; ?></TD>
    <TD><?php echo $row['ao_name']; ?></TD>
    <TD><?php echo $row['memo']; ?></TD>
    <TD><?php echo $row['flag_update']; ?></TD>
</TR>
<?php } ?>
</TABLE>
<?php mysql_close($conn); ?>
</BODY>
</HTML>
Post Reply