phpexcel导入导出,php导出excel表格

发布时间：2022-11-26

本文目录一览：

  1. thinkphp5.0 如何导入phpexcel插件，能详细些点吗
  2. PHP如何导入导出Excel
  3. thinkphp怎么用phpexcel导出数据到excel
  4. thinkphp中phpexcel怎么实现导入导出
  5. phpexcel导入导出excel
  6. 如何利用ThinkPHP框架实现Excel数据导入和导出

thinkphp5.0 如何导入phpexcel插件，能详细些点吗

下载phpexcel，将整个文件夹放到vendor文件夹里面，如下图： 在控制器中的使用方法：

vendor("PHPExcel.PHPExcel");
$objPHPExcel = new \PHPExcel();
$file_name = date('YmdHis').'.xls';
header("Content-Disposition:attachment; filename=".$file_name);
$objActSheet = $objPHPExcel->getActiveSheet()->setTitle('成绩单');
$objActSheet->SetCellValue('A1', 'ID');
$objActSheet->SetCellValue('B1', '名称');
$objActSheet->SetCellValue('C1', '分数');
$list = ""; //数据列表
if($list){
    foreach ($list as $k => $v){
        $i = $k + 2;
        $objActSheet->SetCellValue('A'.$i, ''. $v['id']);
        $objActSheet->SetCellValue('B'.$i, ''. $v['name']);
        $objActSheet->SetCellValue('C'.$i, ''. $v['score']);
    }
    $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    $objWriter->save('php://output'); //输出到浏览器
    exit;
}

PHP如何导入导出Excel

导入导出都成，可以导出office2007格式，同时兼容2003。 下载下来的包中有文档和例子，大家可以自行研究。 抄段例子出来：

<?php
/**
 * PHPExcel
 *
 * Copyright (C) 2006 - 2007 PHPExcel
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
 *
 * @category PHPExcel
 * @package PHPExcel
 * @copyright Copyright (c) 2006 - 2007 PHPExcel
 * @license
 * @version 1.5.0, 2007-10-23
 */
/** Error reporting */
error_reporting(E_ALL);
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . '../Classes/');
/** PHPExcel */
include 'PHPExcel.php';
/** PHPExcel_Writer_Excel2007 */
include 'PHPExcel/Writer/Excel2007.php';
// Create new PHPExcel object
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = new PHPExcel();
// Set properties
echo date('H:i:s') . " Set properties\n";
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
$objPHPExcel->getProperties()->setCategory("Test result file");
// Add some data
echo date('H:i:s') . " Add some data\n";
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'world!');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('D2', 'world!');
// Rename sheet
echo date('H:i:s') . " Rename sheet\n";
$objPHPExcel->getActiveSheet()->setTitle('Simple');
// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);
// Save Excel 2007 file
echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->save(str_replace('.php', '.xlsx', __FILE__));
// Echo done
echo date('H:i:s') . " Done writing file.\r\n";

thinkphp怎么用phpexcel导出数据到excel

（一）导入Excel

  1. 在前台HTML页面进行上传文件：如：
<form method="post" action="php文件" enctype="multipart/form-data">
    <h3>导入Excel表：</h3>
    <input type="file" name="file_stu" />
    <input type="submit" value="导入" />
</form>
  1. 在对应的PHP文件进行文件的处理：
if (!empty($_FILES['file_stu']['name'])) {
    $tmp_file = $_FILES['file_stu']['tmp_name'];
    $file_types = explode(".", $_FILES['file_stu']['name']);
    $file_type = $file_types[count($file_types) - 1];
    if (strtolower($file_type) != "xls") {
        $this->error('不是Excel文件，重新上传');
    }
    $savePath = SITE_PATH . '/public/upfile/Excel/';
    $str = date('Ymdhis');
    $file_name = $str . "." . $file_type;
    if (!copy($tmp_file, $savePath . $file_name)) {
        $this->error('上传失败');
    }
    $res = Service('ExcelToArray')->read($savePath . $file_name);
    foreach ($res as $k => $v) {
        if ($k != 0) {
            $data['uid'] = $v[0];
            $data['password'] = sha1('111111');
            $data['email'] = $v[1];
            $data['uname'] = $v[3];
            $data['institute'] = $v[4];
            $result = M('user')->add($data);
            if (!$result) {
                $this->error('导入数据库失败');
            }
        }
    }
}
  1. ExcelToArray类，用来引用phpExcel并处理Excel数据的：
