PHP - Read Excel File With File_get_contents

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?

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

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

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.

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

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

Hi there,

I have an xml file which actually hold the currency conversion information which I downloaded from xe.com

In the xml, the currency section snapshot looks like this:

<currency>
<csymbol>EUR</csymbol>
<cname>Euro</cname>
<crate>0.713</crate>
<cinverse>1.403 </cinverse>
</currency>
this is the conversion of USD to Euro. And it tells how much is 1USD gonna be in Euros. i.e 1 USD = 0.713 EUR

I have a product page on my website having the rates shown in USD.

For the visitor is accessing the page from Europe it has to show the converted price in Euros.

My application can detect the visitors country if hes accessing the page from Europe so that is not a problem.

I just need to read the xml file and display the converted price based on the rates in the xml file.

How can I read the xml file and output the price in Euros based on the rate.

Thank you.

All comments and feedbacks are always welcome

[LIST=1]
CSV File Reading
Displaying CSV file: Clicking the Show Logfile.txt link at the top of the page should display the CSV records inside logfile.txt
Displaying CSV records formatted : Clicking the Show logfile.txt Formatted should display the CSV records formatted in an HTML table is descending order (more recent records are at the top).
Use this function to convert CSV records back to an array : fgetcsv()
   
I have most of this done and the problem I'm having is trying to read what  I put into the file. (I hard coded what should go into the file). Could you tell me what I'm doing wrong and what I should do to fix it?

Code: [Select]
<?
 
 if ($_POST['_act'] = 'csv'):
$fp = fopen('logfile.txt', 'a');
while (($data = fgetcsv($fp, 1000, ",")) !== FALSE){
print_r($data);
}
fclose($fp);
 endif;
 
if ($_SERVER['REQUEST_METHOD'] == 'POST'):

if (empty($_POST['fullname'])):
$errMsg['fullname'] = "Please fill in Your Full Name.";
endif;
if (ereg("'Mr\. '", $_POST['fullname']) == false):
$errMsg['fullname'] = "Please fill in Your Full Name.";
endif;
print_r($errMsgs); echo count($errMsgs);

if (count($disperrMsgs > 0)):
$dispErrMsgs = true;
else:
$fp = fopen('logfile.txt', 'a');
$fputcsv($fp, array("test", "lol", "roool"));
fclose($fp);


$dispSuccessMsg = true;
endif;


endif;
?>

<html>
<head>

 
<style type="text/css">
h1 {color:red}
div.error {border: 1px solid red; margin: 20px; padding: 20px; width: 400px}
table {border: 1px solid #CCC; margin: 20px; border-collapse: collapse;}
td, th {border: 1px solid #DDD; padding: 2px}
th {background-color:#363; color: white}
td.error {color:red}
div.success  {border: 1px solid green; margin: 20px; padding: 20px; width: 400px; color:green}
</style>
</head>
<body> 
<h1> Form Validation Lab with  Reg Expressions  </h1>
<p><a href="<?= $_SERVER['PHP_SELF'] ?> ">Refresh This Page</a> | <a href="./logfile.txt">Show Logfile.txt</a> | 
<a href="<?= $_SERVER['PHP_SELF'] ?>?_act=csv">Show logfile.txt Formatted</a> 
    <a href="<?= $_SERVER['PHP_SELF'] ?>?_act=clear">Clear logfile.txt </a></p>
   
<? if ($dispErrMsgs): ?>
<div class="error">
<p>There are errors in the code: </p>
    <ol>
    <? foreach($errMsg as $errMsgs ): ?>
        <li><?= $errMsgs ?></li>
            <? endforeach; ?>
            </ol>
    </div>
<? endif; ?>
<? if ($dispSuccessMsg): ?>
<div class="success">
<p>Thank you for your submission. </p>
</div>
<? endif; ?>


<form action="<?= $_SERVER['PHP_SELF'] ?>"  name="getstuff" method="post" >
  <input type="hidden" name="_act" value="post">
  <table cellspacing = "0">
    <tr>
      <th width="78">Full Name:</th>
      <td width="184"><input name="fullname" type="text" class="textbox"
      value="<? $_POST['fullname']; ?>" size="20" ></td>
      <td width="626" >Salution of Mr. or Mrs. followed by two text strings separated by any number of spaces. </td>
      </tr>

    <tr>
      <th>Street:</th>
      <td><input name="street" type="text" class="textbox"
      value="<? $_POST['street']; ?>" size="30"></td>
      <td >2 or 3 digit number followed by a text string ending with Street or Road separated by any number of spaces. </td>
    <tr>
      <th>Phone:</th>
      <td><input name="phone" type="text" class="textbox"
      value="<? $_POST['phone']; ?>" size="20"></td>

      <td >10 digits, first 3 digits have optional parentheses, either side of digits 456 are optional space, dot or hyphen. </td>
    </tr>
    <tr>
      <th>PostalCode:</th>
      <td><input name="postcode" type="text" class="textbox"
      value="<? $_POST['postcode']; ?>" size="8" maxlength="8"></td>
      <td > Postal Code:
        Char Char Digit Hyphen/space Char Digit Digit (No XYZ or 0's. Case insensitive. )</td>
    </tr>

    <tr>
      <th>Email:</th>
      <td><input name="email" type="text" class="textbox"
      value="<? $_POST['email']; ?>" size="25" maxlength="40"></td>
      <td > Must accept as
        a minimum : a@b.com</td>
     </tr>
  </table>
  <input name="reset" type='button' class="button" value='RESET' onClick="location.href = location.href">

  <input name="_submit" type="submit" class="button" value="Submit me now!!!" >
</form>
<p id="notice">Everything below here is for testing purposes Don't remove this from your lab
  as I will use these buttons to fill your form with good and bad data as I mark
  it.</p>
<script>
az=document.getstuff;
function goodstuff(){
az.fullname.value=" Mr. Joe Smith ";
az.street.value="135 Fennell Road ";
az.phone.value="905-575.1212";
az.postcode.value="Ln9-T23";
az.email.value="jsmith@gov.ca";

}
function goodstuff2(){
az.fullname.value=" Mrs. Josephine Smith ";
az.street.value=" 13  Fennell Street";
az.phone.value=" (905)-575.1212 ";
az.postcode.value=" Ln9 T23";
az.email.value=" jsmith@gov.ca  ";

}
function badstuff(){
az.fullname.value="Miss Josephine Smith";
az.street.value="1 Somewhere Avenue";
az.phone.value="905575122";
az.postcode.value="Z8N 3T2";
az.email.value="jsmith$gov#ca";

}
function badstuff2(){
az.fullname.value=" Joe Smith ";
az.street.value="  1392  Fennell Street";
az.phone.value="[905] 575-1212";
az.postcode.value=" Ly9 T20";
az.email.value=" @jsmith$gov#ca";

}
function goodandbad(){
az.fullname.value="Mrs. Joe";
az.street.value=" 135 Fennell Avenue West";
az.phone.value="905:5751212";
az.postcode.value=" L99-T23";
az.email.value=" jsmith@.gov.ca  ";

}

</script>
<form>
  <input type=button class="button" onClick="goodstuff();" value="Fill form with good values " length=100 >
  <input type=button class="button" onClick="goodstuff2();" value="Fill form with good values 2 " >
 
  <input type=button class="button" onClick="badstuff();" value="Fill form with BAD values ">
  <input type=button class="button" onClick="badstuff2();" value="Fill form with BAD values 2 ">
 
  <input type=button class="button" onClick="goodandbad();" value="Fill form with Good and BAD values 2">

  <br>
</form>


</BODY>
</HTML>