php ci 导出excel,CI框架Excel表格导出导入类

/**

* 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("设置文档的分类");

}

}