先下载PHPExcel
1) 压缩包里的Classes文件夹放到application\libraries\目录下,目录结构如下:
-- application\libraries\PHPExcel.php
-- application\libraries\PHPExcel (文件夹)
2)修改application\libraries\PHPExcel\IOFactory.php 文件
-- 将其类名从PHPExcel_IOFactory改为IOFactory,遵从CI类命名规则。
-- 将其构造函数改为public
-- application\libraries\PHPExcel.php
-- application\libraries\PHPExcel (文件夹)
2)修改application\libraries\PHPExcel\IOFactory.php 文件
-- 将其类名从PHPExcel_IOFactory改为IOFactory,遵从CI类命名规则。
-- 将其构造函数改为public
控制器里的方法
/*
* 生成下载excel文件
* $filename="生成的excel名称";
* $headArr=array("表头","名称");
* $data 要显示的数据
* array(array('username'=>1,'pwd'=>2),array(...)..);
* 调用方法:
* $this->getExcel($filename,$headArr,$data);
*
*/
public function getExcel($fileName = "test", $headArr = array("username","pwd"), $data = array(array('username'=>1,'pwd'=>2))) {
date_default_timezone_set('Asia/Shanghai');
//对数据进行检验
if (empty($data) || !is_array($data)) {
die("data must be a array");
}
//检查文件名
if (empty($fileName)) {
exit;
}
$date = date("Y_m_d", time());
$fileName .= "_{$date}.xls";
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
//创建PHPExcel对象
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties();
//设置表头
$key = ord("A");
foreach ($headArr as $v) {
$colum = chr($key);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
$key += 1;
}
$column = 2;
$objActSheet = $objPHPExcel->getActiveSheet();
foreach ($data as $key => $rows) { //行写入
$span = ord("A");
foreach ($rows as $keyName => $value) { // 列写入
$j = chr($span);
$objActSheet->setCellValue($j . $column, $value);
$span++;
}
$column++;
}
$fileName = iconv("utf-8", "gb2312", $fileName);
//重命名表
//设置活动单指数到第一个表,所以Excel打开这是第一个表
$objPHPExcel->setActiveSheetIndex(0);
ob_end_clean(); //清除缓冲区,避免乱码
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=\"$fileName\"");
header('Cache-Control: max-age=0');
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); //文件通过浏览器下载
exit;
}
</pre><pre name="code" class="php">
<pre name="code" class="php">/*
* 导入execl文件获取表中数据
* @param [file] $file [excel文件存放路径]
* @return [array] [返回表中数据]
*/
public function execExcel() {
date_default_timezone_set('Asia/Shanghai');
$file = "./uploads/test.xls";
//检查文件
if (empty($file)) {
exit;
}
$this->load->library('PHPExcel');
$this->load->library('PHPExcel/IOFactory');
// $CI->load->library('PHPExcel/Reader/Excel5');
// 创建对象
$objPHPExcel = new IOFactory();
$readerType = $objPHPExcel::identify($file);
$objReader = $objPHPExcel::createReader($readerType);
// 读文件
$objPHPExcel = $objReader->load($file);
$objWorksheet = $objPHPExcel->getActiveSheet(0);
// 总行数
$highestRow = $objWorksheet->getHighestRow();
// 总列数
$highestColumn = $objWorksheet->getHighestColumn();
$highestColumnIndex = range('A', $highestColumn);
$data = array();
// 从第二行开始,第一行一般是表头
for ($row = 2; $row <= $highestRow; $row++) {
$array = array();
foreach ($highestColumnIndex as $value) {
$address = $value . $row;
$array[] = $objWorksheet->getCell($address)->getFormattedValue();
}
array_push($data, $array);
}
return $data;
}