2019-03-04

通过composer require 引入了依赖PhpSpreadsheet库，通过phpspreadsheet库完成了数据的导出生成相应的xlsx表，方便用户处理数据。贴一处用户数据表导出的相关代码：

<?php

//自动加载类

require_once('../vendor/autoload.php');

//引入phpspreadsheet

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

//引入数据库

require_once('./mysql.php');

//获取26个大写英文字母

$alphabet = "";

for ($i=65; $i <= 90; $i++) {

$alphabet .= chr($i); #chr()返回ASCII码相对应的字符

}

//实例化spreadsheet对象

$spreadsheet = new Spreadsheet();

//获取工作簿

$worksheet = $spreadsheet->getActiveSheet();

//设置工作表标题名称

$worksheet->setTitle('用户数据表');

//设置单元格的值

$worksheet->setCellValueByColumnAndRow(1,1, '用户数据表');

$worksheet->setCellValueByColumnAndRow(1,2, '用户名');

$worksheet->setCellValueByColumnAndRow(2,2, '密码');

$worksheet->setCellValueByColumnAndRow(3,2, '权限');

//合并单元格

$worksheet->mergeCells('A1:C1');

//单元格样式

$styleArray = [

'font' => [

'bold' => true

],

'alignment' => [

'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,

],

];

$worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);

$worksheet->getStyle('A2:C2')->applyFromArray($styleArray)->getFont()->setSize(16);

$sql = "SELECT * FROM login";

$stmt = $pdo->query($sql);

$rows = $stmt->fetchAll(FA);

$excelRow = 2;

for ($i=0, $len=count($rows); $i < $len; $i++) {

$excelRow++;

$worksheet->setCellValueByColumnAndRow(1, $excelRow, $rows[$i]['username']);

$worksheet->setCellValueByColumnAndRow(2, $excelRow, $rows[$i]['password']);

$worksheet->setCellValueByColumnAndRow(3, $excelRow, $rows[$i]['permission']);

}

$total_rows = $len + 2;

$worksheet->getStyle('A3:C'.$total_rows)->getFont()->setSize(16);

//设置列宽

for ($i=0, $totalColumn=3; $i < $totalColumn; $i++) {

$worksheet->getColumnDimension($alphabet[$i])->setAutoSize(true);

}

$styleArrayBody = [

'borders' => [

'allBorders' => [

'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,

'color' => ['argb'=>'666666'],

],

],

'alignment' => [

'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER

],

];

$worksheet->getStyle('A1:C'.$total_rows)->applyFromArray($styleArrayBody);

//设置浏览器header头

$filename = '用户数据表.xlsx';

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); #XLSX

// header('Content-Type: application/vnd.ms-excel'); #XLS

header('Content-Disposition: attachment;filename='.$filename);

header('Cache-Control: max-age=0');

$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');

// $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'xls');

$writer->save('php://output');