极简生成excel方法；excel表导入数据库
<?php /** * 生成excel文件操作 * * @author wesley wu * @date 2013.12.9 */ class Excel { private $limit = 10000; public function download($data, $fileName) { $fileName = $this->_charset($fileName); header("Content-Type: application/vnd.ms-excel; charset=gbk"); header("Content-Disposition: inline; filename=\"" . $fileName . ".xls\""); echo "<?xml version=\"1.0\" encoding=\"gbk\"?>\n <Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">"; echo "\n<Worksheet ss:Name=\"" . $fileName . "\">\n<Table>\n"; $guard = 0; foreach($data as $v) { $guard++; if($guard==$this->limit) { ob_flush(); flush(); $guard = 0; } echo $this->_addRow($this->_charset($v)); } echo "</Table>\n</Worksheet>\n</Workbook>"; } private function _addRow($row) { $cells = ""; foreach ($row as $k => $v) { $cells .= "<Cell><Data ss:Type=\"String\">" . $v . "</Data></Cell>\n"; } return "<Row>\n" . $cells . "</Row>\n"; } private function _charset($data) { if(!$data) { return false; } if(is_array($data)) { foreach($data as $k=>$v) { $data[$k] = $this->_charset($v); } return $data; } return iconv('utf-8', 'gbk', $data); } } //使用方法 $excel = new Excel(); $data = array( array('姓名','标题','网址','价格','数据5','数据6','数据7'), array('数据1','数据2','https://xxx.com/2F01300000164186121366756803686.jpg','数据4','数据5','数据6','数据7'), array('数据1','数据2','数据3','数据4','数据5','数据6','数据7'), array('数据1','数据2','数据3','数据4','数据5','数据6','数据7'), array('数据1','数据2','数据3','数据4','数据5','数据6','数据7'), array('数据1','数据2','数据3','数据4','数据5','数据6','数据7') ); $excel->download($data, '这是一个测试'); ?>
二，phpexcel导出图片到excel表格
public function exceldownAction() { $data = json_decode(urldecode($this->post('row')),true); include_once(ROOT_PATH.'/include/Classes/PHPExcel.php'); $objPHPExcel = new \PHPExcel(); $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel); $objActSheet = $objPHPExcel->getActiveSheet(); // 水平居中（位置很重要，建议在最初始位置） $objPHPExcel->setActiveSheetIndex(0)->getStyle('A')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('B1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('C')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('D')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('E')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('F')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('G')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objPHPExcel->setActiveSheetIndex(0)->getStyle('H')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER); $objActSheet->setCellValue('A1', '巡查人'); $objActSheet->setCellValue('B1', '巡查人部门'); $objActSheet->setCellValue('C1', '相关照片'); $objActSheet->setCellValue('D1', '整改后照片'); $objActSheet->setCellValue('E1', '操作时间'); $objActSheet->setCellValue('F1', '相关情况说明'); $objActSheet->setCellValue('G1', '工作地点'); $objActSheet->setCellValue('H1', '是否已整改'); // 设置个表格宽度 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(16); $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(16); $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(15); $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25); $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12); $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(12); // 垂直居中 $objPHPExcel->getActiveSheet()->getStyle('A')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('B')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('D')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('E')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('F')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('G')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); $objPHPExcel->getActiveSheet()->getStyle('H')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER); foreach($data as $k=>$v){ $k +=2; $objActSheet->setCellValue('A'.$k, $v['base_name']); $objActSheet->setCellValue('B'.$k, $v['base_deptname']); // 图片一生成s $objDrawing[$k] = new \PHPExcel_Worksheet_Drawing(); $objDrawing[$k]->setPath(ROOT_PATH.'/upload'.explode("upload",$v['photo'])[1]); // 设置宽度高度 $objDrawing[$k]->setHeight(80);//照片高度 $objDrawing[$k]->setWidth(80); //照片宽度 /*设置图片要插入的单元格*/ $objDrawing[$k]->setCoordinates('C'.$k); // 图片偏移距离 $objDrawing[$k]->setOffsetX(12); $objDrawing[$k]->setOffsetY(12); $objDrawing[$k]->setWorksheet($objPHPExcel->getActiveSheet()); // 图片一生成e // 图片二生成 s $objDrawingt[$k] = new \PHPExcel_Worksheet_Drawing(); if(is_null($v['photo2']) || empty($v['photo2'])){ $objDrawingt[$k]->setPath(ROOT_PATH.'/images/noimg.jpg'); }else{ $objDrawingt[$k]->setPath(ROOT_PATH.'/upload'.explode("upload",$v['photo2'])[1]); } // 设置宽度高度 $objDrawingt[$k]->setHeight(80);//照片高度 $objDrawingt[$k]->setWidth(80); //照片宽度 /*设置图片要插入的单元格*/ $objDrawingt[$k]->setCoordinates('D'.$k); // 图片偏移距离 $objDrawingt[$k]->setOffsetX(12); $objDrawingt[$k]->setOffsetY(12); $objDrawingt[$k]->setWorksheet($objPHPExcel->getActiveSheet()); // 图片二生成 e // 表格内容 //$objActSheet->setCellValue('D'.$k, $v['photo2']); $objActSheet->setCellValue('E'.$k, $v['optdt']); $objActSheet->setCellValue('F'.$k, $v['desc']); $objActSheet->setCellValue('G'.$k, $v['areaname']); $objActSheet->setCellValue('H'.$k, strip_tags($v['statustext'])); // 表格高度 $objActSheet->getRowDimension($k)->setRowHeight(80); } $fileName = '巡查上报记录表'; $date = date("Y-m-d_h-i-s",time()); $fileName .= "_'$date'.xls"; $fileName = iconv("UTF-8", "GBK//IGNORE", $fileName); $objPHPExcel->setActiveSheetIndex(0); header('Content-Type: application/vnd.ms-excel'); header("Content-Disposition:attachment;filename=".$fileName.".xls"); header('Cache-Control: max-age=0'); $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $user_path = 'upload/xuncha_'.$date.'.xls';// $objWriter->save($user_path); $this->returnjson(array( 'url' => $user_path, 'downCount' => count($data) )); }
(转)
三、excel表导入数据库
index.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd"> <html lang="en"> <head> <meta http-equiv="Content-Type" content="text/html;charset=UTF-8"> <title>Upload</title> </head> <body> <form type="file" action="index.php" method="post" enctype="multipart/form-data"> <input type="file" name="file" id="file" /> <br/> <input type="submit" name="submit" value="上传" /> </form> </body> </html>
index.php
<?php $con = mysqli_connect("localhost","root","root","test");// 需设置 mysqli_query($con ,"set names 'gb2312'"); $file = $_FILES['file']['name']; $filetempname = $_FILES['file']['tmp_name']; //自己设置的上传文件存放路径 $filePath = 'upload/';// 需设置 require_once 'PHPExcel.php';// 需引入 require_once 'PHPExcel/IOFactory.php';// 需引入 require_once 'PHPExcel/Reader/Excel2007.php';// 需引入 $filename=explode(".",$file);//把上传的文件名以“.”好为准做一个数组。 $time=date("Y-m-d-H-i-s");//去当前上传的时间 $filename[0]=$time;//取文件名t替换 $name=implode(".",$filename); //上传后的文件名 $uploadfile=$filePath.$name;//上传后的文件名地址 //move_uploaded_file() 函数将上传的文件移动到新位置。若成功，则返回 true，否则返回 false。 $result=move_uploaded_file($filetempname,$uploadfile); if($result){ //如果上传文件成功，就执行导入excel操作 $objPHPExcel = PHPExcel_IOFactory::load($uploadfile); $sheet = $objPHPExcel->getSheet(0); $highestRow = $sheet->getHighestRow(); // 取得总行数 ,获取行数(多少条数据)错误，所以下面写死89条 $highestColumn = $sheet->getHighestColumn();// 取得总列数 //循环读取excel文件,读取一条,插入一条 $str = ""; for($j=2;$j<=89;$j++){ //因为Excel第一行和数据表中的字段相对照，所以$j=2代表从Excel的第二行开始。 for($k='A';$k<=$highestColumn;$k++){ $str .= iconv("UTF-8","gbk",$objPHPExcel->getActiveSheet()->getCell("$k$j")->getValue()).'\\';//读取单元格 } $strs = explode("\\",$str); $sql = "insert into hw_carm(carnum, device, sim) values('".$strs[0]."','".$strs[1]."','".$strs[2]."')"; if(!mysqli_query($con ,$sql)){ return false; } $str = ""; } unlink($uploadfile); //删除上传的excel文件 $msg = "导入成功！"; }else{ $msg = "导入失败！"; } echo $msg;
