Jump to content




Recent Status Updates

View All Updates

Binpress - Cut your development time and costs in half
Photo
- - - - -

Creating Excel Spreadsheet


  • Please log in to reply
8 replies to this topic

#1 Paradine

Paradine

    CC Regular

  • Just Joined
  • PipPipPip
  • 48 posts

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
  • 0

#2 John

John

    CC Mentor

  • Moderator
  • 4,450 posts
  • Location:New York, NY

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

#3 Guest_Jordan_*

Guest_Jordan_*
  • Guest

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.
  • 0

#4 Ukelo

Ukelo

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

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.
  • 0

#5 xtraze

xtraze

    CC Devotee

  • Just Joined
  • PipPipPipPipPipPip
  • 872 posts

Posted 11 January 2007 - 06:46 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.

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,
  • 0

#6 Guest_Jordan_*

Guest_Jordan_*
  • Guest

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.
  • 0

#7 prakalp

prakalp

    CC Lurker

  • Just Joined
  • Pip
  • 7 posts

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.
  • 0

#8 prakalp

prakalp

    CC Lurker

  • Just Joined
  • Pip
  • 7 posts

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
  • 0

#9 hweima

hweima

    CC Lurker

  • Just Joined
  • Pip
  • 1 posts

Posted 18 May 2010 - 08:48 AM

The code itself seems to work but where am I to find the xls file??

Hanno
  • 0




Powered by binpress