php中excel的导出导入

最新推荐文章于 2024-06-14 09:22:46 发布

jingxiaox

最新推荐文章于 2024-06-14 09:22:46 发布

阅读量236

点赞数 1

文章标签： php

本文链接：https://blog.csdn.net/jingxiaox/article/details/52604778

版权

1,,导出

<?php
namespace Admin\Controller;
use Think\Controller;
/**
*
* 导出Excel文件数据到MySQL数据库
*/
class DaochuController extends CommonController {

public function export(){
//引入PHPExcel类
import('Org.Util.PHPExcel');
$objPHPExcel = new \PHPExcel();
$activeSheet = $objPHPExcel->getActiveSheet();
$objPHPExcel->getProperties()
->setCreator($creator)->setLastModifiedBy($modifier)
->setTitle($title)->setSubject($subject)
->setDescription($description)
->setKeywords($keywords)
->setCategory($category);
$data= M('Stock')->where(array('shop'=>$_SESSION['user']['nid']))->select();
$shop = M('shop')->where(array('id'=>$_SESSION['user']['nid']))->find();
$name= $shop['name'];
$yanse = $daxiao = array();
$color = M('yanse')->where(array('shop'=>$_SESSION['user']['nid']))->select();
$colors = array_coltokey($color,'id');
$size = M('daxiao')->where(array('shop'=>$_SESSION['user']['nid']))->select();
$sizes = array_coltokey($size,'id');
$cursheet = $objPHPExcel->setActiveSheetIndex(0);
$cursheet->setCellValue('A1', '序号')
->setCellValue('B1', '条码')
->setCellValue('C1', '款号')
->setCellValue('D1', '颜色')
->setCellValue('E1', '大小')
->setCellValue('F1', '销售单价')
->setCellValue('G1', '库存数量');
$num = 2;
foreach($data as $k => $v){
$cursheet->setCellValue('A'.$num, $num-1)
->setCellValue('B'.$num, ' '.$v['cno'])
->setCellValue('C'.$num, ' '.$v['kno'])
->setCellValue('D'.$num, ' '.$colors[$v['color']]['color'])
->setCellValue('E'.$num, ' '.$sizes[$v['size']]['size'])
->setCellValue('F'.$num, $v['price']*1)
->setCellValue('G'.$num, $v['num']);
$num++;
}
$objPHPExcel->setActiveSheetIndex(0);
$filename = iconv('utf-8','gbk',$name . '导出.xls');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit();
}

}

2导入

<?php
namespace Admin\Controller;
use Think\Controller;
/**
*
* 导入Excel文件数据到MySQL数据库
*/
class DaoruController extends CommonController {

/**
* 显示上传表单html页面
*/
public function daoru() {
if($_GET['do']=='download'){
$filepath = './Public/daoru/'.$_SESSION['user']['nid'].'.xls';
if(file_exists($filepath)){
$shop = M('shop')->where(array('id'=>$_SESSION['user']['nid']))->find();
$filename = iconv('utf-8','gbk',$shop['name'].'导入格式.xls');
header("Content-Type: application/vnd.ms-excel; charset=utf8");
header("Content-Disposition: attachment; filename=".$filename);
readfile($filepath);
exit();
}else{
$this->error('下载文件不存在！');
}
}
$shop = M('shop')->where(array('id'=>$_SESSION['user']['nid']))->find();
// __ROOT__是根目录，必须指定根目录，才可以下载
$this->display();
}

/**
* 上传Excel文件
*/
public function upload() {
//实例化上传类
$upload = new \Think\Upload();
//设置附件上传文件大小200Kib
$upload->maxSize = 2000000;
//设置附件上传类型
$upload->allowExts = array('xls', 'xlsx', 'csv');
//设置附件上传目录
$upload->rootPath = './Uploads/';
//设置附件上传子目录
$upload->savePath = '';
$upload->subName = array('date','Ym');
$data=array(
'upload_time' => time(),
'upload_user' => $_SESSION['user']['username'],
'upload_ip' => getIp(),
'shop'=>$_SESSION['user']['nid']
);


// 上传文件
$info = $upload->upload();
if (!$info) { //如果上传失败,提示错误信息
$this->error($upload->getError());
} else { //上传成功
//获取上传保存文件名
$fileName = $info['file']['savename'];
$data['filename'] = $fileName;
$data['filepath'] = './Uploads/'.$info['file']['savepath'].$fileName;
$result = M('daorulist')->add($data);
$this->importExcel('./Uploads/'.$info['file']['savepath'].$fileName);


}
}


/**
*
* 导入Excel文件
*/
public function importExcel($fileName) {
header("content-type:text/html;charset=utf-8");
//引入PHPExcel类
import('Org.Util.PHPExcel');
import('Org.Util.PHPExcel.Reader.Excel5','','.php');
//vendor('PHPExcel');
//vendor('PHPExcel.IOFactory');
//vendor('PHPExcel.Reader.Excel5');

//文件路径
$filePath = $fileName;
//实例化PHPExcel类
$PHPExcel = new \PHPExcel();
//默认用excel2007读取excel，若格式不对，则用之前的版本进行读取
import("Org.Util.PHPExcel.Reader.Excel2007",'','.php');
$PHPReader = new \PHPExcel_Reader_Excel2007();
if (!$PHPReader->canRead($filePath)) {
$PHPReader = new \PHPExcel_Reader_Excel5();
if (!$PHPReader->canRead($filePath)) {
echo 'no Excel';
return;
}
}

//读取Excel文件
$PHPExcel = $PHPReader->load($filePath);
//读取excel文件中的第一个工作表
$rows = $PHPExcel->getSheet(0)->toArray();//toArray函数把获取的excel数据转换成数组
unset($rows[0]);
foreach($rows as $v) {
$cno = $v[0];
$kno = $v[1];
$color = $v[2];
$size = $v[3];
$price = $v[4];
$num = $v[5];
if(empty($cno) || empty($kno) || empty($color) || empty($size) || empty($price) || empty($num)){
continue;
}
$m = M('stock');
$yanse = $daxiao = array();
$yanse = M('yanse')->where(array('color'=>$color,'shop'=>$_SESSION['user']['nid']))->find();
$daxiao = M('daxiao')->where(array('size'=>$size,'shop'=>$_SESSION['user']['nid']))->find();
$data = array();
$data = array(
'cno'=>$cno,
'kno'=>$kno,
'color'=>$yanse['id'],
'size'=>$daxiao['id'],
'price'=>$price,
'shop'=>$_SESSION['user']['nid'],
'num'=>$num,
'ctime'=>time(),
'add_time'=>time(),
'ip'=>getIp(),
'name'=>$_SESSION['user']['username']
);
$dat = array();
$dat = $m->where(array('cno'=>$data['cno'],'color'=>$data['color'],'size'=>$data['size'],'kno'=>$data['kno']))->find();
if($dat){
$data['num'] = $dat['num']+ $num;
$m->where(array('id'=>$dat['id']))->save($data);
$number = $dat['id'];
} else {
$number = $m->add($data);
}

M('srecord')->add($data);
}

if ($number > 0) {
$this->success( "导入成功！",U('Daoru/upload_list'));
} else {
$this->error("导入失败！！");
}
}

public function upload_list(){
$count=M('daorulist')->where(array('shop'=>$_SESSION['user']['nid']))->count();
$Page = $this->page($count);
$show = $Page->show();
$this->list=M('daorulist')->where(array('shop'=>$_SESSION['user']['nid']))->limit($Page->firstRow.','.$Page->listRows)->order ('id asc') ->select();
$this->assign('page',$show);
$this->display();

}

}

?>