2019-01-30 13:18:49 fareast_mzh 阅读数 422

关联文章: https://blog.csdn.net/fareast_mzh/article/details/86712896

 

composer require phpoffice/phpexcel

https://github.com/PHPOffice/PHPExcel

 

* ./app/Http/Controllers/PackageController.php

https://laravel.com/docs/5.6/controllers

php artisan make:controller PackageController--invokable

 

<?php

namespace App\Http\Controllers;

use App\Exports\PackagesExport;
use App\Packages;
use Illuminate\Support\Facades\Log;
// use Maatwebsite\Excel\Facades\Excel;

class PackageController extends Controller {

    /**
     * http://localhost:8000/package/excel
     */
    public function excel() {
        $pids = ['P39965','P39966','P39967','P39968','P39969','P39970','p42196','p42197','p42198','P39971','P39972',
            'P39973','P39974','P39975','P39976','P41507','p42199','p42200','p42201','P39989','P40507','P39952',
            'P39990','P40508','P39953','P39991','P39992','P40509','P39954','P39955','P40510','P39993','P40511',
            'P39956','P39994','P40513','P39957','P40514','P39995','P39996','P41983','P41984','P41985','P41986',
            'P41989','P41992','P41993','P41994','P40512'];

        $data = [];
        foreach ($pids as $pid) {
            $p = Packages::getByPids($pid);
            /** @var $it \App\Packages */
            foreach ($p as $it) {
                array_push($data, $it->getRowAsArray());
            }
        }
        $title = Packages::getHeaders();
        return $this->_doExcelExport($data, $title, '导出pids码2016');
    }

    /**
     *
     */
    /**
     * 导出数据为excel表格
     * @param array $data array 一个二维数组,结构如同从数据库查出来的数组
     * @param array $title string excel的第一行标题,一个数组,如果为空则没有标题
     * @param string $filename string 下载的文件名
     * @return int
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     * @example
    $stu = M ('User');
    $arr = $stu->select();
    $this->_doExcelExport($arr,array('id','账户','密码','昵称'),'文件名!');
     */
    private function _doExcelExport(&$data=array(),$title=array(),$filename='report') {
        header("Content-type:application/octet-stream");
        header("Accept-Ranges:bytes");
        header("Content-type:application/vnd.ms-excel");
        header("Content-Disposition:attachment;filename=".$filename.".xlsx");
        header("Pragma: no-cache");
        header("Expires: 0");

        // 2. 实例化一个phpexcel
        $objPHPExcel = new \PHPExcel();

        //  Limits the maximum execution time unlimit
        set_time_limit(0);

        // 设置 在第一个标签中写入数据 设置活动的sheet是第一个 从0开始
        try {
            $sheet = $objPHPExcel->setActiveSheetIndex(0);
        } catch (\PHPExcel_Exception $e) {
            Log::error($e->getTraceAsString());
            return -1;
        }

        // 导出xls 开始
        $letter = array();
        // 根据$title个数生成excel表的列A, B, C...
        if (!empty($title)) {
            foreach ($title as $i => $field) {
                array_push($letter, chr(ord('A') + $i));
                $sheet->setCellValue($letter[$i]."1", $field);
            }
        } else {
            foreach ($data[0] as $i => $field) {
                array_push($letter, chr(ord('A') + $i));
            }
        }
        if (!empty($data)) {
            foreach ($data as $i => $row) {
                $ii = 0;   // from 'A',...
                foreach ($data[$i] as $key => $value) {
                    // 从第2行开始
                    $sheet->setCellValue($letter[$ii].strval($i+2), "'".strval($value));
                    $ii++;
                }
            }
        }

        // 保存
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        ob_clean();    // 关键
        flush();       // 关键
        $objWriter->save('php://output');
        return 0;
    }


}

* ./app/Packages.php 

php artisan make:model Packages

https://blog.csdn.net/fareast_mzh/article/details/86703181  (私密文章)

<?php
// 模型文件

* ./routes/web.php

<?php

/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/

Route::get('/', function () {
    return view('welcome');
});

Route::get('/package/index', 'PackageController@index');

