测试代码如下:
$data = [
["标题1", "标题2"],
["标题1111", "标题2222"],
];
$fileName = 'abc';
(new IExcel())->write_2007($data, $fileName);
$fileName = 'abc.xlsx';
$result = (new IExcel())->read($fileName);
var_dump($result);
<?php
use Exception;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Borders;
use PhpOffice\PhpSpreadsheet\Style\Fill;
/**
* IExcel - excel 文件下载处理类
* 注意：
* 1，全局编码为UTF-8,否则可能出错
* 2，支持读写excel2003,2007
* @version 1.0
* https://phpspreadsheet.readthedocs.io/en/latest/
*/
class IExcel {
// 表格标题样式
private static $headStyle = [
'borders' => [
'diagonalDirection' => Borders::DIAGONAL_BOTH,
'allBorders' => [
'borderStyle' => Border::BORDER_THIN,
],
],
'fill' => [
'fillType' => Fill::FILL_GRADIENT_LINEAR,
'startColor' => [
'argb' => '66CCFF',
],
'endColor' => [
'argb' => '66CCFF',
],
],
];
/**
* 文件下载
* @param array $data :数组数据
* @param string $filename :文件名称
* @param boolean $is_down :是否下载
* @param int $version
* @return bool
*/
private function write($data, $filename = 'export', $is_down = true, $version=2007) {
$memory = '512M';
ini_set('memory_limit', $memory);
/* 数据维度检测 */
$depth = array_depth($data);
if ($depth <= 0 || $depth >= 4) {
echo "depth:$depth. 超过预定范围";
return false;
}
/* 维度补齐到3维数组 */
if ($depth == 1) {
$data = array(array($data));
} elseif($depth == 2) {
$data = array($data);
}
$spreadsheet = new Spreadsheet();
$index = 0;
foreach ($data as $sheet_data) {
$spreadsheet->setActiveSheetIndex($index);
$sheet = $spreadsheet->getActiveSheet();
$currentSpan = '';
$i = 1;
foreach ($sheet_data as $row) {
$j = 'A';
// 写入一行数据
foreach ($row as $v) {
$currentSpan = $j;
// 以字符串写入
$sheet->setCellValueExplicit($j.$i, $v, DataType::TYPE_STRING);
// https://www.php.net/manual/zh/language.operators.increment.php
++$j;
}
++$i;
}
// 头部样式设置
$headRange = "A1:{$currentSpan}1";
var_dump($headRange);
$sheet->getStyle($headRange)->applyFromArray(self::$headStyle);
}
if ($version == 2003) {
$filename .= '.xls';
$writer = IOFactory::createWriter($spreadsheet, 'Xls');
} else {
$filename .= '.xlsx';
$writer= IOFactory::createWriter($spreadsheet, 'Xlsx');
}
if (!$is_down) {
try {
$writer->save($filename);
} catch (Exception $e) {
echo $e->getMessage();
return false;
}
return true;
}
/* 如果是IE,进行转码,防止乱码 */
if (strpos($_SERVER["HTTP_USER_AGENT"],"MSIE")) {
$filename = rawurlencode($filename);
}
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header("Content-Disposition:attachment;filename=\"$filename\"");
header("Content-Transfer-Encoding:binary");
$writer->save('php://output');
return true;
}
/**
* write_2007 - 写2007格式
* @param array $data : 数据
* @param string $filename : 文件名
*/
public function write_2007($data, $filename='export_2007') {
$this->write($data, $filename, false, 2007);
}
/**
* down_2007 - 下载2007文件格式
* @param array $data : 数据
* @param string $filename : 文件名
*/
public function down_2007($data, $filename='export_2007') {
$this->write($data, $filename, true, 2007);
}
/**
* write_2003 - 写2003格式
* @param array $data : 数据
* @param string $filename : 文件名
*/
public function write_2003($data, $filename='export_2003') {
$this->write($data, $filename, false, 2003);
}
/**
* down_2003 - 下载2003文件格式
* @param array $data : 数据
* @param string $filename : 文件名
*/
public function down_2003($data, $filename='down_2003') {
$this->write($data, $filename, true, 2003);
}
/**
* read - 读取excel文件（自动识别excel2007和excel2003)
* 推荐文件只用字符串格式，或者数字，其他格式需要检查是否有误
* @param string $filename 文件名
* @param bool $multi_sheet 是否多个sheet
* @return mixed -1,文件不存在; -2,不能识别的文件; array, 3维数组
*/
public function read($filename, $multi_sheet = false) {
if (!file_exists($filename)) {
return -1;
}
$memory = '1024M';
ini_set('memory_limit', $memory);
$reader = IOFactory::createReader('Xlsx');
if (!$reader->canRead($filename)) {
$reader = IOFactory::createReader('Xls');
if (!$reader->canRead($filename)) {
return -2;
}
}
$excel = $reader->load($filename);
$sheet_list = $excel->getAllSheets();
$data = array();
foreach ($sheet_list as $sheet) {
$sheet_data = $sheet->toArray();
$data[] = $sheet_data;
}
return $multi_sheet ? $data : $data[0];
}
}
/**
* 返回数组深度
* @param $data
* @return int
*/
function array_depth($data)
{
return is_array($data) ? 1 + array_depth(current($data)) : 0;
}
