极简生成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 "\n"; echo "\n<Worksheet ss:Name=\"" . $fileName . "\">\n\n"; $guard = 0; foreach($data as $v)
{ $guard++; if($guard==$this->limit)
{ ob_flush(); flush(); $guard = 0;
} echo $this->_addRow($this->_charset($v));
} echo "\n\n";
}
private function _addRow($row)
{ $cells = ""; foreach ($row as $k => $v)
{ $cells .= "" . $v . "\n";
} return "\n" . $cells . "\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, '这是一个测试'); ?>View Code
二，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)
));
}View Code
(转)
三、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>Uploadtitle>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>View Code
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;View Code
