/**
* 导出
* @param
* @param
* @return
*/
public function exportexcel()
{
set_time_limit(0);
ini_set('memory_limit', '1024M');
vendor("PHPExcel.PHPExcel.PHPExcel");
vendor("PHPExcel.PHPExcel.Writer.IWriter");
vendor("PHPExcel.PHPExcel.Writer.Abstract");
vendor("PHPExcel.PHPExcel.Writer.Excel5");
vendor("PHPExcel.PHPExcel.Writer.Excel2007");
vendor("PHPExcel.PHPExcel.IOFactory");
//$data = Db::table('enroll_activity_hotel')->where('is_del', '0')->field('name,address,phone,departure_time,two_room,three_room')->select();
$name = '酒店列表';
$excel = new \PHPExcel(); //引用phpexcel
iconv('UTF-8', 'gb2312', $name); //针对中文名转码
$header = ['酒店名称', '酒店地址', '酒店电话', '发车时间', '两人间数', '三人间数']; //表头,名称可自定义
$excel->setActiveSheetIndex(0);
$excel->getActiveSheet()->setTitle($name); //设置表名
$excel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(18);
$excel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$excel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
$excel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
$excel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
$excel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
$excel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
$letter = 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');
//生成表头
for ($i = 0; $i < count($header); $i++) {
//设置表头值
$excel->getActiveSheet()->setCellValue("$letter[$i]1", $header[$i]);
//设置表头字体样式
$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setName('宋体');
//设置表头字体大小
$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setSize(14);
//设置表头字体是否加粗
$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->setBold(true);
//设置表头文字水平居中
$excel->getActiveSheet()->getStyle("$letter[$i]1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//设置文字上下居中
$excel->getActiveSheet()->getStyle($letter[$i])->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
//设置单元格背景色
$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFill()->getStartColor()->setARGB('FFFFFFFF');
$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFill()->getStartColor()->setARGB('FF6DBA43');
//设置字体颜色
$excel->getActiveSheet()->getStyle("$letter[$i]1")->getFont()->getColor()->setARGB('FFFFFFFF');
}
//写入数据
$count = Db::table('enroll_activity_hotel')->where('is_del', '0')->count('id');
$nums = ceil($count / 2000);
for ($i = 0; $i < $nums; ++$i) {
$data = Db::table('enroll_activity_hotel')->where('is_del', '0')->field('name,address,phone,departure_time,two_room,three_room')->limit(($i * 2000) . ',' . (2000))->select();
if (!empty($data)) {
$param = $i * 2000;
//写入数据
foreach ($data as $k => $v) {
$n = $param + $k + 2;
//列字体居中
$excel->getActiveSheet()->getStyle('A' . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle('B' . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle('C' . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle('D' . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle('E' . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle('F' . $n)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$excel->getActiveSheet()->getStyle('D')->getNumberFormat()->setFormatCode(\PHPExcel_Style_NumberFormat::FORMAT_TEXT);
//从第二行开始写入数据（第一行为表头）
$excel->getActiveSheet()->setCellValue('A' . $n, $v['name']);
$excel->getActiveSheet()->setCellValue('B' . $n, $v['address']);
$excel->getActiveSheet()->setCellValue('C' . $n, $v['phone']);
$excel->getActiveSheet()->setCellValue('D' . $n, $v["departure_time"]);
$excel->getActiveSheet()->setCellValue('E' . $n, $v['two_room']);
$excel->getActiveSheet()->setCellValue('F' . $n, $v['three_room']);
}
} else {
break;
}
}
unset($data);
//清理缓冲区，避免中文乱码
ob_end_clean();//清除缓冲区,避免乱码
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
header('Cache-Control: max-age=0');
//导出数据
$res_excel = \PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$res_excel->save('php://output');
}
/**
* daoru
* @param
* @param
* @return
*/
public function implodexcel()
{
set_time_limit(0);
if (!empty($_FILES)) {
//获取表单上传文件
if (!empty($_FILES['import']['error'])) {
return array('status' => 0, 'msg' => '文件上传失败');
}
$tmp_file = $_FILES ['import'] ['tmp_name'];
$file_types = explode(".", $_FILES ['import'] ['name']);
$file_type = $file_types [count($file_types) - 1];
/*判别是不是.xls文件，判别是不是excel文件*/
if (strtolower($file_type) != "xlsx" && strtolower($file_type) != "xls") {
$this->error('不是Excel文件，重新上传');
}
/*设置上传路径*/
/*百度有些文章写的上传路径经过编译之后斜杠不对。不对的时候用大写的DS代替，然后用连接符链接就可以拼凑路径了。*/
$savePath = ROOT_PATH . 'public' . DS . 'upload' . DS;/*以时间来命名上传的文件*/
$str = date('Ymdhis');
$file_name = $str . "." . $file_type;
/*是否上传成功*/
if (!copy($tmp_file, $savePath . $file_name)) {
$this->error('上传失败');
}
/*
*注意：这里调用执行了第三步类里面的read函数，把Excel转化为数组并返回给$res,再进行数据库写入
*/
$res = $this->read($savePath . $file_name, "UTF-8", $file_type);//传参,判断office2007还是office2003
$uniques = array_filter(array_column($res, 0));
$filpuni = array_flip(array_flip($uniques));
if (count($uniques) != count($filpuni)) {
return array('status' => 0, 'msg' => "酒店名称有重复项，请处理后再导入");
}
$datas = Db::table('enroll_activity_hotel')->where('is_del', '0')->field('id,name,address,phone,departure_time,two_room,three_room')->select();
$anames = array_column($datas, 'name');
$couludate = array_column($datas, null, 'name');//print_r($anames);die;
Db::startTrans();
try {
/*对生成的数组进行数据库的写入*/
foreach ($res as $k => $v) {
//如果导入数据为空，跳过
if (empty(array_filter($v))) {
continue;
}
if ($k > 1) {
$mblength = mb_strlen($v[0], 'UTF8');
if ($mblength > 50 || $mblength < 1) {
Db::rollback();
return array('status' => 0, 'msg' => '酒店名' . $v[0] . '：字符长度不符请控制在2-50字符以内');
}
$pholength = mb_strlen($v[2], 'UTF8');
if ($pholength > 20 || $pholength < 1) {
Db::rollback();
return array('status' => 0, 'msg' => '酒店' . $v[0] . '：电话长度不符');
}
if(!is_numeric($v[2])) {
Db::rollback();
return array('status' => 0, 'msg' => '酒店' . $v[0] . '：电话必须为数字');
}
$adreslength = mb_strlen($v[1], 'UTF8');
if ($adreslength > 100 || $adreslength < 1) {
Db::rollback();
return array('status' => 0, 'msg' => '酒店' . $v[0] . '：地址长度不符');
}
$timelength = mb_strlen($v[3], 'UTF8');
if ($timelength > 20 || $timelength < 1) {
Db::rollback();
return array('status' => 0, 'msg' => '酒店' . $v[0] . '：发车时间长度不符');
}
$toromlength = mb_strlen($v[4], 'UTF8');
$treromlength = mb_strlen($v[5], 'UTF8');
if ($toromlength < 1 || $treromlength < 1) {
Db::rollback();
return array('status' => 0, 'msg' => '酒店' . $v[0] . '：房间数必须填写,没有请填写数字为0');
}
if(!is_numeric($v[4])) {
Db::rollback();
return array('status' => 0, 'msg' => '酒店' . $v[0] . '：房间数必须为数字');
}
if(!is_numeric($v[5])) {
Db::rollback();
return array('status' => 0, 'msg' => '酒店' . $v[0] . '：房间数必须为数字');
}
$isexit = array_search($v[0], $anames);
if (false === $isexit) {
$data[$k]['name'] = $v[0];
$data[$k]['address'] = $v[1];
$data[$k]['phone'] = $v[2];
$data[$k]['departure_time'] = $v[3];
$data[$k]['two_room'] = $v[4];
$data[$k]['three_room'] = $v[5];
$data[$k]['is_open'] = 1;
$data[$k]['add_admin_id'] = session('user.id');
$data[$k]['add_time'] = time();
$data[$k]['update_admin_id'] = session('user.id');
$data[$k]['update_time'] = time();
} else {
//如果数据表中有本账户，判断他最新导入的表格是否修改过信息
if ($couludate[$v[0]]['address'] != $v[1] || $couludate[$v[0]]['phone'] != $v[2] || $couludate[$v[0]]['departure_time'] != $v[3] || $couludate[$v[0]]['two_room'] != $v[4] || $couludate[$v[0]]['three_room'] != $v[5]) {
$dataup[$k]['id'] = $couludate[$v[0]]['id'];
$dataup[$k]['address'] = $v[1];
$dataup[$k]['phone'] = $v[2];
$dataup[$k]['departure_time'] = $v[3];
$dataup[$k]['two_room'] = $v[4];
$dataup[$k]['three_room'] = $v[5];
$dataup[$k]['update_admin_id'] = session('user.id');
$dataup[$k]['update_time'] = time();
}
}
}
}
if (isset($dataup) || isset($data)) {
//如果有更新的数据，批量更新
if (isset($dataup)) {
foreach ($dataup as $key => $value) {
$updas = Db::table('enroll_activity_hotel')->where('id', $value['id'])->update($value);
if (!$updas) {
Db::rollback();
return array('status' => 0, 'msg' => $e->getMessage());
}
}
}
if (isset($data)) {
//插入的操作最好放在循环外面
$result = Db::table('enroll_activity_hotel')->insertAll($data);
if (!$result) {
Db::rollback();
return array('status' => 0, 'msg' => $e->getMessage());
}
}
Db::commit();
return array('status' => 1, 'msg' => "导入成功");
} else {
Db::rollback();
return array('status' => 0, 'msg' => "重复导入");
}
} catch (\Exception $e) {
Db::rollback();
return array('status' => 0, 'msg' => $e->getMessage());
}
}
}
/**
* 导入方法附属方法
* @param
* @param
* @return
*/
public function read($filename, $encode, $file_type)
{
vendor("PHPExcel.PHPExcel.PHPExcel");
vendor("PHPExcel.PHPExcel.Writer.IWriter");
vendor("PHPExcel.PHPExcel.Writer.Abstract");
vendor("PHPExcel.PHPExcel.Writer.Excel5");
vendor("PHPExcel.PHPExcel.Writer.Excel2007");
vendor("PHPExcel.PHPExcel.IOFactory");
if (strtolower($file_type) == 'xls')//判断excel表类型为2003还是2007
{
Vendor("Excel.PHPExcel.Reader.Excel5");
$objReader = \PHPExcel_IOFactory::createReader('Excel5');
} elseif (strtolower($file_type) == 'xlsx') {
Vendor("Excel.PHPExcel.Reader.Excel2007");
$objReader = \PHPExcel_IOFactory::createReader('Excel2007');
}
$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;
}
