[C#] ExcelファイルをDataGridViewに表示する

1. はじめに

ExcelのファイルをDataGridViewに表示する忘備録になります。

Microsoft.ACE.OLEDB.12.0 を使用できれば、SQLライクに楽に取得できるみたいですが、64bit版Windows環境ではコンポーネントがインストールされない(場合がある)ようです。
Microsoft Access データベース エンジン 201x 再頒布可能コンポーネント　をインストールできない環境を想定した環境での実現方法となります。

2. 概要

もっと簡潔な方法があるのかもしれませんが、実用性重視ということで。。。

• Microsoft.Office.Interop.Excel で Excel ファイルを読み込む
• ExcelのRangeを二次元配列で取得する
• 二次元配列からジャグ配列に乗せ換える
• DataGridViewで表示する

DtaDridViewのDataSourceを理解してなかったので、二次元配列を直接DataGridViewに食わせられないかと四苦八苦しました。
足掻いた後やっと無理だとわかり、ジャグ配列を食わせる事で実現しています。(ExcelのRangeがジャグ配列で取得できればなぁ・・・)

3. 詳細

3.1 Microsoft.Office.Interop.Excel で Excel ファイルを読み込む

「[C#]Excelファイルのセル読取/書込」 に忘備録があるので、割愛しています。

3.2 二次元配列からジャグ配列に乗せ換える

C#初心者なので、躓きまくりました。。。

「【C#】多次元配列とジャグ配列（２次元配列）のサイズ（長さ）、相互変換など」 を参考にさせていただいてます。(というか、まんまです)
(どなたか Linq を使った改善方法を教えてください・・・)

3.3 DataGridViewで表示する

こちらもC#初心者なので、躓きまくりです。。。
「DataGridViewのDataSourceに指定できるオブジェクトについての考察」 を参考にさせていただきました。

3.4 サンプルコード

Form1.csのデザインには、DataGridView dataGridView1を貼り付けてます。

Form1.cs

using System;
using System.Data;
using System.Windows.Forms;

namespace WindowsFormsApp4
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        /// <summary>
        /// Excelファイルを読み込んで、DataGridViewに表示する
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        /// <remarks>UsedRange→二次元配列→ジャグ配列→DataTable→DataGridView</remarks>
        private void Form1_Load(object sender, EventArgs e)
        {
            object[][] jagUsedRange = null;

            using (var excel = new ExcelManager())
            {
                excel.Open(@"e:\test1.xlsx");
                object[,] twoDimUsedRange = excel.GetUsedRange();
                jagUsedRange = ToJaggedArray(twoDimUsedRange);
            }

            var dt = new DataTable();
            for (int colIndex = 0; colIndex < jagUsedRange[0].Length; colIndex++)
            {
                dt.Columns.Add();
            }
            for (int rowIndex = 0; rowIndex < jagUsedRange.Length; rowIndex++)
            {
                dt.Rows.Add(jagUsedRange[rowIndex]);
            }

            dataGridView1.DataSource = dt;
        }

        /// <summary>
        /// 二次元配列をジャグ配列に変換
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        /// <remarks>ExcelのRangeは、(1,1)始まりなので、(+1,+1)オフセットして取得</remarks>
        public T[][] ToJaggedArray<T>(T[,] twoDimArray)
        {
            int rowLength = twoDimArray.GetLength(0);
            int colLength = twoDimArray.GetLength(1);

            T[][] jagArray = new T[rowLength][];

            for (int columnIndex  = 0; columnIndex  < rowLength; columnIndex ++)
            {
                jagArray[columnIndex ] = new T[colLength];
                for (int rowIndex = 0; rowIndex < colLength; rowIndex++)
                {
                    jagArray[columnIndex ][rowIndex] = twoDimArray[columnIndex  + 1, rowIndex + 1];
                }
            }

            return jagArray;
        }
    }
}

ExcelManager.cs

using Microsoft.Office.Interop.Excel;
using System;
using System.Runtime.InteropServices;

namespace WindowsFormsApp4
{
    class ExcelManager : IDisposable
    {
        /// <summary>
        /// Excel操作用オブジェクト
        /// </summary>
        private Application _excel = new Application()
        {
            // 非表示
            Visible = false,
        };
        private Workbook _workbook = null;
        private Worksheet _worksheet = null;

        /// <summary>
        /// Excelワークブックを開く
        /// </summary>
        /// <param name="filename">ファイル名</param>
        /// <param name="sheetname">シート名</param>
        public void Open(string filename, string sheetname = "Sheet1")
        {
            try
            {
                // Bookを開く
                _workbook = _excel.Workbooks.Open(filename);

                // 対象シートを設定する
                _worksheet = _workbook.Worksheets[sheetname];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// UsedRangeを二次元配列で取得する
        /// </summary>
        /// <returns>UsedRangeの二次元配列</returns>
        public object[,] GetUsedRange()
        {
            // Usedrangeを二次元配列に格納する
            return _worksheet.UsedRange.Value;
        }

        private bool disposedValue = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!disposedValue)
            {
                if (disposing)
                {
                    // TODO: Managed Objectの破棄
                }

                if (_workbook != null)
                {
                    _workbook.Close();
                    Marshal.ReleaseComObject(_workbook);
                    _workbook = null;
                }

                if (_excel != null)
                {
                    _excel.Quit();
                    Marshal.ReleaseComObject(_excel);
                    _excel = null;
                }

                disposedValue = true;
            }
        }

        ~ExcelManager()
        {
            Dispose(false);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }
}

4. コンポーネントをインストールした場合の実現方法

Microsoft Access データベース エンジン 201x 再頒布可能コンポーネント　をインストールできる場合の実現方法もまとめておきます。

4.1 コンポーネント準備

クイック実行形式の Office をインストールすると ODBC / OLEDB が利用できない を参考にコンポーネントをインストールします。

32bit Officeをインストールした環境では、32bit版コンポーネント AccessRuntime_x86_ja-jp.exe をインストールしてください。

4.2 概要

手順は、下記になります。

• Microsoft.ACE.OLEDB.12.0 を利用して、Excel ファイルを読み込む
• DataGridViewで表示する

4.3 サンプルコード

「C# : Excel を データベースとして DataGridView に読み込む」 を参考にさせていただきました。
やはり、こちらの方が簡単ですね。。。

Form1.cs

using System;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;

namespace WindowsFormsApp4
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        /// <summary>
        /// Excelファイルを読み込んで、DataGridViewに表示する
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Form1_Load(object sender, EventArgs e)
        {
            var dt = new DataTable();

            using (var oleDbConnection = new OleDbConnection())
            using (var oleDbCommand = new OleDbCommand())
            {

                // SQL文字列格納用
                var query = "select * from [sheet1$]";

                // 接続文字列の作成
                oleDbConnection.ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"", @"e:\test1.xlsx");

                try
                {
                    oleDbConnection.Open();
                    oleDbCommand.Connection = oleDbConnection;
                    oleDbCommand.CommandType = CommandType.Text;
                }
                catch (Exception ex)
                {
                    throw ex;
                }

                oleDbCommand.CommandText = query;

                try
                {
                    using (OleDbDataReader myReader = oleDbCommand.ExecuteReader())
                    {
                        dt.Load(myReader);
                        myReader.Close();
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }

                dataGridView1.DataSource = dt;
            }
        }
    }
}