Jump to content

[SOLVED] Export Data into Excel Spreadsheet


dennismonsewicz

Recommended Posts

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

Link to comment
Share on other sites

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.";
?>

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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]

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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');

Link to comment
Share on other sites

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;
?>

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.