php phpexcelプラグインによるデータのインポートとエクスポート(csvxlsxlsxフォーマットと26以上のフィールド列をサポート)
18359 ワード
一、インポート(ここでshow_msgとlogFileはカスタム関数)
二、エクスポート(この機能はページ上のhtml中のtable表の直接エクスポートをサポートする)
1、フロントエンドコード
2、バックエンドコード
/**
* : excelToTable
* : 【 】 excel
* @param1 ： file
* @param2 ： tableid ，1-statistics_rawdata_pct，2-statistics_rawdata_apply，3-statistics_rawdata_auth，4-statistics_rawdata_valid
* @param3 ： month_number ， 201510
* @param4 ： table_head excel ，
* @date : 2015/11/26
* @author : dingling
*/
private function excelToTable($file,$tableid,$month_number,$table_head=1){
if(!empty($file['name'])){
$file_types = explode ( ".", $file['name'] );
$excel_type = array('xls','csv','xlsx');
// excel
if (!in_array(strtolower(end($file_types)),$excel_type)){
$this->show_msg(" Excel ， ","/search/patentStatistics/uploadRawdata");
}
//
$savePath = _WWW_ . 'www/tmp/';
//
$str = date ( 'Ymdhis' );
$file_name = $str.".".end($file_types);
//
$tmp_file = $file['tmp_name'];
if (!copy($tmp_file,$savePath.$file_name)){
$this->show_msg(" ","/search/patentStatistics/uploadRawdata");
}
if($tableid=="1"){
$rawdata_obj = $this->rawdata_pctmodel;
}elseif($tableid=="2"){
$rawdata_obj = $this->rawdata_applymodel;
}elseif($tableid=="3"){
$rawdata_obj = $this->rawdata_authmodel;
}elseif($tableid=="4"){
$rawdata_obj = $this->rawdata_validmodel;
}else{
$this->show_msg(" ！","/search/patentStatistics/uploadRawdata");
}
if($rawdata_obj)
$fields = $rawdata_obj->returnFields();
else
$this->show_msg(" ！","/search/patentStatistics/uploadRawdata");
//
$logfile = $savePath.$str.'.txt';
// excel， ， key 1
$res = $this->excelToArray($savePath.$file_name,end($file_types));
//echo 12321321;exit;
// ，
if($table_head)
unset($res[1]);
// ， ， ； txt
foreach($res as $k =>$v){
foreach($fields as $key=>$val){
if($v[$key]===null){
$v[$key] = 'null';
}
$data[$val] = $v[$key];
}
// ，
$data['month_number'] = $month_number;
$result = $rawdata_obj->addSave($data);
unset($data);
if(!$result){
$this ->logFile($logfile,$v[0]);
}
}
if(file_get_contents($logfile))
return $logfile;
else
return true;
}
}
/**
* : excelToArray
* : 【 】 excel
* @param1 ： filename excel
* @param2 ： filetype excel （xls、xlsx、csv）
* @param3 ： encode ， utf8
* @return ： 2 ， key 1
* @date : 2015/11/26
* @author : dingling
*/
private function excelToArray($filename,$filetype,$encode='utf-8'){
if(strtolower($filetype)=='xls'){
$objReader = PHPExcel_IOFactory::createReader('Excel5');
}elseif(strtolower($filetype)=='xlsx'){
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
}elseif(strtolower($filetype)=='csv'){
$objReader = PHPExcel_IOFactory::createReader('CSV');
}
$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;
}二、エクスポート(この機能はページ上のhtml中のtable表の直接エクスポートをサポートする)
1、フロントエンドコード
2015 08
2015 08
6814
2088
3763
963
828
5986
412
536
4920
118
1 2157
823
1033
301
246
1911
264
337
1229
81
2 1181
569
407
205
192
989
64
92
825
8
3 1095
259
758
78
51
1044
10
19
1001
14
4 416
78
225
113
55
361
34
5
318
4
5 393
100
262
31
37
356
0
9
340
7
6 388
84
264
40
99
289
16
62
209
2
7 387
84
254
49
55
332
16
1
314
1
8 304
21
228
55
19
285
0
0
285
0
9 164
18
109
37
29
135
6
2
127
0
10 147
33
82
32
10
137
0
8
128
1
11 66
7
52
7
14
52
2
1
49
0
12 58
7
44
7
9
49
0
0
49
0
13 23
1
18
4
6
17
0
0
17
0
14 21
1
17
3
2
19
0
0
19
0
15 10
2
7
1
4
6
0
0
6
0
16 3
0
3
0
0
3
0
0
3
0
17 1
1
0
0
0
1
0
0
1
0
18 191
67
112
12
14
177
0
1
176
0
：
window.οnlοad=function(){
$("#export_data").click(function(){
//
var title = $(".table").find("tr").eq(0).find('th').html();
//
var line_num = $(".table tr").length;
//
var max_col_num = $(".table").find("tr").eq(0).find('th').attr('colspan');
// col_num_arr col_num_str
var col_num_arr = new Array();
// td th
var total_td_num = 0;
for(var i=0;i<line_num;i++){
col_num_arr[i] = $(".table").find("tr").eq(i).find('td').length;
if(col_num_arr[i]=='')
col_num_arr[i] = $(".table").find("tr").eq(i).find('th').length;
total_td_num = total_td_num + col_num_arr[i];
}
var col_num_str = col_num_arr.join(',');//alert(col_num_str);
// th
var th_col_num_arr = [];
for(var i=0;i<line_num;i++){
var j = $(".table").find("tr").eq(i).find('th').length;
if(j != 0)
th_col_num_arr[i] = j;
}
var th_col_num_str = th_col_num_arr.join(',');//alert(th_col_num_str);
// th
var th_data_arr = [];
var th_num = $(".table").find('th').length;
for(var i=0;i<th_num;i++){
var val = $(".table").find('th').eq(i).html();
if(typeof(val)!='undefined'&&typeof(val)!='')
th_data_arr[i] = val;
}
var th_data_str = th_data_arr.join('@@@');//alert(th_data_arr[0]);alert(th_data_str);
// td
var td_data_arr = [];
var td_num = $(".table").find('td').length;
for(var i=0;i<td_num;i++){
var val = $(".table").find('td').eq(i).html();
if(typeof(val)!='undefined')
td_data_arr[i] = val;
}
var td_data_str = td_data_arr.join('@@@');
$.ajax({
url:"/search/patentStatistics/exportData",
type:"POST",
data:{title:title,max_col_num:max_col_num,th_col_num_str:th_col_num_str,th_data_str:th_data_str,td_data_str:td_data_str},
dataType:"json",
success:function(data){
//console.log(data.href);
window.open(data.href);
}
})
});
}
2、バックエンドコード
/**
* : exportData
* :
* @date 2015/03/26
* @author dingling
* @return excel
*/
public function exportDataAction(){
$title = strip_tags($_POST['title']);//excel
$max_col_num = $_POST['max_col_num'];//
$th_num_arr = explode(',',trim($_POST['th_col_num_str']));// th
array_shift($th_num_arr);// th
$head_line = count($th_num_arr);// th
$th_data = explode('@@@',trim($_POST['th_data_str']));
array_shift($th_data);// th( )
$th_data2 = array();
foreach($th_data as $k=>$v){
$th_data2[] = strip_tags($v);
}
// （ ） （ ）
foreach($th_num_arr as $k=>$v){
foreach($th_data2 as $key=>$val){
if($key$v){
if(count($head[$k])$v){
$data[$k/$max_col_num][$k%$max_col_num] = strip_tags($v);
}
$path = $this -> getExcel($title,$title,$head2,$data);
echo json_encode(array('href'=>$path)) ;
}
/**
* : getExcel
* : Excel
* @date 2015/03/26
* @author dingling
* @param1
* @param2 sheet
* @param3 ( )
* @param4
* @return excel
*/
private function getExcel($fileName,$fileName2,$headArr,$data){
//
if(empty($data) || !is_array($data)){
die(" ");
}
//
if(empty($fileName)){
exit;
}
//
$date = date("Y_m_d",time());
$fileName .= "_{$date}.xls";
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('PRC');
if (PHP_SAPI == 'cli')
die(' ');
// PHPExcel
$objPHPExcel = new PHPExcel();
$objProps = $objPHPExcel->getProperties();
//
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue('A1', $fileName2);
//
for($i=0;$iord("Z")){
$key2 += 1;
$key = ord("A");
$colum = chr($key2).chr($key);// 26 dingling 20150626
}else{
if($key2>=ord("A")){
$colum = chr($key2).chr($key);
}else{
$colum = chr($key);
}
}
$objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.$line_num,$v);
$key += 1;
}
}
$column = count($headArr)+2;
$objActSheet = $objPHPExcel->getActiveSheet();
foreach($data as $v){ //
$span = ord("A");
$span2 = ord("@");
foreach($headArr[0] as $key=>$val){
if($span>ord("Z")){
$span2 += 1;
$span = ord("A");
$j = chr($span2).chr($span);// 26 dingling 20150626
}else{
if($span2>=ord("A")){
$j = chr($span2).chr($span);
}else{
$j = chr($span);
}
}
$objActSheet->setCellValue($j.$column, strip_tags($v[$key]));
$span++;
}
$column++;
}
$fileName = iconv("utf-8", "gb2312", $fileName);
$objPHPExcel->getActiveSheet()->setTitle($fileName2);//
$objPHPExcel->setActiveSheetIndex(0);// , Excel
ob_end_clean();// ,
// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=\"$fileName\"");
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header ('Pragma: public'); // HTTP/1.0
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//$objWriter->save('php://output'); //
//
$savePath = _WWW_ . 'www/tmp/';
$file = time().'.xls';
$objWriter->save($savePath.$file); //
return '/tmp/'.$file;
}