php,使用CodeIgniter框架上传Excel表格，并存入到数据库中

最新推荐文章于 2021-05-21 16:03:55 发布

liusslove

最新推荐文章于 2021-05-21 16:03:55 发布

阅读量3.6k

点赞数

分类专栏： php 文章标签： codeigniter 数据库 php 框架 excel

本文链接：https://blog.csdn.net/liusslove/article/details/56480146

版权

php 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

思路：

1.先把Excel文件上传到服务器

2.获取服务器Excel文件内容

3.写入数据库

注意下载PHPExcel文件类，存放在文件目录下

修改文件：application->config->minmes->113行中“xlsx”修改为如图：

1、html界面，上传文件

<html>
<head>
    <title>Upload Form</title>
</head>
<body>

<?php echo $error;?>

<form action="/upload2/do_upload" encType="multipart/form-data" method="post" accept-charset="utf-8">

<input type="file" name="userfile" size="20" />

<br /><br />

<input type="submit" value="upload" />

</form>

</body>
</html>

2、数据库存储model

<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/2/21
 * Time: 16:20
 */
class excel_model extends CI_Model{
    public function __construct()
    {
        $this->load->database();
    }

//数据中的列数是固定的（根据Excel模板）
  public function insertExcel($data){

      if(count($data)<5){

          echo "数组长度不够<br>";
          return;
      }
      $data1=array(

          'A'=>$data[0],
          'B'=>$data[1],
          'C'=>$data[2],
          'D'=>$data[3],
          'E'=>$data[4],
      );
      $this->db->insert('excel1',$data1);
  }
}

3.controller界面（http://localhost/upload/do_upload）访问页面

需要使用PHPExcel文件类，可以从网上下载，直接放在工程目录下即可；

<?php

class Upload extends CI_Controller {

    public function __construct()
    {
        parent::__construct();
        //加载辅助函数
        $this->load->helper(array('form', 'url'));
    }

    public function index()
    {
        $this->load->view('upload/upload_form', array('error' => ' ' ));
    }

    public function do_upload()
    {
        //加载model
        $this->load->model('excel_model');

        //上传到文件夹的路径
        $config['upload_path']      = 'D:\Program Files\Apache24\htdocs\uploads';
        $config['allowed_types']    = 'xlsx';
        $config['max_size']     = 0;
        $config['max_width']        = 0;
        $config['max_height']       = 0;
       //加载upload库
        $this->load->library('upload', $config);

       //上传文件file的name='userfile'
        if ( ! $this->upload->do_upload('userfile'))
        {
            $error = array('error' => $this->upload->display_errors());
        //没有选择文件，打印错误
            $this->load->view('upload/upload_form', $error);
        }
        else
        {
       //选择文件上传
            $data = array('upload_data' => $this->upload->data());
       //跳转到上传成功界面
            $this->load->view('upload/upload_success', $data);

//--------输出Excel内容换行
            define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

            echo '------------------------';
            print_r($data);//打印出数据，可以便于查看存储路径
            echo '------------------------';
       // 上传后存储的路径
            $excleFullPath=$data["upload_data"]["full_path"];

            echo '$excleFullPath',$excleFullPath,'<br>';
       //引用PHPExcel文件类
            require_once 'PHPExcel/PHPExcel/IOFactory.php';
            $objReader = PHPExcel_IOFactory::createReader('Excel2007');
            $objPHPExcel = $objReader->load($excleFullPath);

            // 循环sheet
            foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
                echo 'Worksheet - ' , $worksheet->getTitle() , EOL;

                // 循环行
                foreach ($worksheet->getRowIterator() as $row) {
                    echo '    Row number - ' , $row->getRowIndex() , EOL;


                    $cellIterator = $row->getCellIterator();

                    $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set

                  // 循环列
                    $data=array();//获取一整行的数据，存放在数组中
                    foreach ($cellIterator as $cell) {
                        if (!is_null($cell)) {
                            //往数组中存放数据
                            $data[]=$cell->getCalculatedValue();

                            echo '        Cell - ' , $cell->getCoordinate() , ' - ' , $cell->getCalculatedValue() , EOL;

                        }
                    }

                    echo '***********';
                    print_r($data);
                    echo '<br>';
                    //往数据库中插入数据
                    $this->excel_model->insertExcel($data);
                }
            }


        }
    }
}
?>

4.文件上传成功后跳转界面<html>
<head>
    <title>Upload Form</title>
</head>
<body>

<h3>Your file was successfully uploaded!</h3>

<ul>
    <?php foreach ($upload_data as $item => $value):?>
        <li><?php echo $item;?>: <?php echo $value;?></li>
    <?php endforeach; ?>
</ul>

<p><?php echo anchor('upload', 'Upload Another File!'); ?></p>

</body>
</html>