อิมพอร์ตข้อมูลจาก Excel บันทึกลง ฐานข้อมูล SQL Server โดยใช้ EPPlus ตอนที่ 1 Import Excel


บทความจาก นิตยสาร WindowsITPro
ได้ขออนุญาตทางบก. เรียบร้อยแล้ว

โปรแกรมเมอร์
ที่พัฒนาโปรแกรมด้านงานธุรกิจ(Business Applications)
คงหนีไม่พ้น ที่จะเกี่ยวข้องกับข้อมูลบน Excel

เนื่องจาก Excel นั้น
เป็นสิ่งที่ผู้ใช้งานคุ้นเคยกันดี
จึงมีความเป็นไปได้สูง ที่ผู้ใช้งานจะนำข้อมูลในโปรแกรมของคุณ
ไปทำรายงานต่อบน Excel ตามรูปแบบที่เขาต้องการ
โดยไม่ต้องการเสียเวลารอ เพียงเพื่อให้โปรแกรมเมอร์ สร้างรายงานขึ้นมา 1 ตัว
เพื่อนำเสนอผู้บริหาร และก็ทิ้งไปทันทีเมื่อเสร็จสิ้นการนำเสนอ
หรือการนำข้อมูลใน Excel อิมพอร์ตกลับเข้าไปเก็บในฐานข้อมูล
ซึ่งความคุ้นเคย ทำผู้ใช้งานนั้น ถนัดคีย์การข้อมูลลบน Excel
มากกว่าบนหน้าจอโปรแกรมของคุณ หรือบ้างครั้งผู้ใช้งานรับข้อมูล เช่น
รายการสินค้า จากตัวแทนจำหน่ายมาในรูปของไฟล์ Excel
เขาต้องการอิมพอร์ตข้อมูลจาก Excel เข้าระบบโดยทันที

เป็นสิ่งที่ดีมาก
ถ้าโปรแกรมของคุณ เพิ่มปุ่มให้ผู้ใช้งาน สามารถคลิก
แล้วอิมพอร์ตข้อมูล Excel ดังกล่าว
ลงไปบันทึกเข้าฐานข้อมูลอย่างถูกต้อง ผู้ใช้งานจะมีความสุขมาก

ครั้งหนึ่งนานมาแล้ว
ผมเคยเขียนบทความเกี่ยวกับ การอิมพอร์ตข้อมูล Excel
เข้าฐานข้อมูล Microsoft SQL แต่เป็นการอ่านข้อมูล Excel
ผ่าน Provider Microsoft.Jet.OLEDB

ซึ่งบทความนั้นถือเป็นบทความแรก ในชีวิตของการเป็นโปรแกรมเมอร์ ของผม
และนั่นเป็นจุดเริ่มต้นให้ผมได้เข้ามาเขียนบทความประจำ ในนิตยสาร WindowsITPro จนถึงปัจจุบัน
หากท่านผู้อ่านสนใจ การอิมพอร์ตข้อมูล Excel ผ่าน Microsoft.Jet.OLEDB

สามารถติดตามบทความ: Uploads Excel files to SQL DB
ได้ที่: http://greatfriends.biz/webboards/msg.asp?id=62078

กลับมาในบทความนี้ ผมจะแสดง อีกหนึ่งตัวอย่างของการอิมพอร์ตข้อมูล Excel
เข้าฐานข้อมูล Microsoft SQL โดยใช้คอมโพเนนต์ชื่อ EPPlus (อี-พี-พลัส)
ซึ่งผมจะใช้ตัวอย่างข้อมูลในตาราง Customer ของฐานข้อมูลตัวอย่าง
Northwind
ก่อนอื่นไปสำรวจสิ่งที่จำเป็นในบทความกันก่อน ดังนี้

สิ่งที่จำเป็น (Prerequisites)

