php 导入 导出Excel 实现翻译两种不同的Excel文件（xlsx文件和csv文件）

需要注意的是申请百度翻译的APPID和密钥时，我申请的是标准服务，所以每秒只能调用一次，就必须休眠一秒，不然会报54003，所以翻译大量数据列时调用就比较慢，想要快一些可以申请时选择另外两种申请服务，那样翻译会快些，也就节约了很多的时间。

我用的是php原生代码的写法，所以放在index.php中，

我的项目路径：

html部分

<div style="width:30%;position: absolute; top:5%;left:5%;"><form action="index.php" method="post" enctype="multipart/form-data"><fieldset><legend>翻译</legend><label for="upload-file">上传文件:</label><input type="file" name="upload-fl" /><button type="submit" class="button"><span><span>导入</span></span></button></fieldset></form></div>

需要引入js，

<script type="text/javascript" src="plugins/jquery.min-2.0.js"></script>

下面是php代码，获取上传的文件并获取数据，进行随机语言翻译

<?phprequire "./Class/PHPExcel.php";require "./Class/PHPExcel/IOFactory.php";require "./function.php";require "./config.php";//API配置$_appUrl = 'https://api./api/trans/vip/translate';$_appId = '您的appid';// API Key$_appKey = '您的密钥'; // 密钥$_appSalt = rand(1000000000, 9999999999); // 随机数$_appQPS = 0;function translate($value, $from, $to){global $_appQPS,$_appId,$_appKey,$_appSalt,$_appUrl;$_appQPS++;if($_appQPS == 1){ //$_appQPS是每次调用的频率,每调用一次就休眠一次，这样就不会报54003错误，这里申请的是标准服务，所以每秒只能调用一次sleep(1);$_appQPS = 0;}$args = array('q'=> $value,'appid' => $_appId,'salt' => $_appSalt,'from' => $from,'to' => $to,);// 签名$args['sign'] = buildSign($value, $_appId, $_appSalt, $_appKey);$result = call($_appUrl, $args);$result = json_decode($result, true);if (isset($result['trans_result']['0']['dst'])) {if (!empty($result['trans_result']['0']['dst'])) {return $result['trans_result']['0']['dst'];}$message = sprintf('翻译(%s) [ %s ] 至 (%s) 为空', $from, $value, $to);} else {$message = sprintf('翻译(%s) [ %s ] 至 (%s) 错误失败: %s', $from, $value, $to, print_r($result, true));}echo $message."<br/>";}//请求超时@set_time_limit(0);//文件$tmpName = isset($_FILES['upload-fl']['name'])? $_FILES['upload-fl']['name']: '';$tmpFile = isset($_FILES['upload-fl']['tmp_name']) ? $_FILES['upload-fl']['tmp_name'] : '';$fileTypes = explode('.', $tmpName);$fileType = $fileTypes[count($fileTypes) - 1];$data= array();$productName = array();$type = array('xlsx','csv');$language = array('zh','yue','wyw','jp','kor','fra','spa','th','ara','ru','pt','de','it','el','nl','pl','bul','est','dan','fin','cs','rom','slo','swe','hu','cht','vie');$languageLen = count($language) - 1;if (empty($tmpName)) {echo "<script> alert('上传失败，没有选择文件') </script>";} elseif (!in_array(strtolower($fileType),$type)) {echo "<script> alert('不是xlsx或csv文件，请重新选择') </script>";}//判断文件是否上传成功if (is_uploaded_file($tmpFile)) {if (strtolower($fileType) == 'xlsx') {$objPHPExcel = PHPExcel_IOFactory::load($tmpFile); // 加载Excel表格$sheet = $objPHPExcel->getSheet(0);$allRow= $sheet->getHighestRow(); //取得总行数for ($i = 2; $i <= $allRow; $i++) {$name = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();if (!empty($name)) {$randLanguage = $language[mt_rand(0,$languageLen)];$name = translate($name, 'en', $randLanguage);$randLanguage_1 = $language[mt_rand(0,$languageLen)];$name = translate($name, $randLanguage, $randLanguage_1);$name = translate($name,$randLanguage_1,'en');}array_push($data,$name);}//导出excel$fileName = 'product'.date('YmdHis').'.xlsx';$objPHPExcel = new PHPExcel(); //实例化excel核心类$sheets= $objPHPExcel->getActiveSheet()->setTitle('产品名称');//设置sheet列头信息$objPHPExcel->setActiveSheetIndex()->setCellValue('A1', '产品名称');$i = 2; //第一行为表头//整体设置字体和字体大小$objPHPExcel->getDefaultStyle()->getFont()->setName( 'Arial');//整体设置字体$objPHPExcel->getDefaultStyle()->getFont()->setSize(10);//整体设置字体大小$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(40); //设置列宽度foreach ($data as $val) {//设置单元格值$objPHPExcel->getActiveSheet()->setCellValue('A'.$i,$val);$i++;}$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel');$objWriter->save( 'example/' . $fileName);$url = APP_HTTP.'/example/'. $fileName;} elseif (strtolower($fileType) == 'csv') {if (!($handle = fopen($tmpFile,'r'))) {echo '<script> alert("文件无法读取!") </script>';}while ($data = fgetcsv($handle)) {//获取产品名$name = trim($data[1]);if (!empty($name)) {$randLanguage = $language[mt_rand(0,$languageLen)];$randLanguage_1 = $language[mt_rand(0,$languageLen)];$name = translate($name, 'en', $randLanguage);$name = translate($name, $randLanguage, $randLanguage_1);$name = translate($name,$randLanguage_1,'en');}array_push($productName,$name);}$file_name = 'product'.date('YmdHis').'.csv';$data = '"v_name"'."\n";foreach ($productName as $val) {$data .= '"'.$val.'"'."\n";}header("Content-Type: text/csv");header("Content-Disposition: attachment; filename=".$file_name);header('Cache-Control:must-revalidate,post-check=0,pre-check=0');header('Expires:0');header('Pragma:public');echo $data;die;}}?>

