php操作Excel

参考文档:

http://www.cnblogs.com/windyet/articles/9711044.html

学习地址:

http://www.houdunren.com/houdunren18_lesson_240

插件下载地址:

https://packagist.org/?query=phpoffice

下载插件:

composer require phpoffice/phpspreadsheet

 

百度网盘下载地址:

链接：https://pan.baidu.com/s/1TyTfTUG_YjY0MQlalEw8Zg 密码：f9u4

 手册地址:

https://phpspreadsheet.readthedocs.io/en/develop/

环境要求:

Software requirements

The following software is required to develop using PhpSpreadsheet:

  • PHP version 5.6 or newer
  • PHP extension php_zip enabled
  • PHP extension php_xml enabled
  • PHP extension php_gd2 enabled (if not compiled in)

检测环境:

根目录下新建index.php文件

 

 1 <?php
 2 /**
 3  * Created by Haima.
 4  * Author:Haima
 5  * QQ:228654416
 6  * Date: 2018/7/22
 7  * Time: 9:35
 8  */
 9 
10 require 'vendor/autoload.php';
11 
12 /**
13  * 环境检测
14  */
15 
16 var_dump(PHP_VERSION);
17 var_dump(extension_loaded('zip'));
18 var_dump(extension_loaded('xml'));
19 var_dump(extension_loaded('gd'));

可以看到环境没有问题

index.php里写入数据并保存为Excel文件:

 1 <?php
 2 /**
 3  * Created by Haima.
 4  * Author:Haima
 5  * QQ:228654416
 6  * Date: 2018/7/22
 7  * Time: 9:35
 8  */
 9 
10 require 'vendor/autoload.php';
11 
12 ///**
13 // * 环境检测
14 // */
15 //
16 //var_dump(PHP_VERSION);
17 //var_dump(extension_loaded('zip'));
18 //var_dump(extension_loaded('xml'));
19 //var_dump(extension_loaded('gd'));
20 
21 
22 use PhpOffice\PhpSpreadsheet\Spreadsheet;
23 use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
24 
25 $spreadsheet = new Spreadsheet();
26 $spreadsheet->setActiveSheetIndex(0); //设置要操作的表单页Sheet,传入一个索引 0,相当于Excel里第一个sheet1
27 $sheet = $spreadsheet->getActiveSheet(); //获取当前要操作的表单
28 //设置单元格列宽度
29 $sheet->getColumnDimension('B')->setWidth(20); //设置B列宽度
30 $sheet->getColumnDimension('C')->setWidth(20); //设置C列宽度
31 
32 //设置表头
33 $sheet->setCellValue('A1', '编号')
34         ->setCellValue('B1','用户名')
35         ->setCellValue('B1','昵称')
36         ->setCellValue('B1','年龄');
37 
38 //要写入的数据
39 $data = [
40     [
41         'uid' =>1,
42         'username' =>'lisi',
43         'nickname' =>'李四',
44         'age' =>18
45     ],
46     [
47         'uid' =>2,
48         'username' =>'wangwu',
49         'nickname' =>'王五',
50         'age' =>19
51     ],
52     [
53         'uid' =>3,
54         'username' =>'mailiu',
55         'nickname' =>'麻六',
56         'age' =>20
57     ]
58 ];
59 $sheet->fromArray($data,null,'A2'); //从A2行开始写入数据
60 $writer = new Xlsx($spreadsheet);
61 $writer->save('WriteData.xlsx'); //设置保存文件名称

 效果:

 

 读取Excel数据:

根目录里新建readData.php

 

 1 <?php
 2 /**
 3  * Created by Haima.
 4  * Author:Haima
 5  * QQ:228654416
 6  * Date: 2018/7/22
 7  * Time: 10:17
 8  */
 9 
10 require 'vendor/autoload.php';
11 
12 /**
13  * 读取excel里的内容
14  */
15 use PhpOffice\PhpSpreadsheet\IOFactory;
16 $inputFillName = 'WriteData.xlsx'; //要读取的文件
17 $spreadsheet = IOFactory::load($inputFillName);  //载入要读取的文件
18 //从当前活动的表单里读取并转成数组的形式
19 $sheetData= $spreadsheet->getActiveSheet()->toArray(); 
20 echo '<pre>';
21 print_r($sheetData);

效果:

 

thinkphp里的用法

目录结构:

 

 

<?php
namespace app\index\controller;

