2015年8月30日日曜日

Range vs Cells(1セルを取得)


A1を1000回取得する

Case1:get_Range
Case2:Cells

using System;
using System.Diagnostics;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExcelTest
{
    class Program
    {
        static void Main(string[] args)
        {
            Excel.Application xlApplication = null;
            Excel.Workbooks xlWorkbooks = null;
            Excel.Workbook xlWorkbook = null;
            Excel.Sheets xlSheets = null;
            Excel.Worksheet xlWorksheet = null;

            try
            {
                xlApplication = new Excel.Application();
                xlApplication.Visible = true;
                xlWorkbooks = xlApplication.Workbooks;
                xlWorkbook = xlWorkbooks.Add();
                xlSheets = xlWorkbook.Sheets;
                xlWorksheet = xlSheets[1];

                Case1(xlWorksheet);               
                Case2(xlWorksheet);

                xlWorkbooks.Close();
                xlApplication.Quit();
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                if (xlSheets != null)
                {
                    Marshal.FinalReleaseComObject(xlSheets);
                }

                if(xlWorkbook != null)
                {
                    Marshal.FinalReleaseComObject(xlWorkbook);
                }

                if (xlWorkbooks != null)
                {
                    Marshal.FinalReleaseComObject(xlWorkbooks);
                }

                if (xlApplication != null)
                {
                    Marshal.FinalReleaseComObject(xlApplication);
                }
            }
        }

        static void Case1(Excel.Worksheet xlWorksheet)
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            Excel.Range xlRange = null;
            for (int i = 0; i < 1000; i++)
            {
                xlRange = xlWorksheet.get_Range("A1");
                Marshal.FinalReleaseComObject(xlRange);
            }
            sw.Stop();
            Console.WriteLine("[{0}]{1}", MethodBase.GetCurrentMethod().Name, sw.Elapsed);
        }

        static void Case2(Excel.Worksheet xlWorksheet)
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            Excel.Range xlRange = null;
            for (int i = 0; i < 1000; i++)
            {
                xlRange = xlWorksheet.Cells[1, 1];
                Marshal.FinalReleaseComObject(xlRange);
            }
            sw.Stop();
            Console.WriteLine("[{0}]{1}", MethodBase.GetCurrentMethod().Name, sw.Elapsed);
        }
    }
}
[Case1]00:00:04.5715318
[Case2]00:00:08.4989196

やはり、get_Rangeが速い模様

0 件のコメント:

コメントを投稿