下载PHPExcel:GitHub - PHPOffice/PHPExcel: ARCHIVED
先在继承方法里写一个打包函数:
原则上你写在哪里都可以,但是你要调用的时候,写对就行了。记得引用
use think\facade\Request;
/**
* excel表格导出
* @param string $fileName 文件名称
* @param array $headArr 表头名称
* @param array $data 要导出的数据
* @author static7
**/
public function excelExport($fileName = '', $headArr = [], $data = []) {
require_once '../extend/PHPExcel/Classes/PHPExcel.php';
require_once '../extend/PHPExcel/Classes/PHPExcel/Writer/Excel2007.php';
//首先引入文件自然不用说。
$fileName .= "_" . date("Y_m_d", Request::instance()->time()) . ".xls";
$objPHPExcel = new \PHPExcel();
$objPHPExcel->getProperties();
$key = ord("A"); // 设置表头
foreach ($headArr as $v) {
$colum = chr($key);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
$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) { // 列写入
$objActSheet->setCellValue(chr($span) . $column, $value);
$span++;
}
$column++;
}
$fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表
$objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename='$fileName'");
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); // 文件通过浏览器下载
exit();
}
接着写一个导出方法,当你点击按钮的时候触发就可以了。
<a href="{:url('download')}" class="layui-btn">导出数据</a>
public function download() {
$name='会员信息表';
$header=['ID','用户名','上级ID','性别','手机','openid','等级ID','会员等级','是否禁用','余额','积分','注册时间'];
$data=db('users')
->alias('u')
->join('user_level l','u.level = l.level_id','left')
->field('u.*,l.level_name')
->select();
for($b=0;$b<count($data);$b++){
$newdata[$b]['ID']=$data[$b]['id'];
$newdata[$b]['username']=$data[$b]['username'];
$newdata[$b]['parentid']=$data[$b]['parentid'];
if($data['sex'] == 0){
$newdata[$b]['sex']= '男';
}elseif ($data[$b]['sex'] == 1) {
$newdata[$b]['sex']= '女';
}else{
$newdata[$b]['sex']= '未知';
}
$newdata[$b]['mobile']=$data[$b]['mobile'];
$newdata[$b]['openid']=$data[$b]['openid'];
$newdata[$b]['level']=$data[$b]['level'];
$newdata[$b]['level_name']=$data[$b]['level_name'];
$newdata[$b]['is_lock']=$data[$b]['is_lock'];
$newdata[$b]['balance']=$data[$b]['balance'];
$newdata[$b]['point']=$data[$b]['point'];
$newdata[$b]['reg_time']=$data[$b]['reg_time'];
}
$this->excelExport($name,$header,$newdata);//调用上面的方法,传值即可。
}
方法2:
public function downloadall(){
$xlsData = Db('users')->alias('u')
->join('user_level ul','u.level = ul.level_id','left')
->join('users u2','u.pid = u2.id','left')
->field('u.*,ul.level_name,u2.username as parentname')
->order('u.pid desc')
->select();
require_once '../extend/PHPExcel/Classes/PHPExcel.php';
require_once '../extend/PHPExcel/Classes/PHPExcel/Writer/Excel2007.php';
require_once '../extend/PHPExcel/Classes/PHPExcel/Worksheet/Drawing.php';
$objExcel = new \PHPExcel();
//set document Property
$objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
$objActSheet = $objExcel->getActiveSheet();
$key = ord("A");
$letter =explode(',',"A,B,C,D,E,F,G,H,I,J");
$arrHeader = array('编号','用户名','姓名','手机','地址','会员等级','上级编号','上级名称','注册时间');
//填充表头信息
$lenth = count($arrHeader);
for($i = 0;$i < $lenth;$i++) {
$objActSheet->setCellValue("$letter[$i]1","$arrHeader[$i]");
};
//填充表格信息
foreach($xlsData as $k=>$v){
$k +=2;
$objActSheet->setCellValue('A'.$k,$v['id']);
$objActSheet->setCellValue('B'.$k, $v['username']);
$objActSheet->setCellValue('C'.$k, $v['name']);
$objActSheet->setCellValue('D'.$k, $v['mobile']);
$objActSheet->setCellValue('E'.$k, $v['address']);
$objActSheet->setCellValue('F'.$k, $v['level_name']);
$objActSheet->setCellValue('G'.$k, $v['pid']);
$objActSheet->setCellValue('H'.$k, $v['parentname']);
$objActSheet->setCellValue('I'.$k, date('Y-m-d',$v['reg_time']));
// 表格高度
$objActSheet->getRowDimension($k)->setRowHeight(20);
}
$width = array(10,15,40,25,30);
//设置表格的宽度
$objActSheet->getColumnDimension('A')->setWidth($width[0]);
$objActSheet->getColumnDimension('B')->setWidth($width[2]);
$objActSheet->getColumnDimension('C')->setWidth($width[1]);
$objActSheet->getColumnDimension('D')->setWidth($width[1]);
$objActSheet->getColumnDimension('E')->setWidth(75);
$objActSheet->getColumnDimension('F')->setWidth($width[1]);
$objActSheet->getColumnDimension('G')->setWidth($width[1]);
$objActSheet->getColumnDimension('H')->setWidth($width[1]);
$objActSheet->getColumnDimension('I')->setWidth($width[1]);
$outfile = "信息列表".date('Y-m-d',time()).".xlsx";
ob_end_clean();
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="'.$outfile.'"');
header("Content-Transfer-Encoding: binary");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$objWriter->save('php://output');
}
以上。