<>tp5 read / download excel Document content
1, Download one excel Template
derived function
/** * establish ( export )Excel Data table * @param array $expTableData Data in array format to export * @param string
$expTitle Exported Excel The file name of the table data table * @param array $expCellName
$list Array and Excel Table header $header The name of the field corresponding to each item in (key value ) */ function
exportExcel($expTitle,$expCellName,$expTableData){ // File import
vendor("PHPExcel.PHPExcel"); // Introduction of Toolkits $xlsTitle = iconv('utf-8', 'gb2312',
$expTitle);// File name $fileName = $expTitle.date('_YmdHis');//or $xlsTitle
The file name can be set according to your own situation $cellNum = count($expCellName); $dataNum = count($expTableData);
$objPHPExcel = new \PHPExcel(); $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');// merge cell
// $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]]); } } ob_end_clean();
header('pragma:public');
header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
header("Content-Disposition:attachment;filename=$fileName.xls");//attachment New window printing inline Print in this window
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output'); }
Write the function well , Export in progress
//excel Template public function excelModel() { $list = array();
$head=array(array('username',' full name '),array('id_card',' ID number '),array('mobile',' phone number '),array('type',' classification '),array('msg',' Leaving a message. '));
$name =' Batch import template table -'.date('Y-m-d H:i:s',time()); exportExcel($name,$head,$list); }
When visiting , Generated a excel file . Let's add some data to it
2, read excel content
// read excel Document content public function do_upload(){ vendor("PHPExcel.PHPExcel");
// Import file $objPHPExcel = new \PHPExcel(); // Get form upload file $file =
request()->file('file'); $info = $file->validate(['ext' =>
'xlsx,xls'])->move(ROOT_PATH . 'public' . DS . 'uploads'); // Error returned when data is empty
if(empty($info)){ $output['status'] = false; $output['info'] = ' Failed to import data ~';
$this->ajaxReturn($output); } // Get file name $exclePath = $info->getSaveName();
// The address of the uploaded file $filename = ROOT_PATH . 'public' . DS . 'uploads'.DS . $exclePath;
$extension = strtolower( pathinfo($filename, PATHINFO_EXTENSION) );
\think\Loader::import('PHPExcel.IOFactory.PHPExcel_IOFactory'); if ($extension
=='xlsx') { $objReader = new \PHPExcel_Reader_Excel2007(); $objExcel =
$objReader ->load($filename); } else if ($extension =='xls') { $objReader = new
\PHPExcel_Reader_Excel5(); $objExcel = $objReader->load($filename); }
$excel_array=$objExcel->getsheet(0)->toArray(); // Convert to array format
array_shift($excel_array); // Delete the first array ( title ); array_shift($excel_array); // delete th
$data=[]; foreach ($excel_array as $k=>$v){ $data[$k]["username"]=$v[0];// Odd Numbers
$data[$k]["id_card"]=$v[1];// Type name $data[$k]["mobile"]=$v[2];
$data[$k]["type"]=$v[3]; $data[$k]["msg"]=$v[4]; } $msg=[ 'Code' => '100000',
'Msg' => 'Success' ]; $msg['Data']=$data; return json_encode($msg); }
We use postman Test it .
Technology