DevExpress社ASP.NETを利用して、SQL Serverのテーブルデータをxlsx形式でダウンロードする

DevExpress社ASP.NETの、グリッド（ASPxGridView）のダウンロードは、ASPxGridViewExporterというクラスが用意されているが、以下のような制約があり使い勝手が悪い。そこで、DevExpress社WinFormsの、グリッド（GridView）クラスを使って、自前でダウンロード処理を実装する。

ASPxGridViewExporterの制約

画面で表示されている項目しかダウンロードされない
リボンやコンテキストメニューなど任意のアクションで処理ができない

処理シーケンス

簡単な処理シーケンスは以下の通り

ソースの解説

test.js

Javascriptからは、リンクのhtmlタグを動的に生成し、クリックイベントを起こす

    var tablename = '';
    var link = document.createElement('a');
    link.href = '../../Controllers/Export/ExportXlsx/' + tablename;
    link.click();

var tablename = '';

var link = document.createElement('a');

link.href = '../../Controllers/Export/ExportXlsx/' + tablename;

link.click();

ExportController.cs

ASP.NETでは、ダウンロード用のApiControllerを継承したクラスを作成し、各拡張子に対応したメソッドを作成する。
※GridViewを使用する際は、必ず画面(XtraFormクラス)を生成する。画面を生成しなかった場合は、xlsxは出力されるが、xlsxに内容が出力されない。

    public class ExportController : ApiController
    {
        [HttpGet]
        public HttpResponseMessage ExportXlsx(string tablename)
        {
            var response = new HttpResponseMessage(HttpStatusCode.OK);
            using (var dbContext = new TestDbContext())
            {
                var datatable = dbContext.GetDataTable(tableName);
                var form = new DevExpress.XtraEditors.XtraForm();
                var gridControl = new GridControl();
                gridControl.MainView = new DevExpress.XtraGrid.Views.Grid.GridView();
                gridControl.ViewCollection.AddRange(new DevExpress.XtraGrid.Views.Base.BaseView[] { gridControl.MainView });
                gridControl.MainView.GridControl = gridControl;
                gridControl.MainView.Name = "gridView1";
                form.Controls.Add(gridControl);

                gridControl.DataSource = datatable;
                gridControl.RefreshDataSource();

                var odir = ConfigurationManager.AppSettings["output.path"];
                var nwdt = DateTime.Now;
                var mediaType = mediaType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                var fpath = $"{odir}\\{nwdt:yyyy}\\{nwdt:MM}\\{nwdt:dd}\\{id}_{nwdt:yyyyMMdd_HHmmss}.xlsx";

                var fileName = Path.GetFileName(fpath);
                response.Content = new StreamContent(new FileStream(fpath, FileMode.Open, FileAccess.Read));
                response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");
                response.Content.Headers.ContentDisposition.FileName = $"{fileName}";
                response.Content.Headers.ContentType = new MediaTypeHeaderValue(mediaType);
            }

            return response;
        }
    }

public class ExportController : ApiController

{

[HttpGet]

public HttpResponseMessage ExportXlsx(string tablename)

{

var response = new HttpResponseMessage(HttpStatusCode.OK);

using (var dbContext = new TestDbContext())

{

var datatable = dbContext.GetDataTable(tableName);

var form = new DevExpress.XtraEditors.XtraForm();

var gridControl = new GridControl();

gridControl.MainView = new DevExpress.XtraGrid.Views.Grid.GridView();

gridControl.ViewCollection.AddRange(new DevExpress.XtraGrid.Views.Base.BaseView[] { gridControl.MainView });

gridControl.MainView.GridControl = gridControl;

gridControl.MainView.Name = "gridView1";

form.Controls.Add(gridControl);

gridControl.DataSource = datatable;

gridControl.RefreshDataSource();

var odir = ConfigurationManager.AppSettings["output.path"];

var nwdt = DateTime.Now;

var mediaType = mediaType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

var fpath = $"{odir}\\{nwdt:yyyy}\\{nwdt:MM}\\{nwdt:dd}\\{id}_{nwdt:yyyyMMdd_HHmmss}.xlsx";

var fileName = Path.GetFileName(fpath);

response.Content = new StreamContent(new FileStream(fpath, FileMode.Open, FileAccess.Read));

response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment");

response.Content.Headers.ContentDisposition.FileName = $"{fileName}";

response.Content.Headers.ContentType = new MediaTypeHeaderValue(mediaType);

}

return response;

}

TestDbContext.cs

SQL Serverからのテーブルデータ取得先は、DataTableに格納する。
※カラムコメントをグリッドの項目名にするため、SQL Serverのテーブルにカラムコメントを記述しておく。カラムコメントがない場合は、項目が生成されない。

