您现在的位置是:首页 > IT分类 > PHP网站首页PHPPHP对Excel的导入导出操作

PHP对Excel的导入导出操作

  • 蓝天
  • PHP
  • 2020-03-30 18:14:15
简介安装pecl 当我们发现pecl未安装时,则需要安装pecl。一般情况下,是安装在PHP的安装目录,示例命令如下:# 进入PHP安装目录cd /usr/local/php/bincurl -o go-pear.php http://pear.
安装pecl
  • 当我们发现pecl未安装时,则需要安装pecl。一般情况下,是安装在PHP的安装目录,示例命令如下:
# 进入PHP安装目录

cd /usr/local/php/bin

curl -o go-pear.php http://pear.php.net/go-pear.phar

php go-pear.php

# 安装完成后,软连接到bin目录下

ln -s /usr/local/php/bin/pecl /usr/bin/pecl
安装xlswriter
pecl install xlswriter

# 添加 extension = xlswriter.so 到 ini 配置
封装的导出service
/**

     * 下载

     * @param $header

     * @param $data

     * @param $fileName

     * @param $type

     * @return bool

     * @throws

     */

    public function download($header, $data, $fileName)

    {

        $config     = [

            'path' => $this->getTmpDir() . '/',

        ];

        $now        = date('YmdHis');

        $fileName   = $fileName . $now . '.xlsx';

        $xlsxObject = new \Vtiful\Kernel\Excel($config);

        // Init File

        $fileObject = $xlsxObject->fileName($fileName);

        // 设置样式

        $fileHandle = $fileObject->getHandle();

        $format     = new \Vtiful\Kernel\Format($fileHandle);

        $style      = $format->bold()->background(

            \Vtiful\Kernel\Format::COLOR_YELLOW

        )->align(Format::FORMAT_ALIGN_VERTICAL_CENTER)->toResource();

        // Writing data to a file ......

        $fileObject->header($header)

            ->data($data)

            ->freezePanes(1, 0)

            ->setRow('A1', 20, $style);

        // Outptu

        $filePath = $fileObject->output();

// 下载

 header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

        header('Content-Disposition: attachment;filename="' . $fileName . '"');

        header('Cache-Control: max-age=0');

        header('Content-Length: ' . filesize($filePath));

        header('Content-Transfer-Encoding: binary');

        header('Cache-Control: must-revalidate');

        header('Pragma: public');

        ob_clean();

        flush();

        if (copy($filePath, 'php://output') === false) {

            throw new RuntimeException('导出失败');

        }

       

        // Delete temporary file

        @unlink($filePath);

        return true;

    }

    /**

     * 获取临时文件夹

     * @return false|string

     */

    private function getTmpDir()

    {

      // 目录可以自定义

      // return \Yii::$app->params['downloadPath'];

       

        $tmp = ini_get('upload_tmp_dir');

        if ($tmp !== False && file_exists($tmp)) {

            return realpath($tmp);

        }

        return realpath(sys_get_temp_dir());

    }

    /**

     * 读取文件

     * @param $path

     * @param $fileName

     * @return array

     */

    public function readFile($path,$fileName)

    {

        // 读取测试文件

        $config = ['path' => $path];

        $excel  = new \Vtiful\Kernel\Excel($config);

        $data   = $excel->openFile($fileName)

            ->openSheet()

            ->getSheetData();

        return $data;

    }

调用处代码0A4蓝天个人博客-一个自学的好博客

导出0A4蓝天个人博客-一个自学的好博客

/**

 * 导出

 */

public function actionExport()

{

    try {

        /**

         * @var $searchModel SkuBarCodeSearch

         */

        $searchModel                     = Yii::createObject(SkuBarCodeSearch::className());

        $queryParams['SkuBarCodeSearch'] = [];

        $result = $searchModel->search($queryParams, true);

        $formatData = [];

        if (!empty($result)) {

            foreach ($result as $key => &$value) {

                $tmpData      = [

                    'sku_code'   => $value['sku_code'],

                    'bar_code'   => $value['bar_code'],

                    'created_at' => $value['created_at'],

                    'updated_at' => $value['updated_at'],

                ];

                $formatData[] = array_values($tmpData);

            }

            unset($value);

        }

        $fields = [

            'sku_code'   => 'SKU编码',

            'bar_code'   => '条形码',

            'created_at' => '创建时间',

            'updated_at' => '更新时间',

        ];

        /**

         * @var $utilService UtilService

         */

        $utilService = UtilService::getInstance();

        $utilService->download(array_values($fields), $formatData, 'sku_single_code');

    } catch (\Exception $e) {

        Yii::$app->getSession()->setFlash('error', '导出失败');

    }

}

导入0A4蓝天个人博客-一个自学的好博客

public function actionImportTmpSku()

    {

        try {

            /**

             * @var $utilService UtilService

             */

            $utilService = UtilService::getInstance();

            $path        = '/tmp/'; // 文件目录

            $fileName    = 'sku_0320.xlsx';

            $excelData   = $utilService->readFile($path, $fileName);

            unset($excelData[0]);

            $excelData = array_merge($excelData);

           // ... ... 业务代码

        } catch (\Exception $e) {

            echo $e->getMessage();

            exit;

        }

    }

原文:https://tsmliyun.github.io/php/PHP%E5%AF%BC%E5%87%BAExcel%E7%9A%84%E4%BC%98%E5%8C%96/0A4蓝天个人博客-一个自学的好博客

转载: 感谢您对蓝天个人博客网站平台的认可,非常欢迎各位朋友分享到个人站长或者朋友圈,但转载请说明文章出处“来源蓝天个人博客 http://www.”。

文章评论

联系方式