使用Excel維護多國語系字串資源檔
5 |
針對多國語系,.NET提供了不錯的解決方案 -- 透過.resx資源檔定義字串,透過ResourceManager或Visual Studio自動產生對應的類別[*.Designer.cs]取用。要新增語系支援,只需增加該語系的resx檔,提供各項目對應的文字,配合CultureInfo切換就能輕易切換語系顯示。(延伸閱讀: 逐步解說:使用資源進行 ASP.NET 的當地語系化)
像是以下這個例子:
這個例子也剛好突顯維護多國語系常見的困擾。Message.resx中有四個項目,Message.zh-CN.resx只有兩則。在開發過程,隨著新介面出現就需要定義新的字串項目,此時得在Message.resx加一筆,在Message.zh-CN.resx也加一筆。支援語系一多,同樣的編輯操作得重複N次(還不包含翻譯工作),擺明了要逼某個暴躁無耐性中年程序員走上絕路...
在我心中,理想的多國語系資料維護模式應該要像這樣:
用Excel來管理,每一個項目的內容依語系並列,一眼就能看出各語系的翻譯對照關係。
正體中文要翻成簡體還可直接用Excel搞定,豈不快哉?
最美妙的部分是 -- 文件是Excel格式,可直接丟給具有Domain Know-how的User翻譯校對,以使用者觀點調校出最適當的用語。
完成結果會自動轉回resx!
很棒吧!
網路上有不少現成的解決方案: ResxManager、resx2xls、RESX to XLS conversion、XHEO RESX Translator... 有些甚至整合了自動翻譯(但實務上還是得經人工潤稿才不會貽笑大方),可見大家都有類似需求。由於我還有進一步整合需求,加上評估程式碼並不難寫,就捲了袖子,花了不到兩小時寫出以下小程式讓美夢成真。(謎: 快承認你根本是忍不住手癢吧!)
程式碼如下,有興趣的朋友請自取。使用時請將boo.resx, boo.en-US.resx, boo.zh-CN.resx等放在同一目錄下,使用ConvResxToExcel(resx所在目錄, "boo")轉出boo.xlsx(Excel格式如先前的圖例),修改後可用ConvExcelToResx(xlsx路徑)再轉回多個resx。
using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel.Design;
using System.IO;
using System.Linq;
using System.Resources;
using System.Text;
using System.Text.RegularExpressions;
using ClosedXML.Excel;
namespace ResxConv
{
class Program
{
static void Main(string[] args)
{
string path = @".";
string pattern = "message";
ConvResxToExcel(path, pattern);
ConvExcelToResx(@"FixedMessage.xlsx");
}
public class ResxStrings
{
public string Key;
public string Comment;
public Dictionary<string, string> Strings =
new Dictionary<string, string>();
}
//REF: http://msdn.microsoft.com/en-us/library/system.resources.resxdatanode.aspx
private static void ConvExcelToResx(string xlsxPath)
{
var list = new List<ResxStrings>();
var langs = new List<string>();
using (XLWorkbook wb = new XLWorkbook(xlsxPath))
{
var sht = wb.Worksheets.First();
int col = 3;
while (!sht.Cell(1, col).IsEmpty())
{
langs.Add(sht.Cell(1, col).Value.ToString());
col++;
}
int row = 2;
while (!sht.Cell(row, 1).IsEmpty())
{
ResxStrings data = new ResxStrings()
{
Key = sht.Cell(row, 1).Value.ToString(),
Comment = sht.Cell(row, 2).Value.ToString()
};
for (int i = 0; i < langs.Count; i++)
data.Strings.Add(langs[i],
sht.Cell(row, i + 3).Value.ToString());
list.Add(data);
row++;
}
}
//Gen resx
string path = Path.GetDirectoryName(xlsxPath);
string pattern = Path.GetFileNameWithoutExtension(xlsxPath);
foreach (string lang in langs)
{
string resxPath = Path.Combine(path,
pattern + (lang != "DEFAULT" ? "." + lang : string.Empty) + ".resx");
using (ResXResourceWriter rsxw = new ResXResourceWriter(resxPath))
{
foreach (var data in list)
{
ResXDataNode node = new ResXDataNode(data.Key, data.Strings[lang]);
node.Comment = data.Comment;
rsxw.AddResource(node);
}
rsxw.Generate();
rsxw.Close();
}
}
}
private static void ConvResxToExcel(string path, string pattern)
{
OrderedDictionary dict = new OrderedDictionary();
List<string> langs = new List<string>();
foreach (string file in Directory.GetFiles(path, pattern + ".*resx"))
{
string lang = "DEFAULT";
var m = Regex.Match(file, pattern + "[.](?<l>.+)[.]resx", RegexOptions.IgnoreCase);
if (m.Success) lang = m.Groups["l"].Value;
langs.Add(lang);
using (ResXResourceReader rsxr = new ResXResourceReader(file))
{
rsxr.UseResXDataNodes = true;
ResourceSet rs = new ResourceSet(rsxr);
foreach (DictionaryEntry entry in rs)
{
string key = (string)entry.Key;
var node = (ResXDataNode)entry.Value;
if (!dict.Contains(key))
{
dict.Add(key,
new ResxStrings() { Key = key, Comment = node.Comment });
}
var data = (ResxStrings)dict[key];
string value = (string)node.GetValue((ITypeResolutionService)null);
data.Strings.Add(lang, value);
}
}
}
//Export to Excel
using (XLWorkbook wb = new XLWorkbook())
{
//Excel
var sht = wb.Worksheets.Add("Resx List");
sht.Cell(1, 1).Value = "Key";
sht.Cell(1, 2).Value = "Comment";
for (int i = 0; i < langs.Count; i++)
sht.Cell(1, i + 3).Value = langs[i];
//HTML
StringBuilder sb = new StringBuilder();
sb.AppendFormat(@"
<html>
<head>
<title>{0} RESX</title>
<style>
table {{ border-collapse:collapse; }}
td,th {{ border: 1px solid gray; padding: 6px; font-size: 9pt; }}
</style>
</head>
", pattern);
sb.Append("<body><table><tr><th>Key</th><th>Comment</th>");
foreach (string lang in langs)
sb.AppendFormat("<th>{0}</th>", lang);
sb.AppendLine("</tr>");
int row = 2;
foreach (DictionaryEntry de in dict)
{
//Excel
sht.Cell(row, 1).Value = de.Key;
var data = (ResxStrings)de.Value;
sht.Cell(row, 2).Value = data.Comment;
for (int i = 0; i < langs.Count; i++)
{
string lang = langs[i];
if (data.Strings.ContainsKey(lang))
sht.Cell(row, i + 3).Value = data.Strings[lang];
}
row++;
//HTML
sb.AppendFormat("<tr><td>{0}</td>", de.Key);
sb.AppendFormat("<td>{0}</td>", data.Comment);
for (int i = 0; i < langs.Count; i++)
{
string lang = langs[i];
sb.AppendFormat("<td>{0}</td>",
data.Strings.ContainsKey(lang) ? data.Strings[lang] : string.Empty);
}
sb.AppendLine("</tr>");
}
//Excel
sht.Column(1).AdjustToContents();
sht.Column(1).Width += 2;
wb.SaveAs(Path.Combine(path, pattern + ".xlsx"));
//HTML
sb.AppendLine("</table></html>");
File.WriteAllText(Path.Combine(path, pattern + ".html"), sb.ToString());
}
}
}
}
Comments
# by 大熊
請問有Vb.net的WinForm版本嗎?
# by 大熊
請問有Vb.net的WinForm版本嗎?
# by Jeffrey
to 大熊,如需要VB.NET可考慮使用線上轉換工具 http://converter.telerik.com/。 (衷心建議考慮要不要改學 C#,VB.NET 這條路再走下去,覺得寂寞空虛覺得冷的可能性很高… :P)
# by woic
想詢問 已經引用using System.Resources; 但還是找不到這個ResXResourceWriter 類別耶 使用c# / 開vs 2015 / 專案是.net 4.6.1
# by Jeffrey
to woic, 專案需參照 System.Windows.Forms.dll,ResXResourceWriter 是其內含型別(雖然 Namespace 是 System.Resources)。