转发：：：一个简单的写excel文件的类

xhq6632 · 发表于 2008-3-26 19:26:53

转发：：：一个简单的写excel文件的类

很多web页面有要求将数据保存为excel格式，以满足那些异常喜爱excel的人，老实说，excel确实是很优秀的产品。
这里的这个类，采用的方法是写xml文件，从office 2003后，office系列增强了对xml文件格式的读写能力，对于简单的excel文件，我们可以简单的写入csv格式文件。稍微复杂的，那就采用表格的方式吧，比如下面的这个类

PHP复制代码

<?php

/*
* 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 ='
<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns:x="urn:schemas-microsoft-comffice:excel"
xmlns="<a href="http://www.w3.org/TR/REC-html40">" target="_blank">http://www.w3.org/TR/REC-h...<;/a>

<head>
<meta http-equiv=Content-Type c>
<meta name=ProgId content=Excel.Sheet>

<style>

</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>srirmam</x:Name>
<x:WorksheetOptions>
<x:Selected/>
<xrotectContents>False</xrotectContents>
<xrotectObjects>False</xrotectObjects>
<xrotectScenarios>False</xrotectScenarios>
</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

rotectStructure>False</x

rotectStructure>
<x

rotectWindows>False</x

rotectWindows>
</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;">';
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>\n");
echo "col is $col
";
}
fwrite($this->fp,"</tr>\n");
}
/*
* @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>\n");
}
function writetable($strtb)
{
if($this->state!="OPENED")
{
$this->error="Error : Please open the file.";
return false;
}
fwrite($this->fp,$strtb);
}
}
?>

复制代码

之前的类并不支持中文，修改后已经支持中文了，只是记得把文件保存为utf8格式。最后面的函数wirtetable我增加的，l你可以直接将某一个表格写入excel文件，下面是演示代码

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("你好","

andit","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);
/*
uncomment the follow code if you wanna write a hole table
*/
/*
$excel->writetable('<table x:str border=0 cellpadding=0 cellspacing=0 style="border-collapse: collapse;table-layout:fixed;"><tr><td class=xl24 width=64 >Name</td>
<td class=xl24 width=64 >Last Name</td>
<td class=xl24 width=64 >Address</td>
<td class=xl24 width=64 >Age</td>
</tr>
<tr><td class=xl24 width=64 >你好</td>
<td class=xl24 width=64 >

andit</td>
<td class=xl24 width=64 colspan="2" >23 mayur vihar</td>
</tr>
</tr></table>');*/
$excel->close();
echo "data is write into myXls.xls Successfully.";

?>

复制代码

[ 本帖最后由 xhq6632 于 2008-3-26 19:31 编辑 ]

Hex · 发表于 2008-3-27 09:30:13

感谢楼主发了这么多好代码！

xhq6632 · 发表于 2008-3-27 20:47:54

不客气，大家一起努力，才能把社区做好哈！！共同进步嘛

sam · 发表于 2008-3-29 18:27:07

好东西。。。。。辛苦了。:)

visvoy · 发表于 2010-11-8 13:27:48

赞啊，正好要给客户导出excel，收了

lamtin · 发表于 2010-11-8 13:29:02

确实是好东西！

hangyi03 · 发表于 2010-12-9 11:31:43

写出来的格式用EXCEL读入时候格式说不对。。。读出来有乱码啊。。。

hangyi03 · 发表于 2010-12-9 11:35:31

回复 2# Hex

HEX大大如何讲文件保存为UTF8格式啊。。。

hangyi03 · 发表于 2010-12-9 14:38:42

回复 3# xhq6632

用EXCEL打开乱码哦我都开车UTF8了也乱码。。。

Hex · 发表于 2010-12-9 15:31:06

回复 8# hangyi03

你的字符串是 utf-8 的，你保存后就是 utf-8 的，和保存无关。说白了就是什么种子出什么果实，和土地无关。

		自动登录	找回密码
密码			入住 CI 中国社区