亚洲欧美精品沙发,日韩在线精品视频,亚洲Av每日更新在线观看,亚洲国产另类一区在线5

<pre id="hdphd"></pre>

  • <div id="hdphd"><small id="hdphd"></small></div>
      學(xué)習(xí)啦>知識(shí)大全>方法百科>產(chǎn)品使用方法>

      phpexcel使用方法

      時(shí)間: 陳哲凡673 分享

        PHPExcel可是個(gè)功能強(qiáng)大的好東西。下面由學(xué)習(xí)啦小編整理了幾種phpexcel 使用方法,希望對大家有所幫助。

        phpexcel 使用方法(一)

        equire_once 'PHPExcel.php';

        ////require_once'PHPExcel/Writer/Excel5.php'; // 用于其他低版本xls

        // or

        ////require_once'PHPExcel/Writer/Excel2007.php'; // 用于excel-2007 格式

        // 創(chuàng)建一個(gè)處理對象實(shí)例

        $objExcel = new PHPExcel();

        // 創(chuàng)建文件格式寫入對象實(shí)例, uncomment

        ////$objWriter = newPHPExcel_Writer_Excel5($objExcel); // 用于其他版本格式

        // or

        ////$objWriter = newPHPExcel_Writer_Excel2007($objExcel); // 用于2007 格式

        $objProps = $objExcel->getProperties ();

        //設(shè)置創(chuàng)建者

        $objProps->setCreator ( 'XuLulu');

        //設(shè)置最后修改者

        $objProps->setLastModifiedBy("XuLulu");

        //描述

        $objProps->setDescription("摩比班級");

        //設(shè)置標(biāo)題

        $objProps->setTitle ( '管理器' );

        //設(shè)置題目

        $objProps->setSubject("OfficeXLS Test Document, Demo");

        //設(shè)置關(guān)鍵字

        $objProps->setKeywords ( '管理器' );

        //設(shè)置分類

        $objProps->setCategory ( "Test");

        //工作表設(shè)置

        $objExcel->setActiveSheetIndex( 0 );

        $objActSheet = $objExcel->getActiveSheet ();

        //單元格賦值 例:

        $objActSheet->setCellValue ( 'A1', 'ID');

        $objActSheet->setCellValue ( 'B1', 'HashCode');

        $objActSheet->setCellValue ( 'C1', 'ModelName');

        $objActSheet->setCellValue ( 'D1', 'IndexName');

        $objActSheet->setCellValue('A1', '字符串內(nèi)容'); // 字符串內(nèi)容

        $objActSheet->setCellValue('A2', 26); // 數(shù)值

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

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

        //自動(dòng)設(shè)置單元格寬度 例:

        $objActSheet->getColumnDimension('A')->setAutoSize(true);

        //手動(dòng)設(shè)置單元格的寬度 例:

        //$objActSheet->getColumnDimension('A')->setWidth(10);

        //導(dǎo)出的文件名

        $outputFileName = iconv ( 'UTF-8', 'gb2312', 'XuLulu_'. time() . '.xlsx' );

        //直接導(dǎo)出文件

        $objWriter->save ( $outputFileName );

        //文件直接輸出到瀏覽器

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

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

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

        header ( 'Content-Disposition:attachment;filename='. $outputFileName );

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

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

        其他設(shè)置:

        //顯式指定內(nèi)容類型

        $objActSheet->setCellValueExplicit('A5','847475847857487584',

        PHPExcel_Cell_DataType::TYPE_STRING);

        //合并單元格

        $objActSheet->mergeCells('B1:C22');

        //分離單元格

        $objActSheet->unmergeCells('B1:C22');

        //得到單元格的樣式

        $objStyleA5 = $objActSheet->getStyle('A5');

        //設(shè)置字體

        $objFontA5 = $objStyleA5->getFont();

        $objFontA5->setName('Courier New');

        $objFontA5->setSize(10);

        $objFontA5->setBold(true);

        $objFontA5->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);

        $objFontA5->getColor()->setARGB('FF999999');

        //設(shè)置對齊方式

        $objAlignA5 = $objStyleA5->getAlignment();

        $objAlignA5->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        $objAlignA5->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        //設(shè)置邊框

        $objBorderA5 = $objStyleA5->getBorders();

        $objBorderA5->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        $objBorderA5->getTop()->getColor()->setARGB('FFFF0000');// color

        $objBorderA5->getBottom()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        $objBorderA5->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        $objBorderA5->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        //設(shè)置填充顏色

        $objFillA5 = $objStyleA5->getFill();

        $objFillA5->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

        $objFillA5->getStartColor()->setARGB('FFEEEEEE');

        //從指定的單元格復(fù)制樣式信息.

        $objActSheet->duplicateStyle($objStyleA5,'B1:C22');

        //*************************************

        //添加圖片

        $objDrawing = new PHPExcel_Worksheet_Drawing();

        $objDrawing->setName('ZealImg');

        $objDrawing->setDescription('Image inserted byZeal');

        $objDrawing->setPath('./zeali.net.logo.gif');

        $objDrawing->setHeight(36);

        $objDrawing->setCoordinates('C23');

        $objDrawing->setOffsetX(10);

        $objDrawing->setRotation(15);

        $objDrawing->getShadow()->setVisible(true);

        $objDrawing->getShadow()->setDirection(36);

        $objDrawing->setWorksheet($objActSheet);

        //添加一個(gè)新的worksheet

        $objExcel->createSheet();

        $objExcel->getSheet(1)->setTitle('測試2');

        //保護(hù)單元格

        $objExcel->getSheet(1)->getProtection()->setSheet(true);

        $objExcel->getSheet(1)->protectCells('A1:C22','PHPExcel');

        PHPExcel在cakephp中應(yīng)用:

        在Vendors/下創(chuàng)建一個(gè)文件夾Excel,將PHPExcel的目錄如下:

        在要調(diào)用的Controller下的方法寫如下代碼:

        App::import ( 'Vendor', 'Excel', array ('file' =>'PHPExcel.php' ) );后,就可以實(shí)例化PHPExcel;具體跟以上情況一樣。

        phpexcel 使用方法(二)

        include 'PHPExcel.php';

        include 'PHPExcel/Writer/Excel2007.php';

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

        創(chuàng)建一個(gè)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');

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

        設(shè)置excel的屬性:

        創(chuàng)建人

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

        最后修改人

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

        標(biāo)題

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

        關(guān)鍵字

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

        種類

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

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

        設(shè)置當(dāng)前的sheet

        $objPHPExcel->setActiveSheetIndex(0);

        設(shè)置sheet的name

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

        設(shè)置單元格的值

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

        保護(hù)cell

        $objPHPExcel->getActiveSheet()->getProtection()->setSheet(true); // Needs to be set to true in order to enable any worksheet protection!

        $objPHPExcel->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');

        設(shè)置格式

        // Set cell number formats

        echo date('H:i:s') . " Set cell number formats\n";

        $objPHPExcel->getActiveSheet()->getStyle('E4')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_EUR_SIMPLE);

        $objPHPExcel->getActiveSheet()->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('E4'), 'E5:E13' );

        設(shè)置寬width

        // Set column widths

        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);

        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(12);

        設(shè)置font

        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setName('Candara');

        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setSize(20);

        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->setUnderline(PHPExcel_Style_Font::UNDERLINE_SINGLE);

        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

        $objPHPExcel->getActiveSheet()->getStyle('E1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);

        $objPHPExcel->getActiveSheet()->getStyle('D13')->getFont()->setBold(true);

        $objPHPExcel->getActiveSheet()->getStyle('E13')->getFont()->setBold(true);

        設(shè)置align

        $objPHPExcel->getActiveSheet()->getStyle('D11')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        $objPHPExcel->getActiveSheet()->getStyle('D12')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        $objPHPExcel->getActiveSheet()->getStyle('D13')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

        $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);

        //垂直居中

        $objPHPExcel->getActiveSheet()->getStyle('A18')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

        設(shè)置column的border

        $objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        $objPHPExcel->getActiveSheet()->getStyle('B4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        $objPHPExcel->getActiveSheet()->getStyle('C4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        $objPHPExcel->getActiveSheet()->getStyle('D4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        $objPHPExcel->getActiveSheet()->getStyle('E4')->getBorders()->getTop()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

        設(shè)置border的color

        $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');

        $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');

        $objPHPExcel->getActiveSheet()->getStyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');

        $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getTop()->getColor()->setARGB('FF993300');

        $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');

        $objPHPExcel->getActiveSheet()->getStyle('E13')->getBorders()->getRight()->getColor()->setARGB('FF993300');

        設(shè)置填充顏色

        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080');

        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);

        $objPHPExcel->getActiveSheet()->getStyle('B1')->getFill()->getStartColor()->setARGB('FF808080');

        加圖片

        $objDrawing = new PHPExcel_Worksheet_Drawing();

        $objDrawing->setName('Logo');

        $objDrawing->setDescription('Logo');

        $objDrawing->setPath('./images/officelogo.jpg');

        $objDrawing->setHeight(36);

        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

        $objDrawing = new PHPExcel_Worksheet_Drawing();

        $objDrawing->setName('Paid');

        $objDrawing->setDescription('Paid');

        $objDrawing->setPath('./images/paid.png');

        $objDrawing->setCoordinates('B15');

        $objDrawing->setOffsetX(110);

        $objDrawing->setRotation(25);

        $objDrawing->getShadow()->setVisible(true);

        $objDrawing->getShadow()->setDirection(45);

        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

        //處理中文輸出問題

        需要將字符串轉(zhuǎn)化為UTF-8編碼,才能正常輸出,否則中文字符將輸出為空白,如下處理:

        $str = iconv('gb2312', 'utf-8', $str);

        或者你可以寫一個(gè)函數(shù)專門處理中文字符串:

        function convertUTF8($str)

        {

        if(empty($str)) return '';

        return iconv('gb2312', 'utf-8', $str);

        }

        //從數(shù)據(jù)庫輸出數(shù)據(jù)處理方式

        從數(shù)據(jù)庫讀取數(shù)據(jù)如:

        $db = new Mysql($dbconfig);

        $sql = "SELECT * FROM 表名";

        $row = $db->GetAll($sql); // $row 為二維數(shù)組

        $count = count($row);

        for ($i = 2; $i <= $count+1; $i++) {

        $objPHPExcel->getActiveSheet()->setCellValue('A' . $i, convertUTF8($row[$i-2][1]));

        $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, convertUTF8($row[$i-2][2]));

        $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, convertUTF8($row[$i-2][3]));

        $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, convertUTF8($row[$i-2][4]));

        $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, convertUTF8(date("Y-m-d", $row[$i-2][5])));

        $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, convertUTF8($row[$i-2][6]));

        $objPHPExcel->getActiveSheet()->setCellValue('G' . $i, convertUTF8($row[$i-2][7]));

        $objPHPExcel->getActiveSheet()->setCellValue('H' . $i, convertUTF8($row[$i-2][8]));

        }

        在默認(rèn)sheet后,創(chuàng)建一個(gè)worksheet

        echo date('H:i:s') . " Create new Worksheet object\n";

        $objPHPExcel->createSheet();

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

        $objWriter-save('php://output');

      374332