public static DrawingKeywordCollection ReadKeywordList(string fileName)
{
DrawingKeywordCollection drawingKeywords = new DrawingKeywordCollection();
Microsoft.Office.Interop.Excel.ApplicationClass xlsApp = new ApplicationClass();
// 打开文件
xlsApp.Workbooks.Open(fileName, Missing.Value,
XlFileAccess.xlReadOnly, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
xlsApp.Visible = true;
Microsoft.Office.Interop.Excel.Workbook xlsWorkBook
= xlsApp.Workbooks.get_Item(1);
Microsoft.Office.Interop.Excel.Worksheet xlsWorkSheet
= (Microsoft.Office.Interop.Excel.Worksheet)xlsApp.Worksheets.get_Item(xlsApp.Worksheets.Count);
xlsWorkSheet.Activate();
return(drawingKeywords);
}
public static DrawingKeywordCollection ReplaceEquipmentListKeywords(string fileName,
DrawingKeywordCollection keywords)
{
Console.WriteLine("calling Flute.Drawing.Excel.AzovstalExcelHelper.ReplaceEquipmentListKeywords");
DrawingKeywordCollection drawingKeywords = new DrawingKeywordCollection();
frmAZOVSTALReplaceTrans FrmAZOVSTALReplaceTrans = new frmAZOVSTALReplaceTrans();
System.Windows.Forms.DialogResult dlgResult = FrmAZOVSTALReplaceTrans.ShowDialog();
if (dlgResult == System.Windows.Forms.DialogResult.Cancel)
{
return(drawingKeywords);
}
bool replaceEnglishTrans = FrmAZOVSTALReplaceTrans.SelectedEnglishTrans;
bool replaceRussianTrans = FrmAZOVSTALReplaceTrans.SelectedRussianTrans;
FrmAZOVSTALReplaceTrans.Close();
Microsoft.Office.Interop.Excel.ApplicationClass xlsApp = new ApplicationClass();
// 打开文件
xlsApp.Workbooks.Open(fileName, Missing.Value,
XlFileAccess.xlReadOnly, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
xlsApp.Visible = true;
int workSheetCount = xlsApp.Worksheets.Count;
Microsoft.Office.Interop.Excel.Workbook xlsWorkBook
= xlsApp.Workbooks.get_Item(1);
Microsoft.Office.Interop.Excel.Worksheet xlsWorkSheet
= (Microsoft.Office.Interop.Excel.Worksheet)xlsApp.Worksheets.get_Item(workSheetCount);
xlsWorkSheet.Activate();
int nullCellNumber = 0;
int currentCellNumber = 2;
string keyword = "";
string keywordInEnglish = "";
string keywordInRussian = "";
for (int i = 2; i < 65536; i++)
{
if (Convert.ToString(xlsWorkSheet.get_Range("A" + i.ToString(), Missing.Value).Value2) == String.Empty)
{
keyword = Convert.ToString(xlsWorkSheet.get_Range("B" + i.ToString(), Missing.Value).Value2);
// 如果序号列的下一列也为空，没有Keyword
if (keyword == String.Empty)
{
nullCellNumber++;
// 如果10行都是这样，那就是完了
if (nullCellNumber >= 10)
{
break;
}
else
{
continue;
}
}
}
else
{
nullCellNumber = 0;
currentCellNumber = i;
keyword = Convert.ToString(xlsWorkSheet.get_Range("B" + i.ToString(), Missing.Value).Value2);
keywordInEnglish = Convert.ToString(xlsWorkSheet.get_Range("C" + i.ToString(), Missing.Value).Value2);
keywordInRussian = Convert.ToString(xlsWorkSheet.get_Range("D" + i.ToString(), Missing.Value).Value2);
KeywordInOtherLanguageCollection translated = new KeywordInOtherLanguageCollection();
translated.Add(new KeywordInOtherLanguage(DrawingLanguage.English, keywordInEnglish));
translated.Add(new KeywordInOtherLanguage(DrawingLanguage.Russian, keywordInRussian));
DrawingKeyword KEYWORD = new DrawingKeyword(keyword, translated, new KeywordLocationCollection());
drawingKeywords.AddKeyword(KEYWORD);
}
}
if (drawingKeywords.Count > 0)
{
int currentLine = 9;
string currentSubSystem = "";
string currentCellContent = "";
DrawingKeyword foundKeyword = new DrawingKeyword();
string englishTrans = "";
string russianTrans = "";
for (int i = 1; i < workSheetCount; i++)
{
xlsWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlsApp.Worksheets.get_Item(i);
xlsWorkSheet.Activate();
// SubSystem
currentSubSystem = Convert.ToString(xlsWorkSheet.get_Range("B3", Missing.Value).Value2);
if (drawingKeywords.ContainsKeyword(currentSubSystem))
{
foundKeyword = drawingKeywords[currentSubSystem];
englishTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.English].Translated;
// russianTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.Russian].Translated;
xlsWorkSheet.get_Range("B3", Missing.Value).Value2 = englishTrans;
}
for (int j = 9; j < 15; j = j + 2)
{
// Monitoring and control items
currentCellContent = Convert.ToString(xlsWorkSheet.get_Range("D" + j.ToString(), Missing.Value).Value2);
if (drawingKeywords.ContainsKeyword(currentCellContent))
{
foundKeyword = drawingKeywords[currentCellContent];
englishTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.English].Translated;
russianTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.Russian].Translated;
if (replaceEnglishTrans == true && englishTrans != "")
{
xlsWorkSheet.get_Range("D" + j.ToString(), Missing.Value).Value2 = englishTrans;
}
if (replaceRussianTrans == true && russianTrans != "")
{
xlsWorkSheet.get_Range("D" + (j + 1).ToString(), Missing.Value).Value2 = russianTrans;
}
}
// Medium
currentCellContent = Convert.ToString(xlsWorkSheet.get_Range("E" + j.ToString(), Missing.Value).Value2);
if (drawingKeywords.ContainsKeyword(currentCellContent))
{
foundKeyword = drawingKeywords[currentCellContent];
englishTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.English].Translated;
russianTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.Russian].Translated;
if (replaceEnglishTrans == true && englishTrans != "")
{
xlsWorkSheet.get_Range("E" + j.ToString(), Missing.Value).Value2 = englishTrans;
}
if (replaceRussianTrans == true && russianTrans != "")
{
xlsWorkSheet.get_Range("E" + (j + 1).ToString(), Missing.Value).Value2 = russianTrans;
}
}
// Equipment Name
currentCellContent = Convert.ToString(xlsWorkSheet.get_Range("F" + j.ToString(), Missing.Value).Value2);
if (drawingKeywords.ContainsKeyword(currentCellContent))
{
foundKeyword = drawingKeywords[currentCellContent];
englishTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.English].Translated;
russianTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.Russian].Translated;
if (replaceEnglishTrans == true && englishTrans != "")
{
xlsWorkSheet.get_Range("F" + j.ToString(), Missing.Value).Value2 = englishTrans;
}
if (replaceRussianTrans == true && russianTrans != "")
{
xlsWorkSheet.get_Range("F" + (j + 1).ToString(), Missing.Value).Value2 = russianTrans;
}
}
// Supplier
currentCellContent = Convert.ToString(xlsWorkSheet.get_Range("K" + j.ToString(), Missing.Value).Value2);
if (drawingKeywords.ContainsKeyword(currentCellContent))
{
foundKeyword = drawingKeywords[currentCellContent];
englishTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.English].Translated;
russianTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.Russian].Translated;
if (replaceEnglishTrans == true && englishTrans != "")
{
xlsWorkSheet.get_Range("K" + j.ToString(), Missing.Value).Value2 = englishTrans;
}
if (replaceRussianTrans == true && russianTrans != "")
{
xlsWorkSheet.get_Range("K" + (j + 1).ToString(), Missing.Value).Value2 = russianTrans;
}
}
// Specification
currentCellContent = Convert.ToString(xlsWorkSheet.get_Range("L" + j.ToString(), Missing.Value).Value2);
if (drawingKeywords.ContainsKeyword(currentCellContent))
{
foundKeyword = drawingKeywords[currentCellContent];
englishTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.English].Translated;
russianTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.Russian].Translated;
if (replaceEnglishTrans == true && englishTrans != "")
{
xlsWorkSheet.get_Range("L" + j.ToString(), Missing.Value).Value2 = englishTrans;
}
if (replaceRussianTrans == true && russianTrans != "")
{
xlsWorkSheet.get_Range("L" + (j + 1).ToString(), Missing.Value).Value2 = russianTrans;
}
}
// Ins. Location
currentCellContent = Convert.ToString(xlsWorkSheet.get_Range("N" + j.ToString(), Missing.Value).Value2);
if (drawingKeywords.ContainsKeyword(currentCellContent))
{
foundKeyword = drawingKeywords[currentCellContent];
englishTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.English].Translated;
russianTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.Russian].Translated;
if (replaceEnglishTrans == true && englishTrans != "")
{
xlsWorkSheet.get_Range("N" + j.ToString(), Missing.Value).Value2 = englishTrans;
}
if (replaceRussianTrans == true && russianTrans != "")
{
xlsWorkSheet.get_Range("N" + (j + 1).ToString(), Missing.Value).Value2 = russianTrans;
}
}
// Remark
currentCellContent = Convert.ToString(xlsWorkSheet.get_Range("O" + j.ToString(), Missing.Value).Value2);
if (drawingKeywords.ContainsKeyword(currentCellContent))
{
foundKeyword = drawingKeywords[currentCellContent];
englishTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.English].Translated;
russianTrans = foundKeyword.KeywordsInOtherLanguage[DrawingLanguage.Russian].Translated;
if (replaceEnglishTrans == true && englishTrans != "")
{
xlsWorkSheet.get_Range("O" + j.ToString(), Missing.Value).Value2 = englishTrans;
}
if (replaceRussianTrans == true && russianTrans != "")
{
xlsWorkSheet.get_Range("O" + (j + 1).ToString(), Missing.Value).Value2 = russianTrans;
}
}
}
}
}
Flute.Service.MessageBoxWinForm.Info("成功", "成功替换设备表翻译项", "");
return(drawingKeywords);
}