class ExcelToArrary extends Service {
    public function __construct() {
        include_once('./Excel/PHPExcel.php');
    }
    public function read($filename, $encode = 'utf-8') {
        $objReader = PHPExcel_IOFactory::createReader('Excel5');
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($filename);
        $objWorksheet = $objPHPExcel->getActiveSheet();
        $highestRow = $objWorksheet->getHighestRow();
        $highestColumn = $objWorksheet->getHighestColumn();
        $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
        $excelData = array();
        for ($row = 1; $row <= $highestRow; $row++) {
            for ($col = 0; $col < $highestColumnIndex; $col++) {
                $excelData[$row][] = (string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();
            }
        }
        return $excelData;
    }
}

（二）Excel的导出（相对于导入简单多了）

  1. 先查出数据库里面要生成Excel的数据，如：
$data = M('User')->findAll(); //查出数据
$name = 'Excelfile'; //生成的Excel文件文件名
$res = service('ExcelToArrary')->push($data, $name);
  1. ExcelToArrary类，用来引用phpExcel并处理数据的：
class ExcelToArrary extends Service {
    public function __construct() {
        include_once('./Excel/PHPExcel.php');
    }
    public function push($data, $name = 'Excel') {
        error_reporting(E_ALL);
        date_default_timezone_set('Europe/London');
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()
            ->setCreator("转弯的阳光")
            ->setLastModifiedBy("转弯的阳光")
            ->setTitle("数据EXCEL导出")
            ->setSubject("数据EXCEL导出")
            ->setDescription("备份数据")
            ->setKeywords("excel")
            ->setCategory("result file");
        foreach ($data as $k => $v) {
            $num = $k + 1;
            $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A' . $num, $v['uid'])
                ->setCellValue('B' . $num, $v['email'])
                ->setCellValue('C' . $num, $v['password']);
        }
        $objPHPExcel->getActiveSheet()->setTitle('User');
        $objPHPExcel->setActiveSheetIndex(0);
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }
}

thinkphp中phpexcel怎么实现导入导出

首先，将PHPExcel文件放入到vendor目录下面：

  • ThinkPHP3.1的路径是：./ThinkPHP/Extend/Vendor/
  • ThinkPHP3.2的路径是：./ThinkPHP/Library/Vendor/ 数据导出功能的实现： 编写导出为Excel的函数exportExcel：
public function exportExcel(...) {
    // ...
}

phpexcel导入导出excel

下载phpexcel：

require '/api/PHPExcel.php'; //加载phpexcel
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()
    ->setCreator("Maarten Balliauw")
    ->setLastModifiedBy("Maarten Balliauw")
    ->setTitle("Office 2007 XLSX Test Document")
    ->setSubject("Office 2007 XLSX Test Document")
    ->setDescription("Document for Office 2007 XLSX, generated using PHP classes.")
    ->setKeywords("office 2007 openxml php")
    ->setCategory("Test result file");
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getDefaultStyle()->getFont()->setName('微软雅黑');
$objPHPExcel->getDefaultStyle()->getFont()->setSize(14);
$objPHPExcel->getActiveSheet()->getStyle("A1:L1")->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle("A1:L1")->getFont()->setBold(TRUE);
$objPHPExcel->getActiveSheet()->getStyle('A1:L1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:L1')->getFill()->getStartColor()->setARGB('FF6FA0EC');
$ths = array('A1','B1');
$objPHPExcel->getActiveSheet()->setCellValue('A1', '用户名');
$objPHPExcel->getActiveSheet()->setCellValue('B1', '密码');
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
$size = count($passwords);
if ($passwords) {
    for ($i = 0; $i < $size; $i++) {
        $objPHPExcel->getActiveSheet()->setCellValue("A" . ($i + 2), $lists[$i]['username']);
        $objPHPExcel->getActiveSheet()->setCellValue("B" . ($i + 2), $lists[$i]['mima']);
    }
}
$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setTitle('Excel表');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$outputFileName = "fileaname"; //文件名字
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . iconv('UTF-8','GB18030',$outputFileName) . '.xls"');
header('Cache-Control: max-age=0');
header('Cache-Control: max-age=1');
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT');
header('Cache-Control: cache, must-revalidate');
header('Pragma: public');
$objWriter->save('php://output');

如何利用ThinkPHP框架实现Excel数据导入和导出

实现步骤：

  1. 在下载最新PHPExcel放到Vendor下，注意位置：ThinkPHP\Extend\Vendor\PHPExcel\PHPExcel.php。
  2. 导出Excel代码实现：
