Register and join over 40,000 other developers!
Recent Topics
-
Print specific values from dictionary with a specific key name
Siten0308 - Jun 20 2019 01:43 PM
-
Learn algorithms and programming concepts
johnnylo - Apr 23 2019 07:49 AM
-
Job Gig PHP Form Needed
PJohnson - Apr 18 2019 03:55 AM
-
How to make code run differently depending on the platform it is running on?
xarzu - Apr 05 2019 09:17 AM
-
How do I set a breakpoint in an attached process in visual studio
xarzu - Apr 04 2019 11:47 AM
Recent Blog Entries
Recent Status Updates
Popular Tags
- networking
- Managed C++
- stream
- console
- database
- authentication
- Visual Basic 4 / 5 / 6
- session
- Connection
- asp.net
- import
- syntax
- hardware
- html5
- array
- mysql
- java
- php
- c++
- string
- C#
- html
- loop
- timer
- jquery
- ajax
- javascript
- programming
- android
- css
- assembly
- c
- form
- vb.net
- xml
- linked list
- login
- encryption
- pseudocode
- calculator
- sql
- python
- setup
- help
- game
- combobox
- binary
- hello world
- grid
- innerHTML

Creating Excel Spreadsheet
Started by Paradine, Jan 10 2007 06:12 AM
8 replies to this topic
#1
Posted 10 January 2007 - 06:12 AM
Using PHP to parse data how can I then convert that data into an Excel Spreadsheet file?
EX:
I retrieve A B C
and then next row I get 1 2 3
How can I place that in a spreadsheet looking like this:
A B C
1
2
3
EX:
I retrieve A B C
and then next row I get 1 2 3
How can I place that in a spreadsheet looking like this:
A B C
1
2
3
#2
Posted 10 January 2007 - 10:31 AM
I dont believe there is an easy way to do it, I found this code over at phpclasses.org that seems to do what you want
excelwriter.inc.php
excel_example.php
ref:
PHP Classes - Class: Excel Writer
excelwriter.inc.php
<?php
/*
###############################################
#### ####
#### Author : Harish Chauhan ####
#### Date : 31 Dec,2004 ####
#### Updated: ####
#### ####
###############################################
*/
/*
* Class is used for save the data into microsoft excel format.
* It takes data into array or you can write data column vise.
*/
Class ExcelWriter
{
var $fp=null;
var $error;
var $state="CLOSED";
var $newRow=false;
/*
* @Params : $file : file name of excel file to be created.
* @Return : On Success Valid File Pointer to file
* On Failure return false
*/
function ExcelWriter($file="")
{
return $this->open($file);
}
/*
* @Params : $file : file name of excel file to be created.
* if you are using file name with directory i.e. test/myFile.xls
* then the directory must be existed on the system and have permissioned properly
* to write the file.
* @Return : On Success Valid File Pointer to file
* On Failure return false
*/
function open($file)
{
if($this->state!="CLOSED")
{
$this->error="Error : Another file is opend .Close it to save the file";
return false;
}
if(!empty($file))
{
$this->fp=@fopen($file,"w+");
}
else
{
$this->error="Usage : New ExcelWriter('fileName')";
return false;
}
if($this->fp==false)
{
$this->error="Error: Unable to open/create File.You may not have permmsion to write the file.";
return false;
}
$this->state="OPENED";
fwrite($this->fp,$this->GetHeader());
return $this->fp;
}
function close()
{
if($this->state!="OPENED")
{
$this->error="Error : Please open the file.";
return false;
}
if($this->newRow)
{
fwrite($this->fp,"</tr>");
$this->newRow=false;
}
fwrite($this->fp,$this->GetFooter());
fclose($this->fp);
$this->state="CLOSED";
return ;
}
/* @Params : Void
* @return : Void
* This function write the header of Excel file.
*/
function GetHeader()
{
$header = <<<EOH
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=ProgId content=Excel.Sheet>
<!--[if gte mso 9]><xml>
<o:DocumentProperties>
<o:LastAuthor>Sriram</o:LastAuthor>
<o:LastSaved>2005-01-02T07:46:23Z</o:LastSaved>
<o:Version>10.2625</o:Version>
</o:DocumentProperties>
<o:OfficeDocumentSettings>
<o:DownloadComponents/>
</o:OfficeDocumentSettings>
</xml><![endif]-->
<style>
<!--table
{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
@page
{margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;}
tr
{mso-height-source:auto;}
col
{mso-width-source:auto;}
br
{mso-data-placement:same-cell;}
.style0
{mso-number-format:General;
text-align:general;
vertical-align:bottom;
white-space:nowrap;
mso-rotate:0;
mso-background-source:auto;
mso-pattern:auto;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:0;
border:none;
mso-protection:locked visible;
mso-style-name:Normal;
mso-style-id:0;}
td
{mso-style-parent:style0;
padding-top:1px;
padding-right:1px;
padding-left:1px;
mso-ignore:padding;
color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:0;
mso-number-format:General;
text-align:general;
vertical-align:bottom;
border:none;
mso-background-source:auto;
mso-pattern:auto;
mso-protection:locked visible;
white-space:nowrap;
mso-rotate:0;}
.xl24
{mso-style-parent:style0;
white-space:normal;}
-->
</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>srirmam</x:Name>
<x:WorksheetOptions>
<x:Selected/>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>10005</x:WindowHeight>
<x:WindowWidth>10005</x:WindowWidth>
<x:WindowTopX>120</x:WindowTopX>
<x:WindowTopY>135</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head>
<body link=blue vlink=purple>
<table x:str border=0 cellpadding=0 cellspacing=0 style='border-collapse: collapse;table-layout:fixed;'>
EOH;
return $header;
}
function GetFooter()
{
return "</table></body></html>";
}
/*
* @Params : $line_arr: An valid array
* @Return : Void
*/
function writeLine($line_arr)
{
if($this->state!="OPENED")
{
$this->error="Error : Please open the file.";
return false;
}
if(!is_array($line_arr))
{
$this->error="Error : Argument is not valid. Supply an valid Array.";
return false;
}
fwrite($this->fp,"<tr>");
foreach($line_arr as $col)
fwrite($this->fp,"<td class=xl24 width=64 >$col</td>");
fwrite($this->fp,"</tr>");
}
/*
* @Params : Void
* @Return : Void
*/
function writeRow()
{
if($this->state!="OPENED")
{
$this->error="Error : Please open the file.";
return false;
}
if($this->newRow==false)
fwrite($this->fp,"<tr>");
else
fwrite($this->fp,"</tr><tr>");
$this->newRow=true;
}
/*
* @Params : $value : Coloumn Value
* @Return : Void
*/
function writeCol($value)
{
if($this->state!="OPENED")
{
$this->error="Error : Please open the file.";
return false;
}
fwrite($this->fp,"<td class=xl24 width=64 >$value</td>");
}
}
?>
excel_example.php
<?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.";
?>
ref:
PHP Classes - Class: Excel Writer
#3
Guest_Jordan_*
Posted 10 January 2007 - 03:04 PM
Nice code. Alternatively, you can just write a text file that is comma (,) delimited. Use .xls as the extension and it loads fine in excel. Use line breaks for new rows.
#4
Posted 11 January 2007 - 05:47 AM
You might want to check the PEAR libraries (h t t p : / / pear.php.net). The "Spreadsheet_Excel_Writer" is a really comprehensive package to generate native Excel files.
#5
Posted 11 January 2007 - 06:46 PM
CSV as Jordan has mentioned is far easier than any method lol, but only for small files and can be used for BIGGER ones too but hard to understand if you open with Notepad,Nice code. Alternatively, you can just write a text file that is comma (,) delimited. Use .xls as the extension and it loads fine in excel. Use line breaks for new rows.
#6
Guest_Jordan_*
Posted 15 January 2007 - 06:09 AM
CSV as Jordan has mentioned is far easier than any method lol, but only for small files and can be used for BIGGER ones too but hard to understand if you open with Notepad,
Very hard, and to format properly you need to use commas which can get confusing.
#7
Posted 18 January 2007 - 06:32 AM
How interesting that this conversation is taking place. I have been searching without success for information regarding this exact topic.
The fact remains that there are a number of classes written to handle data to and from the xls format.
Jordan : Will not the csv format limit itself to single page spreadsheets?
However, what I have been looking for is a component \ module or a php app that allows a user to input data within a GUI (primarily in the JOOMLA! framework) and produces complex spreadsheets. Alternatively, takes xls spreadsheets, writes to a db (mySQL) and presents the data to the user within the JOOMLA! framework.
Any ideas or comments?
Regards,
Prakalp.
The fact remains that there are a number of classes written to handle data to and from the xls format.
Jordan : Will not the csv format limit itself to single page spreadsheets?
However, what I have been looking for is a component \ module or a php app that allows a user to input data within a GUI (primarily in the JOOMLA! framework) and produces complex spreadsheets. Alternatively, takes xls spreadsheets, writes to a db (mySQL) and presents the data to the user within the JOOMLA! framework.
Any ideas or comments?
Regards,
Prakalp.
#8
Posted 18 January 2007 - 06:45 AM
Irrelevant to this particular conversation, however, interesting anyways, is this particular site.
ajaxXLS online based spreadsheet viewer is Live. - Keith Dsouza's Home
ajaxXLS online based spreadsheet viewer is Live. - Keith Dsouza's Home
#9
Posted 18 May 2010 - 08:48 AM
The code itself seems to work but where am I to find the xls file??
Hanno
Hanno
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download