Introduction to Excel :
Daniel Bricklin and Bob Frankston created the first spreadsheet application, VisiCalc (for “visible calculator”). Lotus 1-2-3 came next, followed by Microsoft Excel. While Lotus 1-2-3 was the first to introduce cell names and macros, Microsoft Excel implemented a graphical user interface and the ability to point and click using a mouse. There are many other spreadsheet applications on the market today; however, Lotus 1-2-3 and Microsoft Excel continue to be the most popular.
About Spreadsheet Application :
A spreadsheet is an interactive computer application program for organization, analysis and storage of data in tabular form. Spreadsheets developed as computerized simulations of paper accounting worksheets. The program operates on data represented as cells of an array, organized in rows and columns. Each cell of the array is a model–view–controller element[dubious – discuss] that may contain either numeric or text data, or the results of formulas that automatically calculate and display a value based on the contents of other cells.
Spreadsheet users may adjust any stored value and observe the effects on calculated values. This makes the spreadsheet useful for “what-if” analysis since many cases can be rapidly investigated without manual recalculation. Modern spreadsheet software can have multiple interacting sheets, and can display data either as text and numerals, or in graphical form.
Here is the PHP Sample Code to Export MySQL Data into Excel / Spreadsheet File :
<?php
/*******EDIT LINES 3-8*******/
$DB_Server = "localhost"; //MySQL Server
$DB_Username = "username"; //MySQL Username
$DB_Password = "password"; //MySQL Password
$DB_DBName = "databasename"; //MySQL Database Name
$DB_TBLName = "tablename"; //MySQL Table Name
$filename = "excelfilename"; //File Name
/*******YOU DO NOT NEED TO EDIT ANYTHING BELOW THIS LINE*******/
//create MySQL connection
$sql = "Select * from $DB_TBLName";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());
//select database
$Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());
//execute query
$result = @mysql_query($sql,$Connect) or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());
$file_ending = "xls";
//header info for browser
header("Content-Type: application/xls");
header("Content-Disposition: attachment; filename=$filename.xls");
header("Pragma: no-cache");
header("Expires: 0");
/*******Start of Formatting for Excel*******/
//define separator (defines columns in excel & tabs in word)
$sep = "\t"; //tabbed character
//start of printing column names as names of MySQL fields
for ($i = 0; $i < mysql_num_fields($result); $i++) {
echo mysql_field_name($result,$i) . "\t";
}
print("\n");
//end of printing column names
//start while loop to get data
while($row = mysql_fetch_row($result))
{
$schema_insert = "";
for($j=0; $j<mysql_num_fields($result);$j++)
{
if(!isset($row[$j]))
$schema_insert .= "NULL".$sep;
elseif ($row[$j] != "")
$schema_insert .= "$row[$j]".$sep;
else
$schema_insert .= "".$sep;
}
$schema_insert = str_replace($sep."$", "", $schema_insert);
$schema_insert = preg_replace("/\r\n|\n\r|\n|\r/", " ", $schema_insert);
$schema_insert .= "\t";
print(trim($schema_insert));
print "\n";
}
?>
I’m professional web developer with 6+ years experience. My wide range of knowledge in web development using PHP, jQuery, WordPress, Magento, Symfony, Smarty and Web Scrapping. I am working with strong enthusiastic team (Group of friends) with spirit. So we provide all web development related solutions like HTML/CSS development, Web Graphic design and LOGO.
Leave a Reply