use app\BaseController;
use think\facade\Db;
use PhpOffice\PhpSpreadsheet\IOFactory;
class Index extends BaseController
{
    public function index()
    {
        /**
         * 读取excel里的内容
         */
        $inputFillName = 'static/user.xlsx'; //要读取的文件
        $spreadsheet = IOFactory::load($inputFillName);  //载入要读取的文件
        //从当前活动的表单里读取并转成数组的形式
        $sheetData= $spreadsheet->getActiveSheet()->toArray();
        echo '<pre>';
        dd($sheetData);
    }

    public function hello($name = 'ThinkPHP6')
    {
        #$userInfo = Db::table('lb_xjgy_user')->where('id', 100000139)->find();
        # dd($userInfo);
        return 'hello,' . $name;
    }
}

效果:

 

 

 

 

 

 

 

 

 

 设置字体的大小:

目标:
设置B1为30号字体,B2以下的所有设置为20号字体
 1 <?php
 2 /**
 3  * Created by Haima.
 4  * Author:Haima
 5  * QQ:228654416
 6  * Date: 2018/7/22
 7  * Time: 9:35
 8  */
 9 
10 require 'vendor/autoload.php';
11 
12 ///**
13 // * 环境检测
14 // */
15 //
16 //var_dump(PHP_VERSION);
17 //var_dump(extension_loaded('zip'));
18 //var_dump(extension_loaded('xml'));
19 //var_dump(extension_loaded('gd'));
20 
21 
22 use PhpOffice\PhpSpreadsheet\Spreadsheet;
23 use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
24 use PhpOffice\PhpSpreadsheet\Style\Style;
25 use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
26 
27 //数据
28 $data = [
29     [
30         'uid' =>1,
31         'username' =>'lisi',
32         'nickname' =>'李四',
33         'age' =>18
34     ],
35     [
36         'uid' =>2,
37         'username' =>'wangwu',
38         'nickname' =>'王五',
39         'age' =>19
40     ],
41     [
42         'uid' =>3,
43         'username' =>'mailiu',
44         'nickname' =>'麻六',
45         'age' =>20
46     ]
47 ];
48 
49 $spreadsheet = new Spreadsheet();
50 $spreadsheet->setActiveSheetIndex(0); //设置要操作的表单页Sheet,传入一个索引 传入一个索引 0,相当于Excel里第一个sheet1
51 $sheet = $spreadsheet->getActiveSheet(); //获取当前要操作的表单
52 //设置单元格列宽度
53 $sheet->getColumnDimension('B')->setWidth(20); //设置B列宽度
54 $sheet->getColumnDimension('C')->setWidth(20); //设置C列宽度
55 
56 //设置字体的大小
57 $style = new Style();
58 $style->getFont()->setSize(30);
59 //设置从B2的单元格的值开始向下开始循环到最后
60 $column = Coordinate::stringFromColumnIndex(2) . 1; //相当操作B1
61 $sheet->duplicateStyle($style,$column); //设置样式$style:字体大小,$column:要设置的单格
62 
63 $style = new Style();
64 for($i=0;$i<count($data);$i++){
65     $style->getFont()->setSize(20);
66     $column = Coordinate::stringFromColumnIndex(2) . ($i+2);
67     $sheet->duplicateStyle($style,$column);
68 
69 }
70 
71 
72 //设置表头
73 $sheet->setCellValue('A1', '编号')
74         ->setCellValue('B1','用户名')
75         ->setCellValue('B1','昵称')
76         ->setCellValue('B1','年龄');
77 
78 //要写入的数据
79 $sheet->fromArray($data,null,'A2'); //从A2行开始写入数据
80 $writer = new Xlsx($spreadsheet);
81 $writer->save('WriteData.xlsx'); //设置保存文件名称

效果:

 

