ASP.NETでExcelファイルを読み込んでデータベースに登録する処理を実装する~EPPlus
概要
前回のExcelファイルを読み込む処理に引き続き、読み込んだデータをストアドプロシージャで登録する処理を書いてみます。
環境
Visual Studio 2010
EPPlus:EPPlus-Create advanced Excel 2007 spreadsheets on the server - Home
SQLServer2008 R2
今回の要件は
- Excelファイルを読み込み
- GridViewに表示
- 読み込んだデータをストアドプロシージャで登録
として、サンプルコードを書いてみました。
データベースの準備
テーブルの作成
Excelファイルと同じレイアウトです。
CREATE TABLE [dbo].[MyData]( [#] [nvarchar](255) NOT NULL, [NAME] [nvarchar](255) NULL, [AGE] [nvarchar](255) NULL, [MAIL] [nvarchar](255) NULL, [PHONE] [nvarchar](255) NULL, CONSTRAINT [PK_MyData] PRIMARY KEY CLUSTERED ( [#] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
ユーザー定義テーブル型
CREATE TYPE [dbo].[typeMyData] AS TABLE( [#] [nvarchar](max) NOT NULL, [NAME] [nvarchar](max) NULL, [AGE] [nvarchar](max) NULL, [MAIL] [nvarchar](max) NULL, [PHONE] [nvarchar](max) NULL )
ストアドプロシージャ
テーブルに登録済みであれば更新、なければ新規で登録します。
CREATE PROCEDURE [dbo].[myProc] (@tyExcelData typeMyData READONLY) AS BEGIN SET NOCOUNT ON; DECLARE @# nvarchar(255), @NAME nvarchar(255), @AGE nvarchar(255), @MAIL nvarchar(255), @PHONE nvarchar(255), @Cnt int = 0; DECLARE EXCEL_CURSOR CURSOR FOR SELECT * FROM @tyExcelData; OPEN EXCEL_Cursor FETCH NEXT FROM EXCEL_CURSOR INTO @#, @NAME, @AGE, @MAIL, @PHONE WHILE @@FETCH_STATUS=0 BEGIN IF Exists(SELECT 1 FROM dbo.MyData WHERE # = Cast(@# As nvarchar(255))) BEGIN UPDATE dbo.MyData Set NAME = @NAME, AGE = @AGE, MAIL = @MAIL, PHONE = @PHONE WHERE # = Cast(@# As nvarchar(255)) END ELSE BEGIN INSERT INTO dbo.MyData Values ( @#, @NAME, @AGE, @MAIL, @PHONE ) END SET @Cnt = @Cnt + 1 FETCH NEXT FROM EXCEL_CURSOR INTO @#, @NAME, @AGE, @MAIL, @PHONE END CLOSE EXCEL_CURSOR; DEALLOCATE EXCEL_CURSOR; Return @Cnt SET NOCOUNT OFF END
実装
画面
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExcelUpload.aspx.cs" Inherits="WebApplication1.ExcelUpload" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:Button ID="Button1" runat="server" Text="load" onclick="Button1_Click" /> <asp:Button ID="Button2" runat="server" Text="clear" onclick="Button2_Click" /> <asp:Button ID="Button3" runat="server" Text="upload" onclick="Button3_Click" /> </div> <div> <asp:gridview ID="Gridview1" runat="server"></asp:gridview> </div> <div> <asp:Label ID="Label1" runat="server" Text="Label"></asp:Label> </div> </form> </body> </html>
コードビハインド
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.IO; using System.Data; using OfficeOpenXml; using Business; namespace WebApplication1 { public partial class ExcelUpload : System.Web.UI.Page { ExcelUploadBusiness busObj = new ExcelUploadBusiness(); protected void Page_Load(object sender, EventArgs e) { Label1.Text = ""; } protected void Button1_Click(object sender, EventArgs e) { string filePath = @"c:\test\book.xlsx"; using (var pck = new OfficeOpenXml.ExcelPackage()) { try { using (var stream = File.OpenRead(filePath)) { pck.Load(stream); } } catch (System.IO.IOException ex) { if (ex.Message.Contains("because it is being used by another process.")) { Label1.Text = "Excelファイルを閉じてください。"; } return; } catch (Exception) { throw; } var ws = pck.Workbook.Worksheets.First(); DataTable tbl = new DataTable(); //1行目はヘッダー foreach (var firstRowCell in ws.Cells[1,1,1,ws.Dimension.End.Column]) { tbl.Columns.Add(firstRowCell.Text); } //2行目以降がデータ const int START_ROW = 2; for (int row = START_ROW; row <= ws.Dimension.End.Row; row++) { var wsRow = ws.Cells[row, 1, row, ws.Dimension.End.Column]; var newRow = tbl.NewRow(); foreach (var cell in wsRow) { newRow[cell.Start.Column - 1] = cell.Text; } tbl.Rows.Add(newRow); } Gridview1.Visible = true; Gridview1.DataSource = tbl; Gridview1.DataBind(); Session["ExcelData"] = tbl; } } protected void Button2_Click(object sender, EventArgs e) { Gridview1.DataSource = null; Gridview1.DataBind(); Session["ExcelData"] = null; } protected void Button3_Click(object sender, EventArgs e) { if (Gridview1.Rows.Count > 0) { try { DataTable dt = (DataTable)Session["ExcelData"]; Label1.Text = busObj.Upload(dt); } catch (Exception ex) { Label1.Text = "アップロードエラー"; System.Diagnostics.Debug.WriteLine("ERROR:" + ex.Message); } } else { Label1.Text = "データがありません。"; } } } }
ビジネスクラス(クラスライブラリ)
using System; using System.Collections.Generic; using System.Linq; using System.Text; using Data; using System.Data; namespace Business { public class ExcelUploadBusiness { ExcelUploadData dataObj = new ExcelUploadData(); public string Upload(DataTable dt) { return dataObj.Upload(dt); } } }
データアクセスまわり(クラスライブラリ)
web.config
各環境に合わせたデータベース情報を記載します。
<connectionStrings> <add name="connectionString" connectionString=" Server=(localdb)\v11.0;Integrated Security=SSPI;Database=LocalDB;" providerName="System.Data.SqlClient" /> </connectionStrings>
コネクション
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data.SqlClient; namespace Data { public class DBConnection { public SqlConnection Connection { get; private set; } public DBConnection() { Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString); } } }
プロシージャの呼び出し
DataTableを引数として渡します。
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; namespace Data { public class ExcelUploadData { private DBConnection _db; private SqlCommand _cmd; public ExcelUploadData() { _db = new DBConnection(); _cmd = new SqlCommand(); } public string Upload(DataTable dt) { try { if (_db.Connection.State != ConnectionState.Open) { _db.Connection.Open(); } _cmd.Connection = _db.Connection; _cmd.CommandType = System.Data.CommandType.StoredProcedure; _cmd.CommandText = "myProc"; _cmd.Parameters.AddWithValue("@tyExcelData", dt); _cmd.Parameters.Add(new SqlParameter("ReturnValue", SqlDbType.Int)); _cmd.Parameters["ReturnValue"].Direction = ParameterDirection.ReturnValue; _cmd.ExecuteNonQuery(); int ret = ((int)_cmd.Parameters["ReturnValue"].Value); return "処理件数:" + ret.ToString() + " 件"; } catch (Exception) { throw; } finally { _db.Connection.Close(); } } } }
実行結果
画面イメージ
データベース問い合わせ結果