Home > Article > Backend Development > PHP implements exporting styled Excel instance sharing

PHP implements exporting styled Excel instance sharing

墨辰丷Original: 2018-06-01 14:17:432206browse

Sometimes customers will complain to you that the Excel format exported by the software for them is too ugly. Maybe this article will help you. While exporting data with PHP, you can also set the color, font size, boldness, merge cells, etc.

When exporting at work, I need to export a customized table or I feel that the exported Excel format is too ugly.

Need to set color, font size, bold, merge cells, etc.

Rendering:

PHP code:

/**
* 导出文件
* @return string
*/
public function export()
{
$file_name  = "成绩单-".date("Y-m-d H:i:s",time());
$file_suffix = "xls";
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$file_name.$file_suffix");
//根据业务，自己进行模板赋值。
$this->display();
}

HTML code:

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=utf-8">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 11">
</head>
<body>
<table border=1 cellpadding=0 cellspacing=0 width="100%" >
<tr>
<td colspan="5" align="center">
<h2>成绩单</h2>
</td>
</tr>
<tr>
<td style=&#39;width:54pt&#39; align="center">编号</td>
<td style=&#39;width:54pt&#39; align="center">姓名</td>
<td style=&#39;width:54pt&#39; align="center">语文</td>
<td style=&#39;width:54pt&#39; align="center">数学</td>
<td style=&#39;width:54pt&#39; align="center">英语</td>
</tr>
<tr>
<td align="center">1</td>
<td style="background-color: #00CC00;" align="center">Jone</td>
<td style="background-color: #00adee;" align="center">90</td>
<td style="background-color: #00CC00;" align="center">85</td>
<td style="background-color: #00adee;" align="center">100</td>
</tr>
<tr>
<td align="center">2</td>
<td style="background-color: #00CC00;" align="center">Tom</td>
<td style="background-color: #00adee;" align="center">99</td>
<td style="background-color: #00CC00;" align="center">85</td>
<td style="background-color: #00adee;" align="center">80</td>
</tr>
</table>
</body>
</html>

Let’s look at a more convenient component

Here we need to use PEAR’s two software packages Spreadsheet Excel Writer and OLE. If not, you can download them from http: Download //pear.php.net/package/Spreadsheet_Excel_Writer/ and http://pear.php.net/package/OLE/, unzip and put them in the PEAR directory.

The entire code is as follows:

<?php
include &#39;Writer.php&#39;;

/* *** 准备导出的数据 *** */
$head = &#39;One Week Schedule&#39;;
$data = array(&#39;Monday&#39; =>   array( array(&#39;time&#39; => &#39;09:00&#39;, &#39;event&#39; => &#39;公司例会例会&#39;),
                      array(&#39;time&#39; => &#39;14:00&#39;, &#39;event&#39; => &#39;部门例会&#39;)
                    ),
         &#39;Tuesday&#39; =>   array( array(&#39;time&#39; => &#39;09:30&#39;, &#39;event&#39; => &#39;和 Mr. Stinsen 早餐&#39;)),
         &#39;Wednesday&#39; =>   array(array(&#39;time&#39; => &#39;12:10&#39;, &#39;event&#39; => &#39;市场中阶报告&#39;),
                    array(&#39;time&#39; => &#39;15:30&#39;, &#39;event&#39; => &#39;市场部战略部署会议&#39;) ),
         &#39;Thursday&#39; =>   array( array(&#39;time&#39; => &#39;&#39;, &#39;event&#39; => &#39;&#39;)),
         &#39;Friday&#39; =>   array( array(&#39;time&#39; => &#39;16:00&#39;, &#39;event&#39; => &#39;WoC Stock 研讨会&#39;),
                    array(&#39;time&#39; => &#39;17:00&#39;, &#39;event&#39; => &#39;飞往华尔街&#39;),
                    array(&#39;time&#39; => &#39;21:00&#39;, &#39;event&#39; => &#39;会见克林顿&#39;))
     );
/* *** *** */

$workbook = new Spreadsheet_Excel_Writer();
$filename = date(&#39;YmdHis&#39;).&#39;.xls&#39;;//csv
$workbook->send($filename); // 发送 Excel 文件名供下载
$workbook->setVersion( 8 );

$sheet = &$workbook->addWorksheet("Sheet1");   // 创建工作表
$sheet->setInputEncoding(&#39;utf-8&#39;);          // 字符集
$headFormat = &$workbook->addFormat(array(&#39;Size&#39; => 14, &#39;Align&#39; => &#39;center&#39;,&#39;Color&#39; => &#39;white&#39;, &#39;FgColor&#39; => &#39;brown&#39;, &#39;Bold&#39;=>&#39;1&#39;, &#39;Border&#39; => &#39;1&#39;));//定义格式
$dayFormat = &$workbook->addFormat(array(&#39;Size&#39; => 12, &#39;Align&#39; => &#39;center&#39;, &#39;VAlign&#39; => &#39;vcenter&#39;, &#39;FgColor&#39; => &#39;green&#39;, &#39;Color&#39; => &#39;white&#39;, &#39;Border&#39; => &#39;1&#39;));//定义格式
$dataFormat = &$workbook->addFormat(array(&#39;Size&#39; => 10, &#39;Align&#39; => &#39;left&#39;, &#39;Border&#39; => &#39;1&#39;, &#39;Color&#39; => &#39;black&#39;, &#39;FgColor&#39;=> &#39;cyan&#39;));//定义格式

$sheet->setColumn(0, 0, 20);   // 设置宽度
$sheet->setColumn(1, 1, 15);   // 设置宽度
$sheet->setColumn(2, 2, 30);   // 设置宽度

$r = 0;   
$sheet->write(0, $r, $head, $headFormat);   // 表格标题
$sheet->mergeCells(0, 0, 0, 2);   // 跨列显示

$r++;   // 数据从第2行开始
foreach ($data as $day => $events){
   $c = 0;
   $sheet->write($r, $c, $day, $dayFormat);
   if (!$events){
     // 当天没有计划
     $r++;
   } else {
     $startRow = $r;
     foreach ($events as $e){
        $c = 1;
        $sheet->write($r, $c++, $e[&#39;time&#39;], $dataFormat);   // 工作表写入数据
        $sheet->write($r, $c++, $e[&#39;event&#39;], $dataFormat);   // 工作表写入数据
        $r++;
     }
     // 合并 $day 单元格
     $sheet->mergeCells($startRow, 0, $r - 1, 0);
   }
}
$workbook->close(); // 完成下载
 ?>

Summary: The above is the entire content of this article, I hope it will be helpful to everyone's study.

Related recommendations:

phpdetailed socket usage

php中strtotime function performance analysis_phpTips

##phpThe latest solution to the error Call to undefined function curl_init()

The above is the detailed content of PHP implements exporting styled Excel instance sharing. For more information, please follow other related articles on the PHP Chinese website!

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：PHP implements a method to force the callback type to be specified based on CallableNext article：PHP implements a method to force the callback type to be specified based on Callable

See more

PHP implements exporting styled Excel instance sharing

Related articles