• 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 PhpOfficePhpSpreadsheetSpreadsheet;
23 use PhpOfficePhpSpreadsheetWriterXlsx;
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 PhpOfficePhpSpreadsheetIOFactory;
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 appindexcontroller;

use appBaseController;
use thinkfacadeDb;
use PhpOfficePhpSpreadsheetIOFactory;
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 PhpOfficePhpSpreadsheetSpreadsheet;
23 use PhpOfficePhpSpreadsheetWriterXlsx;
24 use PhpOfficePhpSpreadsheetStyleStyle;
25 use PhpOfficePhpSpreadsheetCellCoordinate;
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 PhpOfficePhpSpreadsheetSpreadsheet;
 23 use PhpOfficePhpSpreadsheetWriterXlsx;
 24 use PhpOfficePhpSpreadsheetStyleStyle;
 25 use PhpOfficePhpSpreadsheetCellCoordinate;
 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' => PhpOfficePhpSpreadsheetStyleFont::UNDERLINE_SINGLE,
 80         'strikethrough' => false, //删除线
 81         'color' => [
 82             'rgb' => '808080'
 83         ]
 84     ],
 85     'borders' => [
 86         'bottom' => [
 87             'borderStyle' => PhpOfficePhpSpreadsheetStyleBorder::BORDER_MEDIUM,
 88             'color' => [
 89                 'rgb' => '808080'
 90             ]
 91         ],
 92         'left' => [
 93             'borderStyle' => PhpOfficePhpSpreadsheetStyleBorder::BORDER_MEDIUM,
 94             'color' => [
 95                 'rgb' => '808080'
 96             ]
 97         ],
 98     ],
 99     'fill' =>[
100         'fillType'=>PhpOfficePhpSpreadsheetStyleFill::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 PhpOfficePhpSpreadsheetIOFactory;
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 PhpOfficePhpSpreadsheetSpreadsheet;
 23 use PhpOfficePhpSpreadsheetWriterXlsx;
 24 use PhpOfficePhpSpreadsheetStyleStyle;
 25 use PhpOfficePhpSpreadsheetCellCoordinate;
 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' => PhpOfficePhpSpreadsheetStyleFont::UNDERLINE_SINGLE,
 82         'strikethrough' => false, //删除线
 83         'color' => [
 84             'rgb' => '808080'
 85         ]
 86     ],
 87     'borders' => [
 88         'bottom' => [
 89             'borderStyle' => PhpOfficePhpSpreadsheetStyleBorder::BORDER_MEDIUM,
 90             'color' => [
 91                 'rgb' => '808080'
 92             ]
 93         ],
 94         'left' => [
 95             'borderStyle' => PhpOfficePhpSpreadsheetStyleBorder::BORDER_MEDIUM,
 96             'color' => [
 97                 'rgb' => '808080'
 98             ]
 99         ],
100     ],
101     'fill' =>[
102         'fillType'=>PhpOfficePhpSpreadsheetStyleFill::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 = PhpOfficePhpSpreadsheetIOFactory::createWriter($spreadsheet,'Xlsx');
125 $writer->save('php://output');
126 exit;

    效果:

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

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

     

     
    [Haima的博客] http://www.cnblogs.com/haima/
  • 相关阅读:
    VIM移动
    坏账,断供,四大国有资产管理公司（AMC）：东方、长城、信达和华融
    2016MBA排名
    禅道行任务分解
    oracle教程:PLSQL常用方法汇总
    国内手机号码段，卫星电话号段
    Java异常处理和设计
    购买使用Linode VPS必须知晓的十个问题
    从配置文件中删除节点
    remove namespace from xml config file
  • 原文地址：https://www.cnblogs.com/haima/p/9349104.html
Copyright © 2020-2023  润新知