composer require phpoffice/phpspreadsheet
PhpSpreadsheet中文简介
phpexcel由于版本陈旧性能低下官方放弃维护,转而开发PhpSpreadsheet用了最新得psr标准因而对php版本不向下兼容需要注意!
PhpSpreadsheet是一个用纯PHP编写的库,提供了一组类,使您可以读取和写入不同的电子表格文件格式
PhpSpreadsheet提供了丰富的API接口,可以设置诸多单元格以及文档属性,包括样式、图片、日期、函数等等诸多应用,总之你想要什么样的Excel表格,PhpSpreadsheet都能做到
使用PhpSpreadsheet开发的PHP要求7.1或更高版本,并且支持链式操作
PhpSpreadsheet 支持的文件格式
文件路径extend/Excel.php
<?php
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use think\exception\ValidateException;
class Excel
{
/**
* 导入excel表格
* @param array $file 文件路径
* @return array|string
*/
protected function importExcel($file)
{
try {
// 截取后缀
$fileExtendName = substr(strrchr($file, '.'), 1);
// 有Xls和Xlsx格式两种
if ($fileExtendName == 'xlsx') {
$objReader = IOFactory::createReader('Xlsx');
} else {
$objReader = IOFactory::createReader('Xls');
}
// 设置文件为只读
$objReader->setReadDataOnly(TRUE);
// 读取文件,tp6默认上传的文件,在runtime的相应目录下,可根据实际情况自己更改
$objPHPExcel = $objReader->load($_SERVER['DOCUMENT_ROOT'] . $file);
//excel中的第一张sheet
$sheet = $objPHPExcel->getSheet(0);
// 取得总行数
$highestRow = $sheet->getHighestRow();
// 取得总列数
$highestColumn = $sheet->getHighestColumn();
Coordinate::columnIndexFromString($highestColumn);
$lines = $highestRow - 1;
if ($lines <= 0) {
return '数据不能为空';
}
// 直接取出excle中的数据
$data = $objPHPExcel->getActiveSheet()->toArray();
// 删除第一个元素(表头)
array_shift($data);
// 返回结果
return $data;
} catch (ValidateException $e) {
return $e->getMessage();
}
}
/**
* 导出excel表格
* @param array $header 设置表头数据
* @param array $data 生成的表格数据
* @param bool $type 文件类型,true为Xlsx,false为Xls,默认为true
* @param string $fileName 文件名,默认为数据
*/
protected function export($header = [], $data = [], $type = true, $fileName = "数据")
{
// 实例化类
$preadsheet = new Spreadsheet();
// 创建sheet
$sheet = $preadsheet->getActiveSheet();
// 生成表头字母
$letter = [];
$n = 0;
for ($i = 'A'; $i <= 'Z'; $i++) {
if ($n<count($header)){
$letter[] = $i;
}else{
break;
}
$n++;
}
// 循环设置表头数据
foreach ($header as $k => $v) {
// 解决长数字自动转科学计数法
if(is_numeric($v) && strlen($v) > 11){
$sheet->setCellValueExplicit($letter[$k].'1',$v,'s');
}
$sheet->setCellValue($letter[$k].'1', $v);
}
// 生成数据
$sheet->fromArray($data, null, "A2");
// 样式设置
$sheet->getDefaultColumnDimension()->setWidth(12);
// 设置下载与后缀
if ($type) {
header("Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
$type = "Xlsx";
$suffix = "xlsx";
} else {
header("Content-Type:application/vnd.ms-excel");
$type = "Xls";
$suffix = "xls";
}
// 激活浏览器窗口
header("Content-Disposition:attachment;filename=$fileName.$suffix");
//缓存控制
header("Cache-Control:max-age=0");
// 调用方法执行下载
$writer = IOFactory::createWriter($preadsheet, $type);
// 数据流
$writer->save("php://output");
}
}
调用
$excel = new Excel();
// 导出
$header = ['姓名','性别'];
$data = [['小王','男'],['小李','男']];
$excel->export($header,$data);
// 导入
$data = $excel->importExcel('/storage/picture/20221222/77d80064c35db092c8124a13a7f6fcd5.xlsx');
if(is_array($data)){
print_r($data);
}else{
echo $data;
}
]]>本课程从日常工作实际需要出发,贯穿数据整理、数据分析、数据呈现等模块核心内容,
让你轻松掌握:
数据基础表的规范设计,改变建立数据表格的错误习惯;提升数据处理与分析效率的常用技巧,
包括常用函数与数据透视利用图表有效呈现数据信息,提高工作报表质量。
百度:https://pan.baidu.com/s/11GHSIQiZ4r-yW8mAJAKIqw
天翼:https://cloud.189.cn/t/YrInymB3Abqu
有句话叫:种一棵树的最佳时机是十年前,其次是现在。放到学习和职场中,是一样的道理。求职的时候,我们打着哈哈在简历上写上Excel技能熟练,不过是糊弄;但你总有进入职场的一天,简历上的“熟练”不能作为你的挡箭牌,工作效率低下,Excel技能不够精,将会成为你职业生涯中的绊脚石。学好Excel,能帮你在求职时更有底气,在职场中效率更高。
百度:https://pan.baidu.com/s/1E8Q6SY8Mip1mI9hmB6St1A
天翼:https://cloud.189.cn/t/ZvYFFjneMn22
<?php
include "./PHPExcel-1.8/Classes/PHPExcel/IOFactory.php";
// 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");
// Add some data
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', 'ID');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B1', '微信OpenID');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C1', '昵称');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D1', '性别');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E1', '国家');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F1', '省份');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G1', '城市');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H1', '场景');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I1', '标签');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J1', '头像');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K1', '关注时间');
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A2', 1111);
/*for ($i = 0; $i < count($result); $i++) {
$j = $i + 2;
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A' . $j, $result[$i]['id']);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('B' . $j, $result[$i]['openid']);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('C' . $j, $result[$i]['nickname']);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('D' . $j, $result[$i]['sex']);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('E' . $j, $result[$i]['country']);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('F' . $j, $result[$i]['province']);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('G' . $j, $result[$i]['city']);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('H' . $j, $result[$i]['scene']);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('I' . $j, $result[$i]['tagid']);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('J' . $j, $result[$i]['headimgurl']);
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('K' . $j, date("Y-m-d H:i:s", $result[$i]['subscribe']));
}*/
// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('userlist');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
ob_end_clean(); //解决乱码核心
// Redirect output to a client’s web browser (Excel2007)
$filename = date("YmdHis", time());
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.lanzouy.com/irlxN0a371bi
]]><?php
//引入类库
include "./PHPExcel-1.8/Classes/PHPExcel/IOFactory.php";
//elsx文件路径
$inputFileName = "./tq.xls";
date_default_timezone_set('PRC');
// 读取excel文件
try {
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch (Exception $e) {
}
// 确定要读取的sheet,什么是sheet,看excel的右下角,真的不懂去百度吧
$sheet = $objPHPExcel->getSheet(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);
}
]]>//获取当前文件名,不带后缀
$filename = $_SERVER['PHP_SELF'];
$houzhui = substr(strrchr($filename, '.'), 1);
$result = basename($filename,".".$houzhui);
// echo $result;
//生成excel文件,注意要双引号的字符串。(不加\t就会在一个单元格里,加了\n就会换行!)
header("Content-type:application/vnd.ms-excel");
header("Content-Disposition:filename=".$result.".xls");
echo "test1\t";
echo "test2\t\n";
echo "test1\t";
echo "test2\t\n";
echo "test1\t";
echo "test2\t\n";
echo "test1\t";
echo "test2\t\n";
echo "test1\t";
echo "test2\t\n";
echo "test1\t";
echo "test2\t\n";
]]>