บทความ – Uploads Excel files to SQL DB (2)

 Article refers to:

: ASP.Net 2.0 C#

: SQL EXPRESS 2005

: Excel 2003

จาก บทความ – Uploads Excel files to SQL DB ผู้อ่านคงได้ทราบแล้ว เราสามารถ Uploads ข้อมูลในรูปแบบของ Excel files เข้า Data base ได้อย่างไรนั้นเป็นการทำงานกับ Excel ที่มีเพียง 1 Work Sheet และถ้ามี Work Sheet มากกว่า 1 ล่ะ…

มีท่านผู้อ่านท่านหนึ่งมีความสงสัยว่า แล้วถ้ามีหลาย ๆ Sheet ล่ะทำได้ไหม… แล้วสามารถควบคุมข้อมูลได้หรือไม่ เช่นต้องการเพิ่มข้อมูล หรือต้องการลบข้อมูล หรือต้องการแก้ไขข้อมูล… มีวิธีทำอย่างไร???

จึงเป็นที่มาของบทความ Uploads Excel files to SQL DB (2) เพื่อตอบข้อสงสัยของท่านผู้อ่าน และเป็นแนวทางสำหรับหลายท่านที่จะนำไปประยุกติ์ใช้งานต่อไปครับ

เมื่อพร้อมแล้วเริ่มกันเลยครับ…

1 รูปแบบข้อมูลในระบบ

1.1. ข้อมูล UserName

1.2. ข้อมูล Employees

1.3. ข้อมูล Province

1.4. ข้อมูล ข้อมูล Region

1.5 Table

2. การทำงานของระบบ

2.1 หน้าตาโปรแกรม

2.2. การ Uploads Excel file ให้ผู้ใช้งานยืนยันการ Uploads

2.3. เมื่อ Uploads เรียบร้อยระบบจะเปิด Excel file แสดงบน Datagrid

2.4. เปรียบเทียบข้อมูลบน Excel กับบน Datagrid

2.5. เราสามารถเลือก Sheet ได้โดยเก็บชื่อ WorkSheet ไว้ใน DropDownList1

2.6. ชื่อ Worksheet ได้จาก Excel Worksheet SchemaTable โดยใช้ Column TABLE_NAME ผูกเข้าใน DropDownList1

ได้จาก dt = exConn.GetOleDbSchemaTable แสดงExcel Worksheet SchemaTable บน DataGrid

2.7. สามารถเลือกดูข้อมูล Worksheet อื่นๆได้จาก DropDownList1

2.8. Column StatusDelete ใช้เพื่อกำหนดว่าเป็นข้อมูล(สำหรับลบ)=1 หรือ(สำหรับเพิ่มหรือแก้ไข) =0 สังเกตเห็นว่า ถ้า StatusDelete=1 ข้อมูลบน datagrid จะเป็นสีแดงพื้นสีเหลือง ละมีข้อความว่าลบข้อมูล

2.9. ดูข้อมูล Worksheet อื่นๆ ต่อ ข้อมูล Region

2.10. ข้อมูล Region ต้นฉบับ

2.11. ข้อมูล Username

2.12. ข้อมูล Username ต้นฉบับ

2.13. ต่อไปเป็นการบันทึกเพื่อเอาข้อมูลลง SQL base ซึ่งบทความนี้จะยกตัวอย่างเฉพาะข้อมูล Sheet Employees เท่านั้นครับ ส่วน Sheet อื่นๆ ผู้อ่านสามารถทำต่อได้ครับ โดยโครงสร้าง Table ใน SQL Base เป็นดังรูป

2.14. บน DropDownList1 เลือกข้อมูล Employees$ เพื่อแสดงข้อมูลของ Sheet Employees ทำการบันทึกให้ กดบันทึก และยืนยันการบันทึกกด OK

2.15 บันทึกข้อมูลเรียบร้อย

2.16. เรากลับมาดูที่ SQL Base อีกครั้งจะเห็นว่ามีข้อมูลแล้ว

2.17. ถ้าข้อมูลใน Excel มีการแก้ไขดังรูป โดยกำหนด Emp_ID 1006,1007 เป็นการลบข้อมูล ดูจาก Column StatusDelete = 1 ส่วน Emp_ID 1001,1002,1015 เป็นการแก้ไข

2.18. ทำการ Uploads Excel file ใหม่ จะเห็นว่าเป็นการลบข้อมูล 2 Record ใส่สีให้ดูง่าย ทำการบันทึกข้อมูล

2.19. บันทึกเรียบร้อย

2.20. ดูข้อมูลใน SQL Base อีกครั้ง

