Dream.In.Code > Working With Excel Files. (comma Delimited Or Csv)

apg88

21 Jan, 2005 - 02:51 PM

Working with Excel files. (Comma Delimited or CSV) by apg88

Here is how to work with Excel files and PHP.

First you will need to understand what CSV means. CSV stands for "Comma Separated Values." Whenever you save a file in Excel, it is saved in binary form and can’t be opened by other programs as if it was a text file. Creating the binary file for excel in PHP is very complicated, if even possible. Here is where CSV
comes in. CSV excel files are actually text files with an .csv file name. In this type of file is set up this way.
For example a simple table with name, email, age, and location of a person.

CODE

Name,Email,Age,Location
Chris,csuppa@yahoo.com,23,Miami
John,john@mail.com,17,Washington DC
Kim,butterfy321@aol.com,22,Texas
Albert,ae@prag.net,30,Germany

That is an example of what the CSV excel file will look like if you open it with notepad.
Every column is separated by a comma. When you jump down to the next line is called a carriage return or cr. This way excel knows to go to the next column in excel whenever it sees a comma and to go the next line when it sees a cr.

So, how do I use this information in PHP?

First of all, you have two options, to create an excel file and save it on the server or to create an excel file dynamically for the user to download but not stay on the server.

1 - To save the file on the server

What I always do first is define the carriage return.

CODE

$cr = "\n";

What I like to do is put all the information on a single variable, and at the end save it to the
file.
I usually name all my columns in the first row, so that they will be at the top in the excel file.

CODE

$data = "Name" . ',' . "Email" . ',' . "Age" . ',' . "Location" . $cr;

Later we add the second row. Notice the ".=" instead of just "=" . This tells PHP to add the following information to the variable, not overwrite it. It is the same as saying $data = $data . something

CODE

$data .= $name . ',' . $email . ',' . $age . ',' . $location . $cr;

I put the variables $name, $email, $age, and $location so you understand how to use variables in the file.

After your variable has all the information needed to save the file and finish, we save it to the file.
Here is a simple way to do this.

CODE

$fp = fopen($filename,"a"); // $fp is now the file pointer to file $filename
if($fp){
    fwrite($fp,$data);    //    Write information to the file
    fclose($fp); //    Close the file
    echo "File saved successfully";
} else {
    echo "Error saving file!";
}

$filename is the variable that contains the filename.

NOTE: You will need read/write permissions on the folder you plan to save the file on.

2 - To create the file for the user to download.
First of all, you will need to put this at the very top of the code. Right after the <?php

CODE

header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=yourfilename.csv");
header("Pragma: no-cache");
header("Expires: 0");

You should change yourfilename to whatever file name you want.

After that do the same as if you were creating the file in section 1, but instead of doing all of the saving the file to the server, you will do this.

CODE

echo $data;

CODE

print($data);

whichever you prefer.

Whenever you access this php file you will be prompted if you want to save the file, and that's it!

www.apg88.com