3 Replies - 1070 Views - Last Post: 24 September 2012 - 04:16 AM Rate Topic: -----

#1 damithaperera88  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 23-September 12

exported data to excel is not recognized in formulas

Posted 23 September 2012 - 09:57 PM

	
session_start();

include "db_connect/config_db.php";//DB CONNECTIVITY
include "db_connect/db_connect.php";
include "library/functions.php";

 $xlshead=pack("s*", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
 $xlsfoot=pack("s*", 0x0A, 0x00);
 function xlsCell($row,$col,$val) 
 {
  $len=strlen($val);
  return pack("s*",0x204,8+$len,$row,$col,0x0,$len).$val;
}

 $data=
 xlsCell(0,0,"Name")
  . xlsCell(0,1,"Salary") ;
  
 $rowNumber=0;
 $q="select name,salary from employee";

 $r=mysql_query($q,$con);

 while($row=mysql_fetch_array($r)) {
  $rowNumber=$rowNumber+1;
	 $name=$row['name'];
	 $tot=$row['salary'];
	 
	 $salary=floatval($tot);
	 
  $data.=xlsCell($rowNumber,0,$name) . xlsCell($rowNumber,1,$salary) ;
 }
 $filename="users.xls";

 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 ");
 echo $xlshead . $data . $xlsfoot;
 exit; //this is  important!

This code helps me to export data into a excel successfully.
Exported values are not recognized in SUM function.
I think error is Salary values are not stored in Value format.values are stored in Text Format.
I tried to change the value format using floatval function and Numberformat.But gives same error.
Please advise.
Thanks in advance.

Is This A Good Question/Topic? 0
  • +

Replies To: exported data to excel is not recognized in formulas

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 799
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: exported data to excel is not recognized in formulas

Posted 24 September 2012 - 02:32 AM

You could try exporting to CSV format instead, that will ensure that numeric datatypes are interpreted as numbers when the file is opened in Excel.
Was This Post Helpful? 0
  • +
  • -

#3 damithaperera88  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 23-September 12

Re: exported data to excel is not recognized in formulas

Posted 24 September 2012 - 03:35 AM

Hi all,
Then i have to code it again.
if anyone can tell me what is the wrong with the code?.
Thanks in advance
Was This Post Helpful? 0
  • +
  • -

#4 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,994
  • Joined: 08-June 10

Re: exported data to excel is not recognized in formulas

Posted 24 September 2012 - 04:16 AM

That code seems to be constructing a XLS file by packing values into a specific binary format. If anybody can tell what is wrong with it, it would be the one who originally wrote it; the one who knows how that binary format is supposed to look. - That would be you, right?

Note that CSV files are a lot simpler than XLS files, so if you don't actually understand the code you posted (if, say, you copied it from somewhere without really understanding how it works), then you will have a much easier time trying to create a CSV equivalent than trying to fix that XLS code.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1