Drupal 中读取Excel数据再转换格式到另一个Excel

开放原码了，再有同学碰到类似问题，不会像我这样到处去找资料和翻网站，码中有什么不好或者有什么缺点的请看到的同学多多指教，好更好的更新代码。

放个costsheet.info先：

name = CostSheet Convert Puma  description = 读取CostSheet到Puma格式  package = Ewen DIY  dependencies[] = phpexcel  core = 7.x  version = "7.x-1.0"  project = "costsheet"  datestamp = "1458790508"

直下去把模块丢上来：

<?php/** * @file * Defines "从CostSheet中读取转为Puma的格式"  * * @TODO: Split admin functions into their own file. */ /*** Implements hook_menu(). 执行Hook来注册一个URL*/function costsheet_menu(){$items=array();$items['costsheet']=array('title'=>'CostSheet Convert','page callback'=>'costsheet_intro','access callback'=>TRUE,);$items['costsheet/format']=array('title'=>'Costsheet Convert','description'=>'CostSheet of Inetgarment Convert To Brand fromat ','page callback'=>'drupal_get_form','page arguments'=>array('costsheet_readexcel'),//页面回调的function'access arguments'=>array('use costsheet convert'),//页面调用的用户权限'access callback'=>TRUE,//'weight'=>'5',//'file'=>'costsheet_convert.inc',//如果Function不在本文件可以放到其它文件中再使用此参数加入);return $items;}/** * @file * 定义一个CostSheet的权限 */ function costsheet_permission(){ return array('use costsheet convert'=>array('title'=>t('Use the Inetgarment CostSheet To Other Format'),),); } /** * @file * 定义CostSheet的说明 */ function costsheet_intro(){$markup = t('点击左边栏中的链接对目标的格式进行转换');return array('#markup' => $markup); }/** * @file * 定义一个上传Excel的表单 */function costsheet_readexcel($form, &$form_state){ $form=array(); $form['excel']=array('#type' => 'fieldset','#title' => t('CostSheet Convert'), ); $form['excel']['file'] = array('#type' => 'managed_file','#title' => t('Upload Excel'),'#description' => t('上传Excel文件，可以是: xls,xlsx'),'#upload_location' => 'public://costsheet/','#upload_validators' => array('file_validate_extensions' => array('xls xlsx'),'file_validate_size' => array(2000000),),);$form['excel']['puma_submit'] = array('#type' => 'submit','#value' => t('To PUMA'),//'#submit' =>array('costsheet_puma_ajax'),'#ajax'=>array('callback'=>'costsheet_puma_ajax','wrapper'=>'showexcel-wrapper',),);$form['showexcel']=array('#type'=>'fieldset','#title'=>t('Show The Excel'),'#prefix'=>'<div id="showexcel-wrapper">','#suffix'=>'</div>','#markup' =>'',);$form['submit']=array('#type'=>'submit','#value'=>t('Submit'),);  return $form;} /** * @ * 定义ajax回调PUMA的格式转换 */ function costsheet_puma_ajax($form,&$form_state){$path=$form['excel']['file']['#file']->uri; $myfirst=array(); $myheard=array(); $myfabric=array(); $mybrandaccessories=array(); $mypackaginglaber=array(); $j=0; //drupal_set_message($output); module_load_include('inc','phpexcel'); $result=phpexcel_import($path,FALSE); //dpm($result); if (is_array($result)&& count($result)==1){ $myfirst=$result[0][0]; //dpm($myfirst); //检查是否读数成功和表单为单一表单。 if(in_array('adidas Apparel Costing Sheet',$myfirst)){ //检查标题是否为PUMA格式的表格 for($i=0;$i<=36;$i++){ $myheard[]=$result[0][$i]; unset($result[0][$i]); } //前36行是表头部分//dpm($myheard);unset($result[0][37]);unset($result[0][38]);$j=39; //布料从39行开始//dpm(count($result,1));for($i=0;$i<count($result,1)/17-2;$i++){$myfirst=$result[0][$j];//dpm($myfirst[1]);if(in_array('b. TRIMS by Part # RMS Item #',$myfirst)){break;}if($myfirst[1]==''&&($myfirst[7]=='0'|| empty($myfirst[7]))){unset($result[0][$j]);$j++;continue;}$myfabric[]=$result[0][$j];unset($result[0][$j]);$j++;}//中间布料部分//dpm($myfabric);for($i=0;$i<count($result,1)/17-2;$i++){$myfirst=$result[0][$j];if(in_array('d. APPAREL PACKAGING/LABELS',$myfirst)){break;}if($myfirst[1]==''&&($myfirst[7]=='0'|| empty($myfirst[7]))){unset($result[0][$j]);$j++;continue;}$mybrandaccessories[]=$result[0][$j];unset($result[0][$j]);$j++;}//dpm($mybrandaccessories);//工艺+印花for($i=0;$i<count($result,1)-2;$i++){$myfirst=$result[0][$j];if(in_array('REMARKS:',$myfirst)){break;}if($myfirst[1]==''&&($myfirst[7]=='0'|| empty($myfirst[7]))){unset($result[0][$j]);$j++;continue;}$mypackaginglaber[]=$result[0][$j];unset($result[0][$j]);$j++;}//生成Excel表$myfilename='PUMA'.time().'.xls';$correct_path = file_create_filename($myfilename, 'public://costsheet/');$actual_path = phpexcel_munge_filename($correct_path);create_puma_xls($actual_path,$myheard,$myfabric,$mybrandaccessories,$mypackaginglaber);$form['showexcel']['#description']=t("CostSheet Convert To <a href='@value'>PUMA  Format</a>", array('@value' => file_create_url($actual_path)));return $form['showexcel']; }else{drupal_set_message(t('Read Costsheet Error!')); }  } else{ drupal_set_message(t('excel error,Unsupport!')); } }  /** * @ * 定义Form的提交 */ function costsheet_submit($form,&$form_state){  drupal_set_message(t('submit')); } function create_puma_xls($filename,$header,$fabric,$brandaccessories,$packaginglaber){global $user;$tempfile=drupal_get_path('module','costsheet').'/template/PUMA.xls';$objReader=PHPExcel_IOFactory::createReader("Excel5");$objPHPExcel=$objReader->load($tempfile);//readly $header &Fabric &brandaccessories &packaginglaber;//建立表头//Season$objPHPExcel->getActiveSheet()->setCellValue('c3',$header[3][2]);//Line//$objPHPExcel->getActiveSheet()->setCellValue('c4',$header[3][2]);//Style #$objPHPExcel->getActiveSheet()->setCellValue('c5',$header[3][8]);//Gender//$objPHPExcel->getActiveSheet()->setCellValue('c6',$header[3][2]);//Size Run$objPHPExcel->getActiveSheet()->setCellValue('c7',$header[7][2]);//Item$objPHPExcel->getActiveSheet()->setCellValue('c8',$header[4][8]);//Supplier$objPHPExcel->getActiveSheet()->setCellValue('c9','TSG');//Factory CODE$objPHPExcel->getActiveSheet()->setCellValue('c10','TCNTS');//Forecast$objPHPExcel->getActiveSheet()->setCellValue('c11',$header[6][2]);//布料用量开始$baseRow=17;costsheet_fill_format($objPHPExcel,$fabric,$baseRow);//辅料用量开始$baseRow+=3; costsheet_fill_format($objPHPExcel,$brandaccessories,$baseRow);//包装和其它用量开始$baseRow+=3;costsheet_fill_format($objPHPExcel,$packaginglaber,$baseRow);$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');$objWriter->save($filename);}function costsheet_fill_format(&$objexcel,$fabric,&$position){foreach($fabric as $f=>$fabricRow){$row=$position+$f;$objexcel->getActiveSheet()->insertNewRowBefore($row,1);$tempdata=0;if($fabricRow[10]=='0' || empty($fabricRow[10])){$tempdata=$fabricRow[7];}else{if(substr($fabricRow[10],-1)=='%'){$tempdata=((float)$fabricRow[10]/100+1)*(float)$fabricRow[7];}else{$tempdata=((float)$fabricRow[10])+(float)$fabricRow[7];}}$objexcel->getActiveSheet()->setCellValue('B'.$row,$fabricRow[2])  ->setCellValue('C'.$row,$fabricRow[1])  ->setCellValue('D'.$row,$fabricRow[4])  ->setCellValue('E'.$row,$fabricRow[9])  ->setCellValue('F'.$row,$tempdata)  ->setCellValue('G'.$row,'=E'.$row.'*F'.$row)  ->setCellValue('J'.$row,$fabricRow[6])  ->setCellValue('K'.$row,$fabricRow[0]);  }$beforRow=$position;$position+=count($fabric)+2;$afterRow=$position-1;$objexcel->getActiveSheet()->setCellValue('G'.$position,'=SUM(G'.$beforRow.':G'.$afterRow.')');}