tp6之phpspreadsheet学习（5）读取excel并在模板中显示

哆哆Excel

发布于 2022-10-25 14:54:28

1.5K0

发布于 2022-10-25 14:54:28

文章被收录于专栏：哆哆Excel

tp6之phpspreadsheet学习（5）读取excel并在模板中显示

知识：thinkphp中volist输出二维数组和if的用法，如何输出为表头thead与tbody

控制器代码

<?php

//requier 'vendor/autoload.php';
namespace app\controller;
use app\BaseController;
use think\facade\Db;
use think\facade\View;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

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 = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::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();
  }
}

以上是全部代码，实际用到的是下面的代码

public function list()
{
    $spreadsheet =  IOFactory::load("student2.xlsx");
    $data = $spreadsheet
            ->getSheet(0) // 指定第一个工作表为当前
            ->toArray();  // 转为数组
//dump($data);
    View::assign('data',$data);
return View::fetch();
  }

视图代码：

<!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>

效果如下图