1.横向导出
效果图如下：
public function cross() { require_once '/www/data/assets/PHPExcel/Classes/PHPExcel.php'; $objExcel = new PHPExcel(); $objProps = $objExcel->getActiveSheet(); $names = ['A'=>'姓名','B'=>'电话','C'=>'邮箱']; foreach ($names as $key=>$value) { $objProps->getStyle($key)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objProps->getStyle($key)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objProps->getColumnDimension($key)->setWidth(20); $name = $key.'1'; $objProps->setCellValue($name, $value);
} $line = 2; $data = [
['name'=>'mike','phone'=>'123','email'=>'123@.com'],
['name'=>'lucy','phone'=>'456','email'=>'456@.com'],
['name'=>'jack','phone'=>'789','email'=>'789@.com'],
]; foreach ($data as $key => $value) { $objProps->setCellValue("A" . $line, $value['name']); $objProps->setCellValue("B" . $line, $value['phone']); $objProps->setCellValue("C" . $line, $value['email']); $line++;
} $write = new PHPExcel_Writer_Excel5($objExcel); 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="横向(' . date('d/m/Y', time()) . '-' . date('d/m/Y', time()) . ').xls"');
header("Content-Transfer-Encoding:binary"); $write->setPreCalculateFormulas(false); $write->save('php://output');
}
-
1
-
2
-
3
-
4
-
5
-
6
-
7
-
8
-
9
-
10
-
11
-
12
-
13
-
14
-
15
-
16
-
17
-
18
-
19
-
20
-
21
-
22
-
23
-
24
-
25
-
26
-
27
-
28
-
29
-
30
-
31
-
32
-
33
-
34
-
35
-
36
-
37
-
38
-
39
-
40
-
41
-
42
-
43
-
44
-
45
-
46
-
47
-
48
-
49
-
50
-
51
-
52
-
53
-
54
-
55
-
56
-
57
-
58
-
59
-
60
-
61
-
62
-
63
-
64
-
65
-
66
-
67
-
68
2.垂直向
效果图
public function vertical11() { require_once '/www/data/assets/PHPExcel/Classes/PHPExcel.php'; $objExcel = new PHPExcel(); $objProps = $objExcel->getActiveSheet(); $names = ['A'=>'姓名','B'=>'电话','C'=>'邮箱']; foreach ($names as $key=>$value) { $objProps->getStyle($key)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objProps->getStyle($key)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER); $objProps->getColumnDimension($key)->setWidth(20); $name = $key.'1'; $objProps->setCellValue($name, $value);
} $num = 1; $data = [
['name'=>'mike','phone'=>'123','email'=>'123@.com'],
['name'=>'lucy','phone'=>'456','email'=>'456@.com'],
['name'=>'jack','phone'=>'789','email'=>'789@.com'],
]; foreach ($data as $key => $value) { $data = array_values( $value ); $length = count($data); $data[] = ''; $name = ['姓名','电话','邮箱',''];
reset($name); foreach ($data as $k => $v) { $line = ($num-1)*$length + $k+1; if ($k ==0) { $objProps->setCellValue("A" . $line, $num);
} else { $objProps->setCellValue("A" . $line, '');
} if ($k ==$length-1) { $objProps->setCellValue("B" . $line, ''); $objProps->setCellValue("C" . $line, '');
} else { $objProps->setCellValue("B" . $line, current($name)); $objProps->setCellValue("C" . $line, $v);
}
next($name);
} $num ++;
} $write = new PHPExcel_Writer_Excel5($objExcel); 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="横向(' . date('d/m/Y', time()) . '-' . date('d/m/Y', time()) . ').xls"');
header("Content-Transfer-Encoding:binary"); $write->setPreCalculateFormulas(false); $write->save('php://output');
}
-
1
-
2
-
3
-
4
-
5
-
6
-
7
-
8
-
9
-
10
-
11
-
12
-
13
-
14
-
15
-
16
-
17
-
18
-
19
-
20
-
21
-
22
-
23
-
24
-
25
-
26
-
27
-
28
-
29
-
30
-
31
-
32
-
33
-
34
-
35
-
36
-
37
-
38
-
39
-
40
-
41
-
42
-
43
-
44
-
45
-
46
-
47
-
48
-
49
-
50
-
51
-
52
-
53
-
54
-
55
-
56
-
57
-
58
-
59
-
60
-
61
-
62
-
63
-
64
-
65
-
66
-
67
-
68
-
69
-
70
-
71
-
72
-
73
-
74
-
75
-
76
-
77
-
78
-
79
-
80
-
81
-
82
-
83
3.导入
public function import() { require_once '/www/assets/PHPExcel/Classes/PHPExcel.php'; require_once '/www/assets/PHPExcel/Classes/PHPExcel/IOFactory.php'; $ex = $_FILES['excel']; $game = $_POST['game']; $suffix = substr($ex['name'],stripos($ex['name'],'.')); $filename = time().'-'.rand(10,99).$suffix; $path = '/www/data/excel/'.$filename; move_uploaded_file($ex['tmp_name'],$path); if ($suffix=='.xlsx') { $type = 'Excel2007';
} else if ($suffix=='.xls'){ $type = 'Excel5'; } else {
header('Content-type: application/json'); echo json_encode(['msg'=>'BAD_PARAM','echo'=>'']); exit;
} $xlsReader = PHPExcel_IOFactory::createReader($type); $xlsReader->setReadDataOnly(true); $xlsReader->setLoadSheetsOnly(true); $Sheets = $xlsReader->load($path); $dataArray = $Sheets->getSheet(0)->toArray(); $key_filed = ['游戏'=>'game','区服'=>'server','账号'=>'user','角色名'=>'role_name','角色ID'=>'role_id','充值金额'=>'pay_amount','充值日期'=>'pay_time','平台'=>'platform','是否分成'=>'profit','联系方式'=>'contact','备注'=>'info','提交状态'=>'sub_status','回访状态'=>'visit_status','录入人'=>'oprater','录入时间'=>'create_time','最后修改'=>'update_time']; $excelField = []; foreach ( $dataArray[0] as $key=>$value) { $excelField[] = $key_filed[$value];
} for ($i=1;$i<count($dataArray);$i++) { $check_data = array_combine($excelField, $dataArray[$i]); $check_result = $this->checkField($check_data); if ($check_result) { $string_data = array_map([$this, "valueToString"], $dataArray[$i]); $data = array_combine($excelField, $string_data); $this->mongodb->selectDB("welfare_rebate"); $rebateCollection = $this->mongodb->getCollection('rebate_record'); $data['game'] = $game; $data['create_time'] = time(); $data['update_time'] = time(); $data['oprater'] = $this->authorize->getId2(); $rebateCollection->insert($data);
}
}
header('Content-type: application/json'); echo json_encode(['msg'=>'OK']);
}
转自：http://blog.csdn.net/xiojing825/article/details/70198579
版权声明：若无特殊注明，本文皆为( yueshuo )原创，转载请保留文章出处。
