/// <summary>
/// 对比新旧两张Excel母表，返回旧表中含有但新表中已删除的Key所在旧表中的数据索引、新表中新增Key所在新表中的数据索引、新表中的主语言翻译相对旧表变动的Key所在新表中的数据索引（下标均从0开始）
/// </summary>
public static void CompareExcelFile(LangExcelInfo langExcelInfo, LangExcelInfo oldLangExcelInfo, out List <int> delectedKeyIndex, out List <int> newKeyIndex, out List <int> translationChangedIndex)
{
delectedKeyIndex = new List <int>();
newKeyIndex = new List <int>();
translationChangedIndex = new List <int>();
List <LanguageInfo> languageInfoList = langExcelInfo.GetAllLanguageInfoList();
List <LanguageInfo> oldLanguageInfoList = oldLangExcelInfo.GetAllLanguageInfoList();
// 查找新表中已删除的Key
List <string> keys = langExcelInfo.Keys;
List <string> oldKeys = oldLangExcelInfo.Keys;
int oldKeyCount = oldKeys.Count;
for (int i = 0; i < oldKeyCount; ++i)
{
string oldKey = oldKeys[i];
if (oldKey != null && !keys.Contains(oldKey))
{
delectedKeyIndex.Add(i);
}
}
// 查找新增Key、主语言翻译变动
int keyCount = keys.Count;
List <string> defaultLanguageDataList = langExcelInfo.DefaultLanguageInfo.Data;
List <string> oldDefaultLanguageDataList = oldLangExcelInfo.DefaultLanguageInfo.Data;
for (int i = 0; i < keyCount; ++i)
{
string key = keys[i];
if (key == null)
{
continue;
}
else if (!oldKeys.Contains(key))
{
newKeyIndex.Add(i);
}
else
{
string data = defaultLanguageDataList[i];
if (data == null)
{
continue;
}
else
{
int dataIndexInOldLanguageFile = oldLangExcelInfo.KeyToDataIndex[key];
string oldData = oldDefaultLanguageDataList[dataIndexInOldLanguageFile];
if (!data.Equals(oldData))
{
translationChangedIndex.Add(i);
}
}
}
}
}
// 点击“打开Excel母表”按钮
private void btnOpenExcelFile_Click(object sender, EventArgs e)
{
string excelFilePath = txtExcelPath.Text.Trim();
if (string.IsNullOrEmpty(excelFilePath))
{
MessageBox.Show("请先输入或选择国际化Excel母表所在路径", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
FileState fileState = Utils.GetFileState(excelFilePath);
if (fileState == FileState.Inexist)
{
MessageBox.Show("输入的国际化Excel母表所在路径不存在，建议点击\"选择\"按钮进行文件选择", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (!AppValues.EXCEL_FILE_EXTENSION.Equals(Path.GetExtension(excelFilePath), StringComparison.CurrentCultureIgnoreCase))
{
MessageBox.Show(string.Format("本工具仅支持读取扩展名为{0}的Excel文件", AppValues.EXCEL_FILE_EXTENSION), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (fileState == FileState.IsOpen)
{
MessageBox.Show("该Excel文件正被其他软件打开，请关闭后重试", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 读取设置的注释行开头字符
_SetCommentLineStartChar();
// 解析Excel母表
string errorString = null;
LangExcelInfo langExcelInfo = AnalyzeHelper.AnalyzeLangExcelFile(excelFilePath, AppValues.CommentLineStartChar, out errorString);
if (errorString == null)
{
AppValues.ExcelFullPath = Path.GetFullPath(excelFilePath);
// 当重新选择了Excel母表文件后重置窗口控件
_ChangeStateWhenSetExcelPath(false);
AppValues.LangExcelInfo = langExcelInfo;
// 设置了合法的Excel母表后，可以使用各个子模块功能
_ChangeStateWhenSetExcelPath(true);
}
else
{
MessageBox.Show(string.Format("选定的Excel母表存在以下错误，请修正后重试\n\n{0}", errorString), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
/// <summary>
/// 复制最新母表，将翻译完的Excel文件内容与之合并，并将合并结果报告写入新建的Excel文件中
/// </summary>
public static bool ExportMergedExcelFile(string mergedExcelSavePath, string reportExcelSavePath, LangExcelInfo langExcelInfo, LangExcelInfo translatedLangExcelInfo, List <string> mergeLanguageNames, out string errorString)
{
int languageCount = mergeLanguageNames.Count;
// 记录合并翻译时发现的新版母表与翻译完的Excel文件中Key相同但主语言翻译不同信息
List <MergedResultDifferentDefaultLanguageInfo> differentDefaultLanguageInfo = new List <MergedResultDifferentDefaultLanguageInfo>();
// 记录合并翻译时发现的新版母表与翻译完的Excel文件中Key不同信息
List <MergedResultDifferentKeyInfo> differentKeyInfo = new List <MergedResultDifferentKeyInfo>();
// 记录各个报告部分起始行行号
List <int> partStartRowIndexList = new List <int>();
partStartRowIndexList.Add(1);
// 复制新版母表
FileState fileState = Utils.GetFileState(AppValues.ExcelFullPath);
if (fileState == FileState.Inexist)
{
errorString = string.Format("新版母表所在路径（{0}）已不存在，请勿在使用本工具过程中对母表文件进行操作，合并操作被迫中止", AppValues.ExcelFullPath);
return(false);
}
try
{
File.Copy(AppValues.ExcelFullPath, mergedExcelSavePath, true);
}
catch (Exception exception)
{
errorString = string.Format("复制新版母表（{0}）至指定路径（{1}）失败：{2}，合并操作被迫中止", AppValues.ExcelFullPath, mergedExcelSavePath, exception.Message);
return(false);
}
// 打开复制后的母表，将翻译完的Excel文件中的内容与之合并
Excel.Application mergedApplication = new Excel.Application();
// 不显示Excel窗口
mergedApplication.Visible = false;
// 不显示警告对话框
mergedApplication.DisplayAlerts = false;
// 禁止屏幕刷新
mergedApplication.ScreenUpdating = false;
// 编辑非空单元格时不进行警告提示
mergedApplication.AlertBeforeOverwriting = false;
// 打开Excel工作簿
Excel.Workbook mergedWorkbook = mergedApplication.Workbooks.Open(mergedExcelSavePath);
// 找到名为data的Sheet表
Excel.Worksheet mergedDataWorksheet = null;
int sheetCount = mergedWorkbook.Sheets.Count;
string DATA_SHEET_NAME = AppValues.EXCEL_DATA_SHEET_NAME.Replace("$", "");
for (int i = 1; i <= sheetCount; ++i)
{
Excel.Worksheet sheet = mergedWorkbook.Sheets[i] as Excel.Worksheet;
if (sheet.Name.Equals(DATA_SHEET_NAME))
{
mergedDataWorksheet = sheet;
break;
}
}
if (mergedDataWorksheet == null)
{
errorString = string.Format("新版母表（{0}）找不到Sheet名为{1}的数据表，请勿在使用本工具过程中对母表文件进行操作，导出操作被迫中止", AppValues.ExcelFullPath, DATA_SHEET_NAME);
return(false);
}
// 还要新建一张新表，保存合并报告
Excel.Application reportApplication = new Excel.Application();
reportApplication.Visible = false;
reportApplication.DisplayAlerts = false;
reportApplication.ScreenUpdating = false;
reportApplication.AlertBeforeOverwriting = false;
// 新建Excel工作簿
Excel.Workbook reportWorkbook = reportApplication.Workbooks.Add();
// 在名为合并报告的Sheet表中填充数据
Excel.Worksheet reportWorksheet = reportWorkbook.Sheets[1] as Excel.Worksheet;
reportWorksheet.Name = "合并报告";
// 设置表格中所有单元格均为文本格式
reportWorksheet.Cells.NumberFormatLocal = "@";
// 报告Excel文件中依次按Key与主语言译文均相同、Key相同但主语言译文不同、母表不存在指定Key分成三部分进行报告
// 不同部分之间隔开的行数
const int SPACE_LINE_COUNT = 3;
// Key与主语言译文相同的报告部分，列依次为Key名、母表行号、翻译完的Excel文件中的行号、主语言译文、各外语译文，其中用无色背景标识两表译文相同的单元格，用绿色背景标识母表未翻译而翻译完的Excel文件中新增译文的单元格，用黄色背景标识译文不同的单元格（并以批注形式写入母表中旧的译文）
const int ALL_SAME_KEY_COLUMN_INDEX = 1;
const int ALL_SAME_FILE_LINE_NUM_COLUMN_INDEX = 2;
const int ALL_SAME_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX = 3;
const int ALL_SAME_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX = 4;
const int ALL_SAME_OTHER_LANGUAGE_START_COLUMN_INDEX = 5;
// 每个部分首行写入说明文字
reportWorksheet.Cells[1, 1] = "以下为已合并的译文报告，其中用无色背景标识两表译文相同的单元格，用绿色背景标识母表未翻译而翻译完的Excel文件中新增译文的单元格，用黄色背景标识译文不同的单元格（并以批注形式写入母表中旧的译文）";
// 写入Key与主语言译文均相同部分的列标题说明
reportWorksheet.Cells[2, ALL_SAME_KEY_COLUMN_INDEX] = "Key名";
reportWorksheet.Cells[2, ALL_SAME_FILE_LINE_NUM_COLUMN_INDEX] = "母表中的行号";
reportWorksheet.Cells[2, ALL_SAME_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX] = "翻译完的Excel表中的行号";
reportWorksheet.Cells[2, ALL_SAME_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = "主语言译文";
for (int i = 0; i < languageCount; ++i)
{
int columnIndex = ALL_SAME_OTHER_LANGUAGE_START_COLUMN_INDEX + i;
reportWorksheet.Cells[2, columnIndex] = mergeLanguageNames[i];
}
// 当前报告Excel表中下一个可用空行的行号（从1开始计）
int nextCellLineNum = 3;
// 逐行读取翻译完的Excel表中的内容并与最新母表比较，若Key相同主语言翻译相同，直接将翻译完的Excel表中对应的外语译文合并到母表，若Key相同但主语言翻译不同或者翻译完的Excel表中存在母表中已没有的Key则不合并且记入报告
int translatedExcelDataCount = translatedLangExcelInfo.Keys.Count;
for (int i = 0; i < translatedExcelDataCount; ++i)
{
string mergedExcelKey = translatedLangExcelInfo.Keys[i];
if (mergedExcelKey == null)
{
continue;
}
// 判断母表中是否存在指定Key
if (langExcelInfo.Keys.Contains(mergedExcelKey))
{
// 判断母表与翻译完的Excel文件中该Key对应的主语言译文是否相同
// 母表中该Key所在行的数据索引
int excelDataIndex = langExcelInfo.KeyToDataIndex[mergedExcelKey];
string excelDefaultLanguageValue = langExcelInfo.DefaultLanguageInfo.Data[excelDataIndex];
string translatedExcelDefaultLanguageValue = translatedLangExcelInfo.DefaultLanguageInfo.Data[i];
if (excelDefaultLanguageValue.Equals(translatedExcelDefaultLanguageValue))
{
// 如果该行外语的翻译均相同，则无需合并且不需要记入报告
bool isAllSame = true;
foreach (string languageName in mergeLanguageNames)
{
string excelLanguageValue = langExcelInfo.OtherLanguageInfo[languageName].Data[excelDataIndex];
string translatedLanguageValue = translatedLangExcelInfo.OtherLanguageInfo[languageName].Data[i];
if (!excelLanguageValue.Equals(translatedLanguageValue))
{
isAllSame = false;
break;
}
}
// 存在不同的译文，则要合并到母表中并记入报告
if (isAllSame == false)
{
reportWorksheet.Cells[nextCellLineNum, ALL_SAME_KEY_COLUMN_INDEX] = mergedExcelKey;
reportWorksheet.Cells[nextCellLineNum, ALL_SAME_FILE_LINE_NUM_COLUMN_INDEX] = excelDataIndex + AppValues.EXCEL_DATA_START_INDEX;
reportWorksheet.Cells[nextCellLineNum, ALL_SAME_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX] = i + AppValues.EXCEL_DATA_START_INDEX;
reportWorksheet.Cells[nextCellLineNum, ALL_SAME_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX] = i + AppValues.EXCEL_DATA_START_INDEX;
reportWorksheet.Cells[nextCellLineNum, ALL_SAME_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = translatedExcelDefaultLanguageValue;
for (int j = 0; j < languageCount; ++j)
{
string languageName = mergeLanguageNames[j];
string excelLanguageValue = langExcelInfo.OtherLanguageInfo[languageName].Data[excelDataIndex];
string translatedLanguageValue = translatedLangExcelInfo.OtherLanguageInfo[languageName].Data[i];
int columnIndex = ALL_SAME_OTHER_LANGUAGE_START_COLUMN_INDEX + j;
// 报告中外语列单元格都要写入翻译完的Excel文件中对应的译文
reportWorksheet.Cells[nextCellLineNum, columnIndex] = translatedLanguageValue;
if (!excelLanguageValue.Equals(translatedLanguageValue))
{
int mergedExcelRowIndex = excelDataIndex + AppValues.EXCEL_DATA_START_INDEX;
int mergedExcelColumnIndex = langExcelInfo.OtherLanguageInfo[languageName].ColumnIndex;
if (string.IsNullOrEmpty(excelLanguageValue))
{
// 母表中原来没有译文，则将报告Excel表中对应单元格背景色设为绿色
reportWorksheet.get_Range(reportWorksheet.Cells[nextCellLineNum, columnIndex], reportWorksheet.Cells[nextCellLineNum, columnIndex]).Interior.ColorIndex = 4;
}
else
{
// 母表中和翻译完的Excel表中译文不同，则用黄色背景标识译文不同的单元格（并以批注形式写入母表中旧的译文）
reportWorksheet.get_Range(reportWorksheet.Cells[nextCellLineNum, columnIndex], reportWorksheet.Cells[nextCellLineNum, columnIndex]).Interior.ColorIndex = 6;
reportWorksheet.get_Range(reportWorksheet.Cells[nextCellLineNum, columnIndex], reportWorksheet.Cells[nextCellLineNum, columnIndex]).AddComment(string.Concat("母表中旧的译文：", System.Environment.NewLine, excelLanguageValue));
}
// 将翻译完的Excel表中的译文写入母表
mergedDataWorksheet.Cells[mergedExcelRowIndex, mergedExcelColumnIndex] = translatedLanguageValue;
}
}
++nextCellLineNum;
}
}
else
{
// Key相同，主语言译文不同则不合并且记入报告
MergedResultDifferentDefaultLanguageInfo info = new MergedResultDifferentDefaultLanguageInfo();
info.ExcelLineNum = excelDataIndex + AppValues.EXCEL_DATA_START_INDEX;
info.TranslatedExcelLineNum = i + AppValues.EXCEL_DATA_START_INDEX;
info.Key = mergedExcelKey;
info.ExcelDefaultLanguageValue = excelDefaultLanguageValue;
info.TranslatedExcelDefaultLanguageValue = translatedExcelDefaultLanguageValue;
differentDefaultLanguageInfo.Add(info);
}
}
else
{
// 翻译完的Excel表中存在母表中已没有的Key则不合并且记入报告
MergedResultDifferentKeyInfo info = new MergedResultDifferentKeyInfo();
info.TranslatedExcelLineNum = i + AppValues.EXCEL_DATA_START_INDEX;
info.Key = mergedExcelKey;
info.TranslatedExcelDefaultLanguageValue = translatedLangExcelInfo.DefaultLanguageInfo.Data[i];
differentKeyInfo.Add(info);
}
}
// 设置框线及标题行格式
_FormatPart(reportWorksheet, 1, nextCellLineNum - 1, ALL_SAME_OTHER_LANGUAGE_START_COLUMN_INDEX + languageCount - 1);
// Key相同但主语言译文不同的报告部分，列依次为Key名、母表行号、翻译完的Excel文件中的行号、母表中主语言译文、翻译完的Excel文件中主语言译文
const int KEY_SAME_KEY_COLUMN_INDEX = 1;
const int KEY_SAME_FILE_LINE_NUM_COLUMN_INDEX = 2;
const int KEY_SAME_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX = 3;
const int KEY_SAME_EXCEL_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX = 4;
const int KEY_SAME_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX = 5;
if (differentDefaultLanguageInfo.Count > 0)
{
nextCellLineNum = nextCellLineNum + SPACE_LINE_COUNT;
partStartRowIndexList.Add(nextCellLineNum);
// 每个部分首行写入说明文字
reportWorksheet.Cells[nextCellLineNum, 1] = "以下为母表与翻译完的Excel表中Key相同但主语言译文不同，无法进行合并的信息";
++nextCellLineNum;
// 写入Key相同但主语言译文不同部分的列标题说明
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_KEY_COLUMN_INDEX] = "Key名";
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_FILE_LINE_NUM_COLUMN_INDEX] = "母表中的行号";
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX] = "翻译完的Excel表中的行号";
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_EXCEL_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = "母表中主语言译文";
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = "翻译完的Excel文件中主语言译文";
++nextCellLineNum;
// 将所有Key相同但主语言译文不同信息写入报告
foreach (MergedResultDifferentDefaultLanguageInfo info in differentDefaultLanguageInfo)
{
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_KEY_COLUMN_INDEX] = info.Key;
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_FILE_LINE_NUM_COLUMN_INDEX] = info.ExcelLineNum;
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX] = info.TranslatedExcelLineNum;
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_EXCEL_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = info.ExcelDefaultLanguageValue;
reportWorksheet.Cells[nextCellLineNum, KEY_SAME_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = info.TranslatedExcelDefaultLanguageValue;
++nextCellLineNum;
}
}
// 设置框线及标题行格式
_FormatPart(reportWorksheet, partStartRowIndexList[1], nextCellLineNum - 1, KEY_SAME_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX);
// 母表不存在指定Key的报告部分，列依次为Key名、翻译完的Excel文件中的行号以及主语言译文
const int KEY_DIFFERENT_KEY_COLUMN_INDEX = 1;
const int KEY_DIFFERENT_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX = 2;
const int KEY_DIFFERENT_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX = 3;
if (differentKeyInfo.Count > 0)
{
nextCellLineNum = nextCellLineNum + SPACE_LINE_COUNT;
partStartRowIndexList.Add(nextCellLineNum);
// 每个部分首行写入说明文字
reportWorksheet.Cells[nextCellLineNum, 1] = "以下为翻译完的Excel文件含有但母表已经没有的Key，无法进行合并的信息";
++nextCellLineNum;
// 写入母表中已没有的Key部分的列标题说明
reportWorksheet.Cells[nextCellLineNum, KEY_DIFFERENT_KEY_COLUMN_INDEX] = "Key名";
reportWorksheet.Cells[nextCellLineNum, KEY_DIFFERENT_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX] = "翻译完的Excel表中的行号";
reportWorksheet.Cells[nextCellLineNum, KEY_DIFFERENT_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = "翻译完的Excel文件中主语言译文";
++nextCellLineNum;
// 将所有母表中已没有的Key信息写入报告
foreach (MergedResultDifferentKeyInfo info in differentKeyInfo)
{
reportWorksheet.Cells[nextCellLineNum, KEY_DIFFERENT_KEY_COLUMN_INDEX] = info.Key;
reportWorksheet.Cells[nextCellLineNum, KEY_DIFFERENT_TRANSLATED_FILE_LINE_NUM_COLUMN_INDEX] = info.TranslatedExcelLineNum;
reportWorksheet.Cells[nextCellLineNum, KEY_DIFFERENT_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX] = info.TranslatedExcelDefaultLanguageValue;
++nextCellLineNum;
}
}
// 设置框线及标题行格式
_FormatPart(reportWorksheet, partStartRowIndexList[2], nextCellLineNum - 1, KEY_DIFFERENT_TRANSLATED_DEFAULT_LANGUAGE_VALUE_COLUMN_INDEX);
// 美化生成的Excel文件
_BeautifyExcelWorksheet(reportWorksheet, 40, nextCellLineNum - 1);
// 因为Excel中执行过合并的单元格即便设置了自动换行也无法实现效果，故为了防止每个部分首行的描述文字不完全可见，手工修改其行高
for (int i = 0; i < partStartRowIndexList.Count; ++i)
{
int partStartRowIndex = partStartRowIndexList[i];
reportWorksheet.get_Range("A" + partStartRowIndex).EntireRow.RowHeight = 80;
}
// 保存报告Excel文件
reportWorksheet.SaveAs(reportExcelSavePath);
reportWorkbook.SaveAs(reportExcelSavePath);
// 关闭Excel
reportWorkbook.Close(false);
reportApplication.Workbooks.Close();
reportApplication.Quit();
Utils.KillExcelProcess(reportApplication);
// 保存合并后的Excel文件
mergedDataWorksheet.SaveAs(mergedExcelSavePath);
mergedWorkbook.SaveAs(mergedExcelSavePath);
// 关闭Excel
mergedWorkbook.Close(false);
mergedApplication.Workbooks.Close();
mergedApplication.Quit();
Utils.KillExcelProcess(mergedApplication);
errorString = null;
return(true);
}
public static LangExcelInfo AnalyzeLangExcelFile(string filePath, string commentLineStartChar, out string errorString)
{
LangExcelInfo langExcelInfo = new LangExcelInfo();
DataSet dataSet = XlsxReader.ReadXlsxFileByOleDb(filePath, out errorString);
if (errorString != null)
{
return(null);
}
DataTable dataTable = dataSet.Tables[0];
// 依次记录各语种的信息
List <LanguageInfo> languageInfoList = new List <LanguageInfo>();
// 依次记录各语种的名称，不允许同名语种
List <string> languageNames = new List <string>();
int rowCount = dataTable.Rows.Count;
int columnCount = dataTable.Columns.Count;
if (rowCount < 2)
{
errorString = "Excel表格格式非法，必须在前两行声明语种描述和名称";
return(null);
}
if (columnCount < 3)
{
errorString = "Excel表格格式非法，列自左向右应分别声明Key、主语言和至少一种外语";
return(null);
}
// 从表格第2列开始，查找各语言所在列
for (int i = 1; i < columnCount; ++i)
{
string languageName = dataTable.Rows[AppValues.EXCEL_NAME_ROW_INDEX - 1][i].ToString().Trim();
if (!string.IsNullOrEmpty(languageName))
{
// 检查不同语种不允许名称相同
if (languageNames.Contains(languageName))
{
errorString = string.Format("表格第{0}行的语种名称定义中出现了名称均为\"{1}\"的两列，请修正后重试", AppValues.EXCEL_NAME_ROW_INDEX, languageName);
return(null);
}
else
{
languageNames.Add(languageName);
LanguageInfo languageInfo = new LanguageInfo();
languageInfo.Desc = dataTable.Rows[AppValues.EXCEL_DESC_ROW_INDEX - 1][i].ToString().Trim().Replace(System.Environment.NewLine, " ").Replace('\n', ' ').Replace('\r', ' ').Replace('\t', ' ');
languageInfo.Name = languageName;
languageInfo.ColumnIndex = i + 1;
languageInfoList.Add(languageInfo);
}
}
}
if (languageInfoList.Count < 2)
{
errorString = "Excel母表格式非法，列自左向右应分别声明Key、主语言和至少一种外语";
return(null);
}
// 记录所有的Key值，当Key为空或者以指定字符开头时认为是无效Key，存储为null
List <string> keys = new List <string>();
// 记录已读取的Key列值，不允许出现非空的重复Key（key：key值，value：从1开始计的行号）
Dictionary <string, int> keyDict = new Dictionary <string, int>();
// 记录重复Key所在行的信息（key：重复的Key名，value：此Key所在行列表，行号从1开始计）
Dictionary <string, List <int> > repeatedKeyInfo = new Dictionary <string, List <int> >();
// 记录主语言未进行翻译的行索引（从0开始计）
List <int> notTranslatedRowIndex = new List <int>();
// 逐行读取表格并生成所有语种的数据
for (int dataIndex = AppValues.EXCEL_DATA_START_INDEX - 1; dataIndex < rowCount; ++dataIndex)
{
string keyString = dataTable.Rows[dataIndex][0].ToString().Trim();
if (string.IsNullOrEmpty(keyString) || (commentLineStartChar != null && keyString.StartsWith(commentLineStartChar)))
{
keys.Add(null);
for (int i = 0; i < languageInfoList.Count; ++i)
{
LanguageInfo languageInfo = languageInfoList[i];
languageInfo.Data.Add(null);
}
}
else
{
for (int i = 0; i < languageInfoList.Count; ++i)
{
LanguageInfo languageInfo = languageInfoList[i];
string inputData = dataTable.Rows[dataIndex][languageInfo.ColumnIndex - 1].ToString();
languageInfo.Data.Add(inputData);
}
// 如果Key列值出现重复错误进行记录
if (keyDict.ContainsKey(keyString))
{
if (repeatedKeyInfo.ContainsKey(keyString))
{
List <int> repeatedKeyLineNums = repeatedKeyInfo[keyString];
repeatedKeyLineNums.Add(dataIndex + 1);
}
else
{
List <int> repeatedKeyLineNums = new List <int>();
repeatedKeyLineNums.Add(keyDict[keyString]);
repeatedKeyLineNums.Add(dataIndex + 1);
repeatedKeyInfo.Add(keyString, repeatedKeyLineNums);
}
}
else
{
keys.Add(keyString);
keyDict.Add(keyString, dataIndex + 1);
langExcelInfo.KeyToDataIndex.Add(keyString, dataIndex - AppValues.EXCEL_NAME_ROW_INDEX);
// 检查主语言必须含有翻译
LanguageInfo defaultLanguageInfo = languageInfoList[0];
if (string.IsNullOrEmpty(defaultLanguageInfo.Data[dataIndex - AppValues.EXCEL_NAME_ROW_INDEX].ToString()))
{
notTranslatedRowIndex.Add(dataIndex);
}
}
}
}
StringBuilder errorStringBuilder = new StringBuilder();
if (repeatedKeyInfo.Count > 0)
{
errorStringBuilder.AppendLine("以下行Key重复：");
foreach (var item in repeatedKeyInfo)
{
string key = item.Key;
List <int> repeatedKeyLineNums = item.Value;
errorStringBuilder.AppendFormat("名为\"{0}\"的Key在以下行中重复出现：{1}", key, Utils.CombineString <int>(repeatedKeyLineNums, ",")).AppendLine();
}
}
if (notTranslatedRowIndex.Count > 0)
{
errorStringBuilder.AppendLine("以下行中主语言未填写对应文字：");
for (int i = 0; i < notTranslatedRowIndex.Count; ++i)
{
int rowIndex = notTranslatedRowIndex[i];
string key = keys[rowIndex - AppValues.EXCEL_NAME_ROW_INDEX];
errorStringBuilder.AppendFormat("第{0}行（Key名为\"{1}\"）", rowIndex + 1, key).AppendLine();
}
}
errorString = errorStringBuilder.ToString();
if (string.IsNullOrEmpty(errorString))
{
langExcelInfo.Keys = keys;
langExcelInfo.DefaultLanguageInfo = languageInfoList[0];
for (int i = 1; i < languageInfoList.Count; ++i)
{
LanguageInfo languageInfo = languageInfoList[i];
langExcelInfo.OtherLanguageInfo.Add(languageInfo.Name, languageInfo);
}
errorString = null;
return(langExcelInfo);
}
else
{
return(null);
}
}
// 点击“合并翻译完的Excel文件”按钮
private void btnMergeTranslatedExcelFile_Click(object sender, EventArgs e)
{
// 检查是否指定了合法的合并后的Excel文件的保存路径
string mergedExcelSavePath = txtMergedExcelPath.Text.Trim();
if (string.IsNullOrEmpty(mergedExcelSavePath))
{
MessageBox.Show("必须输入合并后的Excel文件的保存路径", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (!AppValues.EXCEL_FILE_EXTENSION.Equals(Path.GetExtension(mergedExcelSavePath), StringComparison.CurrentCultureIgnoreCase))
{
MessageBox.Show(string.Format("合并后的Excel文件扩展名必须为{0}", AppValues.EXCEL_FILE_EXTENSION), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 检查要导出的后的Excel文件是否已存在且正被其他程序使用
if (Utils.GetFileState(mergedExcelSavePath) == FileState.IsOpen)
{
MessageBox.Show("要覆盖的合并后的Excel文件正被其他程序打开，请关闭后重试", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 检查是否指定了合法的翻译完的Excel文件
string translatedExcelPath = txtTranslatedExcelPath.Text.Trim();
if (string.IsNullOrEmpty(translatedExcelPath))
{
MessageBox.Show("必须输入翻译完的Excel文件所在路径", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
FileState fileState = Utils.GetFileState(translatedExcelPath);
if (fileState == FileState.Inexist)
{
MessageBox.Show("输入的翻译完的Excel文件所在路径不存在，建议点击\"选择\"按钮进行文件选择", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (!AppValues.EXCEL_FILE_EXTENSION.Equals(Path.GetExtension(translatedExcelPath), StringComparison.CurrentCultureIgnoreCase))
{
MessageBox.Show(string.Format("本工具仅支持读取扩展名为{0}的Excel文件", AppValues.EXCEL_FILE_EXTENSION), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (fileState == FileState.IsOpen)
{
MessageBox.Show("指定的翻译完的Excel文件正被其他软件打开，请关闭后重试", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 检查选择的翻译完的Excel文件和新版母表不能为同一个文件
string translatedExcelFileFullPath = Path.GetFullPath(translatedExcelPath);
if (translatedExcelFileFullPath.Equals(AppValues.ExcelFullPath))
{
MessageBox.Show("你选择的翻译完的Excel文件和新版母表是同一个文件", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 解析翻译完的Excel文件
string errorString = null;
LangExcelInfo translatedExcelInfo = AnalyzeHelper.AnalyzeLangExcelFile(translatedExcelPath, AppValues.CommentLineStartChar, out errorString);
if (errorString != null)
{
MessageBox.Show(string.Format("选定的翻译完后的Excel文件存在以下错误，请修正后重试\n\n{0}", errorString), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 检查翻译完的Excel文件与新版母表的主语言名称是否相同
string excelDefaultLanguageName = AppValues.LangExcelInfo.DefaultLanguageInfo.Name;
string translatedExcelDefaultLanguageName = translatedExcelInfo.DefaultLanguageInfo.Name;
if (!excelDefaultLanguageName.Equals(translatedExcelDefaultLanguageName))
{
MessageBox.Show(string.Format("翻译完的Excel文件与新版母表的主语言名称不同，无法进行对比，请统一后重试\n新版母表中主语言名称：{0}，翻译完的Excel文件中主语言名称：{1}", excelDefaultLanguageName, translatedExcelDefaultLanguageName), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 记录翻译完的Excel文件中存在的外语名
List <string> translatedExcelFileOtherLanguageNames = new List <string>();
// 检查翻译完的Excel文件中的外语在新版母表中是否都存在
List <string> inexistentOtherLanguageName = new List <string>();
foreach (string otherLanguageName in translatedExcelInfo.OtherLanguageInfo.Keys)
{
if (!AppValues.LangExcelInfo.OtherLanguageInfo.ContainsKey(otherLanguageName))
{
inexistentOtherLanguageName.Add(otherLanguageName);
}
translatedExcelFileOtherLanguageNames.Add(otherLanguageName);
}
if (inexistentOtherLanguageName.Count > 0)
{
MessageBox.Show(string.Format("翻译完后的Excel文件中存在以下新版母表中不存在的外语名，无法进行合并\n{0}", Utils.CombineString <string>(inexistentOtherLanguageName, ",")), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 检查完毕执行合并功能并生成合并结果报告文件（保存路径与合并后的Excel文件相同）
string reportExcelSavePath = Utils.CombinePath(Path.GetDirectoryName(mergedExcelSavePath), string.Format("合并报告 {0:yyyy年MM月dd日 HH时mm分ss秒}.xlsx", DateTime.Now));
ExportExcelFileHelper.ExportMergedExcelFile(mergedExcelSavePath, reportExcelSavePath, AppValues.LangExcelInfo, translatedExcelInfo, translatedExcelFileOtherLanguageNames, out errorString);
if (errorString == null)
{
DialogResult dialogResult = MessageBox.Show(string.Format("合并操作成功\n合并后的Excel文件存储路径为{0}\n报告文件存储路径为{1}\n\n点击“确定”按钮后将自动打开此报告文件", mergedExcelSavePath, reportExcelSavePath), "恭喜", MessageBoxButtons.OK, MessageBoxIcon.Information);
if (dialogResult == DialogResult.OK)
{
System.Diagnostics.Process.Start(reportExcelSavePath);
}
}
else
{
errorString = string.Concat("合并操作失败：", errorString);
MessageBox.Show(errorString, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
// 点击“打开旧版母表”按钮
private void btnOpenOldExcelFile_Click(object sender, EventArgs e)
{
string excelFilePath = txtOldExcelPath.Text.Trim();
if (string.IsNullOrEmpty(excelFilePath))
{
MessageBox.Show("请先输入或选择与新版母表对比的旧版国际化Excel母表所在路径", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
FileState fileState = Utils.GetFileState(excelFilePath);
if (fileState == FileState.Inexist)
{
MessageBox.Show("输入的旧版国际化Excel母表所在路径不存在，建议点击\"选择\"按钮进行文件选择", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (!AppValues.EXCEL_FILE_EXTENSION.Equals(Path.GetExtension(excelFilePath), StringComparison.CurrentCultureIgnoreCase))
{
MessageBox.Show(string.Format("本工具仅支持读取扩展名为{0}的Excel文件", AppValues.EXCEL_FILE_EXTENSION), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (fileState == FileState.IsOpen)
{
MessageBox.Show("该Excel文件正被其他软件打开，请关闭后重试", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 检查选择的旧表和新表不能为同一个文件
string oldExcelFileFullPath = Path.GetFullPath(excelFilePath);
if (oldExcelFileFullPath.Equals(AppValues.ExcelFullPath))
{
MessageBox.Show("你选择的旧表和新表是同一个文件", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 解析旧版Excel母表
string errorString = null;
LangExcelInfo langExcelInfo = AnalyzeHelper.AnalyzeLangExcelFile(excelFilePath, AppValues.CommentLineStartChar, out errorString);
if (errorString == null)
{
// 检查新旧母表的主语言名称是否相同
string excelDefaultLanguageName = AppValues.LangExcelInfo.DefaultLanguageInfo.Name;
string oldExcelDefaultLanguageName = langExcelInfo.DefaultLanguageInfo.Name;
if (!excelDefaultLanguageName.Equals(oldExcelDefaultLanguageName))
{
MessageBox.Show(string.Format("新旧母表主语言名称不同，无法进行对比，请统一后重试\n新版母表中主语言名称：{0}，旧版中主语言名称：{1}", excelDefaultLanguageName, oldExcelDefaultLanguageName), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
// 检查新旧母表的所有外语名称是否相同
bool isMatchOtherLanguageInfo = true;
if (AppValues.LangExcelInfo.OtherLanguageInfo.Count == langExcelInfo.OtherLanguageInfo.Count)
{
foreach (var item in AppValues.LangExcelInfo.OtherLanguageInfo)
{
if (!langExcelInfo.OtherLanguageInfo.ContainsKey(item.Key))
{
isMatchOtherLanguageInfo = false;
break;
}
}
}
else
{
isMatchOtherLanguageInfo = false;
}
if (isMatchOtherLanguageInfo == false)
{
MessageBox.Show("新旧母表的所有外语种类个数或者名称不完全匹配，无法进行对比", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
AppValues.OldLangExcelInfo = langExcelInfo;
// 设置了合法的旧版Excel母表后，可以使用对比功能下属的各个子模块功能
_ChangeStateWhenSetOldExcelPath(true);
}
else
{
MessageBox.Show(string.Format("选定的旧版Excel母表存在以下错误，请修正后重试\n\n{0}", errorString), "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
public static bool ExportLangFile(LangExcelInfo langExcelInfo, string languageName, string savePath, string keyAndValueSplitChar, out string errorString)
{
// 检查输入的路径是否合法
string fullPath = null;
try
{
fullPath = Path.GetFullPath(savePath);
}
catch
{
errorString = "路径非法";
return(false);
}
string dirPath = Path.GetDirectoryName(fullPath);
if (string.IsNullOrEmpty(Path.GetFileName(fullPath)))
{
errorString = "路径非法，未输入文件名";
return(false);
}
if (!Directory.Exists(dirPath))
{
try
{
Directory.CreateDirectory(dirPath);
}
catch (Exception exception)
{
errorString = string.Format("创建目录（{0}）失败：{1}", dirPath, exception.Message);
return(false);
}
}
if (string.IsNullOrEmpty(Path.GetExtension(fullPath)))
{
fullPath = string.Concat(fullPath, ".", AppValues.LangFileExtension);
}
// 记录未填写译文的数据索引（下标从0开始）
List <int> untranslatedDataIndex = new List <int>();
// 记录要写入lang文件的内容
StringBuilder langFileContent = new StringBuilder();
List <string> keys = langExcelInfo.Keys;
List <string> translatedText = langExcelInfo.GetLanguageInfoByLanguageName(languageName).Data;
int keyCount = keys.Count;
for (int i = 0; i < keyCount; ++i)
{
if (keys[i] == null)
{
continue;
}
if (string.IsNullOrEmpty(translatedText[i]))
{
untranslatedDataIndex.Add(i);
// 当发现有未翻译的问题时，该lang文件不会进行导出，后面也无需组织要写入lang文件的内容，只需继续检查后面是否还有未翻译的问题
for (int j = i + 1; j < keyCount; ++j)
{
if (keys[j] == null)
{
continue;
}
if (string.IsNullOrEmpty(translatedText[j]))
{
untranslatedDataIndex.Add(j);
}
}
break;
}
else
{
langFileContent.AppendLine(string.Concat(keys[i], keyAndValueSplitChar, translatedText[i]));
}
}
if (untranslatedDataIndex.Count > 0)
{
StringBuilder errorStringBuilder = new StringBuilder();
errorStringBuilder.AppendLine("错误：在以下行中存在未翻译的内容，请完全翻译后重试");
foreach (int dataIndex in untranslatedDataIndex)
{
errorStringBuilder.AppendFormat("第{0}行，Key为\"{1}\"", dataIndex + AppValues.EXCEL_DATA_START_INDEX, keys[dataIndex]).AppendLine();
}
errorString = errorStringBuilder.ToString();
return(false);
}
// 检查无误后生成lang文件
if (Utils.SaveFile(fullPath, langFileContent.ToString(), out errorString) == true)
{
return(true);
}
else
{
errorString = string.Concat("生成lang文件失败：", errorString);
return(false);
}
}