ขั้นตอนการพัฒนาโปรแกรม

  • ขั้นตอน ที่หนึ่ง การสร้างโปรเจ็กต์
  • ขั้นตอน ที่สอง การออกแบบหน้าจอ
  • ขั้นตอน ที่สาม การเขียนโค้ด
  • ขั้นตอน ที่สี่ การทดสอบ

ขั้นตอน ที่หนึ่ง การสร้างโปรเจ็กต์
เมื่อมีเครื่องมือพร้อมแล้ว

ทำการเปิด Visual Studio Express 2013 for Web ขึ้นมา
แล้วทำตามขั้นตอนดังนี้
1.1. ในหน้า Start Page คลิก New Project…
1.2. ในหน้าต่าง New Project คลิก Web
1.3. เลือก ASP.NET Web Application Visual C#
1.4. ระบุชื่อโปรเจ็กต์ในช่อง Name: เป็น ImportExcelWithEPPlus
1.5. แล้วกด OK
1.6. บนหน้าต่าง New ASP.NET Project – ImportExcelWithEPPlus เลือกเทมเพลต Web Forms
1.7. Visual Studio จะติ๊กถูก Web Forms อัตโนมัติ
1.8. คลิก OK
1.9. เราไม่ได้นำเว็บไปวางบนคลาวน์ กด No thanks and do not ask me again
1.10. แล้วรอสักครู่… Visual Studio กำลังสร้างโปรเจ็กต์ ดังรูปที่1

รูปที่ 1 ขั้นตอน การสร้างโปรเจ็กต์

 

1.11. เมื่อสร้างโปรเจ็กต์ เรียบร้อย คุณผู้อ่านจะได้หน้าจอดังรูปที่2

รูปที่ 2 สร้างโปรเจ็กต์ ImportExcelWithEPPlus เรียบร้อย

 

ขั้นตอน ที่สอง การออกแบบหน้าจอ
หลังจากสร้างโปรเจ็กต์เป็นที่เรียบร้อย

เราไปทำการออกแบบหน้าจอ ตามขั้นตอน
ดังนี้

2.1 การสร้างฟอร์ม
การเพิ่มไอเทมเข้าในโปรเจ็กต์ ทำง่ายๆ
โดยคลิขวาลงบนชื่อโปรเจ็กต์ แล้วเลือกเมนู ตามขั้นตอนดังนี้

2.1.1 เลือกเมนู Add
2.1.2 เลือกเมนู New Item…
2.1.3 บนหน้าต่าง Add New Item – ImportExcelWithEPPlus เลือก Web Form with Master Page
2.1.4 ตั้งชื่อฟอร์มในช่อง Name: เป็น ImportExcel
2.1.5 คลิก Add
2.1.6 แล้วเลือกไฟล์ Master page ชื่อ Site.Master
2.1.7 จากคลิก OK เสร็จสิ้นขั้นตอน
การสร้างฟอร์ม ดังรูปที่3

รูปที่ 3 การสร้างฟอร์ม

2.1.8 เมื่อสร้างฟอร์มเรียบร้อย คุณจะได้หน้าจอดังรูปที่ 4

รูปที่ 4 สร้างฟอร์ม เรียบร้อย

2.2 การออกแบบหน้าจอ
เมื่อสร้างฟอร์มเรียบร้อย ลำดับต่อไป
เราจะไปทำการออกแบบหน้าจอโปรแกรมกัน ตามขั้นตอนดังนี้
2.2.1 วางเคอร์เซอร์บนฟอร์ม (MainContent
(Custom))
2.2.2 แล้วไปทำการเพิ่มตาราง (Table) เพื่อจัดวางเลย์เอ๊าต์ของคอนโทรล โดยคลิกเมนู TABLE => Insert Table
2.2.3 บนหน้าต่าง Insert Table ทำการกำหนด ให้ตารางมี 3 แถว
2.2.4 และ 1 คอลัมน์
2.2.5 จากนั้น กด OK
2.2.6 เพิ่มตารางสำหรับวางคอนโทรล เป็นที่เรียบร้อย ดังรูปที่5

รูปที่ 5 การเพิ่มตาราง

2.2.7 จากนั้นทำการลากคอนโทรลไปวาง
ดังนี้
2.2.7.1 แถวที่ 1 ระบุข้อป้ายความ Import Excel เพื่อบอกผู้ใช้ว่า หน้าจอนี้ ทำอะไร

2.2.7.2 แถวที่ 2 ใส่คอนโทรล FileUpload และปุ่ม Button
2.2.7.3 แถวที่ 3 ใส่คอนโทรล Label และ GridView ดังรูป6 หรือดูโค้ด Markup html ในไฟล์ ImportExcel.aspx

รูปที่ 6 ออกแบบหน้าจอ เรียบร้อย

2.2.8 โค้ด Markup เป็นดังนี้

ไฟล์ ImportExcel.aspx

<%@
Page
Title=””
Language=”C#”
MasterPageFile=”~/Site.Master”
AutoEventWireup=”true”
CodeBehind=”ImportExcel.aspx.cs”
Inherits=”ImportExcelWithEPPlus.ImportExcel”
%>

<asp:Content
ID=”Content1″
ContentPlaceHolderID=”MainContent”
runat=”server”>

<table
class=”nav-justified”>


<tr>


<td>Import Excel</td>
<%–แถวที่ 1 —%>


</tr>


<tr>


<td>
<%–แถวที่ 2 —%>


<asp:FileUpload
ID=”FileUpload1″
runat=”server”
/>
&nbsp;


<asp:Button
ID=”btImport”
runat=”server”
OnClick=”btImport_Click”
Text=”Import Excel”
/>


</td>


</tr>


<tr>


<td>
<%–แถวที่ 3 —%>


<asp:Label
ID=”Label1″
runat=”server”
Text=”Label”/>


<asp:GridView
ID=”GridView1″
runat=”server”/>


</td>


</tr>

</table>

</asp:Content>

 

2.3 การสร้าง LINQ to SQL Class
เมื่อออกแบบหน้าจอเรียบร้อยแล้ว
ลำดับต่อไปเป็นการสร้าง LINQ เพื่อเชื่อมต่อกับฐานข้อมูล
ตามขั้นตอนดังนี้

2.3.1 คลิกขวาบนชื่อโปรเจ็กต์ => เลือกเมนู Add
2.3.2 เลือกเมนู New Item…
2.3.3 บนหน้าต่าง Add New Item – ImportExcelWithEPPlus => พิมพ์ linq ในช่องค้นหา
2.3.4 จะเห็นไอเท็ม LINQ to SQL Class แสดงขึ้นมา=> จากนั้นทำการคลิกเลือก
2.3.5 ในช่อง Name: => ให้ตั้งชื่อเป็น Northwind.dbml
2.3.6 คลิก Add ดังรูปที่7

รูปที่ 7
สร้าง LINQ to SQL Class

2.4 สร้าง Connection
2.4.1 สร้าง Connection คลิกไอคอนรูปปลั๊กไฟ
2.4.2 เลือกเซอร์ฟเวอร์บนเครื่องตัวเอง => .\SQLEXPRESS (. เครื่องหมายจุด หมายถึง Localhost หรือเครื่องตัวเอง ที่โปรแกรมรันอยู่)
2.4.3 ระบุ UserName และ Password
2.4.4 เลือกฐานข้อมูล โดยในตัวอย่างนี้จะใช้ฐานข้อมูล Northwind
2.4.5 กด OK
2.4.6 จะเห็นชื่อ Data Connection
2.4.7 จากคลิกเลือกตาราง => แล้วลากไปวางบน หน้าต่าง Design Surface ดังรูปที่ 7

รูปที่ 8
สร้าง Connection

2.4.8 เราจะได้ Data Model ของ LINQ ดังรูปที่ 9

รูปที่ 9
LINQ Data Model

