Sign In
Create Account
Follow Us on Twitter
Subscribe on Youtube
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
8 replies to this topic
Back to top|
|
|
#2
Posted 10 January 2007 - 10:31 AM
John
-
- Moderators
-
- 6,321 posts
Writes binary right handed and hex left handed
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
Guest_Jordan_*
Guest_Jordan_*
-
- Guests
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
Ukelo
-
- Members
-
- 1 posts
Newbie
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
xtraze
-
- Members
-
- 910 posts
Programming God
Jordan said:
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
Guest_Jordan_*
Guest_Jordan_*
-
- Guests
xtraze said:
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
prakalp
-
- Members
-
- 7 posts
Newbie
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
prakalp
-
- Members
-
- 7 posts
Newbie
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
hweima
-
- Members
-
- 1 posts
Newbie
The code itself seems to work but where am I to find the xls file??
Hanno
Hanno
