重点:先给自己定一个目标,在thinkphp环境下能在后台导入excel表格到Mysql数据库
Thinkphp6之PhpspreadSheet(2)导出数据库存入excel表
Thinkphp6之PhpspreadSheet导出数据库存入excel表
一、数据库
二、代码
1.导入相关的类文件
2.一个自定义函数与导出函数
三、在public目录中建立一下文件夹excel,代码一运行就有一个文件为03.xlsx的文件
文件内容如下:
测试完成。
<?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 Sheet extends BaseController
{
public function index()
{
# 实例化 Spreadsheet 对象
$spreadsheet = new Spreadsheet();
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
# 获取单元格
$cell = $sheet->getCell('A1');
//$cell = $sheet->getCellByColumnAndRow(1,1);
var_dump($sheet);
}
private function excel($title = [], $data = [], $path = '')
{
// 获取Spreadsheet对象
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
// 表头单元格内容 第一行
$titCol = 'A';
foreach ($title as $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');
$result = $writer->save($path . '.xlsx');
}
public function t1()
{
$data = Db::table('stu_test')->select();
//dump($data);
$title = ['ID','班级','学号','姓名','语文','数学','英语','政治','历史','物理','化学','体育','总分','班名次','级名次','镇名次','上次','进步'];
$this->excel($title, $data, $filename = './excel/03');
}
}
===今天学习到此===