thinkPHP使用PHPExcel实现导入导出

目录

 

一、使用composer安装PHPExcel

二、使用PHPExcel

1、导入Excel文件

2、导出数据

3、导出方法使用demo

效果图:

---

 

一、使用composer安装PHPExcel

安装命令：composer require phpoffice/phpexcel

安装完成后：composer  update

PHPExcel已经不维护了,最高版本应该为1.8.* 如果安装的是1.8.*版本不使用更新命令也可。

 

二、使用PHPExcel

使用前准备：创建Excel工具类并引入所需类库。

<?php
declare (strict_types=1);

namespace app\common;

use PHPExcel_IOFactory;//必须：导出输出文件时使用
use PHPExcel;//核心

class PhpExcelTool
{

}

1、导入Excel文件

  /**
     * 导入数据
     * 读取Excel内容转为数组
     * @param $file_name //上传的Excel文件路径
     * @return array
     */
    function excelToArr($file_name)
    {
        //判断导入表格后缀格式
        $extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));
        if ($extension == 'xlsx') {
            $objReader = new \PHPExcel_Reader_Excel2007();
        } else if ($extension == 'xls') {
            $objReader = new \PHPExcel_Reader_Excel5();
        } elseif ($extension == 'csv') {
            setlocale(LC_ALL, 'zh_CN');
            $csv_file = fopen($file_name, 'r');
            while ($r = fgets($csv_file)) {
                $str = iconv('gbk', 'utf-8', $r);
                $arr[] = explode(',', $str);
            }
            return $arr;
        }
        $objPHPExcel = $objReader->load($file_name, $encode = 'utf-8');
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow();//取得总行数
        $highestColumn = $sheet->getHighestColumn(); //取得总列数
        $arr = $objPHPExcel->getSheet(0)->toArray();
        return $arr;
    }

2、导出数据

     /**
     * 导出数据
     * @param $res  数据
     * @param $columns  列
     * @param $title
     * @return mixed
     */ 

function exportExcel($res, $columns, $title)
    {
        //设置单元格的列数量（支持5*26=130列）
        $cols = ['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', 'BA', 'BB', 'BC',
            'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT',
            'BU', 'BV', 'BW', 'BX', 'BY', 'BZ', 'DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK',
            'DL', 'DM', 'DN', 'DO', 'DP', 'DQ', 'DR', 'DS', 'DT', 'DU', 'DV', 'DW', 'DX', 'DY', 'DZ'];
        //表格样式设置
        $max_col = $cols[count($columns) - 1] ?: 'A';
        $phpExcel = new PHPExcel();
        $objSheet = $phpExcel->getActiveSheet();
        $objSheet->setTitle(date('Ymd') . '导出');//设置sheet名字
        $objSheet->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置excel文件默认水平垂直方向居中
        $objSheet->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup:: PAPERSIZE_A3);     //A3纸大小
        //设置首行的头部样式
        $objSheet->setCellValue('A1', $title);  //设置单元格A1单元格内容
        $objSheet->getRowDimension(1)->setRowHeight(50); //行宽度
        $objSheet->mergeCells("A1:" . $max_col . "1");// 合并单元格
        $objSheet->getStyle("A1:" . $max_col . "1")->getFont()->setSize(20)->setName('微软雅黑')->setBold(true);//设置第一行字体大小和加粗
        foreach ((array)$columns as $key => $var) {
            $objSheet->setCellValue($cols[$key] . '2', $var['title']);//设置单元格第一列内容(字段)
            $objSheet->getColumnDimension($cols[$key])->setWidth(round($var['width'] / 6));//行宽度
        }

        $objSheet->getStyle("A2:" . $max_col . "2")->getFont()->setName('微软雅黑')->setBold(true); //标题行字体和加粗
        $objSheet->getRowDimension(2)->setRowHeight(22); //设置每个单元格的宽度尺寸
        $objSheet->getStyle("A2:" . $max_col . "2")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('cccccc');
        //设置单元格样式填充开始颜色
        //获取内容数据
        foreach ((array)$res as $kkk => &$vvv) {
            foreach ((array)$columns as $k => $v) {
                //长数值列解决长数字串显示为科学计数
                $objSheet->setCellValue($cols[$k] . (3 + $kkk), $vvv[$v['key']]);
                //长文本列设置文字自动换行
                if (in_array($v['key'], ['name', 'address'])) {
                    $objSheet->getStyle($cols[$k] . (3 + $kkk))->getAlignment()->setWrapText(true);
                }

                if (isset($v['align'])) {
                    if ($v['align'] == 'center') {
                        $objSheet->getStyle($cols[$k] . (3 + $kkk))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
                    } else {
                        $objSheet->getStyle($cols[$k] . (3 + $kkk))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
                    }
                }
            }
            //设置行高
//                $objSheet->getDefaultRowDimension(3+$kkk)->setRowHeight(-10);
        }
        //设置整体样式边框
        $styleArray = array(
            'borders' => array(
                'outline' => array(
                    'style' => \PHPExcel_Style_Border::BORDER_THICK,   //边框外线用粗线
                    'color' => array('rgb' => "333333"),//这里颜色没有#
                ),
                'allborders' => array( //设置全部边框
                    'style' => \PHPExcel_Style_Border::BORDER_THIN, //细线
                    'color' => array('rgb' => "333333"),
                ),
            ),
        );
//        return $excel;
        $objSheet->getStyle("A1:" . $max_col . (count($res) + 2))->applyFromArray($styleArray);  //excel样式
        $objSheet->getStyle("A3:" . $max_col . (count($res) + 2))->getFont()->setName('宋体');
        //$objSheet->getDefaultRowDimension("A3:".$max_col.(count($excel)+2))->setRowHeight(-1);

        ob_end_clean();//清除缓冲区  导出excel乱码问题
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename=' . $title . '".xls"');
        header("Content-Transfer-Encoding: binary");
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");

        $objWriter = PHPExcel_IOFactory::createWriter($phpExcel, 'Excel5');
        return $objWriter->save('php://output');//输出
    }

3、导出方法使用demo

    /**
     * export demo
     */
    public function export()
    {
        $res = [
            ['name' => '张三三', 'age' => 23, 'address' => '广东省深圳市南山区'],
            ['name' => '李思思', 'age' => 22, 'address' => '北京市朝阳区'],
            ['name' => '王武', 'age' => 26, 'address' => '上海市浦东新区'],
        ];

        $columns = [
            ['title' => '姓名', 'key' => 'name', 'width' => 100],
            ['title' => '年龄', 'key' => 'age', 'width' => 80],
            ['title' => '地址', 'key' => 'address', 'width' => 160],
        ];

        self::exportExcel($res, $columns, '某某班个人信息数据');
    }

效果图: