php导入excel表,保存进数据库,使用phpexcel插件

安装phpexcel插件

composer require phpoffice/phpexcel -vvv

表单

<script>
        function test()
        {
            alert('点击确定后请勿刷新')
            document.getElementById("myform").submit();

        }
</script>
 <!--  上面是js -->
<div style="float: right;position: relative">
    <form action="/index/file" id="myform" name="myfrom" method="post" enctype="multipart/form-data" style="position: relative;top: 1px;">
        <button style="width:150px">导入本月考勤表</button>
        <input type="file"  name="file" style="position: absolute;top: 0;opacity: 0" onchange="test()">
    </form>
</div>

php接收导入的excel文件

    ///接受文件
    public function actionFile(){
        if ($_FILES["file"]["error"] > 0)
        {
            echo "文件上传失败<br />";
        }else{
            if ($_FILES["file"]['type'] != "application/vnd.ms-excel"){
                echo "<script>alert('必须是excel表（.xls格式）');location.href='/index/index';</script>";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

希望这篇文章对你有帮助