<?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("请选择上传的文件");
???????}
???}
}
?>
phpexecl
原文地址：https://www.cnblogs.com/simadongyang/p/9057080.html