php 导出excel数据实例代码

require_once '../Classes/PHPExcel.php';
require_once '../Classes/PHPExcel/IOFactory.php';
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")->setLastModifiedBy("Maarten Balliauw")->setTitle("Office 2007 XLSX Test Document")->setSubject("Office 2007 XLSX Test Document")->setDescription("Document for Office 2007 XLSX, generated using PHP classes.")->setKeywords("office 2007 openxml php")->setCategory("Test result file");

$objPHPExcel->setActiveSheetIndex(0);
$objRichText = new PHPExcel_RichText();
$objRichText->createText('');
$objPayable = $objRichText->createTextRun('即时监测数据');
$objPayable->getFont()->setColor( new PHPExcel_Style_Color( PHPExcel_Style_Color::COLOR_RED ));
$objPayable->getFont()->setBold(true);
$objPayable->getFont()->setSize(24);
$objPHPExcel->getActiveSheet()->getCell('B1')->setValue($objRichText);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);  // 加粗
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(24);   // 字体大小
$objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_RED); // 文本颜色
$objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFill()->getStartColor()->setARGB('00FFFFE3');//底纹

// 列宽
//$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(18);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);

// 行高
for($i = 2; $i <= 17; $i++) {
 $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(22);
}

$objPHPExcel->getActiveSheet()->setCellValue('A2', '姓名');
$objPHPExcel->getActiveSheet()->setCellValue('A3', 'PHP');
$objPHPExcel->getActiveSheet()->setCellValue('A4', 'EXCEL');
$objPHPExcel->getActiveSheet()->setCellValue('A5', '');
$objPHPExcel->getActiveSheet()->setCellValue('A6', 'XXX');
$objPHPExcel->getActiveSheet()->setCellValue('A7', 'V');
$objPHPExcel->getActiveSheet()->setCellValue('A8', 'd');
$objPHPExcel->getActiveSheet()->setCellValue('A9', 'name');
$objPHPExcel->getActiveSheet()->setCellValue('A10', '描述');
for($i = 2; $i <= 16; $i++) {
 $objPHPExcel->getActiveSheet()->getStyle('A' . $i)->getFont()->setBold(true);  // 加粗
}

$objPHPExcel->getActiveSheet()->setCellValue('B2', '[姓名]');
$objPHPExcel->getActiveSheet()->setCellValue('B3', '导出带图片的EXCEL');
$objPHPExcel->getActiveSheet()->setCellValue('B4', 'XXXXX');

// 为excel加图片
//$objDrawing = new PHPExcel_Worksheet_Drawing();
//$objDrawing->setName('Photo');
//$objDrawing->setDescription('Photo');
//$objDrawing->setPath('../images/touxiang.jpg');
//$objDrawing->setHeight(170);
//$objDrawing->setWidth(120);
//$objDrawing->setCoordinates('G2');
//$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

$objPHPExcel->getActiveSheet()->setCellValue('A17', '其它要求');
$objPHPExcel->getActiveSheet()->setCellValue('B17', '[其它要求]');

$objPHPExcel->getActiveSheet()->getStyle('A17')->getFont()->setBold(true);  // 加粗

$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('即时监测数据');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save('PHPExcel.xls');

<?php
     /**
      * 数据库配置信息
      */
     define('DB_HOST','localhost');            //服务器
     define('DB_USER','root');                 //数据库用户名
     define('DB_PASSWORD','');                 //数据库密码
     define('DB_NAME','test0');                //默认数据库
     define('DB_CHARSET','utf8');              //数据库字符集
     define('TIMEZONE',"PRC");                 //时区设置

?>