function index() {
    $this->display();
}
public function exportExcel($expTitle, $expCellName, $expTableData) {
    $xlsTitle = iconv('utf-8', 'gb2312', $expTitle); //文件名称
    $fileName = $_SESSION['account'] . date('_YmdHis'); //or $xlsTitle 文件名称可根据自己情况设定
    $cellNum = count($expCellName);
    $dataNum = count($expTableData);
    vendor("PHPExcel.PHPExcel");
    $objPHPExcel = new PHPExcel();
    $cellName = array('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');
    $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1'); //合并单元格
    for ($i = 0; $i < $cellNum; $i++) {
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $expCellName[$i][1]);
    }
    for ($i = 0; $i < $dataNum; $i++) {
        for ($j = 0; $j < $cellNum; $j++) {
            $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 3), $expTableData[$i][$expCellName[$j][0]]);
        }
    }
    header('pragma:public');
    header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
    header("Content-Disposition:attachment;filename=$fileName.xls"); //attachment新窗口打印inline本窗口打印
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save('php://output');
    exit;
}
function expUser() { //导出Excel
    $xlsName = "User";
    $xlsCell = array(
        array('id', '账号序列'),
        array('truename', '名字'),
        array('sex', '性别'),
        array('res_id', '院系'),
        array('sp_id', '专业'),
        array('class', '班级'),
        array('year', '毕业时间'),
        array('city', '所在地'),
        array('company', '单位'),
        array('zhicheng', '职称'),
        array('zhiwu', '职务'),
        array('jibie', '级别'),
        array('tel', '电话'),
        array('qq', 'qq'),
        array('email', '邮箱'),
        array('honor', '荣誉'),
        array('remark', '备注')
    );
    $xlsModel = M('Member');
    $xlsData = $xlsModel->Field('id,truename,sex,res_id,sp_id,class,year,city,company,zhicheng,zhiwu,jibie,tel,qq,email,honor,remark')->select();
    foreach ($xlsData as $k => $v) {
        $xlsData[$k]['sex'] = $v['sex'] == 1 ? '男' : '女';
    }
    $this->exportExcel($xlsName, $xlsCell, $xlsData);
}
  1. 导入Excel数据代码：
function impUser() {
    if (!empty($_FILES)) {
        import("@.ORG.UploadFile");
        $config = array(
            'allowExts' => array('xlsx','xls'),
            'savePath' => './Public/upload/',
            'saveRule' => 'time',
        );
        $upload = new UploadFile($config);
        if (!$upload->upload()) {
            $this->error($upload->getErrorMsg());
        } else {
            $info = $upload->getUploadFileInfo();
        }
        vendor("PHPExcel.PHPExcel");
        $file_name = $info[0]['savepath'].$info[0]['savename'];
        $objReader = PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel = $objReader->load($file_name, $encode='utf-8');
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $highestColumn = $sheet->getHighestColumn(); // 取得总列数
        for ($i = 3; $i <= $highestRow; $i++) {
            $data['account'] = $data['truename'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
            $sex = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
            $data['class'] = $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();
            $data['year'] = $objPHPExcel->getActiveSheet()->getCell("F".$i)->getValue();
            $data['city'] = $objPHPExcel->getActiveSheet()->getCell("G".$i)->getValue();
            $data['company'] = $objPHPExcel->getActiveSheet()->getCell("H".$i)->getValue();
            $data['zhicheng'] = $objPHPExcel->getActiveSheet()->getCell("I".$i)->getValue();
            $data['zhiwu'] = $objPHPExcel->getActiveSheet()->getCell("J".$i)->getValue();
            $data['jibie'] = $objPHPExcel->getActiveSheet()->getCell("K".$i)->getValue();
            $data['honor'] = $objPHPExcel->getActiveSheet()->getCell("L".$i)->getValue();
            $data['tel'] = $objPHPExcel->getActiveSheet()->getCell("M".$i)->getValue();
            $data['qq'] = $objPHPExcel->getActiveSheet()->getCell("N".$i)->getValue();
            $data['email'] = $objPHPExcel->getActiveSheet()->getCell("O".$i)->getValue();
            $data['remark'] = $objPHPExcel->getActiveSheet()->getCell("P".$i)->getValue();
            $data['sex'] = $sex == '男' ? 1 : 0;
            $data['res_id'] = 1;
            $data['last_login_time'] = 0;
            $data['create_time'] = $data['last_login_ip'] = $_SERVER['REMOTE_ADDR'];
            $data['login_count'] = 0;
            $data['join'] = 0;
            $data['avatar'] = '';
            $data['password'] = md5('123456');
            M('Member')->add($data);
        }
        $this->success('导入成功！');
    } else {
        $this->error("请选择上传的文件");
    }
}
  1. 模板代码：
<html>
<head></head>
<body>
    <p><a href="{:U('Index/expUser')}">导出数据并生成excel</a></p>
    <form action="{:U('Index/impUser')}" method="post" enctype="multipart/form-data">
        <input type="file" name="import"/>
        <input type="hidden" name="table" value="tablename"/>
        <input type="submit" value="导入"/>
    </form>
</body>
</html>
推荐文章