可见http://zixun.prykweb.com/html/advicedaily/ purui0827
<?php
pc_base::load_sys_class('form', '', 0); //调用form类，不进行实例化操作
include(PC_PATH . 'libs/classes/PhpExcel/PHPExcel.php');
include(PC_PATH . 'libs/classes/PhpExcel/PHPExcel/IOFactory.php');
include(PC_PATH . 'libs/classes/PhpExcel/PHPExcel/Reader/Excel2007.php');
include(PC_PATH . 'libs/classes/PhpExcel/PHPExcel/Reader/Excel5.php');
class advice
{
protected $advice_data = array(
6 => array(0 => '总对话量'),
7 => array(0 => '有效对话'),
8 => array(0 => '预约量'),
9 => array(0 => '转社群量'),
10 => array(0 => '初诊到院'),
11 => array(0 => '手术/配镜'),
12 => array(0 => '明日到院'),
13 => array(0 => '回访'),
14 => array(0 => '反馈'),
15 => array(0 => '预约')
);
private $db;
protected $hospital = array(
2 => '成都普瑞眼科医院',
5 => '昆明普瑞眼科医院',
3 => '合肥普瑞眼科医院',
1 => '兰州普瑞眼科医院',
7 => '南昌普瑞眼科医院',
6 => '乌鲁木齐普瑞眼科医院',
4 => '郑州普瑞眼科医院',
10 => '武汉普瑞眼科医院',
9 => '重庆普瑞眼科医院',
13 => '北京华德眼科医院',
18 => '西安普瑞眼科医院',
11 => '哈尔滨普瑞眼科医院',
8 => '上海普瑞瑞眼科医院',
15 => '山东亮康眼科医院'
);
public function __construct()
{
$this->db = pc_base::load_model('advice_model');
}
/**
* 实现execl导入数据入库
*
*/
public function adviceinput()
{
foreach ($_FILES as $key => $val) {
if ($val['name']) {
$file_path = $val['tmp_name'];
$h_id = end(explode('_', $key));
}
}
$newpath = PHPCMS_PATH . '/uploadfile/advice/';
$csvdir = $this->uploadfile($file_path, $newpath);
$data = $this->importExecl($csvdir);
//动态添加flag字段
foreach ($data as $k => &$v) {
$res = array();
if ($k > 5) {
foreach ($this->advice_data as $key => $val) {
$data[$key]['flag'] = $key;
}
}
}
foreach ($data as $key => $v) {
if ($data[6]['D'] != '对话') {
$arr = array(
'code' => 2,
'msg' => '行跟列所对应的内容和模板不符!'
);
echo json_encode($arr);
exit;
}
if ($key > 5 && $key < 16) {
$res['jiguang'] = $v['F'];
$res['icl'] = $v['G'];
$res['xieruoshi'] = $v['H'];
$res['quguanbuzheng'] = $v['I'];
$res['peijing'] = $v['J'];
$res['bainei'] = $v['K'];
$res['jiaomo'] = $v['L'];
$res['yanji'] = $v['M'];
$res['leidao'] = $v['N'];
$res['yanzhengxin'] = $v['O'];
$res['yanbingqita'] = $v['P'];
$res['shangwutong'] = $v['Q'];
$res['tel'] = $v['R'];
$res['weixin'] = $v['S'];
$res['qq'] = $v['T'];
$res['qudaoqita'] = $v['U'];
if (empty($_POST['addtime'])) {
$inputtime = time();
} else {
$inputtime = strtotime($_POST['addtime']);
}
$res['addtime'] = $inputtime;
$res['h_id'] = $h_id;
$res['flag'] = $v['flag'];
$res['heji'] = $v['F'] + $v['G'] + $v['H'] + $v['I'] + $v['J'] + $v['K'] + $v['L'] + $v['M'] + $v['N'] + $v['O'] + $v['P'];
$res['quguan'] = $v['F'] + $v['G'];
$res['shiguang'] = $v['H'] + $v['I'] + $v['J'];
$item = $this->db->get_one($this->getWhere($res['h_id'], $res['flag']));
if ($item) {
$where_u['id'] = $item['id'];
$e = $this->db->update($res, $where_u);
} else {
$this->db->insert($res, true);
$ids = $this->db->insert_id($res, true);
}
}
}
$message = array(
'code' => 1,
'msg' => "上传成功"
);
echo json_encode($message);
}
function uploadfile($file, $path)
{
$saveto = $path;
$new_file = $saveto . time() . ".csv";
$up = @copy($file, $new_file);
if ($up) return $new_file;
}
//数据导出
public function adviceoutport()
{
$objPHPExcel = new PHPExcel();
//设置excel的属性：
//标题
$sheet = $objPHPExcel->getSheet(0); //读取excel文件中的第一个工作表
$result = $this->getadvice();
$quguan = $this->getQuguanData();
$shiguang = $this->getShiguanData();
// 设置水平居中
for ($i = "A"; $i < "M"; $i++) {
$objPHPExcel->getActiveSheet()->getStyle($i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
}
// 设置表格头部内容
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A3', '医院')
->setCellValue('B3', '总对话量')
->setCellValue('C3', '有效对话')
->setCellValue('D3', '预约量')
->setCellValue('E3', '转社群量')
->setCellValue('F3', '初诊')
->setCellValue('G3', '手术/配镜')
->setCellValue('H3', '明日到院')
->setCellValue('I3', '回访')
->setCellValue('J3', '反馈')
->setCellValue('K3', '预约')
->setCellValue('L3', '存在问题')
->setCellValue('A4', '总体情况')
->setCellValue('A1', date("md") . '集团咨询基本情况');
//冻结标题
$objPHPExcel->getActiveSheet()->freezePane('A4');
//设置单元格字体
$objPHPExcel->getActiveSheet()->getStyle('A4')->getFont()->setName('宋体')//字体
->setSize(12)//字体大小
->setBold(true); //字体加粗
$i = 0;
//获取总的数据
foreach ($result['data'] as $k => $v) {
$objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($i + 5), $this->hospital[$k]);
for ($l = 'B', $j = 6; $l < 'L'; $l++, $j++) {
$objPHPExcel->getActiveSheet(0)->setCellValue($l . ($i + 5), $v[$j]);
}
$objPHPExcel->getActiveSheet(0)->setCellValue('L' . ($i + 5), '');
$i++;
}
$num1 = 3 + count($result['data']);
$objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($num1 + 2), '集团平均');
for ($i = 'B', $j = 6; $i < 'L'; $i++, $j++) {
$objPHPExcel->getActiveSheet(0)->setCellValue($i . ($num1 + 2), $result['pingjun'][$j]);
}
$objPHPExcel->getActiveSheet()->mergeCells('A' . ($num1 + 3) . ':L' . ($num1 + 3));
$objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($num1 + 3), '其中屈光：');
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num1 + 3))->getFont()->setName('宋体')//字体
->setSize(12)//字体大小
->setBold(true); //字体加粗
//设置填充的样式和背景色
$objPHPExcel->getActiveSheet()->getStyle('A3:L3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A3:L3')->getFill()->getStartColor()->setARGB('00262626');
$objPHPExcel->getActiveSheet()->getStyle('A3:L3')->getFont()->setName('宋体'); //字体
$objPHPExcel->getActiveSheet()->getStyle('A3:L3')->getFont()->getColor()->setARGB(PHPExcel_Style_Color::COLOR_WHITE);
$objPHPExcel->getActiveSheet()->getStyle('A4:L4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A4:L4')->getFill()->getStartColor()->setARGB('00FFc000');
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num1 + 3) . ':L' . ($num1 + 3))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num1 + 3) . ':L' . ($num1 + 3))->getFill()->getStartColor()->setARGB('00FFc000');
//设置边框
$objPHPExcel->getActiveSheet()->getStyle('A4')->getBorders()->getLeft()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
$j = 0;
//获取屈光数据
foreach ($quguan['quguan'] as $k1 => $v1) {
$objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($j + $num1 + 4), $this->hospital[$k1]);
for ($l = 'B', $i = 6; $l < 'L'; $l++, $i++) {
$objPHPExcel->getActiveSheet(0)->setCellValue($l . ($j + $num1 + 4), $v1[$i]);
}
$objPHPExcel->getActiveSheet(0)->setCellValue('L' . ($j + $num1 + 4), '');
$j++;
}
$num2 = $num1 + count($quguan['quguan']) + 1;
$objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($num2 + 3), '集团平均');
for ($i = 'B', $j = 6; $i < 'L'; $i++, $j++) {
$objPHPExcel->getActiveSheet(0)->setCellValue($i . ($num2 + 3), $quguan['avg'][$j]);
}
$num3 = $num2 + 4;
//视光合并
$objPHPExcel->getActiveSheet()->mergeCells('A' . ($num3) . ':L' . ($num3));
$objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($num3), '其中视光：');
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num3))->getFont()->setName('宋体')//字体
->setSize(12)//字体大小
->setBold(true); //字体加粗
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num3) . ':L' . ($num3))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num3) . ':L' . ($num3))->getFill()->getStartColor()->setARGB('00FFc000');
$k = 0;
//获取视光数据
foreach ($shiguang['shiguang'] as $k2 => $v2) {
$objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($k + $num3 + 1), $this->hospital[$k2]);
for ($l = 'B', $i = 6; $l < 'L'; $l++, $i++) {
$objPHPExcel->getActiveSheet(0)->setCellValue($l . ($k + $num3 + 1), $v2[$i]);
}
$objPHPExcel->getActiveSheet(0)->setCellValue('L' . ($k + $num3 + 1), '');
$k++;
}
$num4 = $num3 + count($shiguang['shiguang']) + 1;
//视光平均
$objPHPExcel->getActiveSheet(0)->setCellValue('A' . ($num4), '集团平均');
//设置平均数
for ($i = 'B', $j = 6; $i < 'L'; $i++, $j++) {
$objPHPExcel->getActiveSheet(0)->setCellValue($i . ($num4), $shiguang['pj'][$j]);
}
//set width
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(22);
//设置背景色
$objPHPExcel->getActiveSheet()->getStyle('A4:L4')->getFill()->getStartColor()->setARGB('00ffc000');
//设置行高度
$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(22);
$objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(1);
//设置水平居中
$objPHPExcel->getActiveSheet()->getStyle('A4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num1 + 3))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num3))->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
$objPHPExcel->getActiveSheet()->getStyle('A1:L1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); //垂直方向上中间居中
//隔行换色
for ($i = 5; $i <= $num1 + 1; $i++) {
if ($i % 2 == 0) {
$objPHPExcel->getActiveSheet()->getStyle('A' . ($i) . ':L' . ($i))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($i) . ':L' . ($i))->getFill()->getStartColor()->setARGB('00bfbfbf');
}
}
$x = $num1 + 4;
for ($i = $x; $i <= $num2 + 2; $i++) {
if ($i % 2 == 0) {
$objPHPExcel->getActiveSheet()->getStyle('A' . ($i) . ':L' . ($i))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($i) . ':L' . ($i))->getFill()->getStartColor()->setARGB('00bfbfbf');
}
}
$j = $num3 + 1;
for ($i = $j; $i <= $num4 - 1; $i++) {
if ($i % 2 == 0) {
$objPHPExcel->getActiveSheet()->getStyle('A' . ($i) . ':L' . ($i))->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($i) . ':L' . ($i))->getFill()->getStartColor()->setARGB('00bfbfbf');
}
}
//单独一行的字体
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num1 + 2) . ':L' . ($num1 + 2))->getFont()->getColor()->setARGB('00c65911');
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num1 + 2) . ':L' . ($num1 + 2))->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num4) . ':L' . ($num4))->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num2 + 3) . ':L' . ($num2 + 3))->getFont()->setSize(12);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num1 + 2) . ':L' . ($num1 + 2))->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num4) . ':L' . ($num4))->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num2 + 3) . ':L' . ($num2 + 3))->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num2 + 3) . ':L' . ($num2 + 3))->getFont()->getColor()->setARGB('00c65911');
$objPHPExcel->getActiveSheet()->getStyle('A' . ($num4) . ':L' . ($num4))->getFont()->getColor()->setARGB('00c65911');
//合并单元格
$objPHPExcel->getActiveSheet()->mergeCells('A1:L1');
$objPHPExcel->getActiveSheet()->mergeCells('A4:L4');
$objPHPExcel->getActiveSheet()->mergeCells('A1:L2');
$objPHPExcel->getActiveSheet()->setTitle(date('md'));
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="resume.xls"');
header("Content-Transfer-Encoding:binary");
header('Content-Disposition: attachment;filename="' . date('n') . '月咨询日报-追踪.xls"'); //日期为文件名后缀
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); //excel5为xls格式，excel2007为xlsx格式
$objWriter->save('php://output');
}
/**
* 数据导入
* @param string $file excel文件
* @param string $sheet
* @return string 返回解析数据
* @throws PHPExcel_Exception
* @throws PHPExcel_Reader_Exception
*/
function importExecl($file = '', $sheet = 0)
{
$file = iconv("utf-8", "gb2312", $file); //转码
if (empty($file) OR !file_exists($file)) {
die('file not exists!');
}
$objRead = new PHPExcel_Reader_Excel2007(); //建立reader对象
if (!$objRead->canRead($file)) {
$objRead = new PHPExcel_Reader_Excel5();
if (!$objRead->canRead($file)) {
die('No Excel!');
}
}
$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');
$obj = $objRead->load($file); //建立excel对象
$currSheet = $obj->getSheet($sheet); //获取指定的sheet表
$columnH = $currSheet->getHighestColumn(); //取得最大的列号
if (!in_array($columnH, $cellName)) {
$arr = array(
'code' => 0,
'msg' => '单元格列数过多，请用标准模板!'
);
echo json_encode($arr);
exit;
}
$columnCnt = array_search($columnH, $cellName);
$rowCnt = $currSheet->getHighestRow(); //获取总行数
$data = array();
for ($_row = 1; $_row <= $rowCnt; $_row++) { //读取内容
for ($_column = 0; $_column <= $columnCnt; $_column++) {
$cellId = $cellName[$_column] . $_row;
$cellValue = $currSheet->getCell($cellId)->getValue();
if ($cellValue instanceof PHPExcel_RichText) { //富文本转换字符串
$cellValue = $cellValue->__toString();
}
$data[$_row][$cellName[$_column]] = $cellValue;
}
}
return $data;
}
/**
* 获取医院对应的咨询信息数据
*/
public function getadvice()
{
$res = $this->getCount('heji');
//求每列的平均值
foreach ($res['arr1'] as $k2 => $v2) {
$pingju[$k2] = round(array_sum($v2) / (count($v2)));
}
$data['data'] = $res['result'];
$data['pingjun'] = $pingju;
return $data;
}
/**
* 获取屈光的咨询数据
*/
public function getQuguanData()
{
//获取屈光类的数据
$res = $this->getCount('quguan');
//求每列的平均值
foreach ($res['arr1'] as $k2 => $v2) {
$avg[$k2] = round(array_sum($v2) / (count($v2)));
}
$data['avg'] = $avg;
$data['quguan'] = $res['result'];
return $data;
}
/**
* 获取视光的咨询数据
*/
public function getShiguanData()
{
$res = $this->getCount('shiguang');
//求每列的平均值
foreach ($res['arr1'] as $k2 => $v2) {
$avg[$k2] = round(array_sum($v2) / (count($v2)));
}
$data['pj'] = $avg;
$data['shiguang'] = $res['result'];
return $data;
}
/** 根据医院id查询条件
* @param $h_id 医院id
* @return string 返回where条件
*/
public function getWhere($h_id = false, $flag = false)
{
$where = '1=1';
if ($h_id)
$where .= " AND `h_id`=$h_id";
if ($flag)
$where .= " AND `flag`=$flag";
if(!$_POST['addtime'] && !$_GET['addtime']){
$inputtime = time();
}elseif ($_GET['addtime'])
{
$inputtime = strtotime($_GET['addtime']);
}else{
$inputtime = strtotime($_POST['addtime']);
}
$startime = strtotime(date("Y-m-d 0:0:0", $inputtime)); // 0点时间
$endtime = strtotime(date("Y-m-d 23:59:59", $inputtime)); // 23.59.59点时间
$where .= " AND `addtime` between $startime and $endtime ";
return $where;
}
//动态获取平均数
public function getCount($field)
{
foreach ($this->hospital as $k => $v) {
$where = $this->getWhere($k);
$all = $this->db->select($where, 'flag,' . "$field"); // 根据医院id和时间查询 咨询表中的 heji和flag 字段
foreach ($all as $key => $val) {
$arr[$val['flag']] = $val["$field"]; // 将查询的结果形成一个新的数组 格式为： array(6=>58)
foreach ($arr as $item => $value) {
$result[$k] = $arr;
}
}
}
// 每列组成的数组
foreach ($this->advice_data as $k1 => $v1) {
foreach ($result as $key => $value) {
$arr1[$k1][] = $value[$k1];
}
}
$data['arr1'] = $arr1;
$data['result'] = $result;
return $data;
}
//列表页
public function advice_list()
{
$result = $this->getadvice();
$quguan = $this->getQuguanData();
$shiguang = $this->getShiguanData();
$res = array(
'code' => 1,
'result' => $result,
'quguan' => $quguan,
'shiguang' => $shiguang
);
echo json_encode($res);
}
}
