<?php

namespace Admin\Controller;

use Think\Controller;

class InoutController extends Controller {

public function exportExcel($expTitle,$expCellName,$expTableData){

$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称

$fileName = $expTitle.date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定

$cellNum = count($expCellName);

$dataNum = count($expTableData);

vendor("PHPExcel.PHPExcel");

$objPHPExcel = new \PHPExcel();

$objPHPExcel->createSheet();

$objPHPExcel->setActiveSheetIndex();

$objActSheet = $objPHPExcel->getActiveSheet();

$objActSheet->getColumnDimension('D')->setWidth(21); //设置列宽

$objActSheet->getStyle('A1')->getFont()->setBold(true);//设置是否加粗

$objActSheet->getHeaderFooter()->setOddHeader( '&L&G&C&HPlease treat this document as confidential!');

$objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup:: ORIENTATION_LANDSCAPE);

$objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup:: PAPERSIZE_A4); //A4纸大小

//设置填充的样式和背景色

$objPHPExcel->getActiveSheet()->getStyle( 'A10:E10')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);

$objPHPExcel->getActiveSheet()->getStyle( 'A10:E10')->getFill()->getStartColor()->setARGB('#FFFF00');

$cellName = 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');

$objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格

$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.' Export time:'.date('Y-m-d H:i:s'));

for($i=0;$i<$cellNum;$i++){

$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]);

}

// Miscellaneous glyphs, UTF-8

for($i=0;$i<$dataNum;$i++){

for($j=0;$j<$cellNum;$j++){

$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);

}

}

header('pragma:public');

header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');

header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印

$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save('php://output');

exit;

}

function exportOrderExcel($title,$cellName,$data)

{

//引入核心文件

vendor("PHPExcel.PHPExcel");

$objPHPExcel = new \PHPExcel();

//定义配置

$topNumber = 2;//表头有几行占用

$xlsTitle = iconv('utf-8', 'gb2312', $title);//文件名称

$fileName = $title.date('_YmdHis');//文件名称

$cellKey = 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'

);

//写在处理的前面（了解表格基本知识，已测试）

// $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(20);//所有单元格（行）默认高度

// $objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);//所有单元格（列）默认宽度

// $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(30);//设置行高度

// $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);//设置列宽度

// $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);//设置文字大小

// $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);//设置是否加粗

// $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);// 设置文字颜色

// $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);//设置文字居左（HORIZONTAL_LEFT，默认值）中（HORIZONTAL_CENTER）右（HORIZONTAL_RIGHT）

// $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);//设置填充颜色

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('#FFFF00');//设置填充颜色

/* $objPHPExcel->getActiveSheet()->duplicatestyle( $objPHPExcel->getActiveSheet()->getstyle('A1'), 'A1:E13' );

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);//设置填充颜色

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('#FFFF00');//设置填充颜色*/

/*$objPHPExcel->getActiveSheet()->getstyle('D13')->getBorders()->getLeft()->getColor()->setARGB('FF993300');

$objPHPExcel->getActiveSheet()->getstyle('D13')->getBorders()->getTop()->getColor()->setARGB('FF993300');

$objPHPExcel->getActiveSheet()->getstyle('D13')->getBorders()->getBottom()->getColor()->setARGB('FF993300');*/

$styleThinBlackBorderOutline = array(

'borders' => array (

'outline' => array (

'style' => \PHPExcel_Style_Border::BORDER_THIN, //设置border样式 'color' => array ('argb' => 'FF000000'), //设置border颜色

),

),);

/* $objPHPExcel->getActiveSheet()->getStyle( 'B1')->applyFromArray($styleThinBlackBorderOutline);

$objPHPExcel->getActiveSheet()->getStyle( 'B2')->applyFromArray($styleThinBlackBorderOutline);

$objPHPExcel->getActiveSheet()->getStyle( 'C1')->applyFromArray($styleThinBlackBorderOutline);

$objPHPExcel->getActiveSheet()->getStyle( 'C2')->applyFromArray($styleThinBlackBorderOutline);

$objPHPExcel->getActiveSheet()->getStyle( 'C3')->applyFromArray($styleThinBlackBorderOutline);*/

//处理表头标题

$objPHPExcel->getActiveSheet()->mergeCells('A1:'.$cellKey[count($cellName)-1].'1');//合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错）

$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1','订单信息');

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);

$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

//处理表头

foreach ($cellName as $k=>$v)

{

$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellKey[$k].$topNumber, $v[1]);//设置表头数据

$objPHPExcel->getActiveSheet()->freezePane($cellKey[$k].($topNumber+1));//冻结窗口

$objPHPExcel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getFont()->setBold(true);//设置是否加粗

$objPHPExcel->getActiveSheet()->getStyle($cellKey[$k].$topNumber)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);//垂直居中

