Excelデータ比較(C#)

-- Index --

・Top

・Softwares

▼親父の独り言集

・Oracleデータベースに対する独り言集

▼VB.NETやC#に対する独り言集

・ご利用の前に
・Excel遅延バインディング読み書き高速化(C#)
→Excelデータ比較(C#)
・並列処理グループの直列化(C#)
・Treeコマンドもどき(C#)
・コマンドラインを配列に分割する(C#)
・相対パス取得(C#)
・ListViewのフォーカス行取得(VB.NET)
・DataGridViewでパスワードマスク

・我が家の家電事情について

いきさつ

Excelのセルの内容が全くの同一でも、バイナリでコンペアすると微妙に違う・・・。 どうやって比較すんのよ。愚直に１セルずつ比較するしかないのか？？ WinMergeならプラグインで一発なのによ〜〜〜。

参考ソース

/// Copyright(c) 2015-2016 pakkin. All Rights Reserved.
/// [改訂履歴]
/// 2015.11.30 作成
/// 2016.01.27 最終セルが結合セルのケースに対応
/// 2016.01.29 SortedListのReverse時の余計なインスタンス生成を削除
using System;
using System.IO;
using System.Collections.Generic;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Linq;
using System.Security.Cryptography;

namespace ConsoleApplication1
{
    class Program
    {
        public static void Main(string[] args)
        {
            string[] checkFiles = new string[] { @"C:\temp\1\comp.xlsx", @"C:\temp\2\comp.xlsx", @"C:\temp\3\comp.xlsx" };
            Console.WriteLine("result:{0}", Program.ExcelDataComp(checkFiles));
            Console.ReadKey(true);
        }
        private static bool ExcelDataComp(string[] inFiles)
        {
            if (inFiles.Length < 2) return true;
            // バイナリレベルで同じであれば終了
            using (MD5CryptoServiceProvider md5 = new MD5CryptoServiceProvider())
            using (FileStream fs1 = new FileStream(inFiles[0], FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                bool binaryComp = true;
                var bs1 = md5.ComputeHash(fs1);
                for (int i = 1; i < inFiles.Length; i++)
                {
                    using (FileStream fs2 = new FileStream(inFiles[i], FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
                    {
                        var bs2 = md5.ComputeHash(fs2);
                        if (!bs1.SequenceEqual(bs2)) binaryComp = false;
                    }
                }
                if (binaryComp) return true;
            }
            // 同名ファイルは開けないので比較用にコピー
            Dictionary<int, Dictionary<string, string>> compFiles = new Dictionary<int, Dictionary<string, string>>();
            string startupPath = new FileInfo(Environment.CommandLine.Replace("\"", "")).Directory.ToString();
            for (int i = 0; i < inFiles.Length; i++)
            {
                Dictionary<string, string> dic = new Dictionary<string, string>();
                dic["baseFile"] = inFiles[i];
                dic["compFile"] = startupPath + string.Format(@"\compData{0}.xlsx", i);
                compFiles[i] = dic;
                File.Copy(dic["baseFile"], dic["compFile"], true);
            }
            // セル内のデータで比較する
            object[] pLastCell = new object[] { 11, Type.Missing };
            object[] pOpen = Enumerable.Repeat(Type.Missing, 15).ToArray();
            Object[,] xlsData1 = null; // object:× Object:○
            Object[,] xlsData2 = null; // object:× Object:○
            SortedList<string, object> comList = new SortedList<string, object>();
            try
            {
                Type classType = Type.GetTypeFromProgID("Excel.Application");
                comList["01xApp"] = Activator.CreateInstance(classType);
                comList["01xApp"].GetType().InvokeMember("DisplayAlerts", BindingFlags.SetProperty, null, comList["01xApp"], new object[] { false });
                comList["01xApp"].GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, comList["01xApp"], new object[] { false });
                comList["02boks"] = comList["01xApp"].GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, comList["01xApp"], null);
                pOpen[0] = compFiles[0]["compFile"];
                comList["03bok1"] = comList["02boks"].GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, comList["02boks"], pOpen);
                comList["04sts1"] = comList["03bok1"].GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, comList["03bok1"], null);
                int stCount1 = (int)comList["04sts1"].GetType().InvokeMember("Count", BindingFlags.GetProperty, null, comList["04sts1"], null);
                // 比較対象ファイル数分繰り返し
                for (int i = 1; i < compFiles.Count; i++)
                {
                    pOpen[0] = compFiles[i]["compFile"];
                    comList["03bok2"] = comList["02boks"].GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, comList["02boks"], pOpen);
                    comList["04sts2"] = comList["03bok2"].GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, comList["03bok2"], null);
                    int stCount2 = (int)comList["04sts2"].GetType().InvokeMember("Count", BindingFlags.GetProperty, null, comList["04sts2"], null);
                    if (stCount1 != stCount2) return false;
                    // 比較対象シート数分繰り返し
                    for (int stNumber = 0; stNumber < stCount1; stNumber++)
                    {
                        int bulkFetchRows = 600;
                        int bulkRowNo = -1;
                        comList["05sht1"] = comList["04sts1"].GetType().InvokeMember("Item", BindingFlags.GetProperty, null, comList["04sts1"], new object[] { stNumber + 1 });
                        comList["05sht2"] = comList["04sts2"].GetType().InvokeMember("Item", BindingFlags.GetProperty, null, comList["04sts2"], new object[] { stNumber + 1 });
                        string stName1 = (string)comList["05sht1"].GetType().InvokeMember("Name", BindingFlags.GetProperty, null, comList["05sht1"], null);
                        string stName2 = (string)comList["05sht2"].GetType().InvokeMember("Name", BindingFlags.GetProperty, null, comList["05sht2"], null);
                        if (stName1 != stName2) return false;
                        comList["06cls1"] = comList["05sht1"].GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, comList["05sht1"], null);
                        comList["06cls2"] = comList["05sht2"].GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, comList["05sht2"], null);
                        comList["07spc1"] = comList["06cls1"].GetType().InvokeMember("SpecialCells", BindingFlags.GetProperty, null, comList["06cls1"], pLastCell);
                        comList["07spc2"] = comList["06cls2"].GetType().InvokeMember("SpecialCells", BindingFlags.GetProperty, null, comList["06cls2"], pLastCell);
                        string lastAddr1 = (string)comList["07spc1"].GetType().InvokeMember("Address", BindingFlags.GetProperty, null, comList["07spc1"], null);
                        string lastAddr2 = (string)comList["07spc2"].GetType().InvokeMember("Address", BindingFlags.GetProperty, null, comList["07spc2"], null);
                        if (lastAddr1 != lastAddr2) return false;
                        int lastRow = (int)comList["07spc1"].GetType().InvokeMember("Row", BindingFlags.GetProperty, null, comList["07spc1"], null);
                        int lastColN = (int)comList["07spc1"].GetType().InvokeMember("Column", BindingFlags.GetProperty, null, comList["07spc1"], null);
                        lastAddr1 = lastAddr1.Split(':')[lastAddr1.Split(':').Length - 1]; // 2016.01.27 insert
                        string lastColC = lastAddr1.Replace("$", "").Replace(lastRow.ToString(), "");
                        for (int fullRow = 0; fullRow < lastRow; fullRow++)
                        {
                            // エクセルから値を取得
                            if (bulkRowNo <= fullRow)
                            {
                                if (fullRow + bulkFetchRows > lastRow) bulkFetchRows = lastRow - fullRow;
                                bulkRowNo = fullRow + bulkFetchRows;
                                if (comList.ContainsKey("08rng1")) { Marshal.FinalReleaseComObject(comList["08rng1"]); comList["08rng1"] = null; }
                                if (comList.ContainsKey("08rng2")) { Marshal.FinalReleaseComObject(comList["08rng2"]); comList["08rng2"] = null; }
                                if ("A" + (fullRow + 1) == lastColC + bulkRowNo)
                                {
                                    object[] pRange = new object[] { "A" + (fullRow + 1) };
                                    comList["08rng1"] = comList["06cls1"].GetType().InvokeMember("Range", BindingFlags.GetProperty, null, comList["06cls1"], pRange);
                                    comList["08rng2"] = comList["06cls2"].GetType().InvokeMember("Range", BindingFlags.GetProperty, null, comList["06cls2"], pRange);
                                    Object tmp1 = comList["08rng1"].GetType().InvokeMember("Value", BindingFlags.GetProperty, null, comList["08rng1"], null);
                                    Object tmp2 = comList["08rng2"].GetType().InvokeMember("Value", BindingFlags.GetProperty, null, comList["08rng2"], null);
                                    xlsData1 = new Object[,] { { null, null }, { null, tmp1 } };
                                    xlsData2 = new Object[,] { { null, null }, { null, tmp2 } };
                                }
                                else
                                {
                                    object[] pRange = new object[] { "A" + (fullRow + 1), lastColC + bulkRowNo };
                                    comList["08rng1"] = comList["06cls1"].GetType().InvokeMember("Range", BindingFlags.GetProperty, null, comList["06cls1"], pRange);
                                    comList["08rng2"] = comList["06cls2"].GetType().InvokeMember("Range", BindingFlags.GetProperty, null, comList["06cls2"], pRange);
                                    xlsData1 = (Object[,])comList["08rng1"].GetType().InvokeMember("Value", BindingFlags.GetProperty, null, comList["08rng1"], null);
                                    xlsData2 = (Object[,])comList["08rng2"].GetType().InvokeMember("Value", BindingFlags.GetProperty, null, comList["08rng2"], null);
                                }
                            }
                            // エクセル行⇒擬似的バルクフェッチ配列行変換
                            int row = bulkFetchRows - bulkRowNo + fullRow + 1;
                            // 比較処理
                            for (int col = 1; col <= lastColN; col++)
                            {
                                if (xlsData1[row, col] == null && xlsData2[row, col] == null) continue;
                                if (xlsData1[row, col] == null || xlsData2[row, col] == null) return false;
                                if (!xlsData1[row, col].Equals(xlsData2[row, col])) return false;
                            }
                        }
                        Marshal.FinalReleaseComObject(comList["07spc1"]); comList["07spc1"] = null;
                        Marshal.FinalReleaseComObject(comList["07spc2"]); comList["07spc2"] = null;
                        Marshal.FinalReleaseComObject(comList["06cls1"]); comList["06cls1"] = null;
                        Marshal.FinalReleaseComObject(comList["06cls2"]); comList["06cls2"] = null;
                        Marshal.FinalReleaseComObject(comList["05sht1"]); comList["05sht1"] = null;
                        Marshal.FinalReleaseComObject(comList["05sht2"]); comList["05sht2"] = null;
                    }
                    Marshal.FinalReleaseComObject(comList["04sts2"]); comList["04sts2"] = null;
                    comList["03bok2"].GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, comList["03bok2"], null);
                    Marshal.FinalReleaseComObject(comList["03bok2"]); comList["03bok2"] = null;
                }
                return true;
            }
            catch (Exception ex)
            {
                // 例外
                Console.WriteLine("Excel Automation Error!:{0}\r\n{1}", ex.Message, ex.StackTrace);
                return false;
            }
            finally
            {
                // ファイルクローズ　& 参照破棄(Reverse指定)
                foreach (string key in comList.Keys.Reverse())
                {
                    if (comList[key] == null) continue;
                    if (key == "03bok1")
                        comList["03bok1"].GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, comList["03bok1"], null);
                    if (key == "03bok2")
                        comList["03bok2"].GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, comList["03bok2"], null);
                    if (key == "01xApp")
                        comList["01xApp"].GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, comList["01xApp"], null);
                    Marshal.FinalReleaseComObject(comList[key]);
                }
                // 比較用ファイルの削除
                for (int i = 0; i < compFiles.Count; i++) File.Delete(compFiles[i]["compFile"]);
            }
        }
    }
}

Copyright(c) 2014-2022 pakkin. All Rights Reserved.