安装phpexcel插件
composer require phpoffice/phpexcel -vvv
表单
function test()
{
alert('点击确定后请勿刷新')
document.getElementById("myform").submit();
}
导入本月考勤表
php接收导入的excel文件
///接受文件
public function actionFile(){
if ($_FILES["file"]["error"] > 0)
{
echo "文件上传失败
";
}else{
if ($_FILES["file"]['type'] != "application/vnd.ms-excel"){
echo "";exit;
}
if (file_exists("upload/" . $_FILES["file"]["name"]))
{
///如果文件存在,就删除文件(这儿建议做定时删除任务)
unlink("./upload/" . $_FILES["file"]["name"]);
}
///移动文件
move_uploaded_file($_FILES["file"]["tmp_name"],"upload/" . $_FILES["file"]["name"]);
$filename= "upload/" . $_FILES["file"]["name"];
$file = \Yii::getAlias("@webroot").'/'.$filename;
$objReader = new \PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader->load($file);
$objWorksheet = $objPHPExcel->getSheet(0);
$highestRow = $objWorksheet->getHighestRow();//最大行数，为数字
$highestColumn = $objWorksheet->getHighestColumn();//最大列数 为字母
$highestColumnIndex = \PHPExcel_Cell::columnIndexFromString($highestColumn); //将字母变为数字
$tableData = [];
for($row = 1;$row<=$highestRow;$row++){
for($col=0;$col< $highestColumnIndex;$col++){
$tableData[$row][$col] = $objWorksheet->getCellByColumnAndRow($col,$row)->getValue();
}
}
//$tableData就是excel里面的数据,接下来只需要遍历进数据库就OK
foreach ($tableData as $key=>$row){
//排除第一行
if ($key !=1){
$model=new Qiandao();
$model->shijian=$row[0];
$model->time=strtotime($row[0]);
$model->bumen=$row[1];
$model->tijiaoren=$row[2];
$model->didian=$row[3];
$model->mingchen=$row[4];
$model->beizhu=$row[5];
$model->huibao=$row[6];
$model->img=$row[7];
$model->save();
}
}
}
}
生成一个电子表格
use PHPExcel;
$headerArr = ['编号','用户名','生成时间'];
$fileName = "abc.xls";
$objPHPExcel = new PHPExcel();
$objProps = $objPHPExcel->getProperties();
$key = ord('A');
foreach($headerArr as $v){
$colum = chr($key);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum.'1',$v);
$key += 1;
}
$objPHPExcel->getActiveSheet()->setTitle('Simple');
$objPHPExcel->setActiveSheetIndex(0);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment; filename=\"$fileName\"");
header('Cache-Control: max-age=0');
$writer = \PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
$writer->save('php://output');
image.png
希望这篇文章对你有帮助