    public partial class TestDbContext : DbContext
    {
        public DataTable GetDataTable(string tableName)
        {
            var dataTable = new DataTable();
            dataTable.TableName = tableName;
            this.SetDataColumns(dataTable);
            var connection = this.Database.Connection;
            var dbFactory = DbProviderFactories.GetFactory(connection);
            var columns = dataTable.Columns.Cast().ToList();
            var columns2 = columns.Select(x => x.ColumnName == "geom" ? "geom.STAsText() as wkt" : x.ColumnName).ToArray();
            var columnStr = string.Join(",", columns2);
            var sql = $"SELECT {columnStr} FROM {tableName}";
            using (var cmd = dbFactory.CreateCommand())
            {
                cmd.Connection = connection;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = sql;
                using (var adapter = dbFactory.CreateDataAdapter())
                {
                    adapter.SelectCommand = cmd;
                    adapter.Fill(dataTable);
                }
            }
            return dataTable;
        }
    }
    private void SetDataColumns(DataTable table)
    {
        var columns = this.GetColumnInfos(table.TableName);
        var columnNames = table.Columns.Cast().Select(x => x.ColumnName).ToList();
        foreach (var column in columns)
        {
            var dataColumn = new DataColumn(column.ColumnName, ColumnUtil.GetClassType(column.DataType));
            if (!columnNames.Contains(column.ColumnName))
            {
                dataColumn.Caption = column.Caption;
                dataColumn.ExtendedProperties.Add("ColumnInfo", column);
                table.Columns.Add(dataColumn);
            }
        }
    }
    public List GetColumnInfos(string tablename)
    {
        var columns = new List();
        var sql = new StringBuilder();
        sql.Append($"SELECT");
        sql.Append($"  t.name   AS table_name,");
        sql.Append($"  c.name   AS column_name,");
        sql.Append($"  ep.value AS commnet,");
        sql.Append($"  tp.name  AS data_type,");
        sql.Append($"  c.max_length ");
        sql.Append($"FROM");
        sql.Append($"  sys.tables AS t,");
        sql.Append($"  sys.columns AS c,");
        sql.Append($"  sys.extended_properties AS ep,");
        sql.Append($"  sys.types AS tp ");
        sql.Append($"WHERE ");
        sql.Append($"  t.name = '{tablename}' AND");
        sql.Append($"  t.object_id = c.object_id AND");
        sql.Append($"  c.object_id = ep.major_id AND");
        sql.Append($"  c.column_id = ep.minor_id AND");
        sql.Append($"  c.user_type_id = tp.user_type_id");

        var dataTable = new DataTable();
        var connection = this.Database.Connection;
        var dbFactory = DbProviderFactories.GetFactory(connection);
        using (var cmd = dbFactory.CreateCommand())
        {
            cmd.Connection = connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql.ToString();
            using (var adapter = dbFactory.CreateDataAdapter())
            {
                adapter.SelectCommand = cmd;
                adapter.Fill(dataTable);
            }
        }

        foreach (DataRow row in dataTable.Rows)
        {
            var column = new ColumnInfo()
            {
                ColumnName = row["column_name"].ToString(),
                Caption = row["commnet"].ToString(),
                DataType = ColumnUtil.ConvClassType(row["data_type"].ToString()).FullName,
                OrderNo = columns.Count
            };

            if (column.ColumnName == "geom")
            {
                column.DataType = typeof(System.Byte[]).FullName;
            }

            columns.Add(column);
        }
        return columns;
    }
}

