ADO.NET と ACE OleDb プロバイダを利用して Excel のブックを新規作成する話です。

備忘録として残しておきたかったのは、作成するブックが xls 形式の時は接続文字列の Extended Properties に "Excel 8.0" を、xlsx 形式の時は "Excel 12.0 Xml" を指定するということです。

それを書いただけではブログの記事としては寂しいので、以下を追加しました。（笑）

JET でも ACE でも、接続文字列の Data Source に存在しないファイル名を指定して、CREATE TABLE クエリを実行すると、そのファイル名でブックを新規作成できます。

ACE の場合は以下のような感じです。この例では、c:\workbooks というフォルダに test.xlsx という名前のファイルが作成されます。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;

namespace OleDbExcel
{
  class Program
  {
    static void Main(string[] args)
    {
      DataTable table = CreateDataTable();
      string createQuery = CreateQuery(table);
      string insertQuery = InsertQuery(table);
      string connString = 
        "Provider=Microsoft.ACE.OLEDB.12.0;" + 
        "Data Source=C:\\workbooks\\test.xlsx;" + 
        "Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
            
      using (OleDbConnection connection = 
        new OleDbConnection(connString))
      {
        // Excel ブックを作成
        connection.Open();
        using (OleDbCommand command = 
          new OleDbCommand(createQuery, connection))
        {                    
          command.ExecuteNonQuery();
        }

        // Excel ブックにデータを INSERT
        using (OleDbCommand command = 
          new OleDbCommand(insertQuery, connection))
        {                    
          foreach (DataColumn column in table.Columns)
          {
            command.Parameters.Add(
              "@" + column.ColumnName, 
              OleDbType.VarWChar);                        
          }

          foreach (DataRow row in table.Rows)
          {
            foreach (DataColumn column in table.Columns)
            {
              command.
                Parameters["@" + column.ColumnName].
                Value = 
                  row[column.ColumnName];
            }
            command.ExecuteNonQuery();
          }
        }
      }
    }

    // Excel ブックを作る基になるデータを SQL Server の
    // サンプルデータベース Northwind の Customers テー
    // ブルより取得し DataTable を作る。
    protected static DataTable CreateDataTable()
    {
      DataTable dataTable = new DataTable();
      SqlDataAdapter adapter = new SqlDataAdapter();
      string connString = Properties.Settings.Default.MyDB;
      string query = 
        "SELECT CustomerID, CompanyName, ContactName, ContactTitle" + 
        " FROM Customers";
      SqlConnection connection = new SqlConnection(connString);
      SqlCommand command = new SqlCommand(query, connection);
      adapter.SelectCommand = command;
      adapter.Fill(dataTable);
      return dataTable;
    }

    // DataTable の DataColumn を基に CREATE TABLE クエリ
    // を作成する。この例では以下のようになる。
    // CREATE TABLE [MySheet](CustomerID char(255),
    // CompanyName char(255),ContactName char(255),
    // ContactTitle char(255))
    protected static String CreateQuery(DataTable table)
    {
      StringBuilder sb1 = new StringBuilder();
      sb1.Append("CREATE TABLE [MySheet](");
      foreach (DataColumn column in table.Columns)
      {
        sb1.Append(column.ColumnName + " char(255),");
      }
      sb1.Remove(sb1.Length - 1, 1);
      sb1.Append(")");
      return sb1.ToString();
    }

    // DataTable の DataColumn を基に INSERT クエリを作成
    // する。この例では以下のようになる。
    // INSERT INTO [MySheet] (CustomerID,CompanyName,
    // ContactName,ContactTitle) values (?,?,?,?)
    protected static String InsertQuery(DataTable table)
    {
      StringBuilder sb1 = new StringBuilder();
      StringBuilder sb2 = new StringBuilder();
      sb1.Append("INSERT INTO [MySheet] (");
      sb2.Append(" values (");
      foreach (DataColumn column in table.Columns)
      {
        sb1.Append(column.ColumnName + ",");
        sb2.Append("?,");
      }
      sb1.Remove(sb1.Length - 1, 1);
      sb2.Remove(sb2.Length - 1, 1);
      sb1.Append(")");
      sb2.Append(")");
      return sb1.ToString() + sb2.ToString();
    }
  }
}

作成したファイルを Excel アプリケーションで開いたのが上の画像です。クエリで指定したテーブル名のシート（例では、MySheet）が生成されています。

上のコードで、接続文字列の Extended Properties="Excel 12.0 Xml;HDR=YES" の Excel 12.0 Xml の部分はソースデータベースの種類の指定です。HDR=YES は、シートの 1 行目がヘッダであることを指定します。

ソースデータベースの種類は、作成するブックの形式によって、以下のように変わります。

xls は Excel 2003 以前の形式のブック、その他は Excel 2007 以降の Microsoft Office Open XML 形式のブックです。

8.0 または 12.0 という数字は ISAM (Indexed Sequential Access Method) ドライバのバージョンです。

