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

下载后打开如下图：

追加、切换工作表

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