1.导出复杂表头
tp5 composer 安装excel
composer require phpoffice/phpexcel导出复杂表头参考：https://blog.csdn.net/zxj201611/article/details/87940396
use \PHPExcel;
use \PHPExcel_Style_Color;
use \PHPExcel_Style_Alignment;
use \PHPExcel_style_Fill;
//导出复杂表头
public function expolteComplex(){
$indexKey[0] = 'ordersn';
$indexKey[1] = 'userid';
$indexKey[2] = 'uaeraccount';
$indexKey[3] = 'uaername';
$list[0]['ordersn'] = 1;
$list[0]['userid'] = 2;
$list[0]['uaeraccount'] = 3;
$list[0]['uaername'] = 4;
$objPHPExcel = new \PHPExcel();
$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
ob_end_clean();//清空缓存
header("Pragma: public");//设置头信息
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header('Content-Disposition:attachment;filename="表单.xls"');
header("Content-Transfer-Encoding:binary");
$arr = ['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'];//excel列名数组
$col = 4;
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'SiteName')->getColumnDimension('A')->setWidth(12);
$objPHPExcel->getActiveSheet()->setCellValue('B1', 'wjp');
$objPHPExcel->getActiveSheet()->mergeCells('B1:' . ($arr[$col - 1]) . '1');//横向合并单元格
//这里也可以竖合并 单元格写对就可以了
$objPHPExcel->getActiveSheet()->setCellValue('A2', '订单号');
$objPHPExcel->getActiveSheet()->setCellValue('B2', '会员ID');
$objPHPExcel->getActiveSheet()->setCellValue('C2', '会员账号');
$objPHPExcel->getActiveSheet()->setCellValue('D2', '会员姓名');
$objActSheet = $objPHPExcel->getActiveSheet();
// $startRow = 2; //简单表头
$startRow = 3;//复杂表头从3开始
foreach ($list as $row) {
foreach ($indexKey as $key => $value){
//这里是设置单元格的内容
$objActSheet->setCellValue($arr[$key].$startRow,$row[$value]);
}
$startRow++;
}
//设置整行填充的样式和背景色
$objPHPExcel->getActiveSheet()->getStyle( 'A3:D3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle( 'A3:D3')->getFill()->getStartColor()->setARGB('FFFFFF00');
//最后一行
$row10=4;
$sumal = 999;
$objPHPExcel->getActiveSheet()->setCellValue('A'.$row10, 'Week Total');
$objPHPExcel->getActiveSheet()->setCellValue('B'.$row10, $sumal)->getStyle('B'.$row10)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//单个单元格设置填充的样式和背景色
$objPHPExcel->getActiveSheet()->getStyle( 'B'.$row10)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle( 'B'.$row10)->getFill()->getStartColor()->setARGB('FFFF0000');
//所有单元格居中
$objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()>setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //单个单元格居中
$objWriter->save('php://output');//导出
}效果：
2.导出简单表头
//文件引入
require_once './PHPExcel-1.8/Classes/PHPExcel.php';
require_once './PHPExcel-1.8/Classes/PHPExcel/Writer/Excel2007.php';
//require_once ROOT_PATH.'/Public/Classes/PHPExcel/Writer/Excel2007.php';
$excel2007=false;
if(empty($filename)) $filename = time();
$indexKey[0] = 'ordersn';
$indexKey[1] = 'userid';
$indexKey[2] = 'uaeraccount';
$indexKey[3] = 'uaername';
$list[0]['ordersn'] = 1;
$list[0]['userid'] = 2;
$list[0]['uaeraccount'] = 3;
$list[0]['uaername'] = 4;
if( !is_array($indexKey)) return false;
$header_arr = 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');
//初始化PHPExcel()
$objPHPExcel = new \PHPExcel();
//设置保存版本格式
if($excel2007){
$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
$filename = $filename.'.xlsx';
}else{
$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
$filename = $filename.'.xls';
}
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', '订单号')
->setCellValue('B1', '会员ID')
->setCellValue('C1', '会员账号')
->setCellValue('D1', '会员姓名');
//接下来就是写数据到表格里面去
$objActSheet = $objPHPExcel->getActiveSheet();
$startRow = 2;
foreach ($list as $row) {
foreach ($indexKey as $key => $value){
//这里是设置单元格的内容
$objActSheet->setCellValue($header_arr[$key].$startRow,$row[$value]);
}
$startRow++;
}
// 下载这个表格，在浏览器输出
ob_end_clean();
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-execl");
header("Content-Type:application/octet-stream");
header("Content-Type:application/download");
header('Content-Disposition:attachment;filename='.$filename.'');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');
//生成文件
//构造文件保存路径
$fiel_dir = 'upload/excel/' .date('Y-m-d') .'/';
$file_name = 'WM_Upload_' .date('YmdHis').'.xls';
if(!is_dir($fiel_dir)){
mkdir($fiel_dir, 0777,true);
}
$new_file_name = $fiel_dir .$file_name;
$objWriter->save($new_file_name);//保存文件
return $new_file_name;
我报这个错是因为没注意
$objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
//生成xlsx
$objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
//生成xls3.解析excel
$filePath = './123.xls';
// 实例化对象
if (strstr($filePath, '.xlsx')) {
// 对应文件类型为 .xlsx
$PHPReader = new \PHPExcel_Reader_Excel2007();
} elseif (strstr($filePath, '.xls')) {
// 对应文件类型为 .xls
$PHPReader = new \PHPExcel_Reader_Excel5();
} else {
// 文件类型无法识别
return false;
}
// 载入Excel文件
$PHPExcel = $PHPReader->load($filePath);
// 获得sheet1
$sheet = $PHPExcel->getActiveSheet(0);
// 当前sheet的最大行数
$highestRow = $sheet->getHighestRow();
// 获取Excel数据
$arr = $sheet->toArray();
echo '<pre>';
print_r($arr);die;4.生成word
composer require phpoffice/phpword
use PhpOffice\PhpWord\IOFactory;
use PhpOffice\PhpWord\PhpWord; $PHPWord = new PhpWord();
// $section = $PHPWord->createSection();
$section = $PHPWord->addSection();
$PHPWord->addTitleStyle(1, array('bold' => true, 'size' => 18, 'name' => 'Arial', 'Color' => '333'), array('align' => 'center'));
$section->addTitle("编程爱好者之家", 1);
//定义样式数组
$styleTable = array(
'borderSize'=>6,
'borderColor'=>'000000',
'cellMargin'=>150
);
$styleFirstRow = array(
'borderBottomSize'=>18,
'borderBottomColor'=>'000000',
'bgColor'=>'66bbff'
);
$cellStyle = array('gridSpan' => 2);
//添加表格样式
$PHPWord->addTableStyle('myOwnTableStyle',$styleTable,$styleFirstRow);
//添加表格
$table = $section->addTable('myOwnTableStyle');
$table->addRow();
$table->addCell(3000)->addText('测试1');
$table->addCell(3000)->addText('测试2');
$table->addCell(3000)->addText('测试3');
$table->addRow();
$table->addCell(3000)->addText('测试4');
$table->addCell(6000,$cellStyle)->addText('测试5'); //合并列单元格
$table->addRow();
$table->addCell(3000,array('vMerge' => 'restart'))->addText('合并');
$table->addCell(3000)->addText('测试6');
$table->addCell(3000)->addText('测试7');
$table->addRow();
$table->addCell(3000,array('vMerge' => 'continue'));
$table->addCell(3000)->addText('测试8');
$table->addCell(3000)->addText('测试9');
$table->addRow();
$table->addCell(3000)->addText('测试10');
$table->addCell(3000)->addText('测试11');
$table->addCell(3000)->addText('测试12');
$file = '测试.docx';
//文档存在服务器上用此代码
// $objWriter = IOFactory::createWriter($PHPWord, 'Word2007');
// $objWriter->save('存储地址/编程爱好者.docx');
//直接下载不存储在服务器上用此代码
header("Content-Description: File Transfer");
header('Content-Disposition: attachment; filename="' . $file . '"');
header('Content-Type: application/vnd.openxmlformats-officedocument.wordprocessingml.document');
header('Content-Transfer-Encoding: binary');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Expires: 0');
$xmlWriter = IOFactory::createWriter($PHPWord, 'Word2007');
$xmlWriter->save("php://output");
558
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
