先确定项目有没有下载phpexcel这个类库
我的是放在根目录
<?php
class ExcelphpAction extends AdminAction{
public function index(){
$this->display();
}
//导入
public function upexcel(){
if (!D('表名')->autoCheckToken($_POST)){
$this->error('表单已提交,请勿重复提交');
}
set_time_limit(0);
if (! empty ( $_FILES ['file_stu'] ['name'] )){
$tmp_file = $_FILES ['file_stu'] ['tmp_name'];
$file_types = explode ( ".", $_FILES ['file_stu'] ['name'] );
$file_type = $file_types [count ( $file_types ) - 1];
/*判别是不是.xls文件,判别是不是excel文件*/
if (strtolower ( $file_type ) != "xls" and strtolower ( $file_type ) != "xlsx"){
$this->error ( '不是Excel文件,重新上传' );
}
/*设置上传路径*/
$savePath = APP_PATH . 'public/Excel/';
if (!file_exists($savePath)){
mkdir($savePath);
}
/*以时间来命名上传的文件*/
$str = date ( 'Ymdhis' );
$file_name = $str . "." . $file_type;
/*是否上传成功*/
if (! copy ( $tmp_file, $savePath . $file_name )){
$this->error ( '上传失败' );
}
require_once APP_PATH.'PHPExcel/PHPExcel.php';
require_once APP_PATH.'PHPExcel/PHPExcel/Writer/Excel5.php'; // 用于其他低版本xls
require_once APP_PATH.'PHPExcel/PHPExcel/Writer/Excel2007.php'; // 用于 excel-2007 格式
$objPHPExcel = new PHPExcel();
$PHPReader = new PHPExcel_Reader_Excel2007(); //默认是excel2007
$filePath=$savePath.$file_name;
if(!$PHPReader->canRead($filePath)){
$PHPReader = new PHPExcel_Reader_Excel5(); //如果不成功的时候用以前的版本来读取
if(!$PHPReader->canRead($filePath)){
echo 'no Excel';
return ;
}
}
$PHPExcel = $PHPReader->load($filePath);
$currentSheet = $PHPExcel->getSheet(0);
//取得一共有多少列
$allColumn = $currentSheet->getHighestColumn();
//取得一共有多少行
$allRow = $currentSheet->getHighestRow();
//循环读取数据,默认是utf-8输出
//从第二行开始
for($currentRow = 2;$currentRow<=$allRow;$currentRow++){
/*for($currentColumn='A';$currentColumn<=$allColumn;$currentColumn++){
$address = $currentColumn.$currentRow;
var_dump($address."@");
var_dump($currentSheet->getCell($address)->getValue()."\t") ;
} */
$xls_title=$currentSheet->getCell('A'.$currentRow)->getValue();
$xls_body=$currentSheet->getCell('B'.$currentRow)->getValue();
$xls_thumb=$currentSheet->getCell('C'.$currentRow)->getValue();
$data=array(
'title'=>$xls_title,
'body'=>$xls_body,
'thumb'=>$xls_thumb,
);
D('表名')->add($data);
}
$this->success('excel导入成功',U('index'));
exit;
}else{
$this->error('请选择excel表格');
}
}
//导出
public function export(){
$info=D('表名')->select();
$data[]=array('字段名','字段名','字段名');
foreach ($info as $k => $v) {
$data[]=array($v['字段'],$v['字段'],$v['字段']);
}
// $data = array(
// array(NULL, 2010, 2011, 2012),
// array('Q1', 12, 15, 21),
// array('Q2', 56, 73, 86),
// array('Q3', 52, 61, 69),
// array('Q4', 30, 32, 0),
// );
$this->create_xls($data);
}
public function create_xls($data,$filename='simple.xls'){
ini_set('max_execution_time', '0');
require_once APP_PATH.'PHPExcel/PHPExcel.php';
require_once APP_PATH.'PHPExcel/PHPExcel/Writer/Excel5.php'; // 用于其他低版本xls
require_once APP_PATH.'PHPExcel/PHPExcel/Writer/Excel2007.php'; // 用于 excel-2007 格式
$filename=str_replace('.xls', '', $filename).'.xls';
$phpexcel = new PHPExcel();
$phpexcel->getProperties()
->setCreator("Maarten Balliauw")
->setLastModifiedBy("Maarten Balliauw")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
$phpexcel->getActiveSheet()->fromArray($data);
$phpexcel->getActiveSheet()->setTitle('Sheet1');
$phpexcel->setActiveSheetIndex(0);
// $phpexcel->getActiveSheet()->getRowDimension(2)->setRowHeight(35);//设置行高
ob_end_clean();
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=$filename");
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the pastheader
('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modifiedheader
('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objwriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
$objwriter->save('php://output');
exit;
}
}