CI框架Excel表格导出导入类

PHPExcel下载路径：

git：  https://github.com/PHPOffice/PHPExcel

压缩包：https://codeload.github.com/PHPOffice/PHPExcel/zip/1.8

 

<?php
/**
 * Created by PhpStorm.
 * User: 18110
 * Date: 2019/2/20
 * Time: 14:07
 */
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('PRC');

/** 引入PHPExcel */
require_once str_replace('\\' , '/' , APPPATH) . 'libraries/tools/PHPExcel-1.8/Classes/PHPExcel.php';
include str_replace('\\' , '/' , APPPATH) . 'libraries/tools/PHPExcel-1.8/Classes/PHPExcel/IOFactory.php';

class Excel
{
    public $objPHPExcel;
    public function __construct()
    {
        $this->objPHPExcel = new PHPExcel();
    }

    /**
     * 添加数据
     * @param $pCoordinate
     * @param $pValue
     * @throws PHPExcel_Exception
     */
    public function setCellValue($pCoordinate , $pValue){
        //根据excel坐标，添加数据
        $this->objPHPExcel->setActiveSheetIndex(0)->setCellValue($pCoordinate, $pValue);
    }

    /**
     * 导出格式
     * @throws PHPExcel_Reader_Exception
     * @throws PHPExcel_Writer_Exception
     */
    public function exportFormat($format = '2007'){
        $fileName = uniqid();
        $file_path = '/data/www/download.qinjiakonggu.com/excel/';
        if ($format == '2007'){
            $suffix = '.xlsx';
            // 保存Excel 2007格式文件，保存路径为$file_path，名字为$fileName
            $objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel2007');
            $objWriter->save($file_path.$fileName.$suffix);
        }else{
            $suffix = '.xls';
            // 保存Excel 95格式文件
            $objWriter = PHPExcel_IOFactory::createWriter($this->objPHPExcel, 'Excel5');
            $objWriter->save($file_path.$fileName.$suffix);
        }
        return $fileName.$suffix;
    }

    /**
     * 将Excel表格导入到数据库
     * @param $inputFileName
     * @throws PHPExcel_Exception
     */
    public function importFile($inputFileName){
        // 读取excel文件
        try {
            $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
            $objReader = PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($inputFileName);
        } catch(Exception $e) {
            die('加载文件发生错误："'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
        }

        // 确定要读取的sheet，什么是sheet，看excel的右下角，真的不懂去百度吧
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow();
        $highestColumn = $sheet->getHighestColumn();
        // 获取一行的数据
        for ($row = 1; $row <= $highestRow; $row++){
            // Read a row of data into an array
            $arr = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
            $data[] = $arr[0];
        }
        return $data;
    }



    /**
     * 设置sheet名称
     * @throws PHPExcel_Exception
     */
    public function setSheetTitle($sheet = 1){
        // 重命名工作sheet
        $this->objPHPExcel->getActiveSheet()->setTitle('sheet'.$sheet);
        // 设置第一个sheet为工作的sheet
        $this->objPHPExcel->setActiveSheetIndex(0);
    }

    /**
     * 设置文档信息，这个文档信息windows系统可以右键文件属性查看
     */
    public function setAttribute(){
        $this->objPHPExcel->getProperties()->setCreator("作者焦雨康")
            ->setLastModifiedBy("最后更改者")
            ->setTitle("文档标题")
            ->setSubject("文档主题")
            ->setDescription("文档的描述信息")
            ->setKeywords("设置文档关键词")
            ->setCategory("设置文档的分类");
    }
}