参考地址：http://www.thinkphp.cn/topic/53674.html。
项目引入PhpExcel类库
/**
* 导出网站
* @author Manage<admin@jifenbang.net>
* @param array $sites
*/
protected function _export_site($sites){
// vendor("common.Org.PHPExcel");
$filename = get_config('name').'网站导出.'.date("Ymd");
Loader::autoload('./PHPExcel/PHPExcel');
$objPHPExcel = new \PHPExcel();
$objPHPExcel->getProperties()->setCreator(get_config('name'))
->setLastModifiedBy(get_config('name'))
->setTitle(get_config('name')."买卖链网站EXCEL导出")
->setSubject(get_config('name')."网站EXCEL导出")
->setDescription(get_config('name')."网站数据")
->setKeywords("excel")
->setCategory("result file");
// 第一行标题
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '网站名称')
->setCellValue('B1', '网站域名')
->setCellValue('C1', '网站分类')
->setCellValue('D1', '价格/月')
->setCellValue('E1', '百度BR')
->setCellValue('F1', '百度收录')
->setCellValue('G1', '谷歌PR');
// 设置列宽
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
// 设置标题加粗
$objPHPExcel->getActiveSheet()->getStyle('A1:G1')->getFont()->setBold(true);
// 设置yanse
$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);
$objPHPExcel->getActiveSheet()->getStyle('D1:D2001')->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_RED);
$objPHPExcel->getActiveSheet()->getStyle('E1:G1')->getFont()->getColor()->setARGB(\PHPExcel_Style_Color::COLOR_BLUE);
// 循环设置数据
foreach($sites as $k => $v){
$k += 2;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$k, $v['site_name'])
->setCellValue('B'.$k, $v['site_domain'])
->setCellValue('C'.$k, $v['cate']['name'])
->setCellValue('D'.$k, (request()->module()=='Base' ? $v['price'] : $v['price_member']))
->setCellValue('E'.$k, $v['stat']['br_tools'])
->setCellValue('F'.$k, $v['stat']['b_num'])
->setCellValue('G'.$k, $v['stat']['pr']);
}
$objPHPExcel->getActiveSheet()->setTitle('网站列表');
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
}
/*---------------------------------分割线 --------------------------------------------*/
/**
* 读取Excel文件到数组、导入
* @author Base<admin@jifenbang.net>
* @param 文件路径 $file
* @param 开始行 $start_row
*/
protected function _read_excel($file , $start_row=1)
{
Loader::autoload('./PHPExcel/PHPExcel');
if(!file_exists($file)){
return false;
}
$reader = \PHPExcel_IOFactory::createReader('Excel2007');
if(!$reader->canRead($file)){
$reader = \PHPExcel_IOFactory::createReader('Excel5');
if(!$reader->canRead($file)){
return false;
}
}
// 载入excel文件
$PHPExcel = $reader->load($file);
// 读取第一個工作表
$sheet = $PHPExcel->getSheet(0);
// 取得总行数
$highestRow = $sheet->getHighestRow();
// 取得总列数
$highestColumm = $sheet->getHighestColumn();
// 字母列转换为数字列 如:AA变为27
$highestColumm= \PHPExcel_Cell::columnIndexFromString($highestColumm);
//$columnName = \PHPExcel_Cell::stringFromColumnIndex($column);
$list = array();
// 行数是以第1行开始
for ($row = $start_row; $row <= $highestRow; $row++){
// 列数是以第0列开始
for ($column = 0; $column < $highestColumm; $column++) {
$val = $sheet->getCellByColumnAndRow($column, $row)->getValue();
// 第一列不为空即可
if($column < 1 && $val=="") continue;
$list[$row][] = $val;
}
}
return $list;
}
/*---------------------------------分割线 --------------------------------------------*/
//形式二：
//导出excel
public function exportExcel($list)
{
//查询数据
include Env::get('root_path') . 'extend/' . 'PHPExcel/PHPExcel.php';
include Env::get('root_path') . 'extend/' . 'PHPExcel/PHPExcel/IOFactory.php';
include Env::get('root_path') . 'extend/' . 'PHPExcel/PHPExcel/Writer/IWriter.php';
include Env::get('root_path') . 'extend/' . 'PHPExcel/PHPExcel/Writer/Excel5.php';
include Env::get('root_path') . 'extend/' . 'PHPExcel/PHPExcel/Writer/Excel2007.php';
include Env::get('root_path') . 'extend/' . 'PHPExcel/PHPExcel/Cell/DataType.php';
$objPHPExcel = new \PHPExcel();
//设置表头信息
$row = 1;
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A'.$row, 'ID')
->setCellValue('B'.$row, '用户')
->setCellValue('C'.$row, '商品')
->setCellValue('D'.$row, '支付通道')
->setCellValue('E'.$row, '金额(元)')
->setCellValue('F'.$row, '日期')
->setCellValue('G'.$row, '状态');
/*--------------开始从数据库提取信息插入Excel表中------------------*/
$row = 2; //定义一个row变量，目的是在循环输出数据是控制行数
//$count = count($sql); //计算有多少条数据
$activeSheet = $objPHPExcel->getActiveSheet();
foreach ($list as $order) {
$activeSheet->setCellValue('A' . $row, $order['id'])
->setCellValue('B' . $row, $order->user->email)
->setCellValue('C' . $row, $order->product->title)
->setCellValue('D' . $row, $order->type_text)
->setCellValue('E' . $row, $order->pay_fee/100)
->setCellValue('F' . $row, $order->create_time)
->setCellValue('G' . $row, $order->status_text);
$row++;
}
/*--------------下面是设置其他信息------------------*/
$objPHPExcel->getActiveSheet()->setTitle('导出'); //设置sheet的名称
$objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置
//$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来
$PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel, "Excel2007");
header('Cache-Control: max-age=0');//禁止缓存
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
//header('Content-Type:application/vnd.ms-excel');//告诉浏览器将要输出Excel03版本文件
$fileName = 'orders_' . date_time(time(), 'Y-m-d-Hi') . '.xlsx';
header("Content-Disposition: attachment;filename=\"$fileName\"");//告诉浏览器输出浏览器名称
$PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
}上述两种方法局限性大，只能服务于一种表的数据导出，只适用列数固定
Excel导入程序流程： Excel上传 -> excel读取类读取数据 -> 生成格式化数据 ->sql写入数据库
导出方式：1、下载流 savePath = 'php://output' ; 2、文件流 ：保存到指定目录下
/**
* 导出excel
* @param $titleArr 表头数组
* @param $dataArr 数据数组
* @param array $dataMetaArr 数据元数组，下标数组
* @param string $savePath 保存路径
* @param string $writerType 保存格式
* @return string
* @throws \Exception
*/
public function exportExcel($titleArr, $dataArr, $dataMetaArr=[], $savePath='php://output', $writerType='xlsx')
{
if (!is_array($titleArr) || !is_array($dataArr)) {
return '表格表头和数据须为数组!';
}
$dataColumnLen = $this->column_len($dataArr);
if (count($titleArr) != $dataColumnLen) {
return '表格表头和数据数组长度不一致!';
}
if (empty($dataMetaArr)) {
$dataMetaArr = range(0,count($titleArr) - 1,1);
// dump([$dataMetaArr]);
}
if (!in_array($writerType, ['xlsx', 'xls'])) {
$writerType = 'xlsx';
}
//查询数据
include Env::get('root_path') . 'extend/' . 'PHPExcel/PHPExcel.php';
include Env::get('root_path') . 'extend/' . 'PHPExcel/PHPExcel/IOFactory.php';
include Env::get('root_path') . 'extend/' . 'PHPExcel/PHPExcel/Writer/IWriter.php';
include Env::get('root_path') . 'extend/' . 'PHPExcel/PHPExcel/Writer/Excel5.php';
include Env::get('root_path') . 'extend/' . 'PHPExcel/PHPExcel/Writer/Excel2007.php';
include Env::get('root_path') . 'extend/' . 'PHPExcel/PHPExcel/Cell/DataType.php';
$objPHPExcel = new \PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);//设置当前sheet为第一个
/*--------------表头信息插入Excel表中------------------*/
$row = 1; //表行变量
$activeSheet = $objPHPExcel->getActiveSheet();
for ($i = 0; $i < count($titleArr); $i++) {
$col = chr(ord('A') + $i);
$activeSheet->setCellValue($col.$row, $titleArr[$i]);
}
/*--------------数据信息插入Excel表中------------------*/
$row = 2; //定义一个row变量
foreach ($dataArr as $data) {
for ($i = 0; $i < count($dataMetaArr); $i++) {
$col = chr(ord('A') + $i);
//$activeSheet->setCellValue($col . $row, $data[$dataMetaArr[$i]]);
$activeSheet->setCellValueExplicit($col . $row, $data[$dataMetaArr[$i]], \PHPExcel_Cell_DataType::TYPE_STRING);
// dump($data[$dataMetaArr[$i]]);
}
$row++;
}
// die();
$objPHPExcel->getActiveSheet()->setTitle('导出'); //设置sheet的名称
$objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置
if ($writerType == 'xls') {
$PHPWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来
} else {
$PHPWriter = \PHPExcel_IOFactory::createWriter( $objPHPExcel, "Excel2007");
}
//输出路径
if (strtolower($savePath) == 'php://output') {
if ($writerType == 'xls') {
header('Content-Type:application/vnd.ms-excel');//告诉浏览器将要输出Excel03版本文件
} else {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');//告诉浏览器输出07Excel文件
}
header('Cache-Control: max-age=0');//禁止缓存
$fileName = 'excel_' . date_time(time(), 'Y-m-d-Hi') . '.' . $writerType;
header("Content-Disposition: attachment;filename=\"$fileName\"");//告诉浏览器输出浏览器名称
$PHPWriter->save("php://output"); //表示输出到下载流
} else {
$fileName = 'excel_' . date_time(time(), 'Y-m-d-Hi') . '.' . $writerType;
$PHPWriter->save($savePath . DIRECTORY_SEPARATOR . $fileName); //表示在$path路径下面生成demo.xlsx文件
}
}