Open XMLを使った複数シートのExcelブックへの書込みについて

Question

Visual Studioで開発中のASP.NET（C#）のプロジェクトについてです。
Open XMLを使って複数のシートを持つExcelブックを出力するボタンを設けようとしています。
Excelのデータには、SQL ServerでFETCHとPIVOT句を使い同じ列数で複数の出力がされるストアドプロシジャーを用意しました。
また各出力の１列目はそれぞれ異なる（出力内では同一）ため、その１列目をシート名にしようと考えました。
SqlDataReader.NextResult メソッドで、順次ストアドプロシジャーの出力を異なるシートに吐き出そうという意図で、
以下のようなスクリプトを書きました。

しかし実行させてみたところ、目論見通り異なるシート名はできるのですが、データが複数の出力がひとつにマージされた状態で
全てのシートで同じ結果が表示されてしまいます。なにかよい解決策がありますでしょうか。
ご教授いただけましたら幸いです。 

using System;
using System.IO;
using System.Linq;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.Reflection;

   
   public void Button_Click(object sender, EventArgs e)
    {

        SpreadsheetDocument document = SpreadsheetDocument.Create("Test.xlsx", SpreadsheetDocumentType.Workbook, true);
        WorkbookPart wbpart = document.AddWorkbookPart();
        wbpart.Workbook = new Workbook();
        WorksheetPart wspart = wbpart.AddNewPart<WorksheetPart>();
        SheetData sheetData = new SheetData();
        wspart.Worksheet = new Worksheet(sheetData);
        Sheets sheets = wbpart.Workbook.AppendChild<Sheets>(new Sheets());

        Sheet sheet = new Sheet();
        Row row = new Row();
        Cell cell = new Cell();

        var constr = ConfigurationManager.ConnectionStrings[【サーバ名】 + "ConnectionString"].ConnectionString;
        SqlConnection con = new SqlConnection(constr);

        con.Open();
        SqlCommand cmd = con.CreateCommand();
        SqlTransaction tx = con.BeginTransaction(System.Data.IsolationLevel.Serializable);
        cmd.Transaction = tx;
        cmd.CommandText = "EXEC 【ストアドプロシジャー及びパラメーター】";
        SqlDataReader dr = cmd.ExecuteReader();

        DocumentFormat.OpenXml.UInt32Value m = 1;
        do
        {
            int ln = 1;
            if (dr.HasRows)
            {
                //まず１レコード目を取りに行く
                dr.Read();
                {
                //１列目のデータを取得して、シート名にする
                    sheet = new Sheet() { Id = wbpart.GetIdOfPart(wspart), SheetId = m, Name = dr.GetValue(0).ToString() };
                    sheets.Append(sheet);
                    row = new Row();

                    if (ln == 1)
                    {
                    //項目名（ヘッダ）を取りに行く
                        for (int i = 0; i <= dr.FieldCount - 2; i++)
                        {
                            cell = new Cell();
                            cell.DataType = CellValues.String;
                            cell.CellReference = ((char)97 + i) + ln.ToString();
                            cell.CellValue = new CellValue(dr.GetName(i).ToString());
                            row.Append(cell);
                        }
                        sheetData.Append(row);
                        row = new Row();
                    }

                    ln = 2;
                    for (int j = 0; j <= dr.FieldCount - 2; j++)
                    {
                    //値を取りに行く
                        cell = new Cell();
                        switch (dr.GetDataTypeName(j))
                        {
                            case "nvarchar":
                                cell.DataType = CellValues.String;
                                break;
                            case "varchar":
                                cell.DataType = CellValues.String;
                                break;
                            case "int":
                                cell.DataType = CellValues.Number;
                                break;
                            case "float":
                                cell.DataType = CellValues.Number;
                                break;
                            case "datetime":
                                cell.DataType = CellValues.Date;
                                break;
                        }
                        cell.CellReference = ((char)97 + j) + ln.ToString();
                        cell.CellValue = new CellValue(dr.GetValue(j).ToString());
                        row.Append(cell);
                        ln++;
                    }
                    sheetData.Append(row);
                    row = new Row();
                }
                //２レコード目以降の値を取りに行く
                while (dr.Read())
                {
                    row = new Row();

                    ln = 3;
                    for (int j = 0; j <= dr.FieldCount - 2; j++)
                    {
                        cell = new Cell();
                        switch (dr.GetDataTypeName(j))
                        {
                            case "nvarchar":
                                cell.DataType = CellValues.String;
                                break;
                            case "varchar":
                                cell.DataType = CellValues.String;
                                break;
                            case "int":
                                cell.DataType = CellValues.Number;
                                break;
                            case "float":
                                cell.DataType = CellValues.Number;
                                break;
                            case "datetime":
                                cell.DataType = CellValues.Date;
                                break;
                        }
                        cell.CellReference = ((char)97 + j) + ln.ToString();
                        cell.CellValue = new CellValue(dr.GetValue(j).ToString());
                        row.Append(cell);
                        ln++;
                    }
                    sheetData.Append(row);
                }
                m++;
                sheetData = new SheetData();
            }
            //次のストアド・プロシージャーの出力へ移る
        } while (dr.NextResult());

        dr.Close();
        tx.Commit();
        con.Close();
        document.Close();
    }

