phpexcel
来源：互联网 发布：优米网倒闭知乎 编辑：程序博客网 时间：2024/05/21 18:33
1，前台简单的form或者是ajaxfileupload.js提交
<head> <meta charset="UTF-8"> <title></title> <script src="js/jquery-1.7.2.min.js"></script> <script src="js/ajaxfileupload.js"></script></head><body><script> jQuery(function(){ $("#buttonUpload").click(function(){ //加载图标 /* $("#loading").ajaxStart(function(){ $(this).show(); }).ajaxComplete(function(){ $(this).hide(); });*/ //上传文件 $.ajaxFileUpload({ url: 'excelOperate.php',//处理脚本 secureuri: false, fileElementId: 'fileToUpload',//file控件id dataType: 'json', success: function (data, status) { if (typeof(data.error) != 'undefined') { if (data.error != '') { alert(data.error); if(data.responsefile){ window.location.href = data.responsefile; } } else { alert(data.message); //window.location.href = data.responsefile; } } }, error: function (data, status, e) { alert(e); } }); return false; }) })</script><!--jqueryUpload 适合上传成功和失败的文件（已经在服务器上的，然后window.location.href 重定向下载）--><div id="upload"> <input id="fileToUpload" type="file" size="20" name="fileToUpload" class="input"> <button id="buttonUpload">上传</button></div><!--form 则可以提交给隐藏的iframe，传递数据流下载（也就是不保存excel文件直接向浏览器返回excel数据流）--><div> <form method="post" action="excelOperate.php" enctype="multipart/form-data"> <h3>导入Excel表：</h3><input type="file" name="fileToUpload" /> <input type="submit" value="导入" /> </form> <!-- target="excelDownload" <iframe src="" name="excelDownload" frameborder="0" ></iframe>--></div>2，后台处理（关键）
处理一：
<?php/** * Date: 11/13/2015 * Time: 9:57 AM */$result = array( "error" => "", "message" => "", "responsefile" => "");$fileElementName = 'fileToUpload';//html中上传文件的input的name$allowType = array(".xls",".xlsx",".csv"); //允许上传的文件类型$num = strrpos($_FILES[$fileElementName]['name'] ,'.');$fileSuffixName = strtolower(substr($_FILES[$fileElementName]['name'],$num,8));//此数可变$upFilePath = 'd:/'; //最终存放路径if(!empty($_FILES[$fileElementName]['error'])) { switch ($_FILES[$fileElementName]['error']) { case '1': $error = '传的文件超过了 php.ini 中 upload_max_filesize 选项限制的值'; break; case '2': $error = '上传文件的大小超过了 HTML 表单中 MAX_FILE_SIZE 选项指定的值'; break; case '3': $error = '文件只有部分被上传'; break; case '4': $error = '没有文件被上传'; break; case '6': $error = '找不到临时文件夹'; break; case '7': $error = '文件写入失败'; break; default: $error = '未知错误'; } $result['error'] = $error; exit(json_encode($result));}if(empty($_FILES[$fileElementName]['tmp_name']) || $_FILES['fileToUpload']['tmp_name'] == 'none') { $result['error'] = '没有上传文件.'; exit(json_encode($result));}if(!in_array($fileSuffixName,$allowType)) { $result['error'] = '不允许上传的文件类型'; exit(json_encode($result));}/* * 如果需要上传文件到指定指定则指向下面的$upFilePath,如果只是处理数据则 $data->read($_FILES[$fileElementName]['tmp_name']);//读取excel累世这种直接读取缓存文件中的数据是一样的if(@move_uploaded_file($_FILES[$fileElementName]['tmp_name'],$upFilePath) === FALSE){ $error = '上传失败'; exit(json_encode($result));}*///开始处理excel//excel验证（标识符）todo//读取excel//将include设置为./Classses/ 路径set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');include 'PHPExcel/IOFactory.php';//读出excel数据$exceldata = excelRead(($_FILES[$fileElementName]['tmp_name']));//读出错返回错误json//你自己对应的数据处理和筛选//如果要保存或者是输出对应反馈excelexcelWrite('data',$a = array(), $exceldata, false, 5);/** * @param $filename * @return array * @throws PHPExcel_Exception * @throws PHPExcel_Reader_Exception */function excelRead($filename){ $objReader = PHPExcel_IOFactory::createReader('Excel5'); $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(); } /*可筛选固定的列 $excelData[$row]['id'] = $objPHPExcel->getActiveSheet()->getCell("A".$row)->getValue();//ID $excelData[$row]['name'] = $objPHPExcel->getActiveSheet()->getCell("D".$row)->getValue();//姓名 */ } return $excelData;}/** * @param array $data 一个二维数组,结构如同从数据库查出来的数组 * @param array $title excel的第一行标题,一个数组,如果为空则没有标题 * @param string $filename 下载的文件名 * 但是对07以上的excel出错 * * exportexcel($arr,array('id','账户','密码','昵称'),'文件名!'); */function excelWrite1($data=array(),$title=array(),$filename='report'){ header("Content-type:application/octet-stream"); header("Accept-Ranges:bytes"); header("Content-type:application/vnd.ms-excel"); header("Content-Disposition:attachment;filename=".$filename.".xls"); header("Pragma: no-cache"); header("Expires: 0"); //导出xls 开始 if (!empty($title)){ foreach ($title as $k => $v) { $title[$k]=iconv("UTF-8", "GB2312",$v); } $title= implode("\t", $title); echo "$title\n"; } if (!empty($data)){ foreach($data as $key=>$val){ foreach ($val as $ck => $cv) { $data[$key][$ck]=iconv("UTF-8", "GB2312", $cv); } $data[$key]=implode("\t", $data[$key]); } echo implode("\n",$data); }}/*列索引*/function getExcelColumnValue($index){ $array = range('A', 'Z'); $columnValue = ''; if ($index >= 26) { $columnValue = getExcelColumnValue(intval($index / 26) - 1) . $array[$index % 26]; } else { $columnValue = $array[$index] . $columnValue; } return $columnValue;}/** * @param $fileName * @param $headArr * @param $data excel数据 * @param bool|false $colNum 默认不限制列数，防止有些很末尾的列数隐藏数值而中间都是空的 * @param bool|false $local 默认浏览器下载excel * @throws PHPExcel_Exception */function excelWrite($fileName, $headArr, $data,$local = false, $colNum = false){ if (empty($data) || !is_array($data)) { die("data must be a array"); } if (empty($fileName)) { exit; } if(is_numeric($colNum) && is_int($colNum+0) && ((int)$colNum > 0) ) { $colNumCount = true; } $date = date("Y_m_d", time()); /* if($local) { //03兼容格式 $fileName .= "_{$date}.xls"; }else{ $fileName .= "_{$date}.xlsx"; }*/ $fileName .= "_{$date}.xls"; //创建新的PHPExcel对象 $objPHPExcel = new PHPExcel(); $objProps = $objPHPExcel->getProperties(); if(is_array($headArr) && !empty($headArr)){ //设置表头 $columindex = 0; foreach ($headArr as $key => $v) { if($colNumCount ) { if(((int)$columindex < (int)$colNum)) { $colum = getExcelColumnValue($columindex); $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v); $columindex++; } }else{ $colum = getExcelColumnValue($columindex); $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v); $columindex++; } } $row = 2; }else{ $row = 1; } $objActSheet = $objPHPExcel->getActiveSheet(); $objPHPExcel->getActiveSheet()->getStyle( 'A1:E1')->getFill()->getStartColor()->setARGB('FF0094FF'); //$objPHPExcel->getActiveSheet()->getStyle( 'A1:E1')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID); /*$objPHPExcel->getActiveSheet()->getStyle( 'A3:A10')->applyFromArray( array( 'font' => array ( 'bold' => true ), 'alignment' => array ( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_RIGHT , ), 'borders' => array ( 'top' => array ( 'style' => PHPExcel_Style_Border::BORDER_THIN ) ), 'fill' => array ( 'type' => PHPExcel_Style_Fill::FILL_GRADIENT_LINEAR , 'rotation' => 90, 'startcolor' => array ( 'argb' => 'FFA0A0A0' ), 'endcolor' => array ( 'argb' => 'FF0094FF' ) ) ) );*/ foreach ($data as $key => $rows) { //行写入 $columindex = 0; foreach ($rows as $keyName => $value) {// 列写入 if($colNumCount){ if(((int)$columindex < (int)$colNum)) { $colum = getExcelColumnValue($columindex); //宽度 //$objActSheet->getColumnDimension($colum)->setAutoSize(true); //颜色 $objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080'); $objActSheet->setCellValue($colum . $row, $value); $columindex++; } } else { $colum = getExcelColumnValue($columindex); //宽度 //$objActSheet->getColumnDimension($colum)->setAutoSize(true); //颜色 //$objPHPExcel->getActiveSheet()->getStyle('A1')->getFill()->getStartColor()->setARGB('FF808080'); $objActSheet->setCellValue($colum . $row, $value); $columindex++; } } $row++; } $fileName = iconv("utf-8", "gb2312", $fileName); //重命名表 $objPHPExcel->getActiveSheet()->setTitle('Simple'); //设置活动单指数到第一个表,所以Excel打开这是第一个表 $objPHPExcel->setActiveSheetIndex(0); //将输出重定向到一个客户端web浏览器(Excel2007) $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); if($local) { $objWriter->save($fileName); //脚本方式运行，保存在当前目录 }else{ //$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');这个中可以输出xlsx header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header("Content-Disposition: attachment; filename=\"$fileName\""); header('Cache-Control: max-age=0'); $objWriter->save('php://output'); } exit;}处理二：
<?php/** * Date: 2015/11/16 * Time: 18:20 */$result = array( "error" => "", "message" => "", "responsefile" => "");$fileElementName = 'fileToUpload';//html中上传文件的input的name$allowType = array(".xls",".xlsx",".csv"); //允许上传的文件类型$num = strrpos($_FILES[$fileElementName]['name'] ,'.');$fileSuffixName = strtolower(substr($_FILES[$fileElementName]['name'],$num,8));//此数可变$upFilePath = 'd:/'; //最终存放路径if(!empty($_FILES[$fileElementName]['error'])) { switch ($_FILES[$fileElementName]['error']) { case '1': $error = '传的文件超过了 php.ini 中 upload_max_filesize 选项限制的值'; break; case '2': $error = '上传文件的大小超过了 HTML 表单中 MAX_FILE_SIZE 选项指定的值'; break; case '3': $error = '文件只有部分被上传'; break; case '4': $error = '没有文件被上传'; break; case '6': $error = '找不到临时文件夹'; break; case '7': $error = '文件写入失败'; break; default: $error = '未知错误'; } $result['error'] = $error; exit(json_encode($result));}if(empty($_FILES[$fileElementName]['tmp_name']) || $_FILES['fileToUpload']['tmp_name'] == 'none') { $result['error'] = '没有上传文件.'; exit(json_encode($result));}if(!in_array($fileSuffixName,$allowType)) { $result['error'] = '不允许上传的文件类型'; exit(json_encode($result));}/* * 如果需要上传文件到指定指定则指向下面的$upFilePath,如果只是处理数据则 $data->read($_FILES[$fileElementName]['tmp_name']);//读取excel累世这种直接读取缓存文件中的数据是一样的if(@move_uploaded_file($_FILES[$fileElementName]['tmp_name'],$upFilePath) === FALSE){ $error = '上传失败'; exit(json_encode($result));}*///开始处理excel//excel验证（标识符）todo//读取excel//将include设置为./Classses/ 路径set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');include 'PHPExcel/IOFactory.php';include('PHPExcel.php');$excelData = excelRead(($_FILES[$fileElementName]['tmp_name']));$excelHeader = array('a1','a2','a3','a4','a5','a6','a7','a8','a9','a10');excelWrite('aaa', $excelData,$excelHeader, false);function excelRead($excelfile){ if(is_null($excelfile)){ exit('excel文件不存在'); } $exceldata = array(); $reader = PHPExcel_IOFactory::createReaderForFile($excelfile); #Excel2007 $reader->setReadDataOnly(true); $excel= $reader->load($excelfile); $sheet=$excel->getActiveSheet(); $highestRow = $sheet->getHighestRow(); // 取得总行数，从一开始 $highestColumm = $sheet->getHighestColumn(); // 取得总列数，从0开始 //检查excel存在于AZ位置上的密码是否为20021514 /*$key = $sheet -> getCellByColumnAndRow( 51 , 1 ) -> getValue();//按照索引取 if( $key != '20021514' ){}else{}*/ //不包含excel头行 for($row = 2 ;$row <= $highestRow ;$row++){ //直接选择列 $i = $row - 2; $exceldata[$i][] = trim($sheet->getCellByColumnAndRow(0 , $row)->getValue()); $exceldata[$i][] = trim($sheet->getCellByColumnAndRow(1 , $row)->getValue()); $exceldata[$i][] = trim($sheet->getCellByColumnAndRow(2 , $row)->getValue()); $exceldata[$i][] = trim($sheet->getCellByColumnAndRow(3 , $row)->getValue()); $exceldata[$i][] = trim($sheet->getCellByColumnAndRow(4 , $row)->getValue()); $exceldata[$i][] = trim($sheet->getCellByColumnAndRow(5 , $row)->getValue()); $exceldata[$i][] = trim($sheet->getCellByColumnAndRow(6 , $row)->getValue()); $exceldata[$i][] = trim($sheet->getCellByColumnAndRow(7 , $row)->getValue()); $exceldata[$i][] = trim($sheet->getCellByColumnAndRow(8 , $row)->getValue()); $exceldata[$i][] = trim($sheet->getCellByColumnAndRow(9 , $row)->getValue()); } //对数据做相应处理 return $exceldata;}function excelWrite($filename, $exceldata, $excelHeader, $local =false, $uploadPath = './upload/41'){ $writer = new PHPExcel(); $writer -> setActiveSheetIndex(0); $writer_sheet = $writer-> getActiveSheet(); $writerow = 1; if(is_array($excelHeader) && !empty($excelHeader)){ foreach($excelHeader as $k => $v){ $writer_sheet ->setCellValueByColumnAndRow($k, $writerow, $v); } $writerow++; } foreach($exceldata as $data){ foreach($data as $k =>$v){ $writer_sheet -> setCellValueByColumnAndRow($k, $writerow, $v); } $writerow++; } //假设,之后加个默认上传路径 $filename = $filename.".xls" ; $filename = iconv("utf-8", "gb2312", $filename); if($local){ $output = $uploadPath."/".$filename ; $PHPExcelWriter = PHPExcel_IOFactory::createWriter($writer, 'Excel5'); $PHPExcelWriter -> save($output); /* header("Content-type: application/force-download"); header("Content-Disposition: attachment; Filename=\"$filename\""); header("Content-Length: ".Filesize($output)); @readFile($output);*/ }else{ header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header("Content-Disposition: attachment; filename=\"$filename\""); header('Cache-Control: max-age=0'); $PHPExcelWriter = PHPExcel_IOFactory::createWriter($writer, 'Excel5'); $PHPExcelWriter->save('php://output'); }}代码下载链接
http://download.csdn.net/detail/u012329424/9267103
0 0
- phpexcel
- phpexcel
- phpexcel
- PHPExcel
- PHPExcel
- PHPexcel
- PHPEXCEL
- phpexcel
- phpexcel
- phpexcel
- phpexcel
- phpexcel
- PHPexcel
- phpExcel
- PHPExcel
- PHPExcel
- PHPExcel
- PHPExcel
- BeanPropertyRowMapper小错误
- 二分查找算法
- Centos 6.4 python 2.6 升级到 2.7
- C#暴力屏蔽任务管理器
- 稀疏矩阵的存储，及矩阵的转置
- phpexcel
- setjmp和longjmp函数使用详解
- matlab字符函数
- 数据库索引与多表联查
- 【Android学习笔记】Android中的进程和线程
- Android 读取SD卡中的text文件内容
- 日历控件合集
- Vue.js使用props传递数据驼峰式与短横线隔
- hdu 4635 Strongly connected
