因为公司做的系统是全球都会使用到，所以我们每个版本可能都会更新一些新的多语言。
有的语言是有专业的人员翻译好过来的，但是还有的语言需要自己这边来进行翻译。
所以下面写了一个读写Excel翻译供大家参考和使用！
1、准备所需要用到的pom.xml
<!-- /artifact/com.alibaba/easyexcel --><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>3.1.1</version></dependency><!-- /artifact/org.apache.httpcomponents/httpcore --><dependency><groupId>org.apache.httpcomponents</groupId><artifactId>httpcore</artifactId><version>4.4.15</version></dependency>
2、目标：把Excel中的中文全部变成英文(先准备一个1.xlsx)
3、准备读取和写入Excel的实体类
读取Excel实体类
package com.lezu.springboot.excel;import com.alibaba.excel.annotation.ExcelProperty;import lombok.Data;/*** 读取Excel的实体类* @author LianJiaYu* @date /8/11 9:40*/@Datapublic class LanguageReadData {@ExcelProperty(value = "chinese", index = 0)private String chinese;}
写入Excel实体类
package com.lezu.springboot.excel;import com.alibaba.excel.annotation.ExcelProperty;import lombok.Data;/*** 写入Excel的实体类* @author LianJiaYu* @date /8/11 10:11*/@Datapublic class LanguageWriteData {@ExcelProperty(value = "chinese", index = 0)private String chinese;@ExcelProperty(value = "english", index = 1)private String english;}
4、准备调用谷歌的api工具类代码(支持多种语言翻译)
工具类转自：/p/29f95efaa88b#fromHistory
package com.lezu.springboot.utils.google;import com.alibaba.fastjson.JSONArray;import org.apache.http.HttpEntity;import org.apache.http.NameValuePair;import org.apache.http.client.entity.UrlEncodedFormEntity;import org.apache.http.client.methods.CloseableHttpResponse;import org.apache.http.client.methods.HttpPost;import org.apache.http.impl.client.CloseableHttpClient;import org.apache.http.impl.client.HttpClients;import org.apache.http.message.BasicNameValuePair;import org.apache.http.util.EntityUtils;import java.io.IOException;import java.nio.charset.Charset;import java.util.*;/*** @author aping* @time /5/22 16:42*/public class GT {private static final String PATH = "/translate_a/single"; //地址private static final String CLIENT = "gtx";private static final String USER_AGENT = "Mozilla/5.0";//"Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36";private static final Map<String, String> LANGUAGE_MAP = new HashMap();private static GT _instance = null;/*** 获取单例** @return*/public static GT getInstance() {if (null == _instance) {_instance = new GT();_instance.init();}return _instance;}/*** 初始化语言类*/private void init() {LANGUAGE_MAP.put("auto", "Automatic");LANGUAGE_MAP.put("af", "Afrikaans");LANGUAGE_MAP.put("sq", "Albanian");LANGUAGE_MAP.put("am", "Amharic");LANGUAGE_MAP.put("ar", "Arabic");LANGUAGE_MAP.put("hy", "Armenian");LANGUAGE_MAP.put("az", "Azerbaijani");LANGUAGE_MAP.put("eu", "Basque");LANGUAGE_MAP.put("be", "Belarusian");LANGUAGE_MAP.put("bn", "Bengali");LANGUAGE_MAP.put("bs", "Bosnian");LANGUAGE_MAP.put("bg", "Bulgarian");LANGUAGE_MAP.put("ca", "Catalan");LANGUAGE_MAP.put("ceb", "Cebuano");LANGUAGE_MAP.put("ny", "Chichewa");LANGUAGE_MAP.put("zh_cn", "Chinese Simplified");LANGUAGE_MAP.put("zh_tw", "Chinese Traditional");LANGUAGE_MAP.put("co", "Corsican");LANGUAGE_MAP.put("hr", "Croatian");LANGUAGE_MAP.put("cs", "Czech");LANGUAGE_MAP.put("da", "Danish");LANGUAGE_MAP.put("nl", "Dutch");LANGUAGE_MAP.put("en", "English");LANGUAGE_MAP.put("eo", "Esperanto");LANGUAGE_MAP.put("et", "Estonian");LANGUAGE_MAP.put("tl", "Filipino");LANGUAGE_MAP.put("fi", "Finnish");LANGUAGE_MAP.put("fr", "French");LANGUAGE_MAP.put("fy", "Frisian");LANGUAGE_MAP.put("gl", "Galician");LANGUAGE_MAP.put("ka", "Georgian");LANGUAGE_MAP.put("de", "German");LANGUAGE_MAP.put("el", "Greek");LANGUAGE_MAP.put("gu", "Gujarati");LANGUAGE_MAP.put("ht", "Haitian Creole");LANGUAGE_MAP.put("ha", "Hausa");LANGUAGE_MAP.put("haw", "Hawaiian");LANGUAGE_MAP.put("iw", "Hebrew");LANGUAGE_MAP.put("hi", "Hindi");LANGUAGE_MAP.put("hmn", "Hmong");LANGUAGE_MAP.put("hu", "Hungarian");LANGUAGE_MAP.put("is", "Icelandic");LANGUAGE_MAP.put("ig", "Igbo");LANGUAGE_MAP.put("id", "Indonesian");LANGUAGE_MAP.put("ga", "Irish");LANGUAGE_MAP.put("it", "Italian");LANGUAGE_MAP.put("ja", "Japanese");LANGUAGE_MAP.put("jw", "Javanese");LANGUAGE_MAP.put("kn", "Kannada");LANGUAGE_MAP.put("kk", "Kazakh");LANGUAGE_MAP.put("km", "Khmer");LANGUAGE_MAP.put("ko", "Korean");LANGUAGE_MAP.put("ku", "Kurdish (Kurmanji)");LANGUAGE_MAP.put("ky", "Kyrgyz");LANGUAGE_MAP.put("lo", "Lao");LANGUAGE_MAP.put("la", "Latin");LANGUAGE_MAP.put("lv", "Latvian");LANGUAGE_MAP.put("lt", "Lithuanian");LANGUAGE_MAP.put("lb", "Luxembourgish");LANGUAGE_MAP.put("mk", "Macedonian");LANGUAGE_MAP.put("mg", "Malagasy");LANGUAGE_MAP.put("ms", "Malay");LANGUAGE_MAP.put("ml", "Malayalam");LANGUAGE_MAP.put("mt", "Maltese");LANGUAGE_MAP.put("mi", "Maori");LANGUAGE_MAP.put("mr", "Marathi");LANGUAGE_MAP.put("mn", "Mongolian");LANGUAGE_MAP.put("my", "Myanmar (Burmese)");LANGUAGE_MAP.put("ne", "Nepali");LANGUAGE_MAP.put("no", "Norwegian");LANGUAGE_MAP.put("ps", "Pashto");LANGUAGE_MAP.put("fa", "Persian");LANGUAGE_MAP.put("pl", "Polish");LANGUAGE_MAP.put("pt", "Portuguese");LANGUAGE_MAP.put("ma", "Punjabi");LANGUAGE_MAP.put("ro", "Romanian");LANGUAGE_MAP.put("ru", "Russian");LANGUAGE_MAP.put("sm", "Samoan");LANGUAGE_MAP.put("gd", "Scots Gaelic");LANGUAGE_MAP.put("sr", "Serbian");LANGUAGE_MAP.put("st", "Sesotho");LANGUAGE_MAP.put("sn", "Shona");LANGUAGE_MAP.put("sd", "Sindhi");LANGUAGE_MAP.put("si", "Sinhala");LANGUAGE_MAP.put("sk", "Slovak");LANGUAGE_MAP.put("sl", "Slovenian");LANGUAGE_MAP.put("so", "Somali");LANGUAGE_MAP.put("es", "Spanish");LANGUAGE_MAP.put("su", "Sundanese");LANGUAGE_MAP.put("sw", "Swahili");LANGUAGE_MAP.put("sv", "Swedish");LANGUAGE_MAP.put("tg", "Tajik");LANGUAGE_MAP.put("ta", "Tamil");LANGUAGE_MAP.put("te", "Telugu");LANGUAGE_MAP.put("th", "Thai");LANGUAGE_MAP.put("tr", "Turkish");LANGUAGE_MAP.put("uk", "Ukrainian");LANGUAGE_MAP.put("ur", "Urdu");LANGUAGE_MAP.put("uz", "Uzbek");LANGUAGE_MAP.put("vi", "Vietnamese");LANGUAGE_MAP.put("cy", "Welsh");LANGUAGE_MAP.put("xh", "Xhosa");LANGUAGE_MAP.put("yi", "Yiddish");LANGUAGE_MAP.put("yo", "Yoruba");LANGUAGE_MAP.put("zu", "Zulu");}/*** 判断语言是否支持** @param language* @return*/public boolean isSupport(String language) {if (null == LANGUAGE_MAP.get(language)) {return false;}return true;}/*** 获取 语言代码* ISO 639-1 code** @param desiredLang 语言* @return 如果返回null则标示不支持*/public String getCode(String desiredLang) {if (null != LANGUAGE_MAP.get(desiredLang)) {return desiredLang;}String tmp = desiredLang.toLowerCase();for (Map.Entry<String, String> enter : LANGUAGE_MAP.entrySet()) {if (enter.getValue().equals(tmp)) {return enter.getKey();}}return null;}/*** 翻译文本** @param text 文本内容* @param sourceLang 文本所属语言。如果不知道，可以使用auto* @param targetLang 目标语言。必须是明确的有效的目标语言* @return* @throws Exception*/public String translateText(String text, String sourceLang, String targetLang) throws Exception {String retStr = "";if (!(isSupport(sourceLang) || isSupport(targetLang))) {throw new Exception("不支持的语言类型");}List<NameValuePair> nvps = new ArrayList();nvps.add(new BasicNameValuePair("client", CLIENT));nvps.add(new BasicNameValuePair("sl", sourceLang));nvps.add(new BasicNameValuePair("tl", targetLang));nvps.add(new BasicNameValuePair("dt", "t"));nvps.add(new BasicNameValuePair("q", text));// String finalPath=PATH +"?client="+CLIENT+"&sl="+sourceLang+"&tl="+targetLang+"&dt=t&q="+ text ;String resp = postHttp(PATH, nvps);if (null == resp) {throw new Exception("网络异常");}// System.out.println( "==>返回内容：" + resp);JSONArray jsonObject = JSONArray.parseArray(resp);for (Iterator<Object> it = jsonObject.getJSONArray(0).iterator(); it.hasNext(); ) {JSONArray a = (JSONArray) it.next();retStr += a.getString(0);}return retStr;}/*** post 请求** @param url 请求地址* @param nvps 参数列表* @return* @throws*/private String postHttp(String url, List<NameValuePair> nvps) {String responseStr = null;CloseableHttpClient httpclient = HttpClients.createDefault();HttpPost httpPost = new HttpPost(url);//重要！！必须设置 http 头，否则返回为乱码httpPost.setHeader("User-Agent", USER_AGENT);CloseableHttpResponse response2 = null;try {// 重要！！ 指定编码，对中文进行编码httpPost.setEntity(new UrlEncodedFormEntity(nvps, Charset.forName("UTF-8")));response2 = httpclient.execute(httpPost);HttpEntity entity2 = response2.getEntity();responseStr = EntityUtils.toString(entity2);EntityUtils.consume(entity2);} catch (IOException e) {e.printStackTrace();} finally {if (null != response2) {try {response2.close();} catch (IOException e) {e.printStackTrace();}}if (null != httpclient) {try {httpclient.close();} catch (IOException e) {e.printStackTrace();}}}return responseStr;}}
5、代码运行测试
package com.lezu.springboot.test.lian;import com.alibaba.excel.EasyExcel;import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;import com.alibaba.fastjson.JSON;import com.lezu.springboot.excel.LanguageReadData;import com.lezu.springboot.excel.LanguageWriteData;import com.lezu.springboot.utils.google.GT;import java.util.ArrayList;import java.util.List;/*** @author LianJiaYu* @date /7/29 9:58*/public class LianTest2 {public static void main(String[] args) throws Exception {String fileName = "C:\\Users\\jiayu.lian\\Desktop\\1.xlsx";List<LanguageWriteData> listData = new ArrayList<>();GT g = GT.getInstance();String targetLang = g.getCode("en");List<LanguageReadData> list = EasyExcel.read(fileName).head(LanguageReadData.class).sheet().doReadSync();for (LanguageReadData readData : list) {//使用谷歌api翻译String chinese = readData.getChinese();String auto = g.translateText(chinese, "auto", targetLang); //auto自动识别语言,targetLang你需要转成的语言//获取翻译后的结果放到LanguageWriteData中LanguageWriteData writeData = new LanguageWriteData();writeData.setChinese(chinese);writeData.setEnglish(auto);listData.add(writeData);}System.out.println(JSON.toJSONString(listData));// 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭EasyExcel.write(fileName, LanguageWriteData.class).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) //自动列宽(不太精确).sheet().doWrite(listData);}}
6、运行结果截图
出现以下这个问题需要关闭你当前打开的1.xlsx
再次运行main方法然后打开1.xlsx
谷歌翻译API调用次数多了之后会返回302错误
建议用下面这个方法(非常的好用)
谷歌excel表格批量翻译各国语言 - 小朱笔记
