コード例 #1
0
ファイル: Program.cs プロジェクト: eddylin/ExcelReplace
        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();
        }
コード例 #2
0
ファイル: Program.cs プロジェクト: eddylin/ExcelReplace
        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();
        }