class Excel extends Controller
{
public function index()
{
$fileName = ‘名册‘;
$headArr = [‘姓名‘,‘性别‘,‘年龄‘];
$data = db(‘mingce‘)->field(‘names,sex,nian‘)->select();
$this->excelExport($fileName,$headArr,$data);
}
public function aa(){
if(request()->isPost()){
$file = request()->file(‘file‘);
$info = $file->validate([‘size‘=>1567800,‘ext‘=>‘xls‘])->move(ROOT_PATH . ‘public‘ . DS . ‘uploads‘);
$filse = ‘./uploads/‘.$info->getSaveName();
// echo $filse;
$res = $this->getexcel($filse);
$arr = [];
foreach($res as $key=>$val){
$arr[$key][‘names‘] = $val[‘A‘];
$arr[$key][‘sex‘] = $val[‘B‘];
$arr[$key][‘nian‘] = $val[‘C‘];
}
// var_dump($arr);
db(‘mingce‘)->insertAll($arr);
}
return $this->fetch();
}
/**
* excel表格导出
* @param string $fileName 文件名称
* @param array $headArr 表头名称
* @param array $data 要导出的数据
* @author static7
* */
public function excelExport($fileName,$headArr,$data)
{
// // 导出文件名称
// = ‘a‘;
// // 数据表头
// $headArr = [‘姓名‘, ‘性别‘,‘年龄‘];
// // 具体数据
// $data = [
// [‘张三‘, ‘男‘,‘11‘],
// [‘李四‘, ‘女‘,‘12‘]
// ];
$fileName .= "_" . date("Y_m_d", time()) . ".xls";
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties(); // 设置Excel的属性；
// 设置表头
$key = ord("A");//65
foreach ($headArr as $v) {
$colum = chr($key);//‘A‘
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . ‘1‘, $v);
// $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . ‘1‘, $v);
$key += 1;
}
// 设置表头end
// 循环显示数据
$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++;
}
// 循环显示数据end
$fileName = iconv("utf-8", "gb2312", $fileName); // 重命名表
$objPHPExcel->setActiveSheetIndex(0); // 设置活动单指数到第一个表,所以Excel打开这是第一个表
ob_end_clean(); // 清空（擦除）缓冲区并关闭输出缓冲
ob_start(); // 打开输出控制缓冲
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();
}