Page 1 of 1

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

#1 apg88  Icon 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	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.
$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.

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

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;

or
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

Is This A Good Question/Topic? 0
  • +

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

#2 LAubergiste  Icon 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