[SOLVED] Converting a number to text

[johng]

I am working on a page that exports information to excel, and I am putting a number in one of the fields.  The problem with this is that it is a 16 digit number, so when it goes into excel it says 4.12345E+15.  Still not a big deal, I just have to reformat the cell.  The problem comes because it cuts off the last digit of the number and replaces it with a zero.  For example:  1234567890123456 becomes 1.23456E+15, and if I change the format, it becomes 1234567890123450.  This is quite a problem, obviously, because it is an account number, and it turns into a different account number when it does this.  I was wondering if anyone knew how to convert this number so it would go into the Excel table as text, or any other suggestions that would have it stay the full length. 

 

I have tried several things, including:  an apostrophe ['] in front, a space in front or behind (neither of which appeared in the excel sheet), and the   (which just showed up as that in the excel sheet, no matter how I put it in my code). 

 

Any suggestions or help would be much appreciated.  Thanks!

 

-John

[Psycho]

How are you putting data into the excel spreadsheet? Are you using a specific class or tool? If so, you will need to investigate that tools code. Is saving the data to a CSV format an option?

[johng]

I am basically using the code found in the tutorial at http://www.phpfreaks.com/tutorials/114/0.php  And it is more of a report for other people, so a CSV won't really work well. 

[Psycho]

Well, a CSV is normally opened in Excel just like an xls document. But, no matter, a quick test shows that the same problem exists when opening a csv with very parge numbers in excel. Basically is converts to scientific notation and loses some precision.

 

Is the report for "display" to the end users or will they actually be manipulating the data once received? If they will only be looking at the report and will not be manipulating the data, an HTML format might be an option.

 

I found this page: http://exceltips.vitalnews.com/Pages/T0025_Precision_in_Excel.html which states that Excel only maintains 15 digits of precision. And i have not found a way to create "formatted" excel output from PHP - yet.

[johng]

Thanks for the info!  I will have to check to see what they are wanting to do with it, and if they aren't going to use that information straight from the table, I will probably just end up adding an apostrophe either at the beginning or the end.  It's too bad that Excel has to be like that.  I know that inside of Excel, if you add an apostrophe at the beginning of a field of numbers, it will hold the numbers like they are, without formatting them or showing the apostrophe, but when I put one in and then export to excel, it just shows up in the box too. Anyway, thanks for the help, and I'll check to see how they will use this table. 

[Psycho]

One thought I just had. Are you creating a new Excel file each time and populating it with data or are you using a template Excel file? You might try creating a template Excel file and formatting all the data cells as "text" where you will be putting these numbers. Each time you are going to export, copy the template and populate it. It may work. If you try it, post back with yout results.

[Barand]

Can't you just put it in quotes in the csv file

 

1,"abc","1234567890123456","more text",42

[johng]

I'm kind of trying to avoid a CSV file, because just a regular Excel file is easier to work with and look at when it is for someone else.

[Psycho]

It also won't work. Assuming you have Excel installed and it is set up as the default application for csv files, it still experiences the same problem of converting the value to scientific notation and losing precision - with or without the quotes.

 

At least that is what is happening for me with Office 2003. May be different on different versions of Office.

[sasa]

try

file test.csv

'1234567890123456789

[sasa]

ups try

="12345678901234567890",="00098765432109876543210"

[boo_lolly]

settype() may help.

[johng]

Unfortunately, settype() doesn't work.  I think I will probably just have to go with the apostrophe in front of the number.  But if you all want to keep thinking, I am open to other ideas and will try something else if it might work.  Thanks for all of your help.

[Psycho]

So, using a template with the fields pre-formatted as text didn't work?

[johng]

How would I go about inserting it into a premade template?

This is the code that I'm working with to put it into the Excel sheet right now. 

$header1, $header2 and $data are all strings that have text in them, $header1 and $header2 are one line each, and $data can have many lines (it has a line return between the lines) and the lines are tab delimited (they have the "\t" between the fields, separating the columns

 

<?php
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=report.xls");
header("Pragma: no-cache");
header("Expires: 0");
print "$header1\n$header2\n$data";
?>

 

Right now I'm creating a new file, but I don't know how to just insert the data into a template.

[Psycho]

OK, looking at the tutorial you linked to I see that it is just creating CSV data and delivering it directly to the user as an XML file. There is no actual file produced on the server-side.

 

But, I've found a solution!

 

This page ( http://www.phpsimple.net/tutorials/mysql_to_excel/ )  has a tutorial on creating excel files. I noticed that there were two separate function for writing a number vs. writing text. I found that by using the xlsWriteLabel() function to write a very long number resulted in the number being treated as text within excel.

 

But, there is one catch. The value MUST be treated as text within PHP, otherwise it seems the number is converted to scientific notation in PHP before it is ever written to the file.

 

I have included a test script below. Here are the results of my test script as they appear in Excel. I thought it was interesting that the number variable treated as text had more precision. But, I think that is because that is PHP converting to scientific notation. The other two "shorter" results must be Excel converting them to scientific notation. But, you can see that the last example will give you what you want.:

NumberVar as Number: 1.23457E+29
NumberVar as Text:   1.23456789012E+029
TextVar as Number:   1.23457E+29
TextVar as Text:     123456789012345678901234567890

 

 

Test script:

<?php

// Functions for export to excel.
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; 
}


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=orderlist.xls "); 
header("Content-Transfer-Encoding: binary ");

xlsBOF();

$longNumberAsText  = "123456789012345678901234567890";
$longNumberAsNumber = 123456789012345678901234567890;

xlsWriteLabel(0,0,"NumberVar as Number:");
xlsWriteNumber(0,1,$longNumberAsNumber);
xlsWriteLabel(1,0,"NumberVar as Text:");
xlsWriteLabel(1,1,$longNumberAsNumber);
xlsWriteLabel(2,0,"TextVar as Number:");
xlsWriteNumber(2,1,$longNumberAsText);
xlsWriteLabel(3,0,"TextVar as Text:");
xlsWriteLabel(3,1,$longNumberAsText);

xlsEOF();
exit();
?>

[johng]

Is there anything I need to modify to be able to just test it, to see where everything is coming from?

 

Basically what I want to do is just put that code into a site and have it output an excel sheet, then I will look at that sheet and figure out where it is getting each of the pieces of information so I can replace them with the info I need to give it. 

 

I want to do this so you don't have to walk me through what each piece of code actually does.

 

Right now, after just putting it into a new php page, and it is just printing this on the page:

 

NumberVar as Number:>7lÿîøENumberVar as Text:1.2345678901235E+29TextVar as Number:>7lÿîøETextVar as Text:&123456789012345678901234567890

 

I was wondering if you knew what I would need to do to get this functional.  Is there something I'm missing?  maybe an include or something?

 

Thanks for all your help by the way!  You guys (and gals) are all amazing!

[Psycho]

Just look at the tutorial I linked to. You need to be using the functions in that script to write to the spreadsheet, you DO NOT want to write the data in as you see it above. The method you were using was writing data in plain text format. The functions in that script are writing to the file in binary format, which is the correct format for Excel.

[Psycho]

Were you running the entire test script I posted above? When I run it and navigate to the page with my browser I get prompted to download an excel file. And that file contains the values I posted previously. So, I don't see why you are having the output printed to the page.

[johng]

I don't know what happened, but now that I try it again, it spits out an excel file.  Thanks for your help! 

[johng]

One more question.  If I am getting the number from a variable, how would ensure it was stored as as text?

[Psycho]

You know there is probably a way to set the data type of a variable, but I can't seem to find anything at the moment, and I feel kind of lazy. This might work though:

 

Add a space to the variable so that it must be text and then use trim to remove the space.

 

$value = trim($value . " ");

[Barand]

if you mean within PHP

 

$number = 123;     // integer
$str = "$number";   // now a string '123'

[johng]

Unfortunately, neither of those methods worked when I tried them with this function.  I'll keep trying things and update with my results.

[Psycho]

Can you show some code?