2.21. เห้นความแตกต่าง … ข้อมูล Emp_ID 1006,1007 ถูกลบเรียบร้อย และ Emp_id 1001,1002,1015 ถูกแก้ไขด้วย

3. ออกแบบ Program

3.1 จากบทความตอนที่ 1 ใช้ Project เดิม ให้เพิ่ม CheckBox1, GridView2, DropDownList1 ดังรูป

3.2 เมื่อออกแบบหน้าจอเรียบร้อย ให้สร้าง Store เพื่อทำงานกับข้อมูล Stored Procedure ถือเป็นหัวใจหลักของระบบ เนื่องจากเป็นตัวสอบว่าจะ Update, Delete หรือ Edit จะเห็นว่า Store ตัวเดียวแต่ทำงานได้ 3 งาน (Update, Delete, Edit) รัน Script นี้เข้าใน SQL Server เลยครับ เพื่อสร้าง Store ชื่อ SP_Ins_Up_Del_Employees ก่อนต้องสร้าง Table Employees  ก่อนครับ

Stored Procedure Script

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[SP_Ins_Up_Del_Employees]

(

@Emp_ID char(4),

@FName nvarchar(50),

@LName nvarchar(50),

@Address nvarchar(200),

@PRO_ID char(2),

@Telno nvarchar(15),

@Phonce nvarchar(15),

@StatusDelete nvarchar(10)

)

AS

IF ltrim(rtrim(@StatusDelete))=’ลบข้อมูล’ –//เป็นการ ลบข้อมูล

BEGIN–//=================== Delete

Delete Employees where [Emp_ID] = @Emp_ID

END

ELSE

BEGIN

IF exists (select * from Employees where Emp_ID = @Emp_ID) –// ถ้ามีข้อมูลให้ Update

BEGIN–//=================== Update

Update Employees set

[FName] = @FName

,[LName] = @LName

,[Address] = @Address

,[PRO_ID] = @PRO_ID

,[Telno] = @Telno

,[Phonce] = @Phonce

where [Emp_ID] = @Emp_ID

END

ELSE

BEGIN–//=================== Insert  ถ้าไม่มีข้อมูลให้ Insert

INSERT INTO Employees

([Emp_ID]

,[FName]

,[LName]

,[Address]

,[PRO_ID]

,[Telno]

,[Phonce]

,[StatusDelete])

VALUES

(@Emp_ID,

@FName,

@LName,

@Address,

@PRO_ID,

@Telno,

@Phonce,

‘0’)

END

END

3.3 Code Program เขียน Code กันเลย….

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Text.RegularExpressions;

using System.Data.OleDb;

using System.Data.SqlClient;

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

{

private string strConn = "";

private OleDbConnection exConn;

private SqlConnection sqConn;

private DataTable dt = null;

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

Button1.Attributes.Add("onclick", "javascript:return confirm(‘คุณต้องการ Uploads Excel files ใช่หรือไม่???…

nano
วันที่ส่ง: 28 ก.ย. 50 18:53 GMT+7
วันที่ปรับล่าสุด: 20 พ.ย. 50 11:01 GMT+7

REPLY #1 (62440)

‘);");

Button2.Attributes.Add("onclick", "javascript:return confirm(‘คุณต้องการบันทึกข้อมูล ใช่หรือไม่???…’);");

}

}

//Uploads Excel Files

protected void Button1_Click(object sender, EventArgs e)

