phpexecel自个儿的使用记录,PHPEXCEL的用法与简介


$user_sql = “select * from `table`”;

一、PHPExcel简介

$user_sql = “select * from `table`”;

自家意识以来的品类需求导出Excel表的页面相当的多,想来那个也是大家常用的成效了,未来本身可以很在行的导出了,可是记得当时温馨率先次导出时还是绕了有个别弯路的,那么将来小编就来记录下自身本次用exshop框架项目下的导出(其实在不一致的框架下Excel的导出原理都以大半的)

$user_list = $db->fetchAll($user_sql);//数据库查询

PHPEXCEL提供了一星罗棋布的API,能够分析与生成excel,pdf之类的文档。

$user_list = $db->fetchAll($user_sql);//数据库查询


require_once dirname(__FILE__) .
‘/../../class/PHPExcel.php’;//加载phpexcel.php,依据本人项目标路线调整

PHPEXCEL即便强大,但是使用起来相对某个繁锁,假若急需输出较为复杂格式时,是二个不利的选拔。可以到法定下载到源码。

require_once dirname(__FILE__) .
‘/../../class/PHPExcel.php’;//加载phpexcel.php,根据自个儿项目标门径调整

前端

$objPHPExcel = new PHPExcel();

二 、PHPEXCEL部分函数

$objPHPExcel = new PHPExcel();

 <a href="javascript:;" id="export_all" class="coolbg">导出</a>

<script>
    //导出数据
    $('#export_all').click(function(){
        window.open('index.php?app=craft_order&act=export', '_blank');
    });
</script>

$objPHPExcel->getProperties()->setCreator(“Maarten Balliauw”)
->setLastModifiedBy(“Maarten Balliauw”)
->setTitle(“Office 2007 XLSX Test Document”)
->setSubject(“Office 2007 XLSX Test Document”)
->setDescription(“Test document for Office 2007 XLSX, generated using
PHP classes.”)
->setKeywords(“office 2007 openxml php”)
->setCategory(“Test result file”);

安装当前的工作簿,重临该工作簿对象:
$excelSheet = $excel->setActiveSheetIndex(0);

$objPHPExcel->getProperties()->setCreator(“Maarten Balliauw”)
->setLastModifiedBy(“Maarten Balliauw”)
->setTitle(“Office 2007 XLSX Test Document”)
->setSubject(“Office 2007 XLSX Test Document”)
->setDescription(“Test document for Office 2007 XLSX, generated using
PHP classes.”)
->setKeywords(“office 2007 openxml php”)
->setCategory(“Test result file”);