public partial class TestDbContext : DbContext

{

public DataTable GetDataTable(string tableName)

{

var dataTable = new DataTable();

dataTable.TableName = tableName;

this.SetDataColumns(dataTable);

var connection = this.Database.Connection;

var dbFactory = DbProviderFactories.GetFactory(connection);

var columns = dataTable.Columns.Cast().ToList();

var columns2 = columns.Select(x => x.ColumnName == "geom" ? "geom.STAsText() as wkt" : x.ColumnName).ToArray();

var columnStr = string.Join(",", columns2);

var sql = $"SELECT {columnStr} FROM {tableName}";

using (var cmd = dbFactory.CreateCommand())

{

cmd.Connection = connection;

cmd.CommandType = CommandType.Text;

cmd.CommandText = sql;

using (var adapter = dbFactory.CreateDataAdapter())

{

adapter.SelectCommand = cmd;

adapter.Fill(dataTable);

}

return dataTable;

}

private void SetDataColumns(DataTable table)

{

var columns = this.GetColumnInfos(table.TableName);

var columnNames = table.Columns.Cast().Select(x => x.ColumnName).ToList();

foreach (var column in columns)

{

var dataColumn = new DataColumn(column.ColumnName, ColumnUtil.GetClassType(column.DataType));

if (!columnNames.Contains(column.ColumnName))

{

dataColumn.Caption = column.Caption;

dataColumn.ExtendedProperties.Add("ColumnInfo", column);

table.Columns.Add(dataColumn);

}

public List GetColumnInfos(string tablename)

{

var columns = new List();

var sql = new StringBuilder();

sql.Append($"SELECT");

sql.Append($" t.name AS table_name,");

sql.Append($" c.name AS column_name,");

sql.Append($" ep.value AS commnet,");

sql.Append($" tp.name AS data_type,");

sql.Append($" c.max_length ");

sql.Append($"FROM");

sql.Append($" sys.tables AS t,");

sql.Append($" sys.columns AS c,");

sql.Append($" sys.extended_properties AS ep,");

sql.Append($" sys.types AS tp ");

sql.Append($"WHERE ");

sql.Append($" t.name = '{tablename}' AND");

sql.Append($" t.object_id = c.object_id AND");

sql.Append($" c.object_id = ep.major_id AND");

sql.Append($" c.column_id = ep.minor_id AND");

sql.Append($" c.user_type_id = tp.user_type_id");

var dataTable = new DataTable();

var connection = this.Database.Connection;

var dbFactory = DbProviderFactories.GetFactory(connection);

using (var cmd = dbFactory.CreateCommand())

{

cmd.Connection = connection;

cmd.CommandType = CommandType.Text;

cmd.CommandText = sql.ToString();

using (var adapter = dbFactory.CreateDataAdapter())

{

adapter.SelectCommand = cmd;

adapter.Fill(dataTable);

}

foreach (DataRow row in dataTable.Rows)

{

var column = new ColumnInfo()

{

ColumnName = row["column_name"].ToString(),

Caption = row["commnet"].ToString(),

DataType = ColumnUtil.ConvClassType(row["data_type"].ToString()).FullName,

OrderNo = columns.Count

};

if (column.ColumnName == "geom")

{

column.DataType = typeof(System.Byte[]).FullName;

}

columns.Add(column);

}

return columns;

}

取得する画面のイメージ

ダウンロードしたのxlsxのイメージ

DevExpress社WinFormsの、グリッド（GridView）クラスを利用すれば、webシステムでも簡単にエクスポート処理ができることが確認できた。
GridViewクラスは他にも、csvやpdfなどの出力メソッドが提供されているため、他の拡張子にも応用できる。

月	火	水	木	金	土	日
		1	2	3	4	5
6	7	8	9	10	11	12
13	14	15	16	17	18	19
20	21	22	23	24	25	26
27	28	29	30	31