ขั้นตอน ที่สาม เขียนโค้ด
เมื่อผ่านขั้นตอน การออกแบบหน้าจอ การสร้าง
LINQ เชื่อมต่อกับฐานข้อมูลแล้ว
ลำดับถัดไป จะเป็นการเขียนโค้ด เพื่ออิมพอร์ตไฟล์ Excel เข้าฐานข้อมูล Northwind
ในตาราง Customer การเขียนโค้ด
มีขั้นตอนดังนี้

3.1 Add Referent EPPlus.dll
การ Add Referent
EPPlus.dll ทำได้สองวิธีคือ

  • การ Add ด้วยตนเอง
  • และ การ Add โดยการติดตั้งผ่าน Package Manager Console
    ขั้นตอน
    การAdd Referent ดังนี้

3.1.1 ดาวน์โหลดไฟล์ EPPlus.dll ได้ที่ http://epplus.codeplex.com/releases/view/89923
3.1.2 เลือกดาวโหลดไฟล์ EPPlus 3.1.3 Binary
(เวอร์ชั่น ณ วันที่กำลังร่างบทความต้นฉบับ
24/4/2557)
3.1.3 จากนั้นทำการแตกไฟล์ คุณจะเห็นไฟล์ EPPlus.dll อยู่ภายใน
3.1.4 ให้ทำการ Add Referent EPPlus.dll
เข้ามาในโปรเจ็กต์
3.1.5 หรือ ติดตั้ง EPPlus ผ่าน
Package Manager Console
โดยเปิดหน้าต่าง Package Manager Console ขึ้นมาดังนี้
3.1.5.1 คลิกบนเมนู TOOLS=>
3.1.5.2 คลิกเมนู NuGet Package Manager =>
3.1.5.3 แล้วเลือก Package Manager Console
3.1.6 เมื่อหน้าต่าง Package Manager Console แสดงขึ้นมาแล้ว ทำการรันคำสั่งดังนี้
3.1.6.1 ป้อนคำสั่ง Install-Package EPPlus -Version 3.1.3.3 หลังเครื่องหมาย PM> จากนั้นกดปุ่ม Enter
3.1.6.2 รอสักครู่ เมื่อ Package EPPlus
ติดตั้งเรียบร้อย คุณจะเห็น EPPlus อยู่ใน Referent เรียบร้อย
3.1.7 จากนั้นทการเขียนโค้ด ตามไฟล์ ImportExcel.aspx.cs ดังนี้

ไฟล์ ImportExcel.aspx.cs

using OfficeOpenXml;

using System;

using System.Collections.Generic;

using System.IO;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

 

namespace ImportExcelWithEPPlus