在translate()方法中，有两个方法buildSign和call我是放在function.php文件，与index.php在同级目录下，需要引入function.php，这样引入： require "./function.php";就可以调用这两个方法了。

function.php文件下的代码

<?php//加密function buildSign($query, $appID, $salt, $secKey){/*{{{*/$str = $appID . $query . $salt . $secKey;$ret = md5($str);return $ret;}/*}}}*///发起网络请求function call($url, $args=null, $method="post", $testflag = 0, $headers=array()){/*{{{*/$ret = false;$i = 0;while($ret === false){if($i > 1)break;if($i > 0){sleep(1);}$ret = callOnce($url, $args, $method, false, $headers);$i++;}return $ret;}/*}}}*/function callOnce($url, $args=null, $method="post", $withCookie = false, $headers=array()){/*{{{*/$ch = curl_init();if($method == "post"){$data = convert($args);// print_r($data);curl_setopt($ch, CURLOPT_POSTFIELDS, $data);curl_setopt($ch, CURLOPT_POST, 1);}else{$data = convert($args);if($data){if(stripos($url, "?") > 0){$url .= "&$data";}else{$url .= "?$data";}}}curl_setopt($ch, CURLOPT_URL, $url);curl_setopt($ch, CURLOPT_TIMEOUT, 5);curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);if(!empty($headers)){curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);}if($withCookie){curl_setopt($ch, CURLOPT_COOKIEJAR, $_COOKIE);}$r = curl_exec($ch);curl_close($ch);return $r;}/*}}}*/function convert(&$args){/*{{{*/$data = '';if (is_array($args)){foreach ($args as $key=>$val){if (is_array($val)){foreach ($val as $k=>$v){$data .= $key.'['.$k.']='.rawurlencode($v).'&';}}else{$data .="$key=".rawurlencode($val)."&";}}return trim($data, "&");}return $args;}/*}}}*/

在index.php中APP_PATH是定义在config.php,表示导出的Excel所存放的路径

config.php下的代码

define('APP_HOST', (empty($_SERVER['HTTPS']) || $_SERVER['HTTPS'] == 'off' ? 'http://' : 'https://') . $_SERVER['HTTP_HOST']);define('APP_CATALOG', str_ireplace('/index.php', '', $_SERVER['SCRIPT_NAME']) . '/');define('APP_HTTP', APP_HOST . APP_CATALOG);

最后是..xlsx文件输出到浏览器所用js代码

<script>$(function(){var url = "<?php echo isset($url) ? $url : '';?>";if(url){window.open(url,'_blank','width=0,height=0,status=0');}});</script>

亲自测，有用！！！