php 合并表格,php多维数组合并单元格导出

最新推荐文章于 2024-05-16 10:30:18 发布

weixin_39631689

最新推荐文章于 2024-05-16 10:30:18 发布

阅读量577

点赞数

文章标签： php 合并表格

在上一篇文章中我们简单的说了数据的导入及导出，

在这片文章中，我们说一下对多维数组的，

合并单元格后导出的，一对多的数据格式。

我们对上一篇的数据格式进行修改成我们需要的格式，

单元格的合并，主要在于对行号的计算，

这里我们定义多个变量来记录行号。。。

foreach ($data as $k => $v) { // 循环外部数据处理(行)

$lines ++; // 记录外部行号

$descCount = isset($descField) ? count($v['desc']) : 0;

// $newExcel->getActiveSheet()->mergeCells('A19:A22'); // 单元格合并

foreach ($key_field as $m => $n) { // (列)

$letter = num_letter($m+1);

$objSheet = $objSheet->setCellValue($letter . $lines, $v[$n]);

if ($descCount > 0) { // 处理内部desc数据

$descCount > 1 && $newExcel->getActiveSheet()->mergeCells("$letter$lines:$letter".($lines+$descCount-1)); // 单元格合并

$descLine = $lines; // 行号(记录多行desc数据行号)

foreach ($v['desc'] as $x => $y) { // 循环desc数据(行)

foreach ($descKeyField as $q => $w) { // (列)

$letter = num_letter(count($key_field)+$q+1);

$objSheet = $objSheet->setCellValue($letter . $descLine, $y[$w]);

}

$descLine ++; // 行号(记录多行desc数据行号)

}

$descCount > 0 && $lines = $lines + $descCount - 1; // 重新处理数据行号

}

完成示例代码：

/**

* @Author: [FENG] <1161634940@qq.com>

* @Date: 2020-04-16 20:49:34

* @Last Modified by: [FENG] <1161634940@qq.com>

* @Last Modified time: 2020-04-19 13:15:51

include_once './vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\IOFactory; //use \PHPExcel_Style_NumberFormat; //设置列的格式==>>设置文本格式

class Ceshi

{

public static function export()

{

$data = [

[ 'id'=>1, 'name'=>'张三', 'sex'=>'男', 'age'=>20, 'desc'=>[

['chine' => suiji(), 'math' => suiji(), 'english' => suiji()],

] ],

[ 'id'=>2, 'name'=>'李四', 'sex'=>'女', 'age'=>18, 'desc'=>[

['chine' => suiji(), 'math' => suiji(), 'english' => suiji()],

] ],

[ 'id'=>3, 'name'=>'王五', 'sex'=>'女', 'age'=>25, 'desc'=>[

['chine' => suiji(), 'math' => suiji(), 'english' => suiji()],

] ],

[ 'id'=>4, 'name'=>'赵六', 'sex'=>'男', 'age'=>22, 'desc'=>[

['chine' => suiji(), 'math' => suiji(), 'english' => suiji()],

] ],

];

// $fieldArr = ['ID'=>'id', '姓名'=>'name', '性别'=>'sex', '年龄'=>'age', '电话'=>'tel', '测试'=>'ceshi']; // 声明对应字段关系

$newExcel = new Spreadsheet(); //创建一个新的excel文档

$objSheet = $newExcel->getActiveSheet(); //获取当前操作sheet的对象

$objSheet->setTitle('测试表'); //设置当前sheet的标题

$lines = 1;

$fieldArr = [ 'id' => 'ID', 'name' => '姓名', 'sex' => '性别', 'age' => '年龄', 'desc' => [

'chine' => '语文', 'math' => '数学', 'english' => '英语'

] ]; // 声明对应字段关系

$fieldArr && $fieldArr = array_change_key_case($fieldArr,CASE_LOWER);

if (isset($fieldArr['desc'])) {

$descField = array_change_key_case($fieldArr['desc'],CASE_LOWER);;

$descKeyField = array_keys($descField);

unset($fieldArr['desc']);

$titleFieldArr = array_merge($fieldArr, $descField);

$key_field = array_keys($fieldArr);

} else {

$titleFieldArr = $fieldArr;

$key_field = $titleFieldArr ? array_keys($titleFieldArr) : array_keys($data[0]);

}

$value_field = $titleFieldArr ? array_values($titleFieldArr) : array_keys($data[0]);

foreach ($value_field as $k => $v) {

$letter = num_letter($k+1);

$newExcel->getActiveSheet()->getColumnDimension($letter)->setAutoSize(true); // 简单设置列宽

$objSheet = $objSheet->setCellValue($letter . $lines, $v); // 设置标题

}

//第二行起，每一行的值,setCellValueExplicit是用来导出文本格式的。

//->setCellValueExplicit('C' . $k, $val['admin_password']PHPExcel_Cell_DataType::TYPE_STRING),可以用来导出数字不变格式

foreach ($data as $k => $v) { // 循环外部数据处理(行)

$lines ++; // 记录外部行号

$descCount = isset($descField) ? count($v['desc']) : 0;

// $newExcel->getActiveSheet()->mergeCells('A19:A22'); // 单元格合并

foreach ($key_field as $m => $n) { // (列)

$letter = num_letter($m+1);

$objSheet = $objSheet->setCellValue($letter . $lines, $v[$n]);

if ($descCount > 0) { // 处理内部desc数据

$descCount > 1 && $newExcel->getActiveSheet()->mergeCells("$letter$lines:$letter".($lines+$descCount-1)); // 单元格合并

$descLine = $lines; // 行号(记录多行desc数据行号)

foreach ($v['desc'] as $x => $y) { // 循环desc数据(行)

foreach ($descKeyField as $q => $w) { // (列)

$letter = num_letter(count($key_field)+$q+1);

$objSheet = $objSheet->setCellValue($letter . $descLine, $y[$w]);

}

$descLine ++; // 行号(记录多行desc数据行号)

}

$descCount > 0 && $lines = $lines + $descCount - 1; // 重新处理数据行号

}

// downloadExcel($newExcel, '测试表', 'Xlsx', './public'); // 保存到本地

downloadExcel($newExcel, '测试表', 'Xlsx'); //生成文件直接下载

}

// 生成随机数

function suiji()

{

return rand(50,100);

}

// 公共文件，十进制转二十六进制(基数为A-Z)

function num_letter($num) {

$num = intval($num);

if ($num <= 0)

return false;

$letterArr = array('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');

$letter = '';

do {

$key = ($num - 1) % 26;

$letter = $letterArr[$key] . $letter;

$num = floor(($num - $key) / 26);

} while ($num > 0);

return $letter;

}

// 公共文件，用来传入xls并下载

function downloadExcel($newExcel, $filename, $format, $savePath = false)

{

if(!$savePath){ //网页下载

// $format只能为 Xlsx 或 Xls

if ($format == 'Xlsx') {

header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

} elseif ($format == 'Xls') {

header('Content-Type: application/vnd.ms-excel');

}

header("Content-Disposition: attachment;filename=" . $filename . date('Y-m-d') . '.' . strtolower($format));

header('Cache-Control: max-age=0');

$objWriter = IOFactory::createWriter($newExcel, $format);

$objWriter->save('php://output');exit;

} else {

ob_clean();

ob_start();

$objWriter = IOFactory::createWriter($newExcel, $format);

$savePath = trim(trim($savePath, '.'), '/') . '/' . $filename . date('Y-m-d') . '.' . strtolower($format);

$objWriter->save($savePath);

/* 释放内存 */

$newExcel->disconnectWorksheets();

unset($newExcel);

ob_end_flush();

}

Ceshi::export(); // 数据文件的导出到ececl