Answer 1

自己レスというか補足です。下から数えて１０行目。dr.NextResult())直前の

sheetData = new SheetData();

は余分でした。
余分というか、これを入れると全てのシートで最初のストアドプロシジャーの出力だけが表示されるようになり、
この１行を削除すると、全てのシートで最初から最後までのストアドプロシジャーの出力が表示されるようになるようです。

Answer 2

ワークシートのデータを格納しているsheetDataが１回しか登録されてないです。
sheets.Appendしても最初に作ったsheetDataしか登録されてないので、それを参照してるために全ページで同じ内容が表示されることになります。

下のほうにあるsheetData=new sheetData()は、それをどこにも登録していないので残りません。
消したら全出力が表示されるのは、最初に作ったsheeetDataにどんどん行追加していってるからです。

作りたいワークシート毎にWorksheetPartを作りましょう。

SpreadsheetDocument document = SpreadsheetDocument.Create("Test.xlsx", SpreadsheetDocumentType.Workbook, true);
WorkbookPart wbpart = document.AddWorkbookPart();
wbpart.Workbook = new Workbook();

//ここで一回だけしかシートを登録してない
//WorksheetPart wspart = wbpart.AddNewPart<WorksheetPart>();
//SheetData sheetData = new SheetData();
//wspart.Worksheet = new Worksheet(sheetData);

Sheets sheets = wbpart.Workbook.AppendChild<Sheets>(new Sheets());

Sheet sheet = new Sheet();
Row row = new Row();
Cell cell = new Cell();

var constr = ConfigurationManager.ConnectionStrings[【サーバ名】 + "ConnectionString"].ConnectionString;            
System.Data.SqlClient.SqlConnection con = new SqlConnection(constr);

con.Open();
SqlCommand cmd = con.CreateCommand();
SqlTransaction tx = con.BeginTransaction(System.Data.IsolationLevel.Serializable);
cmd.Transaction = tx;
cmd.CommandText = "EXEC 【ストアドプロシジャー及びパラメーター】"; 
SqlDataReader dr = cmd.ExecuteReader();

