PHP上传Excel-xls-xlsx

PHP上传Excel xls格式和xlsx格式

---

PHPExcle插件

引入插件
require_once("assets/PHPExcel-1.8/Classes/PHPExcel.php");

视图层代码

<form action="test.php" method="post" enctype="multipart/form-data">
	<span>导入Excel表格</span>
	<input type="file" name="userfile" id="userfile" value="" />
	<input type="submit" name="submin" id="submin" value="Submit" />
</form>

Excle用英文命名

处理代码

<?php
header("Content-type:text/html;charset=utf-8");
require_once("PHPExcel-1.8/Classes/PHPExcel.php");
$name=$_FILES['userfile']['name'];//名字
$suffixName=explode('.', $name)[1];//后缀名
$filePath='upload/'.$_FILES["userfile"]["name"];//文件存储路径
if($suffixName=="xls" || $suffixName=="xlsx"){
	if (is_uploaded_file($_FILES["userfile"]["tmp_name"])) {
		echo "已经上传到临时文件夹";echo "<hr>";
		if (!move_uploaded_file($_FILES["userfile"]["tmp_name"],$filePath)) {
			echo "移动失败";
		}else{
			 echo "移动到".$filePath."成功";echo "<hr>";
			//调用解析方法 返回数组
			$ExcleArr=analysisExcel($filePath);
			echo "<pre>";
			print_r($ExcleArr);
			echo "</pre>";
		}
	} else {
		echo "上传临时文件失败";
	}
}else{
	echo "<a href='http://localhost/Excel/upload.html'>文件上传格式不正确，请重新上传</a>";
}
function analysisExcel($filePath){
	$file_type=explode('.',$filePath)[1];
	$objReader='';
	//根据上传类型做不同处理
	if ($file_type == 'xls') {
		$objReader = \PHPExcel_IOFactory::createReader('Excel5');//创建读取实例
	}
	if ($file_type == 'xlsx') {
		$objReader = new \PHPExcel_Reader_Excel2007();
	}
    $objPHPExcel = $objReader->load($filePath,$encode='utf-8');//加载文件
    $sheet = $objPHPExcel->getSheet(0);//取得sheet(0)表
    $highestRow = $sheet->getHighestRow(); // 取得总行数
    $highestColumn = $sheet->getHighestColumn(); // 取得总列数
    $data=[];
    for($i=1;$i<=$highestRow;$i++)
    {
        array_push($data, $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue());
    }
    return $data;
}
?>

下载Excel

$db = db::getInstance();
        $needId=$_COOKIE['needId'];
        $arrId=explode('-',$needId);
        array_shift($arrId);
        $data=[];
        $order_count=0;//订单总数
        $income=0.00;//预期收入
        $income_cal=0.00;//结算收入
        $cal_count=0;//结算个数
        foreach($arrId as $value){
            $item="SELECT Id,empName,title,author,order_count,title_time,income,cal_count,cal_income,keyname from list_order WHERE  Id ='". intval($value)."'";
            $result=$db->select($item,1);
            array_push($data,$result);
            $order_count+=$result['order_count'];
            $income+=$result['income'];
            $income_cal+=$result['cal_income'];
            $cal_count+=$result['cal_count'];
        }
        $objPHPExcel=new \PHPExcel();
        $objPHPExcel->getProperties()
            ->setCreator("WOLF")
            ->setLastModifiedBy("WOLF")
            ->setTitle("Office 2007 XLSX Test Document")
            ->setSubject("Office 2007 XLSX Test Document")
            ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
        $objActSheet = $objPHPExcel->setActiveSheetIndex(0); //填充表头
        $objActSheet->setCellValue('A1','序号');
        $objActSheet->setCellValue('B1','员工姓名');
        $objActSheet->setCellValue('C1','文章标题');
        $objActSheet->setCellValue('D1','标题作者名称');
        $objActSheet->setCellValue('E1','订单个数');
        $objActSheet->setCellValue('F1','标题时间');
//        $objActSheet->setCellValue('G1','成交金额');
//        $objActSheet->setCellValue('H1','结算金额');
        $objActSheet->setCellValue('G1','预期收入');
        $objActSheet->setCellValue('H1','结算个数');
        $objActSheet->setCellValue('I1','结算收入');
        $objActSheet->setCellValue('J1','keyname');
        //数据

        for($i=0;$i<count($data);$i++) {
            $item=$data[$i];
            $key = $i+2;
            $objActSheet->setCellValue('A' . $key, $item['Id']);
            $objActSheet->setCellValue('B' . $key, $item['empName']);
            $objActSheet->setCellValue('C' . $key, $item['title']);
            $objActSheet->setCellValue('D' . $key, $item['author']);
            $objActSheet->setCellValue('E' . $key, $item['order_count']);
            $objActSheet->setCellValue('F' . $key, date("Y-m-d",$item['title_time']));
            $objActSheet->setCellValue('G' . $key, $item['income']);
            $objActSheet->setCellValue('H' . $key, $item['cal_count']);
            $objActSheet->setCellValue('I' . $key, $item['cal_income']);
            $objActSheet->setCellValue('J' . $key, $item['keyname']);
        }
        //填充计算后的信息
        $countArr[0][0]['value']=$order_count;//订单总数
        $countArr[0][0]['key']="订单总数";//订单总数
        $countArr[0][1]['value']=$cal_count;//结算个数
        $countArr[0][1]['key']="结算个数";//结算个数
        $countArr[1][0]['value']=$income;//预期收入
        $countArr[1][0]['key']="预期收入";//预期收入
        $countArr[1][1]['value']=$income_cal;//结算收入
        $countArr[1][1]['key']="结算收入";//结算收入
        for($j=0;$j<count($countArr);$j++){
            $item=$countArr[$j];
            $key = $j+count($data)+3;
            $objActSheet->setCellValue('E' . $key, $item[0]['key']);
            $objActSheet->setCellValue('F' . $key, $item[0]['value']);
            $objActSheet->setCellValue('G' . $key, $item[1]['key']);
            $objActSheet->setCellValue('H' . $key, $item[1]['value']);
        }
        //4.输出
        $objPHPExcel->getActiveSheet()->setTitle('详细信息');
        $objPHPExcel->setActiveSheetIndex(0);
        $day=date("Y-m-d H:i:s");
        $filename = $day.'.xls';
        ob_end_clean();//清除缓冲区,避免乱码
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename='.$filename);
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
        $objWriter->save('php://output');