Thinkphp5.1引入及使用phpspreadsheet
本文分享在本地环境Thinkphp中引入phpspreadsheet并导出数据：
1、获取phpspreadsheet
① 小编尝试了从github下载和composer下载后移动到thinkphp项目下，但是将phpspreadsheet引入thinkphp有些麻烦。需要单独使用require加文件路径的方式引入，容易发生错误。
② 建议将composer设置为全局使用，切换到thinkphp项目下，再使用composer 获取phpspreadsheet库。这样Thinkphp可以帮你完成引入操作，可以方便的使用。（Composer全局设置方法）
composer获取phpspreadsheet的命令：
composer require phpoffice/phpspreadsheet
2、引入phpspreadsheet及实例化
<?php
namespace app\admin\controller;
use think\Controller;
use think\View;
use app\index\model\Inform;
use PhpOffice\PhpSpreadsheet\Spreadsheet; //引入PhpSpreadSheet
use PhpOffice\PhpSpreadsheet\Writer\Xlsx; //引入PhpSpreadSheet
$spreadsheet = new Spreadsheet(); //PhpSpreadSheet实例化
3、查询数据库数据，并导出Excel文件
//从数据库查询需要的数据
$data = Inform::where([
‘status’ =>-1
])
->order(‘id’,’desc’)
->select();
// Create new Spreadsheet object
$spreadsheet = new Spreadsheet();
// Add title
$spreadsheet->setActiveSheetIndex(0)
->setCellValue(‘A1’, ‘ID’)
->setCellValue(‘B1’, ‘大类型’)
->setCellValue(‘C1’, ‘中类型’)
->setCellValue(‘D1’, ‘小类型’)
->setCellValue(‘E1’, ‘名称’)
->setCellValue(‘F1’, ‘网址’)
->setCellValue(‘G1’, ‘描述’);
// Rename worksheet
$spreadsheet->getActiveSheet()->setTitle(‘待审核数据’);
$i = 2;//第一行标题使用了，因此需要从2开始
foreach ($data as $rs) {
// Add data
$spreadsheet->getActiveSheet()
->setCellValue(‘A’.$i, $rs[‘id’])
->setCellValue(‘B’.$i, $rs[‘type’])
->setCellValue(‘C’.$i, $rs[‘category’][‘text’])
->setCellValue(‘D’.$i, $rs[‘smalltype’])
->setCellValue(‘E’.$i, $rs[‘name’])
->setCellValue(‘F’.$i, $rs[‘url’])
->setCellValue(‘G’.$i, $rs[‘des’]);
$i++;
}
//Set width
$spreadsheet->getActiveSheet()
->getColumnDimension(‘A’)
->setWidth(15);
$spreadsheet->getActiveSheet()
->getColumnDimension(‘B’)
->setWidth(15);
$spreadsheet->getActiveSheet()
->getColumnDimension(‘C’)
->setWidth(60);
$spreadsheet->getActiveSheet()
->getColumnDimension(‘D’)
->setWidth(15);
$spreadsheet->getActiveSheet()
->getColumnDimension(‘E’)
->setWidth(20);
$spreadsheet->getActiveSheet()
->getColumnDimension(‘F’)
->setWidth(20);
$spreadsheet->getActiveSheet()
->getColumnDimension(‘G’)
->setWidth(20);
// Set alignment
$spreadsheet->getActiveSheet()->getStyle(‘A1:G’.$i)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
$spreadsheet->getActiveSheet()->getStyle(‘C2:C’.$i)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$spreadsheet->setActiveSheetIndex(0);
return $this->exportExcel($spreadsheet, ‘xls’, ‘登陆日志’);
}
/**
* 导出Excel
* @param object $spreadsheet 数据
* @param string $format 格式:excel2003 = xls, excel2007 = xlsx
* @param string $savename 保存的文件名
* @return filedownload 浏览器下载
*/
function exportExcel($spreadsheet, $format = ‘xls’, $savename = ‘export’) {
if (!$spreadsheet) return false;
if ($format == ‘xls’) {
//输出Excel03版本
header(‘Content-Type:application/vnd.ms-excel’);
$class = “\PhpOffice\PhpSpreadsheet\Writer\Xls”;
} elseif ($format == ‘xlsx’) {
//输出07Excel版本
header(‘Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet’);
$class = “\PhpOffice\PhpSpreadsheet\Writer\Xlsx”;
}
//输出名称
header(‘Content-Disposition: attachment;filename=”‘.$savename.’.’.$format.'”‘);
//禁止缓存
header(‘Cache-Control: max-age=0’);
$writer = new $class($spreadsheet);
$filePath = env(‘runtime_path’).”temp/”.time().microtime(true).”.tmp”;
$writer->save($filePath);
readfile($filePath);
unlink($filePath);
}
需要注意的是setCellValue(‘A’.$i, $rs[‘id’])，第二个参数需要是字符串，不能是数组。
以上就是Thinkphp5.1引入及使用phpspreadsheet的方法。
