背景

ある業界団体のサイトで、データ入力用のASP.NetMVCで構築したサイトがあります。

ここで各会員会社が様々なデータを入力して、事務局にあるデータベースサーバーにデータを登録します。
それを事務局で集計して統計データを作成し、会員会社・関係省庁に提示するというサイトです。

本来は、データ入力のためのWeアプリケーションなのですが、会員会社の一部から、データ入力だけではなく、集計したデータの一部を画面に表示してほしいとの要望が出てきました。

画面構成上、これ以上データ項目を表示することが難しいため、妥協案として希望するフォーマットで「Microsoft Excel」ファイルを作成し、それをダウンロードする方法をとることになりました。

EPPlusのインストール

VisualStudio2015のNugetからインストール可能です。
Vwesion4.0.5が最新安定板となっています。(2016/03/30現在）

EPPlusはOOXMLを使用してExcelのスプレッドシートを操作できます。
WebサーバーにExcelがインストールされている必要はありません。

具体的なストーリー

サーバーマシンの所定のフォルダに、必要な書式を設定したテンプレートファイルを作成します。
EPPlusから直接セルの属性や　計算式を設定することもできますが、設定済みのテンプレートファイルを作成するほうがはるかに簡単です。

テンプレートファイルの作成

Microsoft Excelで、データ部分を除いたほかの部分をあらかじめ定義したファイルをテンプレートとして用意しておいて、このファイルを読み込んでデータ部分だけを直接操作するほうが、コードも見やすく、作業時間も圧倒的に省力化できます。
セルのマージや。セルの背景色、フォントの指定・印刷の設定など、通常のExcelファイルとして作成します。

また、自動計算のセルなども通常のファイルと同じように定義しておきます。
ただし、その項目をコーディングで使用するときは、そのセルをCalculate()してあげないとうまくいかないので注意が必要です。

このように、実際のデータ部分を除いたすべてを定義したファイルを、所定の場所（MVCプロジェクト以外の場所でも可）に保存しておきます。
このファイルは、読み込むだけなのでプロジェクトで変更されることはありません。

コントローラでの設定

必要なViewにボタンなどを配置し、コントローラにダウンロード用のメソッドを定義します。
そのリクエスト用にメソッドを定義します。

public ActionResult ExcelDownload()
{
string dfilename = "RWAData" + DateTime.Now.ToString() + ".xlsx"; // ダウンロードファイル名
MemoryStream streem = ExcelHandler.MakeData1(); // 実装のサブルーチンを呼び出します。
return File(streem.ToArray(), "application/msexcel", dfilename);
}

この例では、サブルーチンの呼び出しで定義しています。 私は、MVCのコントローラにはなるべくコードを書かないようにしています。 ヘルパークラスや、モデルクラスに実装することで、コントローラクラスの見通しが良くなります。

この例でも、「ExcelHandler」クラスでコードの実装を行います。
MakeData1メソッドでは、MemoryStreamをNewして戻す仕様にしてあります。

MakeData1()は以下のように実装します。

/ excelテンプレートファイルを読み込む
FileInfo templateFile = new FileInfo(@"C:\Webhelpers\indexBook.xlsx");

var excel = new ExcelPackage(templateFile,true);
var sheet = excel.Workbook.Worksheets["sheet1"];

sheet.Cells[1, 1].Value = ”テスト”;

sheet.Cells[strow, 8].Value = ((double)sheet.Cells[strow, 7].Value / (double)funenkei); // ｈ(8列) //　既存のセルを使って計算もできる

MemoryStream ms = new MemoryStream();
excel.SaveAs(ms);
return ms;

FileInfo を使用してテンプレートファイルを読み込みます。
前にも説明したように、このファイルは上書きsave()しない限り内容の変更はされません。
Worksheetの指定は名前で指定します（この場合は”sheet1”）。

これで、テンプレートファイルのデータを編集する準備ができました。
上の例では、単純に”A1”セルに文字データを設定しているだけですが、具体的な例としては、データベースからデータを読み込み、必要なデータを必要なセルに設定してあげるだけです。

sheet.Cells[7, 10].Value = (double)Model1.value1;

などのように設定してあげます。
そのセルには、テンプレートファイルで「右寄せ/小数点1位」などの属性を設定してあるという前提です。

要点は、データベースのデータ型をそのまま入れてあげるということです。

また、テンプレートファイルで自動計算（Sum関数など）が設定されているセルのデータ（例えば、sheet.Cells[5, 7]）を使いたい場合は

sheet.Cells[5, 7].Calculate();
sheet.Cells[5, 9].value = sheet.Cells[5, 7].value / sheet.Cells[5, 8].value ;

と、明示的にCalculate()しないとデフォルトの0で計算式が実行されます。

上記例の場合、

MemoryStream ms = new MemoryStream();
excel.SaveAs(ms);
return ms;

として呼び出し元に、戻しています。

ExcelPackageオブジェクトをSaveAs(メモリストリーム)とすることで、元のテンプレートファイルが変更されないようにしています。

最終的には、呼び出し元で

return File(streem.ToArray(), "application/msexcel", dfilename);

とすることで、編集したファイルがクライアントにダウンロードされます。

全く便利ですよね。