【ASP.NET MVC5】ClosedXMLでExcelファイル出力
業務システム開発において
画面やデータベースの情報をExcelファイルに出力する機能は、本当によくあります！
今まで色んな現場でシステム開発を経験してきましたが、なかったところはなかったくらいです。。
そこで、この記事では、ASP.NET MVC5のプロジェクトにて、
画面やデータベースなどのデータをClosedXMLを用いてExcel出力する方法をまとめています。
目次
事前準備
使用ツール
・Visual Studio Community 2019
こちらでダウンロード可能です。(無料)
・SQL Server 2019 Developer エディション
こちらでダウンロード可能です。(無料)
使用プロジェクト
今回は「ClosedXMLSample」というプロジェクト名で、
ASP.NET MVCのプロジェクトを作成しました。
Entity FrameWorkのバージョン：6.0.2
bootstrapのバージョン：3.4.1
データベース構築
今回は、Microsoft SQL Server でデータベースを作成して、商品テーブル(T_COM)を作成します。
SQL Serverのインストールに関しては、ここでは割愛します。
商品テーブルのカラム情報
| No. | 論理名 | 物理名 | データ型 | Not Null | デフォルト | 備考 |
|---|---|---|---|---|---|---|
| 1 | 商品ID | com_id | bigint identity | Yes (PK) | ||
| 2 | 商品名 | com_name | nvarchar(100) | |||
| 3 | 商品詳細 | com_detail | nvarchar(max) | |||
| 4 | 削除フラグ | del_flag | tinyint | Yes | 未削除：0 削除済：1 | |
| 5 | 登録日時 | create_date | datetime | Yes | ||
| 6 | 登録ユーザー | create_user | nvarchar(50) | |||
| 7 | 更新日時 | update_date | datetime | Yes | ||
| 8 | 更新ユーザー | update_user | nvarchar(50) |
SQL Server上で、以下のSQLを実行してください。
商品テーブル(T_COM)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE [dbo].[T_COM]( [com_id] [bigint] IDENTITY(1,1) NOT NULL, [com_name] [nvarchar](100) NULL, [com_detail] [nvarchar](max) NULL, [del_flag] [tinyint] NOT NULL, [create_date] [datetime] NOT NULL, [create_user] [nvarchar](50) NULL, [update_date] [datetime] NOT NULL, [update_user] [nvarchar](50) NULL, CONSTRAINT [PK_COM] PRIMARY KEY CLUSTERED ( [com_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] |
商品テーブル(T_COM)の初期データ
|
1 2 3 4 5 6 7 8 9 |
INSERT INTO [dbo].[T_COM] ([com_name], [com_detail], [del_flag], [create_date], [create_user], [update_date], [update_user]) VALUES ('テスト商品1', 'テスト商品1の詳細です。', 0, GETDATE(), 'admin', GETDATE(), 'admin') INSERT INTO [dbo].[T_COM] ([com_name], [com_detail], [del_flag], [create_date], [create_user], [update_date], [update_user]) VALUES ('テスト商品2', 'テスト商品2の詳細です。', 0, GETDATE(), 'admin', GETDATE(), 'admin') INSERT INTO [dbo].[T_COM] ([com_name], [com_detail], [del_flag], [create_date], [create_user], [update_date], [update_user]) VALUES ('テスト商品3', 'テスト商品3の詳細です。', 0, GETDATE(), 'admin', GETDATE(), 'admin') INSERT INTO [dbo].[T_COM] ([com_name], [com_detail], [del_flag], [create_date], [create_user], [update_date], [update_user]) VALUES ('テスト商品4', 'テスト商品4の詳細です。', 0, GETDATE(), 'admin', GETDATE(), 'admin') INSERT INTO [dbo].[T_COM] ([com_name], [com_detail], [del_flag], [create_date], [create_user], [update_date], [update_user]) VALUES ('テスト商品5', 'テスト商品5の詳細です。', 0, GETDATE(), 'admin', GETDATE(), 'admin') |
-
- SQL Server Management StudioでSQLを実行
Entity Data Model更新
データベースでテーブルの追加、初期データ追加ができたら、プロジェクト上でADO.NET Enitiy Data Modelも更新して、データベースの情報をマッピングする為のEDMファイルを作成します。
（Visual Studio上でEDMファイルを作成する方法は、こちらの記事を参照ください。）
対象のEDMファイルのデザイナー画面を開き、右クリック→「データベースからモデルを更新」を選択します。
EDMのデザイナー画面に、商品テーブル（T_COM）が追加された事を確認できたら、
Ctrl+Sで上書き保存しましょう！
ClosedXMLインストール
ClosedXMLは、システム上でExcelファイル（*.xlsx形式）を読み書きなどするためのライブラリです。
まずは、NuGetのパッケージの管理からインストールを行います。
・「ソリューションエクスプローラー」のプロジェクトを右クリック→「NuGetパッケージの管理」をクリックします。
・参照タブをクリックして、ClosedXMLと検索すると、ClosedXMLと一覧に表示されるので、インストールします。
-
- NuGetパッケージの管理
-
- ClosedXMLをインストール
Excelテンプレートファイルを使用しないで出力する方法
まずはExcelテンプレートファイルを使用しないで、商品テーブルのデータを直接Excelファイルに出力する方法です。
今回は、出力する事がメインなので、画面には拘らず、デフォルトのトップ画面に「Excel出力」というボタンを配置して、
クリックすることで処理が開始されるようにしましょう。
Viewの修正
Excel出力のボタンを追加する為、
[Views]→[Home]→index.cshtmlを選択してください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
@{ ViewBag.Title = "Home Page"; } <div class="jumbotron"> <h1>ASP.NET</h1> <p class="lead">ASP.NET is a free web framework for building great Web sites and Web applications using HTML, CSS and JavaScript.</p> <p><a href="https://asp.net" class="btn btn-primary btn-lg">Learn more »</a></p> @using (Html.BeginForm("ListExport", "Home", FormMethod.Post)) { <input type="submit" value="Excel出力" class="btn btn-warning" /> } </div> /* 以下、省略 */ |
9～12行目にHomeContollerのListExportメソッドで出力処理がされるようにformで囲みます。
Controllerの修正
トップページでの処理になる為、
[Controller]→HomeController.csを選択してください。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; using System.Data; using System.IO; using ClosedXML.Excel; using ClosedXMLSample.DAL; namespace ClosedXMLSample.Controllers { public class HomeController : Controller { public ActionResult Index() { return View(); } public ActionResult About() { ViewBag.Message = "Your application description page."; return View(); } public ActionResult Contact() { ViewBag.Message = "Your contact page."; return View(); } /// <summary> /// 商品一覧 Excel出力 /// </summary> /// <returns></returns> [HttpPost] public FileResult ListExport() { List<T_COM> comModelList = new List<T_COM>(); using (var context = new SAMPLEDB01Entities()) { // データベースから商品一覧取得 comModelList = context.T_COM.AsNoTracking() .Where(x => x.del_flag == 0) .ToList(); } // データテーブルの引数がシート名になる DataTable dt = new DataTable("商品一覧"); // ヘッダー行の内容設定 dt.Columns.AddRange(new DataColumn[3] { new DataColumn("商品ID"), new DataColumn("商品名"), new DataColumn("商品詳細")}); using (var wb = new XLWorkbook()) { // 商品テーブルのデータをDataTableにコピー foreach (var com in comModelList) { dt.Rows.Add(com.com_id, com.com_name, com.com_detail); } // ワークブックにシートを追加 wb.Worksheets.Add(dt); // 出力 using (MemoryStream stream = new MemoryStream()) { wb.SaveAs(stream); return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"ComList_{DateTime.Now.ToString("yyyyMMdd")}.xlsx"); } } } |
まずは6～9行目に必要なusingディレクティブを追加、。
次に33行目からListExportメソッドを追加することで実装完了です。
商品テーブルのデータを取得後、DataTable型のオブジェクトにコピー、
その後、DataTable型のオブジェクトをワークブックのオブジェクトに設定します。
※9、11行目のClosedXMLSampleの部分は、私が作成したプロジェクト名の為、ご自身で設定したプロジェクト名にしてください。
※SAMPLEDB01Entitiesは、私がEDMファイルを作成した際に設定したエンティティの名前の為、ご自身で設定したエンティティ名に変更してください。
動作確認
ここまで実装できたら、実行してみましょう。
「Excel出力」ボタンをクリックして、無事にExcelファイルのダウンロードが開始されました。
Excelテンプレートファイルを使用して出力する方法
次に事前にテンプレートとなるExcelファイルを用意しておいて、
そのテンプレートファイルの指定したセル位置にデータを出力する方法です。
今回は商品詳細画面で各商品の情報をExcel出力するようにします。
同じファイルとセル位置で試したい場合は、こちらで作成したテンプレートファイルをご使用ください。
プロジェクトにテンプレートファイルを追加
作成したExcelテンプレートファイルをプロジェクトに追加します。
こちらでは、ExcelTmpというディレクトリを新規作成して、その中にテンプレートファイルを追加しました。
・プロジェクト(ClosedXMLSample)を右クリック → [追加] → [新しいフォルダ]
・[作成したフォルダを任意の名前に変更] → [作成したフォルダを右クリック] → [追加] → [既存の項目]で、
用意しているExcelファイルを選択します。
※追加したテンプレートファイルを選択し、プロパティの「出力ディレクトリにコピー」を「常にコピーする」に変更してください。
これを設定しないとアプリの実行ファイルパス内にテンプレートファイルのディレクトリおよびファイルがコピーされません。
Viewの修正
テンプレートなしの時と同じように、今回は画面にこだわりはないので、
トップページのソース(index.cshtml)に、ボタンを追加します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
@{ ViewBag.Title = "Home Page"; } <div class="jumbotron"> <h1>ASP.NET</h1> <p class="lead">ASP.NET is a free web framework for building great Web sites and Web applications using HTML, CSS and JavaScript.</p> <p><a href="https://asp.net" class="btn btn-primary btn-lg">Learn more »</a></p> @using (Html.BeginForm("ListExport", "Home", FormMethod.Post)) { <input type="submit" value="Excel出力" class="btn btn-warning" /> } <br /> @using (Html.BeginForm("DetailExport", "Home", FormMethod.Post)) { <input type="submit" value="Excel出力(テンプレートあり)" class="btn btn-warning" /> } </div> |
Controllerの修正
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 |
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.Mvc; using System.Data; using System.IO; using ClosedXML.Excel; using ClosedXMLSample.DAL; using System.Reflection; namespace ClosedXMLSample.Controllers { public class HomeController : Controller { public ActionResult Index() { return View(); } public ActionResult About() { ViewBag.Message = "Your application description page."; return View(); } public ActionResult Contact() { ViewBag.Message = "Your contact page."; return View(); } /// <summary> /// 商品一覧 Excel出力 /// </summary> /// <returns></returns> [HttpPost] public FileResult ListExport() { List<T_COM> comModelList = new List<T_COM>(); using (var context = new SAMPLEDB01Entities()) { // DBから商品一覧取得 comModelList = context.T_COM.AsNoTracking() .Where(x => x.del_flag == 0) .ToList(); } // データテーブルの引数がシート名なる DataTable dt = new DataTable("商品一覧"); // ヘッダー行の内容設定 dt.Columns.AddRange(new DataColumn[3] { new DataColumn("商品ID"), new DataColumn("商品名"), new DataColumn("商品詳細")}); using (var wb = new XLWorkbook()) { // 商品テーブルのデータをDataTableにコピー foreach (var com in comModelList) { dt.Rows.Add(com.com_id, com.com_name, com.com_detail); } // ワークブックにシートを追加 wb.Worksheets.Add(dt); // 出力 using (MemoryStream stream = new MemoryStream()) { wb.SaveAs(stream); return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"ComList_{DateTime.Now.ToString("yyyyMMdd")}.xlsx"); } } } /// <summary> /// 商品詳細 Excel出力 /// </summary> /// <param name="model"></param> /// <returns></returns> [HttpPost] public FileResult DetailExport() { // アプリケーションの実行ファイルのパスを取得 Assembly executionAsm = Assembly.GetExecutingAssembly(); string executingPath = Path.GetDirectoryName(new Uri(executionAsm.CodeBase).LocalPath); // Excelテンプレートファイルのパス設定 string tmpPath = executingPath + @"\ExcelTmp\商品詳細.xlsx"; // 既存のワークブックを開く using (var wb = new XLWorkbook(tmpPath)) { // 既存のシートを開く var ws = wb.Worksheet("Sheet1"); // 指定セルにデータを設定 ws.Cell("C4").Value = "1"; ws.Cell("C6").Value = "テスト商品"; ws.Cell("B9").Value = "この商品は～非常にワイルドです！"; // 出力 using (MemoryStream stream = new MemoryStream()) { wb.SaveAs(stream); return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"ComDetail_{DateTime.Now.ToString("yyyyMMdd")}.xlsx"); } } } } } |
まずは10行目に必要なusingディレクティブを追加、
ビルドした際に、Excelテンプレートファイルも実行ファイルのパスにコピーされる為、
その実行ファイルを参照するコード(82～84行目)の為に必要になります。
次に74行目からDetailExportメソッドを追加することで実装完了です。
今回はセルに設定する内容は、固定値にしていますが、
データベースから取得してきた情報でも、なんでもいいです。
とにかく、ワークブックのオブジェクトからシートのオブジェクトを開いて、
Cellメソッドの引数にExcelのセル位置を指定すれば、そのセルに値が設定される事が理解できればOKです！
動作確認
それでは、実行してみましょう。
まとめ
このようにClosedXMLを使用することで、
データベースなどの情報を簡単にExcelファイルに出力できました。
業務システム開発では、ほぼ確実にある機能です。
ClosedXML以外にも方法はありますが、
1つやった経験があるだけでも、その経験が役にたつ時は来ます！
まだ実務でやったことのない方は、是非試してみてください！
それでは、今回もありがとうございました！
この記事を書いた人
- フリーエンジニア / Webデザイナー / LPコーディング講師 / シンガーソングライター
-
歌うプログラマーとして
IT業界、プログラミングの"あるある"を歌にしている。プログラミングもデザインも音楽も色々とやるパラレルキャリアな人。
最新の投稿
WordPress2024.08.23【Lightning】無料版でヘッダー上部機能を追加する方法- LPコーディング2024.08.11【LPコーディング】HTML共通パーツで同じセクションを再利用
- 新着情報2024.07.17【制作実績】あざみ野 勝俣接骨院 様 Webサイトリニューアル
- LPコーディング2022.09.27【LPコーディング】Q＆A 質問をクリックすると回答を表示する方法
フリーランスエンジニア歴10年のセールスデザインができるWeb制作者 石井よしまさのメルマガ
10年で関わった現場は13社。
その中で4社から正社員オファーを頂くことができた私が
主にフリーランス初心者向けに、
以下のような内容を配信していきます。
「常駐型のフリーランスで評価される為に大切な5つのマインド」
「10年で13社の案件に関わり、なぜ4社から正社員オファーがあったのか？」
「セールスデザイナーが依頼すべき良いコーダーとは？」..
などについて学べます。
是非、フリーランスとしてやっていきたい！
という方は、ご登録ください(^^)
