ThinkPHP番外篇：导出数据到excel文件中

前言

客户要求将数据导入到excel文件中，百度到的办法往往是使用PHPExcel组件，然而自打thinkphp5.1以后，这个框架就把vendor()这个函数给删掉了，导致网上一票教程都成了过时的东西，不具有参考价值

解决办法：

方法也很简单，不在使用传统的vendor引入，而是使用composer包管理的方式，下面介绍详细步骤：

1、composer引入

在项目所在目录打开命令行，输入如下指令，引入phpoffice/phpexcel包

https://blog.youkuaiyun.com/typedef_struct

在控制器中引入命名空间

use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\IOFactory;

2、代码实现

这里是导出了一个users用户表，表结构如下

DROP TABLE IF EXISTS `users`;
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `school` varchar(50) DEFAULT NULL,
  `class` varchar(50) DEFAULT NULL,
  `stu_num` varchar(50) DEFAULT NULL,
  `tel` int(12) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `role` varchar(10) DEFAULT NULL,
  `addate` int(15) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

--
-- 转存表中的数据 `users`
--

INSERT INTO `users` (`id`, `username`, `school`, `class`, `stu_num`, `tel`, `password`, `role`, `addate`) VALUES
(1, '小明', '现代', '一年级', '10001', 11111, '11111', 'stu', 1592806424),
(2, '小刚', '现代', '一年级', '10002', 22222, '11111', 'stu', 1592806449),
(3, '小红', '现代', '一年级', '10003', 33333, '11111', 'stu', 1592806469),
(4, '小王', '现代', '二年级', '10004', 44444, '11111', 'stu', 1592806480),
(5, '李白', '唐', '二年级', '10005', 55555, '11111', 'stu', 1592806489),
(6, '杜甫', '唐', '二年级', '10006', 66666, '11111', 'stu', 1592806500),
(7, '白居易', '唐', '二年级', '10007', 77777, '11111', 'stu', 1592806509),
(8, '王安石', '宋', '三年级', '10008', 88888, '11111', 'stu', 1592806520),
(9, '张飞', '三国', '三年级', '10009', 99999, '11111', 'stu', 1592806528),
(10, '关羽', '三国', '三年级', '10010', 1010101010, '1234567', 'stu', 1592806541),
(11, '黄盖', '三国', '四年级', '10011', 1111111111, '11111', 'stu', 1592806555),
(12, '诸葛亮', '三国', '四年级', '10012', 1212121212, '11111', 'stu', 1592806569);
COMMIT;

导出代码：

class Excel extends Controller
{
    //模板下载
    public function download()
    {
        $datas = users::select();
        $spreadsheet = new Spreadsheet();
        $worksheet = $spreadsheet->getActiveSheet();

        $worksheet->setTitle('导入模板');  //设置当前sheet的标题
        $worksheet->getStyle('A1:E1')->getFont()->setBold(true)->setName('Arial')->setSize(10);
        $worksheet->getStyle('B1')->getFont()->setBold(true);
        $worksheet->getDefaultColumnDimension()->setWidth(30);

        //设置第一栏的标题
        $worksheet->setCellValue('A1', '姓名');
        $worksheet->setCellValue('B1', '学院');
        $worksheet->setCellValue('C1', '年级');
        $worksheet->setCellValue('D1', '学号');
        $worksheet->setCellValue('E1', '电话/账号');
        $worksheet->setCellValue('F1', '注册时间');

        //默认填充数据


        //第二行起
        $baseRow = 2; //数据从N-1行开始往下输出 这里是避免头信息被覆盖
        foreach ($datas as $k => $val) {
            $i = $k + $baseRow;
            $worksheet->setCellValue('A' . $i, $val['username']);
            $worksheet->setCellValue('B' . $i, $val['school']);
            $worksheet->setCellValue('C' . $i, $val['class']);
            $worksheet->setCellValue('D' . $i, $val['stu_num']);
            $worksheet->setCellValue('E' . $i, $val['tel']);
            $worksheet->setCellValue('F' . $i, date('Y-m-d H:i:s', $val['addate']));
        }

        //处理 数字过大会进行科学计数法
        $worksheet->getStyle('A2')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
        $worksheet->getStyle('C2')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);


        $this->downloadExcel($spreadsheet, '批量导出用户表', 'Xls');


    }


    /*********************************************************************************************************************/

    //公共文件，用来传入xls并下载
    private function downloadExcel($spreadsheet, $filename, $format)
    {
        // $format只能为 Xlsx 或 Xls
        if ($format == 'Xlsx') {
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        } elseif ($format == 'Xls') {
            header('Content-Type: application/vnd.ms-excel');
        }

        header("Content-Disposition: attachment;filename="
            . $filename . date('Y-m-d') . '.' . strtolower($format));
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($spreadsheet, $format);

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

        //通过php保存在本地的时候需要用到
        //$objWriter->save($dir.'/demo.xlsx');

        //以下为需要用到IE时候设置
        // If you're serving to IE 9, then the following may be needed
        //header('Cache-Control: max-age=1');
        // If you're serving to IE over SSL, then the following may be needed
        //header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
        //header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
        //header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
        //header('Pragma: public'); // HTTP/1.0
        exit;
    }
}