tp6之phpspreadsheet学习（5）读取excel并在模板中显示
知识：thinkphp中volist输出二维数组 和if的用法，如何输出为表头thead与tbody
控制器代码
代码语言：javascript复制<?php
//requier 'vendor/autoload.php';
namespace appcontroller;
use appBaseController;
use thinkfacadeDb;
use thinkfacadeView;
use PhpOfficePhpSpreadsheetIOFactory;
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetWriterXlsx;
class Phpsheet extends BaseController
{
public function demo1()
{
$spreadsheet = new spreadsheet();
$sheet =$spreadsheet->getActiveSheet();
$cell1 = $sheet ->getCell('A1');
$v1 = $cell1->setValue("杨过");
$cell2 = $sheet ->getCellByColumnAndRow(1,2);
$v2 = $cell2->setValue("小龙女");
$cell3 = $sheet ->getCellByColumnAndRow(2,2);
$v3 = $cell3->setValue("郭靖");
$get = $sheet -> getCell('A1') ->getValue();
echo $get,PHP_EOL;
echo "-----------";
echo $sheet ->getCell('A2') ->getValue();
echo $sheet ->getCell('B2') ->getValue();
/*$Writer = New Xlsx($spreadsheet);
$Writer ->save('demo1.xlsx');*/
}
public function demo2()
{
$spreadsheet= new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$title = ["班级" , "学号" , "姓名" , "语文" , "数学" , "英语" , "政治" , "历史" , "物理" , "化学" , "体育" , "总分" , "班名次" , "级名次" , "镇名次" , "上次" , "进步"];
$col = 1;
$row = 1;
foreach($title as $v){
echo "$col-----$v <br>";
$sheet ->setCellValueByColumnAndRow($col, 1, $v);
$col ;
}
$count = count($title);
for ($i=2; $i<=$count; $i )
{
for ($r=1; $r<=$count; $r )
{
$sheet -> setCellValueByColumnAndRow($r,$i,rand(50,100));
}
}
$Writer = new Xlsx($spreadsheet);
$Writer ->save('demo2.xlsx');
}
public function Exceltomysql(){
$obj = IOFactory::createReader('Xlsx');
$obj -> setReadDataOnly(true);
$spreadsheet = $obj -> load('student.xlsx');//
$sheet = $spreadsheet->getActiveSheet();
# 获取总列数
$highestColumn = $sheet->getHighestColumn();
# 获取总行数
$highestRow = $sheet->getHighestRow();
# 列数 改为数字显示
$highestColumnIndex = PhpOfficePhpSpreadsheetCellCoordinate::columnIndexFromString($highestColumn);
//echo "行数".$highestRow."列数".$highestColumnIndex;
$log = [];
for($a=2;$a<$highestRow;$a ){
$name = $sheet->getCellByColumnAndRow(1,$a)->getValue();
$chinese = $sheet->getCellByColumnAndRow(2,$a)->getValue();
$maths = $sheet->getCellByColumnAndRow(3,$a)->getValue();
$english = $sheet->getCellByColumnAndRow(4,$a)->getValue();
$data = [
'name' => $name,
'chinese' => $chinese,
'maths' => $maths,
'english' => $english
];
$res = Db::table('t_student')->insert($data);
if($res){
$log[] = '第'.$a.'条，插入成功';
}else{
$log[] = '第'.$a.'条，插入失败';
}
}
//echo json_encode(['code'=>0,'msg'=>'成功','data'=>$log]);
dump($log);
}
public function list()
{
$spreadsheet = IOFactory::load("student2.xlsx");
$data = $spreadsheet
->getSheet(0) // 指定第一个工作表为当前
->toArray(); // 转为数组
//dump($data);
View::assign('data',$data);
return View::fetch();
}
}以上是全部代码，实际用到的是下面的代码
代码语言：javascript复制public function list()
{
$spreadsheet = IOFactory::load("student2.xlsx");
$data = $spreadsheet
->getSheet(0) // 指定第一个工作表为当前
->toArray(); // 转为数组
//dump($data);
View::assign('data',$data);
return View::fetch();
}视图代码：
代码语言：javascript复制<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>查看数据</title>
<link rel="stylesheet" type="text/css" href="/static/layui/css/layui.css">
<script type="text/javascript" src="/static/layui/layui.js"></script>
</head>
<body>
<table class="layui-table">
{volist name='data' id="d" key="k"}
{if $k == 1}
<thead>
{volist name='d' id="dd"}
<td>{$dd."--".$k}</td>
{/volist}
</thead>
{else /}
<tbody>
{volist name='d' id="dd"}
<td>{$dd}</td>
{/volist}
</tbody>
{/if}
{/volist}
</table>
</body>
</html>效果如下图
===今天学习到此===
