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.')');
}

中间如何写Excel是本文的主功能，其它好多是排版。