Excel遅延バインディング読み書き高速化(C#)

いきさつ

とあるいきさつでEXCELを遅延バインディング方式で読み込み処理をしているプログラムを修正することに、でも 何百万件もあるデータだと、遅い、遅すぎる・・・。ソースを覗いてみれば、 EXCELとのインターフェースもご丁寧に細かくクラス化されて模範的に素晴らしい作りこみなのでしょう。 だが、ここであえてそれらを１から捨て、汚らしく愚直的なコーディングで作り直してみます。 ここで目指すもの、それは、データベースの世界でも言えること。
ずばり"アクセス回数を極限まで減らす"です。
PL/SQLの機能"バルクフェッチ"を疑似的に構築し、EXCELとのアクセス回数を減らしてみました。

<<2016.07.29>>
Excelのインストールが不要なアクセス方式(ClosedXML,NPOI)についてもネットで記載されていましたので 参考までに速度比較してみました。

参考ソース

/// Copyright(c) 2015-2016 pakkin. All Rights Reserved.
/// [改訂履歴]
/// 2015.11.08 作成
/// 2015.11.09 dynamic方式版を追加
/// 2015.11.19 1行1列しか存在しない場合を対応
/// 2015.11.30 空シート判定を修正
/// 2016.01.27 最終セルが結合セルのケースに対応
/// 2016.01.29 SortedListのReverse時の余計なインスタンス生成を削除
/// 2016.07.29 ClosedXMLやNPOIのケースも比較対象として追加
using System;
using System.IO;
using System.Collections.Generic;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Linq;
using ClosedXML.Excel;
using NPOI.SS.UserModel;

namespace ConsoleApplication1
{
    class Program
    {
        /// <summary>
        /// Main
        /// </summary>
        /// <param name="args"></param>
        public static void Main(string[] args)
        {
            Program.ExcelTest1(@"C:\temp\input.xlsx", "Sheet1", @"C:\temp\output1.xlsx");
            Program.ExcelTest2(@"C:\temp\input.xlsx", "Sheet1", @"C:\temp\output2.xlsx");
            Program.ExcelTest3(@"C:\temp\input.xlsx", "Sheet1", @"C:\temp\output3.xlsx");
            Program.ExcelTest4(@"C:\temp\input.xlsx", "Sheet1", @"C:\temp\output4.xlsx");
            Console.ReadKey(true);
        }
        /// <summary>
        /// Dynamic方式
        /// </summary>
        /// <param name="readFile"></param>
        /// <param name="sheetName"></param>
        /// <param name="saveFile"></param>
        private static void ExcelTest1(string readFile, string sheetName, string saveFile)
        {
            // 参照用配列
            SortedList<string, dynamic> comList = new SortedList<string, dynamic>();
            // EXCELへのアクセス行単位指定パラメータ(擬似的バルクフェッチ)
            int bulkFetchRows = 600;
            int bulkRowNo = -1;
            Object[,] xlsData = null; // object:× Object:○
            // コンソール出力用
            int lastRow = 0;
            DateTime start = DateTime.Now;
            try
            {
                // 既存ファイルの読み込み
                Type classType = Type.GetTypeFromProgID("Excel.Application");
                comList["01xApp"] = Activator.CreateInstance(classType);
                comList["01xApp"].DisplayAlerts = false;
                comList["01xApp"].Visible = false;
                comList["02boks"] = comList["01xApp"].Workbooks;
                comList["03book"] = comList["02boks"].Open(readFile);
                comList["04shts"] = comList["03book"].Worksheets;
                comList["05shet"] = comList["04shts"].Item(sheetName);
                comList["06cels"] = comList["05shet"].Cells;
                comList["07spcc"] = comList["06cels"].SpecialCells(11);
                string lastAddr = (string)comList["07spcc"].Address;
                lastRow = (int)comList["07spcc"].Row;
                int lastColN = (int)comList["07spcc"].Column;
                lastAddr = lastAddr.Split(':')[lastAddr.Split(':').Length - 1]; // 2016.01.27 insert
                string lastColC = lastAddr.Replace("$", "").Replace(lastRow.ToString(), "");
                for (int fullRow = 0; fullRow <= lastRow; fullRow++)
                {
                    // ----- 更新不要ならコメントアウト...ここから ------
                    if (fullRow > 0 && (fullRow == lastRow || bulkRowNo <= fullRow))
                    {
                        // エクセルに編集結果を反映
                        comList["08rnge"].Value = xlsData.GetLowerBound(0) == 1 ? xlsData : xlsData[1, 1];
                        // 保存
                        if (fullRow == lastRow) comList["03book"].SaveAs(saveFile);
                    }
                    // ----- 更新不要ならコメントアウト...ここまで ------
                    // 終了
                    if (fullRow == lastRow) break;
                    // エクセルから値を取得
                    if (bulkRowNo <= fullRow)
                    {
                        if (fullRow + bulkFetchRows > lastRow) bulkFetchRows = lastRow - fullRow;
                        bulkRowNo = fullRow + bulkFetchRows;
                        if (comList.ContainsKey("08rnge")) Marshal.FinalReleaseComObject(comList["08rnge"]);
                        if ("A" + (fullRow + 1) == lastColC + bulkRowNo)
                        {
                            comList["08rnge"] = comList["06cels"].Range("A" + (fullRow + 1));
                            Object tmp = comList["08rnge"].Value;
                            if (tmp == null && lastRow == 1) { lastRow = 0; break; } // 空白シートはファイル作成しない
                            xlsData = new Object[,] { { null, null }, { null, tmp } };
                        }
                        else
                        {
                            comList["08rnge"] = comList["06cels"].Range("A" + (fullRow + 1) + ":" + lastColC + bulkRowNo);
                            xlsData = (Object[,])comList["08rnge"].Value;
                        }
                    }
                    // エクセル行⇒擬似的バルクフェッチ配列行変換
                    int row = bulkFetchRows - bulkRowNo + fullRow + 1;
                    // 実際の編集処理はここでおこなう
                    for (int col = 1; col <= lastColN; col++)
                    {
                        // 値を編集
                        if (xlsData[row, col] == null)
                        {
                            xlsData[row, col] = "[null]";
                        }
                        else if (xlsData[row, col].GetType().Equals(typeof(System.Double)))
                        {
                            string add = string.Format("[double:{0,7}({1,3}):{2}]", fullRow + 1, row, col);
                            xlsData[row, col] = ((double)xlsData[row, col]).ToString("0") + add;
                        }
                        else if (xlsData[row, col].GetType().Equals(typeof(System.DateTime)))
                        {
                            string add = string.Format("[date:{0,7}({1,3}):{2}]", fullRow + 1, row, col);
                            xlsData[row, col] = ((DateTime)xlsData[row, col]).ToString("yyyy/MM/dd") + add;
                        }
                        else
                        {
                            string add = string.Format("[string:{0,7}({1,3}):{2}]", fullRow + 1, row, col);
                            xlsData[row, col] = (string)xlsData[row, col] + add;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                // 例外
                Console.WriteLine("Excel Automation Error!:{0}\r\n{1}", ex.Message, ex.StackTrace);
            }
            finally
            {
                // ファイルクローズ　& 参照破棄(Reverse指定)
                foreach (string key in comList.Keys.Reverse())
                {
                    switch (key)
                    {
                        case "01xApp":
                            comList[key].Quit();
                            Marshal.FinalReleaseComObject(comList[key]);
                            break;
                        case "03book":
                            comList[key].Close();
                            break; // FinalReleaseComObject開放無し
                        default:
                            Marshal.FinalReleaseComObject(comList[key]);
                            break;
                    }
                }
                TimeSpan required = DateTime.Now - start;
                Console.WriteLine("---- Dynamic方式 ----");
                Console.WriteLine("Time required : {0} seconds.", required);
                Console.WriteLine("Read/Write    : {0} records.", lastRow);
            }
        }
        /// <summary>
        /// InvokeMember方式
        /// </summary>
        /// <param name="readFile"></param>
        /// <param name="sheetName"></param>
        /// <param name="saveFile"></param>
        private static void ExcelTest2(string readFile, string sheetName, string saveFile)
        {
            // 参照用配列
            SortedList<string, object> comList = new SortedList<string, object>();
            // 遅延バインディングパラメータ
            object[] pOpen = Enumerable.Repeat(Type.Missing, 15).ToArray(); pOpen[0] = readFile;
            object[] pItem = new object[] { sheetName };
            object[] pLastCell = new object[] { 11, Type.Missing };
            // EXCELへのアクセス行単位指定パラメータ(擬似的バルクフェッチ)
            int bulkFetchRows = 600;
            int bulkRowNo = -1;
            Object[,] xlsData = null; // object:× Object:○
            // コンソール出力用
            int lastRow = 0;
            DateTime start = DateTime.Now;
            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);
                comList["03book"] = comList["02boks"].GetType().InvokeMember("Open", BindingFlags.InvokeMethod, null, comList["02boks"], pOpen);
                comList["04shts"] = comList["03book"].GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, comList["03book"], null);
                comList["05shet"] = comList["04shts"].GetType().InvokeMember("Item", BindingFlags.GetProperty, null, comList["04shts"], pItem);
                comList["06cels"] = comList["05shet"].GetType().InvokeMember("Cells", BindingFlags.GetProperty, null, comList["05shet"], null);
                comList["07spcc"] = comList["06cels"].GetType().InvokeMember("SpecialCells", BindingFlags.GetProperty, null, comList["06cels"], pLastCell);
                string lastAddr = (string)comList["07spcc"].GetType().InvokeMember("Address", BindingFlags.GetProperty, null, comList["07spcc"], null);
                lastRow = (int)comList["07spcc"].GetType().InvokeMember("Row", BindingFlags.GetProperty, null, comList["07spcc"], null);
                int lastColN = (int)comList["07spcc"].GetType().InvokeMember("Column", BindingFlags.GetProperty, null, comList["07spcc"], null);
                lastAddr = lastAddr.Split(':')[lastAddr.Split(':').Length - 1]; // 2016.01.27 insert
                string lastColC = lastAddr.Replace("$", "").Replace(lastRow.ToString(), "");
                for (int fullRow = 0; fullRow <= lastRow; fullRow++)
                {
                    // ----- 更新不要ならコメントアウト...ここから ------
                    if (fullRow > 0 && (fullRow == lastRow || bulkRowNo <= fullRow))
                    {
                        // エクセルに編集結果を反映
                        object[] pValue = new object[] { xlsData.GetLowerBound(0) == 1 ? xlsData : xlsData[1, 1] };
                        comList["08rnge"].GetType().InvokeMember("Value", BindingFlags.SetProperty, null, comList["08rnge"], pValue);
                        // 保存
                        if (fullRow == lastRow)
                            comList["03book"].GetType().InvokeMember("SaveAs", BindingFlags.InvokeMethod, null, comList["03book"], new object[] { saveFile });
                    }
                    // ----- 更新不要ならコメントアウト...ここまで ------
                    // 終了
                    if (fullRow == lastRow) break;
                    // エクセルから値を取得
                    if (bulkRowNo <= fullRow)
                    {
                        if (fullRow + bulkFetchRows > lastRow) bulkFetchRows = lastRow - fullRow;
                        bulkRowNo = fullRow + bulkFetchRows;
                        if (comList.ContainsKey("08rnge")) Marshal.FinalReleaseComObject(comList["08rnge"]);
                        if ("A" + (fullRow + 1) == lastColC + bulkRowNo)
                        {
                            object[] pRange = new object[] { "A" + (fullRow + 1) };
                            comList["08rnge"] = comList["06cels"].GetType().InvokeMember("Range", BindingFlags.GetProperty, null, comList["06cels"], pRange);
                            Object tmp = comList["08rnge"].GetType().InvokeMember("Value", BindingFlags.GetProperty, null, comList["08rnge"], null);
                            if (tmp == null && lastRow == 1) { lastRow = 0; break; } // 空白シートはファイル作成しない
                            xlsData = new Object[,] { { null, null }, { null, tmp } };
                        }
                        else
                        {
                            object[] pRange = new object[] { "A" + (fullRow + 1), lastColC + bulkRowNo };
                            comList["08rnge"] = comList["06cels"].GetType().InvokeMember("Range", BindingFlags.GetProperty, null, comList["06cels"], pRange);
                            xlsData = (Object[,])comList["08rnge"].GetType().InvokeMember("Value", BindingFlags.GetProperty, null, comList["08rnge"], null);
                        }
                    }
                    // エクセル行⇒擬似的バルクフェッチ配列行変換
                    int row = bulkFetchRows - bulkRowNo + fullRow + 1;
                    // 実際の編集処理はここでおこなう
                    for (int col = 1; col <= lastColN; col++)
                    {
                        // 値を編集
                        if (xlsData[row, col] == null)
                        {
                            xlsData[row, col] = "[null]";
                        }
                        else if (xlsData[row, col].GetType().Equals(typeof(System.Double)))
                        {
                            string add = string.Format("[double:{0,7}({1,3}):{2}]", fullRow + 1, row, col);
                            xlsData[row, col] = ((double)xlsData[row, col]).ToString("0") + add;
                        }
                        else if (xlsData[row, col].GetType().Equals(typeof(System.DateTime)))
                        {
                            string add = string.Format("[date:{0,7}({1,3}):{2}]", fullRow + 1, row, col);
                            xlsData[row, col] = ((DateTime)xlsData[row, col]).ToString("yyyy/MM/dd") + add;
                        }
                        else
                        {
                            string add = string.Format("[string:{0,7}({1,3}):{2}]", fullRow + 1, row, col);
                            xlsData[row, col] = (string)xlsData[row, col] + add;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                // 例外
                Console.WriteLine("Excel Automation Error!:{0}\r\n{1}", ex.Message, ex.StackTrace);
            }
            finally
            {
                // ファイルクローズ　& 参照破棄(Reverse指定)
                foreach (string key in comList.Keys.Reverse())
                {
                    if (key == "03book")
                        comList["03book"].GetType().InvokeMember("Close", BindingFlags.InvokeMethod, null, comList["03book"], null);
                    if (key == "01xApp")
                        comList["01xApp"].GetType().InvokeMember("Quit", BindingFlags.InvokeMethod, null, comList["01xApp"], null);
                    Marshal.FinalReleaseComObject(comList[key]);
                }
                TimeSpan required = DateTime.Now - start;
                Console.WriteLine("---- InvokeMember方式 ----");
                Console.WriteLine("Time required : {0} seconds.", required);
                Console.WriteLine("Read/Write    : {0} records.", lastRow);
            }
        }
        /// <summary>
        /// 参考：ClosedXML方式
        /// </summary>
        /// <param name="readFile"></param>
        /// <param name="sheetName"></param>
        /// <param name="saveFile"></param>
        private static void ExcelTest3(string readFile, string sheetName, string saveFile)
        {
            DateTime start = DateTime.Now;
            try
            {
                File.Copy(readFile, saveFile, true);
                using (XLWorkbook workbook = new XLWorkbook(saveFile))
                using (IXLWorksheet worksheet = workbook.Worksheet(sheetName))
                {
                    int lastRow = worksheet.LastRowUsed().RowNumber();
                    int lastCol = worksheet.LastColumnUsed().ColumnNumber();
                    for (int i = 1; i <= lastRow; i++)
                    {
                        for (int j = 1; j <= lastCol; j++)
                        {
                            IXLCell cell = worksheet.Cell(i, j);
                            if (cell.DataType == XLCellValues.Number)
                            {
                                string add = string.Format("[double:{0,7}:{1}]", i, j);
                                cell.Value = ((Double)cell.Value).ToString("0") + add;
                            }
                            else if (cell.DataType == XLCellValues.DateTime)
                            {
                                string add = string.Format("[date:{0,7}:{1}]", i, j);
                                cell.Value = ((DateTime)cell.Value).ToString("yyyy/MM/dd") + add;
                            }
                            else if (cell.DataType == XLCellValues.Text)
                            {
                                string txt = cell.Value.ToString();
                                string add = string.Format("[string:{0,7}:{1}]", i, j);
                                cell.Value = txt.Length == 0 ? "[null]" : txt + add;
                            }
                        }
                    }
                    workbook.Save();
                    TimeSpan required = DateTime.Now - start;
                    Console.WriteLine("---- ClosedXML方式 ----");
                    Console.WriteLine("Time required : {0} seconds.", required);
                    Console.WriteLine("Read/Write    : {0} records.", lastRow);
                }
            }
            catch (Exception ex)
            {
                TimeSpan required = DateTime.Now - start;
                Console.WriteLine("---- ClosedXML方式 ----");
                Console.WriteLine("Time required : {0} seconds.", required);
                Console.WriteLine(ex.Message);
            }
        }
        /// <summary>
        /// 参考：NPOI方式
        /// </summary>
        /// <param name="readFile"></param>
        /// <param name="sheetName"></param>
        /// <param name="saveFile"></param>
        private static void ExcelTest4(string readFile, string sheetName, string saveFile)
        {
            DateTime start = DateTime.Now;
            IWorkbook workbook = null;
            ISheet worksheet = null;
            try
            {
                File.Copy(readFile, saveFile + "#", true);
                workbook = WorkbookFactory.Create(saveFile + "#");
                worksheet = workbook.GetSheet(sheetName);
                {
                    int lastRow = worksheet.LastRowNum;
                    for (int i = 0; i <= lastRow; i++)
                    {
                        IRow row = worksheet.GetRow(i);
                        if (row == null) continue;
                        int col = 0;
                        List<int> newCellIndexes = new List<int>();
                        for (int j = 0; j < row.Cells.Count; j++)
                        {
                            ICell cell = row.Cells[j];
                            if (cell.CellType == CellType.Numeric && !DateUtil.IsCellDateFormatted(cell))
                            {
                                string add = string.Format("[double:{0,7}:{1}]", i + 1, j);
                                cell.SetCellValue(cell.NumericCellValue.ToString("0") + add);
                            }
                            else if (cell.CellType == CellType.Numeric)
                            {
                                string add = string.Format("[date:{0,7}:{1}]", i + 1, j);
                                cell.SetCellValue(cell.DateCellValue.ToString("yyyy/MM/dd") + add);
                            }
                            else if (cell.CellType == CellType.String)
                            {
                                string add = string.Format("[string:{0,7}:{1}]", i + 1, j);
                                cell.SetCellValue(cell.StringCellValue + add);
                            }
                            while (col < cell.ColumnIndex) newCellIndexes.Add(col++);
                            col++;
                        }
                        foreach (int j in newCellIndexes)
                        {
                            ICell cell = row.CreateCell(j, CellType.String);
                            cell.SetCellValue("[null]");
                        }
                    }
                    using (FileStream stream = new FileStream(saveFile, FileMode.Create, FileAccess.Write, FileShare.None))
                    {
                        workbook.Write(stream);
                    }
                    TimeSpan required = DateTime.Now - start;
                    Console.WriteLine("---- NPOI方式 ----");
                    Console.WriteLine("Time required : {0} seconds.", required);
                    Console.WriteLine("Read/Write    : {0} records.", lastRow + 1);
                }
            }
            catch (Exception ex)
            {
                TimeSpan required = DateTime.Now - start;
                Console.WriteLine("---- NPOI方式 ----");
                Console.WriteLine("Time required : {0} seconds.", required);
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (workbook != null) workbook.Close();
                File.Delete(saveFile + "#");
            }
        }
    }
}

実行結果

dynamicとInvokeMemberはほぼ同等、ClosedXMLやNPOIの方が遅い気がします。 また、データ量が多いとClosedXMLやNPOIはOutOfMemoryが発生してしまいました。 まだライブラリとして発展途上なのでしょう。
でも、Officeインストール不要というのはかなりのメリットですね。今後に期待します。

300,000件

700,000件

測定結果

実行前(input.xlsx)

実行後(output.xlsx)