设置字体/边框/填充

 

  1 <?php
  2 /**
  3  * Created by Haima.
  4  * Author:Haima
  5  * QQ:228654416
  6  * Date: 2018/7/22
  7  * Time: 9:35
  8  */
  9 
 10 require 'vendor/autoload.php';
 11 
 12 ///**
 13 // * 环境检测
 14 // */
 15 //
 16 //var_dump(PHP_VERSION);
 17 //var_dump(extension_loaded('zip'));
 18 //var_dump(extension_loaded('xml'));
 19 //var_dump(extension_loaded('gd'));
 20 
 21 
 22 use PhpOffice\PhpSpreadsheet\Spreadsheet;
 23 use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 24 use PhpOffice\PhpSpreadsheet\Style\Style;
 25 use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
 26 
 27 //数据
 28 $data = [
 29     [
 30         'uid' =>1,
 31         'username' =>'lisi',
 32         'nickname' =>'李四',
 33         'age' =>18
 34     ],
 35     [
 36         'uid' =>2,
 37         'username' =>'wangwu',
 38         'nickname' =>'王五',
 39         'age' =>19
 40     ],
 41     [
 42         'uid' =>3,
 43         'username' =>'mailiu',
 44         'nickname' =>'麻六',
 45         'age' =>20
 46     ]
 47 ];
 48 
 49 $spreadsheet = new Spreadsheet();
 50 $spreadsheet->setActiveSheetIndex(0); //设置要操作的表单页Sheet,传入一个索引 0,相当于Excel里第一个sheet1
 51 $sheet = $spreadsheet->getActiveSheet(); //获取当前要操作的表单
 52 //设置单元格列宽度
 53 $sheet->getColumnDimension('B')->setWidth(20); //设置B列宽度
 54 $sheet->getColumnDimension('C')->setWidth(20); //设置C列宽度
 55 
 56 //设置字体的大小
 57 $style = new Style();
 58 $style->getFont()->setSize(30);
 59 //设置从B2的单元格的值开始向下开始循环到最后
 60 $column = Coordinate::stringFromColumnIndex(2) . 1; //相当操作B1
 61 $sheet->duplicateStyle($style,$column); //设置样式$style:字体大小,$column:要设置的单格
 62 
 63 
 64 
 65 $style = new Style();
 66 for($i=0;$i<count($data);$i++){
 67     $style->getFont()->setSize(20);
 68     $column = Coordinate::stringFromColumnIndex(2) . ($i+2);
 69     $sheet->duplicateStyle($style,$column);
 70 
 71 }
 72 
 73 //设置字体/边框/填充
 74 $sheet->getStyle('B2:C4')->applyFromArray([
 75     'font' => [
 76         'name' => 'Arial',
 77         'bold' => true,
 78         'italic' => false,
 79         'underline' => \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_SINGLE,
 80         'strikethrough' => false, //删除线
 81         'color' => [
 82             'rgb' => '808080'
 83         ]
 84     ],
 85     'borders' => [
 86         'bottom' => [
 87             'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM,
 88             'color' => [
 89                 'rgb' => '808080'
 90             ]
 91         ],
 92         'left' => [
 93             'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM,
 94             'color' => [
 95                 'rgb' => '808080'
 96             ]
 97         ],
 98     ],
 99     'fill' =>[
100         'fillType'=>\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
101         'color'=>[
102             'argb'=>'FFCCFFCC'
103         ]
104     ]
105 ]);
106 
107 //设置表头
108 $sheet->setCellValue('A1', '编号')
109         ->setCellValue('B1','用户名')
110         ->setCellValue('B1','昵称')
111         ->setCellValue('B1','年龄');
112 
113 //要写入的数据
114 $sheet->fromArray($data,null,'A2'); //从A2行开始写入数据
115 $writer = new Xlsx($spreadsheet);
116 $writer->save('WriteData.xlsx'); //设置保存文件名称

 效果:

设置Excel的属性/文件里的sheet文件重命名:

效果:

sheet1改名为houdunren

 

从文件->属性里 看属性

 

生成统计数据:

代码:

 

效果:

 

读取文件:

 

 1 <?php
 2 /**
 3  * Created by Haima.
 4  * Author:Haima
 5  * QQ:228654416
 6  * Date: 2018/7/22
 7  * Time: 10:17
 8  */
 9 
10 require 'vendor/autoload.php';
11 
12 /**
13  * 读取excel里的内容
14  */
15 use PhpOffice\PhpSpreadsheet\IOFactory;
16 $inputFillName = 'WriteData.xlsx'; //要读取的文件
17 $spreadsheet = IOFactory::load($inputFillName);  //载入要读取的文件
18 //从当前活动的表单里读取并转成数组的形式
19 //$sheetData= $spreadsheet->getActiveSheet()->toArray();
20 
21 //从B2读取到D4
22 $sheetData= $spreadsheet->getActiveSheet()->rangeToArray('B2:D4');
23 
24 //从B2读取到D4,并转换数组的key下标为字母
25 //$sheetData= $spreadsheet->getActiveSheet()->rangeToArray('B2:D4',null,null,null,true);
26 echo '<pre>';
27 print_r($sheetData);

 

 

从B2读取到D4,并转换数组的key下标为字母:

1 //从B2读取到D4,并转换数组的key下标为字母
2 $sheetData= $spreadsheet->getActiveSheet()->rangeToArray('B2:D4',null,null,null,true);

