(本地開發(fā)環(huán)境下進(jìn)行： CI 3.x + PHPExcel 1.8)

1汪诉、下載 PHPExcel 1.8

下載地址：https://archive.codeplex.com/?p=phpexcel

2、解壓钻心，拷貝有效文件及文件夾

2-1、將解壓后的文件夾里的Classes文件夾里面的所有文件和文件夾放到application\libraries\目錄下楞艾，目錄結(jié)構(gòu)如下：

application\libraries\PHPExcel.php
application\libraries\PHPExcel\

2-2拧略、修改application\libraries\PHPExcel\IOFactory.php 文件

1）將其類名從PHPExcel_IOFactory改為IOFactory，遵從CI類命名規(guī)則
2）將其構(gòu)造函數(shù)改為public

3寒锚、代碼整合范例

3-1、導(dǎo)出

先看下效果截圖：

phpexcel_export.png

控制器代碼：

public function ExcelExport(){
    $this->load->library('PHPExcel');
    $this->load->library('PHPExcel/IOFactory');

    $objPHPExcel = new PHPExcel();
    // 設(shè)置sheet標(biāo)簽
    $objPHPExcel->setActiveSheetIndex(0);
    // 設(shè)置sheet的name
    $objPHPExcel->getActiveSheet()->setTitle('測試sheet');

    // 合并單元格
    $objPHPExcel->getActiveSheet()->mergeCells('A1:E1');
    // 設(shè)置行高
    $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(40);

    // 自動行高
    $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(-1);

    // 設(shè)置水平居中
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    //設(shè)置垂直居中
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);

    // 設(shè)置加粗
    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

    //設(shè)置當(dāng)前的sheet
    $objPHPExcel->getActiveSheet()->setCellValue('A1', '導(dǎo)出測試excel樣式及內(nèi)容');
    $objPHPExcel->getActiveSheet()->setCellValue('A2', 'IMG');
    $objPHPExcel->getActiveSheet()->setCellValue('B2', '項(xiàng)目');
    $objPHPExcel->getActiveSheet()->setCellValue('C2', '結(jié)果');
    $objPHPExcel->getActiveSheet()->setCellValue('D2', '數(shù)量');
    $objPHPExcel->getActiveSheet()->setCellValue('E2', '統(tǒng)計(jì)');

    $i = 3;
    $m_data = ['12', '5445', '457', '63'];
    foreach($m_data as $key => $value){
        // 設(shè)置行高
        $objPHPExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(45);
        $objPHPExcel->getActiveSheet()->getDefaultColumnDimension('A'.$i)->setWidth(8);

        // 圖片生成
        $imgPath = PUBLIC_IMG_RESOURCE_PATH . 'default-user.jpg';
        $objDrawing = new PHPExcel_Worksheet_Drawing();
        $objDrawing->setPath($imgPath);
        // 設(shè)置寬度高度
        $objDrawing->setHeight(40);//照片高度
        $objDrawing->setWidth(40); //照片寬度
        /*設(shè)置圖片要插入的單元格*/
        $objDrawing->setCoordinates('A'.$i);
        // 圖片偏移距離
        $objDrawing->setOffsetX(10);
        $objDrawing->setOffsetY(10);
        $objDrawing->setWorksheet($objPHPExcel->getActiveSheet());

        $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, $value);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $i, $value);
        $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, $value);
        $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, '=SUM(B'.$i.':D'.$i.')');
        $i ++;
    }

    $outputFileName = time().".xls";
    $xlsWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");
    header('Content-Disposition:inline;filename="'.$outputFileName.'"');
    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");
    $xlsWriter->save( "php://output" );
}

3-2、導(dǎo)入

控制器代碼：

public function ExcelImport(){
    //加載工廠類
    $this->load->library('PHPExcel/IOFactory');

    //要讀取的xls文件路徑
    $inputFileName = PUBLIC_FILE_RESOURCE_PATH . 'listTest/userListTest.xlsx';

    /** 用PHPExcel_IOFactory的load方法得到excel操作對象  **/
    $objPHPExcel = IOFactory::load($inputFileName);

    //得到當(dāng)前活動表格刹前，調(diào)用toArray方法泳赋，得到表格的二維數(shù)組
    $sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);

    $sheet = $objPHPExcel->getActiveSheet(); // 當(dāng)前活動表格
    $highestRow = $sheet -> getHighestRow(); // 取得總行數(shù)
    $highestColumn = $sheet->getHighestColumn(); // 取得總列數(shù)(格式：'AA')
    $columns = PHPExcel_Cell::columnIndexFromString($highestColumn); // 將數(shù)據(jù)總列數(shù)(格式：'AA')轉(zhuǎn)換成數(shù)字列數(shù)(格式：27)表示

    // 處理轉(zhuǎn)換成可以存入數(shù)據(jù)庫表中的數(shù)據(jù)
    $newData = [];
    for($currentRow = 2; $currentRow <= $highestRow; $currentRow++){
        $rowData = [];
        foreach($sheetData[1] as $key => $currentColumn){
            $rowData[$currentColumn] = $sheetData[$currentRow][$key]; //$objPHPExcel->getActiveSheet()->getCell($key.$currentRow)->getValue();
        }

        $newData[] = $rowData;
    }

    // 插入數(shù)據(jù)
    $res = $this->userModel->import_user($newData);

    if(!$res){
        $this->ajax_return('500', 'Import Failed!');
    }
    $this->ajax_return('200', 'Import Success!');
}

至此，CI框架整合PHPExcel完畢喇喉。

【如若文檔有錯誤祖今，歡迎大家不吝賜教。本文檔是集網(wǎng)上各位大神的資源進(jìn)行整合的拣技，具體資源來源已經(jīng)忘記了衅鹿，如果發(fā)現(xiàn)有侵權(quán)等行為，請聯(lián)系我过咬，我將對應(yīng)處理，謝謝~~~】