经验教程

PHP使用PHPExcel上传下载Excel文件

悠悠楠杉

2020-12-12

2 评论

2,020 阅读

正在检测是否收录...

12/12

1、生成Excel并下载

<?php

include "./PHPExcel-1.8/Classes/PHPExcel/IOFactory.php";

$header = ['表头1','表头2','表头3'];
$data = [['数据1','数据2','数据3'],['数据1','数据2','数据3']];
$sheet_name = '数据';

// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
    ->setLastModifiedBy("Maarten Balliauw")
    ->setTitle("Office 2007 XLSX Test Document")
    ->setSubject("Office 2007 XLSX Test Document")
    ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
    ->setKeywords("office 2007 openxml php")
    ->setCategory("Test result file");

// 生成表头字母
$letter = [];
$n = 0;
for ($i = 'A'; $i <= 'Z'; $i++) {
    if ($n<count($header)){
        $letter[] = $i;
    }else{
        break;
    }
    $n++;
}

// Add some data
$objPHPExcel->setActiveSheetIndex(0);

// 循环设置表头数据
foreach ($header as $k => $v) {
    $objPHPExcel->setActiveSheetIndex(0)->setCellValue($letter[$k].'1', $v);
}

// 生成数据
for($i = 0;$i < count($data);$i++){
    foreach($data[$i] as $k2 => $v2){
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($letter[$k2].($i+2), $v2);
    }
}

// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle($sheet_name);

ob_end_clean(); //解决乱码核心

// Redirect output to a client’s web browser (Excel2007)
$filename = $sheet_name;
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="' . $filename . '.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

// $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');// $objWriter->save($filename.'.xls');// 保存到本地，上面的header()可以不用了

2、上传Excel文件

<?php
header("Content-type: text/html; charset=utf-8");

include "./PHPExcel-1.8/Classes/PHPExcel.php";

// var_dump($_FILES);
if (@is_uploaded_file($_FILES['upfile']['tmp_name'])) {
    $upfile = $_FILES["upfile"]; //获取数组里面的值
    $name = $upfile["name"]; //上传文件的文件名
    $type = $upfile["type"]; //上传文件的类型
    $size = $upfile["size"]; //上传文件的大小
    $tmp_name = $upfile["tmp_name"]; //上传文件的临时存放路径
    $dirname = 'uploads/';
    if(!is_dir($dirname)){//判断有没有这个文件夹
        mkdir($dirname);//新建文件夹
    }
    $file_name = __DIR__ . '/uploads/' . $name;
    move_uploaded_file($tmp_name, $file_name); //将上传到服务器临时文件夹的文件重新移动到新位置
    $error = $upfile["error"]; //上传后系统返回的值
    if ($error == 0) {
        echo "文件上传成功啦！<br>";
    } else {
        echo "上传失败";
    }
    // print($file_name);

    $objReader = \PHPExcel_IOFactory::createReader('Excel5'); //创建读取实例
    /*
     * log()//方法参数
     * $file_name excel文件的保存路径
     */
    $objPHPExcel = $objReader->load($file_name, $encode = 'utf-8'); //加载文件
    $sheet = $objPHPExcel->getSheet(0); //取得sheet(0)表
    $highestRow = $sheet->getHighestRow(); // 取得总行数
    $highestColumn = $sheet->getHighestColumn(); // 取得总列数

    // 获取excel文件的数据，$row=1代表从第一行开始获取数据
    for ($row = 1; $row <= $highestRow; $row++) {
        // Read a row of data into an array
        $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, false);
        //这里得到的rowData都是一行的数据，得到数据后自行处理，我们这里只打出来看看效果
        echo '<pre>';
        print_r($rowData);
    }
    unlink($file_name);//删除文件
    echo '<button onclick="location.href = \'./\'" style="margin-top: 10px">重新上传</button>';
}else{
?>
<html lang="zh-cn">
<head>
<meta charset="utf-8"/>
</head>
<body>
    <form action="" enctype="multipart/form-data" method="post">
    上传文件：<input type="file" name="upfile"/><br><!--上传多个文件：<input type="file" name="upfile[]" multiple/>-->
    <input type="submit" value="上传"/>
    </form>
</body>
<?php }?>

PHPExcel下载地址：https://zhizun.lanzouu.com/i5RxA2u45s4j

经验 PHP excel PHPExcel Excel上传 Excel下载

朗读