DataTableの内容をエクセルにコピーする方法

アプリでデータをエクセルへコピーするときは注意すべきことがある。エクセルではデータが自動変換される。

• 全角数字「１２３４５」は半角数字「12345」に変わる
• 頭に「0」が付いている「00012345」は「12345」に変わる
• 日付「2017/7/7」が数値に変わる（ことがある）
• 「-」でつながった数字「7-7」が日付の「7/7」に変わる

エクセルにデータをコピーするときは、上記のことに気を付けないといけない（場合もある）。数字だけコピーしてOKなら、悩むことは無い。（2018/7/25追記）

エクセルにデータをコピーする方法は、MSDNにまとめてある。いろいろな方法をこれでもかという具合にまとめてある。書いた人の気合を感じる。

• Visual C# 2005 または Visual C# .NET を使用してデータを Excel ブックに転送する方法

VB6のころは、レコードセットをエクセルにコピーするメソッドがあったような記憶がある。しかし悲しいことに、上の記事から10年経とうとしているのに、DataTableにはエクセルへデータをコピーするメソッドは追加されていない。

解決策

冒頭に書いた注意点を避けるにはどうするか。以下のようにすると割とうまくいくことに最近気がついた。

1. エクセルのワークシートを作って、タイトル行をコピーする
2. データが文字列のときは、エクセルの書式設定を「文字列」にしてコピーする。
   これで「１２３４５」も「00012345」も「7-7」もそのままコピーされる。
3. 日付データなら書式設定を「日付」にしてコピーする。
   これで日付データが数値に化けることは無い。
4. それ以外のデータは、そのままコピーする。

DataTableをコピーするときは、列ごとにデータの型があるはずなので、列ごとに書式設定をすればよい。

たとえば、A列は文字列、B列は日付の場合は下記の通り。エクセルの列は1始まりなのに注意。

   ws.Columns[1].NumberFormatLocal = "@";
   ws.Columns[2].NumberFormatLocal = "yyyy/m/d";

ただし、書式設定は割と時間がかかるので、数百列も設定すると大変かも。

サンプルソース

dtはデータテーブルの変数で、この内容をエクセルのシートに丸ごとコピーしたい。

まずは、2次元の配列を用意して、値をコピーする。

object[,] rzData = new object[dt.Rows.Count, dt.Columns.Count];
 
for (int i = 0; i < dt.Rows.Count; i++)
{
    for (int j = 0; j < dt.Columns.Count; j++)
    {
         rzData[i, j] = dt.Rows[i][j];
    }
}

次に書式。1列ずつ設定してもよいのだけど、書式も2次元の配列を用意して、コピーする方法でやってみる。データが化けるのは文字列と日付の場合なので、そのデータだけ書式を設定する。他は設定無し。

/// セルの書式を設定する
/// 文字列は文字列としておかないと「3-1」が日付値に化けたりする
object[,] mmFormat = new object[dt.Rows.Count, dt.Columns.Count];
 
for (int j = 0; j < dt.Columns.Count; j++)
{
     DataColumn co = dt.Columns[j];
     string t = "";
 
     if (co.DataType == typeof(string))
     {
         t = "@";
     }
     else if (co.DataType == typeof(DateTime))
     {
         t = "yyyy/m/d";
         /// t = "yyyy/m/d h:mm:ss";  /// 時刻の場合はこちら
     }
     if (t != "")
     {
         for (int i = 0; i < dt.Rows.Count; i++)
         {
             mmFormat[i, j] = t;  // 行数分だけ設定する
         }
     }
}

最後に、セルの範囲を指定して、まとめて設定する。１セルごとにデータをコピーして書式を設定すると、毎回エクセルにアクセスするので遅くなる（と思う）。まとめて１回で済ますのだ。

// データをコピーする
string pos1 = mmUtils.GetCellAddressA1Style(startRow, startColumn);
string pos2 = mmUtils.GetCellAddressA1Style(startRow + dt.Rows.Count - 1, startColumn + dt.Columns.Count - 1);
sysExcel.Range ra = ws.get_Range(pos1, pos2);
ra.NumberFormatLocal = mmFormat;
ra.Value2 = rzData;

GetCellAddressA1Styleは「1,1」を渡すと「A1」というセル位置を返すメソッドということで。