PHPExcel 导出数据（xls或xlsx）- 助手类（函数）
2024-09-01 10:25:55
本文链接：https://www.cnblogs.com/tujia/p/11358096.html
说明：简单好用的导出助手，轻松导出数据到 excel ！！
使用示例1：
使用示例2：
使用示例3：
源码：
<?php
namespace common\helpers; use yii\helpers\ArrayHelper; /**
* Excel 助手
*/
class ExcelHelper
{
public static $styleFormat = []; /**
* @see \PHPExcel_Style_NumberFormat
*/
public static function setStyleFormat($format)
{
self::$styleFormat = $format;
} /**
* 导出
* @see https://www.cnblogs.com/tujia/p/11358096.html
* @param array $titles 标题，一维数组，可传map或单纯标题
* @param array $dataArray 数据，二维数组，可传map或单纯数据
* @param string $filename 文件名，要带后缀
* @param string $bigTitle 居中加粗的大标题，默认为空
* @param array $extra 扩展数据
* @return file
*/
public static function export(array $titles, $dataArray, $filename, $bigTitle='', $extra=[])
{
set_time_limit(0);
ini_set('memory_limit', '512M'); // 后缀
$suffix = substr($filename, strrpos($filename, '.'));
empty($titles) && die('标题数组不能为空！');
empty($dataArray) && die('数据数组不能为空！');
!in_array($suffix, ['.xls', '.xlsx']) && die('文件名格式错误！'); $cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
$cacheSettings = array('memoryCacheSize ' => '512MB');
\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings); $oExcel = new \PHPExcel();
$oExcel->setActiveSheetIndex(0);
$sheet = $oExcel->getActiveSheet(); // 设置列数据格式
if (!empty(self::$styleFormat)) {
$fields = array_keys($titles);
foreach (self::$styleFormat as $field => $formatCode) {
$offset = array_search($field, $fields);
$col = chr(65+$offset);
$sheet->getStyle($col)->getNumberFormat()->setFormatCode($formatCode);
}
} // 行索引
$rowIndex = $bigTitle!=''? 2:1; $chr = [
'A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z',
'AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'
]; // 设置大标题
if ($bigTitle != '') {
$sheet->mergeCells('A1:'. $chr[count($titles)-1] .'1');
$sheet->getStyle('A1')->applyFromArray([
'font' => ['bold'=>true],
'alignment' => ['horizontal'=>\PHPExcel_Style_Alignment::HORIZONTAL_CENTER]
]);
$sheet->setCellValue('A1', $bigTitle);
} // 设置标题 A1 B1 C1 ....
$colIndex = 0;
$fieldsMap = [];
foreach ($titles as $key => $title) {
$fieldsMap[] = $key;
$sheet->setCellValue($chr[$colIndex] . $rowIndex, $title);
$colIndex++;
} // 设置内容 A1 B1 C1 .... A2 B2 C2 ....
$rowIndex++;
foreach ($dataArray as $key => $value)
{
foreach ($fieldsMap as $colIndex => $field) {
if (strrpos($field, '|') !== false) {
$temp1 = explode('|', $field);
$pos = strrpos($temp1[1], '.');
$pos === false && $pos = strlen($temp1[1]);
$temp2 = [];
$temp2[0] = substr($temp1[1], 0, $pos);
$temp2[1] = substr($temp1[1], $pos+1);
$val = $value[$temp1[0]];
$val = self::$temp2[0]($extra, $temp2[1], $val);
} else {
$val = $field? $value[$field] : $value;
}
$sheet->setCellValue($chr[$colIndex].$rowIndex, $val);
}
$rowIndex++;
} header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
if ($suffix == '.xlsx') {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
} else {
header('Content-Type: application/vnd.ms-excel');
}
header('Content-Disposition: attachment;filename="'. $filename .'"');
header("Content-Transfer-Encoding: binary");
header("Pragma: no-cache");
$oWriter = \PHPExcel_IOFactory::createWriter($oExcel, 'Excel2007');
$oWriter->save('php://output');
$oExcel->disconnectWorksheets();
exit;
} /**
* 导出
* @see https://www.cnblogs.com/tujia/p/5999806.html
* @param array $titles 标题，一维数组，可传map或单纯标题
* @param array $dataArray 数据，二维数组，可传map或单纯数据
* @param string $filename 文件名，要带后缀
* @param array $extra 扩展数据
* @return file
*/
public static function exportSimple(array $titles, $dataArray, $filename, $extra=[])
{
// 后缀
$suffix = substr($filename, strrpos($filename, '.'));
empty($titles) && die('标题数组不能为空！');
empty($dataArray) && die('数据数组不能为空！');
!in_array($suffix, ['.xls', '.xlsx', '.csv']) && die('文件名格式错误！'); // 导出准备
set_time_limit(0);
ini_set('memory_limit', '512M'); header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition: attachment; filename='.$filename);
if ($suffix == '.xlsx') {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
} elseif ($suffix == '.xls') {
header('Content-Type: application/vnd.ms-excel');
} elseif ($suffix == '.csv') {
header('Content-Type: application/vnd.ms-excel; charset=GB2312');
}
header("Content-Transfer-Encoding: binary");
header("Pragma: no-cache"); $isCsv = ($suffix == '.csv');
$fieldsCount = count($titles);
if ($isCsv) {
echo mb_convert_encoding(implode(',', array_values($titles)), 'gbk') . "\n";
} else {
echo '<table>';
echo '<tr>';
foreach ($titles as $key => $value) {
echo sprintf('<td>%s</td>', $value);
}
echo '</tr>';
} foreach ($dataArray as $key => $value) {
$i = 0;
$isCsv==false && print('<tr>');
foreach ($titles as $field => $title) {
if (strrpos($field, '|') !== false) {
$temp1 = explode('|', $field);
$pos = strrpos($temp1[1], '.');
$pos === false && $pos = strlen($temp1[1]);
$temp2 = [];
$temp2[0] = substr($temp1[1], 0, $pos);
$temp2[1] = substr($temp1[1], $pos+1);
$val = $value[$temp1[0]];
$val = self::$temp2[0]($extra, $temp2[1], $val);
} else {
$val = $field? $value[$field] : $value;
} if ($isCsv) {
echo mb_convert_encoding($val . ($i == $fieldsCount-1? "\n":','), 'gbk');
} else {
if (isset(self::$styleFormat[$field])) {
echo sprintf("<td style='mso-number-format:\"%s\";'>%s</td>", self::$styleFormat[$field], $val);
} else {
echo sprintf('<td>%s</td>', $val);
}
}
$i++;
}
$isCsv==false && print('</tr>');
}
$isCsv==false && print('</table>');
exit;
} public static function extra($extra, $extra_key, $val)
{
$arr = ArrayHelper::getValue($extra, $extra_key, []);
return ArrayHelper::getValue($arr, $val, '');
} public static function dateIsEmpty($extra, $extra_key, $val)
{
return strtotime($val)>1000? $val:'';
} public static function toFixed($extra, $extra_key, $val)
{
return (string)sprintf("%.{$extra_key}f", floatval($val));
} public static function dateFormat($extra, $extra_key, $val)
{
return date('Y-m-d H:i:s',$val/1000);
} public static function trim($extra, $extra_key, $val)
{
return str_replace(["\r", "\n", ","], ["", "", "，"], $val);
}
}
原创内容，转载请声明出处！
本文链接：https://www.cnblogs.com/tujia/p/11358096.html
最新文章
- [转载]触发ASSERT(afxCurrentResourceHandle != NULL)错误的原因
- Xamarin.Forms入门-使用 Xamarin.Forms 来创建跨平台的用户界面
- HLS入门收集(1)
- 妙味课堂——HTML+CSS(第四课)(二)
- [MarsZ]Unity3d游戏开发之Unity3d全策划配置新手指引
- LINUX防火墙firewall、iptables
- 用Java实现非阻塞通信
- Codeforces Round #254 (Div. 2)：A. DZY Loves Chessboard
- jsp EL表达式和JSTL标签if-else if-else用法
- zookeeper启动报 Unexpected exception, exiting abnormally 错误
- 前端开发之css
- Java工具之上传文件
- css布局方式总结
- NPOI 通过excel模板写入数据并导出
- “ORA-06550: 第 1 行, 第 7 列”解决方法
- css引入外部字体使网站字体更美观
- .Net Discovery系列之四 深入理解.Net垃圾收集机制(下)
- mongo获取lbs数据
- ubuntu 14.04网卡配置以及关闭防火墙
- （二）MySQL中级篇