a:根目录下执行:composer require phpoffice/phpexcel
b:引入(可new \PHPExcel()直接使用):
use PHPExcel_IOFactory;
use PHPExcel;
a:下载PHPExcel:
地址:https://pan.baidu.com/s/1gfrEt7tz2uwOrubeHcuorg
提取码:zxcv
b:放到项目根目录extend文件夹下,目录结构如下:
c:引入(可new \PHPExcel()直接使用)
//要导出的数据
$data[] = ['aa'=>'第一列', 'bb'=>'第二列', 'cc'=>'第三列', 'dd'=>'第四列'];
$data[] = ['aa'=>'第一列', 'bb'=>'第二列', 'cc'=>'第三列', 'dd'=>'第四列'];
//模板文件路径(可更改,文件内定义表头与格式,第一行标题,第二行表头)
$file = EXTEND_PATH . 'files/excel.xls';
//加载模板
$phpexcel=\PHPExcel_IOFactory::createReader("Excel2007")->load($file);
$objActSheet = $phpexcel->getActiveSheet();
//写入标题
$objActSheet->setCellValue('A1', '标题');
//循环写入数据
$i = 0;
foreach ($data as $item) {
$row = 3 + $i;
$objActSheet->setCellValue('A' . $row, $i + 1);
$objActSheet->setCellValue('B'.$row,$item['aa']);
$objActSheet->setCellValue('C'.$row,$item['bb']);
$objActSheet->setCellValue('D'.$row,$item['cc']);
$objActSheet->setCellValue('E'.$row,$item['dd']);
$objActSheet->getRowDimension($row)->setRowHeight(40);//行高40
$i++;
}
//表头
$head = ['aa'=>'第一列', 'bb'=>'第二列', 'cc'=>'第三列', 'dd'=>'第四列'];
//导出数据
$data[] = ['aa'=>'aa', 'bb'=>'bb', 'cc'=>'cc', 'dd'=>'dd'];
$data[] = ['aa'=>'aa', 'bb'=>'bb', 'cc'=>'cc', 'dd'=>'dd'];
//创建新的PHPExcel对象
$phpexcel= new \PHPExcel();
$objActSheet = $phpexcel->getActiveSheet();
//设置表头
$key = ord("A");
$key2 = ord("A");
$colum2 = '';
foreach ($head as $k => $v) {
$colum = chr($key);
$objActSheet->setCellValue($colum2 . $colum . '2', $v);
if ($key < 90) {
$key += 1;
} else {
$key = ord("A");
$colum2 = chr($key2);
$key2++;
}
}
//标题
$objActSheet->setCellValue('A1', '标题')->mergeCells('A1:' . $colum . 1);
$row= 2;
foreach ($data as $rows) { //行写入
$span = ord("A");
$span2 = ord("A");
$j2 = '';
$row++;
foreach ($head as $k=>$value) {// 列写入
$j = chr($span);
$objActSheet->setCellValueExplicit($j2 . $j . $row, isset($rows[$k])?$rows[$k]:'', \PHPExcel_Cell_DataType::TYPE_STRING);
if ($span < 90) {
$span += 1;
} else {
$span = ord("A");
$j2 = chr($span2);
$span2++;
}
}
}
$fileName = 'excle.xls';
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename=' . $fileName);
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($phpexcel,'Excel2007');
$objWriter->save('php://output');
//创建新的工作表
$phpexcel->createSheet();
//选择工作表
$phpexcel->setActiveSheetIndex(0);
//重命名工作表
$phpexcel->getActiveSheet()->setTitle('Simple');
//单元格合并并设置值
$phpexcel->getActiveSheet()->setCellValue('A1', '值')->mergeCells('A1:E1' );
//设置字体
$phpexcel->getActiveSheet()->getStyle('A3:S' . $row )->getFont()->setName('Candara');
//设置字号
$phpexcel->getActiveSheet()->getStyle('A3:S' . $row )->getFont()->setSize(12);
//字体加粗
$phpexcel->getActiveSheet()->getStyle('A1:S'. $row )->getFont()->setBold(true);
//行高度
$phpexcel->getActiveSheet()->getRowDimension($row)->setRowHeight(40);
//列宽度
$phpexcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true);//自适应
$phpexcel->getActiveSheet()->getColumnDimension('A')->setWidth(300);
//表格画线
$styleArray=array('borders'=>array('allborders'=>array('style'=>\PHPExcel_Style_Border::BORDER_THIN,),),);
$phpexcel->getActiveSheet()->getStyle('A1:S' . $row)->applyFromArray($styleArray);
//文字居中
$phpexcel->getActiveSheet()->getStyle('A1:S' .$row)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//文字靠右
$phpexcel->getActiveSheet()->getStyle('A' .$row)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
//内容自动换行
$phpexcel->getActiveSheet()->getStyle('A1:S' .$row)->getAlignment()->setWrapText(TRUE);
//锁定(以B3单元格为分界线,上方和左方都会锁定)
$phpexcel->getActiveSheet()->freezePane('B3');
//设置下拉
$objValidation=$phpexcel->getActiveSheet()->getCell('C1' )->getDataValidation();
$objValidation->setType(\PHPExcel_Cell_DataValidation::TYPE_LIST)
->setErrorStyle(\PHPExcel_Cell_DataValidation::STYLE_INFORMATION )
->setAllowBlank(false)
->setShowInputMessage(true)
->setShowErrorMessage(true)
->setShowDropDown(true)
->setErrorTitle('输入的值有误')
->setError('您输入的值不在下拉框列表内.')
->setPromptTitle("标题")
->setPrompt("备注")
->setFormula1("选项一,选项二");
$file = '路径';
$phpexcel = \PHPExcel_IOFactory::load($file);
$phpexcel->setActiveSheetIndex(0);//指定第一个工作表
$data= $phpexcel->getActiveSheet()->toArray(null, true, true, true);
手机扫一扫
移动阅读更方便
你可能感兴趣的文章