Page 1 of 1

Working With Excel Files. (tab-delimited) Rate Topic: -----

#1 apg88   User is offline

  • New D.I.C Head
  • member icon

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 15-December 04

Posted 21 January 2005 - 02:52 PM

Working with Excel files. (Tab-Delimited) by apg88

Here 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.

Name	Email	Age	Location
Chris	[email protected]	23	Miami
John	[email protected]	17	Washington DC
Kim	[email protected]	22	Texas
Albert	[email protected]	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.
$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
I usually name all my columns in the first row, so that they will be at the top in the excel file.

$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

$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.

$fp = fopen($filename,"a"); // $fp is now the file pointer to file $filename
	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

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.

echo $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!

Is This A Good Question/Topic? 0
  • +

Replies To: Working With Excel Files. (tab-delimited)

#2 LAubergiste   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 11-November 08

Posted 11 November 2008 - 02:13 PM

Thank you for this example. How do you add new lines in existing tab delimited file without erasing the existing data?
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1