社区微信群开通啦,扫一扫抢先加入社区官方微信群
社区微信群
准备工作:
1.下载PHPExcel的SDK,下载地址:https://github.com/PHPOffice/PHPExcel。
/**
* 处理表单导出成表格
*/
function excel_handle($a = 2)
{
require_once 'vendor/PHPExcel/PHPExcel.php';
$PHPExcel = new PHPExcel(); //实例化PHPExcel类,类似于在桌面上新建一个Excel表格
$PHPSheet = $PHPExcel->getActiveSheet(); //获得当前活动sheet的操作对象
$PHPSheet->setTitle('处理表'); //给当前活动sheet设置名称
//表头
//给当前活动sheet填充数据,数据填充是按顺序一行一行填充的,假如想给A1留空,可以直接setCellValue(‘A1’,’’);
$PHPSheet->setCellValue('A1', 'ID')
->setCellValue('B1', '姓名')
->setCellValue('C1', '手机号')
->setCellValue('D1', '业务需求')
->setCellValue('E1', '处理人姓名')
->setCellValue('F1', '创建时间')
->setCellValue('G1', '是否处理')
;
$order = D('Site/Handle')
->alias('h')
->join('LEFT JOIN ly_site_category AS c ON FIND_IN_SET(c.id,h.cid)')
->join('LEFT JOIN ly_admin_user AS u ON u.id = h.uid')
->group('h.id')
->field('h.id,h.username,h.mobile,group_concat(distinct c.title SEPARATOR " | ") as c_title,u.username as uusername, h.create_time as create_time,is_handle')
->select();
$list = array(
'0' => '待处理',
'1' => '已处理',
);
foreach ($order as $k1 => $v1) {
$cell = $k1 + 2;
if ($v1['is_handle']){
$v1['is_handle'] = '已处理';
} else {
$v1['is_handle'] = '待处理';
}
$PHPSheet->setCellValue('A' . $cell, $v1['id'])
->setCellValue('B' . $cell, $v1['username'])
->setCellValue('C' . $cell, $v1['mobile'])
->setCellValue('D' . $cell, $v1['c_title'])
->setCellValue('E' . $cell, $v1['uusername'])
->setCellValue('F' . $cell, date('Y-m-d H:i:s', $v1['create_time']))
->setCellValue('G' . $cell, $v1['is_handle'])
;
}
switch ($a) {
case '1':
$PHPWriter = PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel2007'); //按照指定格式生成Excel文件,‘Excel2007’表示生成2007版本的xlsx,
$PHPWriter->save('handle.xlsx'); //表示在$path路径下面生成demo.xlsx文件
break;
case '2':
// 生成2007excel格式的xlsx文件
$PHPWriter = PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5'); //按照指定格式生成Excel文件,‘Excel2007’表示生成2007版本的xlsx
header('Content-Type: text/html;charset=utf-8');
header('Content-Type: xlsx');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="handle.xls"');
header('Cache-Control: max-age=0');
$PHPWriter->save("php://output");
break;
}
}
可以先在自己环境尝试后再考虑应用
另外,推荐a=2
//导出Excel
public function out()
{
$path = dirname(__FILE__); //找到当前脚本所在路径
vendor("PHPExcel.PHPExcel.PHPExcel");
vendor("PHPExcel.PHPExcel.Writer.IWriter");
vendor("PHPExcel.PHPExcel.Writer.Abstract");
vendor("PHPExcel.PHPExcel.Writer.Excel5");
vendor("PHPExcel.PHPExcel.Writer.Excel2007");
vendor("PHPExcel.PHPExcel.IOFactory");
$objPHPExcel = new PHPExcel();
$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'ID')
->setCellValue('B1', '姓名')
->setCellValue('C1', '性别')
->setCellValue('D1', '身高')
->setCellValue('E1', '体重')
->setCellValue('F1', '创建时间')
;
$arr = [
['id'=>1,'name'=>'a','sex'=>'男','shengao'=>173,'tizhong'=>120],
['id'=>2,'name'=>'b','sex'=>'女','shengao'=>160,'tizhong'=>92],
['id'=>3,'name'=>'c','sex'=>'女','shengao'=>163,'tizhong'=>100]
];
foreach ($arr as $k1 => $v1) {
$cell = $k1 + 2;
$objPHPExcel->getActiveSheet()->setCellValue('A' . $cell, $v1['id'])
->setCellValue('B' . $cell, $v1['name'])
->setCellValue('C' . $cell, $v1['sex'])
->setCellValue('D' . $cell, $v1['shengao'])
->setCellValue('E' . $cell, $v1['tizhong'])
->setCellValue('F' . $cell, date('Y-m-d H:i:s'))
;
}
/*--------------下面是设置其他信息------------------*/
$objPHPExcel->getActiveSheet()->setTitle('productaccess'); //设置sheet的名称
$objPHPExcel->setActiveSheetIndex(0); //设置sheet的起始位置
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //通过PHPExcel_IOFactory的写函数将上面数据写出来
$PHPWriter = PHPExcel_IOFactory::createWriter( $objPHPExcel,"Excel2007");
header('Content-Disposition: attachment;filename="设备列表.xlsx"');
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$PHPWriter->save("php://output"); //表示在$path路径下面生成demo.xlsx文件
}
需要改动插入数据的话,直接改动这一块就好了
需要使用Excel导入的,可以去这里转转:https://www.cnblogs.com/yayaa/p/8006919.html
如果觉得我的文章对您有用,请随意打赏。你的支持将鼓励我继续创作!