private static void MergeExcel(string source, string target)
{
Excel.Application app = new Excel.Application();
app.Visible = false;
Excel.Workbook source_file = app.Workbooks.Open(source);
Excel.Workbook target_file = app.Workbooks.Open(target);
Excel.Worksheet source_sheet = source_file.Sheets[1];
Excel.Worksheet target_sheet = target_file.Sheets[1];
int last_row1 = source_sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
int last_row2 = target_sheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
Array source_values = (System.Array)source_sheet.get_Range("A1", string.Format("B{0}", last_row1)).Cells.Value;
Array target_values = (System.Array)target_sheet.get_Range("A1", string.Format("B{0}", last_row2)).Cells.Value;
char[] strip_chars = new char[] { '\r', '\n', '\t', ' ' };
List <TranslateItem> source_trans = new List <TranslateItem>();
for (int i = 1; i <= last_row1; ++i)
{
TranslateItem tran = new TranslateItem();
if (source_values.GetValue(i, 1) != null)
{
tran.Oringin = source_values.GetValue(i, 1).ToString().Trim(strip_chars);
}
if (source_values.GetValue(i, 2) != null)
{
tran.Translate = source_values.GetValue(i, 2).ToString().Trim(strip_chars);
}
source_trans.Add(tran);
}
List <TranslateItem> target_trans = new List <TranslateItem>();
for (int i = 1; i <= last_row2; ++i)
{
Console.WriteLine("i: " + i);
for (int j = 0; j < source_trans.Count; ++j)
{
if (target_values.GetValue(i, 1) != null && (target_values.GetValue(i, 1).ToString().Trim(strip_chars) == source_trans[j].Oringin.Trim(strip_chars)))
{
try
{
target_sheet.Cells[i, 1] = target_values.GetValue(i, 1).ToString().Trim(strip_chars);
target_sheet.Cells[i, 2] = source_trans[j].Translate.ToString().Trim(strip_chars);
}
catch (Exception e)
{
var e2 = e;
}
break;
}
}
}
target_file.Save();
target_file.Close();
source_file.Close();
}
private static void CompareFile(string file1, string file2, string output_file)
{
Excel.Application app = new Excel.Application();
app.Visible = false;
Excel.Workbook book1 = app.Workbooks.Open(file1);
Excel.Workbook book2 = app.Workbooks.Open(file2);
Excel.Worksheet sheet1 = book1.Sheets[1];
Excel.Worksheet sheet2 = book2.Sheets[1];
int last_row1 = sheet1.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
int last_row2 = sheet2.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
Array values1 = (Array)sheet1.get_Range("A1", string.Format("B{0}", last_row1)).Cells.Value;
Array values2 = (Array)sheet2.get_Range("A1", string.Format("B{0}", last_row2)).Cells.Value;
TranslateItem[] items1 = new TranslateItem[last_row1];
TranslateItem[] items2 = new TranslateItem[last_row2];
for (int i = 1; i <= last_row1; ++i)
{
TranslateItem item = new TranslateItem();
item.Oringin = (values1.GetValue(i, 1) ?? "").ToString();
item.Translate = (values1.GetValue(i, 2) ?? "").ToString();
items1[i - 1] = item;
}
for (int i = 1; i <= last_row2; ++i)
{
TranslateItem item = new TranslateItem();
item.Oringin = (values2.GetValue(i, 1) ?? "").ToString();
item.Translate = (values2.GetValue(i, 2) ?? "").ToString();
items2[i - 1] = item;
}
List <TranslateItem> deleted_items = new List <TranslateItem>();
List <TranslateItem> added_items = new List <TranslateItem>();
List <ModifyTranslateItem> modified_items = new List <ModifyTranslateItem>();
foreach (var item1 in items1)
{
bool is_deleted = true;
foreach (var item2 in items2)
{
if (item1.Oringin == item2.Oringin)
{
is_deleted = false;
if (item1.Translate != item2.Translate)
{
// modified
ModifyTranslateItem modified_item = new ModifyTranslateItem();
modified_item.Oringin = item1.Oringin;
modified_item.Translate = item1.Translate;
modified_item.NewTranslate = item2.Translate;
modified_items.Add(modified_item);
}
}
}
if (is_deleted)
{
// deleted
deleted_items.Add(item1);
}
}
foreach (var item2 in items2)
{
bool is_added = true;
foreach (var item1 in items1)
{
if (item2.Oringin == item1.Oringin)
{
is_added = false;
break;
}
}
if (is_added)
{
// added
added_items.Add(item2);
}
}
if (File.Exists(output_file))
{
File.Delete(output_file);
}
var output_workbook = app.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
if (!Directory.Exists(Path.GetDirectoryName(output_file)))
{
Directory.CreateDirectory(Path.GetDirectoryName(output_file));
}
output_workbook.Sheets.Add();
output_workbook.Sheets.Add();
Excel.Worksheet sheet = output_workbook.Sheets[1];
for (int i = 1; i <= modified_items.Count; ++i)
{
sheet.Cells[i, 1] = modified_items[i - 1].Oringin;
sheet.Cells[i, 2] = modified_items[i - 1].Translate;
sheet.Cells[i, 3] = modified_items[i - 1].NewTranslate;
}
sheet = output_workbook.Sheets[2];
for (int i = 1; i <= added_items.Count; ++i)
{
sheet.Cells[i, 1] = added_items[i - 1].Oringin;
sheet.Cells[i, 2] = added_items[i - 1].Translate;
}
sheet = output_workbook.Sheets[3];
for (int i = 1; i <= deleted_items.Count; ++i)
{
sheet.Cells[i, 1] = deleted_items[i - 1].Oringin;
sheet.Cells[i, 2] = deleted_items[i - 1].Translate;
}
output_workbook.SaveAs(output_file);
output_workbook.Close();
}
