1.21 jigowatts

Great Scott!

トップ > ASP.NET > ASP.NETでExcelファイルを読み込んでデータベースに登録する処理を実装する～EPPlus

ASP.NETでExcelファイルを読み込んでデータベースに登録する処理を実装する～EPPlus

ASP.NET

概要

前回のExcelファイルを読み込む処理に引き続き、読み込んだデータをストアドプロシージャで登録する処理を書いてみます。
f:id:sh_yoshida:20140719191919p:plain

環境

Visual Studio 2010
EPPlus：EPPlus-Create advanced Excel 2007 spreadsheets on the server - Home
SQLServer2008 R2

今回の要件は

  1. Excelファイルを読み込み
  2. GridViewに表示
  3. 読み込んだデータをストアドプロシージャで登録

として、サンプルコードを書いてみました。

データベースの準備

テーブルの作成

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);
                }

                //２行目以降がデータ
                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();
            }            

        }
    }
}

実行結果

画面イメージ
f:id:sh_yoshida:20140719191919p:plain

データベース問い合わせ結果
f:id:sh_yoshida:20140719192054p:plain