PHP操作excel推荐使用phpoffice组件，不推荐使用phpexcel
<p>由于phpexcel官方已经不再维护，建议所有新项目都使用phpoffice。</p><p>在些记录一下相关操作(复制过去就可以使用，尽量减少不必要的开发时间)：</p>
<p>在调试设置时，确保引入了正确的文件并实例化。</p>
<pre>
use PhpOffice\PhpSpreadsheet\Spreadsheet;
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();</pre>
<h4><span style="color:#e74c3c">字体</span></h4>
<p>第1行代码将A7至B7两单元格设置为粗体字，Arial字体，10号字；第2行代码将B1单元格设置为粗体字。</p>
<pre>
$spreadsheet->getActiveSheet()->getStyle('A7:B7')->getFont()->setBold(true)->setName('Arial')->setSize(10);
$spreadsheet->getActiveSheet()->getStyle('B1')->getFont()->setBold(true);</pre>
<h4><span style="color:#e74c3c">颜色</span></h4>
<pre>
$spreadsheet->getActiveSheet()->getStyle('A4')->getFont()->getColor()
->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED); //将文字颜色设置为红色。
$worksheet->getStyle('A4')->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
->getStartColor()->setARGB('76933C');//单元格背景颜色
//单元格背景颜色+边框
$styleArray = [
'borders' => [//边框
'outline' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => '00000000'],
],
],
'fill' => [
'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_GRADIENT_LINEAR,
'rotation' => 90,
'startColor' => [
'argb' => '76933C',
],
'endColor' => [
'argb' => '76933C',
],
],
'alignment' => [
'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,//水平对齐
'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,//上下居中
],
];
$worksheet->getStyle('A4')->applyFromArray($styleArray);</pre>
<h4><span style="color:#e74c3c">图片</span></h4>
<p>可以将图片加载到Excel中。</p>
<pre>
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\Drawing();
$drawing->setName('Logo');
$drawing->setDescription('Logo');
$drawing->setPath('./images/officelogo.jpg');//这里的地址只能是本地服务器的地址不能是http网络地址
$drawing->setHeight(36);
$drawing->setCoordinates('E' . $row);//哪一列哪一行
$drawing->setWorksheet($worksheet);
$worksheet->getRowDimension($row)->setRowHeight(60);
unset($drawing);</pre>
<h4><span style="color:#e74c3c">列宽</span></h4>
<p>将A列宽度设置为30（字符）。</p>
<pre>
$spreadsheet->getActiveSheet()->getColumnDimension('A')->setWidth(30);</pre>
<p>如果需要自动计算列宽，可以这样：</p>
<pre>
$spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);</pre>
<p>设置默认列宽为12。</p>
<pre>
$spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(12);
$worksheet->getColumnDimension($k)->setWidth(12);//列宽</pre>
<h4><span style="color:#e74c3c">行高</span></h4>
<p>设置第10行行高为100pt。</p>
<pre>
$spreadsheet->getActiveSheet()->getRowDimension('10')->setRowHeight(100);</pre>
<p>设置默认行高。</p>
<pre>
$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setRowHeight(15);
$worksheet->getRowDimension($startRow)->setRowHeight(25);//行高</pre>
<h4><span style="color:#e74c3c">对齐</span></h4>
<p>将A1单元格设置为水平居中对齐。</p>
<pre>
$styleArray = ['alignment' => ['horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,],];
$worksheet->getStyle('A1')->applyFromArray($styleArray);</pre>
<h4><span style="color:#e74c3c">合并</span></h4>
<p>将A18到E22合并为一个单元格（注意：合并为一个单元格后，再设置值的时候，单元格为A18）。</p>
<pre>
$spreadsheet->getActiveSheet()->mergeCells('A18:E22');</pre>
<h4><span style="color:#e74c3c">拆分</span></h4>
<p>将合并后的单元格拆分。</p>
<pre>
$spreadsheet->getActiveSheet()->unmergeCells('A18:E22');</pre>
<h4><span style="color:#e74c3c">边框</span></h4>
<p>将B2至G8的区域添加红色边框。</p>
<pre>
$styleArray = [
'borders' => [
'outline' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK,
'color' => ['argb' => 'FFFF0000'],
],
],
];
$worksheet->getStyle('B2:G8')->applyFromArray($styleArray);</pre>
<h4><span style="color:#e74c3c">工作表标题</span></h4>
<p>设置当前工作表标题。</p>
<pre>
$spreadsheet->getActiveSheet()->setTitle('Hello');</pre>
<h4><span style="color:#e74c3c">日期时间</span></h4>
<p>设置日期格式。</p>
<pre>
$spreadsheet->getActiveSheet()->setCellValue('D1', '2018-06-15');
$spreadsheet->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD2);</pre>
<h4><span style="color:#e74c3c">换行</span></h4>
<p>使用\n进行单元格内换行，相当于(ALT+"Enter")。</p>
<pre>
$spreadsheet->getActiveSheet()->getCell('A4')->setValue("hello\nworld");
$spreadsheet->getActiveSheet()->getStyle('A4')->getAlignment()->setWrapText(true);</pre>
<h4><span style="color:#e74c3c">超链接</span></h4>
<p>将单元格设置为超链接形式。</p>
<pre>
$spreadsheet->getActiveSheet()->setCellValue('E6', 'www.phpshiti.com');
$spreadsheet->getActiveSheet()->getCell('E6')->getHyperlink()->setUrl('https://www.phpshiti.com');</pre>
<h4><span style="color:#e74c3c">使用函数</span></h4>
<p>使用SUM计算B5到C5之间单元格的总和。其他函数同理：最大数(MAX)，最小数(MIN)，平均值(AVERAGE)。</p>
<pre>
$spreadsheet->getActiveSheet()->setCellValue('B7', '=SUM(B5:C5)');</pre>
<h4><span style="color:#e74c3c">设置文档属性</span></h4>
<p>可以设置Excel文档属性。</p>
<pre>
$spreadsheet->getProperties()
->setCreator("Helloweba") //作者
->setLastModifiedBy("Yuegg") //最后修改者
->setTitle("Office 2007 XLSX Test Document") //标题
->setSubject("Office 2007 XLSX Test Document") //副标题
->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.") //描述
->setKeywords("office 2007 openxml php") //关键字
->setCategory("Test result file"); //分类</pre>
<h4><span style="color:#e74c3c">设置提示</span></h4>
<p>To add a comment to a cell, use the following code. The example below adds a comment to cell E11:</p>
<pre>
$spreadsheet->getActiveSheet()->getComment('E11')->setAuthor('Mark Baker');
$commentRichText = $spreadsheet->getActiveSheet()->getComment('E11')
->getText()->createTextRun('PhpSpreadsheet:');
$commentRichText->getFont()->setBold(true);
$spreadsheet->getActiveSheet()->getComment('E11')->getText()->createTextRun("\r\n");
$spreadsheet->getActiveSheet()->getComment('E11')
->getText()->createTextRun('Total amount on the current invoice, excluding VAT.');</pre>
<p><img alt="08-cell-comment.png" src="https://phpspreadsheet.readthedocs.io/en/latest/topics/images/08-cell-comment.png" /></p>
<p> </p>
<p>此外，除了提供丰富的Excel文件处理接口外，PhpSpreadshee还提供了CSV，PDF，HTML以及XML等文件处理接口。</p>
<p>更多使用设置请参照官网文档：<a href="https://phpspreadsheet.readthedocs.io/en/stable/" rel="nofollow">https://phpspreadsheet.readthedocs.io/en/stable/</a>。</p>
页:
[1]