在项目中，需要从Excel表中提取翻译，使用工具的话相比手动复制粘贴快太多了，工作量从小时计算缩小到以秒计算，简直不要太方便。
在使用ClosedXml前需要先下载ClosedXml的库。在VS中从里"工具"--->“NuGet包管理器”--->“管理解决方案的NuGet程序包”，然后在浏览标签下输入ClosedXml，然后下载。下载好后，相应的文件会出现在工程的Packages下，从Packages文件夹下找到“ClosedXML.dll”，“DocumentFormat.OpenXml.dll”，“ExcelNumberFormat.dll”这三个程序集，然后放到Plugins目录下（Packages下相应的文件夹可以删除了）。下载后库后下面开始从Excel提取内容写入Xml中。
1、从Excel表中把内容提取出来
using (XLWorkbook wb = new XLWorkbook(excelPath))
{
//Excel表中的所有工作表
IXLWorksheets worksheets = wb.Worksheets;
int index = 0;
foreach (var worksheet in worksheets)
{
//给工作表规定一个顺序，以免混乱
if (index >= workSheetNames.Length || !worksheet.Name.Equals(workSheetNames[index]))
{
Debug.LogError("Excel表顺序不对!!");
break;
}
index++;
col1StrList = new List<string>();
col2StrList = new List<string>();
//第一列中文
col1StrList = ReadColStrList(worksheet, 1);
col1StrLists.Add(col1StrList);
//第二列英文
col2StrList = ReadColStrList(worksheet, 2);
col2StrLists.Add(col2StrList);
}
}2、从工作表中按行、列读取内容
private static List<string> ReadColStrList(IXLWorksheet worksheet, int column)
{
if (worksheet == null)
{
Debug.LogError("工作表为空！！");
return null;
}
List<string> strs = new List<string>();
if (selectIndexs.Length > 0)
{
for (int i = 0; i < selectIndexs.Length; i++)
{
var cell = worksheet.Cell(selectIndexs[i], column);
strs.Add(cell.Value.ToString());
}
}
else
{
for (int i = startIndex; i <= endIndex; i++)
{
//取某一列从起始行到结束行的内容，如果是用worksheet.CellsUsed();
//或者worksheet.Cells();就会跳过空行
var cell = worksheet.Cell(i, column);
strs.Add(cell.Value.ToString());
}
}
return strs;
}3、通过上面的步骤就把文档中所有工作表的相关内容内容取出来了，然后开始写入XML中。这里用System.Xml.Linq库来读写XML。
private static void WriteXml()
{
for (int i = 0; i < col1StrLists.Count; i++)
{
if (i >= xmlNames.Length) break;
XElement xmlDoc = ReadXML(i);
//写一下模块的注释
WriteComment(xmlDoc, modelTitle);
for (int j = 0; j < Enum.GetNames(typeof(Keys)).Length; j++)
{
if (j >= col1StrLists[i].Count) break;
WriteString(xmlDoc, Enum.GetNames(typeof(Keys))[j], col2StrLists[i][j], col1StrLists[i][j], xmlNames[i]);
}
//结束时再写一下模块的注释
WriteComment(xmlDoc, modelTitle);
}
}4、写注释的方法，就是这个格式的“<!---->”
private static void WriteComment(XElement xmlDoc, string value)
{
XComment xComment = new XComment(value);
xmlDoc.Add(xComment);
xmlDoc.Save(xmlPath);
}5、写内容的方法
private static void WriteString(XElement xmlDoc, string key, string value, string comment, string xmlName)
{
//先判断一下有没有已经存在的Key
if (localizaleData.ContainsKey(key))
{
Debug.LogError(xmlName + " contains this key:" + key);
return;
}
WriteComment(xmlDoc, comment);
XElement xe = new XElement("string");
xe.SetAttributeValue("name", key);
//这里把翻译中的占位符换成{0}的形式
for(int i = 0; i < replaceFlags.Length; i++)
{
if(value.IndexOf(replaceFlags[i]) >= 0)
value = value.Replace(replaceFlags[i], "{" + i + "}");
}
if (string.IsNullOrEmpty(value))
Debug.LogError("内容为空---" + xmlName + "--" + key);
xe.SetValue(value);
xmlDoc.Add(xe);
xmlDoc.Save(xmlPath);
}6、下面是全部的代码了，这里做测试的只建了一个工作表，实际是支持N个工作表的。另外这个脚本是放在Editor文件夹下的。
public class ExcelToXml
{
//模块名称
static string modelTitle = "模块名称";
enum Keys
{
key1,
key2,
key3
}
static int startIndex = 2;//起始行序号
static int endIndex = 4;//结束行序号
static int[] selectIndexs = new int[] { };//不连续行的翻译(设置了这个参数就不要设置起始结束行了)
static string excelPath = @"E:\Test.xlsx";//Excel表路径及文件名+后缀（只支持.xlsx）
static string[] xmlNames = new string[] { "English"};
static string[] workSheetNames = new string[] {"英语" };
static string[] replaceFlags = new string[] { "AA", "BB", "CC", "DD", "EE"};
static Dictionary<string, string> localizaleData = new Dictionary<string, string>();
static string xmlPath;
static List<string> col1StrList;
static List<string> col2StrList;
static List<List<string>> col1StrLists = new List<List<string>>();
static List<List<string>> col2StrLists = new List<List<string>>();
[MenuItem("Assets/ExcelToXml(关闭文档再执行)")]
static void ExcelToXmlTool()
{
ReadWorkSheet();
}
private static void ReadWorkSheet()
{
if (!ValidCheck()) return;
using (XLWorkbook wb = new XLWorkbook(excelPath))
{
IXLWorksheets worksheets = wb.Worksheets;
int index = 0;
foreach (var worksheet in worksheets)
{
if (index >= workSheetNames.Length || !worksheet.Name.Equals(workSheetNames[index]))
{
Debug.LogError("Excel表顺序不对，worksheet:" + worksheet.Name + ",workSheetNames:" + workSheetNames[index]");
break;
}
index++;
col1StrList = new List<string>();
col2StrList = new List<string>();
//第一列中文
col1StrList = ReadColStrList(worksheet, 1);
col1StrLists.Add(col1StrList);
//第二列英文
col2StrList = ReadColStrList(worksheet, 2);
col2StrLists.Add(col2StrList);
}
}
WriteXml();
Debug.LogError("Excel To Xml Complete!!");
}
private static bool ValidCheck()
{
bool isValid = true;
if (Enum.GetNames(typeof(Keys)).Length == 0)
{
Debug.LogError("请输入Key！！");
return isValid = false;
}
if (string.IsNullOrEmpty(excelPath))
{
Debug.LogError("请输入Excel表路径！！");
return isValid = false;
}
if (!File.Exists(excelPath))
{
Debug.LogError("Excel表不存在！！");
return isValid = false;
}
if (!Path.GetExtension(excelPath).Equals(".xlsx"))
{
Debug.LogError("Excel表类型不是xlsx！！");
return isValid = false;
}
if (startIndex > endIndex && selectIndexs.Length == 0)
{
Log.PrintLog("Excel表结束行序号小于开始行");
return isValid = false;
}
return isValid;
}
private static List<string> ReadColStrList(IXLWorksheet worksheet, int column)
{
if (worksheet == null)
{
Debug.LogError("工作表为空！！");
return null;
}
List<string> strs = new List<string>();
if (selectIndexs.Length > 0)
{
for (int i = 0; i < selectIndexs.Length; i++)
{
var cell = worksheet.Cell(selectIndexs[i], column);
strs.Add(cell.Value.ToString());
}
}
else
{
for (int i = startIndex; i <= endIndex; i++)
{
var cell = worksheet.Cell(i, column);
strs.Add(cell.Value.ToString());
}
}
return strs;
}
private static XElement ReadXML(int index)
{
localizaleData.Clear();
XElement xmlDoc;
string fileName = "strings_" + xmlNames[index] + ".xml";
xmlPath = Application.dataPath + "/Localization" + "/" + fileName;
string text = File.ReadAllText(xmlPath);
xmlDoc = XElement.Parse(text);
IEnumerable<XElement> elements =
from el in xmlDoc.Elements()
select el;
foreach (XElement el in elements)
{
XAttribute xat = el.FirstAttribute;
localizaleData.Add(xat.Value, el.Value);
}
return xmlDoc;
}
private static void WriteXml()
{
for (int i = 0; i < col1StrLists.Count; i++)
{
if (i >= xmlNames.Length) break;
XElement xmlDoc = ReadXML(i);
WriteComment(xmlDoc, modelTitle);
for (int j = 0; j < Enum.GetNames(typeof(Keys)).Length; j++)
{
if (j >= col1StrLists[i].Count) break;
WriteString(xmlDoc, Enum.GetNames(typeof(Keys))[j], col2StrLists[i][j], col1StrLists[i][j], xmlNames[i]);
}
WriteComment(xmlDoc, modelTitle);
}
}
private static void WriteString(XElement xmlDoc, string key, string value, string comment, string xmlName)
{
if (localizaleData.ContainsKey(key))
{
Debug.LogError(xmlName + " contains this key:" + key);
return;
}
WriteComment(xmlDoc, comment);
XElement xe = new XElement("string");
xe.SetAttributeValue("name", key);
for(int i = 0; i < replaceFlags.Length; i++)
{
if(value.IndexOf(replaceFlags[i]) >= 0)
value = value.Replace(replaceFlags[i], "{" + i + "}");
}
if (string.IsNullOrEmpty(value))
Debug.LogError("内容为空---" + xmlName + "--" + key);
xe.SetValue(value);
xmlDoc.Add(xe);
xmlDoc.Save(xmlPath);
}
private static void WriteComment(XElement xmlDoc, string value)
{
XComment xComment = new XComment(value);
xmlDoc.Add(xComment);
xmlDoc.Save(xmlPath);
}
}7、内容取出来后XML中大概是这个样子的。转换之前要把Excel表关闭，不然后报错“IOException: Sharing violation on path E:\Test.xlsx”
<?xml version="1.0" encoding="utf-8"?>
<resources>
<!--模块名称-->
<!--测试1-->
<string name="key1">Test1</string>
<!--测试2-->
<string name="key2">Test2</string>
<!--测试3-->
<string name="key3">Test3 {0} Test3</string>
<!--模块名称-->
</resources>有个这样的工具太方便了，取翻译几秒钟就搞定了。当然前提是文档是按约定的格式做的，比如工作表的顺序，内容的格式等，如果格式不对的话，取出来的内容就会混乱了。
还有一点需要注意，XML经过代码读写后，XML里面的转义符如（&(逻辑与) &，<(小于) <）等会被替换成原符号，所以操作完后只上传新增加的部分。也可以使用<![CDATA[......]]>域，将<resources>中的内容括起来，这样里面的转义符就不会被修改了。
附带添加CDATA的方法，在开始新增XML时添加CDATA，新增完成后再把CDATA删除。
static List<string> xmlList = new List<string>();
private static void XmlCdata(string xmlPath, bool isRemove = false)
{
xmlList.Clear();
StreamReader sr = new StreamReader(xmlPath);
while (!sr.EndOfStream)
{
xmlList.Add(sr.ReadLine() + "\n");
}
sr.Dispose();
sr.Close();
for (int i = 0; i < xmlList.Count - 1; i++)
{
if (!isRemove)
{
if (xmlList[i].Contains("resources"))
{
xmlList[i] += "<![CDATA[\n";
break;
}
}
else
{
if (xmlList[i].Contains("<![CDATA["))
{
xmlList[i] = "<resources>\n";
break;
}
}
}
for (int i = xmlList.Count - 1; i > 0; i--)
{
if (!isRemove)
{
if (xmlList[i].Contains("resources"))
{
xmlList[i - 1] += "]]>\n";
break;
}
}else
{
if (xmlList[i].Contains("]]>"))
{
xmlList[i] = "\n";
break;
}
}
}
//写入文本
StreamWriter sw = new StreamWriter(xmlPath, false);
foreach (var data in xmlList)
{
sw.Write(data);
}
sw.Flush();
sw.Close();
}新增创建一个XML的方法
[MenuItem("Assets/CreateXmlFile(关闭文档再执行)")]
static void CreateXmlFile()
{
if (!ValidCheck()) return;
if (!ReadWorkbook()) return;
string filePath = Application.dataPath + "/Test.xml";
if (File.Exists(filePath))
{
Debug.LogError("File Exists!!");
return;
}
XDocument xDoc = new XDocument();
XElement root = new XElement("resources");
for (int i = 0; i < col1StrLists.Count; i++)
{
if (i >= xmlNames.Length) break;
XElement subRoot = new XElement(xmlNames[i]);
for (int j = 0; j < Enum.GetNames(typeof(Keys)).Length; j++)
{
if (j >= col1StrLists[i].Count) break;
XComment xComment = new XComment(col1StrLists[i][j]);
subRoot.Add(xComment);
XElement xElement = new XElement("string");
xElement.SetAttributeValue("name", Enum.GetNames(typeof(Keys))[j]);
for (int k = 0; k < replaceFlags.Length; k++)
{
if (col3StrLists[i][j].IndexOf(replaceFlags[k]) >= 0)
col3StrLists[i][j] = col3StrLists[i][j].Replace(replaceFlags[k], "{" + k + "}");
}
xElement.SetValue(col3StrLists[i][j]);
subRoot.Add(xElement);
}
root.Add(subRoot);
}
xDoc.Add(root);
xDoc.Save(filePath);
AssetDatabase.Refresh();
}新增在指定位置插入内容的方法，针对不同的XML结构需要使用不同的方式
<?xml version="1.0" encoding="utf-8"?>
<resources>
<!--Key-->
<string name="Key">Key</string>
</resources> [MenuItem("Assets/插入翻译(关闭文档再执行)")]
static void InsertString()
{
string xmlPath = Application.dataPath + "/Test.xml";
XElement xElement = XElement.Load(xmlPath);
var insertTarget = xElement.Elements("string");
XComment insertXC = new XComment("Test");
XElement insertXE = new XElement("string");
insertXE.SetAttributeValue("name", "Test");
insertXE.SetValue("Test");
insertTarget.FirstOrDefault(x => x.Attribute("name").Value == "Key").AddAfterSelf(insertXE);
insertTarget.FirstOrDefault(x => x.Attribute("name").Value == "Key").AddAfterSelf(insertXC);
xElement.Save(xmlPath);
AssetDatabase.Refresh();
}最终的结果如下：
<?xml version="1.0" encoding="utf-8"?>
<resources>
<!--Key-->
<string name="Key">Key</string>
<!--Test-->
<string name="Test">Test</string>
</resources>还有一种结构的XML使用不同的方法插入内容
<?xml version="1.0" encoding="utf-8"?>
<resources>
<Work1>
<!--Key-->
<string name="Key">Key</string>
</Work1>
<Work2>
<!--Key-->
<string name="Key">Key</string>
</Work2>
</resources> [MenuItem("Assets/插入翻译(关闭文档再执行)")]
static void InsertString()
{
string xmlPath = Application.dataPath + "/Test.xml";
XDocument doc = XDocument.Load(xmlPath);
XElement xElement = doc.Root.Element("Work1");
var insertTarget = xElement.Elements("string");
XComment insertXC = new XComment("Test");
XElement insertXE = new XElement("string");
insertXE.SetAttributeValue("name", "Test");
insertXE.SetValue("Test");
insertTarget.FirstOrDefault(x => x.Attribute("name").Value == "Key").AddAfterSelf(insertXE);
insertTarget.FirstOrDefault(x => x.Attribute("name").Value == "Key").AddAfterSelf(insertXC);
doc.Save(xmlPath);
AssetDatabase.Refresh();
}最终的结果如下：
<?xml version="1.0" encoding="utf-8"?>
<resources>
<Work1>
<!--Key-->
<string name="Key">Key</string>
<!--Test-->
<string name="Test">Test</string>
</Work1>
<Work2>
<!--Key-->
<string name="Key">Key</string>
</Work2>
</resources>补充一个异常：
InvalidOperationException: Sequence contains no matching element
System.Linq.Enumerable.First[TSource] (System.Collections.Generic.IEnumerable`1[T] source, System.Func`2[T,TResult] predicate) (at <351e49e2a5bf4fd6beabb458ce2255f3>:0)
ClosedXML.Excel.XLWorkbook.LoadSpreadsheetDocument (DocumentFormat.OpenXml.Packaging.SpreadsheetDocument dSpreadsheet) (at <9e48877aecc144c1aeb3dca0ad417e52>:0)
ClosedXML.Excel.XLWorkbook.LoadSheets (System.String fileName) (at <9e48877aecc144c1aeb3dca0ad417e52>:0)
ClosedXML.Excel.XLWorkbook.Load (System.String file) (at <9e48877aecc144c1aeb3dca0ad417e52>:0)
ClosedXML.Excel.XLWorkbook..ctor (System.String file, ClosedXML.Excel.XLEventTracking eventTracking) (at <9e48877aecc144c1aeb3dca0ad417e52>:0)
ClosedXML.Excel.XLWorkbook..ctor (System.String file) (at <9e48877aecc144c1aeb3dca0ad417e52>:0)提取文档时，如果提示这个异常，可以看看文档中是不是有特殊格式或者有批注之类（之前因为文档有批注导致提取失败，卡壳了半天才找到原因。。），清除特殊格式或删除批注即可。
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