Route::get('/package/export', 'PackageController@export');

Route::get('/package/excel', 'PackageController@excel');

* 启动服务

https://laravel.com/docs/5.6

E:\code\export>php artisan serve
Laravel development server started: <http://127.0.0.1:8000>
 

* composer.json

{
    "name": "laravel/laravel",
    "type": "project",
    "description": "The Laravel Framework.",
    "keywords": [
        "framework",
        "laravel"
    ],
    "license": "MIT",
    "require": {
        "php": "^7.1.3",
        "fideloper/proxy": "^4.0",
        "laravel/framework": "5.7.*",
        "laravel/tinker": "^1.0",
        "maatwebsite/excel": "^3.1",
        "phpoffice/phpexcel": "^1.8",
        "phpoffice/phpspreadsheet": "^1.6"
    },
    "require-dev": {
        "beyondcode/laravel-dump-server": "^1.0",
        "filp/whoops": "^2.0",
        "fzaninotto/faker": "^1.4",
        "mockery/mockery": "^1.0",
        "nunomaduro/collision": "^2.0",
        "phpunit/phpunit": "^7.0"
    },
    "config": {
        "optimize-autoloader": true,
        "preferred-install": "dist",
        "sort-packages": true
    },
    "extra": {
        "laravel": {
            "dont-discover": []
        }
    },
    "autoload": {
        "psr-4": {
            "App\\": "app/"
        },
        "classmap": [
            "database/seeds",
            "database/factories"
        ]
    },
    "autoload-dev": {
        "psr-4": {
            "Tests\\": "tests/"
        }
    },
    "minimum-stability": "dev",
    "prefer-stable": true,
    "scripts": {
        "post-autoload-dump": [
            "Illuminate\\Foundation\\ComposerScripts::postAutoloadDump",
            "@php artisan package:discover --ansi"
        ],
        "post-root-package-install": [
            "@php -r \"file_exists('.env') || copy('.env.example', '.env');\""
        ],
        "post-create-project-cmd": [
            "@php artisan key:generate --ansi"
        ]
    }
}

 

 

* assign.js

var s = "guid, name, coursecontent, teacherids, grades, pricedescription, packageimage, servicecontent, ispublish, opentime, closetime, shelves, unshelfves, type, tutortimes, subject";
var a = s.split(', ');
var code = a.map(function(it) {
    return "$a[] = $this->"+it+";";
}).join("\n");
console.log(code);

output:

$a[] = $this->guid;
$a[] = $this->name;
$a[] = $this->coursecontent;
$a[] = $this->teacherids;
$a[] = $this->grades;
$a[] = $this->pricedescription;
$a[] = $this->packageimage;
$a[] = $this->servicecontent;
$a[] = $this->ispublish;
$a[] = $this->opentime;
$a[] = $this->closetime;
$a[] = $this->shelves;
$a[] = $this->unshelfves;
$a[] = $this->type;
$a[] = $this->tutortimes;
$a[] = $this->subject;

 

2019-07-31 19:59:03 diandianxiyu 阅读数 1093

安装phpoffice/phpspreadsheet环境OneinStack安装ext-fileinfo这个扩展

Composer安装phpoffice/phpspreadsheet

1.添加"phpoffice/phpspreadsheet":"1.8.*"composer.json
2.执行composer install

报错

1 首先出现下面的提示

Installation request for phpoffice/phpspreadsheet 1.8.2 -> satisfiable by phpoffice/phpspreadsheet[1.8.2].

1.8.*改成1.8.2即可

2 缺少扩展fileinfo

对应的提示如下

- phpoffice/phpspreadsheet 1.8.2 requires ext-fileinfo * -> the requested PHP extension fileinfo is missing from your system.
To enable extensions, verify that they are enabled in your .ini files:
- /usr/local/php/etc/php.ini
- /usr/local/php/etc/php.d/03-imagick.ini
- /usr/local/php/etc/php.d/05-memcached.ini
- /usr/local/php/etc/php.d/05-redis.ini
You can also run `php --ini` inside terminal to see which files are used by PHP in CLI mode.

