controller代码:
在上传excel时要用到用PHPExcel,PHPExcel是相当强大的 MS Office Excel 文档生成类库。
你上它的官网把程序包下载下来放在Apache24\htdocs 【新建一个文件夹(PHPExcel)】里面,然后就可以使用了。
在config配置文件下的mimes.php的文件下xlsx后面(113行)增加一条语句:
‘xlsx’ => array(’ application/octet-stream’,……)
<?php
class Upload extends CI_Controller
{
parent::__construct();
public function __construct()
{
parent::__construct();//继承或者调用父类构造函数
$this->load->helper(array('form', 'url'));//加载辅助函数
}
public function index()
{
$this->load->view('upload_form', array('error' => ' '));
}
public function do_upload()
{
//上传到文件夹的路径
$config['upload_path'] = './uploads/';
$config['allowed_types'] = 'gif|jpg|png|xlsx';
$config['max_size'] = 100;
$config['max_width'] = 1024;
$config['max_height'] = 768;
//加载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_form', $error);
} else {
//选择文件上传
$data = array('upload_data' => $this->upload->data());
// $this->load->view('upload_success', $data);
// 上传后存储的路径 upload_data/full_path
$excleFullPath = $data["upload_data"]["full_path"];
//获取上传文件的后缀名
$extname = strtolower(substr($excleFullPath, strrpos($excleFullPath, '.') + 1));
//判断是否是excel文件,如果是excel文件就写入数据库
if ($extname == 'xlsx') {
//此处往下为新加的excel上传代码:
//--------输出Excel内容换行
define('EOL', (PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
$this->load->view('upload_success', $data);
echo '$excleFullPath', $excleFullPath, '<br>';
require_once 'PHPExcel/Classes/PHPExcel/IOFactory.php';
$objReader = PHPExcel_IOFactory::createReader('Excel2007'); //创建一个2007的读取对象
$objPHPExcel = $objReader->load($excleFullPath);//读取一个xlsx文件
// 循环sheet
{
//只取第一个sheet工作簿里面的值
$worksheet = $objPHPExcel->getSheet(0);
echo 'Worksheet - ', $worksheet->getTitle(), EOL;
//循环行
foreach ($worksheet->getRowIterator() as $row) {
echo 'Row number - ', $row->getRowIndex(), EOL;
$cellIterator = $row->getCellIterator(); // 拿到行中的cell迭代器
$cellIterator->setIterateOnlyExistingCells(false); // 设置cell迭代器,遍历所有cell,哪怕cell没有值
// 循环列
$arr = array();
foreach ($cellIterator as $cell) {
if (!is_null($cell)) {//如果列不给空就得到它的坐标和计算的值
$data = $cell->getCalculatedValue();
$arr[] = $data;
echo 'Cell - ', $cell->getCoordinate(), '-', $cell->getCalculatedValue(), EOL;
}
}
$this->load->model('Insert');
$this->Insert->to_sql($arr);
// print_r($arr);
// echo '<br>';
}
}
} else {
$this->load->view('upload_success', $data);
return;
}
}
}
}
models代码:
<?php
class Insert extends CI_Model
{
public function __construct(){
parent::__construct();
$this->load->database();
}
public function to_sql($data){
$colomnArr =array("col1","col2","col3","col4","col5");
if(count($data)<count($colomnArr)){
return;
}
$db_array=array();
//遍历一行的每一列组成一个数组
foreach ($colomnArr as $index=>$item){
$db_array[$colomnArr[$index]] = $data[$index];
}
$this->db->insert("sql",$db_array);
echo $this->db->last_query();
return $this->db->affected_rows();
}
}
views代码:
Upload Form:
<html>
<head>
<title>Upload Form</title>
</head>
<body>
<?php echo $error; ?>
<form method="post" enctype="multipart/form-data" action="upload/do_upload" accept-charset="utf-8">
<input type="file" name="userfile" size="20"/>
<br/><br/>
<input type="submit" value="upload"/>
</form>
</body>
</html>
Upload Success:
<html>
<head>
<title>Upload Success</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>