{

if (FileUpload1.HasFile)

{

string filepath = FileUpload1.PostedFile.FileName;

string pat = @"\(?:.+)\(.+).(.+)";

Regex r = new Regex(pat);

Match m = r.Match(filepath);

string file_ext = m.Groups[2].Captures[0].ToString();

string filename = m.Groups[1].Captures[0].ToString();

string file = filename + "." + file_ext;

FileUpload1.PostedFile.SaveAs(Server.MapPath(".\Uploads\") + file);

Label1.Text = "File Saved to: " + Server.MapPath(".\Uploads\") + file;

//ShowDatatoGrid(file);

GetExcelSheetNames(file);

ShowData2GridView1(DropDownList1.Text.Trim ());

}

}

private void GetExcelSheetNames(string f)

{

try

{

this.Session["strConn"] = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(".\Uploads\") +

f + @";Extended Properties=""Excel 8.0;""";

exConn = new OleDbConnection(this.Session["strConn"].ToString ());

exConn.Open();

dt = exConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dt.Rows.Count > 0)

{

DropDownList1.DataSource = dt;

DropDownList1.DataTextField = "TABLE_NAME";

DropDownList1.DataValueField = "TABLE_NAME";

DropDownList1.DataBind();

//Show Excel SchemaTable

GridView2.DataSource = dt;

GridView2.DataBind();

}

else

return;

}

catch (Exception ex)

{

Label1.Text = ex.Message;

}

finally

{

// Clean up.

if (exConn != null)

{

exConn.Close();

exConn.Dispose();

}

if (dt != null)

{

dt.Dispose();

}

}

}

//private void ShowDatatoGrid(string f)

//{

// try

// {

// //this.strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(".\Uploads\")+f+ @";Extended Properties=""Excel 8.0;""";

// // dt = getWorksheet("UesrName$");

// if (dt != null)

// {

// this.GridView1.DataSource = dt;//getWorksheet("UesrName$");

// this.GridView1.DataBind();

// }

// else

// this.Label1.Text = "ไม่มีข้อมูล";

// }

// catch (Exception ex)

// {

// this.Label1.Text = ex.Message;

// }

//}

private DataTable getDataByWorksheet(string worksSheetNames)

{

DataSet ds = new DataSet();

try

{

exConn = new OleDbConnection(this.Session["strConn"].ToString ());

exConn.Open();

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM [" + worksSheetNames + "] ", exConn);

da.Fill(ds);

}

catch (Exception ex)

{

this.Label1.Text = ex.Message + "";

}

finally

{

if (exConn != null)

{

exConn.Close();

exConn.Dispose();

}

}

if (ds.Tables.Count > 0)

return ds.Tables[0];

else

return null;

}

//Insert Excel file to SQL DB

protected void Button2_Click(object sender, EventArgs e)

{

this.strConn = "Server=(local);uid=sa;pwd=xxx;database=TestDB;";

sqConn = new SqlConnection(this.strConn);

if (sqConn.State == ConnectionState.Closed)

sqConn.Open();

SqlTransaction tr = sqConn.BeginTransaction();

SqlCommand cmd = sqConn.CreateCommand();

try

{

cmd.CommandType = CommandType.StoredProcedure;

cmd.Transaction = tr;

string emp_ID;

string fName;

string lName;

string aDdress;

string pro_ID;

string telNo;

string phone;

string sdel;

for (int i = 0; i <= GridView1.Rows.Count – 1; i++)

{

emp_ID = GridView1.Rows[i].Cells[0].Text.Trim();

fName = GridView1.Rows[i].Cells[1].Text.Trim();

lName = GridView1.Rows[i].Cells[2].Text.Trim();

aDdress = GridView1.Rows[i].Cells[3].Text.Trim();

pro_ID = GridView1.Rows[i].Cells[4].Text.Trim();

telNo = GridView1.Rows[i].Cells[5].Text.Trim();

phone = GridView1.Rows[i].Cells[6].Text.Trim();

sdel = GridView1.Rows[i].Cells[7].Text.Trim();

//ตัดค่าว่าง "&nbsp;" ออก

fName = (fName=="&nbsp;")?"":fName;

lName = (lName == "&nbsp;") ? "" : lName;

aDdress = (aDdress == "&nbsp;") ? "" : aDdress;

pro_ID = (pro_ID == "&nbsp;") ? "" : pro_ID;

telNo = (telNo == "&nbsp;") ? "" : telNo;

phone = (phone == "&nbsp;") ? "" : phone;

sdel = (sdel == "&nbsp;") ? "" : sdel;

cmd.Parameters.Clear();

cmd.CommandText = "SP_Ins_Up_Del_Employees";

cmd.Parameters.Add(new SqlParameter("@Emp_ID", emp_ID));

cmd.Parameters.Add(new SqlParameter("@FName", fName));

cmd.Parameters.Add(new SqlParameter("@LName", lName));

cmd.Parameters.Add(new SqlParameter("@Address", aDdress));

cmd.Parameters.Add(new SqlParameter("@PRO_ID", pro_ID));

cmd.Parameters.Add(new SqlParameter("@Telno", telNo));

cmd.Parameters.Add(

nano
วันที่ส่ง: 28 ก.ย. 50 19:04 GMT+7
วันที่ปรับล่าสุด: 28 ก.ย. 50 19:04 GMT+7

REPLY #2 (62441)

new SqlParameter("@Phonce", phone));

cmd.Parameters.Add(new SqlParameter("@StatusDelete", sdel));

cmd.ExecuteNonQuery();

}

tr.Commit();

GridView1.DataSource = null;

GridView1.DataBind();

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

}

catch (SqlException ex)

{

tr.Rollback();

Label2.Text = "Error – TRANSACTION ROLLED BACKn" + ex.Message;

}

catch (Exception ex)

{

tr.Rollback();

Label2.Text = "System Errorn" + ex.Message;

}

finally

{

if (exConn != null)

{

exConn.Close();

exConn.Dispose();

}

tr.Dispose();

}

}

protected void CheckBox1_CheckedChanged(object sender, EventArgs e)

{

if (CheckBox1.Checked)

{

GridView2.Visible = true;

}

else

GridView2.Visible = false ;

}

protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)

{

if (DropDownList1.SelectedValue != "")

{

ShowData2GridView1(DropDownList1.SelectedValue.ToString());

}

}

void ShowData2GridView1(string workShee)

{

GridView1.DataSource = getDataByWorksheet(workShee);

GridView1.DataBind();

}

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)

{

int xc =0;

if (e.Row.RowType == DataControlRowType.DataRow)

{

xc = e.Row.Cells.Count-1;// หา Cell สุดท้าย

if (e.Row.Cells[xc].Text == "1")

{

e.Row.BackColor = System.Drawing.Color.Yellow;

e.Row.ForeColor = System.Drawing.Color.Red ;

e.Row.Cells[xc].Text = "ลบข้อมูล";

}else{

e.Row.Cells[xc].Text = "";

}

}

}

}

