php导出数据至MS OFFICE并生成相应的图表
各位大佬：谁会使用php将mysql中的相关数据库表中的数据，通过前台导出至MS OFFICE并生成相应的图表 额。。。不会 [b] [url=http://www.shenhoulong.net/redirect.php?goto=findpost&pid=35484&ptid=7899]2#[/url] [i]ysuny[/i] [/b]
那老大,前台选择相关条件,导出相关的mysql数据至excel呐? [i=s] 本帖最后由 trashgod 于 2009-2-26 10:15 编辑 [/i]
[quote] 2# ysuny
那老大,前台选择相关条件,导出相关的mysql数据至excel呐?
[size=2][color=#999999]zhgl2884 发表于 2009-2-25 13:56[/color] [url=http://www.shenhoulong.net/redirect.php?goto=findpost&pid=35572&ptid=7899][img]http://www.shenhoulong.net/images/common/back.gif[/img][/url][/size][/quote]
导出到excel倒是不太难的，给你一个范例吧，这是我的一个报名表格写法，前面是一个class类，后面是具体的数据库内容。我觉得比较罗嗦，只是参考了word的文档格式：
<?php
class Excel{
var $header = "<?xml version=\"1.0\" encoding=\"UTF-8\"?\>
<Workbook xmlns=\"urn:schemas-microsoft-com: office:spreadsheet\"
xmlns:x=\"urn:schemas-microsoft-com: office:excel\"
xmlns:ss=\"urn:schemas-microsoft-com: office:spreadsheet\"
xmlns:html=\"http://www.w3.org/TR/REC-html40\">";
var $footer = "</Workbook>";
var $lines = array ();
var $worksheet_title = "Table1";
function addRow ($array) {
// initialize all cells for this row
$cells = "";
// foreach key -> write value into cells
foreach ($array as $k => $v):
// 加个字符串与数字的判断 避免生成的 excel 出现数字以字符串存储的警告
if(is_numeric($v)) {
// 防止首字母为 0 时生成 excel 后 0 丢失
if(substr($v, 0, 1) == 0) {
$cells .= "<Cell><Data ss:Type=\"String\">" . $v . "</Data></Cell>\n";
} else {
$cells .= "<Cell><Data ss:Type=\"Number\">" . $v . "</Data></Cell>\n";
}
} else {
$cells .= "<Cell><Data ss:Type=\"String\">" . $v . "</Data></Cell>\n";
}
endforeach;
// transform $cells content into one row
$this->lines[] = "<Row>\n" . $cells . "</Row>\n";
}
/**
* Add an array to the document
*
* This should be the only method needed to generate an excel
* document.
*
* @access public
* @param array 2-dimensional array
* @todo Can be transfered to __construct() later on
*/
function addArray ($array) {
// run through the array and add them into rows
foreach ($array as $k => $v):
$this->addRow ($v);
endforeach;
}
/**
* Set the worksheet title
*
* Checks the string for not allowed characters (:\/?*),
* cuts it to maximum 31 characters and set the title. Damn
* why are not-allowed chars nowhere to be found? Windows
* help's no help...
*
* @access public
* @param string $title Designed title
*/
function setWorksheetTitle ($title) {
// strip out special chars first
$title = preg_replace ("/[\\\|:|\/|\?|\*|\[|\]]/", "", $title);
// now cut it to the allowed length
$title = substr ($title, 0, 31);
// set title
$this->worksheet_title = $title;
}
/**
* Generate the excel file
*
* Finally generates the excel file and uses the header() function
* to deliver it to the browser.
*
* @access public
* @param string $filename Name of excel file to generate (...xls)
*/
function generateXML ($filename) {
// deliver header (as recommended in php manual)
header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
header("Content-Disposition: inline; filename=\"" . $filename . ".xls\"");
// print out document to the browser
// need to use stripslashes for the damn ">"
echo stripslashes ($this->header);
echo "\n<Worksheet ss:Name=\"" . $this->worksheet_title . "\">\n<Table>\n";
echo "<Column ss:Index=\"1\" ss:AutoFitWidth=\"0\" ss:Width=\"110\"/>\n";
echo implode ("\n", $this->lines);
echo "</Table>\n</Worksheet>\n";
echo $this->footer;
}
function writeXLS ($filename) {
$fdbf = @fopen('register.xls','w'); //先写入到备份文件里面，现在不需要头部文件了
//fwrite($fdbf,'header("Content-Type: application/vnd.ms-excel; charset=UTF-8"');
//fwrite($fdbf,'header("Content-Disposition: inline; filename=register.xls")');
fwrite($fdbf,stripslashes($this->header));
fwrite($fdbf,"\n<Worksheet ss:Name=\"" . $this->worksheet_title . "\">\n<Table>\n");
fwrite($fdbf,"<Column ss:Index=\"1\" ss:AutoFitWidth=\"0\" ss:Width=\"110\"/>\n");
fwrite($fdbf,implode ("\n", $this->lines));
fwrite($fdbf,"</Table>\n</Worksheet>\n");
fwrite($fdbf,$this->footer);
fclose($fdbf); //新文件写入完毕
}
}
?>
<?php
//[color=Red]开始具体数据[/color]
require_once("../../dt-config.php");
require_once("../../inc/class.database.php");
session_start();
$xls = new Excel;
$titlerow=array('课程名称','主题','地点','姓名','职位','联系地址','发票抬头','联系电话','电子邮箱','专业背景','兴趣方向','遇到的研究问题','报名时间');
$xls->addrow($titlerow);
global $db,$params,$request;
$sql = "SELECT coursename,topic,city,username,position,address,receipt,phone,email,background,interest,problem,addtime FROM `dt_register` order by coursename asc,topic asc,addtime desc";
$myresult = $db->get_results($sql);
if(!empty($myresult)){
foreach($myresult as $o)
{
$xls->addrow($o);
}
}
$xls->writeXLS ("mytest");
echo '已生成最新的培训在线报名表格，请使用Excel打开编辑!<br><br><a href="register.xls">下载培训报名表格</a>';
}
?> 顶..不错.. 如果是ASP版本的，比较简单一些，直接操作xsl文件，语法和access mdb数据库一模一样，但是第一行的标题也被当成了数据。还有删除数据好像比较麻烦 [b] [url=http://www.shenhoulong.net/redirect.php?goto=findpost&pid=35629&ptid=7899]4#[/url] [i]trashgod[/i] [/b]
谢谢！
我去试试！ :lol支持！！ 支持下，学习了！
页:
[1]