12.导出数据并下载excel表格

  1 <?php
  2 /**
  3  * Created by Haima.
  4  * Author:Haima
  5  * QQ:228654416
  6  * Date: 2018/7/22
  7  * Time: 9:35
  8  */
  9 
 10 require 'vendor/autoload.php';
 11 
 12 ///**
 13 // * 环境检测
 14 // */
 15 //
 16 //var_dump(PHP_VERSION);
 17 //var_dump(extension_loaded('zip'));
 18 //var_dump(extension_loaded('xml'));
 19 //var_dump(extension_loaded('gd'));
 20 
 21 
 22 use PhpOffice\PhpSpreadsheet\Spreadsheet;
 23 use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
 24 use PhpOffice\PhpSpreadsheet\Style\Style;
 25 use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
 26 
 27 //数据
 28 $data = [
 29     [
 30         'uid' =>1,
 31         'username' =>'lisi',
 32         'nickname' =>'李四',
 33         'age' =>18
 34     ],
 35     [
 36         'uid' =>2,
 37         'username' =>'wangwu',
 38         'nickname' =>'王五',
 39         'age' =>19
 40     ],
 41     [
 42         'uid' =>3,
 43         'username' =>'mailiu',
 44         'nickname' =>'麻六',
 45         'age' =>12345678910
 46     ]
 47 ];
 48 
 49 $spreadsheet = new Spreadsheet();
 50 $spreadsheet->setActiveSheetIndex(0); //设置要操作的表单页Sheet,传入一个索引
 51 $sheet = $spreadsheet->getActiveSheet(); //获取当前要操作的表单
 52 //设置单元格列宽度
 53 $sheet->getColumnDimension('B')->setWidth(20); //设置B列宽度
 54 $sheet->getColumnDimension('C')->setWidth(20); //设置C列宽度
 55 //不以科学计数的解决方法,列宽设置的宽一些
 56 $sheet->getColumnDimension('D')->setWidth(20); //设置D列宽度
 57 
 58 //设置字体的大小
 59 $style = new Style();
 60 $style->getFont()->setSize(30);
 61 //设置从B2的单元格的值开始向下开始循环到最后
 62 $column = Coordinate::stringFromColumnIndex(2) . 1;
 63 $sheet->duplicateStyle($style,$column); //设置样式$style:字体大小,$column:要设置的单格
 64 
 65 
 66 
 67 $style = new Style();
 68 for($i=0;$i<count($data);$i++){
 69     $style->getFont()->setSize(20);
 70     $column = Coordinate::stringFromColumnIndex(2) . ($i+2);
 71     $sheet->duplicateStyle($style,$column);
 72 
 73 }
 74 
 75 //设置字体/边框/填充
 76 $sheet->getStyle('B2:C4')->applyFromArray([
 77     'font' => [
 78         'name' => 'Arial',
 79         'bold' => true,
 80         'italic' => false,
 81         'underline' => \PhpOffice\PhpSpreadsheet\Style\Font::UNDERLINE_SINGLE,
 82         'strikethrough' => false, //删除线
 83         'color' => [
 84             'rgb' => '808080'
 85         ]
 86     ],
 87     'borders' => [
 88         'bottom' => [
 89             'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM,
 90             'color' => [
 91                 'rgb' => '808080'
 92             ]
 93         ],
 94         'left' => [
 95             'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM,
 96             'color' => [
 97                 'rgb' => '808080'
 98             ]
 99         ],
100     ],
101     'fill' =>[
102         'fillType'=>\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
103         'color'=>[
104             'argb'=>'FFCCFFCC'
105         ]
106     ]
107 ]);
108 
109 //设置表头
110 $sheet->setCellValue('A1', '编号')
111         ->setCellValue('B1','用户名')
112         ->setCellValue('C1','昵称')
113         ->setCellValue('D1','年龄');
114 
115 //要写入的数据
116 $sheet->fromArray($data,null,'A2'); //从A2行开始写入数据
117 //$writer = new Xlsx($spreadsheet);
118 //$writer->save('WriteData.xlsx'); //设置保存文件名称
119 
120 //保存并下载生成的Excel文件
121 header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
122 header('Content-Disposition: attachment;filename="hello.xlsx"');  //保存的文件名字
123 
124 $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet,'Xlsx');
125 $writer->save('php://output');
126 exit;

效果:

 php中导出数据到excel时数字变为科学计数的解决方法_php技巧

https://yq.aliyun.com/ziliao/161770

 

 

 
posted @ 2018-07-22 09:30  HaimaBlog  阅读(898)  评论(1)  编辑  收藏  举报