注意すべきは、xlsx 形式のブックを作る時は "Excel 12.0 Xml" のように Xml を付けなければならないところです。JET の場合のように単に "Excel 12.0" とすると、中身は xlsb 形式（バイナリ形式）になってしまいます。

Excel アプリケーションでそのファイル開こうとすると、"Excel でファイルを開くことができません。ファイル形式または・・・" という警告メッセージが出て開けません（拡張子を xlsb に変更すれば開けますが）。

ブックを作った後、SELECT, INSERT, UPDATE（Excel の場合、DELETE はできません）操作を行う場合は、Microsoft サポートの記事 Visual Basic .NET と ADO.NET を使用して Excel ブックのレコードの取得と変更を行う方法 が参考になると思います。

クッキーは同名でもパスが違うと別々に保存されるようです。実は、同じ名前を付けると上書きされると誤解していて、先日、半日ぐらいハマってしまいました。（笑）

クッキーについては ASP.NET の Cookie の概要 に詳しく書いてあって、参考にしていましたが、そのあたりのことは書いてないんですよね。（逆に、名前は一意で、同じだと上書きされると書いてあったりします）

クッキーはサーバーからの応答ヘッダーの Set-Cookie: で指定してブラウザに保存させる手段と、クライアントスクリプトの document.cookie で設定する手段があります。

ASP.NET で、サーバー側から応答ヘッダーの Set-Cookie: を使ってクッキーを設定する場合は以下のようにします。

HttpCookie aCookie = new HttpCookie("TestCookie");
aCookie.Value = "Server side";
aCookie.Expires = DateTime.Now.AddDays(1);
Response.Cookies.Add(aCookie);

この時、HttpCookie.Path を設定しないと Set-Cookie: に含まれるパス情報は path=/ となります。Path を設定するのは、フォルダまたはアプリケーションに Cookie を制限する場合のみで、普通は設定しないというのが自分の理解です。

クライアントスクリプトでクッキーを設定する場合は document.cookie を利用しますが、このとき path を省略すると、クッキーがブラウザに保存される時そのページのディレクトリがパスに設定されます。

例えば、surferonwww.info/test/abc.aspx というページでクッキーを設定するとパスは /test/ になります（/test ではないところに注意。IE9 の開発者ツールを起動して、［キャッシュ(C)］⇒［Cookie 情報を表示する(I)］で調べられます）。

今回、クライアントスクリプトで設定したクッキーを書き換えまたは削除するため、サーバーから同名のクッキーを応答ヘッダーの Set-Cookie: に設定してやりました。

ハマったのはここのところです。同名だから上書きされると思っていたところ、パスが違うので上書きされず、既存のクッキーはそのままで、Set-Cookie: で送ったクッキーが追加されただけでした。結果、書き換えも削除もできませんでした。

解決策はパスを一致させることです。path=/ とすると、そのドメインのすべてのページ要求でクッキーがサーバーに送られますが、それで問題なければ ASP.NET のデフォルト（？）の path=/ にしておくのがよさそうです。

何故なら、同名でパスが異なるクッキーが複数保存されてしまうと、消去したり内容を書き換えるためクッキーを上書きするには、当該クッキーのパスを指定しなければならず、それは結構大変だからです。また、そういう状態になってしまうと、テスト中にも想定外の動作になって混乱すると思います。

HTTP ハンドラ内では Session の読み書きができません。例えば、以下のようにすると context.Session は null になって NullReferenceException がスローされてしまいます。

string s = (string)context.Session["note"];

ご存知でしたでしょうか？　実は、自分はそのことを知らなくて、つい先日 1 時間ぐらいハマってしまいました。（笑）

この問題を解決するには、HTTP ハンドラのクラスにマーカーインターフェースを継承させ、この HTTP ハンドラは Session へのアクセスを必要としているという印を付けてやります。

そのマーカーインターフェイスは System.Web.SessionState 名前空間 に属しており、以下のとおり 2 種類あります。

1. IRequiresSessionState 読み取り/書き込みアクセス権を必要とすることを指定します。
2. IReadOnlySessionState 読み取り専用アクセス権のみが必要であることを指定します。

実際の使用例は、まぁ、書かなくても分るとは思いますが、以下のような感じです（この例では IRequiresSessionState を使用）。

<%@ WebHandler Language="C#" Class="_MyHttpHandler" %>

using System;
using System.Web;
using System.Web.SessionState;

public class _MyHttpHandler : IHttpHandler, IRequiresSessionState 
{    
  public void ProcessRequest (HttpContext context) 
  {
    byte[] data = (byte[])context.Session["PageResponse"];
        
    if (data != null)
    {
      context.Response.BinaryWrite(data);
      context.Session.Remove("PageResponse");
    }
    context.Response.End();
  }
 
  // 以下省略

マーカーインターフェイスはメソッドの定義を持たないので、一般的なインターフェイスを継承したときのように、継承したクラス内にメソッドを実装する必要はありません。