PHP - Converting Microsoft Excel Formatted Text File As An Excel File

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

Hi 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 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!

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

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

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??

This topic has been moved to Other.

http://www.phpfreaks.com/forums/index.php?topic=316401.0

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.

Hi All,

I am exporting data to excel, but run into a problem if the text contains a line break.  When it gets to the line break, it cuts off the rest of the text.

Here is my printer code:

Code: [Select]
$file = 'Notes_Export';
$csv_output = array();


$tmp = array();
$tmp[] = 'Created On';
$tmp[] = 'Created By';
$tmp[] = 'Note';

$csv_output[] = '"' . implode('","', $tmp) . '"';

$sql  = "SELECT pn.created_on, CONCAT( u.firstname,' ', u.lastname) AS created_by, pn.note FROM prop_notes pn LEFT JOIN users u ON pn.created_by = u.user_id ";
$sql .= "WHERE pn.deleted_by IS NULL AND pn.archive = '0' AND pn.property_id = '".$_GET['pid']."'";
      $result = mysqli_query($connect, $sql);

         while($rowr = mysqli_fetch_row($result))
         {
$tmp = array();
for ($j=0; $j<3; $j++) {$tmp[] = $rowr[$j];}
$csv_output[] = '"' . implode('","', $tmp) . '"';
         }


$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
// header( "Content-disposition: filename=".$filename.".csv");
header("Content-disposition: attachment; filename=".$filename.".csv");
print implode("\n",$csv_output) . "\n";
exit;

Hi there,

I got some good advice from Harristweed the other day about how to export from Mysql to excel, so that each field appears in a different column in excel.

I expanded the code Harristweed gave me to cover all fields in my database, and all went well until the 2nd last field, when I hit a brick wall. Unlike previous fields, which were based on info inputted via radio buttons, dropdown lists, or text boxes, the field causing problems was based on data that came from a text area on the web form.

A few tests revealed that the problem was related to when a user pressed the "Enter" key while filling in that field, to begin a new paragraph. When this happens, it would go into the database fine. However, when I used the code to export it to excel, anything after the "Enter" key had been pressed would appear in a new cell on a new row, thereby putting the table out of alignment.

I could get around this by rewording the question into a few questions which each use a text box rather than a text area box.

However, if there is a way to get around this by tweaking the php code, I'd love to know. The field with which I'm having the problem is the one called "experience".

Thanks for your time,

Dave




<?PHP


$db = mysql_connect("localhost", "MYUSERNAME", "MYPASSWORD");

mysql_select_db("MYDATABASE",$db);

$query="SELECT * FROM applications2010";
$result=mysql_query($query);

$header=" date\t firstname\t lastname\t sex\t dobday\t dobmonth\t dobyear\t streetaddress\t suburb\t state\t postcode\t country\t preferredphone\t secondphone\t thirdphone\t emailaddress\t emconname\t emconphone\t experience\t ";

while($row = mysql_fetch_assoc($result)) {   

if(empty($row[date]))$row[date]=" ";   
if(empty($row[firstname]))$row[firstname]=" ";   
if(empty($row[lastname]))$row[lastname]=" ";   
if(empty($row[sex]))$row[sex]=" ";   
if(empty($row[dobday]))$row[dobday]=" ";   
if(empty($row[dobmonth]))$row[dobmonth]=" ";   
if(empty($row[dobyear]))$row[dobyear]=" ";   

if(empty($row[streetaddress]))$row[streetaddress]=" ";   
if(empty($row[suburb]))$row[suburb]=" ";   
if(empty($row[state]))$row[state]=" ";   
if(empty($row[postcode]))$row[postcode]=" ";   
if(empty($row[country]))$row[country]=" ";   
if(empty($row[preferredphone]))$row[preferredphone]=" ";   
if(empty($row[secondphone]))$row[secondphone]=" ";   
if(empty($row[thirdphone]))$row[thirdphone]=" ";   

if(empty($row[emailaddress]))$row[emailaddress]=" ";   

if(empty($row[emconname]))$row[emconname]=" ";   
if(empty($row[emconphone]))$row[emconphone]=" ";   
if(empty($row[experience]))$row[experience]=" ";   




$line = '';   
$line .= "$row[date]\t $row[firstname]\t $row[lastname]\t $row[sex]\t $row[dobday]\t $row[dobmonth]\t $row[dobyear]\t $row[streetaddress]\t  $row[suburb]\t  $row[state]\t  $row[postcode]\t $row[country]\t $row[preferredphone]\t $row[secondphone]\t $row[thirdphone]\t $row[emailaddress]\t $row[emconname]\t $row[emconphone]\t $row[experience]\t ";   

$data .= trim($line)."\n"; }
$data = str_replace("\r","",$data); 
if ($data == "") 
{    
$data = "\n(0) Records Found!\n";                    
}

header("Content-type: application/x-msdownload"); header("Content-Disposition: attachment; filename=filename=".date("d-m-Y")."-export.xls"); header("Pragma: no-cache"); header("Expires: 0"); print "$header\n$data";exit;



?>

Other than downloading the file, editing it on the local PC, saving it, and uploading it back to the server using some applicable script, any ideas?

Hi

I have a script which references a DB and then produces an XML output saved as XLS to then open in Excel.

This works an absolute dream in my dev environment using IE7 and in live using Firefox.

However my entire audience uses IE7 and this is where it breaks in the live environment.  For some reason, I cannot get the download to work.

I get a windows pop-up with an error on it - see attached.

If you need to see code, let me know which bit of code could possibly cause this, i.e. headers etc.

I've not posted code as there's loads of it and I can't understand why it works in dev in IE 7 but not in live, and works in Firefox in live.