if($v[3] > 0)//大于0表示需要设置宽度

{

$objPHPExcel->getActiveSheet()->getColumnDimension($cellKey[$k])->setWidth($v[3]);//设置列宽度

}

}

$num = count($cellName) -1 + 65;

$E = chr($num);

//处理数据

foreach ($data as $k=>$v)

{

$id = $k +1;

if($id%9 ==0)

{

$A = 'A' . ($id + 2) . ':' . $E . ($id+2);

$objPHPExcel->getActiveSheet()->getStyle($A)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);//设置填充颜色

$objPHPExcel->getActiveSheet()->getStyle($A)->getFill()->getStartColor()->setARGB('#FFFF00');//设置填充颜色

$A = 'A' . ($id + 1) . ':' . $E . ($id+1);

$objPHPExcel->getActiveSheet()->getStyle($A)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);//设置填充颜色

$objPHPExcel->getActiveSheet()->getStyle($A)->getFill()->getStartColor()->setARGB('#FFFF00');//设置填充颜色

}

foreach ($cellName as $k1=>$v1)

{

$objPHPExcel->getActiveSheet()->setCellValue($cellKey[$k1].($k+1+$topNumber), $v[$v1[0]]);

if($v['end'] > 0)

{

if($v1[2] == 1)//这里表示合并单元格

{

$objPHPExcel->getActiveSheet()->mergeCells($cellKey[$k1].$v['start'].':'.$cellKey[$k1].$v['end']);

$objPHPExcel->getActiveSheet()->getStyle($cellKey[$k1].$v['start'])->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

}

}

if($v1[4] != "" && in_array($v1[4], array("LEFT","CENTER","RIGHT")))

{

$v1[4] = eval('return PHPExcel_Style_Alignment::HORIZONTAL_'.$v1[4].';');

//这里也可以直接传常量定义的值，即left,center,right；小写的strtolower

$objPHPExcel->getActiveSheet()->getStyle($cellKey[$k1].($k+1+$topNumber))->getAlignment()->setHorizontal($v1[4]);

}

}

}

//导出execl

header('pragma:public');

header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');

header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印

$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

$objWriter->save('php://output');

exit;

}

/**

*

* 导出Excel

*/

public function expUser(){//导出Excel

$xlsName = "Contacts";

$xlsCell = array(

array('id','账号序列'),

array('name','姓名'),

array('deapr','岗位'),

array('status','状态'),

array('city_level','城市级别')

);

$xlsModel = M('project');

$xlsData = $xlsModel->field('id,depar,name,status,city_level')->select();

foreach ($xlsData as $k => $v)

{

$xlsData[$k]['tname']=$v['id'];

array_splice($xlsData[$k]['id']);

}

//$this->exportExcel($xlsName,$xlsCell,$xlsData);

$this->exportOrderExcel($xlsName,$xlsCell,$xlsData);

}

/**

*

* 显示导入页面 ...

*/

/**实现导入excel

**/

function impUser(){

if (!empty($_FILES)) {

$upload = new \Think\Upload();// 实例化上传类

$filepath='./Public/Excle/';

$upload->exts = array('xlsx','xls');// 设置附件上传类型

$upload->rootPath = $filepath; // 设置附件上传根目录

$upload->saveName = 'time';

$upload->autoSub = false;

if (!$info=$upload->upload()) {

$this->error($upload->getError());

}

foreach ($info as $key => $value) {

unset($info);

$info[0]=$value;

$info[0]['savepath']=$filepath;

}

vendor("PHPExcel.PHPExcel");

$file_name=$info[0]['savepath'].$info[0]['savename'];

$objReader = \PHPExcel_IOFactory::createReader('Excel5');

$objPHPExcel = $objReader->load($file_name,$encode='utf-8');

$sheet = $objPHPExcel->getSheet(0);

$highestRow = $sheet->getHighestRow(); // 取得总行数

$highestColumn = $sheet->getHighestColumn(); // 取得总列数

$j=0;

for($i=3;$i<=$highestRow;$i++)

{

$data['name']= $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();

$tname= $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();

$data['tid']=Gettid($tname);

$data['danwei']= $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();

$data['phone']= $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();

// if(M('Contacts')->where("name='".$data['name']."' and phone=$data['phone']")->find()){

if(M('Contacts')->where("phone='".$data['phone']."'")->find()){

//如果存在相同联系人。判断条件：电话 两项一致，上面注释的代码是用姓名/电话判断

}else{

M('Contacts')->add($data);

$j++;

}

}

unlink($file_name);

User_log('批量导入联系人，数量：'.$j);

$this->success('导入成功！本次导入联系人数量：'.$j);

}else

{

$this->error("请选择上传的文件");

}

}

}

?>