Convert to Excel Spreadsheet

[Mutley]

I have a database I need to convert to an Excel spreadsheet.

 

Would it be easier for the PHP to read the database and convert it straight away, or display it first in HTML/XML which you then convert?

 

What's the best way of doing this?

[Mark Baker]

You'd need a third party library to do either.

 

However, HTML to Excel or even XML to Excel requires a somewhat more specialised library, and I'm not familiar with any tools that would do this.

If you read from your database using PHP, you can use that script to create a workbook in memory and then write that out to an Excel file: fairly straightforward and simple to do.

[richardw]

You will need a third party mudule. Pear is on most installs, so first you will have to track down ot's location. Specifically the "Speadsheet/Excel/Writer.php" script.

 

That foundation will enable you to create an Excel file, complete with formatting and headers

 

here is my example for a mailing list download:

 

<?php
// Include PEAR::Spreadsheet_Excel_Writer
set_include_path('../PEAR/'); 
require_once("Spreadsheet/Excel/Writer.php");

// Create an instance
$xls =& new Spreadsheet_Excel_Writer();
$today = date("Ymd"); 

// Send HTTP headers to tell the browser what's coming
$xls->send("filename_here".$today.".xls");

// Add a worksheet to the file, returning an object to add data to
$sheet =& $xls->addWorksheet('Mailing List Download');


$titleText = '   Mailing List generated: ' . date('dS M Y'); 

// Create a format object 
$titleFormat =& $xls->addFormat(); 

// Set the font family - Helvetica works for OpenOffice calc too... 
$titleFormat->setFontFamily('Helvetica'); 

// Set the text to bold 
$titleFormat->setBold(); 

// Set the text size 
$titleFormat->setSize('15'); 

// Set the text color 
$titleFormat->setColor('navy'); 

// Set the bottom border width to "thick" 
$titleFormat->setBottom(2); 

// Set the color of the bottom border 
$titleFormat->setBottomColor('navy'); 

// Set the alignment to the special merge value 
$titleFormat->setAlign('left'); 

// Add the title to the top left cell of the worksheet, 
// passing it the title string and the format object 
$sheet->write(0,0,$titleText,$titleFormat); 

// Some text to use as a title for the worksheet 
// $titleText = 'List Created:' . date('dS M Y'); 


// $sheet->write(0,1,$titleText); 
// Set up some formatting 
$colHeadingFormat =& $xls->addFormat(); 
$colHeadingFormat->setBold(); 
$colHeadingFormat->setFontFamily('Helvetica'); 
$colHeadingFormat->setBold(); 
$colHeadingFormat->setSize('10'); 
$colHeadingFormat->setAlign('center'); 

// An array with the data for the column headings 

// ****************  *******************************
  		//   $colNames = array('Item','Price($)','Quantity','Total'); 
	 //  $sheet->writeRow(2,0,$colNames,$colHeadingFormat);
    // ****************  *******************************
  

							$str = "HEADING HERE"; //"$row["fname"];
							$arr = explode (",", $str);


$colNames = array('ID','Salutation','First Name','Middle Name','Last Name','last_index','first_index', 'Address','Address2','City','Organization','Oranization Type','role','Department','E-Mail','email_id','Phone','Title','State ID','Zip','Referral Source ID','Reffered By','OtherSource','created', 'modified', 'mail_type', 'sent_on');

	  $sheet->writeRow(2,0,$arr,$colHeadingFormat);
	  $sheet->writeRow(5,0,$colNames,$colHeadingFormat);

// Get data records from table. 

          
// leaving a blank row to look nicer 

// The row height 

// The cell group to freeze 
// 1st Argument - vertical split position 
// 2st Argument - horizontal split position (0 = no horizontal split) 
// 3st Argument - topmost visible row below the vertical split 
// 4th Argument - leftmost visible column after the horizontal split 
$freeze = array(6,0,7,0); 

// Freeze those cells! 
$sheet->freezePanes($freeze);

// Use this to keep track of the current row number 
$currentRow = 8; 

// $sheet->setRow(0,30); 

// Set the column width for the first 4 columns 
$sheet->setColumn(0,3,15);


// Write some numbers
/*
for ( $i=0;$i<11;$i++ ) {

// Use PHP's decbin() function to convert integer to binary
$sheet->write($i,0,decbin($i));
}
*/
// Connect database. 
		// mysql_connect("");
	// mysql_select_db("");
$host="localhost"; // Host name 
$username_s =""; // Mysql username 
$password_s =""; // Mysql password 
$db_name=""; 
mysql_connect("$host", "$username_s", "$password_s")or die("cannot connect"); 
mysql_select_db("$db_name")or die("cannot select DB");


// Get data records from table. 
$sql = "SELECT * FROM contacts";
$result =mysql_query($sql);


		// Create an array of arrays out of the recordset. 
			while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
				    $sheet->writeRow($currentRow,0,$row); 
						$currentRow++; 
		}


// Finish the spreadsheet, dumping it to the browser
$xls->close();


?>

 

For more detail google this module

 

/*
*  Module written/ported by Xavier Noguer <[email protected]>
*
*  PERL Spreadsheet::WriteExcel module.
*


require_once 'PEAR.php';
require_once 'Spreadsheet/Excel/Writer/Workbook.php';

/**
* Class for writing Excel Spreadsheets. This class should change COMPLETELY.
*
* @author   Xavier Noguer <[email protected]>
* @category FileFormats
* @package  Spreadsheet_Excel_Writer
*/

[Mark Baker]

Note that the PEAR Excel Writer script hasn't been supported for sometime now ( 2 1/2 years). The only pure PHP Excel reader/writer that is still supported and under ongoing development is listed in my sig

[kodstationen]

Can't you just output the tables as a comma-separated list and import it as a csv file into Excell?

 

Something like:

 

header("Content-Type: text/csv");
header("Content-Disposition: attachment; filename=\"test.csv\"");
header('Vary: User-Agent');
header("Cache-Control: cache, must-revalidate");
header('Pragma: public');

$r = mysql_query("select * from table");
while ($row = mysql_fetch_array($r)) {
    echo '"'.implode('","',$row).'"\n";
}

(for the purpse of simplicity I don't care about good coding practices here...)

[jackofalltrades]

There is functionality is built into Excel 2007.

 

Go to the DATA tab, click on the From Other Sources icon, choose From SQL Server.

 

I dont know if it is MS SQL only or can also do MySQL?