{


public
partial
class
ImportExcel : System.Web.UI.Page

{


protected
void Page_Load(object sender, EventArgs e)

{

 

}

 


protected
void btImport_Click(object sender, EventArgs e)

{


//ประกาศตัวแปล: lsCustomer เป็นอ็อบเจ็กต์ List ของ Customer


List<DAL.Customer> lsCustomer = new
List<DAL.Customer>();

 


//ถ้าคอนโทรล FileUpload มีไฟล์ และเป็นไฟล์ นามสกุล .xlsx


if (FileUpload1.HasFile && Path.GetExtension(FileUpload1.FileName) == “.xlsx”)

{

 


//ใช้ using statement ประกาศตัวแปล: excel เป็นอ็อบเจ็กต์ ExcelPackage โดยผ่านอินพุตสรีมที่อ่านได้จาก FileUpload1


using (var excel = new
ExcelPackage(FileUpload1.PostedFile.InputStream))

{


//ประกาศตัวแปล worksheet โดยกำหนดให้เท่ากับ sheet แรก


var worksheet = excel.Workbook.Worksheets.First();

 


//วนลูบแถวข้อมูลใน Excel โดยเริ่มที่ 2 จนถึงแถวสุดท้าย


for (int row = 2; row <= worksheet.Dimension.End.Row; row++)

{


//ประกาศตัวแปล เก็บข้อมูลคอลัมน์ต่างๆ


var CustomerID = worksheet.Cells[row, 1].Value;//คอลัมน์ 1


var CompanyName = worksheet.Cells[row, 2].Value;//คอลัมน์ 2


var ContactName = worksheet.Cells[row, 3].Value;//คอลัมน์ 3


var ContactTitle = worksheet.Cells[row, 4].Value;//คอลัมน์ 4


var Address = worksheet.Cells[row, 5].Value;//คอลัมน์ 5


var City = worksheet.Cells[row, 6].Value;//คอลัมน์ 6


var Region = worksheet.Cells[row, 7].Value;//คอลัมน์ 7


var PostalCode = worksheet.Cells[row, 8].Value;//คอลัมน์ 8


var Country = worksheet.Cells[row, 9].Value;//คอลัมน์ 9


var Phone = worksheet.Cells[row, 10].Value;//คอลัมน์ 10


var Fax = worksheet.Cells[row, 11].Value;//คอลัมน์ 11

 


//เพิ่มข้อมูลที่อ่านได้จาก Excel ใส่เข้าไปในอ็อบเจ็กต์ List ของ Customer

lsCustomer.Add(new DAL.Customer

{

 

CustomerID = CustomerID == null ? “” : CustomerID.ToString(),

CompanyName = CompanyName == null ? “” : CompanyName.ToString(),

ContactName = ContactName == null ? “” : ContactName.ToString(),

ContactTitle = ContactTitle == null ? “” : ContactTitle.ToString(),

Address = Address == null ? “” : Address.ToString(),

City = City == null ? “” : City.ToString(),

Region = Region == null ? “” : Region.ToString(),

PostalCode = PostalCode == null ? “” : PostalCode.ToString(),

Country = Country == null ? “” : Country.ToString(),

Phone = Phone == null ? “” : Phone.ToString(),

Fax = Fax == null ? “” : Fax.ToString(),

});

}

 

 


//ใช้ using statement ประกาศตัวแปล: dc เป็นอ็อบเจ็กต์ ของ NorthwindDataContext


using (DAL.NorthwindDataContext dc = new DAL.NorthwindDataContext ())

{


//วนลูบ อ็อบเจ็กต์ List ของ Customer


foreach (var item in lsCustomer)

{


//คิวรี่ข้อมูล มาตรวจสอบ


var q = dc.Customers

.Where(t => t.CustomerID == item.CustomerID )

.FirstOrDefault();

 


//ถ้ายังไม่มีข้อมูล อยู่ในฐานข้อมูล (ให้เพิ่มได้)


if (q == null)

{


//ประตัวแปล:newCus โดยกำหนดให้เท่ากับ อ็อบเจ็กต์ใหม่ของ Customer


//พร้อมกับกำหนดค่าให้ อ็อบเจ็กต์ใหม่ของ Customer


var newCus = new DAL.Customer

{

CustomerID = item.CustomerID,

CompanyName = item.CompanyName,

ContactName = item.ContactName,

ContactTitle = item.ContactTitle,

Address = item.Address,

City = item.City,

Region = item.Region,

PostalCode = item.PostalCode,

Country = item.Country,

Phone = item.Phone,

Fax = item.Fax,

};

 


//เพิ่มอ็อบเจ็กต์ใหม่ของ Customer เข้าไปในเอ็นทีตี้ Customers

dc.Customers.InsertOnSubmit(newCus);

 


//ส่งอ็อบเจ็กต์ใหม่ของ Customer บันทึกลงฐานข้อมูล

dc.SubmitChanges();

}

 

}

}

 


//ผูกข้อมูลเข้า DataSource ของ GridView

GridView1.DataSource = lsCustomer.ToList();

 


//แสดงผ่านหน้าจอ

GridView1.DataBind();

 


//แสดงป้าย บอกให้ผู้ใช้งานทราบสถานะ การทำงาน

Label1.Text = “บันทึกข้อมูลเรียบร้อย”;

}

}


else

{


//แสดงป้าย บอกให้ผู้ใช้งานทราบสถานะ การทำงาน

Label1.Text = “คุณไม่ได้ระบุไฟล์ที่จะอัปโหลด”;

}

}

}

}

 

