极简生成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"?> <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 " <Worksheet ss:Name="" . $fileName . ""> <Table> "; $guard = 0; foreach($data as $v) { $guard++; if($guard==$this->limit) { ob_flush(); flush(); $guard = 0; } echo $this->_addRow($this->_charset($v)); } echo "</Table> </Worksheet> </Workbook>"; } private function _addRow($row) { $cells = ""; foreach ($row as $k => $v) { $cells .= "<Cell><Data ss:Type="String">" . $v . "</Data></Cell> "; } return "<Row> " . $cells . "</Row> "; } 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) )); }