ClosedXML で DataTable の内容を出力する方法

.NET フレームワークでは、表組みデータを扱うためには DataTable 型のオブジェクトがよく利用されます。

ここでは DataTable で与えられたデータを、ClosedXML を利用してエクセルシートに書き出す方法を説明します。

もっとも基本的な方法は、行毎、列毎にループさせて内容を１セル毎に書き出す方法ですが、ここでは 1 セル毎に独自で書き出すのではなく、 ClosedXML の機能を用いてガバッと一気に書き出してみます。

利用したデータはかの Northwind データベースです。

Northwind データベースは昔からサンプルコードによく登場するデータベースで、マイクロソフトのダウンロードセンターからダウンロード可能です。SQL サーバー上に構築可能です。

この中の Products テーブルを使います。

次のコードでは Northwind データベースに接続して、Products テーブルの内容を DataTable に取り込みます。そして、その DataTable を ClosedXML を用いて、「ワークシートに追加」します。これだけで DataTable の内容がエクセルシートに出力されます。

using ClosedXML.Excel;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;

namespace ClosedXMLTest4
{
    class Program
    {
        static void Main(string[] args)
        {
            var dt = GetNorthwindProductTable();
            Debug.Assert(dt != null);
            var workbook = new XLWorkbook();
            var worksheet = workbook.Worksheets.Add(dt);
            workbook.SaveAs(@"C:\Temp\test_table.xlsx");
        }

        static DataTable GetNorthwindProductTable()
        {
            try {
                var dt = new DataTable("Products");
                
                using (var conn = new SqlConnection(@"your connection string"))
                {
                    var cmd = conn.CreateCommand();
                    cmd.CommandText = "SELECT * FROM Products";
                    var sda = new SqlDataAdapter(cmd);
                    sda.Fill(dt);
                }

                return dt;
            }
            catch(Exception ex)
            {
                Debug.Write(ex.Message);
            }

            return null;
        }
    }
}

この実行結果は次の通りです。

ポイントは Workbook の Worksheets コレクションに DataTable を追加しているところです。

worksheet.Worksheets.Add メソッドでは DataTable オブジェクトを第一引数にとります。第二引数はワークシート名なります。上記のように DataTable しか渡していない場合は、DataTable の TableName プロパティがシート名 (タブ名) になります。

ちなみに、上記は新規ワークシートを作成して、それを Worksheets コレクションに追加。そのワークシートのタブ名を (既定で) TableName をセットする。そして、セル A1 にテーブルを追加する、という動きと同じです。

もしセル B2 を左上とするようにテーブルを出力する場合は、次のように DataTable を追加するセルを明示的に指定すれば OK です。

        var dt = GetNorthwindProductTable();
        Debug.Assert(dt != null);
        var workbook = new XLWorkbook();
        var worksheet = workbook.Worksheets.Add("Tab 1");
        worksheet.Cell("B2").InsertTable(dt);

以上、DataTable の内容を出力する方法を紹介しました。