ขั้นตอน ที่สี่ ทดสอบ

4.1 เตรียมรูปแบบไฟล์ Excel
เมื่อเขียนโค้ดเรียบร้อย
มาถึงขั้นตอนการทดสอบโปรแกรม
ให้เตรียมรูปแบบไฟล์ Excel ที่จะอิมพอร์ตเข้า ดังรูปที่10 (หมายเลข 1)

ตามรูปแบบของตาราง Customer รูปที่10 (หมายเลข 2)
แล้วใส่ข้อมูลดังรูปที่10


รูปที่ 10
รูปแบบไฟล์ Excel สำหรับเตรียมข้อมูล อิมพอร์ต

4.2 รันโปรแกรม
4.2.1 กด F5 รันโปรแกรม
จากนั้นกดปุ่มเลือกไฟล์
4.2.2 เลือกไฟล์ Excel
4.2.3 กดปุ่ม Open
4.2.4 จะเห็นชื่อไฟล์ แสดงบนฟอร์ม ดังรูปที่ 11


รูปที่ 11 เลือกไฟล์ Excel

4.2.4 กดปุ่ม Import Excel เราจะเห็นข้อมูล Excel แสดงบนหน้าจอ ดังรูป12

รูปที่ 12 อิมพอร์ตไฟล์ Excel เข้าฐานข้อมูลเรียบร้อย

4.2.5 ตรวจสอบข้อมูลในฐานข้อมูล กรองเฉพาะ CustomerID ที่ขึ้นต้นด้วย NANO เราจะเห็นข้อมูลดังรูปที่ 13


รูปที่ 13 ตรวจสอบคิวรี่ ข้อมูลที่อิมพอร์ต

การอิมพอร์ตข้อมูล Excel ผ่านคอมโพเนนต์ EPPlus จะต่างกับการอิมพอร์ตผ่าน Provider Microsoft.Jet.OLEDB คือ

  • ไม่ต้องติดตั้ง ไลบรารี่ของ Excel ไว้บนเว็บเซอร์ฟเวอร์
  • ไม่ต้อง Upload ไฟล์ excel ไปวางบนฮาร์ดดิสก์ของเว็บเซอร์ฟเวอร์
  • สามารถใช้ LINQ ในการเชื่อมต่อกับฐานข้อมูล
    ซึ่งทำให้การเขียนโค้ดใช้เวลาน้อนลง โค้ดของเราสั้นลง

ในตอนต่อไป เราจะไปดูการเอ็กซ์พร์อตข้อมูลไปเป็น Excel แล้วพบกันครับ

แหล่งข้อมูลดาวน์โหลด

แหล่งข้อมูลอ้างอิง:

 

โดย:
นุชิต อรรจนวจรรน์
GreatFriends Community Leader (nano)
WindowsITPro Columnist
Microsoft MVP

2014-09-19

 

Advertisements
เกี่ยวกับ

Nuchit Atjanawat is a Microsoft MVP, GreatFriends Community Leader, WindowsITPro Columnist(Mr.Nano), Silverlight Business Application Instructor, Speaker in various Microsoft events, Author, passionate Blogger and a Software Engineer by profession. He has a very good skill over Silverlight, LightSwitch, C#, XAML & Blend. He shares his technical findings, tutorials in his technical blog. Follow his Blog and Facebook page to be updated on latest articles. - Technical Blog: https://janawat.wordpress.com - WindowsITPro Columnist(Mr.Nano): http://windowsitpro.net - The GreatFriends.Biz Community Discussion Board (nano): http://greatfriends.biz - witter: https://twitter.com/janawat - Facebook Page: https://www.facebook.com/nuchit - Instructor: http://bit.ly/GF250-01, http://bit.ly/GF150-02

เขียนใน Uncategorized

ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

Connecting to %s

In Archive
%d bloggers like this: