Page 1 of 1

Working With Excel Files. (comma Delimited Or Csv) 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: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.

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.

$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" . ',' . "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

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

$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.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.
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. (comma Delimited Or Csv)

#2 ncacace  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 03-February 11

Posted 03 February 2011 - 04:02 PM

View Postapg88, on 21 January 2005 - 02:51 PM, said:

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.

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.

$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" . ',' . "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

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

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



Doesn't your first solution for saving to the server assume that the file already exists on the server? What if you had to create a new file each time before writing to it?
Was This Post Helpful? 0
  • +
  • -

#3 ncacace  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 03-February 11

Posted 03 February 2011 - 04:10 PM

$fp = fopen($filename,"a"); // 'a' - opens or creates file if doesn't exist
-- Sorry, my bad.
Was This Post Helpful? 0
  • +
  • -

#4 PHPGangsta  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 2
  • Joined: 23-April 11

Posted 23 April 2011 - 03:21 AM

What happens if your $name or $location contains a comma? You get an invalid CSV file. There is a function exactly for that purpose: fputcsv()

http://de3.php.net/m...ion.fputcsv.php

It automatically escapes and surrounds the fields correctly.

If you need real .xls files, with cells that can have colors, merged cells, formulas and so on, you should use Spreadsheet_Excel_Writer or PHPExcel

http://pear.php.net/...riter.intro.php
http://phpexcel.codeplex.com/

___________________________________
My Blog: http://www.phpgangsta.de
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1