DataSetの内容をExcel出力する

前回の記事「SQL Serverからデータを取得してDataGridViewに表示する(その2)」で抽出したデータをExcel出力する方法を紹介します。
手段の1つとして、VB.NETの参照設定で「Microsoft Excel xx.x オブジェクトライブラリ」を追加して直接書き込む方法があります。ただし、後処理をしっかり対処しないとタスクにEXCELが残ったり、配布先のOfficeバージョンによっては参照設定のオブジェクトライブラリのバージョンが違う為、エラーになる可能性があるので個人的には使いません。
ここでは、環境に依存しにくいOleDbを使った方法を紹介します。

イメージとしては下図の様な事をします。
2016021308

まずは、前回の記事「SQL Serverからデータを取得してDataGridViewに表示する(その2)」ベースにButton2を追加します。
2016021304

Button2には以下のコードをコピペしてください。
セーブファイルダイアログでxls形式かxlsx形式かを選択します。

次にDatasetの内容をExcelに出力するコードをコピペします。

エクセルに書き込むデータ型は、ヘッダーを作成する段階で確定させています。
まず、DataSet内のDataTableのデータ型が何になっているか調べてみました。
※下図はデータ型と列名の関係です。
ここでは数値型にしたいのは、System.Int32とSystem.Decimal、日付型にしたいのは、System.DataTime、残りはテキスト型になります。
2016021305
なお、DataSet側のデータ型を変更したい場合は、クエリーを発行する際にConvert文を使うと型指定できます。
SELECT convert(varchar,SUG_UpdateTime,111) AS SUG_UpdateTime みたいな感じにすると、System.DataTime→System.Stringになります。

エクセルには以下3点のデータ型を指定できます。
ヘッダー情報を書き込んだ後に続いてデータ挿入するとデータ型を継承して書き込みされます。
NUMBER・・・エクセルの特定列を数値型でエクスポートします。
TEXT・・・エクセルの特定列を数字の値であっても文字列としてエクスポートします。
DATE・・・エクセルの特定列を日付型でエクスポートします。

Button2をクリックしてファイル保存すると以下の様にエクセルが生成されます。

2016021306

エクセルを開いてみると、下図のように日付型、数値型、テキスト型と正しく書き込みされています。
下図を例にみると、EAN列とSUG_Price列の値は数字ですが、EAN列は数字で認識されると困りますが正しく元のテキスト形式を継承しています。
2016021307

上の例では、シートが1つのみですが、複数シートを作ることも出来ます。
下記では二つのDataTableの内容を2つのシートに書き込みます。