Excelメモ

■値の取得
セルの値をまとめて取得する

■その他
Excel列名の変換（数字⇔文字列）

■パフォーマンス
Range vs Cells（１セルを取得）
Range vs Cells（範囲を取得）
Excel 100×10のデータを取得する方法の比較

Excel 100×10のデータを取得する方法の比較

A1～J100の範囲のデータを取得する

Case1:Cellsでぐるぐる回しながら取得する
Case2:get_Rangeでぐるぐる回しながら取得する
Case3:範囲のデータを１度に取得する

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);
                Case3(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 r = 1; r <= 100; r++)
            {
                for (int c = 1; c <= 10; c++)
                {
                    xlRange = xlWorksheet.Cells[r, c];
                    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;
            string[] column = new string[]{ string.Empty, "A", "B", "C", "D", "E", "F", "G", "H", "I", "J"};
            for (int r = 1; r <= 100; r++)
            {
                for (int c = 1; c <= 10; c++)
                {
                    xlRange = xlWorksheet.get_Range(column[c] + r.ToString());
                    Marshal.FinalReleaseComObject(xlRange);
                }
            }
            sw.Stop();
            Console.WriteLine("[{0}]{1}", MethodBase.GetCurrentMethod().Name, sw.Elapsed);
        }

        static void Case3(Excel.Worksheet xlWorksheet)
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();
            object[,] cellData = new object[100, 10];
            cellData = xlWorksheet.get_Range("A1", "J100").Value;

            sw.Stop();
            Console.WriteLine("[{0}]{1}", MethodBase.GetCurrentMethod().Name, sw.Elapsed);
        }
    }
}

[Case1]00:00:24.1975109
[Case2]00:00:06.7251560
[Case3]00:00:00.0383916

比較するまでもないよな。。。

Range vs Cells（１セルを取得）

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が速い模様

Range vs Cells（範囲を取得）

A1:A10の範囲を1000回取得する。現実的にある数値にしてみた。

Case1:get_Range
Case2:Cells 一度Rangeに置き換えてからCells
Case3:Cells 型変換して直接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);
                Case3(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", "A10");
                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 stRange = null;
            Excel.Range edRange = null;
            Excel.Range xlRange = null;
            for (int i = 0; i < 1000; i++)
            {
                stRange = xlWorksheet.Cells[1, 1];
                edRange = xlWorksheet.Cells[1, 10];
                xlRange = xlWorksheet.get_Range(stRange, edRange);
                Marshal.FinalReleaseComObject(stRange);
                Marshal.FinalReleaseComObject(edRange);
                Marshal.FinalReleaseComObject(xlRange);
            }
            sw.Stop();
            Console.WriteLine("[{0}]{1}", MethodBase.GetCurrentMethod().Name, sw.Elapsed);
        }

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

[Case1]00:00:04.4432538
[Case2]00:00:19.4220595
[Case3]00:00:18.9210169

get_Rangeが速い模様。

stylecopをexpress2010に導入のメモ

StyleCopは、こちらからダウンロード。


[Project名].csproj

<import Project="$(MSBuildToolsPath)\Microsoft.CSharp.targets" />の下に以下のように追加。

<Import Project="$(MSBuildToolsPath)\Microsoft.CSharp.targets" />
<Import Condition="Exists('$(ProgramFiles)\MSBuild\StyleCop\v4.7\StyleCop.targets') and '$(Configuration)' != 'Debug'"
Project="$(ProgramFiles)\MSBuild\StyleCop\v4.7\StyleCop.targets" />

存在チェックとリリース時のみ実行の書き方。

メモALL

■初期化
・配列
string[] str = {"aaa", "bbb", "ccc"};
int[] num = {1, 2, 3, 4, 5};
List list = new List {1, 2, 3};



・structの初期化

using System.Collections.Generic;

namespace SampleCode {

    class Program {
        static void Main(string[] args) {

            List list = new List();
            list.Add(new MyClass { age = 3, name = "bbb" });
            list.Add(new MyClass { age = 10, name = "aaa"});
        }
    }

    struct MyClass {
        public string name { get; set; }
        public int age { get; set; }
    }
}

LINQメモ

・LINQ SampleCode
ど忘れした時のための参考サンプルコード

・メソッド一覧
Aggregate
All
Any
Count
DefaultIfEmpty

・いろいろ
大文字、小文字を無視してDistinct