ThinkPHP5.1扩展phpspreadsheet实现数据导出Excel

thinkphp通过composer扩展phpspreadsheet，最终实现数据导出到excel.

研究了一下phpspreadsheet官方例子来直接输出数据到浏览器实现下载。但在windows本地测试一直没有成功， 发现是由于$rewriter->save('php://output')对目录的写入权限问题，在linux服务器正常。最后更换为以下方式实现输出：

以下是代码：环境 thinkphp 5.1.23 +Apache + PHP7.1

use PhpOffice\PhpSpreadsheet\Spreadsheet;

//从数据库查询需要的数据

$data = model('admin/Loginlog')->where($where)->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', 'IP');

// Rename worksheet

$spreadsheet->getActiveSheet()->setTitle('登陆日志');

$i = 2;

foreach ($data as $rs) {

    // Add data

    $spreadsheet->getActiveSheet()

        ->setCellValue('A'.$i, $rs['id'])

        ->setCellValue('B'.$i, $rs['username'])

        ->setCellValue('C'.$i, $rs['info'])

        ->setCellValue('D'.$i, $rs['status'] ? '成功' : '失败')

        ->setCellValue('E'.$i, date('Y-m-d H:i:s',$rs['logintime']))

        ->setCellValue('F'.$i, $rs['loginip']);

    $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);

// Set alignment

$spreadsheet->getActiveSheet()->getStyle('A1:F'.$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 exportExcel($spreadsheet, 'xls', '登陆日志');

复制代码

函数exportExcel写在公共函数common.php里了

/**

* 导出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);

}