1

I want to make a excel file by fetching data from database. Every thing works fine but the phone number field return unknown value in excel sheet but it displays correct value in browser.

<?php
 include('connection.php');
    header( "Content-Type: application/vnd.ms-excel" );
    header( "Content-disposition: attachment; filename=spreadsheet.xls" );
                        $q = mysql_query("select * from members order by id desc");

                        echo 'First Name' . "\t" . 'email' . "\t" . 'Phone' . "\t". 'address' . "\t". 'Membership Number' . "\t". 'Membership Category' . "\n";

                        while($r = mysql_fetch_array($q))
                        {
                            $name = $r['name'];
                            $email = $r['email'];
                            $phone = $r['phone'];
                            $address = $r['address'];
                            $mem_num = $r['mem_num'];
                            $mem_cat = $r['mem_category'];
                        echo "$name"."\t"."$email"."\t"."$phone"."\t"."$address"."\t"."$mem_num"."\t"."$mem_cat"."\n";
                        }
                    mysql_close($con);
?>

Screenshot

Community
  • 1
  • 1
Rahul
  • 5,594
  • 7
  • 38
  • 92
  • I have taken Phone num field as bigInt in database of length 10 – Rahul Jan 21 '15 at 18:28
  • The drawbacks of assuming that a csv file will be read exactly "as is" when loading it in MS Excel..... MS Excel will treat this as a number, and convert to scientific notation if it contains more than a few digits – Mark Baker Jan 21 '15 at 18:51
  • To control the format, either create a proper BIFF format spreadsheet file, where you can define the format that MS Excel uses for display, or save as `="00441723568170"` in your csv file – Mark Baker Jan 21 '15 at 18:52

3 Answers3

2

I believe you are confusing data-types here. A phone number for instance, is not a number. But it's called a number! Yeah I know, because it has a lot of numbers in it, but still, it isn't a number... Why?

A phone number is indeed constructed of a set of numerals - the symbols that represent a number - but that doesn't make it a number yet. Compare letters and words; a word is constructed of a set of letters, but not every set of letters is a word. For example: dfslkwpdjkcnj is not a word, not in a language I know of at least... And if it would be a number, how would you add-up two phone numbers? Or how would you divide a phone number by another one? Would that be something like [grandma's phonenumber] / [mom's phonenumber] = [my phonenumber]?

So, to store a phone number in a database, a varchar would be a more suitable column type. For example international phone numbers start with either a + sign, or double zero (00). Both of them can not be stored in a numeral field type. The + isn't a numeral sign, or is used to designate a positive number, and will be cut off. Leading zero's in a number have no function at all, and will be cut off as well...

So bottom line; in your database, use a varchar to store a phone number, and use conversion functions to format a phone number to your liking. There are almost certainly a dozen of algorithms to be found to format a phone number to some kind of a standardized format.

Then back to your excel: your aren't creating an excel file, but a csv file, and you're giving it an excel mime-type. But that would be the same to give someone a cd and say it is a dvd. If you put the cd in a dvd player, it will almost certainly be able to play it, but it is mere luck then wisdom that it does.

Creating an excel file isn't as easy as setting the mime-type, as you can't expect the browser to know how to convert text to an excel file, as it does not know about excel's internals. So if you reaaally want to create an excel file, and set the data types of certain columns, use a library like phpExcel or any other available, if you don't want to create a library yourself that is.

Community
  • 1
  • 1
giorgio
  • 10,111
  • 2
  • 28
  • 41
1

have you tried expanding(stretching) phone column in your excel file? sometime if column is small and numbers are big, excel displays number like(1.23+09) this.

If stretching column does not work. you can convert numbers into string and then put them in excel file

sorry i can't add this in comment as i don't have privilege to comment yet.

enter image description here

vishal
  • 55
  • 6
  • If the column is too small, then MS Excel will display hashes ('#'), not convert the display to scientific notation – Mark Baker Jan 21 '15 at 18:51
  • @MarkBaker Yes it does add #s also if column is very small. – vishal Jan 21 '15 at 18:56
  • Conversion to scientific format is based on the number of digits that would need to be displayed; even a very wide column will still show a number in scientific format if there are a lot of digits to display – Mark Baker Jan 21 '15 at 19:05
0

If with your API you are able to format cells, that's what you would need to do. You are storing your phone number as a BigInt instead of as a String. I have always stored phone numbers as Strings.

Excel is interpreting your data correctly--as a number, not as text. If you wish to continue to store your phone number as a BigInt (which I don't recommend), you would need to convert it to a String before writing it out to Excel. Or, if your API permits, write it out to Excel as a number, but then apply cell formatting to bring it to the formatting you expect.