DocumentFormat.OpenXml.UInt32Value m = 1;
do
{
    int ln = 1;
    if (dr.HasRows)
    {
        //クエリ結果毎にシートを作るならここで作って登録する
        WorksheetPart wspart = wbpart.AddNewPart<WorksheetPart>();//ワークブックに新しいワークシートを追加(ワークシートの中身ではなく、ワークシートへの索引のようなもの)
        SheetData sheetData = new SheetData();//シート内容(シートの行・列など)を格納するための新しいSheetDataを作る
        wspart.Worksheet = new Worksheet(sheetData);//索引と中身を関連付ける


        dr.Read();
        {
            sheet = new Sheet() { Id = wbpart.GetIdOfPart(wspart), SheetId = m, Name = dr.GetValue(0).ToString() };
            sheets.Append(sheet);
            row = new Row();

            if (ln == 1)
            {
                for (int i = 0; i <= dr.FieldCount - 2; i++)
                {
                    cell = new Cell();
                    cell.DataType = CellValues.String;
                    cell.CellReference = ((char)97 + i) + ln.ToString();
                    cell.CellValue = new CellValue(dr.GetName(i).ToString());
                    row.Append(cell);
                }
                sheetData.Append(row);
                row = new Row();
            }

            ln = 2;
            for (int j = 0; j <= dr.FieldCount - 2; j++)
            {
                cell = new Cell();
                switch (dr.GetDataTypeName(j))
                {
                    case "nvarchar":
                        cell.DataType = CellValues.String;
                        break;
                    case "varchar":
                        cell.DataType = CellValues.String;
                        break;
                    case "int":
                        cell.DataType = CellValues.Number;
                        break;
                    case "float":
                        cell.DataType = CellValues.Number;
                        break;
                    case "datetime":
                        cell.DataType = CellValues.Date;
                        break;
                }
                cell.CellReference = ((char)97 + j) + ln.ToString();
                cell.CellValue = new CellValue(dr.GetValue(j).ToString());
                row.Append(cell);
                ln++;
            }
            sheetData.Append(row);
            row = new Row();
        }

        while (dr.Read())
        {
            row = new Row();

            ln = 3;
            for (int j = 0; j <= dr.FieldCount - 2; j++)
            {
                cell = new Cell();
                switch (dr.GetDataTypeName(j))
                {
                    case "nvarchar":
                        cell.DataType = CellValues.String;
                        break;
                    case "varchar":
                        cell.DataType = CellValues.String;
                        break;
                    case "int":
                        cell.DataType = CellValues.Number;
                        break;
                    case "float":
                        cell.DataType = CellValues.Number;
                        break;
                    case "datetime":
                        cell.DataType = CellValues.Date;
                        break;
                }
                cell.CellReference = ((char)97 + j) + ln.ToString();
                cell.CellValue = new CellValue(dr.GetValue(j).ToString());
                row.Append(cell);
                ln++;
            }
            sheetData.Append(row);
        }
        m++;


        //シートの中身(SheetData)だけ作ってどこにも登録されていない
        //sheetData = new SheetData();
    }

} while (dr.NextResult());

dr.Close();
tx.Commit();
con.Close();
document.Close();

---

個別に明示されていない限りgekkaがフォーラムに投稿したコードにはフォーラム使用条件に基づき「MICROSOFT LIMITED PUBLIC LICENSE」が適用されます。(かなり自由に使ってOK!)

Answer 3

gekka様　

素晴しいです！まさにこれをしたかったのです。ありがとうございます。

Answer 4

既に解決されていますし、原因はgekkaさんの指摘の通りですが、

この問題の根本の原因は変数の使い回しにあると思います。変数に新しいインスタンスを再代入してしまうと、インスタンスを適切に扱ったかどうか読み取り辛くなります。変数とインスタンスは生存期間を一致させ、再代入を行わないコーディングをお勧めします。

と思ってVS2017 C# 7.0で書き直してみました。

# これはこれで、どこからどう見てもF#だなぁ…。

var typeMap = new Dictionary<String, CellValues> {
    { "nvarchar", CellValues.String },
    { "varchar",  CellValues.String },
    { "int",      CellValues.Number },
    { "float",    CellValues.Number },
    { "datetime", CellValues.Date   },
};
using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)) {
    connection.Open();
    using (var transaction = connection.BeginTransaction(IsolationLevel.Serializable))
    using (var command = new SqlCommand("EXEC 【ストアドプロシジャー及びパラメーター】", connection, transaction))
    using (var dataReader = command.ExecuteReader())
    using (var document = SpreadsheetDocument.Create("Test.xlsx", SpreadsheetDocumentType.Workbook, true)) {
        var workbookPart = document.AddWorkbookPart();
        IEnumerable<Sheet> EnumerateSheet() {
            var id = 1u;
            do {
                if (!dataReader.HasRows)
                    continue;
                dataReader.Read();  // fetch first row to read columns' name and type.
                var sheetName = dataReader.GetString(0);
                var columns = Enumerable
                    .Range(0, dataReader.FieldCount - 1)    // TODO: maybe start is 1. 
                    .Select(i => (Index: i, Name: dataReader.GetName(i), Type: typeMap[dataReader.GetDataTypeName(i)]))
                    .ToArray();
                IEnumerable<Row> EnumerateRow() {
                    Cell CreateCell(int row, int column, CellValues type, string value)
                        => new Cell { CellReference = $"{Char.ConvertFromUtf32('A' + column)}{row}", DataType = type, CellValue = new CellValue(value) };
                    var r = 1;
                    yield return new Row(columns.Select(c => CreateCell(r, c.Index, CellValues.String, c.Name)));   // header row.
                    do {
                        r++;
                        yield return new Row(columns.Select(c => CreateCell(r, c.Index, c.Type, dataReader.GetValue(c.Index).ToString())));
                    } while (dataReader.Read());
                }
                var worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                worksheetPart.Worksheet = new Worksheet(new SheetData(EnumerateRow()));
                yield return new Sheet { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = id++, Name = sheetName };
            } while (dataReader.NextResult());
        }
        workbookPart.Workbook = new Workbook(new Sheets(EnumerateSheet()));
        transaction.Commit();
    }
}

