原生代码导出
$DB_Server = "127.0.0.1";
$DB_Username = "";//数据库用户名
$DB_Password = "";//数据库密码
$DB_DBName = "";//数据库名
$DB_TBLName = "";//要下载的数据表
$savename = date("Y-m-j H:i:s");
// 数据库连接
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password) or die("Couldn't connect.");
//注意mysql 导入cxcel中的时候
mysql_query("Set Names 'utf8'");
//定义文件导出的格式
$file_type = "vnd.ms-excel";
//定义文件后缀名称
$file_ending = "xls";
header("Content-Type: application/$file_type;charset=utf8");
header("Content-Disposition: attachment; filename=".$savename.".$file_ending");
//header("Pragma: no-cache");
$now_date = date("Y-m-j H:i:s");
//定义要输出的数据表标题
$title = "数据表名:$DB_TBLName,|日期:$now_date";
$sql = "";
$ALT_Db = @mysql_select_db($DB_DBName, $Connect) or die("Couldn't select database");
// $result = @mysql_query($sql,$Connect) or die(mysql_error());
echo "ID\t";echo "称呼\t";echo "公司\t";echo "手机号\t";echo "状态\t";echo "会员类型\t";echo "注册时间\t\n";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)){
echo $row['id']."\t";echo $row['nicname']."\t";echo $row['company']."\t";echo $row['mobilephone']."\t";echo $ischecka."\t";echo $isdealera."\t";echo $date."\t\n";
}
phpexcel导入
$file = $_FILES['excelfile'];//file上传文件
if (!empty($file['name'])) {
$config = array(
'maxSize' => 2097152,// 设置附件上传大小 字节为单位B
'rootPath' => './Uploads/',// 设置附件上传根目录
'savePath' => 'excel/', //附件保存目录
'exts' => array('xlsx', 'xls'),// 设置附件上传类型
'autoSub' => false,// 是否使用子目录保存上传文件
'replace' => true, // 存在同名文件是否是覆盖
'subName' => array('date', 'Y-m-d'), //子目录创建方式，[0]-函数名，[1]-参数，多个参数使用数组
);
$upload = new \Think\Upload($config);
if (!$info = $upload->uploadOne($file)) {
$this->error($upload->getError());
}
require_once('Public/Admin/phpExcel/PHPExcel.php');
$file_name = './Uploads/'. $info['savepath'] . $info['savename'];//要写./相对路径
$file_path = pathinfo($file_name);
if($file_path['extension'] =='xlsx' )
{
$objReader = new \PHPExcel_Reader_Excel2007();
$objPHPExcel = $objReader->load($file_name, $encode = 'utf-8');
}
else
{
$objReader = new \PHPExcel_Reader_Excel5();
$objPHPExcel = $objReader->load($file_name, $encode = 'utf-8');
}
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumn = $sheet->getHighestColumn(); // 取得总列数
//i=2 从第二行开始
for ($i = 2; $i <= $highestRow; $i++) {
$data['orderid'] = $objPHPExcel->getActiveSheet()->getCell("A" . $i)->getValue();
$data['type'] = $objPHPExcel->getActiveSheet()->getCell("B" . $i)->getValue();
$data['cname'] = $objPHPExcel->getActiveSheet()->getCell("C" . $i)->getValue();
$data['name'] = $objPHPExcel->getActiveSheet()->getCell("D" . $i)->getValue();
$data['company'] = $objPHPExcel->getActiveSheet()->getCell("E" . $i)->getValue();
$data['duty'] = $objPHPExcel->getActiveSheet()->getCell("F" . $i)->getValue();
$data['headpic'] = $objPHPExcel->getActiveSheet()->getCell("G" . $i)->getValue();
$data['time'] = time();
if (!empty($data['cname']) && !empty($data['orderid'])) {
$member->add($data);
}
else{
$this->error("表中编号和ID为空，上传中断", U('Admin/Member/memberlist'), 1);
}
}
}
phpexcel导出
error_reporting(E_ALL);
date_default_timezone_set('Etc/GMT-8');
require_once('Public/phpExcel/PHPExcel.php');
// echo file_exists("./Public/phpexcel/Classes/PHPExcel.php");
$obpe = new \PHPExcel();
/* @func 设置文档基本属性 */
$obpe_pro = $obpe->getProperties();
$obpe_pro->setCreator('midoks')//设置创建者
// ->setLastModifiedBy('2013/2/16 15:00')//设置时间
->setTitle('data')//设置标题
->setSubject('beizhu')//设置备注
->setDescription('miaoshu')//设置描述
->setKeywords('keyword')//设置关键字 | 标记
->setCategory('catagory');//设置类别
/*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/
$i =0;
$obpe->setactivesheetindex($i);
//写入多行数据
$obpe->getActiveSheet()->getColumnDimension('B')->setWidth(25);
$obpe->getActiveSheet()->getColumnDimension('E')->setWidth(40);
$obpe->getactivesheet()->setcellvalue('A'."1", "姓名");
$obpe->getactivesheet()->setcellvalue('B'."1", "电话");
$obpe->getactivesheet()->setcellvalue('C'."1","公司" );
$obpe->getactivesheet()->setcellvalue('D'."1","职位" );
$obpe->getactivesheet()->setcellvalue('E'."1", "身份证");
foreach($data as $k=>$v){
$k = $k+2;
/* @func 设置列 */
$obpe->getactivesheet()->setcellvalue('A'.$k, $v['p_name']);
$obpe->getactivesheet()->setcellvalue('B'.$k, $v['p_phone']);
$obpe->getactivesheet()->setcellvalue('C'.$k, $v['p_company']);
$obpe->getactivesheet()->setcellvalue('D'.$k, $v['p_position']);
$obpe->getactivesheet()->setcellvalue('E'.$k, ' '.$v['p_idcard']);
}
$obpe->getActiveSheet()->setTitle('个人信息表');
if (strstr($type,"1")) {
$i++;
// // 创建一个新的工作空间(sheet)
$obpe->createSheet();
$obpe->setactivesheetindex($i);
//写入多行数据
$obpe->getActiveSheet()->getColumnDimension('A')->setWidth(25);
$obpe->getactivesheet()->setcellvalue('A'."1", "制卡时间");
$obpe->getactivesheet()->setcellvalue('B'."1","类型" );
$obpe->getactivesheet()->setcellvalue('C'."1", "卡模板");
foreach($data['cardmakinglog'] as $k=>$v){
$k = $k+2;
/* @func 设置列 */
$v['cm_date'] =date("Y.m.d H:i:s",$v['cm_date']);
$obpe->getactivesheet()->setcellvalue('A'.$k, $v['cm_date']);
if($v['cm_type']==1){
$v['cm_type'] ="批量制卡";
}
$obpe->getactivesheet()->setcellvalue('B'.$k, $v['cm_type']);
$v['cm_modal'] =M("model")->where("mod_id =".$v['cm_modal'])->getField("mod_name");
$obpe->getactivesheet()->setcellvalue('C'.$k, $v['cm_modal']);
}
$obpe->getActiveSheet()->setTitle('制卡日志表');
}
if (strstr($type,"2")) {
// // 创建一个新的工作空间(sheet)
$i++;
$obpe->createSheet();
$obpe->setactivesheetindex($i);
//写入多行数据
$obpe->getActiveSheet()->getColumnDimension('A')->setWidth(25);
$obpe->getactivesheet()->setcellvalue('A'."1", "写卡时间");
$obpe->getactivesheet()->setcellvalue('B'."1", "类型");
$obpe->getactivesheet()->setcellvalue('C'."1","卡类型" );
foreach($data['cardwritinglog'] as $k=>$v){
$k = $k+2;
/* @func 设置列 */
$v['cw_date'] =date("Y.m.d H:i:s",$v['cw_date']);
$obpe->getactivesheet()->setcellvalue('A'.$k, $v['cw_date']);
$obpe->getactivesheet()->setcellvalue('B'.$k, $v['cw_type']);
$obpe->getactivesheet()->setcellvalue('C'.$k, $v['cw_card']);
}
$obpe->getActiveSheet()->setTitle('写卡日志表');
}
//写入类容
$obwrite = \PHPExcel_IOFactory::createWriter($obpe, 'Excel5');
//ob_end_clean();
//保存文件
// $obwrite->save('mulit_sheet.xls');
//or 以下方式
/*******************************************
直接在浏览器输出
*******************************************/
header('Pragma: public');
header('Expires: 0');
header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
header('Content-Type:application/force-download');
header('Content-Type:application/vnd.ms-execl');
header('Content-Type:application/octet-stream');
header('Content-Type:application/download');
header("Content-Disposition:attachment;filename='".$name.".xls'");
header('Content-Transfer-Encoding:binary');
$obwrite->save('php://output');
-------------------------------------------------------------------------------------------------
网友评论