phpExcel简单导出/导入xlsx电子表格文件excel例子

使用PHPExcel库导出excel文件代码，Yii1.1框架下编写，相关代码需要自行调整

    /**
     * @param string $filepath
     * @param array $titles
     * @param int $startRow
     * @return array|false
     */
    public static function importExcelData($filepath, $titles, int $startRow)
    {
        $datas = [];
        try {
            Yii::import('application.vendors.phpexcel.*');
            require_once 'PHPExcel.php';

            //读取文件
            $objReader   = PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel = $objReader->load($filepath);
            //读取表格数据存入$datas
            $objPHPExcel->setActiveSheetIndex(0);
            $sheet      = $objPHPExcel->getSheet(0);
            $highestRow = $sheet->getHighestDataRow();
            $letters    = range('A', 'Z');
            $columns    = array_keys($titles);
            for ($row = $startRow; $row <= $highestRow; $row++) {
                $_data = [];
                foreach ($columns as $k => $key) {
                    $_data[$key] = $sheet->getCell("{$letters[$k]}{$row}")->getValue();
                }
                $datas[] = $_data;
            }
            return $datas;
        } catch (\Exception $e) {
            return false;
        }
    }

    /**
     * 导出Excel文件
     * @param $titles
     * @param $data
     * @param $filename
     * @return void
     * @throws CException
     */
    public static function exportExcel($titles, $data, $filename)
    {

        Yii::import('application.vendors.phpexcel.*');
        require_once 'PHPExcel.php';

        $objPHPExcel = new PHPExcel();
        try {
            //生成A到AZ的列名称数组
            $cols = range('A', 'Z');
            array_push($cols, ...array_map(function ($v) {
                return "A{$v}";
            }, $cols));
            $objSheet = $objPHPExcel->getActiveSheet();
            //工作表标题
            $objSheet->setTitle("外呼结果");
            $rowIdx = 1;
            $titleArr = array_values($titles);
            //第一行内容，放列标题
            foreach ($titleArr as $k=>$title) {
                $objSheet->setCellValue($cols[$k] . $rowIdx, $title);
            }
            //设置第一行的背景颜色
            $objSheet->getStyle("A{$rowIdx}:{$cols[$k]}1")
                ->getFill()
                ->setFillType(PHPExcel_Style_Fill::FILL_SOLID)
                ->getStartColor()
                ->setRGB('E8E8E8');
            //后面行循环放内容
            foreach ($data as $row) {
                $rowIdx++;
                foreach ($titleArr as $k=>$title) {
                    //如果值长度超过阈值，则只保存为字符型单元格格式，避免长数字显示为科学计数法
                    $value = $row[$k]??'';
                    if (is_numeric($value) && mb_strlen($value, 'UTF-8') > 10) {
                        $objSheet->setCellValueExplicit($cols[$k] . $rowIdx, $value);
                    } else {
                        $objSheet->setCellValue($cols[$k] . $rowIdx, $value);
                    }
                }
            }

            // Rename worksheet
            // Set active sheet index to the first sheet, so Excel opens this as the first sheet
            $objPHPExcel->setActiveSheetIndex(0);
            // Redirect output to a client’s web browser (Excel2007)
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
            header('Cache-Control: max-age=0');
            // If you're serving to IE 9, then the following may be needed
            header('Cache-Control: max-age=1');
            // If you're serving to IE over SSL, then the following may be needed
            header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
            header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
            header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
            header('Pragma: public'); // HTTP/1.0
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            $objWriter->save('php://output');
        } catch (\PHPExcel_Exception $e) {
            $line = __LINE__;
            $file = __FILE__;
            zmf::fp("导出文件 {$filename} 出错：{$e->getMessage()}：({$line}){$file}，args:".CJSON::encode(func_get_args()));
            Yii::app()->controller->message(200, '导出文件出错');
        }
    }

版权属于：Joyber

本文链接：https://blog.qqvbc.com/default/1199.html

转载时须注明出处及本声明