สรุป

การบันทึกข้อมูลในบทความตอนที่ 2 เป็นการใช้งาน Stored Procedure แล้วผ่าน parameter เข้า เพื่อให้การตรวจสอบข้อมูลต่างๆ นั้นทำงานบน Server จะช่วยให้ระบบทำงานเร็วขึ้นกรณีที่เรามีข้อมูลเป็นจำนวนมาก ซึ่งผู้เขียนได้ยกตัวอย่างเพียง การทำงานกับข้อมูลเฉพาะ Table Employees ซึ่งท่านผู้อ่านสามารถปรับใช้งานกับ Table ที่เหลือได้

การประยุกติ์ใช้งาน

– การ Uploads ข้อมูล ถ้าข้อมูลมีมากเกินไปอาจทำให้ผู้ใช้งานเบื่อหน่ายได้ ท่านผู้อ่านอาจใช้วิธิการ Zip ข้อมูลก่อนทำการ Uploads และอาจเพิ่มขั้นตอนการตรวจสอบความสมบูรณ์ของ file ว่าส่งครบหรือไม่ เกิดการสูญหายกลางทางหรือเปล่า …

– การบันทึกข้อมูล ในการประยุกติใช้งานจริง ผู้อ่านสามารถให้ระบบทำการการบันทึกข้อมูลพร้อมกันหลายๆ Sheet โดยไม่ต้องให้ ผู้ใช้งานเลือกผ่าน DropDownList1 เพียงแค่ให้ระบบเลือกเองโดยใช้ Loop ใน DropDownList1 แทนครับ หรืออื่นๆ ตามเทคนิคแต่ละคนครับ

อ้างอิง

C# – Retrieve Excel Workbook Sheet Names.

http://www.codeproject.com/aspnet/getsheetnames.asp

เห็นใหมครับว่าเราสามารถทำงานกับ Excel ได้หลายๆ Sheet ไม่จำกัด…

หากเกิดข้อผิดพลาดประการใด ต้องขออภัย ณ ที่นี้ และพร้อมน้อมรับคำแนะนำ เพื่อแก้ไขในครั้งต่อๆ ไปครับ

 

อ่านเพิ่มเติมที่: Greatfriends.biz:=> บทความ – Uploads Excel files to SQL DB (2)

เกี่ยวกับ janawat

Seasoned Senior System Analyst & Developer with over a decade of experience in designing, analyzing, and developing highperformance, scalable applications using a wide range of technologies, including Java, Spring Boot, C#, ASP.NET, MVC, React.js, Kubernetes, Microservices, PostgreSQL, MySQL, Oracle, and Microsoft SQL. Notable Achievements: Consistently delivered projects on time and within budget, ensuring client satisfaction and project success. Demonstrated versatility in working both independently and as a valuable team member, contributing to collaborative achievements. Honored with the prestigious "Employee of the Year" award in 2012 for exceptional dedication and outstanding performance. Recognized as a Microsoft Most Valuable Professional (MVP) for significant contributions to the software development community. My Commitment: I am committed to innovation, excellence, and solving complex technical challenges. I take pride in my ability to consistently deliver robust and efficient software solutions that meet and exceed the expectations of clients and stakeholders.

‹ บทความ – ทำงานกับข้อมูลแบบง่ายไม่มี Code ด้วย WindowsApplication

บทความ – Uploads Excel files to SQL DB ›

เขียนใน Web Application