PhpSpreadsheet导出和读取Excel的简单使用

下载或安装
① github下载：https://github.com/PHPOffice/PhpSpreadsheet
② composer安装：

composer require phpoffice/phpspreadsheet

composer安装时注意php版本，php要开启fileinfo扩展。

一、Excel导出
这里使用的thinkphp5.1做个示范

<?php

use think\Controller;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\IOFactory;

class Test extends Controller
{
	public function saveExcel()
	{
		$title = ['第一行','第二行','第三行','第四行','第五行'];
		$data = [
	        ['row1' => '1','row2' => '2','row3' => '3','row4' => '4','row5' => '5'],
	        ['row1' => '1','row2' => '2','row3' => '3','row4' => '4','row5' => '5'],
	        ['row1' => '1','row2' => '2','row3' => '3','row4' => '4','row5' => '5'],
	        ['row1' => '1','row2' => '2','row3' => '3','row4' => '4','row5' => '5'],
	        ['row1' => '1','row2' => '2','row3' => '3','row4' => '4','row5' => '5']
	    ];
    	
		$spreadsheet = new Spreadsheet();
		$sheet = $spreadsheet->getActiveSheet();

	    //设置单元格内容
	    $titCol = 'A';
	    foreach ($title as $key => $value) {
	    	 //单元格内容写入
	        $sheet->setCellValue($titCol . '1', $value);
	        $titCol++;
	    }
	    $row = 2; 
	    foreach ($data as $item) {
	        $dataCol = 'A';
	        foreach ($item as $value) {
	            //单元格内容写入
	            $sheet->setCellValue($dataCol . $row, $value);
	            $dataCol++;
	        }
	        $row++;
	    }
	    //保存
	    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
	    $fileName = 'demo.xlsx';
	    //输出到浏览器
	    header('Content-type: text/html; charset=utf-8');
    	header('Content-Type: application/vnd.ms-excel');
    	header('Content-Disposition: attachment;filename=' . $fileName);
    	header('Cache-Control: max-age=0');
	    $writer->save('php://output');
	}
}

效果：

二、Excel读取

public function readExcel()
{
	if (!isset($_FILES['file'])) {
		return false;
	}
	$inputFileName = $_FILES['file']['tmp_name'];
    $spreadsheet = IOFactory::load($inputFileName);
    $sheetList = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);
    $list = [];
	foreach ($sheetList as $key => $value) {
		$list[] = array_values($value);
	}
	$titles = [];
	$indexArr = [];
	foreach ($list as $key => $value) {
		if ($key != 0) {
			$indexArr[] = $value;
		} else {
			$titles = $value;
		}
	}
	$relList = [];
	foreach ($indexArr as $key => $value) {
		$relList[] = array_combine($titles,$value);
	}
    var_dump($relList);
}

结果：

---

PhpSpreadsheet类库使用的功能非常广，这里只是一个简单的使用。