PHPEXCEL的用法與簡介

2024-05-04 21:49:20

字體：大中小

來源：轉載

供稿：網友

PHPEXCEL是一個用來生成excel的php插件,他可以很方便的對excel數據進行操作,如:生成excel,修改excel數據等等.

一、PHPEXCEL簡介

PHPEXCEL提供了一系列的API,能夠解析與生成excel,pdf之類的文檔.

PHPEXCEL雖然強大,不過使用起來相對有些繁鎖,如果需要輸出較為復雜格式時,是一個不錯的選擇,可以到官方下載到源碼.

二、PHPEXCEL部分函數

設置當前的工作簿,返回該工作簿對象:

$excelSheet = $excel->setActiveSheetIndex(0);

合并單元格,返回該單元格對象,以下示例即合并A列第一行與第二行所在單元格:

$excelSheet->mergeCells('A1:A2');

設置單元格的值,參數:單元格名稱,值:

$excelSheet->setCellValue('A1', '字符串內容');

$excelSheet->setCellValue('A2', 26); //數值

$excelSheet->setCellValue('A3', true); //布爾值

$excelSheet->setCellValue('A4', '=SUM(A2:A2)'); //公式

phpexcel用法介紹,代碼如下:

include ‘PHPExcel.php’;

　　include ‘PHPExcel/Writer/Excel2007.php’;

　　//或者include ‘PHPExcel/Writer/Excel5.php’; 用于輸出.xls的

　　創建一個excel

　　$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記錄了每個商戶編號的順序,為了在表體把對應的數據取出,代碼如下:

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';

    // 創建一個處理對象實例（此對象對于2003 2007是相同的）

    $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);

     //開源代碼Vevb.com

    // 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;