[SOLVED] Export Data into Excel Spreadsheet

[dennismonsewicz]

Does anyone know how I can do a PHP export from a website into an excel spreadsheet?

 

Example:

 

My company intranet has a ton of data that is loaded into MySQL and one of the users of the intranet wants an export to excel function. So if they are looking at a table of data they can click on export to excel and it does it.

 

Anyone know where to start?

 

-Dennis

[rhodesa]

I use this one a lot:

http://www.phpclasses.org/browse/package/2037.html

[laffin]

simplest way, is to use a format supported by both systems.

 

Excel also supports CSV Files.

 

if yer database dun have any binary stuff

ya can easily export yer mysql db into a csv file.

 

 

fputcsv function

[dennismonsewicz]

Rhodesa,

 

So in the below code I change the values of the $myArr variable array with the values of my header names from my DB table?

 

<?php

    include("excelwriter.inc.php");
    
    $excel=new ExcelWriter("myXls.xls");
    
    if($excel==false)    
        echo $excel->error;
        
    $myArr=array("Name","Last Name","Address","Age");
    $excel->writeLine($myArr);

    $myArr=array("Sriram","Pandit","23 mayur vihar",24);
    $excel->writeLine($myArr);
    
    $excel->writeRow();
    $excel->writeCol("Manoj");
    $excel->writeCol("Tiwari");
    $excel->writeCol("80 Preet Vihar");
    $excel->writeCol(24);
    
    $excel->writeRow();
    $excel->writeCol("Harish");
    $excel->writeCol("Chauhan");
    $excel->writeCol("115 Shyam Park Main");
    $excel->writeCol(22);

    $myArr=array("Tapan","Chauhan","1st Floor Vasundhra",25);
    $excel->writeLine($myArr);
    
    $excel->close();
    echo "data is write into myXls.xls Successfully.";
?>

[rhodesa]

Yeah...i usually use the $excel->writeLine($myArr); method. Look at the documentation, cus you can you have it dynamically generate the excel file and then send it to the browser for download instead of saving the file locally on the server.

[dennismonsewicz]

Yeah I actually need for this to be dynamic. I am trying to locate the documentation now, and can't seem to find out how to do that?

[rhodesa]

I just downloaded the copy from phpclasses and my version is a little different. I don't remember if I modified it or not, but it's attached here. (Note I renamed it to .txt so i could upload it.)

 

Usage:

 

<?php

    include("excelWriter.inc.php");
    $excel=new ExcelWriter();
        
    $myArr=array("Name","Last Name","Address","Age");
    $excel->writeLine($myArr);

    $myArr=array("Sriram","Pandit","23 mayur vihar",24);
    $excel->writeLine($myArr);
   
    $myArr=array("Tapan","Chauhan","1st Floor Vasundhra",25);
    $excel->writeLine($myArr);
    
    $excel->send('filename'); //Automatically adds .xls
?>

 

[attachment deleted by admin]

[dennismonsewicz]

Rhodesa,

 

is there a way to make the array's dynamic? I see where you can manually add them but how would I make them dynamic? Making them dynamic would probably be a lot easier to manage.

 

-Dennis

[rhodesa]

well...from a DB you would just do:

 

<?php
  include("db.php"); //Connect to MySQL DB
  include("excelWriter.inc.php");
  $excel=new ExcelWriter();

  //Add Column Headers
  $excel->writeLine(array("FIELD 1","FIELD 2","FIELD 3"));

  //Add rows from Database
  $sql = "SELECT `field1`,`field2`,`field3` FROM `table` WHERE `type` = 'test'";
  $result = mysql_query($sql) or die(mysql_error());
  while($row = mysql_fetch_array($result)){
    //Feel free to manipulate the values here

    //Add row to excel
    $excel->writeLine(array($row['field1'],$row['field2'],$row['field3']));
  }
  
  //Send file to browser
  $excel->send('filename'); //Automatically adds .xls
?>

[dennismonsewicz]

ok I am not getting an error but I am getting this problem:

 

It is looping the line of "data is write into hr.xls successfully"

 

There is only one record recorded into the XLS file

 

And the file is not over-writing the previous one.

 

Here is my code:

 

