<?php
class GetpriceAction extends AdministratorAction {
// 文件保存路径
protected $savepath;
// 允许上传的文件类型
protected $allowFileType;
public function _initialize(){
parent::_initialize();
$this->savepath = './xxx/'.date('Ymd').'/';
$this->allowFileType = array('xlsx','xls');
}
public function index(){
$this->display();
}
public function upload(){
$beginLine = intval($_POST['beginLine']);
$bnCol = htmlspecialchars(strtoupper($_POST['bnCol']));
// $numCol = htmlspecialchars(strtoupper($_POST['numCol']));
if($beginLine<1){
$this->dialogmessage('须正确填写开始行数','error');
}
if(!preg_match('/^[a-zA-Z]+$/',$bnCol)) {
$this->dialogmessage('须正确填写货号所在列', 'error');
}
$savepath = $this->savepath;
mk_dir($savepath);
import("ORG.Net.UploadFile");
$upload = new UploadFile();
$upload->maxSize = 114570;
$upload->allowExts = $this->allowFileType;
$upload->savePath = $savepath.''; // 讴置附件上传目录
if(!$upload->upload()){
$this->dialogmessage($upload->getErrorMsg(),'excel');
exit();
}else{
$fileInfo = $upload->getUploadFileInfo();
$data = $this->editStore($fileInfo,$beginLine,$bnCol);
}
$this->assign($data);
$this->display();
}
/**
* $info
*
* @param $fileInfo
* @param $beginLine
* @param $bnCol
* @return array
*/
protected function editStore($fileInfo,$beginLine,$bnCol){
require_once './excel/Classes/PHPExcel.php';
$filePath = $fileInfo[0]['savepath'].$fileInfo[0]['name'];
chmod($filePath,0777);
//建立reader对象
$PHPReader = new PHPExcel_Reader_Excel2007();
if(!$PHPReader->canRead($filePath)){
$PHPReader = new PHPExcel_Reader_Excel5();
if(!$PHPReader->canRead($filePath)){
$this->dialogmessage('no excel','error');
}
}
//建立excel对象，此时你即可以通过excel对象读取文件，也可以通过它写入文件
$PHPExcel = $PHPReader->load($filePath);
/**读取excel文件中的第一个工作表*/
$currentSheet = $PHPExcel->getSheet(0);
/**取得最大的列号*/
$allColumn = $currentSheet->getHighestColumn();
/**取得一共有多少行*/
$allRow = $currentSheet->getHighestRow();
//循环读取每个单元格的内容。注意行从1开始，列从A开始
$codes=array();
for($rowIndex=$beginLine;$rowIndex<=$allRow;$rowIndex++){
$bn=$currentSheet->getCell($bnCol.$rowIndex)->getValue();
$b = $currentSheet->getCell('B'.$rowIndex)->getValue();
$c = $currentSheet->getCell('C'.$rowIndex)->getValue();
$d = $currentSheet->getCell('D'.$rowIndex)->getValue();
$e = $currentSheet->getCell('E'.$rowIndex)->getValue();
if($bn){
$f = model('Product')->where('bn=\''.$bn.'\'')->getField('price');
$codes[] = array('a'=>$bn,'b'=>$b,'c'=>$c,'d'=>$d,'e'=>$e,'f'=>$f);
}
}
$data['data'] = $codes;
$data['title'] = array('货号','1','1','1','1','销售价');
$data['width'] = array(30,30,30,30,30,30);
$add = array();
$add['ctime'] = time();
$add['data'] = serialize($data);
$add['title'] = '价格' . date('Ymdhis');
$name = uniqid();
$add['file'] = SITE_PATH . '/Public/excel/' . $name . '.xls';
$add['url'] = SITE_URL . '/Public/excel/' . $name . '.xls';
$add['uid'] = 1;
$id = M('excel')->add($add);
if ($id > 0) {
pclose(popen('/usr/bin/php -f /xxx/exceltask.php ' . $id . ' &', 'r'));
echo '导出任务成功,请等待系统完成';
exit;
} else {
echo '导出任务失败';
exit;
}
}
/**
* 写入日志
* @param Array $arrContent
* @param String $filename
* @return int
*/
protected function writeLog($arrContent,$filename){
$intLogStatus = file_put_contents($this->savepath.$filename.'.php','<?php '.PHP_EOL.' return '.
var_export($arrContent,true).';');
return $intLogStatus;
}
}
<?php
error_reporting(0);
date_default_timezone_set('RPC');
define('SITE_PATH',dirname(__FILE__));
include SITE_PATH.'/db.class.php';
$id=intval($argv[1]);
if($id<=0){
exit();
}
$exceldata=M('excel')->where('id='.$id)->find();
$data=unserialize($exceldata['data']);
include SITE_PATH.'/libs/PHPExcel.php';
$objExcel=new PHPExcel();
$columns=array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ');
$objExcel->getProperties()->setCreator("xxx");
$objExcel->getProperties()->setLastModifiedBy("xxx");
$objExcel->getProperties()->setTitle($title);
$objExcel->getProperties()->setSubject($title);
$objExcel->getProperties()->setDescription($title);
$objExcel->getProperties()->setKeywords($title);
$objExcel->getProperties()->setCategory($title);
$objExcel->setActiveSheetIndex(0);
foreach($data['title'] as $k=>$v){
$objExcel->getActiveSheet()->setCellValue($columns[$k].'1',$v);
}
if($data['width']){
foreach($data['width'] as $k=>$v){
$objExcel->getActiveSheet()->getColumnDimension($columns[$k])->setWidth($v);
}
}
$i=2;
foreach($data['data'] as $v){
$v=array_values($v);
foreach($v as $k=>$vv){
preg_match_all('/http:\/\/img\.xxx\.com\//', $vv, $matches);
if($matches[0]){
$objExcel->getActiveSheet()->getRowDimension($i)->setRowHeight(100);
$objDrawing = new PHPExcel_Worksheet_Drawing();
$file=SITE_PATH.'/tmp/'.uniqid().'.jpg';
file_put_contents($file,file_get_contents($vv));
$objDrawing->setPath($file);
$objDrawing->setCoordinates($columns[$k].$i);
$objDrawing->setOffsetX(10);
$objDrawing->setOffsetY(10);
$objDrawing->setWidth(100);
$objDrawing->setHeight(100);
$objDrawing->setWorksheet($objExcel->getActiveSheet());
unset($objDrawing);
}
else{
if(is_numeric($vv)){
//$vv=number_format($vv,2);
$objExcel->getActiveSheet()->setCellValueExplicit($columns[$k].$i,$vv);
}
else{
$objExcel->getActiveSheet()->setCellValueExplicit($columns[$k].$i,$vv,PHPExcel_Cell_DataType::TYPE_STRING);
}
}
}
$i++;
}
$objWriter = PHPExcel_IOFactory::createWriter($objExcel, 'Excel5');
$objWriter->save($exceldata['file']);
M('excel')->where('id='.$id)->setField('endtime',time());
exit;
?>
