ความเดิมตอนที่แล้ว ผู้เขียนได้พูดถึงความสำคัญของข้อมูล และวิธีการ Import ข้อมูลจากไฟล์ Excel กันไปพอสมควร สำหรับในบทความนี้ ผู้เขียนจะขอพูดถึงวิธีการส่งออกข้อมูล หรือที่เรามักเรียกกันติดปากว่า “Export ข้อมูล” กันบ้าง เพื่อให้ผู้พัฒนาที่มีความสนใจสามารถพัฒนาโปรแกรมได้ครบวงจรทั้งแบบนำเข้าและส่งออกข้อมูล โดยผู้เขียนจะไม่ขอพูดถึงในรายละเอียดที่ได้กล่าวไว้แล้วก่อนหน้า ผู้อ่านสามารถอ่านรายละเอียดเพิ่มเติมได้ในบทความ “เรียนรู้วิธีการ Import ข้อมูลในรูปแบบไฟล์ Excel ด้วย ASP.NET (C#)” สำหรับในบทความนี้ผู้เขียนจะเน้นในส่วนของการ Export ข้อมูลเท่านั้น ซึ่งจะมีการอธิบายใน 2 ลักษณะเช่นกัน คือ แบบ Single sheet และแบบ Multiple sheet เพื่อให้เห็นเป็นแนวทางและสามารถนำไปต่อยอดการพัฒนาเพิ่มเติมสำหรับแต่ละท่านได้
กรณีส่งออกข้อมูล(Export) ซึ่งในบทความนี้จะแบ่งเป็น 2 ลักษณะ ดังนี้
- การส่งออกข้อมูลแบบ Single-sheet ซึ่งมีขั้นตอนการทำงานไม่ซับซ้อนมากนัก ดังนี้
protected void btnExport_Click(object sender, EventArgs e) { //// ตารางสมมติ สร้างขึ้นเพื่อให้ผู้พัฒนาเห็นภาพ หากเป็นกรณีใช้งานจริงจะเป็นข้อมูลที่ดึงจากฐานข้อมูลเพื่อ Export ในรูปแบบไฟล์ Excel DataTable table = new DataTable(); table.Columns.Add("Name", typeof(string)); table.Columns.Add("Latitude", typeof(decimal)); table.Columns.Add("Longitude", typeof(decimal)); table.Columns.Add("Description", typeof(string)); table.Rows.Add("University1", 7.006923, 100.500238, "Desc1"); table.Rows.Add("University2", 7.172661, 100.613726, "Desc2"); StringBuilder sb = new StringBuilder(); if (table.Rows.Count > 0) { string fileName = Path.Combine(Server.MapPath("~/ImportDocument"), DateTime.Now.ToString("ddMMyyyyhhmmss") + ".xls"); //// ลักษณะการตั้งค่าเพื่อเชื่อมต่อด้วย OleDb ซึ่งในกรณีนี้ไฟล์ Excel จะต้องมีนามสกุลเป็น .xls แต่หากเป็นนามสกุลแบบ .xlsx ต้องเปลี่ยนการกำหนดค่าให้เป็น conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"; แทน string conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; using (OleDbConnection con = new OleDbConnection(conString)) { ////เขียนคำสั่งในการสร้างตาราง ซึ่งในที่นี้คือ WorkSheet ที่ต้องการ พร้อมทั้งกำหนดชื่อและชนิดของข้อมูลในแต่ละคอลัมน์ string strCreateTab = "Create table University (" + " [Name] varchar(50), " + " [Latitude] double, " + " [Longitude] double, " + " [Description] varchar(200)) "; if (con.State == ConnectionState.Closed) { con.Open(); } ////รันคำสั่งที่เขียนในการสร้างตาราง OleDbCommand cmd = new OleDbCommand(strCreateTab, con); cmd.ExecuteNonQuery(); ////เขียนคำสั่งในการเพิ่มข้อมูล(insert) ข้อมูลในแต่ละฟิลด์ รวมทั้งประกาศพารามิเตอร์ที่ใช้ในการรับค่าข้อมูลที่อ่านได้ string strInsert = "Insert into University([Name],[Latitude]," + " [Longitude], [Description]" + ") values(?,?,?,?)"; OleDbCommand cmdIns = new OleDbCommand(strInsert, con); cmdIns.Parameters.Add("?", OleDbType.VarChar, 50); cmdIns.Parameters.Add("?", OleDbType.Double); cmdIns.Parameters.Add("?", OleDbType.Double); cmdIns.Parameters.Add("?", OleDbType.VarChar, 200); ////วนค่าที่ได้จากฐานข้อมูลและกำหนดค่าให้กับพารามิเตอร์และรันคำสั่งในการเพิ่มข้อมูลทีละรายการ foreach (DataRow i in table.Rows) { cmdIns.Parameters[0].Value = i["Name"]; cmdIns.Parameters[1].Value = i["Latitude"]; cmdIns.Parameters[2].Value = i["Longitude"]; cmdIns.Parameters[3].Value = i["Description"]; cmdIns.ExecuteNonQuery(); } } ////สร้างไฟล์ที่ต้องการดาวน์โหลดจากการอ่านที่ได้ทั้งหมด เพื่อบันทึกเป็นไฟล์ Excel ที่มีชื่อว่า University.xls byte[] content = File.ReadAllBytes(fileName); HttpContext context = HttpContext.Current; context.Response.BinaryWrite(content); context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; context.Response.AppendHeader("Content-Disposition", "attachment; filename=University.xls"); Context.Response.End(); } }
- การส่งออกข้อมูลแบบ Multiple sheet ใช้ในกรณีที่มีข้อมูลที่ Export จำนวนมากและต้องการแบ่งข้อมูลที่มีอยู่ออกเป็น WorkSheet ย่อย ซึ่งหลักการทำงานจะคล้ายกับการส่งออกข้อมูลแบบ Single-sheet แต่จะซับซ้อนกว่าในส่วนของการวนค่าจากในฐานข้อมูลมาใส่ใน WorkSheet โดยมีการกำหนดจำนวนแถวที่จะให้บันทึกในแต่ละ WorkSheet หากเกินค่าที่กำหนดจะสร้าง WorkSheet ใหม่และบันทึกข้อมูลลงไปใน WorkSheet นั้น
static StringBuilder sqlInsert = new StringBuilder(); static StringBuilder strScript = new StringBuilder(); public static void ExportToMultipleXLSheets( System.String OutputFileName) { string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mOutputFileName + ";Extended Properties='Excel 8.0'"; OleDbConnection xlConn = new OleDbConnection(connstr); try { xlConn.Open(); ////ตารางสมมติเช่นเดียวกับที่ได้กล่าวไว้ในแบบการส่งออกข้อมูลแบบ Single sheet DataTable table = new DataTable(); table.Columns.Add("EmployeeID", typeof(string)); table.Columns.Add("Name", typeof(string)); table.Columns.Add("LastName", typeof(string)); table.Columns.Add("Position", typeof(string)); table.Rows.Add("0001", "Jack", "Rayman", "Programmer"); table.Rows.Add("0002", "John", "Wicky", "Programmer"); table.Rows.Add("0003", "Jenifer", "Wincy", "Programmer"); table.Rows.Add("0004", "Kate", "Wrapper", "Manager"); table.Rows.Add("0005", "Bella", "Cole", "Programmer"); table.Rows.Add("0006", "Sandy", "Stick", "Programmer"); table.Rows.Add("0007", "Tom", "Runner", "Programmer"); ////เป็นการเรียกใช้เมธอดที่ใช้ในการเตรียมคำสั่งที่จะใช้วนสร้างตาราง/Worksheet PrepareScript(table); ////เป็นเมธอดที่ใช้ในการเริ่มต้นทำงานคำสั่งในการ Export โดยจะมีการคำนวณจำนวนแถวของข้อมูลว่าเกินที่ระบุไว้หรือไม่ หากเกินกำหนดจะสร้าง WorkSheet ใหม่นั่นเอง StartExport(table, xlConn); if (xlConn != null) { if (xlConn.State == ConnectionState.Open) xlConn.Close(); xlConn.Dispose(); } ////อ่านค่าและ Export ไปยังไฟล์ที่มีชื่อว่า ExportMultiSheetData.xls byte[] content = File.ReadAllBytes(mOutputFileName); HttpContext context = HttpContext.Current; context.Response.BinaryWrite(content); context.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; context.Response.AppendHeader("Content-Disposition", "attachment; filename=ExportMultiSheetData.xls" ); context.Response.End(); } catch (Exception exp) { throw new Exception("ImportToMultipleXLSheets", exp.InnerException); } finally { if (xlConn != null) { if (xlConn.State == ConnectionState.Open) xlConn.Close(); xlConn.Dispose(); } } }
o เมธอดในการเตรียมคำสั่งที่ใช้ในการสร้างตารางและเพิ่มข้อมูล
private static string PrepareScript(DataTable DTable) { // เตรียมคำสั่งในการสร้าง WorkSheet sqlInsert.Length = 0; strScript.Length = 0; ////วนค่าเพื่ออ่านค่าคอลัมน์ที่มีในแต่ละตาราง for (int i = 0; i < DTable.Columns.Count; i++) { sqlInsert.Append( "[" + DTable.Columns[i].ColumnName + "],"); strScript.Append("[" + DTable.Columns[i].ColumnName.Replace("'", "''") + "]"); ////กำหนดชนิดของข้อมูลแต่ละคอลัมน์ if (DTable.Columns[i].DataType.ToString().ToLower().Contains("int") || DTable.Columns[i].DataType.ToString().ToLower().Contains("decimal")) strScript.Append(" double"); else strScript.Append(" text"); strScript.Append(", "); } sqlInsert.Remove(sqlInsert.Length - 1, 1); strScript.Remove(strScript.Length - 2, 1); strScript.Append(") "); ////ผลที่ได้ : sqlInsert >> [EmployeeID],[Name],[LastName],[Position] strScript >> [EmployeeID] text, [Name] text, [LastName] text, [Position] text ) เพื่อนำไปใช้ในการ run คำสั่งการทำงานสร้าง (create table) และเพิ่มข้อมูล (insert) return strScript.ToString(); }
o เมธอดในการสร้างตาราง/WorkSheet ตามคำสั่งที่เตรียมไว้
private static void CreateXLSheets(DataTable DTable, OleDbConnection xlConn, System.String XLSheetName) { // สร้าง WorkSheet ใหม่ System.Text.StringBuilder SqlFinalScript = new System.Text.StringBuilder(); OleDbCommand cmdXl = new OleDbCommand(); try { SqlFinalScript.Length = 0; cmdXl.Connection = xlConn; ///สร้างคำสั่งในการสร้างตาราง/WorkSheet ขึ้นใหม่ โดยตั้งชื่อตามพารามิเตอร์ที่ได้รับมา SqlFinalScript.Append("CREATE TABLE " + XLSheetName + " ("); SqlFinalScript.Append(strScript.ToString()); cmdXl.CommandText = SqlFinalScript.ToString(); cmdXl.ExecuteNonQuery(); } catch (Exception xlSheetExp) { throw (new Exception("CreateXLSheetException", xlSheetExp.InnerException)); } finally { cmdXl.Dispose(); } }
private static void StartExport(DataTable DTable, OleDbConnection xlConn) { Int64 rowNo = 0, xlSheetIndex = 1, TotalNoOfRecords = 0; System.String NewXLSheetName = "Sheet"; System.Text.StringBuilder strInsert = new System.Text.StringBuilder(); TotalNoOfRecords = DTable.Rows.Count; OleDbCommand cmdXl = new OleDbCommand(); cmdXl.Connection = xlConn; for (int count = 0; count < DTable.Rows.Count; count++) { strInsert.Length = 0; ////กรณีที่เป็นแถวแรกจะทำการสร้าง WorkSheet ขึ้นใหม่ โดยเรียกใช้เมธอด CreateXLSheets if (rowNo == 0 ) { CreateXLSheets(DTable, xlConn, NewXLSheetName + xlSheetIndex); } rowNo += 1; ////กรณีที่จำนวนแถวใน 1 WorkSheet เกินที่กำหนดไว้จะสร้างชีทใหม่ ในกรณีนี้คือจะสร้าง WorkSheet ใหม่ทุกๆ 3 แถว และเพิ่มลำดับของ index ของ WorkSheet คราวละ 1 if (TotalNoOfRecords > 3 && rowNo > 3 { xlSheetIndex += 1; CreateXLSheets(DTable, xlConn, NewXLSheetName + xlSheetIndex); rowNo = 1; } ////เขียนคำสั่งในการเพิ่มข้อมูลไปยัง WorkSheet ที่กำหนด โดยมีการแทนที่ค่าของคำสั่งที่เตรียมไว้แล้วก่อนหน้านี้ในตอนเรียกใช้เมธอด PrepareScript strInsert.Append("Insert Into [" + NewXLSheetName + xlSheetIndex.ToString() + "$](" + sqlInsert.ToString() + ") Values ("); foreach (DataColumn dCol in DTable.Columns) { if (dCol.DataType.ToString().ToLower().Contains("int")) { if (DTable.Rows[count][dCol.Ordinal].ToString() == "") strInsert.Append("NULL"); else strInsert.Append(DTable.Rows[count][dCol.Ordinal]); } else if (dCol.DataType.ToString().ToLower().ToLower().Contains("decimal")) { if (DTable.Rows[count][dCol.Ordinal].ToString() == "") strInsert.Append("NULL"); else strInsert.Append(DTable.Rows[count][dCol.Ordinal]); } else strInsert.Append("\"" + DTable.Rows[count][dCol.Ordinal].ToString().Replace("'", "''") + "\""); strInsert.Append(","); } strInsert.Remove(strInsert.Length - 1, 1); strInsert.Append(");"); ////run คำสั่งที่สร้างขึ้นในการเพิ่มข้อมูลทีละรายการ cmdXl.CommandText = strInsert.ToString(); cmdXl.ExecuteNonQuery(); } }
o เขียนการทำงานเมื่อกดปุ่ม “Export”
protected void btnExport_Click(object sender, EventArgs e) { string fileName = Path.Combine(Server.MapPath("~/ImportDocument"), Guid.NewGuid().ToString() + ".xls"); //เรียกใช้เมธอดในการ Export ข้อมูลแบบ Multiple sheet ExportToMultipleXLSheets(fileName); }
หลักการทำงานคร่าวๆในการ Export ข้อมูลใน 2 ลักษณะจะมีหลักการพื้นฐานคล้ายกัน ซึ่งจะสรุปได้ดังนี้
- ดึงข้อมูลจากฐานข้อมูลมาเก็บไว้ใน Datadatable หรือ Dataset
- กำหนดชื่อไฟล์ที่จะใช้ในการเชื่อมต่อกับ OleDb
- เชื่อมต่อกับ OleDb โดยการกำหนดค่าต่างๆ เพื่อใช้ในการทำงานกับไฟล์ Excel นั้นๆ
- เตรียมกำหนดคำสั่ง sql command ในการสร้างโครงสร้างตาราง(create table) และ เพิ่มข้อมูล (insert) ในตารางที่สร้างไว้ หากเป็นกรณีที่ต้องการสร้างแบบ Multiple sheet
ก็จะทำการเตรียมชุดคำสั่งไว้ และนำไปวน run คำสั่งนั้นๆตามจำนวนรอบที่มีการสร้าง WorkSheet ใหม่นั่นเอง - สั่ง run คำสั่งดังกล่าว หากจำนวนแถวของข้อมูลเกินกว่าที่กำหนดต่อ 1 WorkSheet (ซึ่งในกรณีสมมติให้เป็น 3 แถว) จะทำการสร้าง WorkSheet ใหม่
- อ่านค่าที่ได้จากไฟล์ที่เชื่อมต่อกับ OleDb และผ่านกระบวนการ Export ข้อมูล มาเขียนเป็นไฟล์ Excel เพื่อให้ผู้ใช้สามารถบันทึกข้อมูลในรูปแบบไฟล์ที่ Export ได้
- หากเป็นการ Export ข้อมูลแบบ Multiple sheet จำนวนของ WorkSheet จะขึ้นอยู่กับข้อมูลที่ดึงมาและจำนวนที่กำหนดไว้ต่อ 1 WorkSheet นั่นเอง
หมายเหตุ : Namespace ที่ต้องอ้างอิงเพิ่มเติมในการใช้งานโค้ดที่กล่าวไว้ข้างต้น มีดังนี้
– System.IO
– System.Data.OleDb
– System.Data
– System.Text
– System.Data.OleDb
– System.Data
– System.Text
บทความนี้ถือเป็นเพียงการแนะนำวิธีเบื้องต้นในการ Export ข้อมูลไฟล์ในรูปแบบ Excel เท่านั้น ซึ่งผู้พัฒนาสามารถนำความรู้หรือข้อมูลที่ได้จากบทความนี้ไปประยุกต์ ดัดแปลงหรือเพิ่มลูกเล่นให้กับงานที่ท่านกำลังพัฒนาอยู่ได้ เพื่อเพิ่มความสามารถในกระบวนการนำ-เข้าส่งออกดังกล่าว นอกจากนี้ผู้เขียนหวังเป็นอย่างยิ่งว่าบทความนี้อาจเป็นอีกหนึ่งทางเลือกสำหรับผู้พัฒนามือใหม่หรือผู้ที่กำลังค้นหาวิธีการนี้อยู่ สามารถนำไปพัฒนาต่อได้โดยง่าย หากผิดพลาดประการใด ผู้เขียนขออภัยไว้ ณ ที่นี้ค่ะ
แหล่งข้อมูลอ้างอิง :
http://www.codeproject.com/Articles/33271/Import-and-Export-to-Multiple-Worksheets
http://dotnetawesome.blogspot.com/2013/11/how-to-import-export-database-data-from_18.html
