今天分享下laravel中的Maatwebsite/Laravel-Excel简单使用,但是这其实不难的,所以,老鸟勿喷,请继续飞。。。
安装过程就直接看github的就行了,很详细。。。
以下上代码吧,不罗嗦了: - <?php
- use Maatwebsite\Excel\Classes\LaravelExcelWorksheet;
- use Maatwebsite\Excel\Exceptions\LaravelExcelException;
- use Maatwebsite\Excel\Facades\Excel;
- use Maatwebsite\Excel\Writers\CellWriter;
- use Maatwebsite\Excel\Writers\LaravelExcelWriter;
- use PHPExcel_Exception;
- trait HandlerExcelTrait
- {
- /**
- * @var array
- */
- protected $cellLetter = [
- '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','AA','AB','AC','AD',
- 'AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN',
- 'AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ'
- ];
- /**
- * $data = Array(
- * [0] => Array(
- * [0] => 序号
- * )
- * [1] => Array(
- * [id] => 1
- * )
- * )
- * $title = ['测试']
- * @param string $fileName
- * @param array $title
- * @param array $data
- * @return mixed
- * @throws LaravelExcelException
- */
- public function exportExcel(string $fileName, array $title, array $data)
- {
- try {
- return Excel::create($fileName, function ($excel) use ($data, $title) {
- /** @var LaravelExcelWriter $excel */
- $excel->sheet('sheet', function ($sheet) use ($data, $title) {
- /** @var LaravelExcelWorksheet $sheet */
- $column = $this->cellLetter[count($data[0]) - 1];
- try {
- $sheet->fromArray($data, null, 'A1', true, false);
- } catch (PHPExcel_Exception $e) {
- throw new LaravelExcelException($e->getMessage());
- }
- /** 此为设置整体样式 */
- $sheet->setStyle([
- 'font' => [
- 'name' => 'Calibri',
- 'size' => 12,
- 'bold' => false,
- ]
- ])
- ->prependRow($title)
- ->row(1, function ($row) {
- /** @var CellWriter $row */
- $row->setFont(array( //设置标题的样式
- 'family' => 'Calibri',
- 'size' => '16',
- 'bold' => true
- ));
- })
- ->mergeCells('A1:' . $column . '1')
- ->cell('A2:' . $column . '2', function ($cells) {
- /** @var CellWriter $cells */
- $cells->setBackground('#AAAAFF');
- })->setHeight(1, 30)
- ->setAutoFilter('A2:' . $column . '2'); //设置自动过滤
- /** 此为针对每行的高宽进行设置 */
- for ($i = 2; $i <= count($data[0]) + 1; $i++) {
- $sheet->setHeight($i, 20);
- $sheet->setWidth($this->cellLetter[$i - 1], 30);
- $sheet->row($i - 1, function ($row) {
- /** @var CellWriter $row */
- $row->setAlignment('center');
- $row->setValignment('center');
- });
- }
- });
- })->export('xlsx');
- } catch (LaravelExcelException $e) {
- throw new LaravelExcelException($e->getMessage());
- }
- }
- }
复制代码- // 设置表格样式
- $sheet -> row(1,function($row){
- // 单元格处理方法
- $row ->setBackground('#ff3300'); // 设置单元格背景
- $row ->setFontColor('#ffffff'); // 改变字体颜色
- // 分开设置字体
- /*$row ->setFontSize(18); // 改变字体大小
- $row ->setFontFamily('Calibri'); // 设置字体
- $row -> setFontWeight('bold'); // 字体设置为粗体*/
- //要改变当前表的字体用:->setFont($array)
- $row ->setFont([
- 'family' => 'Calibri',
- 'size' => '18',
- 'bold' => true
- ]);
- // 设置边框
- // $row -> setBorder('solid','none','none','solid');
- //设置水平对齐
- $row ->setAlignment('center');
- //设置垂直对齐
- $row ->setValignment('middle');
- });
- });
- // 设置行高
- // $sheet->setHeight(1, 110);
- // 为多个行设置高度
- $sheet->setHeight(array(
- 1 => 50,
- 2 => 25
- ));
- // 设置所有边框
- $sheet->setAllBorders('thin');
- // 设置单元格尺寸
- $sheet->setSize('A1', 10, 10);
复制代码- // 在第1行后插入
- $sheet->appendRow(1, array(
- '0','1','2','3'
- ));
- // 插入最后
- $sheet->appendRow(array(
- '商品名','价格','库存','备注'
- ));
复制代码- // 添加到第1行前
- $sheet->prependRow(1, array(
- '商品名','价格','库存','备注'
- ));
- // 添加到最前面
- $sheet->prependRow(array(
- '商品名','价格','库存','备注'
- ));
复制代码
|