也就是说 ,我没有安装 ext-fileinfo这个扩展
解决这个问题的方式就是去安装这个扩展

官方地址 https://www.php.net/manual/zh/book.fileinfo.php

我的PHP是通过OneinStack安装的

参考文档
https://oneinstack.com/question/oneinstack-how-to-support-the-fileinfo/

[root@VM_0_15_centos oneinstack]# ./addons.sh
#######################################################################
#       OneinStack for CentOS/RedHat 6+ Debian 7+ and Ubuntu 12+      #
#                    Install/Uninstall Extensions                     #
#       For more information please visit https://oneinstack.com      #
#######################################################################
2048+0 records in
2048+0 records out
2147483648 bytes (2.1 GB) copied, 18.5868 s, 116 MB/s
Setting up swapspace version 1, size = 2097148 KiB
no label, UUID=c25345f9-fe45-4252-9987-df3661c04525
swapon: /swapfile: insecure permissions 0644, 0600 suggested.
What Are You Doing?
         1. Install/Uninstall PHP opcode cache
         2. Install/Uninstall ZendGuardLoader/ionCube/SourceGuardian PHP Extension
         3. Install/Uninstall ImageMagick/GraphicsMagick PHP Extension
         4. Install/Uninstall fileinfo/imap/phalcon/mongodb PHP Extension
         5. Install/Uninstall memcached/memcache
         6. Install/Uninstall Redis
         7. Install/Uninstall swoole PHP Extension
         8. Install/Uninstall xdebug PHP Extension
         9. Install/Uninstall PHP Composer
        10. Install/Uninstall fail2ban
        11. Install/Uninstall ngx_lua_waf
         q. Exit
Please input the correct option: 4
Please select an action:
        1. install
        2. uninstall
Please input a number:(Default 1 press Enter) 1
Please select fileinfo/imap/phalcon/mongodb:
        1. fileinfo
        2. imap
        3. phalcon
        4. mongodb
Please input a number:(Default 1 press Enter) 1
[php-7.3.0.tar.gz] found
~/oneinstack/src/php-7.3.0/ext/fileinfo ~/oneinstack/src ~/oneinstack ~/oneinstack
Configuring for:
PHP Api Version:         20180731
Zend Module Api No:      20180731
Zend Extension Api No:   320180731
checking for grep that handles long lines and -e... /bin/grep
checking for egrep... /bin/grep -E
checking for a sed that does not truncate output... /bin/sed
checking for cc... cc
checking whether the C compiler works... yes
checking for C compiler default output file name... a.out
checking for suffix of executables...
checking whether we are cross compiling… no
此处省略
----------------------------------------------------------------------
Libraries have been installed in:
   /root/oneinstack/src/php-7.3.0/ext/fileinfo/modules
