|
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using OfficeOpenXml;
using Org.BouncyCastle.Asn1.Cmp;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Net;
using System.Security.Cryptography;
using System.Text;
using System.Text.Json;
using System.Threading;
using System.Web.Script.Serialization;
using System.Windows.Forms;
using static System.Windows.Forms.VisualStyles.VisualStyleElement;
namespace AliyunTranslator40
{
public partial class MainForm : Form
{
private string host = "", port = "3306", database = "", user = "", password = "";
private string AccessKeyId = "", AccessKeySecret = "";
private const string ConfigFile = "config.json";
private const string CacheFile = "cache.json";
private string ConnStr = "";
private volatile bool stopRequested = false;
private Dictionary<string, string> cache = new Dictionary<string, string>();
private int total = 0, done = 0;
private Semaphore sema = new Semaphore(4, 4);
private Stopwatch sw = new Stopwatch();
public MainForm()
{
InitializeComponent();
LoadConfig();
}
#region 配置管理
private void btnSaveDb_Click(object sender, EventArgs e)
{
host = txtHost.Text.Trim();
port = txtPort.Text.Trim();
database = txtDb.Text.Trim();
user = txtUser.Text.Trim();
password = txtPwd.Text.Trim();
BuildConnStr();
SaveConfig();
Log("✅ 数据库配置已保存。");
}
private void btnSaveKey_Click(object sender, EventArgs e)
{
AccessKeyId = txtKeyId.Text.Trim();
AccessKeySecret = txtKeySecret.Text.Trim();
SaveConfig();
Log("✅ 阿里云密钥已保存。");
}
private void BuildConnStr()
{
ConnStr = $"Server={host};Port={port};Database={database};Uid={user};Pwd={password};Charset=utf8mb4;SslMode=None;";
}
private void LoadConfig()
{
if (!File.Exists(ConfigFile)) return;
var js = new JavaScriptSerializer();
var cfg = js.Deserialize<Dictionary<string, string>>(File.ReadAllText(ConfigFile));
if (cfg == null) return;
host = cfg.ContainsKey("Host") ? cfg["Host"] : "";
port = cfg.ContainsKey("Port") ? cfg["Port"] : "3306";
database = cfg.ContainsKey("Database") ? cfg["Database"] : "";
user = cfg.ContainsKey("User") ? cfg["User"] : "";
password = cfg.ContainsKey("Password") ? cfg["Password"] : "";
AccessKeyId = cfg.ContainsKey("AccessKeyId") ? cfg["AccessKeyId"] : "";
AccessKeySecret = cfg.ContainsKey("AccessKeySecret") ? cfg["AccessKeySecret"] : "";
txtHost.Text = host;
txtPort.Text = port;
txtDb.Text = database;
txtUser.Text = user;
txtPwd.Text = password;
txtKeyId.Text = AccessKeyId;
txtKeySecret.Text = AccessKeySecret;
BuildConnStr();
Log("✅ 已加载配置。");
}
private void SaveConfig()
{
var js = new JavaScriptSerializer();
var cfg = new Dictionary<string, string>
{
{"Host", host}, {"Port", port}, {"Database", database},
{"User", user}, {"Password", password},
{"AccessKeyId", AccessKeyId}, {"AccessKeySecret", AccessKeySecret}
};
File.WriteAllText(ConfigFile, js.Serialize(cfg));
}
#endregion
#region 翻译流程
private void btnStart_Click(object sender, EventArgs e)
{
stopRequested = false;
ThreadPool.QueueUserWorkItem(_ => RunTranslate());
}
private void btnStop_Click(object sender, EventArgs e)
{
stopRequested = true;
Log("🛑 已请求停止。");
}
private void RunTranslate()
{
try
{
LoadCache();
List<LangRecord> list = new List<LangRecord>();
UpdateStatus("正在读取数据...");
using (MySqlConnection conn = new MySqlConnection(ConnStr))
{
conn.Open();
var cmd = new MySqlCommand("SELECT table_name, table_id, field_name, field_value FROM sys_language WHERE language='zh-CN';", conn);
var reader = cmd.ExecuteReader();
while (reader.Read())
{
list.Add(new LangRecord
{
TableName = reader["table_name"].ToString(),
TableId = Convert.ToInt64(reader["table_id"]),
FieldName = reader["field_name"].ToString(),
FieldValue = reader["field_value"].ToString()
});
}
reader.Close();
}
total = list.Count;
done = 0;
sw.Restart();
UpdateStatus($"共 {total} 条数据，开始翻译...");
foreach (var rec in list)
{
if (stopRequested) break;
sema.WaitOne();
ThreadPool.QueueUserWorkItem(state => ProcessRecord(rec));
}
while (done < total && !stopRequested)
Thread.Sleep(500);
SaveCache();
ExportToExcel();
UpdateStatus("✅ 翻译完成。");
}
catch (Exception ex)
{
Log("❌ 翻译错误：" + ex.Message);
}
}
private void ProcessRecord(LangRecord rec)
{
try
{
if (stopRequested) return;
string cn = rec.FieldValue.Trim();
if (chkEnUs.Checked && !HasTranslation(rec, "en-US"))
SaveTranslation(rec, "en-US", TranslateAliyun(cn, "zh", "en"));
if (chkZhTw.Checked && !HasTranslation(rec, "zh-TW"))
SaveTranslation(rec, "zh-TW", TranslateAliyun(cn, "zh", "zh-tw"));
if (chkJaJp.Checked && !HasTranslation(rec, "ja-JP"))
SaveTranslation(rec, "ja-JP", TranslateAliyun(cn, "zh", "ja"));
}
catch (Exception ex)
{
Log("⚠️ 单条失败: " + ex.Message);
}
finally
{
Interlocked.Increment(ref done);
UpdateProgress();
sema.Release();
}
}
#endregion
#region 阿里云签名翻译
private string TranslateAliyun(string text, string from, string to)
{
if (string.IsNullOrWhiteSpace(text))
return text;
if (string.IsNullOrWhiteSpace(AccessKeyId) || string.IsNullOrWhiteSpace(AccessKeySecret))
{
Log("⚠️ 阿里云AccessKey未配置");
return text;
}
try
{
string endpoint = "https://mt.aliyuncs.com";
string version = "2018-10-12";
string action = "TranslateGeneral";
// 构建参数（按字母顺序排序，这是阿里云的要求）
var parameters = new SortedDictionary<string, string>(StringComparer.Ordinal)
{
["AccessKeyId"] = AccessKeyId,
["Action"] = action,
["Format"] = "JSON",
["FormatType"] = "text",
["Scene"] = "general",
["SignatureMethod"] = "HMAC-SHA1",
["SignatureNonce"] = Guid.NewGuid().ToString(),
["SignatureVersion"] = "1.0",
["SourceLanguage"] = from,
["SourceText"] = text,
["TargetLanguage"] = to,
["Timestamp"] = DateTime.UtcNow.ToString("yyyy-MM-ddTHH:mm:ssZ"),
["Version"] = version
};
// 构建查询字符串
string queryString = BuildQueryString(parameters);
// 计算签名
string signature = CalculateSignature("GET", parameters);
// 构建最终URL
string finalUrl = $"{endpoint}/?{queryString}&Signature={Uri.EscapeDataString(signature)}";
return ExecuteTranslationRequest(finalUrl, text, from, to);
}
catch (Exception ex)
{
Log($"⚠️ 翻译异常: {ex.Message}");
return text;
}
}
/// <summary>
/// 构建查询字符串（URL编码）
/// </summary>
private string BuildQueryString(SortedDictionary<string, string> parameters)
{
var encodedParams = parameters.Select(p =>
$"{Uri.EscapeDataString(p.Key)}={Uri.EscapeDataString(p.Value)}");
return string.Join("&", encodedParams);
}
/// <summary>
/// 计算阿里云签名
/// </summary>
private string CalculateSignature(string method, SortedDictionary<string, string> parameters)
{
// 1. 构建规范化查询字符串
string canonicalizedQueryString = BuildQueryString(parameters);
// 2. 构建待签名字符串
string stringToSign = $"{method}&{Uri.EscapeDataString("/")}&{Uri.EscapeDataString(canonicalizedQueryString)}";
// 3. 计算HMAC-SHA1签名
string key = $"{AccessKeySecret}&";
using (var hmac = new HMACSHA1(Encoding.UTF8.GetBytes(key)))
{
byte[] hash = hmac.ComputeHash(Encoding.UTF8.GetBytes(stringToSign));
return Convert.ToBase64String(hash);
}
}
/// <summary>
/// 执行翻译请求并解析结果
/// </summary>
private string ExecuteTranslationRequest(string url, string originalText, string from, string to)
{
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
request.Method = "GET";
request.Timeout = 15000; // 15秒超时
request.UserAgent = "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36";
using (HttpWebResponse response = (HttpWebResponse)request.GetResponse())
using (Stream stream = response.GetResponseStream())
using (StreamReader reader = new StreamReader(stream, Encoding.UTF8))
{
string responseText = reader.ReadToEnd();
if (response.StatusCode != HttpStatusCode.OK)
{
Log($"⚠️ API返回错误状态码: {(int)response.StatusCode}");
return originalText;
}
return ParseTranslationResult(responseText, originalText, from, to, JsonDocument.Parse(responseText));
}
}
/// <summary>
/// 解析翻译结果
/// </summary>
private string ParseTranslationResult(string jsonResponse, string originalText, string from, string to, JsonDocument doc)
{
try
{
JsonElement root = doc.RootElement;
// 检查是否有错误
if (root.TryGetProperty("Code", out JsonElement codeElement))
{
string errorCode = codeElement.GetString();
if (!string.IsNullOrEmpty(errorCode) && errorCode != "200")
{
string errorMessage = root.TryGetProperty("Message", out JsonElement messageElement)
? messageElement.GetString()
: "未知错误";
Log($"⚠️ 翻译API错误: {errorCode} - {errorMessage}");
return originalText;
}
}
// 提取翻译结果
if (root.TryGetProperty("Data", out JsonElement dataElement) &&
dataElement.TryGetProperty("Translated", out JsonElement translatedElement))
{
string translatedText = translatedElement.GetString();
if (!string.IsNullOrEmpty(translatedText))
{
Log($"🌍 [{from}->{to}] {originalText} => {translatedText}");
return translatedText;
}
}
Log($"⚠️ 无法解析翻译结果: {jsonResponse}");
return originalText;
}
catch (Newtonsoft.Json.JsonException ex)
{
Log($"⚠️ JSON解析失败: {ex.Message}");
return originalText;
}
}
#endregion
#region 数据保存与导出
private void SaveTranslation(LangRecord rec, string lang, string val)
{
using (MySqlConnection conn = new MySqlConnection(ConnStr))
{
conn.Open();
string sql = "INSERT INTO sys_language (table_name, table_id, field_name, language, field_value) VALUES (@t,@id,@f,@lang,@val) ON DUPLICATE KEY UPDATE field_value=VALUES(field_value)";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@t", rec.TableName);
cmd.Parameters.AddWithValue("@id", rec.TableId);
cmd.Parameters.AddWithValue("@f", rec.FieldName);
cmd.Parameters.AddWithValue("@lang", lang);
cmd.Parameters.AddWithValue("@val", val);
cmd.ExecuteNonQuery();
}
}
private void btnTestDb_Click(object sender, EventArgs e)
{
try
{
// 获取输入
host = txtHost.Text.Trim();
port = txtPort.Text.Trim();
database = txtDb.Text.Trim();
user = txtUser.Text.Trim();
password = txtPwd.Text.Trim();
BuildConnStr();
// 检查输入有效性
if (string.IsNullOrEmpty(host) || string.IsNullOrEmpty(database) || string.IsNullOrEmpty(user))
{
MessageBox.Show("请输入完整的数据库连接信息（服务器、数据库、用户名）！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
// 显示测试中状态
lblStatus.Text = "状态: 正在连接数据库...";
lblStatus.Refresh();
DateTime start = DateTime.Now;
using (var conn = new MySqlConnection(ConnStr))
{
conn.Open();
MySqlCommand cmd = new MySqlCommand("SELECT VERSION();", conn);
string version = Convert.ToString(cmd.ExecuteScalar());
TimeSpan elapsed = DateTime.Now - start;
MessageBox.Show(
$"✅ 数据库连接成功！\n\n服务器: {host}\n数据库: {database}\n版本: {version}\n耗时: {elapsed.TotalMilliseconds:F0} ms",
"连接成功",
MessageBoxButtons.OK,
MessageBoxIcon.Information
);
Log($"✅ 成功连接数据库 [{database}] (版本: {version})，耗时 {elapsed.TotalMilliseconds:F0} ms");
lblStatus.Text = "状态: 数据库连接成功 ✅";
}
}
catch (MySqlException ex)
{
string msg;
switch (ex.Number)
{
case 1045: msg = "用户名或密码错误"; break;
case 1042: msg = "无法连接到指定主机"; break;
case 1049: msg = "数据库不存在"; break;
default: msg = "MySQL 错误: " + ex.Message; break;
}
MessageBox.Show($"❌ 连接失败: {msg}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
Log("❌ 连接失败: " + msg);
lblStatus.Text = "状态: 连接失败 ❌";
}
catch (Exception ex)
{
MessageBox.Show($"❌ 未知错误: {ex.Message}", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
Log("❌ 未知错误: " + ex.Message);
lblStatus.Text = "状态: 连接异常 ❌";
}
}
private void btnExport_Click(object sender, EventArgs e)
{
ExportToExcel();
}
private void ExportToExcel()
{
try
{
UpdateStatus("正在准备导出 Excel...");
// 1️⃣ 弹出保存文件对话框
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "选择导出路径";
sfd.Filter = "Excel 文件 (*.xlsx)|*.xlsx";
sfd.FileName = $"translate_result_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx";
sfd.InitialDirectory = AppDomain.CurrentDomain.BaseDirectory;
if (sfd.ShowDialog() != DialogResult.OK)
{
Log("⚠️ 用户取消导出。");
UpdateStatus("已取消导出");
return;
}
string file = sfd.FileName;
Log("📁 导出路径：" + file);
UpdateStatus("正在导出 Excel，请稍候...");
// 2️⃣ 创建 Excel
using (var pkg = new OfficeOpenXml.ExcelPackage())
{
var ws = pkg.Workbook.Worksheets.Add("Translations");
// 表头
string[] headers = { "表名", "主键ID", "字段名", "中文(zh-CN)", "繁体(zh-TW)", "英文(en-US)", "日文(ja-JP)" };
for (int i = 0; i < headers.Length; i++)
{
ws.Cells[1, i + 1].Value = headers[i];
ws.Cells[1, i + 1].Style.Font.Bold = true;
ws.Cells[1, i + 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ws.Cells[1, i + 1].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray);
ws.Cells[1, i + 1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
}
// 3️⃣ 查询数据并写入
using (MySqlConnection conn = new MySqlConnection(ConnStr))
{
conn.Open();
string sql = @"
SELECT
table_name,
table_id,
field_name,
MAX(CASE WHEN language='zh-CN' THEN field_value END) AS zhCN,
MAX(CASE WHEN language='zh-TW' THEN field_value END) AS zhTW,
MAX(CASE WHEN language='en-US' THEN field_value END) AS enUS,
MAX(CASE WHEN language='ja-JP' THEN field_value END) AS jaJP
FROM sys_language
GROUP BY table_name, table_id, field_name
ORDER BY table_name, table_id, field_name;";
MySqlCommand cmd = new MySqlCommand(sql, conn);
var reader = cmd.ExecuteReader();
int row = 2;
while (reader.Read())
{
string tableName = Convert.ToString(reader["table_name"]);
string tableId = Convert.ToString(reader["table_id"]); // ⚠️ BigInt → string
string fieldName = Convert.ToString(reader["field_name"]);
ws.Cells[row, 1].Value = tableName;
ws.Cells[row, 2].Value = tableId;
ws.Cells[row, 3].Value = fieldName;
ws.Cells[row, 4].Value = Convert.ToString(reader["zhCN"]);
ws.Cells[row, 5].Value = Convert.ToString(reader["zhTW"]);
ws.Cells[row, 6].Value = Convert.ToString(reader["enUS"]);
ws.Cells[row, 7].Value = Convert.ToString(reader["jaJP"]);
// 设置 ID 列为文本格式（防止科学计数法）
ws.Cells[row, 2].Style.Numberformat.Format = "@";
row++;
}
reader.Close();
}
// 4️⃣ 自动列宽 + 边框
ws.Cells.AutoFitColumns();
var range = ws.Cells[1, 1, ws.Dimension.End.Row, ws.Dimension.End.Column];
range.Style.Border.Top.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
range.Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
range.Style.Border.Right.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
range.Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
// 5️⃣ 保存文件
pkg.SaveAs(new FileInfo(file));
}
Log("✅ Excel 导出完成：" + file);
UpdateStatus("✅ Excel 导出完成");
MessageBox.Show($"✅ 导出成功！\n文件已保存至：\n{file}", "导出成功", MessageBoxButtons.OK, MessageBoxIcon.Information);
// 6️⃣ 询问是否打开文件夹
if (MessageBox.Show("是否打开文件所在文件夹？", "完成", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
try
{
System.Diagnostics.Process.Start("explorer.exe", "/select,\"" + file + "\"");
}
catch { }
}
}
catch (Exception ex)
{
Log("❌ 导出失败: " + ex.Message);
MessageBox.Show("❌ 导出失败: " + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
UpdateStatus("❌ 导出失败");
}
}
#endregion
#region UI 控制
private void UpdateProgress()
{
if (InvokeRequired) { Invoke(new Action(UpdateProgress)); return; }
double pct = total > 0 ? done * 100.0 / total : 0;
progressBar.Value = (int)Math.Min(100, pct);
lblStatus.Text = $"进度: {pct:F1}% ({done}/{total})";
}
private void UpdateStatus(string msg)
{
if (InvokeRequired) { Invoke(new Action<string>(UpdateStatus), msg); return; }
lblStatus.Text = "状态: " + msg;
}
private void Log(string msg)
{
if (InvokeRequired) { Invoke(new Action<string>(Log), msg); return; }
txtLog.AppendText($"[{DateTime.Now:HH:mm:ss}] {msg}\r\n");
}
private void LoadCache()
{
if (File.Exists(CacheFile))
{
var js = new JavaScriptSerializer();
cache = js.Deserialize<Dictionary<string, string>>(File.ReadAllText(CacheFile));
if (cache == null) cache = new Dictionary<string, string>();
Log($"🧠 加载缓存：{cache.Count} 条");
}
}
private void SaveCache()
{
var js = new JavaScriptSerializer();
File.WriteAllText(CacheFile, js.Serialize(cache));
Log($"💾 保存缓存：{cache.Count} 条");
}
#endregion
private bool HasTranslation(LangRecord rec, string lang)
{
try
{
using (MySqlConnection conn = new MySqlConnection(ConnStr))
{
conn.Open();
string sql = "SELECT COUNT(*) FROM sys_language WHERE table_name=@t AND table_id=@id AND field_name=@f AND language=@lang AND field_value IS NOT NULL AND field_value <> '';";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@t", rec.TableName);
cmd.Parameters.AddWithValue("@id", rec.TableId);
cmd.Parameters.AddWithValue("@f", rec.FieldName);
cmd.Parameters.AddWithValue("@lang", lang);
object count = cmd.ExecuteScalar();
return Convert.ToInt32(count) > 0;
}
}
catch (Exception ex)
{
Log("⚠️ 检查翻译状态出错：" + ex.Message);
return false;
}
}
}
public class LangRecord
{
public string TableName;
public long TableId;
public string FieldName;
public string FieldValue;
}
}
|
33
1
已为社区贡献2条内容
所有评论(0)