禅道博客

分享专业技术知识,文章内容干货满满

PHP-Xlswriter 导出数据为Excel

2022-10-28 18:03:40
宋辰轩
原创 104
摘要:本文将详解如何用PHP-Xlswriter导出数据为Excel。

PHP-Xlswriter官网


安装

Ubuntu下使用pecl安装比较方便,除此之外,官方还提供了其他的安装方法。

pecl install xlswriter
在php.ini中添加扩展即可使用,在ubuntu中的/etc/php中,找到你使用的版本即可。笔者的在/etc/php/7.4/fpm/php.ini中添加(使用的是Nginx)
# Add extension = xlswriter.so to ini configuration


安装报错解决方案

使用pecl安装xlswriter过程中make报错zend_smart_str.h: No such file or directory:php-dev 与当前环境版本不一致,卸载当前错误的dev(ubuntu: apt-get autoremove phpx.x-dev),安装对应的php-dev,笔者为php7.4-dev(ubuntu: apt-get install php7.4-dev)。


常用的一个配置示例

在一个方法中,写入如下代码:

// $tmpRoot是一个路径
$config = ['path' => $tmpRoot];
$xlsxObject = new \Vtiful\Kernel\Excel($config);
$fileName = 'tutorial01.xlsx';
// fileName will automatically create a worksheet,
// you can customize the worksheet name, the worksheet name is optional
$filePath = $xlsxObject->fileName($fileName, 'sheet1')
    ->header(['姓名', '年龄'])
    ->data([['Tom', 22], ['Bob', 82],])
    ->output();
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Disposition: attachment;filename="' . $fileName . '"');
header('Cache-Control: max-age=0');
ob_clean();
flush();
if(copy($filePath, 'php://output') === false)
{
    // Throw exception
}
// Delete temporary file
@unlink($filePath);

下载后打开如下图:

追加、切换工作表

使用addSheet(string sheetName)另起一张工作表进行数据插入,使用checkoutSheet(string sheetName)切换工作表
// $tmpRoot是一个路径
$config = ['path' => $tmpRoot];
$xlsxObject = new \Vtiful\Kernel\Excel($config);
$fileName = 'tutorial01.xlsx';
// fileName will automatically create a worksheet,
// you can customize the worksheet name, the worksheet name is optional
$fileObject = $xlsxObject->fileName($fileName, 'sheet1');
$fileObject->header(['name', 'age'])
    ->data([['Tom', 22], ['Bob', 82]]);
// add a new sheet
$fileObject->addSheet()
    ->header(['name', 'age'])
    ->data([['scx', 22]]);
// checkout to sheet1 and add data.
 $fileObject->checkoutSheet('sheet1')
     ->data([['newperson', 23]]);
$filePath = $fileObject->output();
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Disposition: attachment;filename="' . $fileName . '"');
header('Cache-Control: max-age=0');
ob_clean();
flush();
if(copy($filePath, 'php://output') === false)
{
    // Throw exception
}
// Delete temporary file
@unlink($filePath);

下载后打开如下图:


数据验证


下拉列表


使用Validation()定义规则

$config = ['path' => $tmpRoot];
$xlsxObject = new \Vtiful\Kernel\Excel($config);
$fileName = 'tutorial01.xlsx';
$validation = new \Vtiful\Kernel\Validation();
$validation->validationType(\Vtiful\Kernel\Validation::TYPE_LIST)
->valueList(['f', 'm']);
// fileName will automatically create a worksheet,
// you can customize the worksheet name, the worksheet name is optional
$fileObject = $xlsxObject->fileName($fileName, 'sheet1');
$fileObject->header(['name', 'gender'])
  ->data([['Tom', 'f'], ['Bob', 'm']]);
$fileObject->validation('B2', $validation->toResource());
$fileObject->validation('B3', $validation->toResource());
$filePath = $fileObject->output();
header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
header('Content-Disposition: attachment;filename="' . $fileName . '"');
header('Cache-Control: max-age=0');
ob_clean();
flush();
if(copy($filePath, 'php://output') === false)
{
// Throw exception
}
// Delete temporary file
@unlink($filePath);


发表评论
评论通过审核后显示。