If you ever happen to want to link against installed libraries
in a given directory, LIBDIR, you must either use libtool, and
specify the full pathname of the library, or use the `-LLIBDIR'
flag during linking and do at least one of the following:
   - add LIBDIR to the `LD_LIBRARY_PATH' environment variable
     during execution
   - add LIBDIR to the `LD_RUN_PATH' environment variable
     during linking
   - use the `-Wl,--rpath -Wl,LIBDIR' linker flag
   - have your system administrator add LIBDIR to `/etc/ld.so.conf'
See any operating system documentation about shared libraries for
more information, such as the ld(1) and ld.so(8) manual pages.
----------------------------------------------------------------------
Build complete.
Don't forget to run 'make test'.
Installing shared extensions:     /usr/local/php/lib/php/extensions/no-debug-non-zts-20180731/
Redirecting to /bin/systemctl restart php-fpm.service
PHP fileinfo module installed successfully!
What Are You Doing?
         1. Install/Uninstall PHP opcode cache
         2. Install/Uninstall ZendGuardLoader/ionCube/SourceGuardian PHP Extension
         3. Install/Uninstall ImageMagick/GraphicsMagick PHP Extension
         4. Install/Uninstall fileinfo/imap/phalcon/mongodb PHP Extension
         5. Install/Uninstall memcached/memcache
         6. Install/Uninstall Redis
         7. Install/Uninstall swoole PHP Extension
         8. Install/Uninstall xdebug PHP Extension
         9. Install/Uninstall PHP Composer
        10. Install/Uninstall fail2ban
        11. Install/Uninstall ngx_lua_waf
         q. Exit
Please input the correct option: q

扩展安装完成
查看扩展是否安装完成

[root@VM_0_15_centos oneinstack]# php -

然后继续安装

- Installing phpoffice/phpspreadsheet (1.8.2): Downloading (connecting...)Downloading (0%) Downloading (5%)Downloading (10%)Downloading (15%)Downloading (20%)Downloading (25%)Downloading (30%)Downloading (35%)Downloading (40%)Downloading (45%)Downloading (50%)Downloading (55%)Downloading (60%)Downloading (65%)Downloading (70%)Downloading (75%)Downloading (80%)Downloading (85%)Downloading (90%)Downloading (95%)Downloading (100%)

安装完成。

参考资料

2019-11-14 18:01:24 lqm417626953 阅读数 13

下载phpoffice/phpspreadsheet

composer require phpoffice/phpspreadsheet 1.8.2

这个版本兼容PHP5和PHP7

1、新建Excel.php文件

<?php


namespace app\admin\controller;


use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use think\Controller;

class Excel extends Controller
{
    /**
     * 使用PHPEXECL导入
     *
     * @param string $file      文件地址
     * @param int    $sheet     工作表sheet(传0则获取第一个sheet)
     * @param int    $columnCnt 列数(传0则自动获取最大列)
     * @param array  $options   操作选项
     *                          array mergeCells 合并单元格数组
     *                          array formula    公式数组
     *                          array format     单元格格式数组
     *
     * @return array
     * @throws Exception
     */
    function importExecl($file = '', $sheet = 0, $columnCnt = 0, &$options = [])
    {
        try {
            /* 转码 */
            $file = iconv("utf-8", "gb2312", $file);

            if (empty($file) OR !file_exists($file)) {
                $msg['code'] = 0;
                $msg['tips'] = '文件不存在!';
                return $msg;
                //throw new \Exception('文件不存在!');
            }

            /** @var Xlsx $objRead */
            $objRead = IOFactory::createReader('Xlsx');

            if (!$objRead->canRead($file)) {
                /** @var Xls $objRead */
                $objRead = IOFactory::createReader('Xls');

                if (!$objRead->canRead($file)) {
                    $msg['code'] = 0;
                    $msg['tips'] = '只支持导入Excel文件!';
                    return $msg;
                    //throw new \Exception('只支持导入Excel文件!');
                }
            }

            /* 如果不需要获取特殊操作,则只读内容,可以大幅度提升读取Excel效率 */
            empty($options) && $objRead->setReadDataOnly(true);
            /* 建立excel对象 */
            $obj = $objRead->load($file);
            /* 获取指定的sheet表 */
            $currSheet = $obj->getSheet($sheet);

            if (isset($options['mergeCells'])) {
                /* 读取合并行列 */
                $options['mergeCells'] = $currSheet->getMergeCells();
            }

            if (0 == $columnCnt) {
                /* 取得最大的列号 */
                $columnH = $currSheet->getHighestColumn();
                /* 兼容原逻辑,循环时使用的是小于等于 */
                $columnCnt = Coordinate::columnIndexFromString($columnH);
            }

            /* 获取总行数 */
            $rowCnt = $currSheet->getHighestRow();
            $data   = [];

            /* 读取内容 */
            for ($_row = 1; $_row <= $rowCnt; $_row++) {
                $isNull = true;

                for ($_column = 1; $_column <= $columnCnt; $_column++) {
                    $cellName = Coordinate::stringFromColumnIndex($_column);
                    $cellId   = $cellName . $_row;
                    $cell     = $currSheet->getCell($cellId);

                    if (isset($options['format'])) {
                        /* 获取格式 */
                        $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
                        /* 记录格式 */
                        $options['format'][$_row][$cellName] = $format;
                    }

                    if (isset($options['formula'])) {
                        /* 获取公式,公式均为=号开头数据 */
                        $formula = $currSheet->getCell($cellId)->getValue();

                        if (0 === strpos($formula, '=')) {
                            $options['formula'][$cellName . $_row] = $formula;
                        }
                    }

                    if (isset($format) && 'm/d/yyyy' == $format) {
                        /* 日期格式翻转处理 */
                        $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
                    }

                    $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());

                    if (!empty($data[$_row][$cellName])) {
                        $isNull = false;
                    }
                }

                /* 判断是否整行数据为空,是的话删除该行数据 */
                if ($isNull) {
                    unset($data[$_row]);
                }
            }

            return $data;
        } catch (\Exception $e) {
            throw $e;
        }
    }


    /**
     * Excel导出
     *
     * @param array  $datas      导出数据,格式[['name' => 'alibaba','age' => 18]]
     * @param string $fileName   导出文件名称
     * @param array  $options    操作选项,例如:
     *                           bool   print       设置打印格式
     *                           string freezePane  锁定行数,例如表头为第一行,则锁定表头输入A2
     *                           array  setARGB     设置背景色,例如['A1', 'C1']
     *                           array  setWidth    设置宽度,例如['A' => 30, 'C' => 20]
     *                           bool   setBorder   设置单元格边框
     *                           array  mergeCells  设置合并单元格,例如['A1:J1' => 'A1:J1']
     *                           array  formula     设置公式,例如['F2' => '=IF(D2>0,E42/D2,0)']
     *                           array  format      设置格式,整列设置,例如['A' => 'General']
     *                           array  alignCenter 设置居中样式,例如['A1', 'A2']
     *                           array  bold        设置加粗样式,例如['A1', 'A2']
     *                           string savePath    保存路径,设置后则文件保存到服务器,不通过浏览器下载
     * @param array $param       数据键名  ['0' => 'name','1' => 'age']
     */
    function exportExcel($datas,$param, $fileName = '', $options = [])
    {
        try {
            if (empty($datas)) {
                return false;
            }
            if (empty($param)) {
                return false;
            }
            //列名
            $rows = ['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'];
            set_time_limit(0);
            /** @var Spreadsheet $objSpreadsheet */
            $objSpreadsheet = new Spreadsheet();
            //设置默认文字居左,上下居中
            $styleArray = [
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_LEFT,
                    'vertical'   => Alignment::VERTICAL_CENTER,
                ],
            ];
            $objSpreadsheet->getDefaultStyle()->applyFromArray($styleArray);
            //设置Excel Sheet
            $activeSheet = $objSpreadsheet->setActiveSheetIndex(0);

            //打印设置
            /*if (isset($options['print']) && $options['print']) {
                //设置打印为A4效果
                $activeSheet->getPageSetup()->setPaperSize(PageSetup:: PAPERSIZE_A4);
                //设置打印时边距
                $pValue = 1 / 2.54;
                $activeSheet->getPageMargins()->setTop($pValue / 2);
                $activeSheet->getPageMargins()->setBottom($pValue * 2);
                $activeSheet->getPageMargins()->setLeft($pValue / 2);
                $activeSheet->getPageMargins()->setRight($pValue / 2);
            }*/

            //行数据处理
            foreach ($datas as $sKey => $sItem) {
                //默认文本格式
                $pDataType = DataType::TYPE_STRING;
                //设置单元格格式
                /*if (isset($options['format']) && !empty($options['format'])) {
                    $colRow = Coordinate::coordinateFromString($sKey);
                    //存在该列格式并且有特殊格式
                    if (isset($options['format'][$colRow[0]]) &&
                        NumberFormat::FORMAT_GENERAL != $options['format'][$colRow[0]]) {
                        $activeSheet->getStyle($sKey)->getNumberFormat()
                            ->setFormatCode($options['format'][$colRow[0]]);

                        if (false !== strpos($options['format'][$colRow[0]], '0.00') &&
                            is_numeric(str_replace(['¥', ','], '', $sItem))) {
                            //数字格式转换为数字单元格
                            $pDataType = DataType::TYPE_NUMERIC;
                            $sItem     = str_replace(['¥', ','], '', $sItem);
                        }
                    } elseif (is_int($sItem)) {
                        $pDataType = DataType::TYPE_NUMERIC;
                    }
                }*/
                $sKey = $sKey + 1;
                for ($i = 0;$i < count($param);$i++) {
                    $activeSheet->setCellValueExplicit($rows[$i] .$sKey, $sItem[$param[$i]], $pDataType);
                }

                //存在:形式的合并行列,列入A1:B2,则对应合并
                /*if (false !== strstr($sKey, ":")) {
                    $options['mergeCells'][$sKey] = $sKey;
                }*/
            }

            unset($datas);

            //设置锁定行
            /*if (isset($options['freezePane']) && !empty($options['freezePane'])) {
                $activeSheet->freezePane($options['freezePane']);
                unset($options['freezePane']);
            }*/

            //设置宽度
            /*if (isset($options['setWidth']) && !empty($options['setWidth'])) {
                foreach ($options['setWidth'] as $swKey => $swItem) {
                    $activeSheet->getColumnDimension($swKey)->setWidth($swItem);
                }

                unset($options['setWidth']);
            }*/

            //设置背景色
            /*if (isset($options['setARGB']) && !empty($options['setARGB'])) {
                foreach ($options['setARGB'] as $sItem) {
                    $activeSheet->getStyle($sItem)
                        ->getFill()->setFillType(Fill::FILL_SOLID)
                        ->getStartColor()->setARGB(Color::COLOR_YELLOW);
                }

                unset($options['setARGB']);
            }*/

            //设置公式
            /*if (isset($options['formula']) && !empty($options['formula'])) {
                foreach ($options['formula'] as $fKey => $fItem) {
                    $activeSheet->setCellValue($fKey, $fItem);
                }

                unset($options['formula']);
            }*/

            //合并行列处理
            /*if (isset($options['mergeCells']) && !empty($options['mergeCells'])) {
                $activeSheet->setMergeCells($options['mergeCells']);
                unset($options['mergeCells']);
            }*/

            //设置居中
            /*if (isset($options['alignCenter']) && !empty($options['alignCenter'])) {
                $styleArray = [
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical'   => Alignment::VERTICAL_CENTER,
                    ],
                ];

                foreach ($options['alignCenter'] as $acItem) {
                    $activeSheet->getStyle($acItem)->applyFromArray($styleArray);
                }

                unset($options['alignCenter']);
            }*/

            //设置加粗
            /*if (isset($options['bold']) && !empty($options['bold'])) {
                foreach ($options['bold'] as $bItem) {
                    $activeSheet->getStyle($bItem)->getFont()->setBold(true);
                }

                unset($options['bold']);
            }*/

            //设置单元格边框,整个表格设置即可,必须在数据填充后才可以获取到最大行列
            /*if (isset($options['setBorder']) && $options['setBorder']) {
                $border    = [
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN, // 设置border样式
                            'color'       => ['argb' => 'FF000000'], // 设置border颜色
                        ],
                    ],
                ];
                $setBorder = 'A1:' . $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();
                $activeSheet->getStyle($setBorder)->applyFromArray($border);
                unset($options['setBorder']);
            }*/

            $fileName = !empty($fileName) ? $fileName : (date('YmdHis') . '.xlsx');

            if (!isset($options['savePath'])) {
                //直接导出Excel,无需保存到本地,输出07Excel文件
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header(
                    "Content-Disposition:attachment;filename=" . iconv(
                        "utf-8", "GB2312//TRANSLIT", $fileName
                    )
                );
                header('Cache-Control: max-age=0');//禁止缓存
                $savePath = 'php://output';
            } else {
                $savePath = $options['savePath'];
            }

            ob_clean();
            ob_start();
            $objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');
            $objWriter->save($savePath);
            //释放内存
            $objSpreadsheet->disconnectWorksheets();
            unset($objSpreadsheet);
            ob_end_flush();

            return true;
        } catch (Exception $e) {
            return false;
        }
    }
}

2.导入

//导入用户
    public function importUser()
    {
        $excel = new Excel();
        $file = request()->file('file');
        //获取表单上传文件   限制大小20M
        $info = $file->validate(['size' => 20971520, 'ext' => 'xlsx,xls'])->move(ROOT_PATH . 'public' . DS . 'excel');
        if ($info) {
            $exclePath = $info->getSaveName();
            //获取文件名
            $file_name = ROOT_PATH . 'public' . DS . 'excel/' . $exclePath;
            //获取导入数据
            $data = $excel->importExecl($file_name);
            array_shift($data);
            $insertData = [];
            foreach ($data as $key => $datum) {
                $insertData[$key]['username'] = $datum['A'];
                $insertData[$key]['password'] = md5(md5($datum['B']));
                $insertData[$key]['phone'] = $datum['C'];
                $insertData[$key]['email'] = $datum['D'];
                $insertData[$key]['create_time'] = ($datum['E'] - 25569) * 24 * 3600;
            }
            $success_count = $this->insertAll($insertData);
            $msg['code'] = 1;
            $msg['tips'] = '导入成功:' . $success_count . '条。';
            return $msg;
        } else {
            $msg['code'] = 0;
            $msg['tips'] = $file->getError();
            return $msg;
        }
    }

3、导出

//导出用户
    public function exportUser()
    {
        $excel = new Excel();
        $param = input('post.');
        $where = [];
        $where['status'] = $param['status'];
        $list = $this->where($where)->select()->toArray();
        foreach ($list as $key => $item) {
            $list[$key]['create_time'] = date('Y-m-d',$item['create_time']);
        }
        //设置表格标题
        $title = [
            'username' => '用户名',
            'password' => '密码',
            'phone' => '手机号',
            'email' => '邮箱',
            'create_time' => '注册时间',
        ];
        //添加标题到数组头部
        array_unshift($list,$title);
        //根据此参数获取数据中的值,要与表格标题键名对应
        $keys = ['username','password','phone','email','create_time'];
        $excel->exportExcel($list,$keys,'用户' . date('Y-m-d_His') . '.xlsx');
    }

 

2019-05-28 09:58:19 assasin0308 阅读数 527

1. 首先你得有Ci框架才行啊.....codeigniter

composer create-project codeigniter/framework

2. 直接撸代码..下载安装PHPoffice.....

composer require phpoffice/phpspreadsheet

3. 接下来,你懂得,封装一个模型方法,哪里使(bu)用(hui)哪(dian)里(na)调(li).......

ExportXlsModel.php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class ExportXlsModel extends CI_Model{
    /**
     * 导出excel
     * @param $data
     * @param string $title
     */
    public function export_excel($data, $title = '未命名.xlsx') {
       // var_dump($data);
        ini_set ('memory_limit', '1024M');
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        foreach ($data as $key1=>$sub_data) { //列
            foreach ($sub_data as $key2=>$item) { //行
                $sheet->setCellValueExplicitByColumnAndRow($key2+1, $key1+1,$item,'s');
            }
        }
        unset($data);
        $writer = new Xlsx($spreadsheet);
        unset($spreadsheet);
//      $writer->save($title);
        header("Pragma: public");
        header("Expires: 0");
        header('Access-Control-Allow-Origin:*');
        header('Access-Control-Allow-Headers:content-type');
        header('Access-Control-Allow-Credentials:true');
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");;
        header("Content-Disposition:attachment;filename=$title");
        header("Content-Transfer-Encoding:binary");
        $writer->save('php://output');
        exit();
    }
}

4. 用一下(ha)....

//把数据组装出呢个下面结构就可以啦......

$arr = [
  ['id','name','age','sex'],  //这一行是表头
  [1,'assasin',25,'male'], //第一行数据
  [2,'bob',20,'male'],  //第二行数据
  [3,'juli',18,'female'] //第三行数据
];

 $this->load->model('exportXlsModel');
 $this->exportXlsModel->export_excel($export_array,'导出结果.xlsx');

 

PHPWord使用简介

阅读数 7843

php操作office相关

阅读数 566

没有更多推荐了,返回首页