<?php

     require_once("./db.class.php");
 
  class User{
   public $name = NULL;
   public $password = NULL;
  
   /**
    * 构造函数
    */
   public function __construct($name,$password){
    $this->name = $name;
    $this->password = $password;
    }

   public function insert(){
    $db = new DB();
       $resultid = $db->insertData("user",array(),array('',$this->name,$this->password)); 
       return $resultid;
    }
  
   public static function getUserById($uid){
     $db = new DB();
     return $db->getDataByAtr("user",'uid',$uid);
     }
 
   public static function getUserByName($name){
     $db = new DB();
     @$data = $db->getObjListBySql("SELECT * FROM user WHERE name = '$name'");
     if(count($data)!=0)return $data;
     else return null;
     }

   public static function getAllUser(){
     $db = new DB();
      @$data = $db->getObjListBySql("SELECT * FROM user");
      if(count($data)!=0) return $data;
      else return null;
     }
    
   public static function deleteByUid($uid){
     $admin = Admin::getAdminById($uid);
     $db = new DB();
     if($db->delete("user","uid",$uid)) return true;
     else return false;
     }
   } 
  
?>

<?php
    header("Content-Type:text/html; charset=utf8");

 require_once("./user.class.php");

 $user = new User("HelloWorld","123456");
 $user->insert();

 $users = User::getAllUser();

 foreach ($users as $u) {
  echo "<br/>".$u->name."<br/>".$u->password."<br/>";
 }
?>

require_once('./phpexcel1.7.3/PHPExcel.php');
$php_excel_obj = new PHPExcel();
$php_reader = newPHPExcel_Reader_Excel2007();
if(!$php_reader->canRead($file_name)){
       $php_reader= new PHPExcel_Reader_Excel5();
       if(!$php_reader->canRead($file_name)){
              echo'NO Excel!';
       }
}
$php_excel_obj = $php_reader->load($file_name);
$current_sheet =$php_excel_obj->getSheet(0);

$all_column =$current_sheet->getHighestColumn();
$all_row =$current_sheet->getHighestRow();

$all_arr = array();
$c_arr = array();
//字符对照表
for($r_i = 1; $r_i<=$all_row; $r_i++){
       $c_arr= array();
       for($c_i= 'A'; $c_i<= 'B'; $c_i++){
              $adr= $c_i . $r_i;
              $value= $current_sheet->getCell($adr)->getValue();
              if($c_i== 'A' && empty($value) )  break;
              if(is_object($value))  $value= $value->__toString();
              $c_arr[$c_i]= $value;
       }
       $c_arr&& $all_arr[] =  $c_arr;
}

require_once('./phpexcel1.7.3/PHPExcel.php');
$excel_obj = new PHPExcel();
$objWriter = newPHPExcel_Writer_Excel5($excel_obj);
$excel_obj->setActiveSheetIndex(0);
$act_sheet_obj=$excel_obj->getActiveSheet();
$act_sheet_obj->setTitle('sheet');
$act_sheet_obj->setCellValue('A1', '字符串内容');
$act_sheet_obj->setCellValue('A2', 26);

$file_name = "output.xls";
$objWriter->save($file_name);

mysql>exit;
@>mysql -u phplamp -p
@>输入密码
mysql>登录成功

//登录MYSQL（有ROOT权限）。我里我以ROOT身份登录.
@>mysql -u root -p
@>密码
//首先为用户创建一个数据库(phplampDB)
mysql>create database phplampDB;
//授权phplamp用户拥有phplamp数据库的所有权限。
>grant all privileges on phplampDB.* to phplamp@localhost identified by '1234';
//刷新系统权限表
mysql>flush privileges;
mysql>其它操作

/*
如果想指定部分权限给一用户，可以这样来写:
mysql>grant select,update on phplampDB.* to phplamp@localhost identified by '1234';
//刷新系统权限表。
mysql>flush privileges;
*/

<?php
$con = mysql_connect("localhost","peter","abc123");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

if (mysql_query("CREATE DATABASE my_db",$con))
  {
  echo "Database created";
  }
else
  {
  echo "Error creating database: " . mysql_error();
  }

mysql_close($con);
?>