PHP - E-mailing Exported Sql Tables In Excel File Format
what I am trying to do is use this php script to load the data being submitted in the html form into my database and then populate the database into an excel (xls) file and then e-mail it to my address.
Everything works great it populates into the database and creates the xls file perfect. But it is wanting me to download the file. What can I add to the script to have it e-mail the file to my e-mail address INSTEAD of downloading it. Code: [Select] <?php define('DB_NAME', 'database'); define('DB_USER', 'username'); define('DB_PASSWORD', 'password'); define('DB_HOST', 'hostname'); $link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD); if (!$link) { die('Could not connect: ' . mysql_error()); } $db_selected = mysql_select_db(DB_NAME, $link); if (!$db_selected) { die('Can\'t use ' . DB_NAME . ': ' . mysql_error()); } $value1 = $_POST['groupname']; $value2 = $_POST['name']; $value3 = $_POST['address']; $value4 = $_POST['city']; $value5 = $_POST['state']; $value6 = $_POST['zip']; $value7 = $_POST['homephone']; $value8 = $_POST['cellphone']; $value9 = $_POST['email']; $value10 = $_POST['age']; $value11 = $_POST['maritalstatus']; $value12 = $_POST['income']; $value13 = $_POST['contact1']; $value14 = $_POST['contact2']; $value15 = $_POST['contact3']; $value16 = $_POST['date1']; $value17 = $_POST['date2']; $value18 = $_POST['date3']; $sql = "INSERT INTO clients (groupname, name, address, city, state, zip, homephone, cellphone, email, age, maritalstatus, income, contact1, contact2, contact3, date1, date2, date3) VALUES ('$value1', '$value2', '$value3', '$value4', '$value5', '$value6', '$value7', '$value8', '$value9', '$value10', '$value11', '$value12', '$value13', '$value14', '$value15', '$value16', '$value17', '$value18')"; if (!mysql_query($sql)) { die('Error: ' . mysql_error()); } mysql_close(); mysql_connect('hostname', 'username', 'password'); mysql_select_db('database'); $sql = "SELECT `groupname` AS `Group`, `name` AS `Customer Name`, `address` AS `Address`, `city` AS `City`, `state` AS `State`, `zip` AS `Zip Code`, `homephone` AS `Home Phone`, `cellphone` AS `Cell Phone`, `email` AS `E-Mail`, `age` AS `Age Group`, `maritalstatus` AS `Marital Status`, `income` AS `Household Income`, `contact1` AS `Contact VIA`, `contact2` AS `Contact VIA`, `contact3` AS `Contact VIA`, `date1` AS `1st Date`, `date2` AS `2nd Date`, `date3` AS `3rd Date` FROM fundtour_info.clients clients"; // Query Database $result=mysql_query($sql); $filename = 'file.xls'; // Send Header header("Pragma: public"); header("Expires: 0"); header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); header("Content-Type: application/force-download"); header("Content-Type: application/octet-stream"); header("Content-Type: application/download");; header("Content-Disposition: attachment;filename=$filename"); header("Content-Transfer-Encoding: binary "); // XLS Data Cell xlsBOF(); xlsWriteLabel(0,0,"Group"); xlsWriteLabel(0,1,"Name"); xlsWriteLabel(0,2,"Address"); xlsWriteLabel(0,3,"City"); xlsWriteLabel(0,4,"State"); xlsWriteLabel(0,5,"Zip Code"); xlsWriteLabel(0,6,"Home Phone"); xlsWriteLabel(0,7,"Cell Phone"); xlsWriteLabel(0,8,"E-mail Address :"); xlsWriteLabel(0,9,"Age Group"); xlsWriteLabel(0,10,"Marital Status"); xlsWriteLabel(0,11,"Income"); xlsWriteLabel(0,12,"Contact Via"); xlsWriteLabel(0,13,"Dates"); $xlsRow = 1; while(list($groupname,$name,$address,$city,$state,$zip,$homephone,$cellphone,$email,$age,$maritalstatus,$income,$contact1, $contact2, $contact3,$date1, $date3, $date3)=mysql_fetch_row($result)) { ++$i; xlsWriteLabel($xlsRow,0,"$groupname"); xlsWriteLabel($xlsRow,1,"$name"); xlsWriteLabel($xlsRow,2,"$address"); xlsWriteLabel($xlsRow,3,"$city"); xlsWriteLabel($xlsRow,4,"$state"); xlsWriteLabel($xlsRow,5,"$zip"); xlsWriteLabel($xlsRow,6,"$homephone"); xlsWriteLabel($xlsRow,7,"$cellphone"); xlsWriteLabel($xlsRow,8,"$email"); xlsWriteLabel($xlsRow,9,"$age"); xlsWriteLabel($xlsRow,10,"$maritalstatus"); xlsWriteLabel($xlsRow,11,"$income"); xlsWriteLabel($xlsRow,12,"$contact1, $contact2, $contact3"); xlsWriteLabel($xlsRow,13,"$date1, $date3, $date3"); $xlsRow+++; } xlsEOF(); exit(); function xlsBOF() { echo pack("ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0); return; } function xlsEOF() { echo pack("ss", 0x0A, 0x00); return; } function xlsWriteNumber($Row, $Col, $Value) { echo pack("sssss", 0x203, 14, $Row, $Col, 0x0); echo pack("d", $Value); return; } function xlsWriteLabel($Row, $Col, $Value ) { $L = strlen($Value); echo pack("ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L); echo $Value; return; } ?> Thanks for any help Similar TutorialsHi Guys, I am looking for a way to format excel cells using PHP, I am exporting mysql data to excel file using below method: $fields = array("Date", "Time In", "Time Out", "Hours", "Project", "Component", "Sub-Component", "Work Done", "Supervisor Changes", PHP_EOL); file_put_contents($filename, implode("\t", $fields), FILE_APPEND); Now I want to make BOLD all this header fields, so please help me to this construct.. Thank you 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. Hello, well I guess my question is pretty clear. How would I be able to read data out of an Excel sheet? F.E. to allow my users to simply upload an .xls file to help them avoid the coding and such. Of course, I know how to create the tables etc, the main problem is just getting that data. Also, I am uncertain if uploading an .xls file is a lot harder/totally done differently then images, and if so.. what is the best way to do this? P.s. I do want all the fancy equations to be included, just normally entered data (which would just follow some sort of a protocol). Thanks in advance. Hey, been trying to make a form to upload an image to the server and also email the image as attachment. After nearly a week I managed to make it work. Only some validation needs to be done. My next step is to make it work for multiple files, ie. 5. The form will have 5 boxes to upload images and no more. I have tried a few different things with for loops ( ie for $i=0; $i<5; $i++ {...} ) but no luck. I can't think of any solution of a loop to upload multiple files and then email multiple attachment as my code. The code is: <?php session_start(); $imagename1 = $_FILES["uploadFile1"]["name"]; $_SESSION['imagename1'] = $imagename1; $imagename1 = $_SESSION['imagename1']; $imagetype1 = $_SESSION['imagetype1']; $imagesize1 = $_SESSION['imagesize1']; $imagetemp1 = $_SESSION['imagetemp1']; $thankyouurl = 'thankyou.php' ; $errorurl = 'error.php' ; $http_referrer = getenv( "HTTP_REFERER" ); $http_agent = getenv( "HTTP_USER_AGENT" ); $domain = $_SERVER['REMOTE_ADDR']; $filename = $_SESSION['uploadFilename']; if(isset($_POST['send'])) { $fp = fopen($imagetemp1, "rb"); $file = fread($fp, $imagesize1); $file = chunk_split(base64_encode($file)); $num = md5(time()); fclose($fp); // UPLOAD FILE // possible PHP upload errors $errors = array(1 => 'php.ini max file size exceeded', 2 => 'html form max file size exceeded', 3 => 'file upload was only partial', 4 => 'no file was attached'); // check the upload form was actually submitted else print form isset($_POST['send']) or error('the upload form is neaded', $uploadForm); // check for standard uploading errors ($_FILES[$fieldname1]['error'] == 0) or error($errors[$_FILES[$fieldname1]['error']], $uploadForm); // check that the file we are working on really was an HTTP upload @is_uploaded_file($_FILES[$fieldname1]['tmp_name']) or error('not an HTTP upload', $uploadForm); // validation... since this is an image upload script we // should run a check to make sure the upload is an image @getimagesize($_FILES[$fieldname1]['tmp_name']) or error('only image uploads are allowed', $uploadForm); // make a unique filename for the uploaded file and check it is // not taken... if it is keep trying until we find a vacant one $now = date("y-m-d_H-i-s", time()); while(file_exists($uploadFilename = $uploadsDirectory.$now.'_'.$_FILES[$fieldname1]['name'])) { $now++; } // now let's move the file to its final and allocate it with the new filename @move_uploaded_file($_FILES[$fieldname1]['tmp_name'] , $uploadFilename) or error('receiving directory insuffiecient permission', $uploadForm); $from= 'youremail@server.co.uk'; $emailTo = 'mail@hotmail.com'; $subject = 'Quote Form'; $body = "equipment_type: $equipment_type \n\nModel: $Model \n\nMake: $Make \n\nModelNumber: $ModelNumber"; $fp = $_SESSION['fp']; $file = $_SESSION['file']; $num = "==Multipart_Boundary_x{$semi_rand}x".$_SESSION['num']; $headers = 'From: Name <'.$from.'>' . "\r\n" . 'Reply-To: ' . $emailTo; //Normal headers $headers .= "MIME-Version: 1.0\r\n"; $headers .= "Content-Type: multipart/mixed; "; $headers .= "boundary=".$num."\r\n"; $headers .= "--$num\r\n"; // This two steps to help avoid spam $headers .= "Message-ID: <".gettimeofday()." TheSystem@".$_SERVER['SERVER_NAME'].">\r\n"; $headers .= "X-Mailer: PHP v".phpversion()."\r\n"; // With message $headers .= "Content-Type: text/html; charset=iso-8859-1\r\n"; $headers .= "Content-Transfer-Encoding: 8bit\r\n"; $headers .= "".$body."\n"; $headers .= "--".$num."\n"; // Attachment headers $headers .= "Content-Type:".$imagetype1." "; $headers .= "name=\"".$imagename1."\"r\n"; $headers .= "Content-Transfer-Encoding: base64\r\n"; $headers .= "Content-Disposition: attachment; "; $headers .= "filename=\"".$imagename1."\"\r\n\n"; $headers .= "".$file."\r\n"; $headers .= "--".$num."--"; mail($emailTo, $subject, $body, $headers); header( "Location: $thankyouurl" ); } ?> the form is like this Code: [Select] <input name="uploadFile1" id="uploadFile1" value="" class="file" type="file"/>can anyone suggest anything please? how can I form the for statement? much appreciated I have a PHP code to download one mysql table to excel sheet.what i want to do is download 2 tables in to same excel sheet.those tables are should be in different excel worksheet.please help me. this code work fine.it's download one table
<?php ob_start(); session_start(); include('dbconnection.php'); $usr= $_SESSION['fname']; header('Content-Type: text/csv'); header('Content-Disposition: attachment;filename=exported-data.csv'); $select_table=mysql_query("select * from regfarmer WHERE ffname='$usr'"); $rows = mysql_fetch_assoc($select_table); if ($rows) { getcsv(array_keys($rows)); } while($rows) { getcsv($rows); $rows = mysql_fetch_assoc($select_table); } function getcsv($no_of_field_names) { $separate = ''; foreach ($no_of_field_names as $field_name) { if (preg_match('/\\r|\\n|,|"/', $field_name)) { $field_name = '' . str_replace('', $field_name) . ''; } echo $separate . $field_name; $separate = ','; } echo "\r\n"; } ?> Hey everyone, I'm pretty new to this, not my full time job, but just something I thought I'd give a shot... I have a database, in postgres, in which I make my query and I go fetch all the info I need. What I need to do next is the tricky part for me. For each line of results received, I have to output to a text file (.txt) but I have to respect a format that was given to me from the person requesting the info. Example: Character 1 must be G or N. Character 2 to to 11 will be a result from my database search, which is a 10 digit string. Character 12 to 14 must be spaces. Another rule is: start at character 78: input a value from my database search but it must not exceed 20 characters and if it is less then 20 character, fill the remaining with spaces. If anyways has a code I can copy and work off of I would really appreciate it....thanks! Hi Attached file I get sent a excel file as attached each day updating products and stock levels and prices.. I am trying to find away to dump that file into my database without having to restructure the excel file. It needs to be in the data base in such away that I can create a popout menu with the headings you see in the file i.e CPUs :: Intel CPUs :: AMD etc etc to filter the procucts in my shopping cart.. I have 3 tables atm, maincat, subcat and products.. But it takes way to long to try and manually sort the changes. Any Ideas on how I can do it by just dumping the excel file? Hello,
I am trying to display the data from two tables with proper format. But Its not happening
Here is my 1st table - orders
2.PNG 10.12KB
0 downloads
and my 2nd table - order_line_items
1.PNG 14.92KB
0 downloads
I want to display like this
3.PNG 4.03KB
0 downloads
Here is my code
$query = $mysqli->query("SELECT orders.order_id, orders.company_id, orders.order_for, order_line_items.order_id, order_line_items.item, order_line_items.unit,SUM(order_line_items.unit_cost * order_line_items.quantity) AS 'Total', order_line_items.tax from orders INNER JOIN order_line_items ON orders.order_id = order_line_items.order_id where orders.order_quote = 'Order' GROUP BY order_line_items.id"); ?> <table id="dt_hScroll" class="table table-striped"> <thead><tr> <th>Order ID</th> <th>Company</th> <th>Contact Person</th> <th>Products</th> <th>Total</th> </tr> </thead> <tbody> <?php while($row = $query->fetch_array()) { ?> <tr> <td><?php echo $row['order_id']; ?></td> <td><?php echo $row['company_id']; ?></td> <td><?php echo $row['contact_person'] ?></td> <td><?php echo $row['item']; ?></td> <td><?php echo $row['Total']; ?> %</td> </tr> <?php }But here order ID, Company ID, Contact Person are also repeating thrice with item in order_line_items table Please suggest me how to do this Hi there, I have a cronjob for PHP which generates a table with dynamic data and this table is exported to excel when run. When i have one table it downloads one excel file. I want to download multiple excel files using a for loop. Please Help... Any suggestions will be helpful.. Thank you, Regards, Rohit Hello, Is it possible to read the content of an excel file with file_get_contents. I tried it a couple times. The result looked like this: ������������������������������������������������������������������������������������������������������������������������������������Oh��+'��0�8@Td p|�WorkshopJohn@�v����@��p�������՜.��+,�D��՜.��+,�$�HPX`hp x�� HuidcoachHuidcoach en spec. in huidverbSheet3 f2�ـ����\pJohn B�a�=���=�?�8�@�"��1���A And if it's possible can somebody tell me what i'm doing wrong. Thanx Hi all, Does anyone know of a way of extracting/reading images from an excel file using PHP? This seems great (http://phpexcel.codeplex.com/) but I can't see a way of reading images from excel files. Any help would be greatly appreciated! Thanks I have an excel file that I want to show in PHP page. I dont want this to first download and then view. I want it something like gmail where you just click on the file name and it opens up. Can you please give me an idea as how to do this. Regards, Faisa Hello Everyone, I'm new to PHP and got a good big stuff. I would like to write an image in excel file. There will be almost 600+ rows and each row will have url and qrcode image. I would like to write an excel file which will write url and respective image into the next cell of the url. Is it possible to write an image in excel file? If yes, can anyone please guide me how? I have read so many post saying to use a pear package but again pear package is not allowing to write jpe/gif it only allows .bmp files. So, pear package is I don't think will work for me. I have also attached an example excel file which is showing the way I require to have an output. Please anyone if having any idea guide me and let me know. Thank you in advance for your help. Looking forward to hear from you soon! hello everyone, please anyone can help me how to read data of excel file in php Hi, this is my first time here. i am trying to create an xls file from the data i get from mysql. here is the code i tried but i am not able to use it correctly. Code: [Select] ob_start(); include('config'); $datacat = mysql_query("SELECT * FROM `leads`") or die(mysql_error()); $name = mysql_fetch_assoc($datacat); $line1="Industry,Company Name,Officials Name,Job Title,Country,Direct Number Mobile Number,Switch Board Number,E-mail,Executive NamePitch Date,Call Back Date,Comments\t"; while($row = mysql_fetch_array($datacat, MYSQL_ASSOC)) { $line2= $name['industry'].",".$name['company_name'].",".$name['officials_name'].",".$name['job_title'].",".$name['country'].",".$name['direct_number_mobile_number'].",".$name['switch_board_number'].",".$name['email'].",".$name['executive_name'].",".$name['pitch_date'].",".$name['call_back_date'].",".$name['comment']."\t"."\n"; } $data="$line1\n$line2\n"; header("Content-type: application/x-msdownload"); header("Content-Disposition: attachment; filename=extraction.xls"); header("Pragma: no-cache"); header("Expires: 0"); print "$header\n$data"; i get my webpage title in the top of xls file and all the fields in xls file are separated by commas, whereas i want them to be in proper tables. Help would be much appreciated. I have an excel file that has several columns with different data and I need to create a form that can upload this excel file and insert the data from the columns into its conrresponding data columns in a mysql database. I know how to create the form but I have no idea how to get the file and tell it to input each cell of data into its corresponding field in the mysql database. Does anyone know how to do this? Any help would be appreciated. Thanks, Hi.., My code is like: Code: [Select] <?php $filename ="excelreport.xls"; print "<table border=1>"; print "<tr ><td valign=bottom><img src=http://abc/images/big.png ></img></td> <td valign=bottom><img src=http://abc/images/small.png ></img></td> </tr>"; print "</table>"; header('Content-type: application/ms-excel'); header('Content-Disposition: attachment; filename='.$filename); ?>when I open the excel file, the images alignment is from top to bottom(equal from top of image). what i want is from bottom to top(equal from bottom of image). i did try using valign in td property, but still the images equal from top of image thanks for any reply/ help 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?? death.announcement.contact.php The above file format is correct? thank you |