include "../includes/db_login.php";

			include("../excel/excelwriter.inc.php");

				$excel=new ExcelWriter();

				$myArr=array("Id","Product","Company","Description","Web Address","Last Used","Where product was last use","Is product active or inactive?");
				$excel->writeLine($myArr);

                $query = "SELECT * FROM $tablename";
                
                $result = mysql_query( $query );
                
                if(!$result)
                    {
                        die("Could not query the database: <br/>" . mysql_error());
                    }
                
                while($row = mysql_fetch_array($result, MYSQL_ASSOC))
				{
					$id = $row['rowid'];
					$company = $row['company'];
					$product = $row['product'];
					$description = $row['description'];
					$web = $row['web'];
					$last = $row['last'];
					$used = $row['used'];
					$active = $row['active'];

				$myArr=array($id,$product,$company,$description,$web,$last,$used,$active);
				$excel->writeLine($myArr);

				$excel->close();
				echo "data is write into hr.xls Successfully.";

				}
				$excel->send('hr');

[ratcateme]

you need to specify the file it is writing the excel file to

change

$excel=new ExcelWriter();
to 
$excel=new ExcelWriter("myXls.xls");

that should work

 

Scott.

[dennismonsewicz]

the file problem has been fixed, but it keeps looping the same sentence and it is only writing one record into the Excel sheet!

[rhodesa]

Nope...don't do that...the version I posted is different...

 

If you were more careful with your indenting, you would notice that echo is INSIDE the while loop:

  while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    ....
    $myArr=array($id,$product,$company,$description,$web,$last,$used,$active);
    $excel->writeLine($myArr);

    $excel->close();
    echo "data is write into hr.xls Successfully.";
  }

we don't want close() (no longer needed with my updated copy of ExcelWriter) and we don't want the echo, because the send() can be the only thing displaying output. Use this:

<?php
  include "../includes/db_login.php";
  include("../excel/excelwriter.inc.php");

  $excel=new ExcelWriter();

  $myArr=array("Id","Product","Company","Description","Web Address","Last Used","Where product was last use","Is product active or inactive?");
  $excel->writeLine($myArr);

  $query = "SELECT * FROM $tablename";
  $result = mysql_query($query);
  if(!$result){
    die("Could not query the database: <br/>" . mysql_error());
  }
  while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    $id = $row['rowid'];
    $company = $row['company'];
    $product = $row['product'];
    $description = $row['description'];
    $web = $row['web'];
    $last = $row['last'];
    $used = $row['used'];
    $active = $row['active'];
    $myArr=array($id,$product,$company,$description,$web,$last,$used,$active);
    $excel->writeLine($myArr);
  }
  $excel->send('hr');
  exit;
?>

 

[ratcateme]

you need to remove the

$excel->close();

form your while loop because it is closing the file after writing the first loop.

put it after you

}

at the end of the loop

like this:

}

$excel->close();

that should fix it.

 

Scott.

[dennismonsewicz]

ok so I have this slight problem:

 

It worked, but when I try to open the file it physically does not open the file HAHA this is pretty funny.

 

Any ideas on this?

[dennismonsewicz]

Like it opens Excel but cannot open the actual XLS file. This saddens me

[dennismonsewicz]

Anyone have any ideas?

[ratcateme]

what version of excel are you using i used the example script and successfully opened it with excel 2003

i do get a message saying it has been locked for editing but it still opens

 

Scott.

[dennismonsewicz]

I have office 04 (MAC)

 

But I sent it to a co worker who has office 03 (windows) and it still did not open

[ratcateme]

try just running the example script from the website and see if you can open that

if you can then it is your script that is broken

 

Scott.

[dennismonsewicz]

I can run the example script. I am trying to run the modified script from rhodesa. I am going to probably have to revert back to the old one i guess

[rhodesa]

Are you using the updated class that I posted?

 

Here is the link again: http://www.phpfreaks.com/forums/index.php?action=dlattach;topic=180789.0;attach=3752

[dennismonsewicz]

Yep I am using the exact file you uploaded. It opens Excel but cannot open the XLS file spreadsheet itself. I have even tried on another computer and it cannot open the spreadsheet. Any thoughts?

[rhodesa]

Add piece by piece to find your problem. First comment out the

$excel->writeLine($myArr);

inside the while loop and make sure that opens with just the headers

 

then uncomment the above line and start with

$myArr=array($id);

in the while loop, testing and adding each column 1 by 1

 

then, let me know where it breaks

[ratcateme]

i ran the script using the modified script from rhodesa and it worked fine are you sure there isn't a problem with your sql query are you specifying $tablename

 

Scott

.