“ข้อมูล” นับว่าเป็นส่วนหนึ่งที่มีความสำคัญในการทำงานหรือการแสดงผลข้อมูลของระบบหรือเว็บไซต์ในปัจจุบัน ซึ่งอาจมีที่มาจากการจัดการเองผ่านระบบจัดการข้อมูล (Back office) หรือมีการนำเข้าจากแหล่งอื่นเนื่องจากข้อมูลที่ต้องการบันทึกเข้าสู่ระบบดังกล่าวอาจมีจำนวนมาก ทำให้การป้อนข้อมูลผ่านระบบทีละรายการเป็นไปอย่างลำบากและเกิดความผิดพลาดได้โดยง่าย เช่น ข้อมูลการเงิน ข้อมูลการสั่งซื้อสินค้า เป็นต้น อีกทั้งยังพบว่ามีบางกรณีที่ผู้ใช้มีความต้องการดึงข้อมูลจากฐานข้อมูลและส่งออกมาในรูปแบบไฟล์อื่นๆเพื่อนำไปประมวลผลต่อไปได้โดยง่าย ซึ่งโดยทั่วไปผู้ใช้มักเก็บข้อมูลเหล่านี้ในรูปแบบไฟล์ต่างๆ เช่น ไฟล์ Excel หรือ ไฟล์ Access ดังนั้น นักพัฒนาของระบบที่มีความต้องการจัดการข้อมูลในลักษณะนี้จึงจำเป็นที่จะต้องมีความรู้เกี่ยวกับกระบวนการดังกล่าวเพื่อให้ได้ข้อมูลตามรูปแบบที่ผู้ใช้ต้องการ ซึ่งในบทความนี้ ผู้เขียนขอเสนอวิธีการ Import ข้อมูลให้ออกมาในรูปแบบไฟล์ Excel พัฒนาโดยใช้ ASP.NET ด้วย C# เนื่องจากเป็นกรณีความต้องการที่พบบ่อยและสามารถนำข้อมูลจากไฟล์ไปใช้งานต่อได้โดยง่าย ซึ่งจะพูดทั้งในลักษณะแบบ Single sheet และแบบ Multiple sheet เพื่อที่จะช่วยให้ผู้พัฒนาที่มีความสนใจสามารถเห็นภาพการทำงานโดยรวม และสามารถนำไปประยุกต์ใช้กับระบบที่ท่านกำลังพัฒนาอยู่ได้
กรณีนำเข้าข้อมูล(Import) ซึ่งในบทความนี้จะแบ่งเป็น 2 ลักษณะ ดังนี้
- การนำเข้าข้อมูลแบบ Single sheet ถือเป็นการนำเข้าในแบบทั่วไป ไม่ซับซ้อนมากนัก
ฝั่ง Client
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Excel.aspx.cs" Inherits="ExcelTest" %> <!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"> <asp:FileUpload ID="FileUpload1" runat="server" /> <div> <asp:GridView ID="GridView" runat="server"> </asp:GridView> <asp:Button ID="btnImport" runat="server" onclick="btnImport_Click" Text="Import" /> </div> </form> </body> </html>
ฝั่งเซิร์ฟเวอร์
protected void btnImport_Click(object sender, EventArgs e) { try { ////เป็นการกำหนดชื่อของไฟล์ที่ต้องการจะบันทึกลงเซิร์ฟเวอร์ ซึ่งมีการระบุพาธรวมทั้งนามสกุลของไฟล์ตามไฟล์ที่รับเข้ามา string fileName = Path.Combine(Server.MapPath("~/ImportDocument"), Guid.NewGuid().ToString() + Path.GetExtension(FileUpload1.PostedFile.FileName)); ////บันทึกไฟล์ดังกล่าวลงเซิร์ฟเวอร์ FileUpload1.PostedFile.SaveAs(fileName); string conString = ""; string ext = Path.GetExtension(FileUpload1.PostedFile.FileName); ////เป็นส่วนของเงื่อนไขในการตั้งค่า ConnectionString ในการอ่านไฟล์ Excel ด้วย OleDb ซึ่งจะแยกด้วยนามสกุลของไฟล์ Excel ที่รับมา if (Path.GetExtension(ext) == ".xls") { conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\""; } else if (Path.GetExtension(ext) == ".xlsx") { conString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';"; } ////เป็นการเปิดการเชื่อมต่อผ่าน OleDb OleDbConnection con = new OleDbConnection(conString); if (con.State == System.Data.ConnectionState.Closed) { con.Open(); } DataTable dtExcelSchema; dtExcelSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); ////ดึงค่าชื่อของ Worksheet ที่อ่านมาจากไฟล์ Excel ที่รับเข้ามา string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString(); ////เขียนคำสั่งในการดึงข้อมูลจาก Worksheet ดังกล่าว ซึ่งลักษณะการทำงานจะคล้ายกับการเขียนคำสั่ง sql command ในการดึงข้อมูลตารางโดยทั่วไป และเปรียบ Worksheet นั้นเป็นตาราง string query = "Select * from [" + SheetName + "]"; OleDbCommand cmd = new OleDbCommand(query, con); OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); da.Dispose(); con.Close(); con.Dispose(); ////เป็นการสมมติโครงการสร้างตาราง หากเป็นการทำงานจริงส่วนนี้จะหมายถึงตารางในฐานข้อมูลของแต่ละระบบ DataTable table = new DataTable(); table.Columns.Add("EmployeeID", typeof(string)); table.Columns.Add("EmployeeName", typeof(string)); ////เป็นการวนค่าเพื่อบันทึกลงฐานข้อมูล แต่ในกรณีนี้จะเป็นเพียงแค่การเพิ่มแถวข้อมูลลงใน datatable ที่ชื่อ table เท่านั้น foreach (DataRow dr in ds.Tables[0].Rows) { //// dr["EmployeeID"].ToString() ชื่อของค่าฟิลด์ต้องตรงกับชื่อของคอลัมน์ใน Worksheet ที่อ่านมาจากไฟล์ Excel table.Rows.Add(dr["EmployeeID"].ToString(), dr["EmployeeName"].ToString()); } ////นำค่าที่ได้แสดงในกริดวิว GridView.DataSource = table; GridView.DataBind(); } catch (Exception) { throw; } }
- การนำเข้าข้อมูลแบบ Multiple Sheet จะใช้ในกรณีที่มีจำนวนของ Worksheet ไม่จำกัด ขึ้นอยู่กับข้อมูล ซึ่งจะมีความซับซ้อนกว่าแบบแรก โดยหลักการทำงานโดยสรุปจะเป็นในลักษณะของการดึงข้อมูล Worksheet ที่มีทั้งหมดในไฟล์ Excel ที่อ่านได้ และนำไปเพื่อวนอ่านค่าข้อมูลในแต่ละชีทและนำค่าเหล่านั้นลงฐานข้อมูล ซึ่งจะอธิบายเป็นส่วนๆดังนี้
1. การดึงข้อมูลชื่อ Worksheet ที่มีทั้งหมดในไฟล์ที่รับเข้ามา
public static string[] getExcelSheets(string mFile) { try { string strXlsConnString; strXlsConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mFile + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; OleDbConnection xlsConn = new OleDbConnection(strXlsConnString); xlsConn.Open(); ////เป็นการดึงค่าชื่อ Worksheet ของไฟล์ excel ที่กำลังอ่าน โดยตารางหรือชีทใน Excel จะมีสัญลักษณ์ $ ต่อท้ายชื่อเสมอ DataTable xlTable = new DataTable(); xlTable = xlsConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); System.String strExcelSheetNames = ""; string sheetName; for (int lngStart = 0; lngStart < xlTable.Rows.Count; lngStart++) { ////เป็นการเอา '' ออกจากชื่อตาราง/worksheet ที่ดึงมาได้ sheetName = xlTable.Rows[lngStart][2].ToString().Replace("'", ""); ////เป็นการคัดกรองเฉพาะตัวที่เป็นตารางหรือworksheet เนื่องจากจบด้วย $ if (sheetName.EndsWith("$")) { ////เป็นการเชื่อมตัวสุดท้ายด้วย ~ เพื่อใช้ในการตัดคำในขั้นตอนถัดไป strExcelSheetNames += sheetName.Substring(0, sheetName.Length - 1) + "~"; } } ////เป็นการตัด ~ ตัวสุดท้ายออกจากการเชื่อมคำ if (strExcelSheetNames.EndsWith("~")) { strExcelSheetNames = strExcelSheetNames.Substring(0, strExcelSheetNames.Length - 1); } xlsConn.Close(); xlsConn.Dispose(); char[] chrDelimter = { '~' }; ////เป็นการตัดคำด้วย ~ และส่งค่าตัวแปร array ของ string ที่เป็นชื่อ worksheet ทั้งหมดที่อ่านได้กลับไป return strExcelSheetNames.Split(chrDelimter); } catch (Exception exp) { throw new Exception("Error while listing the excel" + " sheets from upload file " + exp.Message, exp); } }
2. การอ่านค่าข้อมูลใน Worksheet ที่มีทั้งหมดในไฟล์ที่รับเข้ามา โดยมีการส่งค่าของชื่อ Worksheet และชื่อของไฟล์ที่อ่าน รวมทั้งฟิลด์เพิ่มเติมที่ต้องการระบุในการอ่านค่า ซึ่งจะทำงานในลักษณะเดียวกับการ Import แบบ Single sheet นั่นเอง
public static DataSet getXLData(string xlSheetName, string xlFileName, string AdditionalFields) { try { string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + xlFileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'"; OleDbConnection xlConn = new OleDbConnection(connstr); DataSet xlTDS = new DataSet("xlDataSet"); xlConn.Open(); OleDbDataAdapter xlDA = new OleDbDataAdapter("Select" + AdditionalFields + " * from [" + xlSheetName + "$] ", xlConn); xlDA.Fill(xlTDS); xlConn.Close(); xlConn.Dispose(); ////เป็นการลบแถวที่มีค่าว่างออกจากการอ่านข้อมูลในไฟล์ RemoveEmptyRows(xlTDS.Tables[0], (AdditionalFields.Length - AdditionalFields.ToLower().Replace(" as ", "").Length) / 4); return xlTDS; } catch (Exception e) { throw new Exception("Error while reading data from excel sheet", e); } }
public static void RemoveEmptyRows(DataTable dtbl, System.Int32 intNumberOfFieldsToIgnore) { ////เป็นการตรวจสอบค่าว่างในแต่ละแถว System.String strFilter = ""; System.Int32 intAvgColsToCheck = Convert.ToInt32((dtbl.Columns.Count - intNumberOfFieldsToIgnore) * 0.75); if (intAvgColsToCheck < 3) { intAvgColsToCheck = dtbl.Columns.Count; } System.Int32 lngEnd = dtbl.Columns.Count; lngEnd = lngEnd - intAvgColsToCheck; ////เป็นการเชื่อมเงื่อนไขในการดึงข้อมูลว่าให้ฟิลด์ใดบ้างที่ห้ามเป็นค่าว่าง ในที่นี้จะทำการวนดูคอลัมน์ที่มีใน worksheet นั้นๆ และเชื่อมเป็นเงื่อนไข for (int lngStartColumn = dtbl.Columns.Count; lngStartColumn > lngEnd; lngStartColumn--) { strFilter += "[" + dtbl.Columns[lngStartColumn - 1].ColumnName + "] IS NULL AND "; } ////ทำในกรณีที่มีอย่างน้อย 1 คอลัมน์ถูกเพิ่มเป็นเงื่อนไขในการตรวจสอบค่าว่าง และลบคำว่า “AND” สุดท้ายออก เพื่อนำไปใช้งานในการกรองข้อมูลตามเงื่อนไขนี้ if (strFilter.Length > 1) { strFilter = strFilter.Remove(strFilter.Length - 4); } DataRow[] drows = dtbl.Select(strFilter); ////ลบแถวเมื่อพบว่าค่าของฟิลด์นั้นๆ เป็นค่าว่าง foreach (DataRow drow in drows) { dtbl.Rows.Remove(drow); } }
3. เขียนการทำงานเมื่อกดปุ่ม “Import”
protected void btnImport_Click(object sender, EventArgs e) { ////เรียกใช้เมธอดในการบันทึกไฟล์ Excel ที่รับเข้ามาตามพาธของโฟลเดอร์ที่กำหนด string fileName = uploadXLFile(FileUpload, Server.MapPath("~/ImportDocument")); ////เรียกใช้เมธอดในการดึงค่าชื่อ WorkSheet ที่มีทั้งหมดในไฟล์ โดยมีตัวแปร array ชนิด string มารับข้อมูลดังกล่าว string[] listExcelSheet = getExcelSheets(fileName); DataSet DSTotal = new DataSet(); ////วนลูปข้อมูล WorkSheet ตามชื่อในตัวแปร array และส่งค่าให้กับเมธอดที่ใช้ในการอ่านค่าข้อมูลในแต่ละ WorkSheet นั้น for (int i = 0; i < listExcelSheet.Count(); i++) { DataSet DS = getXLData(listExcelSheet[i], fileName, ""); DSTotal.Merge(DS); } ////แสดงผลตัวอย่างข้อมูลที่อ่านได้ในกริดวิว ซึ่งในการใช้งานจริงในส่วนนี้ผู้พัฒนาจะต้องนำข้อมูลที่อ่านได้เหล่านี้วนบันทึกลงฐานข้อมูลเช่นเดียวกับที่กล่าวไว้ในการ Import ข้อมูลแบบ Single sheet นั่นเอง if (DSTotal.Tables[0].Rows.Count > 0) { GvData.DataSource = DSTotal.Tables[0]; GvData.DataBind(); } } } public static string uploadXLFile(FileUpload fileUpload, string mPath) { mPath = Path.Combine(mPath ,Guid.NewGuid().ToString() + Path.GetExtension(fileUpload.PostedFile.FileName)); fileUpload.SaveAs(mPath); return mPath; }
จะเห็นว่าจริงๆแล้วการทำงานใน 2 ลักษณะจะมีหลักการพื้นฐานคล้ายกัน ซึ่งจะสรุปได้ดังนี้
- บันทึกไฟล์ Excel บนเซิร์ฟเวอร์ตามพาธที่กำหนดเพื่อให้สามารถเรียกอ่านค่าได้
- เชื่อมต่อกับ OleDb โดยการกำหนดค่าต่างๆ เพื่อใช้ในการอ่านค่าจากไฟล์ Excel นั้นๆ
- กำหนดคำสั่ง sql command ในการดึงข้อมูลจาก WorkSheet ซึ่งต้องมีการระบุชื่อของ WorkSheet นั้นๆ
- สั่ง run คำสั่งดังกล่าวและนำค่าที่ได้ไปประมวลผลต่อไป
- หากเป็นกรณีแบบ Multiple sheet เราจะไม่สามารถทราบจำนวนและชื่อของ WorkSheet ตายตัว จึงต้องเพิ่มการทำงานที่ทำการวนค่าเพื่อดึงข้อมูลชื่อ WorkSheet และทำตามกระบวนการต่อไป
หมายเหตุ : Namespace ที่ต้องอ้างอิงเพิ่มเติมในการใช้งานโค้ดที่กล่าวไว้ข้างต้น มีดังนี้
– System.IO
– System.Data.OleDb
– System.Data
– System.Text
– System.Data.OleDb
– System.Data
– System.Text
สำหรับในบทความนี้ผู้เขียนจะขอเสนอวิธีการ Import ข้อมูลด้วยไฟล์ Excel ไว้เพียงเท่านี้ก่อน หากมีผู้รู้ท่านใดมีข้อเสนอแนะที่ต้องการแลกเปลี่ยนความรู้ร่วมกัน สามารถชี้แจงเพิ่มเติมได้เป็นกรณีศึกษาเพื่อการเรียนรู้ หากผิดพลาดประการใด ผู้เขียนขออภัยไว้ ณ ที่นี้ค่ะ และสำหรับท่านผู้พัฒนาที่มีความสนใจเกี่ยวกับการ Export ข้อมูลไฟล์ Excel ด้วย ASP.NET(C#) สามารถติดตามต่อได้ใน Part II นะคะ
แหล่งข้อมูลอ้างอิง :
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