控制器
“`
// 导出多少
public function export() {
$result =
$this->_oaOrderModel->getAllOrderListForManager($this->store_id,
$orderSn=null, $buyer_id=null, $buyer_name=null, $consignee=null,
$phone=null, $company_name=null, $status=null, $s_time=null,
$e_time=null, $page=null, $listRows=null, $execl=true);
//那个是得到数据的代码-model里
$orderList = $result[‘orderList’];
if (!empty($orderList)) {
$j = 1;
$stmt = array();
foreach ($orderList as $val) {
$stmt[$j][‘网站ID’] = $val[‘store_id’];
$stmt[$j][‘订单音信’] = $val[‘order_sn’];
$stmt[$j][‘商品消息’] = $val[‘inventory_sn_count_chinese’];
$stmt[$j][‘工艺选取’] = $val[‘craft_count_chinese’];
$stmt[$j][‘商品总数据’] = $val[‘real_goods_total_count’];
$stmt[$j][‘提交日期’] = date(“Y-m-d H:i:s”,$val[‘add_time’]);
$stmt[$j][‘客户名称’] = $val[‘company_phpexecel自个儿的使用记录,PHPEXCEL的用法与简介。name’];
$stmt[$j][‘联系人’] = $val[‘consignee’];
$stmt[$j][‘联系格局’] = $val[‘phone_mob’];
$stmt[$j][‘订单成功率’] = $val[‘percentage_complete’];
$stmt[$j][‘订单状态’] = $val[‘statusChinese’];
$j++;
}
$current_path = dirname(FILE);
$home_path = dirname($current_path);
require_once ROOT_PATH . ‘/includes/libraries/PHPExcel.php’;
require_once ROOT_PATH .
‘/includes/libraries/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)
->setCellValue(‘A1’, ‘编号’)
->setCellValue(‘B1’, ‘在线人数’)
->setCellValue(‘C1’, ‘手机’)

美高梅开户网址 ,集合单元格,再次回到该单元格对象,以下示例即联合A列第2行与第②行所在单元格:

//设置标题名称
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue(‘A1’, ‘编号’)
->setCellValue(‘B1’, ‘在线人数’)
->setCellValue(‘C1’, ‘手机’)

        // 行高
        for ($i = 2; $i <= count($stmt); $i++) {
            $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(22);
        }
        foreach ($stmt as $fid => $fval) {
            if ($fid == 1) {
                $key = 0;
                foreach ($fval as $title => $first) {
                    //如果一级标题
                    $objPHPExcel->getActiveSheet()->setCellValue(chr($key + 65) . '1', $title);
                    $objPHPExcel->getActiveSheet()->getStyle(chr($key + 65) . '1')->getFont()->setBold(true);       // 加粗
                    $key ++;
                }
            }
            $cid = 0;
            $row_id = $fid + 1;
            foreach ($fval as $cval) {
                $objPHPExcel->getActiveSheet()->setCellValue(chr($cid + 65) . (string) ($row_id), $cval);
                $cid++;
            }
        }
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle('Excel表');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        //$objWriter->save('订单列表详细.xls');
        //输出到浏览器
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="订单列表.xls"');
        header("Content-Transfer-Encoding: binary");
        header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $objWriter->save('php://output');
    }
}

```

->setCellValue(‘D1’, ‘记录时间’);  

 代码如下

->setCellValue(‘D1’, ‘记录时间’);  

只顾:PHPExcel(); //这些方法本人下载放到公共艺术里

//循环每一种数据,假如赶上手机号码的数量导出,可以在相应的数近期、后添加1个空格,达到将数据变成字符串的法子,令execl不是讲其当成科学数据法的办法显示出来
foreach ($user_list as $k => $v) {
$num=$k+2;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue(‘A’.$num, $v[‘id’])
->setCellValue(‘B’.$num, $v[‘online’])

$excelSheet->mergeCells(‘A1:A2’);

//循环每种数据,如若遇上手机号码的多寡导出,可以在对应的多寡前、后添加三个空格,达到将数据变成字符串的法子,令execl不是讲其当成科学数据法的点子浮现出来
foreach ($user_list as $k => $v) {
$num=$k+2;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue(‘A’.$num, $v[‘id’])
->setCellValue(‘B’.$num, $v[‘online’])

成果图

->setCellValue(‘C’.$num, ‘ ‘.$v[‘phone_num’].’ ‘)
->setCellValue(‘D’.$num, date(‘Y-m-d H:i:s’,$v[‘dateline’]));
}

安装单元格的值,参数:单元格名称,值:
$excelSheet->setCellValue(‘A1’, ‘字符串内容’);
$excelSheet->setCellValue(‘A2’, 26); //数值
$excelSheet->setCellValue(‘A3’, true); //布尔值
$excelSheet->setCellValue(‘A4’, ‘=SUM(A2:A2)’); //公式

->setCellValue(‘C’.$num, ‘ ‘.$v[‘phone_num’].’ ‘)
->setCellValue(‘D’.$num, date(‘Y-m-d H:i:s’,$v[‘dateline’]));
}

美高梅开户网址 1

$objPHPExcel->getActiveSheet()->getColumnDimension(‘C’)->setWidth(20);//对某列单元格设置宽度

phpexcel用法介绍

$objPHPExcel->getActiveSheet()->getColumnDimension(‘C’)->setWidth(20);//对某列单元格设置宽度

美高梅开户网址 2

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle(‘sheet1’);

 

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle(‘sheet1’);

