PHP - Export To Excel

Hello,

I want to export data from php-mysql to excel in a particular cell.

1. Get the data from php-Mysql and export data to excel sheet. I already create a template excel sheet format.I want to export data from mysql to a particular cell. Sorry I can't able to attach my template excel sheet.
2. Is it possible to export image?


Thanks in advance

Hi All,

      As we know........to export excel sheet the database content....we query the data base and then process the result and  using Workbook  and header we export the result in excel..................

 Is there any way we can reduce the down load time  or increase the performance for same. ?

Thanks.

Hi,

i have export the search result to excel file, but arabic characters not displaying properly in excel file, eventhough i mention the header content type as utf-8

here my code...

Code: [Select]
<?php
include("global.php");
// Original PHP code by Chirp Internet: www.chirp.com.au 
// Please acknowledge use of this code by including this header. 

function cleanData(&$str) 
{
 $str = preg_replace("/\t/", "\\t", $str); $str = preg_replace("/\r?\n/", "\\n", $str); 
 if(strstr($str, '"')) $str = '"' . str_replace('"', '""', $str) . '"'; 
} 

$sql = $_SESSION['emp_search_sql'];
// file name for download 
$filename = "employees_" . date('Ymd') . ".xls";
header("Content-Disposition: attachment; filename=\"$filename\"");
header("Content-Type: application/vnd.ms-excel; charset=UTF-8"); 
header("Pragma: no-cache";


$flag = false;
 $result = $DB_site->query($sql) or die('Query failed!');
while(false !== ($row = mysql_fetch_assoc($result)))
{
if(!$flag) 
{
// display field/column names as first row 

echo implode("\t", array_keys($row)) . "\n"; $flag = true; 
} 
array_walk($row, 'cleanData'); 
echo implode("\t", array_values($row)) . "\n";
}

?>


Thanks,

Hi Guys....

I need to export data into excel file. So far its works on IE and Firefox. But if i using Google Chrome it download only php file. Eg(suppose file test.xls but it goes to test.php). 

Why it happen??

Hi..,
I use below method to export data to excel.

header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename=abc.xls');

if I run the script from the server. (http://localhost/export.php) it is work. (pop-up window if i want save or open the file)

but if i run the script from the client (http://192.168.1.5/export.php) it is not work. (nothing happen)

any idea how to solve this?

I need some help and hopefully will respond. I'm trying to put some information to the second row of the excel sheet once extracted. Can somebody help me with this script? I'm trying to move the address line to the second row. Thanks.

Hello,
i am currently getting an Microsoft Excel formatted text file whose save type is .Txt from a URL.I used to open it and will change the save type  as excel file. Please suggest whether we can do this with php code.

currently my code is like this,
<? php
copy("http://www.faa.gov/airports/airport_safety/airportdata_5010/menu/emergencyplanexport.cfm?Region=&District=&State=&County=&City=LAS%20VEGAS&Use=&Certification=","./contactsexport.xls");
?>

where as the contactsexport.xls type is .Txt which i need it in .xls

Thanks in Advance.

I need to write a cron job that will sync 2 databases.
Is there a quick way to write an export from one database for import into another?
Or do i manually have to write the select statements per table, format them as insert ignore strings, then execute them on the other database?

Thanks guys!

Hi,

I have tried these PDF generators:

1. TCPDF- doesn't support css class. Styles will work if inline.
2. DOMPDF - Supports CSS class. Doesn't have Unicode support. Already tried load_font to convert ttf to amf but still Chinese char are not displayed properly.
- Also tried (installed) PDFlib, watermark appears in pdf but characters are not displayed properly.

Any suggestion in PDF generator that can do the functionality of TCPDF and DOMPDF? BTW i'm using xampp.
Thanks in advance.

Dear freak gurus out there,
I'm new to php and sql as i learn from online examples  .

Though there are lots of links and answers out there related to my post but none reflects what i need. You guys my only hope.

What i have:

3 tables : each has its own number of fields (tbl1=14 ,tbl2=9,tbl3=9). There is a field in each tables that can be related (FamilyField).

What i need:

export to csv all the records from all 3 tables which relates to search criteria (by user) based on a field (MembershipField) which the data may vary from other tables.

pseudo code:
$membership_type =$_POST['mtype'];

$sql=" SELECT field1,field2,field5,MembershipField from tbl1, (SELECT field1,field2,MembershipField from tbl2), (SELECT field3,field6,field8,MembershipField from tbl3) WHERE MembershipFied=$membership_type";

export to csv file by using INTO OUTFILE;

I have tried all the code i can find to export from 1 table to csv, and i manage to use UNION to get output from 3 tables but unable to export
as the error message was "incorrect usage of UNION and INTO.

is this possible? or any workaround?
guides with samples will be a great help.

Hi,

Am having a total brain melt down on a php/mysql export script. I've a table wp_bp_xprofile_data with this sort of layout:
ID
field_id
user_id
value

It shows data like so:
id | field_id | user_id | value
1 | 1 | 1| John Smith
2 | 2 | 1 |john@smith.com
3 | 3 | 1 |23 arcacia avenue
4 | 1 | 2 |Fred Smith
5 | 2 | 2 |fred@smith.com
6 | 3 | 2 |999 lets be avenue


field_id joins to another table wp_bp_xprofile_fields, where it has
id
name
This shows data like so:
id | name
1 | Name
2 | Email
3 | Address

What I need to do, is export the data to a csv so the CSV will then show:

Name | Email | Address
John Smith | john@smith.com | 23 arcacia avenue
Fred Smith | fred@smith.com | 999 lets be avenue

However, mine keeps coming back
Name | Email | Address
John Smith
john@smith.com
23 arcacia avenue
Fred Smith
fred@smith.com
999 lets be avenue

Here's my code:
[php]
   header ("Content-Disposition:\"inline; filename=export.csv");
   header ("Content-type: text/csv\"");
   
   $query = "SELECT VALUE FROM `wp_bp_xprofile_data` ORDER BY `wp_bp_xprofile_data`.`user_id` ASC";
   $result = mysql_query($query) or die("Query failed : " . mysql_error());
   
   echo "email,telephone,county,town,add 2,add 1,company name,name,acc type,date joined,country\r\n"; //header

   while($row = mysql_fetch_row($result)){
      echo "$row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6],$row[7],$row[8],$row[9],$row[10],$row[11]\r\n"; //data
      
      echo "$row[]\r\n"; //data
   }
[php]
Where am I going wrong?

TIA

I have this code that is supposed to export the results of this query into a CSV. When I run the code in Firefox, it works perfectly. The only browser that it doesn't work in is IE. It opens an HTML page with the results on the page instead of prompting to download the CSV. Can anyone help me out?

Code: [Select]
<?php
//code to export report to excel
if(isset($_GET['date'])){
    
include('../inc/db.php');    
    
if (!$link = mysql_connect($dbhost, $dbuser, $dbpass)) {
    echo 'Could not connect to mysql';
    exit;
}

if (!mysql_select_db($dbname, $link)) {
    echo 'Could not select database';
    exit;
}
$date = $_GET['date']; 

$select = "SELECT patients.lname, patients.fname, patients.dob, patients.sex, visit_data.cc, 
FROM_UNIXTIME(visit_data.reg_time), FROM_UNIXTIME(visit_data.discharged_time), visit_data.disposition, visit_data.leftby 
FROM patients, visit_data WHERE discharge_date = '$date' AND patients.patientid = visit_data.patientid";

$export = mysql_query ( $select ) or die ( "Sql error : " . mysql_error( ) );

$fields = mysql_num_fields ( $export );

for ( $i = 0; $i < $fields; $i++ )
{
    $header .= mysql_field_name( $export , $i ) . "\t";
}

while( $row = mysql_fetch_row( $export ) )
{
    $line = '';
    foreach( $row as $value )
    {                                            
        if ( ( !isset( $value ) ) || ( $value == "" ) )
        {
            $value = "\t";
        }
        else
        {
            $value = str_replace( '"' , '""' , $value );
            $value = '"' . $value . '"' . "\t";
        }
        $line .= $value;
    }
    $data .= trim( $line ) . "\n";
}
$data = str_replace( "\r" , "" , $data );

if ( $data == "" )
{
    $data = "\n(0) Records Found!\n";                        
}

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=DailyLog.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header\n$data";

}
?>

I created this php class which recieves an sql query from flash and then produces a csv file for download. The code in its current form produces a csv file on the server. However, I want it to save on the client computer with a 'Save As' dialog box. I am testing on localhost.

Here is the class:


<?php

class CSVExport
{
public function __construct()
{
require_once 'Zend/Date.php';
require_once 'DatabaseConnection.php';
}

public function exportCSV($query)
{

$result = mysql_query($query);

$fname = 'CSVFile.csv';

$headers = array();
$rowArray = array();

$numFields = mysql_num_fields($result);

for ($i = 0; $i < $numFields; $i++) {
        $headers[] = mysql_field_name($result , $i);
}

$fp = fopen($fname, 'w');

if ($fp && $result) {
    header('Content-Type: application/csv');
    header('Content-Disposition: inline; filename='.$fname);

fputcsv($fp, $headers);
while ($thisrow = mysql_fetch_row($result)) {
fputcsv($fp, $thisrow);
}

readfile($fname);
fclose($fp);
}
        die;
}

}



Thanks

Hello,

How can I export mysql database (tables) with php from browser to backup.sql , something like in phpMyAdmin.
And simple option with file save as???

Thanks
Regards