本文章收藏了三款php 生成excel文件代码程序,第一款为比较全面的生成函数,后面二款很简单,但是不如第一款好,好了现在来看看生成excel的原理吧。
代码如下 |
复制代码 |
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) {
$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> ";
} else {
$cells .= "<cell><data ss:type="number">" . $v . "</data></cell> ";
}
} else {
$cells .= "<cell><data ss:type="string">" . $v . "</data></cell> ";
}
endforeach;
// transform $cells content into one row
$this->lines[] = "<row> " . $cells . "</row> ";
}
function addarray ($array) {
// run through the array and add them into rows
foreach ($array as $k => $v):
$this->addrow ($v);
endforeach;
}
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;
}
function generatexml ($filename) {
header("content-type: application/vnd.ms-excel; charset=utf-8");
header("content-disposition: inline; filename="" . $filename . ".xls"");
echo strips教程lashes ($this->header);
echo " <worksheet ss:name="" . $this->worksheet_title . ""> <table> ";
echo "<column ss:index="1" ss:autofitwidth="0" ss:width="110"/> ";
echo implode (" ", $this->lines);
echo "</table> </worksheet> ";
echo $this->footer;
}
}
/**
* 非框架使用方法
*
* require_once('excel.php');
* $doc = array (
* 0 => array ('中国', '中国人', '中国人民', '123456");
* );
* $xls = new excel;
* $xls->addarray ( $doc );
* $xls->generatexml ("mytest");
*/
?>
|
方法二
其实在做真正的应用的时候,大家可以将数据从数据库教程中取出,然后按照每一列数据结束后加t,每一行数据结束后加n的方法echo出来,在php的开头用header("content-type:application/vnd.ms-excel");表示输出的是excel文件,用header("content-disposition:filename=test.xls");表示输出的文件名为text.xls。这样就ok了。
代码如下 |
复制代码 |
<?
header("content-type:application/vnd.ms-excel");
header("content-disposition:filename=test.xls");
echo "test1";
echo "test2";
echo "test1";
echo "test2";
echo "test1";
echo "test2";
echo "test1";
echo "test2";
echo "test1";
echo "test2";
echo "test1";
echo "test2";
?>
方法三
<?
header("content-type: application/octet-stream");
header("accept-ranges: bytes");
header("content-type:application/vnd.ms-excel");
header("content-disposition:attachment;filename=export_excel_gshjsl.xls");
$tx='表头';
echo $tx." ";
echo "编号"." ";
echo "姓名"." ";
echo " ";
echo "="411481198507150666""." ";
echo "="0123456""." ";
echo " ";
?>
|
如果要利用了php输出excel格式文件就必须利用header content-type:application/vnd.ms-excel来实现。如下
代码如下 |
复制代码 |
<?php
$filename = name .'.xls';
header("content-type:application/vnd.ms-excel");
header("content-disposition:attachment;filename=$filename");
?> |
再看一php输出excel实例
代码如下 |
复制代码 |
<?php
header("content-type:application/vnd.ms-excel");
header("content-disposition:filename=test.xls");
echo "a1 b1 c1 a2 a3 a4 ";//r 单元格, 新一行
?>
|
代码如下 |
复制代码 |
<?php
require_once("../../config/sys_config.php"); //配置文件
require_once("../../include/db_class.php");
header("content-type: text/html; charset=$page_code"); //页面编码
header("content-type:application/vnd.ms-excel");
header("content-disposition:attachment;filename=".mb_convert_encoding("客户资料报表","gbk",$page_code).".xls");
header("pragma:no-cache");
header("expires:0");
//$usersid = intval( $_get['uid'] ); //用户id
//输出内容如下:
// 输出表头
echo iconv("utf-8", "gb2312", "客户名称")." ";
echo iconv("utf-8", "gb2312", "电话")." ";
echo iconv("utf-8", "gb2312", "地址")." ";
echo iconv("utf-8", "gb2312", "添加日期")." ";
echo " "; //换行
$sqlstr = "select * from clients where usersid=32 order by clientsid desc";
$rows = $db -> select($sqlstr);
$num = count($rows); //客户总数
for( $i = 0; $i < $num; $i++ )
{
echo iconv("utf-8", "gb2312",$rows[$i][clientsname])." ";
echo iconv("utf-8", "gb2312",$rows[$i][clientsphone])." ";
echo iconv("utf-8", "gb2312",$rows[$i][clientsaddress])." ";
echo iconv("utf-8", "gb2312",$rows[$i][clientstime])." ";
echo " "; //换行
}
?>
|
再来一款简单实例
代码如下 |
复制代码 |
header("content-type:application/vnd.ms-excel");
header("content-disposition:attachment;filename=users.xls" );
echo "公司名称"."t";
echo "用户名"."t";
echo "密码"."t";
echo "二级域名"."t";
echo "n";
foreach($result['result'] as $val){
echo "$val->comname"."t";
echo "$val->username"."t";
echo "$val->usertruepw"."t";
echo emptyempty($val->domainname)?'':('http://'.$val->domainname.'.jiaomai.com')."t";
echo "n";
}
|
html代码
代码如下 |
复制代码 |
<!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>ansys教程</title>
</head>
<body>
<img" width=100% src="/info.php教程" width="22" height="30" />
</body>
</html> |
php代码
代码如下 |
复制代码 |
$db = new sqlite3('mysql教程itedb.db');
//获取文件2进制流
$filename = "/www.111cn.net/logo.gif";
$handle = fopen($filename, "r");
$contents = fread($handle, filesize ($filename));
fclose($handle);
//创建数据表
$db->exec('create table person (idnum text,name text,photo blob)');
$stmt = $db->prepare("insert into person values ('41042119720101001x', '张三',?)");
$stmt->bindvalue(1, $contents, sqlite3_blob);
$stmt->execute();
|
<?php
代码如下 |
复制代码 |
$pdo = new sqlite3('mysqlitedb.db');
$results = $pdo->query('select * from person');
while ($row = $results->fetcharray()) {
ob_start();
header("content-type: image/jpg");
echo $row['photo'] ;
ob_end_flush();
}
|
?>
这是一款非常完整理的php连接mysql数据库哦,利用了php与mysql数据库进行连接哦,好了费话不说多了来看看这款经典的连接数据库代码是不是你想要找的吧。
代码如下 |
复制代码 |
class mysql {
var $linkid=null;
function __construct($dbhost, $dbuser, $dbpw, $dbname = '', $dbcharset = 'gbk', $connect = 1) {
$this -> connect($dbhost, $dbuser, $dbpw, $dbname, $dbcharset, $connect);
}
function connect($dbhost, $dbuser, $dbpw, $dbname = '', $dbcharset = 'gbk', $connect=1){
$func = empty($connect) ? 'mysql_pconnect' : 'mysql_connect';
if(!$this->linkid = @$func($dbhost, $dbuser, $dbpw, true)){
$this->dbshow('can not connect to mysql!');
} else {
if($this->dbversion() > '4.1'){
mysql_query( "set names gbk");
if($this->dbversion() > '5.0.1'){
mysql_query("set sql_mode = ''",$this->linkid);
}
}
}
if($dbname){
if(mysql_select_db($dbname, $this->linkid)===false){
$this->dbshow("can't select mysql database($dbname)!");
}
}
}
function select_db($dbname){
return mysql_select_db($dbname, $this->linkid);
}
function query($sql){
if(!$query=@mysql_query($sql, $this->linkid)){
$this->dbshow("query error:$sql");
}else{
return $query;
}
}
function getall($sql, $type=mysql_assoc){
$query = $this->query($sql);
while($row = mysql_fetch_array($query,$type)){
$rows[] = $row;
}
return $rows;
}
function getone($sql, $type=mysql_assoc){
$query = $this->query($sql,$this->linkid);
$row = mysql_fetch_array($query, $type);
return $row;
}
function fetch_array($result,$type = mysql_assoc){
return mysql_fetch_array($result);
}
function affected_rows(){
return mysql_affected_rows($this->linkid);
}
function num_rows(){
return mysql_num_rows($this->linkid);
}
function num_fields($result){
return mysql_num_fields($result);
}
function insert_id(){
return mysql_insert_id($this->linkid);
}
function free_result(){
return mysql_free_result($this->linkid);
}
function error(){
return mysql_error($this->linkid);
}
function errno(){
return mysql_errno($this->linkid);
}
function close(){
return mysql_close($this->linkid);
}
function dbversion(){
return mysql_get_server_info($this->linkid);
}
function dbshow($msg){
if($msg){
echo "error:".$msg."<br><br>";
}else{
echo "errno:".$this->errno()."<br>error:".$this->error();
}
exit;
}
}
|
这是一款我在自己用时写的一款利用php 导出mysql数据库的数据保存成csv文件,并提供下载,原理很简单就是把mysql数据查出来,然后以csv格式保存到一个.csv文件这样就OK了。
$times = time();
$filename = $times.".csv";
$a = "联系人,联系电话,申请时间 ";
$days = postget("days");
$mktime = daystomktime($days);
$sql = "select * from v_tugou where ($times-times)<$mktime";
$db = new db();
$result = $db->query( $sql );
$rs = $db->fetch($result,0);
foreach($rs as $v=>$vv)
{
$a.=$vv['name'].','. $vv['mo'].",";
$a.=date('y-m-d ',$vv['times'])." ";
}
//echo $a;
$hod = fopen ($filename,"w+");
if( fwrite($hod,$a) )
{
echo "生成excel文件成功,点击<a href=$filename target=_blank>右击另存为excel文档</a>";
}
/*
数据结构
--
-- 表的结构 `hn_tugou`
--
create table if not exists `v_tugou` (
`id` int(4) not null auto_increment,
`name` varchar(20) not null default '0',
`mo` varchar(20) not null default '0',
`times` int(4) default null,
`ip` varchar(20) not null default '0',
primary key (`id`)
) engine=myisam default charset=utf8 auto_increment=2 ;
--
-- 导出表中的数据 `hn_tugou`
--
insert into `hn_tugou` (`id`, `name`, `mo`, `times`, `ip`) values
(1, 'adsense', 'www.111cn.net', 1283241159, '192.168.0.119');
本站原创文章转载注明来自http://www.111cn.net/phper/php.html
*/
标签:[!--infotagslink--]