美高梅开户网址 3

// Set active sheet index to the first sheet, so Excel opens this as the
first sheet
$objPHPExcel->setActiveSheetIndex(0);

 代码如下

// Set active sheet index to the first sheet, so Excel opens this as the
first sheet
$objPHPExcel->setActiveSheetIndex(0);


$time_name =
‘在线人数’.date(‘YmdHis’,time()).’.xls’;//设置下载时文件的暗中同意名

include ‘PHPExcel.php’;

$time_name =
‘在线人数’.date(‘YmdHis’,time()).’.xls’;//设置下载时文件的私自认同名

心得

// Redirect output to a client’s web browser (Excel5)
header(‘Content-Type: application/vnd.ms-excel’);
header(“Content-Disposition: attachment;filename=’$time_name'”);
header(‘Cache-Control: max-age=0’);
// If you’re serving to IE 9, then the following may be needed
header(‘Cache-Control: max-age=1’);

  include ‘PHPExcel/Writer/Excel2007.php’;

// Redirect output to a client’s web browser (Excel5)
header(‘Content-Type: application/vnd.ms-excel’);
header(“Content-Disposition: attachment;filename=’$time_name'”);
header(‘Cache-Control: max-age=0’);
// If you’re serving to IE 9, then the following may be needed
header(‘Cache-Control: max-age=1’);

有时碰着那么些题材得以多动脑筋,多看看它的法则,原理明白了下次做任何的也是会的,但最关键的是要明了做笔录,大家的回忆并不曾设想的那么好

// If you’re serving to IE over SSL, then the following may be needed
header (‘Expires: Mon, 26 Jul 1997 05:00:00 GMT’); // Date in the past
header (‘Last-Modified: ‘.gmdate(‘D, d M Y H:i:s’).’ GMT’); // always
modified
header (‘Cache-Control: cache, must-revalidate’); // HTTP/1.1
header (‘Pragma: public’); // HTTP/1.0

  //大概include ‘PHPExcel/Writer/Excel5.php’; 用于输出.xls的

// If you’re serving to IE over SSL, then the following may be needed
header (‘Expires: Mon, 26 Jul 1997 05:00:00 GMT’); // Date in the past
header (‘Last-Modified: ‘.gmdate(‘D, d M Y H:i:s’).’ GMT’); // always
modified
header (‘Cache-Control: cache, must-revalidate’); // HTTP/1.1
header (‘Pragma: public’); // HTTP/1.0

注:小说来源雨中笑记录实习期碰到的难点与感受,转发请表明原文

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,
‘Excel5’);
$objWriter->save(‘php://output’);
die;

  创造一个excel

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel,
‘Excel5’);
$objWriter->save(‘php://output’);
die;

  $objPHPExcel = new PHPExcel();

  保存excel—2007格式

  $objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);

  //或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
非2007格式

  $objWriter->save(”xxx.xlsx”);

  间接出口到浏览器

  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);

  header(”Pragma: public”);

  header(”Expires: 0″);

  header(”Cache-Control:must-revalidate, post-check=0, pre-check=0″);

  header(”Content-Type:application/force-download”);

  header(”Content-Type:application/vnd.ms-execl”);

  header(”Content-Type:application/octet-stream”);

  header(”Content-Type:application/download”);;

  header(’Content-Disposition:attachment;filename=”resume.xls”‘);

  header(”Content-Transfer-Encoding:binary”);

  $objWriter->save(’php://output’);

  

——————————————————————————————————————–

  设置excel的属性:

  

 代码如下

创建人

  $objPHPExcel->getProperties()->setCreator(”Maarten Balliauw”);

  最终修改人

  $objPHPExcel->getProperties()->setLastModifiedBy(”Maarten
Balliauw”);

  标题

  $objPHPExcel->getProperties()->setTitle(”Office 2007 XLSX Test
Document”);

  题目

  $objPHPExcel->getProperties()->setSubject(”Office 2007 XLSX
Test Document”);

  描述

  $objPHPExcel->getProperties()->setDescription(”Test document
for Office 2007 XLSX, generated using PHP classes.”);

  关键字

  $objPHPExcel->getProperties()->setKeywords(”office 2007
openxml php”);

  种类

  $objPHPExcel->getProperties()->setCategory(”Test result
file”);

  ——————————————————————————————————————–

  设置当前的sheet

  $objPHPExcel->setActiveSheetIndex(0);

  设置sheet的name

  $objPHPExcel->getActiveSheet()->setTitle(’Simple’);

  设置单元格的值

  $objPHPExcel->getActiveSheet()->setCellValue(’A1′, ‘String’);

  $objPHPExcel->getActiveSheet()->setCellValue(’A2′, 12);

  $objPHPExcel->getActiveSheet()->setCellValue(’A3′, true);

  $objPHPExcel->getActiveSheet()->setCellValue(’C5′,
‘=SUM(C2:C4)’);

  $objPHPExcel->getActiveSheet()->setCellValue(’B8′,
‘=MIN(B2:C5)’);

  合并单元格

  $objPHPExcel->getActiveSheet()->mergeCells(’A18:E22′);

  分离单元格

  $objPHPExcel->getActiveSheet()->unmergeCells(’A28:B28′);

 ③ 、PHPEXCEL举例应用

成套代码如下(值得注意的是表头用了$orderCellData记录了各个专营商编号的相继,为了在表体把相应的数目取出):

 代码如下

<<?php

   

    require_once ‘../../../libs/PHPExcel/Classes/PHPExcel.php’;

    require_once
‘../../../libs/PHPExcel/Classes/PHPExcel/Writer/Excel5.php’;

    include_once
‘../../../libs/PHPExcel/Classes/PHPExcel/IOFactory.php’;

    include ‘../common/config.php’;

    // 成立二个处理目的实例(此目的对于二〇〇一 2005是同等的)

    $objExcel = new PHPExcel();

   

    //设置属性(那段代码无关主要,其中的情节可以替换为您须要的)

    $objExcel->getProperties()->setCreator(“office 2003 excel”);

    $objExcel->getProperties()->setLastModifiedBy(“office 2003
excel”);

    $objExcel->getProperties()->setTitle(“Office 2003 XLS Test
Document”);

    $objExcel->getProperties()->setSubject(“Office 2003 XLS Test
Document”);

    $objExcel->getProperties()->setDescription(“Test document for
Office 2003 XLS, generated using PHP classes.”);

    $objExcel->getProperties()->setKeywords(“office 2003 openxml
php”);

    $objExcel->getProperties()->setCategory(“Test result file”);

   

    //初叶拍卖数据(索引从0开首)

    $objExcel->setActiveSheetIndex(0);

 

    $conn =
mssql_connect($config[‘mssql’][‘host’],$config[‘mssql’][‘user’],$config[‘mssql’][‘password’]);

    mssql_select_db($config[‘mssql’][‘dbname’],$conn);

   

    $tm=$_REQUEST[‘tm’];

   

    $sql = “exec HNow05_getTTSpace ”,'”.$tm.”‘,”,1″;

    $sql=mb_convert_encoding($sql,’GBK’,’UTF-8′);

    $res=mssql_query($sql);

 

    $i=0;

    $k = array(‘站码’,’站名’,’河系’,’来报时间’,’水位’,’水势’);

    $count = count($k);

    $arrs = array(‘A’,’B’,’C’,’D’,’E’,’F’);

    //添加表头

    for($i=0;$i<$count;$i++){

       $objExcel->getActiveSheet()->setCellValue($arrs[$i].”1″,
“$k[$i]”);

      

    }

   

    /*——–从数据库读取数据——-*/

    $i=0;

    while($arr=mssql_fetch_array($res))

    {

       $stcd = $arr[“STCD”];

       $stnm = $arr[“STNM”];

       $rvnm = $arr[“RVNM”];

       $tm= $arr[“TM”];

       $tdz= $arr[“TDZ”];

        $tdptn= $arr[“TDPTN”];

       if($tdptn==’6′){

           $tdptn=’平’;

       }else if($tdptn==’5′){

           $tdptn=’涨’;

       }else if($tdptn==’4′){

           $tdptn=’落’;

       }

 

       $u1=$i+2;

       $stnm=iconv(“GBK”,”utf-8″,$stnm);

       $rvnm=iconv(“GBK”,”utf-8″,$rvnm);

       $tm=iconv(“GBK”,”utf-8″,$tm);

 

       /*———-写入内容————-*/

       $objExcel->getActiveSheet()->setCellValue(‘a’.$u1,
“$stcd”);

       $objExcel->getActiveSheet()->setCellValue(‘b’.$u1,
“$stnm”);

       $objExcel->getActiveSheet()->setCellValue(‘c’.$u1,
“$rvnm”);

       $objExcel->getActiveSheet()->setCellValue(‘d’.$u1, “$tm”);

       $objExcel->getActiveSheet()->setCellValue(‘e’.$u1, “$tdz”);

       $objExcel->getActiveSheet()->setCellValue(‘f’.$u1,
“$tdptn”);

 

       $i++;

    }

 

    /*———-设置单元格边框和颜料————-*/

    $rows = mssql_num_rows($res);

    for($i=0;$i<($rows+1);$i++){

       for($j=0;$j<$count;$j++){

           $a = $i+1;

          
$objExcel->getActiveSheet()->getStyle($arrs[$j].$a)->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

          
$objExcel->getActiveSheet()->getStyle($arrs[$j].$a)->getBorders()->getAllBorders()->getColor()->setARGB(‘FF00BBcc’);

           //水平居中

          
$objExcel->getActiveSheet()->getStyle($arrs[$j].$a)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

       }

    }

 

    // 高置列的小幅度

   
$objExcel->getActiveSheet()->getColumnDimension(‘A’)->setWidth(10);

   
$objExcel->getActiveSheet()->getColumnDimension(‘B’)->setWidth(15);

   
$objExcel->getActiveSheet()->getColumnDimension(‘C’)->setWidth(15);

   
$objExcel->getActiveSheet()->getColumnDimension(‘D’)->setWidth(20);

   
$objExcel->getActiveSheet()->getColumnDimension(‘E’)->setWidth(10);

   
$objExcel->getActiveSheet()->getColumnDimension(‘F’)->setWidth(10);

   

    // 设置页眉和页脚。倘使没有不一样的题目奇数/即便是采纳单头假定.

   
$objExcel->getActiveSheet()->getHeaderFooter()->setOddHeader(‘&L&BPersonal
cash register&RPrinted on &D’);

   
$objExcel->getActiveSheet()->getHeaderFooter()->setOddFooter(‘&L&B’
. $objExcel->getProperties()->getTitle() . ‘&RPage &P of &N’);

   

    // 设置页方向和范围

   
$objExcel->getActiveSheet()->getPageSetup()->setOrientation(PHPExcel_Worksheet_PageSetup::ORIENTATION_PORTRAIT);

   
$objExcel->getActiveSheet()->getPageSetup()->setPaperSize(PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);

   

    // 重命名表

    $objExcel->getActiveSheet()->setTitle(‘实时潮汐情状’);

   

    // Set active sheet index to the first sheet, so Excel opens this as
the first sheet

    $objExcel->setActiveSheetIndex(0);

   

    // Redirect output to a client’s web browser
(Excel5)保存为excel2003格式

    //设置Excel的名字

    $excelName = ‘实时潮汐意况(‘.$tm.’)’;

    //$excelName = ‘Excel_’.date(“YmdHis”);

    header(‘Content-Type: application/vnd.ms-excel’);

    header(‘Cache-Control: max-age=0’);

    header( ‘Content-Disposition: attachment; filename=’.iconv(“utf-8”,
“GBK”, $excelName).’.xls’);

    $objWriter = PHPExcel_IOFactory::createWriter($objExcel, ‘Excel5’);

    $objWriter->save(‘php://output’);

    exit;

?>

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图