“ข้อมูล” นับว่าเป็นส่วนหนึ่งที่มีความสำคัญในการทำงานหรือการแสดงผลข้อมูลของระบบหรือเว็บไซต์ในปัจจุบัน ซึ่งอาจมีที่มาจากการจัดการเองผ่านระบบจัดการข้อมูล (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