Page 1 of 1

Export to excel from a query

Posted: Sat Jan 01, 2011 7:35 am
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?

Re: Export to excel from a query

Posted: Sat Jan 01, 2011 11:57 am
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.

Re: Export to excel from a query

Posted: Sun Jan 16, 2011 6:41 am
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>