Answer 5

ご教授ありがとうございます。
示唆に富むエレガントなコードに感心しつつ、早速試してみたのですが、
残念なことに下記のコンパイルエラーが出てしまいました。

コンパイル エラー メッセージ: CS1528: Expected ; or = (cannot specify constructor arguments in declaration)

using (var document = SpreadsheetDocument.Create("Test.xlsx", SpreadsheetDocumentType.Workbook, true)) {
var workbookPart = document.AddWorkbookPart();
IEnumerable<Sheet> EnumerateSheet()
{
           var id = 1u;

EnumerateSheet()の後に;をつけるなど試してもみたのですが……
環境の違い（私の方はVS2015　C#6.0）の影響もあるのでしょうか？
初心者ゆえなかなか難儀しています。

Answer 6

質問文に環境が記されていなかったので、回答に記したようにVS2017 C# 7.0でコードを記述しました。C# 7.0で導入されたローカル関数なので、以前のコンパイラーでは解析できずエラーとなります。

特に理由がなければ最新の環境を使用することをお勧めします。逆に必要があって過去の環境を使用されている場合は使用環境を明示することをお勧めします。

Answer 7

せっかくご教授いただきましたので、早速VS2017をインストールしてみました。
そして下記のサイトなども参考にしたのですが

http://qiita.com/forest1/items/295b25231cfc49f9b755
http://dev.classmethod.jp/etc/visual-studio-2017-rc-c-sharp-7/

機能’ローカル変数’はC#6では使用できません。７以上の言語バージョンを使用してください。機能’タプル’はC#6では使用できません。７以上の言語バージョンを使用してください。

と出てしまって、残念なのですが、C#7が使えないようです。
本件とはいささか脱線気味で恐縮ですが、もし何かお分かりになるようでしたら
ご指摘いただけると助かります。

OSはWindows7とWindows Server2008Rの両方で試しています。
ちなみにWindows7側のバージョン情報は以下の通りです。

Microsoft Visual Studio Community 2017
Version 15.2 (26430.13) Release
VisualStudio.15.Release/15.2.0+26430.13
Microsoft .NET Framework
Version 4.6.01055

インストールされているバージョン:Community

Visual Basic 2017   00369-60000-00001-AA279
Microsoft Visual Basic 2017

Visual C# 2017   00369-60000-00001-AA279
Microsoft Visual C# 2017

Application Insights Tools for Visual Studio のパッケージ   8.6.00404.2
Application Insights Tools for Visual Studio

ASP.NET AJAX Control Toolkit   1.0
ASP.NET AJAX Control Toolkit Visual Studio integration package

ASP.NET and Web Tools 2017   15.0.30503.0
ASP.NET and Web Tools 2017

ASP.NET Web Frameworks and Tools 2017   5.2.50303.0
For additional information, visit https://www.asp.net/

Azure App Service Tools v3.0.0   15.0.30209.0
Azure App Service Tools v3.0.0

Common Azure Tools   1.9
Azure Mobile Services および Microsoft Azure Tools で使用する共通サービスを提供します。

JavaScript 言語サービス   2.0
JavaScript 言語サービス

KofePackagePackage Extension   1.0
KofePackagePackage Visual Studio Extension Detailed Info

Microsoft Azure Tools   2.9
Microsoft Azure Tools for Microsoft Visual Studio 2017 - v2.9.50131.1

Microsoft MI-Based Debugger   1.0
Provides support for connecting Visual Studio to MI compatible debuggers

NuGet パッケージ マネージャー   4.2.0
Visual Studio 内の NuGet パッケージ マネージャー。NuGet の詳細については、http://docs.nuget.org/ にアクセスしてください。

SQL Server Data Tools   15.1.61702.140
Microsoft SQL Server Data Tools

TypeScript   2.2.2.0
TypeScript tools for Visual Studio

Answer 8

VS2017で新規に作成したプロジェクトであればこの問題は発生しませんが、以前のバージョンからのコンバートの場合に発生します。

プロジェクトのプロパティのビルドの詳細設定で言語バージョンを選択・変更できます。

Answer 9

情報ご提供ありがとうございます。ただご指摘の「ビルドの詳細設定」が見当たらず、これはどうしたことかと探ってみました。
すると確かにWebアプリケーションプロジェクトの場合だと「ビルドの詳細設定がある」のですが、今回、私がVS2015で制作を開始したのはWebサイトプロジェクトで、その場合はどうも当該ダイアログがないようです。
C#7は断念するか、Webアプリケーションプロジェクトに変換するか、はたまたVS2017で新規にWebサイトプロジェクトとしてつくり始めるか。悩ましいところです。

Answer 10

ASP.NETという点を失念しておりました。手順を確認しましたが、VS2017は現時点で二重にバグっていました。

Web Siteメニューに「Enable C# 6 / VB 14」という項目がありますが、見ての通りそもそもC# 7に追随できていません。更にこのメニューを選択してもエラーが発生します。

C# 7.0を諦めるのも１つの選択肢ですが、ひとまず（メニュー項目が行う処理をC# 7にアレンジした）手順を提示します。

1. Web SiteプロジェクトにBinフォルダーを作成します。メニューから行っても直接作成しても構いません。
2. 1.で作成したBinフォルダーにroslynフォルダーを作成します。
3. 2.で作成したBin/roslynフォルダーにC:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\15.0\Bin\Roslynにある全ファイルをコピーします。
   （メニューではC:\Program Files (x86)\Microsoft Web Tools\Packages\Microsoft.Net.Compilers.1.3.2\toolsからコピーしようとしますが、こちらはC# 6 / VB 14向けです。）
4. 1.で作成したBinフォルダーにC:\Program Files (x86)\Microsoft Web Tools\Packages\Microsoft.CodeDom.Providers.DotNetCompilerPlatform.1.0.3\lib\net45にある全ファイルをコピーします。
   （メニューでは1.0.2にアクセスしようとしてエラーになります。）
5. Web.config次の内容を記述します。/langversion:7 の部分でバージョンを指定できます。
   （この内容はC:\Program Files (x86)\Microsoft Web Tools\Packages\Microsoft.CodeDom.Providers.DotNetCompilerPlatform.1.0.3\content\web.config.install.xdtを元にしています。）

<system.codedom>
  <compilers>
    <compiler
      language="c#;cs;csharp"
      extension=".cs"
      type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.3.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
      warningLevel="4"
      compilerOptions="/langversion:7 /nowarn:1659;1699;1701" />
  </compilers>
</system.codedom>

Answer 11

佐祐理　様　

いつもご親切ご丁寧にありがとうございます。
早速教えていただいた手順に従って試してみましたが、ビルドの段で以下のエラーが出るようになってしまいました。

エラー  ファイルまたはアセンブリ 'Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=1.0.3.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'、またはその依存関係の 1 つが読み込めませんでした。見つかったアセンブリのマニフェスト定義はアセンブリ参照に一致しません。 (HRESULT からの例外:0x80131040)    

内容から察するに項番4または5のMicrosoft.CodeDom.Providers.DotNetCompilerPlatform.1.0.3に関連する
あたりのようだったので、4と5を一旦は元に戻し、Web.configのlangversionを6から7に書き換えてみました。
ちなみに、元のWeb.configのMicrosoft.CodeDom.Providers.DotNetCompilerPlatformのVersionは1.0.0.0でした。

すると

エラー: Sys.WebForms.PageRequestManagerServerErrorException: この Command に関連付けられている DataReader が既に開かれています。このコマンドを最初に閉じる必要があります。

と表示され、まだ軽微な修正がありそうですが、C#7でもほぼ期待通りのエクセルファイルが出力できました。

ありがとうございました。