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