开放原码了,再有同学碰到类似问题,不会像我这样到处去找资料和翻网站,码中有什么不好或者有什么缺点的请看到的同学多多指教,好更好的更新代码。
放个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.')');
}