Working with Excel files. (Tab-Delimited)
by apg88Here is how to work with Excel files and PHP.
First you will need to understand what tab-delimited means. Whenever you save a file in Excel, it is saved in binary form and can’t be opened by other programs like if it was a text file. Creating the binary file for excel in PHP is very complicated, if even possible. Here is where tab-delimited
comes in. Tab-delimited excel files are actually text files with an .xls 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 tab-delimited excel file will look like if you open it with notepad.
Every column is separated by a tab. 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 tab 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 two constants, the tab, and the carriage return.
CODE
$tab = "\t";
$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" . $tab . "Email" . $tab . "Age" . $tab . "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 . $tab . $email . $tab . $age . $tab . $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.xls");
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;
or
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
Working With Excel Files. (tab-delimited)
21 Jan, 2005 - 02:52 PM
| 